Question

Evaluate the Pear Computer Company proposal.

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%.

Answer 1

BASE CASE:	0	1	2	3	4
Sales in units		25000	95000	70000	25000
Sales revenue at $580/unit		$ 1,45,00,000	$   5,51,00,000	$     4,06,00,000	$     1,45,00,000
Cost of goods sold at 60% of sales		$     87,00,000	$   3,30,60,000	$     2,43,60,000	$         87,00,000
Depreciation (25000000/4)		$     62,50,000	$       62,50,000	$        62,50,000	$         62,50,000
Selling and administrative expenses		$     15,00,000	$       17,50,000	$        10,00,000	$           5,00,000
NOI		$    -19,50,000	$   1,40,40,000	$        89,90,000	$         -9,50,000
Tax at 38%		$      -7,41,000	$       53,35,200	$        34,16,200	$         -3,61,000
NOPAT		$    -12,09,000	$       87,04,800	$        55,73,800	$         -5,89,000
Less: Depreciation		$     62,50,000	$       62,50,000	$        62,50,000	$         62,50,000
OCF		$     50,41,000	$   1,49,54,800	$     1,18,23,800	$         56,61,000
Capital expenditure	$   2,50,00,000
Change in NWC	$       12,50,000	$           10,000	$             10,000	$              10,000	$       -12,80,000
FCF	$ -2,62,50,000	$     50,31,000	$   1,49,44,800	$     1,18,13,800	$         69,41,000
PVIF at 14% [PVIF = 1/1.14^n]	1	0.87719	0.76947	0.67497	0.59208
PV at 14%	$ -2,62,50,000	$     44,13,158	$   1,14,99,538	$        79,73,978	$         41,09,629
NPV	$       17,46,304
As the NPV is positive, the project can be implemented.
BEST CASE:	0	1	2	3	4
Sales in units		25000	95000	70000	25000
Sales revenue at $580/unit		$ 1,45,00,000	$   5,51,00,000	$     4,06,00,000	$     1,45,00,000
Cost of goods sold at 60% of sales		$     87,00,000	$   3,30,60,000	$     2,43,60,000	$         87,00,000
Depreciation (25000000/4)		$     62,50,000	$       62,50,000	$        62,50,000	$         62,50,000
Selling and administrative expenses		$     15,00,000	$       17,50,000	$        10,00,000	$           5,00,000
NOI		$    -19,50,000	$   1,40,40,000	$        89,90,000	$         -9,50,000
Tax at 38%		$      -7,41,000	$       53,35,200	$        34,16,200	$         -3,61,000
NOPAT		$    -12,09,000	$       87,04,800	$        55,73,800	$         -5,89,000
Less: Depreciation		$     62,50,000	$       62,50,000	$        62,50,000	$         62,50,000
OCF		$     50,41,000	$   1,49,54,800	$     1,18,23,800	$         56,61,000
Capital expenditure	$   2,50,00,000
Change in NWC	$       12,50,000	$           10,000	$             10,000	$              10,000	$       -12,80,000
FCF	$ -2,62,50,000	$     50,31,000	$   1,49,44,800	$     1,18,13,800	$         69,41,000
PVIF at 11% [PVIF = 1/1.11^n]	1	0.90090	0.81162	0.73119	0.65873
PV at 11%	$ -2,62,50,000	$     45,32,432	$   1,21,29,535	$        86,38,149	$         45,72,252
NPV	$       36,22,368
As the NPV is positive, the project can be implemented.
WORST CASE:	0	1	2	3	4
Sales in units		25000	95000	70000	25000
Sales revenue at $580/unit		$ 1,45,00,000	$   5,51,00,000	$     4,06,00,000	$     1,45,00,000
Cost of goods sold at 60% of sales		$     87,00,000	$   3,30,60,000	$     2,43,60,000	$         87,00,000
Depreciation (25000000/4)		$     62,50,000	$       62,50,000	$        62,50,000	$         62,50,000
Selling and administrative expenses		$     15,00,000	$       17,50,000	$        10,00,000	$           5,00,000
NOI		$    -19,50,000	$   1,40,40,000	$        89,90,000	$         -9,50,000
Tax at 38%		$      -7,41,000	$       53,35,200	$        34,16,200	$         -3,61,000
NOPAT		$    -12,09,000	$       87,04,800	$        55,73,800	$         -5,89,000
Less: Depreciation		$     62,50,000	$       62,50,000	$        62,50,000	$         62,50,000
OCF		$     50,41,000	$   1,49,54,800	$     1,18,23,800	$         56,61,000
Capital expenditure	$   2,50,00,000
Change in NWC	$       12,50,000	$           10,000	$             10,000	$              10,000	$       -12,80,000
FCF	$ -2,62,50,000	$     50,31,000	$   1,49,44,800	$     1,18,13,800	$         69,41,000
PVIF at 17% [PVIF = 1/1.17^n]	1	0.85470	0.73051	0.62437	0.53365
PV at 17%	$ -2,62,50,000	$     43,00,000	$   1,09,17,379	$        73,76,189	$         37,04,065
NPV	$             47,633