How to Calculate Production Cost in Excel (3 Effective Ways)

The production cost is the total cost of manufacturing items or providing services, including all intended and unintended expenses. It can be expressed in the following equation:

Production Cost = Direct Labor Cost + Direct Material Cost +  Indirect Material Cost + Indirect Labor Cost + Other Overhead Cost

If

Manufacturing Overhead Cost = Indirect Material Cost + Indirect Labor Cost + Other Overhead Cost

Then

Production Cost = Direct Labor Cost + Direct Material Cost + Manufacturing Overhead Cost

To calculate production cost we will use the following dataset, containing Direct Labor Cost, Direct Material Cost, Indirect Material Cost, Indirect Labor Cost, and Other Overhead Costs.

Method 1 – Insert Simple Formula to Compute Production Cost

As the formula for production cost is a simple addition operation, we can use a simple formula to calculate it.

STEPS:

To simplify the formula, we will find the manufacturing cost first.

• Select cell C11.
• Enter the following formula:
`=C7+C8+C9`
• Then, press Enter.

• To calculate the production cost, enter the following formula into cell C13:
`=C5+C6+C11`
• Press Enter on your keyboard.

• Alternatively, simply use the following formula:
`=C5+C6+C7+C8+C9`
• Press Enter to complete the process.

The result is returned, and the formula is visible in the formula bar.

Method 2 – Derive the Production Cost Using the SUM Function

The SUM function is used to sum a set of values.

STEPS:

We will again calculate the manufacturing cost first to simplify the formula.

• In cell C11, enter the following formula:
`=SUM(C7,C8,C9)`
• Press the Enter key on your keyboard.

• In cell C13, enter the following formula:
`=SUM(C5,C6,C11)`
• Press Enter.

• Alternatively, just use the formula below:
`=SUM(C5,C6,C7,C8,C9)`
• Press Enter to finish the procedure and view the result in the formula bar.

Method 3 – Apply User Defined Function to Calculate Production Cost Using VBA

To calculate the production cost using a user-defined function made with VBA code, we’ll use the following simplified dataset, containing the costs for direct labor, direct material, and manufacturing cost.

STEPS:

• Go to the Developer tab on the ribbon.
• From the Code category, click on Visual Basic to open the Visual Basic Editor.

• Alternatively, to open the Visual Basic Editor press Alt + F11 or right-click on your worksheet and go to View Code.

The Visual Basic Editor opens.

• Click on Module from the Insert drop-down menu bar.

A Module box opens.

• Into it, copy and paste the following VBA code:

VBA Code:

``````Function ProductionCost(dl As Double, dm As Double, mc As Double) As Variant
ProductionCost = (dl + dm + mc)
End Function``````
• Next, save the code by pressing Ctrl + S.

The code creates a function named ProductionCost, where dl is the Direct Labor Cost, dm is the Direct Material Cost, and mc is Overhead Costs on Manufacturing.

• Go back to the worksheet.
• Select cell C9, the cell where we want the production cost result to appear.
• Enter the following formula using the function that we just created:
`=ProductionCost(C5,C6,C7)`
• Press Enter.
• The result displays in cell C9, and the user-defined formula will show in the formula bar.

<< Go Back to Cost | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF