Question

Intro Consider a project with a 4-year life. The initial cost to set up the project is $100,000. This amount is to be linearly depreciated to zero over the life of the project and there is no salvage value. The required return is 15% and the tax rate is 34%. You've collected the following estimates: Base case Pessimistic Optimistic Unit sales per year (Q) 7,000 5,000 9,000 Price per unit (P) 50 4 0 Variable cost per unit (VC) 20 Fixed costs per year (FC) 30,000 50,000 20.000 60 15

Answer 1

1]

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

Annual cash flow = $127,300

2]

NPV is calculated using NPV function in Excel

NPV is $263,439

2 Initial Investment 3 Initial cost $100,000 5 OCF 6 Unit sales 7 Revenues 8 - Variable Costs 9 - Fixed Costs 10 - Depreciation 11 Income before tax 12 - Taxes 13 Income after tax 14 + Depreciation 15 OCF 7,000 $350,000 $140,000 $30,000 $25,000 $155,000 $52,700 $102,300 $25,000 $127,300 7,000 $350,000 $140,000 $30,000 $25,000 $155,000 $52,700 $102,300 $25,000 $127,300 7,000 $350,000 $140,000 $30,000 $25,000 $155,000 $52,700 $102,300 $25,000 $127,300 7,000 $350,000 $140,000 $30,000 $25,000 $155,000 $52,700 $102,300 $25,000 $127,300 16 17 NPV Analysis 18 Project Cash Flows ($100,000) $127,300 $127,300 $127,300 $127,300 19 20 NPV $263,439

2 Initial Investment 3 Initial cost 100000 5 OCF 6 Unit sales 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 7000 7000 7000 7000 =C6*50 =D6*50 =E6*50 =F6*50 =C6*20 =D6*20 =E6*20 =F6*20 30000 30000 30000 30000 =$B$3/4 =$B$3/4 =$B$3/4 =$B$3/4 =C7-C8-C9-C10 =D7-D8-D9-D10 =E7-E8-E9-E10 =F7-F8-F9-F10 =C11*34% =D11*34% =E11*34% =F11*34% =C11-C12 =D11-D12 =E11-E12 =F11-F12 =C10 =D10 =E10 =F10 =C13+C14 =D13+D14 =E13+E14 =F13+F14 R3 C15 =D15 =E15 =F15 =NPV(15%,C18:F18) +B18

3]

NPV is calculated using NPV function in Excel

NPV is -$122,840

в с D E $100,000 2 Initial Investment 3 Initial cost 4 5 OCF 6 Unit sales 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 5,000 5,000 5,000 5,000 $200,000 $200,000 $200,000 $200,000 $175,000 $175,000 $175,000 $175,000 $50,000 $50,000 $50,000 $50,000 $25,000 $25,000 $25,000 $25,000 ($50,000) ($50,000) ($50,000) ($50,000) ($17,000) ($17,000) ($17,000) ($17,000) ($33,000) ($33,000) ($33,000) ($33,000) $25,000 $25,000 $25,000 $25,000 ($8,000) $8,000) ($8,000) ($8,000) ($100,000) ($8,000) ($8,000) ($8,000) ($8,000) ($122,840)

А 100000 2 Initial Investment 3 Initial cost 4. 5 OCF 6 Unit sales 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 5000 =C6*40 =C6*35 50000 =$B$3/4 =C7-C8-C9-C10 =C11*34% =C11-C12 =C10 =C13+C14 5000 =D6*40 =D6*35 50000 =$B$3/4 =D7-D8-D9-D10 =D11*34% =D11-D12 =D10 =D13+D14 5000 =E6*40 =E6*35 50000 =$B$3/4 =E7-E8-E9-E10 =E11*34% =E11-E12 =E10 =E13+E14 5000 =F6*40 =F6*35 50000 =$B$3/4 =F7-F8-F9-F10 =F11*34% =F11-F12 =F10 =F13+F14 --B3 =C15 =D15 =E15 =F15 =NPV(15%,C18:F18) +B18

4]

NPV is calculated using NPV function in Excel

NPV is $649,717

B E o 11 21 $100,000 2 Initial Investment 3 Initial cost 4 5 OCF 6 Unit sales 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 9,000 9,000 9,000 9,000 $540,000 $540,000 $540,000 $540,000 $135,000 $135,000 $135,000 $135,000 $20,000 $20,000 $20,000 $20,000 $25,000 $25,000 $25,000 $25,000 $360,000 $360,000 $360,000 $360,000 $122,400 $122,400 $122,400 $122,400 $237,600 $237,600 $237,600 $237,600 $25,000 $25,000 $25,000 $25,000 $262,600 $262,600 $262,600 $262,600 ($100,000) $262,600 $262,600 $262,600 $262,600 $649,717

2 Initial Investment 3 Initial cost 100000 5 OCF 6 Unit sales 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 9000 9000 9000 9000 =C6*60 =D6*60 =E6*60 =F6*60 =C6*15 =D6*15 =E6*15 =F6*15 20000 20000 20000 20000 =$B$3/4 =$B$3/4 =$B$3/4 =$B$3/4 =C7-C8-C9-C10 =D7-D8-D9-D10 =E7-E8-E9-E10 =F7-F8-F9-F10 =C11*34% =D11*34% =E11*34% =F11*34% =C11-C12 =D11-D12 =E11-E12 =F11-F12 =C10 =D10 =E10 =F10 =C13+C14 =D13+D14 =E13+E14 =F13+F14 --B3 =C15 =D15 =E15 =F15 =NPV(15%, C18:F18) +B18