Develop a two-way data table that shows the calculation of the 30-yr NPV for SIP rates of $1.95,

Individual assignment to be turned in for a grade. Green energy is in the news these days, and
the adoption of solar and wind power, as examples, is being encouraged through various types of tax
credits and other incentives. This case presents realistic data regarding the decision to install solar
power for three bank branches in Los Angeles. A base case spreadsheet is provided for you, so that
you do not have to build that from scratch. Before considering the details of the assignment, study
the spreadsheet to be sure that you understand how it was developed, and discover how the facts in
the case regarding costs, taxes, incentives, efficiencies, and so on were incorporated into the
The top portion of the spreadsheet presents model inputs from the case. Here it is important to pay
attention to the units of measurement (W versus kW). The middle portion presents the year-by-year
economics. An important factor here is accounting for both the accelerated depreciation (MACRS)
and the tax implications of energy savings: without solar panels, energy cost is an expense that
decreases taxable profit, but with solar panels, lower cost means more tax. The case does not present
the details of the profitability and tax obligations per store (and they are not accounted for on the store
level), but a reasonable assumption is that Wells Fargo earns a positive profit larger than its energy
costs. Finally, the bottom part of the exhibit presents the present value calculations for the NPV over
12 yearsthe decision-makers goal, stated in the case, is over 30 years, the stated life-span of the
projectand the payback period without accounting for the time-value of money. Assume the base
case spreadsheet is correct, and do not try to change its logic.
Before beginning the assignment, please watch the video below which provides some additional
background regarding the solar panel decision.
Next, watch the instructional video below that provides an introduction into some Excel tools for
sensitivity analysis, specifically one-way and two-way data tables. You may have to copy and paste
this link into your browser.
There are several uncertainties that might be considered in a sensitivity analysis for Wells Fargo. For
simplicity, focus your analysis on the 30 year NPV (cell B60). Please consider the following in your
1. The SIP rebate rate is estimated to be $1.95/W. What if that rate turns out to be lower? It might
be worth investigating how the NPV of the project would change if the SIP rate goes all the way to 0,
by considering SIP rates of $1.95, $1.50, $1.05, $.75 or no SIP. Develop a one-way data table to
explore these possibilities
2. Develop a two-way data table that shows the calculation of the 30-yr NPV for SIP rates of $1.95,
$1.50, $1.05, $.75 or no SIP, and also for 0%, 10%, and 20% price decreases in the estimated total
system cost per KW.
3. What if the annual degradation in the solar panels increased from 0.50% per year to 1.0% per
year? You could assume some reasonable increments for this change.
4. What if the federal tax credit were to be reduced?
Prepare a two-page discussion of a reasonable and manager friendly sensitivity analysis of this
case. In conducting this analysis, I assume that you will use some one-way and two-way data tables.
Show the most relevant tables in an Appendix to your two-page discussion of the sensitivity of the
analysis to the changes in the input factors identified above.

Latest Assignment