Question

neducation.com/flow/connecthtml k6 i Saved Emperor's Clothes Fashions can invest $5 million in a new plant for producing invisible makeup. The plant has an expected life of 5 years, and expected sales are 6 million jars of makeup a year. Fixed costs are $3.8 million a year, and variable costs are $2.50 per jar. The product will be priced at $3.90 per jar. The plant will be depreciated straight-line over 5 years to a salvage value of zero. The opportunity cost of capital is 10%, and the tax rate is 30%. a. What is project NPV under these base-case assumptions? (Do not round intermediate calculations. Enter your answer in millions rounded to 2 decimal places.) b. What is NPV if variable costs turn out to be $2.80 per jar? (Do not round intermediate calculations. Enter your answer in millions rounded to 2 decimal places.) c. What is NPV if fixed costs turn out to be $3.3 million per year? (Do not round intermediate calculations. Enter your answer in millions rounded to 2 decimal places.) d. At what price per jar would project NPV equal zero? (Enter your answer in dollars not in millions. Do not round intermediate calculations. Round your answer to 2 decimal places.) million million NPV b. NPV c. NPV d. Price million perjar

Answer 1

Operating cash flow (OCF) each year = income after tax + depreciation

NPV is calculated using NPV function in Excel

a]

NPV is $8.34 million

B C D G E 3 F 4 0 1 2 5 2 Initial Investment 3 Cost of plant $5,000,000 5 OCF 6 Jars sold 7 Revenues 8 - Variable Costs 9 - Fixed Costs 10 - Depreciation 11 Income before tax 12 - Taxes 13 Income after tax 14 + Depreciation 15 OCF 16 17 NPV Analysis 18 Project Cash Flows 19 20 NPV 21. 6,000,000 $23,400,000 $15,000,000 $3,800,000 $1,000,000 $3,600,000 $1,080,000 $2,520,000 $1,000,000 $3,520,000 6,000,000 $23,400,000 $15,000,000 $3,800,000 $1,000,000 $3,600,000 $1,080,000 $2,520,000 $1,000,000 $3,520,000 6,000,000 6,000,000 $23,400,000 $23,400,000 $15,000,000 $15,000,000 $3,800,000 $3,800,000 $1,000,000 $1,000,000 $3,600,000 $3,600,000 $1,080,000 $1,080,000 $2,520,000 $2,520,000 $1,000,000 $1,000,000 $3,520,000 $3,520,000 6,000,000 $23,400,000 $15,000,000 $3,800,000 $1,000,000 $3,600,000 $1,080,000 $2,520,000 $1,000,000 $3,520,000 ($5,000,000) $3,520,000 $3,520,000 $3,520,000 $3,520,000 $3,520,000 $8,343,569

2 Initial Investment 3 Cost of plant 5000000 A 5 OCF 6 Jars sold 7 Revenues 8 - Variable Costs 9 - Fixed Costs 10 - Depreciation 11 Income before tax 12 - Taxes 13 Income after tax 14 + Depreciation 15 OCF 16 17 NPV Analysis 18 Project Cash Flows 19 20 NPV 6000000 6000000 6000000 6000000 6000000 =C6*3.9 =D6*3.9 =E6*3.9 =F6*3.9 =G6*3.9 =C6*2.5 =D6*2.5 =E6*2.5 =F6*2.5 =G6*2.5 3800000 3800000 3800000 3800000 3800000 =$B$3/5 =$B$3/5 =$B$3/5 =$B$3/5 =$B$3/5 =C7-C8-C9-C10 =D7-D8-D9-D10 =E7-E8-E9-E10 =F7-F8-F9-F10 =G7-G8-G9-G10 =C11*30% =D11*30% =E11*30% =F11*30% =G11*30% =C11-C12 =D11-012 =E11-E12 =F11-F12 =G11-G12 =C10 =D10 =E10 =F10 =G10 =C13+C14 =D13+D14 =E13+E14 =F13+F14 =G13+G14 --B3 =C15 =D15 =E15 =F15 =G15 =NPV(10%, C18:18)+B18

