The Insurance cost (A43) and Total insurance (A44) for years 1 through 30
Questions for Wells Fargo Case Study (Corrected)
Due July 6, 2021
Please answer the following questions in a Word document and include your completed Excel spreadsheet as indicated on the shaded areas of the spreadsheet. Submit both files in the Assignment folder in Blackboard, Course Content for Week 9.
The response to each question should briefly summarize and/or interpret the results of the spreadsheet for this case.
This case study is 15% of the course grade and has a total of 100 points.
1) (30 points) Calculate the NPV of the solar panel project for both 30 years and 12 years. (These are the cells “NPV of Net Savings (30 yrs)” and “NPV of Net Savings (12 yrs)” in cells B56 and B57 of your spreadsheet.
2) (10 points) Calculate the NPV with Tax Incentives only for 30 years and 12 years in cells B61 and B62 of your spreadsheet. Also examine the NPV with both tax incentives and SIP for 30 years and 12 years, in cells B68 and B69 (the calculations are done for you). Explain your results.
3) (10 points) Calculate the NPVs for 30 years and 12 years, (Cells B56 and B57, respectively) using the Modified Accelerated Cost Recovery System (MACRS) as shown in the table on page 280 of the text. What effect does accelerated depreciation have on the results of the project? (Be sure to change back the depreciation schedule to its original straight-line basis after answering this question).
4) (10 points; each part 5 points)
a) Calculate the payback for the project. (The formula is already in cell B74). Interpret the result.
b) Name two uncertainties that you have about the assumptions used in the NPV analysis and why
they may concern you.
5) (15 points) Break-Even Analysis. Reference pages 263 and 267 of the text, particularly page 267 on using Excel: “Goal Seek for Break-Even Analysis.” Do a What-If analysis where you set the break-even for the project, NPV = 0, based on the levels of three key parameters: the tax credit (cell B19), the SIP rate (cell B14) and Electricity Cost (Cell B21). For example, Goal Seek will tell you the tax credit percentage that would make the NPV = 0. How would you interpret the results? After completing this exercise be sure to set all three parameters back to their original values.
6) (15 points) Sensitivity Analysis. Reference pages 264-65 and 267-68 of the text for an explanation of sensitivity analysis and the Excel functions to perform a sensitivity analysis. Construct a data table in Excel with various SIP rates down the first column and various tax credit percentages across the first row. You may want to have the existing values of these two parameters as the midpoint of your ranges. You will reference the cell address for the current NPV in the top left corner of your data table as the starting point. The completed table will show the NPVs resulting from changes in these two key parameters. Interpret your results.
7) (10 points) Based on your results in questions 1) through 6), would you proceed with the project? What are your concerns? Do you think that you should assign probabilities to values of key parameters to get an expected NPV, based on your results in the sensitivity table?
References to NPV function in Excel in Text:
“Capital Budgeting Using Excel,” Chapter 8, page 255
“Calculating NPV,” Chapter 4, page 114
“Excel’s NPV Function,” Chapter 4, page 115
Also see video in Course Content folder for Week 7: “Video on Wells Fargo Case”
Article in Course Content for Week 7: “Solar Panel Projects: Does It Pay to Play?” Journal of Property Management, Nov./Dec. 2012.