How to Calculate Variable Cost Per Unit in Excel (with Quick Steps)

In our daily calculation and management work, we need to evaluate the Variable Cost of various components. If you are curious to know how you can calculate the variable cost per unit in Excel. This article may come in handy for you. In this article, we are going to show how you can calculate the variable cost per unit in Excel with detailed explanations.

Overview of Variable Cost

Definition

General Formula

Characteristics of Variable Cost

• They are normally output-dependent, meaning the value of the total variable cost will be entirely dependent only on the No of Units Produced.
• The output of production and the variable cost have a positive relationship.
• Variable cost per unit produced or in other words, the Average Variable Cost generally remains constant. In some cases, it changes.
• They are also known as the Prime Costs.
• Hypothetically, when the output unit becomes zero, the variable cost is nullified accordingly.

Types of Variable Cost

In theory, two different types of variable cost curves exist.

1. Non-linear Variable Cost

As the name suggests, the variable cost value of the items is not linear in this type of curve. This means the value of the Variable Cost of the items does not increase in proportion to the unit increase. One such example is given below:

In this table, we can see that the variable cost is now set to 50, which means whatever the item number of the item produced, the cost of producing them would be 50. However as the variable cost is non-linear, the graph will be nonlinear as well, as shown below. According to the graph, when the no of units produced is zero, then the variable cost will be zero. The only cost remaining will be the Fixed Cost.

2. Linear Variable Cost

As the name suggests here, the variable cost here in the table will be linear, which means that the value of the variable cost will increase in proportion to the unit increase. Furthermore, as the fixed cost won’t change with the change of unit increase, the total cost also going to be linear, as shown below.

In this table, we can see that the variable cost is now set to 50, which means whatever the item number produced, the cost of producing them would be 50. However, as the variable cost is linear, the graph will be linear as well, as shown below. According to the graph, when the no of units produced is zero, then the variable cost will be zero. The only cost remaining then will be the fixed cost.

Components of Variable Cost

There are different types of variable costs that exist, so, normally, the variable cost will consist of diverse types of components.

For example,

• Raw Materials
• Labor Cost
• Transportation
• Manufacturing Overhead
• Utility

How to Calculate Variable Cost Per Unit in Excel: Step-by-Step Procedures

For the demonstration purpose, we are going to use the following dataset. In this dataset, we have all the variable cost breakdowns per month listed. The variable cost is Raw Materials, Labor Cost, Manufacturing Overhead, etc.

Step 1: Prepare Dataset

To get variable expenses per unit, we need to prepare the dataset properly, Otherwise, a poorly made dataset can produce flawed output that can lead to a misleading conclusion.

Steps

• In the beginning, we need to set up the dataset properly to calculate the variable cost per month.
• We know, that the variable cost consists of various terms and factors like Labor Cost, Manufacturing Overhead, etc.
• We will add the terms in the horizontal direction.
• Then we calculate the variable cost for each month and then we will calculate the variable cost per unit or in other words, the Average Variable Cost by dividing the total Variable Cost by the total amount of Units.

Step 2: Evaluate Variable Cost for Each Month

After preparing the dataset we are going to calculate the variable cost for each month in the range of cells G5:G16.

Steps

• First, select the cell G5, and then enter the following formula:

Entering this formula will calculate the variable cost of January in cell G5.

• Now drag the Fill Handle in the corner of cell G5 to cell G16.
• Doing this will fill the range of cells G5:G16 with the variable cost, of the corresponding months in the range of cells B5:B16.

Step 3: Calculate Total Variable Cost

After we calculated the variable cost for each month, now we will calculate the total variable cost over the span of 12 months, using the SUM function.

Steps

• Then select the cell E18 and enter the following formula:

Doing this will calculate the total variable cost over the span of the 12 months in cell E18.

Step 4: Estimate Total Unit Produced

After we calculated the whole variable cost over the span of 12 months, we needed to calculate how many units we produced in the 12 months, using the SUM function.

Steps

• Now select cell E19, and enter the following formula:

Doing this will estimate the Total Number of Units Produced in the span of 12 months.

Step 5: Determine Variable Cost Per Unit

After we calculated the total variable cost and the total no of units produced over the span of 12 months. We will calculate the variable cost per unit as we got all the necessary parameters.

Steps

• Now select the cell E19 and enter the following formula:

Doing this will divide the Total Variable Cost by the Total Amount of Units Produced. Which in turn will calculate the Variable Cost Per Unit or in the other words, the Average Variable Cost.

By following the above step-by-step procedure, we were able to calculate variable cost per unit in Excel.

Download Practice Workbook

Download this practice workbook below.

Conclusion

To sum it up, the question “how to calculate Variable Cost per unit in Excel” is answered here in 4 separate steps with elaborate explanations. For this problem, a macro-enabled workbook is attached where you can practice these methods.

Feel free to ask any questions or feedback through the comment section.

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