We walk through the Salmeron Solar Leasing Case with a possible solution to the problem of lease pricing. Ultimately we arrive at a model with multi-period objective involving net present value (done the old-fashioned way which mean not with =NPV) with a one-period lease price decision. Plrice sensitive demand for leased components, assumptions, all aimed at a sensitivity analysis of the impact of price on net present value. A graph of the price-npv simulation (equally probable price scenarios -- not a bad start for a prior distribution) and "optimal" price-npv completes the first part of the analytical task. The second part is much harder! Interpretation of results is now left to the analyst to ruminate over ... and over, especially decision maker questions which cannot be answered through this model!
We continue to use named ranges, formatting tables for clarity, documenting functions with =FORMULATEXT, modular tasks in separate worksheets, building dynamic charts with a chart set-up worksheet, allowing user control of the price grid (begs for a slide-bar!) and, of course, =INDEX and =MATCH for an important lookup of paired values in the price-npv simulation.
An exercise would be to perform a two-way data table of the joint impact of price and some other assumption, say, price inflation to generate a surface of npv outcomes. Perhaps it might be useful to format this two-dimensional grid as a contour or even a heat map.
7 сен 2024
