How to Make Vehicle Life Cycle Cost Analysis Spreadsheet in Excel

Life Cycle Cost Analysis (LCCA) evaluates the overall cost of asset possession. It takes into account costs involved in buying, caring for, and discarding a venture or an item. The goal of LCCA is to determine the entire project costs of different designs. Also, choose the one that will result in a lower overall residual value for a facility of comparable features. We can easily construct LCCA with Microsoft Excel, as it has built-in tools and functions to perform different calculations. In this article, we will demonstrate the procedures to make a vehicle life cycle cost analysis spreadsheet in Excel.


Download Template

You can download the template and practice with them.


What Is Life Cycle Cost Analysis?

A technique for determining the overall cost of facility management is life-cycle cost analysis (LCCA). It accounts for all expenses related to purchasing, maintaining, and getting rid of construction or formation.

The overall cost of resource ownership is investigated and evaluated using life cycle cost analysis. It takes into account costs associated with purchasing, caring for, using, and discarding a project or an item. When numerous projects meet the same performance standards but have different running expenses and startup prices, it is notably utilized to choose the best project. These costs must be compared in order to choose the approach that would maximize net savings.

The formula for Life Cycle Cost is,

LCC = Costs of Acquisition + Operating Cost + Maintenance Costs + Total Other Costs & Savings – End of Life

Step-by-Step Procedures to Make Vehicle Cycle Cost Analysis Spreadsheet in Excel

As we already know, Life Cycle Cost Analysis (LCCA) draws upon the well-established concepts of financial evaluation. Compared to other economic methodologies that solely consider initial expenses or short-term operational costs, LCCA greatly outperforms in evaluating long-term cost-effectiveness. So, let’s look at the process to construct the LCCA in excel.


Step 1: Input General Conditions for Vehicle

To start, we need to input the general conditions of the vehicle.

  • Firstly, insert the period of vehicle ownership in the year. In our case, we have ten years of the period of vehicle ownership.
  • Secondly, the discount rate we input is in percentage. We get a 2% discount.
  • Thirdly, suppose we get two offers, so, we put the number of bids or offers in the column.

Step-by-Step Procedures to Make Vehicle Cycle Cost Analysis Spreadsheet in Excel


Step 2: Compute Acquisition Costs

To compute the acquisition costs we will need to combine the IF function and the PV function. The PV function is categorized under the financial function in excel. This function returns the present value of any investment. And we use the IF function to match any condition. This is one of the logical functions in excel. Now, assume that our vehicle model is a Mercedes Citaro, now we need to calculate the costs of acquisition per unit.

  • In the first place, input the number of vehicles, then the purchase price per unit, and also the lease price (if any). In our example, we do not have any lease price, so we keep the cell empty.
  • Then, insert the following formula.
=IF(D11>0,-PV($D$6*0.01,$D$5,D13)+D12,0)
  • Further, press Enter to see the result.

Step-by-Step Procedures to Make Vehicle Cycle Cost Analysis Spreadsheet in Excel

🔎 How Does the Formula Work?

  • -PV($D$6*0.01,$D$5,D13): To acquire the present value we multiply the discount rate by 1% which is 0.01 in decimal. We retain the cell as an absolute value so that the value of that cell won’t change. Then, we take the period of vehicle ownership and the lease price as the arguments of the PV function. And, we get the result -0.
  • -PV($D$6*0.01,$D$5,D13)+D12: Now, simply add the purchase price with the present value. And we get $230.
  • IF(D11>0,-PV($D$6*0.01,$D$5,D13)+D12,0): By estimating the formula we get the acquisition cost.

Step 3: Calculate Operating Cost Per Vehicle

Now, we will calculate the operating cost per vehicle. The vehicle operating cost refers to the entire costs that drivers incur when they use their automobiles to travel from one location to another.

  • For this, we need the annual use of the vehicle which is 80000 km.
  • Then, the type of the fuel, in this case, we use diesel.
  • Also, the fuel consumption per vehicle is 45.
  • Next is the price of the fuel, which is $5.
  • After inserting all the components that we use for the calculation of the operating cost per unit, put the formula for the calculation.
