Doing Cost Benefit Analysis in Excel – a case study

Imagine you are the in-charge of finance department at Hogwarts. So one fine day, while you are practicing the spells, Dumbledore walks in to your office and says, “Our electricity bills are way too high. As the muggles don’t accept wizard money, we have to find a way to reduce our power consumption.”

So you summoned the previous 12 month utility bills to examine energy consumption patterns, and pretty soon you realized that most of the electricity consumption is due to the light bulbs. You suddenly have a brilliant idea. Why not replace the light bulbs with a variety that consumes low power? A light bulb moment indeed.

Your next step is to figure out what varieties of light bulbs are out there. Fortunately this is easier than catching a snitch in a game of quidditch. A quick search revealed that there are 3 types of light bulbs:

• Regular incandescent bulbs (the kind Hogwarts currently uses)
• Compact Fluorescent Light bulbs (CFL)
• Light Emitting Diode bulbs (LED)

Now your job is to do a cost benefit analysis of these options and pick one.

What is cost benefit analysis & how to do it?

Cost benefit analysis, as the name suggests is a process of identifying all the costs & benefits of different decision choices and finding which choice offers maximum benefit for minimum cost.

It is a generic technique and the implementation varies depending on situation, industry and available data.

A typical cost benefit analysis involves these steps:

1. Gather all the necessary data
2. Calculate costs
   1. Fixed or one time costs
   2. Variable costs
3. Calculate the benefits
4. Compare costs & benefits over a period of time
5. Decide which option is best for chosen time period
6. Optional: Provide what-if analysis

Let’s conduct cost benefit analysis for our light bulb problem and figure out which option is best.

But first, download the cost benefit analysis workbook

Click here to download the cost benefit analysis workbook. Refer to it as you read this article for best results.

1. Input Data & Assumptions

For each type of bulb we need to find out below information:

Costs:

• Price
• Electricity consumption (watts/hr)
• Life time in hours

Benefits:

• Amount of light (lumens) generated by the bulb

Assumptions:

We also need to assume a few things to keep our cost benefit analysis model realistic & simple.

Some of the assumptions we can go with are,

Global assumptions:

• We need only 1 bulb (doing analysis for n bulbs is just a matter of multiplying 1 bulb results with n)
• Analysis will be conducted in Indian Rupees
• Let’s compare bulbs that give same amount of light (lumens). This means we can ignore the benefit part and focus on costs alone
• This analysis ignores any impact / costs / benefits associated with environmental impact (CO2 emissions, harmful metals like mercury, heat generation etc.)
• Analysis time frame is 5 years.

Other assumptions:

• Average usage of bulb per day is 8 hours.
• Cost of electricity is Rs. 5 per unit (KWH)
• Inflation for electricity cost is 1%

Once we have all the data, tabulate it in Excel like this:

2. Calculate Total Cost of Ownership

Once we have all the necessary data, let’s calculate the total cost of each option (Regular, CFL & LED) over a period of 5 years.

This step involves calculating both fixed & variable costs.

Fixed or one time costs:

The fixed cost for each light bulb type is nothing but the price.

But wait… what about the life time of bulb?

Since each type of bulb has certain life time, we will have to pay for replacement of bulbs too.

This means, apart from fixed cost at the start of time period, we will also have a variable cost that depends on the life time of bulb type.

Variable costs:

There are 2 variable costs in our analysis.

1. Electricity consumption cost
2. Bulb replacement cost

Electricity consumption cost:

This is calculated by below formula:

Wattage per month / 1000 * Inflated unit cost

The actual Excel formula looks like this:

FV($C$13/12,$B20,-$C$14*30*C$8*$C$12/1000)

How this formula works?

To understand this formula, first imagine what the total unit cost should be at the end of Month x.

For first month, the cost is =total monthly usage in hours * watts per hour / 1000 * unit cost * (1 + inflation/12)^1

For second month, the cost is same as above, but the exponent in the end becomes 2.

Let’s say, the blue part of the formula is denoted by something.

Then, the cost at the end of Month X will be,

=something * ( (1+inflation/12)^1 + (1+inflation/12)^2 + … + (1+inflation/12)^X )

Oh, all this math is confusing… Isn’t there a simple spell to answer this?

I am glad you asked. There is a spell to get this answer in one shot. It is called as FV()

The FV formula calculates sum of above series.

We simply write

= -FV(inflation/12, month number, total monthly usage in hours * watts per hour / 1000 * unit cost)

to get the answer we want.

Why the minus sign in front of FV?

This is because, by default FV returns values in negative. It has got something to do with how banks always take money from us, but are very reluctant to give back or like that.

Bulb replacement cost:

The unit cost formula felt like trying to catch a snitch while riding a broomstick upside down. Thankfully, the bulb replacement cost formula feels like sitting in the crowd, cheering match while eating chocolate frogs.

The calculation for bulb replacement goes like this:

Cumulative usage in hours / life time of the bulb * unit price of bulb

Here is the formula for this:

(INT(cumulative usage/ life time of the bulb)+1)*unit price of bulb

Why use INT(…) + 1

Let’s say the life time is 1,000 hrs, cost is Rs 20 and we use 240 hrs in first month. Our cost is still Rs. 20, not 24% of 20.

