Question

The most likely outcomes for a particular project are estimated as follows: points Unit price: Variable cost: Fixed cost: Expected sales: $ 50 $ 30 $420,000 41,000 units per year ebook Print However, you recognize that some of these estimates are subject to error. Suppose that each variable may turn out to be either 10% higher or 10% lower than the initial estimate. The project will last for 10 years and requires an initial investment of $1.2 million, which will be depreciated straight-line over the project life to a final value of zero. The firm's tax rate is 21% and the required rate of return is 12%. References (For all the requirements, a negative amount should be indicated by a minus sign. Enter your answer in dollars not in millions. Do not round intermediate calculations. Round your answer to the nearest dollar amount.) a. What is project NPV in the best-case scenario, that is, assuming all variables take on the best possible value? b. What is project NPV in the worst-case scenario? NPV b. NPV

Answer 1

a:

NPV	2891846.32

Workings

Best case
Sales units	45100
Price	55
variable cost	27
Fixed cost	378000
Total sales	2480500
Total cost	1595700

Year	Cost of new machine	Tax shield- depreciation	(Sales-cost) after tax	Net CF
0	-1200000			-1200000
1		25200	698992	724192
2		25200	698992	724192
3		25200	698992	724192
4		25200	698992	724192
5		25200	698992	724192
6		25200	698992	724192
7		25200	698992	724192
8		25200	698992	724192
9		25200	698992	724192
10		25200	698992	724192

AutoSaver HD Book2 - Excel Sign in - 0 X File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do Share Comments A = = 0 £:49 0 Calibri -9 BIU - B Font -A A = E Paste La- Insert - Delete - Format - General 3$ -% Number , Conditional Format as Formatting Table - Cell Styles Sort & Find & Filter - Select Editing Clipboard Alignment Styles Cells AZ - A HA Year Net Cr Cost of new machine |-1200000 Tax shield depreciation (Sales cost) after tax NmNOEN Best case Sales units Price variable cost Fixed cost Total sales Total cost = 41000 110% =50*110% =30*90% =420000*90% =G2 G3 -G2G4+G5 = 1200000*21%/10 = 1200000*21%/10 = 1200000*21%/10 = 1200000*21%/10 = 1200000*21%/10 -1200000*21%/10 =1200000*21%/10 -1200000*21%/10 =1200000*21%/10 =1200000*21%/10 =($G$6-$G$7)*(1-21%) =(SG$6-$G$7)*(1-21%) =($G$6-$G$7)*(1-21%) =(SG$6-$G$7)*(1-21%) =(SG$6-SG$7)*(1-21%) -(SG$6-SG$7)*(1-21%) =(SG$6 $G$7)*(1-21%) F($G$6 $G$7)*(1 21%) F($G$6 $G$7)*(1-21%) =($G$6-$G$7)*(1-21%) NPV =SUM(B2:D2) =SUM(B3:03) =SUM(B4:04) =SUM(B5:05) =SUM(B6:06) =SUM(B7:07) SUM(B8:08) SUM(B9:09) SUM(B10:010) ESUM(B11:011) =SUM(B12:012) =NPV(12%,F3:E12)+F2 13 16 17 18 10 Sheet1 Ready ^ ! 9 d ENG 4:58 AM 0

b:

NPV	-1143316.96

Worst cae
Sales units	36900
Price	45
variable cost	33
Fixed cost	462000
Total sales	1660500
Total cost	1679700

Year	Cost of new machine	Tax shield- depreciation	(Sales-cost) after tax	Net CF
0	-1200000			-1200000
1		25200	-15168	10032
2		25200	-15168	10032
3		25200	-15168	10032
4		25200	-15168	10032
5		25200	-15168	10032
6		25200	-15168	10032
7		25200	-15168	10032
8		25200	-15168	10032
9		25200	-15168	10032
10		25200	-15168	10032
			NPV	-1143316.96

AutoSave © of CHO- Book2 - Excel Sign in - o File Home Insert Draw Page Layout Formulas Data Review View Help O Tell me what you want to do - Calibri = = -9 BI U - - A À - A - Insert Delete - Format Share Comme A 47 0 Sort & Find & Filter - Select = det ab General E 3- $ - % 9 OSOS Conditional Format as Formatting Table Alignment to Number ol Styles T- Paste Cell Styles Clipboard Font Cells Editing X ✓ fx Cost of new B1 - A B Year Cost of new machine Ен Net CF Tax shield- depreciation (Sales-cost) after tax 1 20-1200000 3 1 4 Worst cae Sales units Price variable cost Fixed cost Total sales Total cost =41000*90% =50*90% =30*110% = 420000*110% =J2*J3 =J2*J4+J5 6 7 8 6 97 10 8 11 9 12 10 13 =1200000*21%/10 = 1200000*21%/10 =1200000*21%/10 =1200000*21%/10 =1200000*21%/10 =1200000*21%/10 =1200000*21%/10 =1200000*21%/10 =1200000*21%/10 =1200000*21%/10 =($1$6-$J$7)*(1-21%) = ($I$6-$J$ 7)*(1-21%) =($I$6-$J$7)*(1-21%) =($1$6-$J$7)*(1-21%) =($I$6-$J$7)*(1-21%) =($I$6-$J$7)*(1-21%) = ($I$6-$J$7)*(1-21%) =($1$6-$J$7)*(1-21%) =($I$6-$J$7)*(1-21%) =($1$6-$J$7)*(1-21%) NPV =SUM(B2:D2) SUM(B3:03) =SUM(B4:04) =SUM(B5:05) SUM(B6:06) SUM(B7:07) =SUM(B8:08) =SUM(B9:09) SUM(B10:010) =SUM(B11:011) =SUM(B12:012) =NPV(12%,E3:E12)+E2 - 14 15 16 17