=(D19*D20)/D17
  • Finally, press Enter. And you will get the result in the resulting cell and the formula will appear in the formula bar.

🔎 How Does the Formula Work?

Here, we use the formula for operating cost, so we first obtain the total cost. For this, we multiply the fuel consumption per vehicle by the fuel price. Then, divide the total cost by the annual use of the vehicle.

Read More: How to Make Daily Vehicle Mileage and Fuel Report in Excel


Step 4: Quantify Maintenance Costs Per Vehicle

Maintenance costs are the single or multiple ongoing expenses a business has to pay to maintain its assets, vehicles, and equipment. To calculate the maintenance cost, again we are going to merge the IF and the PV functions.

  • To begin with, we provide the annual maintenance costs per unit for a year and also an annual service agreement alternatively.
  • Secondly, insert the formula.
=IF(H5>0,-PV($D$6*0.01,$D$5,(H5+H6)))
  • Lastly, press the Enter button on your keyboard.

🔎 How Does the Formula Work?

  • -PV($D$6*0.01,$D$5,(H5+H6): Likewise step 2, we multiply the discount rate by 1%, or 0.01 in decimal form, in order to obtain the present value. To ensure that the value of the cell won’t change, we keep it as an absolute value. Then, periods of ownership are used as the arguments in the PV function. Next, we just add the annual maintenance costs per unit and the annual service agreement. But in our case, we don’t have the annual service agreement.
  • =IF(H5>0,-PV($D$6*0.01,$D$5,(H5+H6))): This will calculate the maintenance cost per unit.

Step 5: Evaluate Taxes & Other Costs/Subsidies Per Vehicle

In this step, we will evaluate the taxes and other costs or subsidies per vehicle.

  • First, we need the vehicle tax per unit within a year.
  • Second, the insurance cost during a year, per unit.
  • Further, the infrastructure.
  • Instead of this, we can just set the infrastructure annual cost.
  • Furthermore, to calculate the total other costs and savings per unit, insert the formula into the selected cell.
=-PV(D6*0.01,D5,(H10+H11+H13))+H12
  • Press Enter to compute the total costs and savings.

🔎 How Does the Formula Work?

Similarly, as in the previous steps, we use those arguments for the PV function. Here, we also add the vehicle tax, insurance cost, and infrastructure. And we get the sum of those parameters.


Step 6: Insert End of Life

A vehicle may be stripped, damaged, or otherwise unusable due to mechanical breakdown in order to be designated End of Life. Although some components are revenues to fund from car part manufacturers. Some are recovered and sold for use in fixing other vehicles. These latter parts are then reconditioned and supplied to auto supply retailers.

  • We input the remnant value per unit.
  • Then, simply put the SUM function to generate the End of Life.
=Sum(H17)
  • Lastly, hit the Enter button on your keyboard.


Step 7: Calculate Total Life Cycle Cost

Now, the main step, to calculate the total life cycle cost we have to follow the sub procedures.

  • Firstly, we will calculate the total life cycle cost per unit. For this, put the formula into the resulted cell.
=D14+D21+H7+H14-H18
  • Hit Enter.

  • Further, to calculate the total cost type the formula.
=H20*D11
  • Similarly, to finish the procedure, press the Enter key.

Step 8: Construct Diagram

Spreadsheet administrators may visualize data sets using Excel charts. We can construct a diagram for visualizing the total life cycle cost.

  • Select both cells of the total life cycle cost per unit and the total life cycle cost.
  • Then, go to the Insert tab from the ribbon.
  • Now, from the Charts category, click on the Insert Column or Bar Chart drop-down menu.
  • Next, choose the Clustered Column from the 2-D Column.

  • And, you will be able to see the diagram for the LCCA for vehicles.


Final Template

This is the final template for the vehicle life cycle cost analysis spreadsheet in Excel.

Vehicle Life Cycle Cost Analysis Excel Spreadsheet


Conclusion

The above procedures will assist you to Make Vehicle Life Cycle Cost Analysis Spreadsheet in Excel. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo