Question

1) Use an Excel spreadsheet to evaluate the Pear Computer Company proposal (info below).
2) Conduct a sensitivity analysis that focuses on the cost of capital. For a best case scenario, decrease the cost of capital by three percentage points. For a worst case scenario, increase the cost of capital by three percentage points.
3) You must provide one spreadsheet for each of the three situations—the base case estimate, the best case, and the worst case.
4) What do you recommend? Explain. You may type your recommendation and explanation on the Excel sheet.

Pear Computer’s research and development (R&D) department has developed a proposal for a new generation of tablet-sized computers.
1. Project’s useful life: 4 years.
2. Capital expenditures: $25,000,000.
3. Depreciation: straight-line over 4 years.
4. Sales: 25000 units in year 1, 95,000 in year 2, 70,000 in year 3, 25,000 in year 4. The sales price is expected to remain constant at $580.
5. Cost of goods sold (not counting depreciation): 60% of sales.
6. Selling, general and administrative expenses: $1,500,000 the first year, $1,750,000 the second year, $1,000,000 the third, and $500,000 the 4th.
7. R&D: $1,500,000 spent one year ago.
8. Initial investment in net working capital: $1,250,000. Then it increases by $10,000 for each of three years and finally is fully recovered in the final year.
9. Tax rate: 38%.
10. Cost of Capital: 14%.
MAXIMIZE THE USE OF FORMULAS!

Answer 1

This is the formulas sheet that I have used in the analysis.

The BASE CASE Result is this. I show you how to create scenarios. Go to Data>What If Analysis> Scenario Manager

Now Add your Scenario by changing cell is the Cost of Capital. Everything is link you just need to change Cost of Capital in Cell C6

Then enter new cost of capital. I had entered 0.11 for best case and 0.17 for worst. Click show to see the magic.

You can create a summary report like this by clicking on summary.

ALSO SHARING THE OTHER TWO SCENARIOS WITH YOU.

THIS IS A BONUS LOOK DOWN.