b]

NPV is $3.57 million

$5,000,000 2 Initial Investment 3 Cost of plant 4 5 OCF 6 Jars sold 7 Revenues 8 - Variable Costs 9 - Fixed Costs 10 - Depreciation 11 Income before tax 12 - Taxes 13 Income after tax 14 + Depreciation 15 OCF 6,000,000 6,000,000 6,000,000 6,000,000 6,000,000 $23,400,000 $23,400,000 $23,400,000 $23,400,000 $23,400,000 $16,800,000 $16,800,000 $16,800,000 $16,800,000 $16,800,000 $3,800,000 $3,800,000 $3,800,000 $3,800,000 $3,800,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,800,000 $1,800,000 $1,800,000 $1,800,000 $1,800,000 $540,000 $540,000 $540,000 $540,000 $540,000 $1,260,000 $1,260,000 $1,260,000 $1,260,000 $1,260,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $2,260,000 $2,260,000 $2,260,000 $2,260,000 $2,260,000 16 ($5,000,000) $2,260,000 $2,260,000 $2,260,000 $2,260,000 17 NPV Analysis 18 Project Cash Flows 19 20 NPV $2,260,000 $3,567,178 21

5000000 2 Initial Investment 3 Cost of plant 4 5 OCF 6 Jars sold 7 Revenues 8 - Variable Costs 9 - Fixed Costs 10 - Depreciation 11 Income before tax 12 - Taxes 13 Income after tax 14 + Depreciation 15 OCF 6000000 6000000 6000000 6000000 6000000 =C6*3.9 =D6*3.9 =E6*3.9 =F6*3.9 =G6*3.9 =C6*2.8 =D6*2.8 =E6*2.8 =F6*2.8 =G6*2.8 3800000 3800000 3800000 3800000 3800000 =$B$3/5 =$B$3/5 =$B$3/5 =$B$3/5 =$B$3/5 =C7-C8-C9-C10 =D7-D8-D9-D10 =E7-E8-E9-E10 =F7-F8-F9-F10 =G7-G8-G9-G10 =C11*30% =D11*30% =E11*30% =F11*30% =G11*30% =C11-C12 =D11-D12 =E11-E12 =F11-F12 =G11-G12 =C10 =D10 =E10 =F10 =G10 =C13+C14 =D13+D14 =E13+E14 =F13+F14 =G13+G14 16 17 NPV Analysis 18 Project Cash Flows --B3 =C15 =D15 =E15 =F15 =G15 20 NPV =NPV(10%, C18:G18)+B18

c]

NPV is $9.67 million

E 1 2 Initial Investment 3 Cost of plant $5,000,000 4 5 OCF 6 Jars sold 7 Revenues 8 - Variable Costs 9 - Fixed Costs 10 - Depreciation 11 Income before tax 12 - Taxes 13 Income after tax 14 + Depreciation 15 OCF 16 17 NPV Analysis 18 Project Cash Flows 19 20 NPV 21 6,000,000 6,000,000 6,000,000 $23,400,000 $23,400,000 $23,400,000 $15,000,000 $15,000,000 $15,000,000 $3,300,000 $3,300,000 $3,300,000 $1,000,000 $1,000,000 $1,000,000 $4,100,000 $4,100,000 $4,100,000 $1,230,000 $1,230,000 $1,230,000 $2,870,000 $2,870,000 $2,870,000 $1,000,000 $1,000,000 $1,000,000 $3,870,000 $3,870,000 $3,870,000 6,000,000 $23,400,000 $15,000,000 $3,300,000 $1,000,000 $4,100,000 $1,230,000 $2,870,000 $1,000,000 $3,870,000 6,000,000 $23,400,000 $15,000,000 $3,300,000 $1,000,000 $4,100,000 $1,230,000 $2,870,000 $1,000,000 $3,870,000 ($5,000,000) $3,870,000 $3,870,000 $3,870,000 $3,870,000 $3,870,000 $9,670,345