Likewise, at the end of 5th month, our total usage would be 1200 hrs (240 x 5 = 1200) and we must buy a new bulb as the life time is only 1000 hrs.

The replacement cost is not uniformly spread across months (or hours). It happens once at beginning and then recurs once per lifetime of the bulb.

Hence we use INT to round the cumulative usage / life time to the integer portion (ex: INT(240/1000) will be 0) and add 1 to it as there is initial cost.

Explanation of these formulas in our spreadsheet

Look at below illustration to understand how these formulas look in the cost benefit analysis worksheet.

3. Calculate benefits

This part is not required for our problem as the benefits are same for all 3 types of bulbs. You can use logic similar to cost calculation when the benefits vary. For example if you want to do cost benefit analysis of 3 types of investment choices – mutual funds, stocks, bank deposits, then you can use below framework:

Costs: 

• Brokerage costs
• Entry costs
• Operating expenses
• Exit costs
• Volatility / risk factors

Benefits:

• Return of the investment
• Liquidity benefit

4. Compare costs & benefits over a period of time

Once we have these cost & benefit calculations ready, we need to calculate them for 60 months (5 years) for all 3 types of bulbs.

The resultant table looks something like this:

5. Decide the winner

Once we have all the numbers, it is just a matter of picking the winner. If you are comparing costs, pick the lowest cost item. If you are comparing benefits, pick the item that offers most benefit / cost ratio.

How to convince your boss about the decision?

While it is easy to decide which option is the winner by just looking at numbers, when you take this proposal back to Dumbledore, he may want a little more explanation. This is where a visualization of cost benefit analysis can help.

Example – Visualization of Cost benefit analysis

Here is a completed visualization of our light bulb cost benefit analysis.

How to create this chart?

Simple, just follow below steps.

1. Select the total cost of ownership table that you calculated
2. Insert a new line chart
3. Format the chart as per your (or your boss’) taste
4. Done

Things to keep in mind:

• Go with line charts if your analysis is against a time period or similar
• Go with column / bar charts if you are comparing various options with one time costs only
• Format the chart so that it is easy to identify winning choice at each point of time.

Adding what-if analysis

What if analysis is a great way give power to your decision makers. When you prepare a cost benefit analysis model like above, you will always hear questions like:

• So what would be the total cost for using 12 CFL bulbs 16 hours per day for next 25 years?
• In above case, how much would we save if we switch to LED bulbs?
• What would be the cost for 30 years? 10 hours a day? 20 bulbs?

Thanks to powerful Excel form control feature, you can easily add a comprehensive what-if analysis tool to your model.

Like this:

Process for adding what-if analysis

Follow below process for including what-if analysis in your spreadsheet models.

1. Identify all possible scenarios for which what-if analysis may be required.
2. Determine the variables (in our case, the variables are number of bulbs, bulb type, usage in hours & years)
3. Figure out what output should be displayed (in our case, the output is total cost and a comparison with other bulb types)
4. Set up an input area where user can select any combination of variables
5. Use form controls, slicers, data validation, VBA or simple input cells for gathering this data
   Tip: click on those linked words to understand how to set them up
6. Write formulas that link to the user input cells / form controls
7. Display the output (text, charts etc.)

Please examine the download workbook for actual implementation of this.

Guidelines for creating better analysis models

Whenever you are analyzing something like this, please follow below guiding principles for awesome results.

• Do your research first: Identify all factors, inputs, assumptions & facts that impact the decisions. Spend sometime researching before jumping in to Excel.
• Set up separate areas of input, analysis & output: Create separate areas in your workbook to handle inputs, calculations & outputs (such as charts, text). Clearly demarcate them by using different styles or headers for each section.
• Avoid analysis paralysis: Keep your analysis workbooks simple & realistic. Don’t over complicate them with too many inputs or too much calculation. If a certain factor is irrelevant or too complicated to consider for your analysis, ignore it. Example: in our analysis we ignored benefits as they are same for all 3 options. We also ignore environmental impact as it is tricky to calculate.
• Use consistent formulas: Write your formulas in such a way that same pattern is repeated many times. This way you can write once and use the power of relative & absolute references in Excel to fill the formulas everywhere.
• Let users play with your model: Include what-if analysis or form control based interaction in your workbooks so that your users can play with the model and get answers for their questions.
• Visual explanations: Visual explanations like charts, dashboards are very powerful & memorable. So depict your results visually as much as possible. Remember the old adage, a picture is worth thousand words. 

Related: Spreadsheet modeling best practices for analysts

Download Cost Benefit Analysis workbook

Please click here to download cost benefit analysis workbook. Examine the calculations & form controls to learn more.

Cost benefit analysis – your experiences please…

Cost benefit analysis was a big part of my work when I worked as an analyst. Now I am in the role of a CEO and it is even more relevant for me. For most situations I create a simple Excel model to examine the costs & benefits to decide the winner.

What about you? How do you analyze costs vs benefits? What techniques do you use? Please share your stories, examples & tips in the comments section.

Want more? Introducing 50 ways to analyze data course

If you like to learn how to analyze data, gather insights, prepare outputs & interpret results, then you will love my new course – 50 ways to analyze data.

The above example is lesson number 24 in our course. Each of the 50 lessons deal with common business analysis situations, case studies & techniques so that you can become the analytical wizard you always wanted to.

This course is now open.

If you want to know more about it, please click here.