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

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.