5000000 2 Initial Investment 3 Cost of plant 4 5 OCF 6 Jars sold 7 Revenues 8 - Variable Costs 9 - Fixed Costs 10 - Depreciation 11 Income before tax 12 - Taxes 13 Income after tax 14 + Depreciation 15 OCF 16 17 NPV Analysis 18 Project Cash Flows 19 20 NPV 6000000 6000000 6000000 6000000 6000000 =C6*3.9 =D6*3.9 =E6*3.9 =F6*3.9 =G6*3.9 =C6*2.5 =D6*2.5 =E6*2.5 =F6*2.5 =G6*2.5 3300000 3300000 3300000 3300000 3300000 =$B$3/5 =$B$3/5 =$B$3/5 =$B$3/5 =$B$3/5 =C7-C8-C9-C10 =D7-D8-D9-D10 =E7-E8-E9-E10 =F7-F8-F9-F10 =G7-G8-69-610 =C11*30% =D11*30% =E11*30% =F11*30% =G11*30% =C11-C12 =D11-D12 =E11-E12 =F11-F12 =G11-G12 =C10 =D10 =E10 =F10 =G10 =C13+C14 =D13+D14 =E13+E14 =F13+F14 =G13+G14 --R3 =C15 =D15 =E15 =F15 =G15 =NPV(10%,C18:G18) +B18

d]

Price per jar at which NPV is zero is calculated using GoalSeek in Excel

B $3.90 c o E F G I 1 Price per jar 2 3 Initial Investment 4 Cost of plant $5,000,000 6 OCF 7 Jars sold 8 Revenues 9 - Variable Costs 10 - Fixed Costs 11 - Depreciation 12 Income before tax 13 - Taxes 14 Income after tax 15 + Depreciation 16 OCF 6,000,000 6,000,000 6,000,000 6,000,000 6,000,000 $23,400,000 $23,400,000 $23,400,000 $23,400,000 $23,400,000 $15,000,000 $15,000,000 $15,000,000 $15,000,000 $15,000,000 $3,800,000 $3,800,000 $3,800,000 $3,800,000 $3,800,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $3,600,000 $3,600,000 $3,600,000 $3,600,000 $3,600,000 $1,080,000 $1,080,000 $1,080,000 $1,080,000 $1,080,000 $2,520,000 $2,520,000 $2,520,000 $2,520,000 $2,520,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $3,520,000 $3,520,000 $3,520,000 $3,520,000 $3,520,000 17 18 NPV Analysis 19 Project Cash Flows ($5,000,000) $3,520,000 $3,520,000 $3,520,000 $3,520,000 $3,520,000 20 21 NPV $8,343,569

5000000 1 Price per jar 2 3 Initial Investment 4 Cost of plant 5 6 OCF 7 Jars sold 8 Revenues 9 - Variable Costs 10 - Fixed Costs 11 - Depreciation 12 Income before tax 13 - Taxes 14 Income after tax 15 + Depreciation 16 OCF 17 18 NPV Analysis 19 Project Cash Flows 20 21 NPV 6000000 6000000 6000000 6000000 =C7*$B$1 =D7*$B$1 =E7*$B$1 =F7*$B$1 =C7*2.5 =D7*2.5 =E7*2.5 =F7*2.5 3800000 3800000 3800000 3800000 =$B$4/5 =$B$4/5 =$B$4/5 =$B$4/5 =C8-C9-C10-011 =D8-D9-D10-D11 =E8-E9-E10-E11 =F8-F9-F10-F11 =C12*30% =D12*30% =E12*30% =F12*30% =C12-C13 =D12-D13 =E12-E13 =F12-F13 =C11 =D11 =E11 =F11 C14+C15 =D14+D15 =E14+E15 =F14+F15 6000000 =G7*$B$1 =G7*2.5 3800000 =$B$4/5 =G8-G9-G10-G11 =G12*30% =G12-G13 =G11 =G14+G15 --B4 =C16 =D16 =E16 =F16 =G16 =NPV(10%,C19:G19) +B19

