Mashhura Jahan
There are many ways of calculating Depreciation. Units of Production is one of them. The main objective of this article is to explain how to calculate the Units of Production Depreciation method with formula in Excel.
Download Practice Workbook
Here, I have provided the practice workbook. You can download it from here.
Units of Production Depreciation Method
The Units of Production is a method of calculating the Depreciation of an asset as time passes. In this method, the value of an asset is mainly related to the number of units produced rather than the number of years. To formula for the Units of Production Depreciation method is:
Step-by-Step Procedures to Calculate Units of Production Depreciation Method with Formula in Excel
I have taken the following dataset to explain this article. Here, the Asset Cost, Salvage Value, and estimated Useful Units in miles are provided. This dataset also contains the units per year over 5 years. The example is for a truck and the units produced are considered as Miles Driven. I will use this dataset to show you the step-by-step procedures to calculate the Units of Production Depreciation method with formula in Excel.
Step-01: Calculate Depreciable Cost
In this first step, I will show you how you can calculate the Depreciable Cost in units of production method. Let’s see the steps.
- Firstly, create a section for Depreciable Cost.
- Secondly, select the cell where you want the Depreciable Cost. Here, I selected Cell C9.
- Thirdly, in Cell C9 write the following formula.
=C5-C6
- After that, press Enter to get the result.
Read More: How to Create Vehicle Depreciation Calculator in Excel
Step-02: Evaluate Depreciation Per Unit
Now, I will calculate Depreciation Per Unit. Let me show you how you can do it.
- In the beginning, create a section for Depreciation Per Unit.
- Next, select the cell where you want the Depreciation Per Unit.
- Then, write the following formula in that selected cell.
=C9/C7
- After that, press Enter to get the result.
Read More: How to Use WDV Method of Depreciation Formula in Excel
Step-03: Find the Opening Book Value
Here, I will show you how to find out the Opening Book Value in units of production method. Let’s see the steps.
- First, create a table containing the following columns.
- Then, select the cell where you want to calculate the first Opening Book Value. Here, I selected Cell C14.
- Next, in Cell C14 write the following formula.
=C5
- Afterward, press Enter to get the result.
- After that, select the cell where you want the second Opening Book Value.
- Then, write the following formula in that selected cell.
=G14
- Next, press Enter to the result.
- Further, drag the Fill Handle down to copy the formula.
- Finally, you can see that I have copied the formula to the other cells. And, the values will show up when I complete the rest of the table.
Read More: How to Create Fixed Asset Depreciation Calculator in Excel
Step-04: Determine Depreciation Expense
In this step, I will calculate the Depreciation Expenses. For this, you will need to multiply the Depreciation Per Unit by the Unit Produced in a year. Let’s see how you can do it for this example.
- Firstly, select the cell where you want the Depreciation Expense. Here, I selected Cell E14.
- Secondly, in Cell E14 I wrote the following formula.
=D14*$C$10
- Thirdly, press Enter to get the Depreciation Expense.
- Afterward, drag the Fill Handle down to copy the formula.
- Finally, you can see that I have copied the formula to all the other cells and got my desired result.
Read More: How to Calculate Straight Line Depreciation Using Formula in Excel
Step-05: Calculate Accumulated Depreciation
Now, I will show you how to calculate the Accumulate Depreciation. Let’s see the steps.
- To begin with, select the cell where you want the first Accumulated Depreciation.
- Then, write the following formula in that selected cell.
=E14
- After that, press Enter to get the result.
- Then, select the cell where you want the second Accumulated Depreciation. Here, I selected cell F15.
- Afterward, in cell F15 write the following formula.
=E15+F14
- Next, press Enter to get the result.
- Then, drag the Fill Handle down to copy the formula.
- Finally, you can see that I have copied the formula to the other cells and got all the Accumulated Depreciation.
Read More: Calculate Sum of Years Digits Depreciation with Formula in Excel
Step-06: Find the Closing Book Value
Here, I will show you how you can find out the Closing Book Value in the Units of Production Depreciation method. Let me show you the steps.
- Firstly, select the cell where you want the Closing Book Value.
- Secondly, write the following formula in that selected cell.
=C14-E14
- Thirdly, press Enter to get the result.
- After that, drag the Fill Handle down to copy the formula.
- Lastly, you can see that I have copied the formula to the other cells and got the Closing Book Value.
Read More: How to Use SLM Method of Depreciation Formula in Excel
Step-07: Evaluate Total and Adjust It Accordingly
At this point, I will calculate the Total and then adjust it accordingly if an adjustment is needed. Let’s see the steps.
- In the beginning, select the cell where you want the Total. Here, I selected Cell E19.
- Then, in Cell E19 write the following formula.
=SUM(E14:E18)
- After that, press Enter and you will get the Total.
- You can see that the Total does not match the Depreciable Cost. So, you will need to adjust that.
- To do that, select the last row of the table.
- After that, right-click on the selected row.
- Then, select Delete.
- Consequently, the Delete dialog box will appear.
- Select Shift cells up.
- After that, select OK.
- Now, you will see the last row is deleted. But, the Total is still greater than the Depreciable Cost.
- To adjust that, select the last cell of Depreciation Expense. Here, I selected Cell E17.
- After that, in Cell E17 write the following formula.
=G16-C6
- Next, press Enter to get the result.
- In the end, you can see that the Total now matches the Depreciable Cost.
Final Output
In the following image, you can see the final output of the calculation of the Units of Production Depreciation method with a formula in Excel.
Practice Section
Here, I have provided a practice sheet for you to practice how to calculate the Units of Production Depreciation method with formula in Excel.
Conclusion
To conclude, I tried to cover how to calculate the Units of Production Depreciation method with formula in Excel in this article with 7 easy steps. I hope this article was clear to you. For more articles, stay connected with ExcelDemy. Lastly, if you have any questions, feel free to let me know in the comment section below.
