ExcelInsider Team
The cost-benefit analysis is a lengthy process of evaluating the costs and benefits of a project. Businesses conduct cost-benefit analyses to understand the advantages and disadvantages of certain investments, and to determine whether the subsidiaries are worth keeping or not. In Excel, we can do cost-benefit analysis by counting the expenses and revenues and using a few formulas to sort everything. This article provides a step-by-step guide on how to perform a cost-benefit analysis in Excel.
➤ Summarize the benefits and costs in a table, use the SUM function to add the values, like the following:
=SUM(B3:B6)
➤ Replace B3:B6 with the range that you want to add values from.
➤ Calculate the Net Benefits by subtracting the total costs from the total benefits. You can use the following formula:
=G7-G11
➤ Replace G7 with the total benefits and G11 with the total costs.
➤ Calculate the net flows for each year using a similar formula.
➤ Calculate the NPV using the following formula:
=NPV(0.07,B16:F16)
➤ Replace 0.07 with the discount rate, and B16:F16 with the range of the net flows.
➤ Calculate the Benefit-Cost Ratio using the following formula:
=G7/G11
➤ Here, G7 should be replaced by the total benefits, and G11 should be replaced with the total costs.
What Is Cost-Benefit Analysis (CBA) in Finance?
Cost-Benefit Analysis (CBA) is a process to find the financial strengths and weaknesses of a business or an investment. The process compares the cost with the benefits to determine whether the investment is worthwhile or not. A cost-benefit analysis includes a few measurements like the net benefit, net present value, and benefit-cost ratio.
Cost-Benefit Analysis, unlike the other evaluation methods, includes non-monetary benefits and assigns a monetary value to them. For example, a business might spend a lot to gain customer satisfaction or brand value. In a regular financial analysis, like ROI, these values will be omitted. CBA, on the other hand, will use an estimated value to truly understand the potential of a project.
An example of CBA would be getting food delivered to your home instead of cooking. Cooking food costs less, maybe 30$ for a meal. It might cost $100 to get the food delivered, but it saves you time and labor, which might cost more than $100 if you are a working employee. As the benefits outweigh the cost, choosing food delivery would be a better choice.
Steps to Perform Cost-Benefit Analysis in Excel
To do the cost-benefit analysis, we essentially need six tables, and we have those here to do the calculation.
Non-Recurring Costs:
Recurring Costs:
Quantitative Benefits:
Cost Savings:
Cost Avoidance:
Other Benefits:
Now, we will summarize all of these tables and calculate the NPV and Benefit-Cost Ratio.
Step 1: Calculate the Totals
First, we need to calculate the totals of every cost and benefit. Here is how to do so:
➤ In the G2 cell, write the following formula to calculate the totals of the cost “Software Development” for all the years:
=SUM(B2:F2)
➤ Then, autofill G3 and G4 to calculate the sum of other non-recurring costs.
➤ Write the following formula in B5 and autofil til F5.
=SUM(B2:B4)
➤ Here, all of the tables contain three items. Therefore, we don’t need to insert the SUM function again and again. Instead, we can copy the current cells to the others.
➤ Select G2:G4 and press Ctrl + C to copy.
➤ Go to G8:G10 and press Enter.
➤ The formulas will automatically be replaced with the suitable cells from the Recurring Costs table.
➤ Do the same for the totals, select B5:F5 cells, and press the Copy icon of the Clipboard section of the Home tab.
➤ Then go to B11 and click on the Paste icon.
➤ Do the same for all the tables, and we end up with a filled worksheet.
Step 2: Create a Cost-Benefit Summary Table
With all these values from the other tables, we need to create a table that will summarize everything.
➤ In a new worksheet, create the following table:
➤ We need to import the values from other tables using references so that if we have to change something in the future, we can do that at ease.
➤ In B3 cell, write the following formula, then autofill till F3:
='Cost-Benefit Analysis'!B17
➤ Similarly, import Cost Savings, Cost Avoidance, Other Benefits, Non-Recurring Costs, and Recurring Costs from the original sheet.
➤ Now, to calculate the total benefits for the years, write the following formula in the B7 cell and autofill till G7.
=SUM(B3:B6)
➤ The G7 cell shows 0 dollars as there are no values in the G column yet. Fill up the column by putting the following formula in the G3 cell and autofilling till G6:
=SUM(B3:F3)
➤ Now the G7 cell is filled automatically.
➤ In B11 cell, write the following formula and autofill till G11 cell:
=B9+B10
➤ Use the following function in the G9 cell and autofill G10:
=SUM(B9:F9)
➤ Calculate the Net Benefits in B13 cell using the following formula:
=G7-G11
Step 3: Calculate the NPV and the Benefit Cost Ratio
To calculate the NPV, we need to make a small table with the net flows. The calculation of the benefit-cost ratio is rather simpler.
➤ Create a table in A15:F16 cells like the following for the net flows:
➤ In B16 cell, write the following formula and autofill till F16:
=B7-B11
➤ In this tutorial, we are using a discount rate of 7% for the Net Present Value (NPV) calculation. To do the calculation, use the following formula in the B18 cell:
=NPV(0.07,B16:F16)
➤ Calculate the Benefit-Cost Ratio using the following formula:
=G7/G11
➤ As the Benefit-Cost Ratio is greater than 1, the investment can be considered profitable.
Step 4: Create a Breakeven Chart (Optional)
For better visualization of the project, you might want to create a breakeven chart that will show the cost-benefit analysis.
➤ Hold Ctrl and select A7:F7 and A11:F11 cells.
➤ Go to the Insert tab of the ribbon, and select the first 2-D Line chart from the Charts group.
➤ Click on the Chart Title, and change it to Cost-Benefit Analysis.
Frequently Asked Questions
What is a simple example of a cost-benefit analysis?
Imagine you want to buy a bicycle to go to work instead of using a car or public transportation. The bicycle might cost around $500. If you were to use your car or public transportation, you would have to spend $750 for the whole year. The bicycle will last more than 1 year. When you subtract $500 from $750, you save $250 for the first year, and more to come. Hence, it is a good investment.
What is the difference between CBA and ROI?
Cost-benefit analysis provides a clearer picture than return on investment because ROI only calculates the monetary value, while CBA assigns a price tag to the intangible values as well.
Is NPV a cost-benefit analysis?
Yes, NPV is part of the cost-benefit analysis as it helps in understanding the outcome of the project and making an investment decision. However, cost-benefit analysis contains more calculations than NPV, and NPV alone is not the same as doing the whole analysis.
What is a good cost-benefit ratio?
A cost-benefit ratio above 1.0 is a good cost-benefit ratio. When the ratio is 1.0, it means that the project hits the break-even point. When it’s under 1.0, the project is losing money, and over 1.0 is making a profit.
What is a cost-benefit diagram?
A cost-benefit diagram shows the benefits and the costs of the investment in a graph or chart so that the investors understand the situation of the investment better.
Wrapping Up
In this article, we have learned how to do a cost-benefit analysis in Excel. There were a lot of steps in doing the analysis, but we hope that we were able to make you understand the methods. If you still have questions or confusion about the tutorial, don’t hesitate to drop them in the comment section below, and we will get back to you. See you again in another Excel tutorial.