B $3.90 D E F G H I J . 1 Price per jar 2 3 Initial Investment 4 Cost of plant $5,000,000 Goal Seek 1 6 OCF 7 Jars sold 8 Revenues 9 - Variable Costs 10 - Fixed Costs 11 - Depreciation 12 Income before tax 13 - Taxes 14 Income after tax 15 + Depreciation 16 OCF Set cell: $B$21 To value: By changing cell: SBS1 6,000,000 6,000,000 6,000,000 6,000,000 6,000,000 $23,400,000 $23,400,000 $23,400,000 $23,400,000 $23,400,000 $15,000,000 $15,000,000 $15,000,000 $15,000,000 $15,000,000 $3,800,000 $3,800,000 $3,800,000 $3,800,000 $3,800,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $3,600,000 $3,600,000 $3,600,000 $3,600,000 $3,600,000 $1,080,000 $1,080,000 $1,080,000 $1,080,000 $1,080,000 $2,520,000 $2,520,000 $2,520,000 $2,520,000 $2,520,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $3,520,000 $3,520,000 $3,520,000 $3,520,000 $3,520,000 OK Cancel 18 NPV Analysis 19 Project Cash Flows 20 21 NPV ($5,000,000 $3,520,000 $3,520,000 $3,520,000 $3,520,000 $3,520,000 $8,343,569

Price per jar at which NPV is zero is $3.38

А 1 Price per jar $3.38 0 1 2 3 4 5 3 Initial Investment 4 Cost of plant $5,000,000 6 OCF 7 Jars sold 8 Revenues 9 - Variable Costs 10 - Fixed Costs 11 - Depreciation 12 Income before tax 13 - Taxes 14 Income after tax 15 + Depreciation 16 OCF 6,000,000 6,000,000 6,000,000 6,000,000 6,000,000 $20,255,696 $20,255,696 $20,255,696 $20,255,696 $20,255,696 $15,000,000 $15,000,000 $15,000,000 $15,000,000 $15,000,000 $3,800,000 $3,800,000 $3,800,000 $3,800,000 $3,800,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $455,696 $455,696 $455,696 $455,696 $455,696 $136,709 $136,709 $136,709 $136,709 $136,709 $318,987 $318,987 $318,987 $318,987 $318,987 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,318,987 $1,318,987 $1,318,987 $1,318,987 $1,318,987 ($5,000,000) $1,318,987 $1,318,987 $1,318,987 $1,318,987 $1,318,987 18 NPV Analysis 19 Project Cash Flows 20 21 NPV

3.37594938189851 5000000 1 Price per jar 2 3 Initial Investment 4 Cost of plant 5 6 OCF 7 Jars sold 8 Revenues 9 - Variable Costs 10 - Fixed Costs 11 - Depreciation 12 Income before tax 13 - Taxes 14 Income after tax 15 + Depreciation 16 OCF 17 18 NPV Analysis 19 Project Cash Flows 20 21 NPV 6000000 6000000 6000000 6000000 =C7*$B$1 =D7*$B$1 =E7*$B$1 =F7*$B$1 =C7*2.5 =D7*2.5 =E7*2.5 =F7*2.5 3800000 3800000 3800000 3800000 =$B$4/5 =$B$4/5 =$B$4/5 =$B$4/5 =C8-C9-C10-C11 =D8-D9-D10-D11 =E8-E9-E10-E11 =F8-F9-F10-F11 =C12*30% =D12*30% =E12*30% =F12*30% =C12-C13 =D12-D13 =E12-13 =F12-F13 =C11 =D11 =E11 =F11 C14+C15 =D14+D15 =E14+E15 =F14+F15 6000000 =G7*$B$1 =G7*2.5 3800000 =$B$4/5 =G8-G9-G10-G11 =G12*30% =G12-G13 =G11 =G14+G15 =-B4 =C16 =D16 =E16 =F16 =G16 =NPV(10%,C19:G19)+B19