When I was looking for financial modeling exercise through the Internet, I found one case from the Association for Financial Professional website. The case involving cost/benefit analysis for two different objects which has their characteristic. The task was to perform excel modeling and create a cost/benefit analysis. case: https://www.afponline.org/ideas-inspiration/topics/articles/Details/recruit-fp-a-staff-with-case-interviews
Using Excel, create a cost/benefit analysis showing the cost of lighting a room with a 60 watt LED versus a 60 watt CFL lightbulb. Assume the LED costs $7, lasts 25,000 hours, and uses 7 watts per hour, while the CFL costs $3, lasts 8,000 hours, and uses 14 watts per hour. The cost per kilowatt hour is $0.50, and the bulb is used 12 hours per day in winter, 10 hours per day in summer. Present your conclusion in PowerPoint.
- In the seasonal model, the first season was assumed to happen in Spring.
- In the yearly model, the first year was assumed in 2019.
- The bulb is used for 11 hours per day in Spring and Fall Season.
These are the results, click the image to open it in full size.
- From the model, we can conclude that it is better to use a 60 watt LED Lightbulb instead of 60 watt CFL to light the entire room. Recurring costs for LED Lightbulb are significantly better (lower) than CFL since LED power consumption is half the CFL. Also, in the long run, the purchase cost of the LED tends to be lower than the purchase cost of CFL. Since these two factors are the main contributors to the Total Cost, we can see that using LED would give us more benefits and lower costs, thus it is more favorable for us to use LED.
- Taking other factors such as bulb durability, we can see that using LED instead of CFL would give us a much lower cost. Since 60 watt LED has longer durability that only depreciated around 4% per season (around 16% a year) while 60 watt CFL depreciated around 12% per season (around 50% a year) makes LED Lightbulb a much better choice.
- The high depreciation rate of CFL Lightbulb urges us to replace the bulb constantly as the book value of the bulb decreased significantly over time. We can see from the model above (Yearly Model Sheet: Bulb Purchase Cost) that if we use CFL, we have to replace it in the second year and replace it again in the fourth year. On the other hand, when we using LED Bulb the first replacement will only occur in the seventh year (2025).
- The only period CFL tend to be more favorable to be chosen is when we only want to light the room for period of one season – that is in the Spring of the first year – this favorable event occurred because Total Cost (Recurring Cost + Bulb Purchase Cost) of LED at that moment is higher than CFL. After that first season, the Total Cost for CFL overtakes Total Cost of LED.
You can download protected excel files of the model above. Inputs (blue colored font) are not protected, so you can change it to see how changes in input flow through the entire model. You can also choose the period time of the yearly model simply by clicking the “time period” dropdown list on the panes, and we can see the changes in time period affect the dynamic model and dynamic chart.
Feel free to comment or contact me whether I am missed something on my works or there is any question regarding this post, any critics and constructive feedback would be appreciated 🙂 Thank You