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.


Download Practice Workbook

Download this practice workbook below.


Overview of Variable Cost

Definition

Variable Cost per unit denotes the per unit cost needed for the labor, raw material, and other resources needed, which fluctuate as the unit produce changes.

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 curve exists.

1. Non-linear Variable Cost

As the name suggests, the Variable Cost value of the items is not linear in this type of curve. Meaning 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 then remaining then will be the Fixed Cost.

 Calculate Variable Cost Per Unit in Excel

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 zero. The only cost then remaining then will be the Fixed Cost.

 Calculate Variable Cost Per Unit in Excel


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

Step-by-Step Procedure to Calculate Variable Cost Per Unit in Excel

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.

 Calculate Variable Cost Per Unit in Excel

Read More: How to Calculate Cost per Unit in Excel (With Easy Steps)


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:
=D5+E5+F5

Entering this formula will calculate the Variable Cost of January in cell G5.

 Calculate Variable Cost Per Unit in Excel

  • 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.

 Calculate Variable Cost Per Unit in Excel

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


Similar Readings


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:
[/wpsm_box
=SUM(G5:G16)

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 need to calculate how many units we produced in the 12 months, using the SUM function.

Steps

  • Now select the cell E19, and enter the following formula:
=SUM(C5:C16)

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

 Calculate Variable Cost Per Unit in Excel

Read More: How to Calculate Projected Cost in Excel (4 Effective Ways)


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:
=E18/E19

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.


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. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo