Year 0 1 2 3 4 5 EBIT Taxes NOPAT Plus: Depreciation Less: CAPEX Less: Net working capital needs (See Note 1) Plus: Salvage value of the fixed assets in year 5 Firm Free Cash Flow (FFCF) Net Fixed Assets (beginning of the year) Plus: CAPEX Less: Depreciation Expense for the Year Net Fixed Assets (end of the year) How come the complete assignment is not showing
Problem 22 PROBLEM 2-2 Given Growth rate for years 1-5 EBIT (1) CAPEX for year 0 CAPEX for years 1-5 Depreciation Expense in year 0 Tax rate Debt Retirements for years 1-5 New borrowing for years 1-5 New working capital for years 1-5 Interest rate on debt (all years) Debt Outstanding (0) Solution Legend 5% $100,000 $400,000 $20,000 per year over and above annual depreciation expense $80,000 30% $15,000 per year $10,000 per year 20% of new EBIT 10% $120,000 = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Solution 1 $115,000 Debt financing (End of Year) Interest Expense (based on EOY balance) 11,500 0 EBIT Less: Interest expense Earnings before tax Taxes Net Income Plus: Depreciation Less: CAPEX Plus: Salvage Value of fixed assets = Net fixed assets at the end of year 5 (see Problem 2.1) Less: New working capital needs Less: Principal repayments Plus: New debt issues Equity Free Cash Flow (EFCF) (400,000) (20,000) 120,000 $(300,000) 1 $100,000 (11,500) 88,500 (26,550) $61,950 80,000 (100,000) (1,000) (15,000) 10,000 $35,950 Year 2 $110,000 11,000 Year 2 $105,000 (11,000) 94,000 (28,200) $65,800 100,000 (120,000) (1,050) (15,000) 10,000 $39,750 3 $105,000 4 $100,000 10,500 10,000 9,500 3 $110,250 (10,500) 99,750 (29,925) $69,825 124,000 (144,000) 4 $115,763 (10,000) 105,763 (31,729) $74,034 152,800 (172,800) 5 $121,551 (9,500) 112,051 (33,615) $78,435 187,360 (207,360) 400,000 (1,103) (15,000) 10,000 $43,723 (1,158) (15,000) 10,000 $47,876 24,310 (15,000) 10,000 $477,746 Page 1 5 $95,000 PROBLEM 2-2 Given a. Initial investment Annual Cash Flows Project Life $ 4,000,000.00 500,000.00 10 Solution: IRR = 4.28% Given b. Year Solution: IRR = Cash Flow 0 $ (4,000,000.00) 1 500,000.00 2 500,000.00 3 500,000.00 4 500,000.00 5 300,000.00 6 500,000.00 7 500,000.00 8 500,000.00 9 500,000.00 10 500,000.00 11 500,000.00 12 500,000.00 13 500,000.00 14 500,000.00 Solution Legend = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output PROBLEM 2-3 Given Growth rate in EBIT for years 1-5 EBIT (year 1) CAPEX for year 0 Depreciable life of fixed assets Tax rate New working capital for years 1-5 $ $ Solution Legend = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output 5% 100,000 400,000 5 years 30% 20% of EBIT Solution Year 0 EBIT Taxes NOPAT Plus: Depreciation Less: CAPEX Less: Net working capital needs (See Note 1) Plus: Salvage value of the fixed assets in year 5 Firm Free Cash Flow (FFCF) Net Fixed Assets (beginning of the year) Plus: CAPEX Less: Depreciation Expense for the Year Net Fixed Assets (end of the year) 1 2 3 4 5 Note 1: At the end of year 5 the firm liquidates all of it's investment in net operating working capital. PROBLEM 2-7 Given Incremental revenue growth for years 1-5 Revenues (current) Incremental Revenues CAPEX for year 0 CAPEX for years 1-5 Maintenance expense for years 1-5 Depreciable life of ovens Depreciation Expense Tax rate Discount Rate Solution Legend $ $ $ $ $ 10.0% 9,000,000 900,000 1,860,000 120,000 per year 5 years = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output 30% 9% Solution 0 Incremental Revenues Incremental Depreciation Incremental maintenance expense Incremental EBIT Taxes NOPAT Plus: Depreciation Less: CAPEX Less: New working capital needs Project Free Cash Flow (PFCF) Net Present Value Internal Rate of Return $ - 1 900,000 $ - Year 2 900,000 $ - 3 900,000 $ - 4 900,000 $ - 5 900,000 - PROBLEM 2-10 Given Cost of Tester (Year 0) Installation and training costs CAPEX (Year 5) Annual cost savings Salvage value Depreciation Project Life Tax rate Cost of Capital Solution Legend $ 250,000 $ 10,000 $ 100,000 $ 70,000 $ 5,000 Straight Line 10 years 30% 12% = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Solution a. 0 1 2 3 Year 5 4 6 7 8 9 Investment Outlays Equipment purchases Installation costs Initial Outlay After-tax salvage value Free Cash Flows Operating Expense Savings Less: Depreciation Expense Additional Operating Income Less: Taxes NOPAT Plus: Depreciation Less: CAPEX Free Cash Flow b. Net Present Value Internal Rate of Return NPV Profile Discount Rates NPV 0% 2% 4% 6% 8% 10% 12% 14% 16% 18% 20% 22% 24% 26% 28% 30% 32% 34% 36% 38% 40% NPV Profile 12 Net Present Value 10 8 6 4 2 0% 5% 10% 15% 20% 25% Discount Rat es 30% 35% 4 0% 4 5% 10 Problem 3-1 Solution Legend Given Initial cost of equipment Project and equipment life Salvage value of equipment Working capital requirement Depreciation method Depreciation expense Discount rate Tax rate Base case Enter the given values from the text book here Unit sales Price per unit Variable cost per unit Fixed costs $ $ $ $1,000,000.00 10 0 0 Straight-Line $100,000.00 10.00% 34.00% Worst case 9000 $112.50 $82.50 $275,000.00 10,000 125.00 75.00 250,000.00 Best Case Solution Revenues Variable cost Fixed Expenses Gross profit Depreciation Net operating income Income tax expense Net income Cash flow NPV Expected Case Solution Revenues Variable cost Fixed Expenses Gross profit Depreciation Net operating income Income tax expense NOPAT plus: Depreciation less: CAPEX less: Working capital investment Free cash flow NPV Worst Case Revenues Variable cost Fixed Expenses Gross profit Depreciation Net operating income Income tax expense Net income Cash flow NPV=PV(E12,E7,D50)-E6 NPV Assuming the negative tax credit obtained here can used somewhere else or carried forward Solution Enter the given values from the text book here Enter the given values from the text book here Best Case 11000 $137.50 $67.50 $225,000.00 = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output PROBLEM 3-4: Clayton Manufacturing Company Given EBITDA (Year 1) Growth Rate in EBITDA Initial investment Depreciation (Straight line) over Estimated salvage value Tax rate Cost of capital Solution Legend $ 200,000 5% 800,000 5 years 35% 12% $ $ = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Solution Years a. EBITDA Less: Depreciation Expense EBIT Less: Taxes NOPAT Plus: Depreciation Expense Less: CAPEX Less: Change in Working Capital Project FCF 0 1 $ 2 200,000 b. NPV c. Using "Goal Seek" to solve for the EBITDA in year 1 (C5) that yields a NPV of 0 (C28). Breakeven Year 1 EBITDA 3 4 5 PROBLEM 3-4: TitMar Motor Company Given Assumptions and Predictions Price per unit Market share (%) Market size (Year 1) Growth rate in market size beginning in Year 2 Unit variable cost Fixed cost Tax rate Cost of capital Investment in NWC Initial investment in PP&E Depreciation (5 year life wo salvage) Solution Legend = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Estimates $ Part b. Substitute $4,500 for the price per unit. Part a. Substitute 5% for market share (%) . 4,895 15.00% 200,000 units 5.00% 4,250 9,000,000 50.00% 18.00% $ $ $ of the predicted change in firm 5.00% revenues. 7,000,000 1,400,000 $ $ Solution Year 0 1 2 3 4 Investment Revenue Variable Cost Fixed cost Depreciation EBT(Net Operating Income) Tax Net Operating Profit after Tax (NOPAT) Plus: Depreciation expense Less: Capex Less: Change in NWC Free Cash Flow Net Present Value Internal Rate of Return Units Sold a. If the market share is only 5% then the project's NPV = b. If market share = 15% and the price of the PTV falls to $4,500 the NPV = Breakeven Sensitivity Analysis Price per unit Market share (%) Market size (Year 1) Growth rate in market size beginning in Year 2 Unit variable cost Fixed cost Tax rate Cost of capital Investment in NWC Analysis: Critical % Change Critical Value The above analysis suggests that the two key value drivers are price per unit and unit variable cost! 5 PROBLEM 2-2 Given a. Initial investment Annual Cash Flows Project Life $ 4,000,000.00 500,000.00 10 Solution: IRR = 4.28% Given b. Year Solution: IRR = Cash Flow 0 $ (4,000,000.00) 1 500,000.00 2 500,000.00 3 500,000.00 4 500,000.00 5 300,000.00 6 500,000.00 7 500,000.00 8 500,000.00 9 500,000.00 10 500,000.00 11 500,000.00 12 500,000.00 13 500,000.00 14 500,000.00 Solution Legend = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output PROBLEM 2-3 Given Growth rate in EBIT for years 1-5 EBIT (year 1) CAPEX for year 0 Depreciable life of fixed assets Tax rate New working capital for years 1-5 $ $ Solution Legend = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output 5% 100,000 400,000 5 years 30% 20% of EBIT Solution Year 0 EBIT Taxes NOPAT Plus: Depreciation Less: CAPEX Less: Net working capital needs (See Note 1) Plus: Salvage value of the fixed assets in year 5 Firm Free Cash Flow (FFCF) Net Fixed Assets (beginning of the year) Plus: CAPEX Less: Depreciation Expense for the Year Net Fixed Assets (end of the year) 1 2 3 4 5 Note 1: At the end of year 5 the firm liquidates all of it's investment in net operating working capital. PROBLEM 2-7 Given Incremental revenue growth for years 1-5 Revenues (current) Incremental Revenues CAPEX for year 0 CAPEX for years 1-5 Maintenance expense for years 1-5 Depreciable life of ovens Depreciation Expense Tax rate Discount Rate Solution Legend $ $ $ $ $ 10.0% 9,000,000 900,000 1,860,000 120,000 per year 5 years = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output 30% 9% Solution 0 Incremental Revenues Incremental Depreciation Incremental maintenance expense Incremental EBIT Taxes NOPAT Plus: Depreciation Less: CAPEX Less: New working capital needs Project Free Cash Flow (PFCF) Net Present Value Internal Rate of Return $ - 1 900,000 $ - Year 2 900,000 $ - 3 900,000 $ - 4 900,000 $ - 5 900,000 - PROBLEM 2-10 Given Cost of Tester (Year 0) Installation and training costs CAPEX (Year 5) Annual cost savings Salvage value Depreciation Project Life Tax rate Cost of Capital Solution Legend $ 250,000 $ 10,000 $ 100,000 $ 70,000 $ 5,000 Straight Line 10 years 30% 12% = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Solution a. 0 1 2 3 Year 5 4 6 7 8 9 Investment Outlays Equipment purchases Installation costs Initial Outlay After-tax salvage value Free Cash Flows Operating Expense Savings Less: Depreciation Expense Additional Operating Income Less: Taxes NOPAT Plus: Depreciation Less: CAPEX Free Cash Flow b. Net Present Value Internal Rate of Return NPV Profile Discount Rates NPV 0% 2% 4% 6% 8% 10% 12% 14% 16% 18% 20% 22% 24% 26% 28% 30% 32% 34% 36% 38% 40% NPV Profile 12 Net Present Value 10 8 6 4 2 0% 5% 10% 15% 20% 25% Discount Rat es 30% 35% 4 0% 4 5% 10 Problem 3-1 Solution Legend Given Initial cost of equipment Project and equipment life Salvage value of equipment Working capital requirement Depreciation method Depreciation expense Discount rate Tax rate Base case Enter the given values from the text book here Unit sales Price per unit Variable cost per unit Fixed costs $ $ $ $1,000,000.00 10 0 0 Straight-Line $100,000.00 10.00% 34.00% Worst case 9000 $112.50 $82.50 $275,000.00 10,000 125.00 75.00 250,000.00 Best Case Solution Revenues Variable cost Fixed Expenses Gross profit Depreciation Net operating income Income tax expense Net income Cash flow NPV Expected Case Solution Revenues Variable cost Fixed Expenses Gross profit Depreciation Net operating income Income tax expense NOPAT plus: Depreciation less: CAPEX less: Working capital investment Free cash flow NPV Worst Case Revenues Variable cost Fixed Expenses Gross profit Depreciation Net operating income Income tax expense Net income Cash flow NPV=PV(E12,E7,D50)-E6 NPV Assuming the negative tax credit obtained here can used somewhere else or carried forward Solution Enter the given values from the text book here Enter the given values from the text book here Best Case 11000 $137.50 $67.50 $225,000.00 = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output PROBLEM 3-4: Clayton Manufacturing Company Given EBITDA (Year 1) Growth Rate in EBITDA Initial investment Depreciation (Straight line) over Estimated salvage value Tax rate Cost of capital Solution Legend $ 200,000 5% 800,000 5 years 35% 12% $ $ = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Solution Years a. EBITDA Less: Depreciation Expense EBIT Less: Taxes NOPAT Plus: Depreciation Expense Less: CAPEX Less: Change in Working Capital Project FCF 0 1 $ 2 200,000 b. NPV c. Using "Goal Seek" to solve for the EBITDA in year 1 (C5) that yields a NPV of 0 (C28). Breakeven Year 1 EBITDA 3 4 5 PROBLEM 3-4: TitMar Motor Company Given Assumptions and Predictions Price per unit Market share (%) Market size (Year 1) Growth rate in market size beginning in Year 2 Unit variable cost Fixed cost Tax rate Cost of capital Investment in NWC Initial investment in PP&E Depreciation (5 year life wo salvage) Solution Legend = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Estimates $ Part b. Substitute $4,500 for the price per unit. Part a. Substitute 5% for market share (%) . 4,895 15.00% 200,000 units 5.00% 4,250 9,000,000 50.00% 18.00% $ $ $ of the predicted change in firm 5.00% revenues. 7,000,000 1,400,000 $ $ Solution Year 0 1 2 3 4 Investment Revenue Variable Cost Fixed cost Depreciation EBT(Net Operating Income) Tax Net Operating Profit after Tax (NOPAT) Plus: Depreciation expense Less: Capex Less: Change in NWC Free Cash Flow Net Present Value Internal Rate of Return Units Sold a. If the market share is only 5% then the project's NPV = b. If market share = 15% and the price of the PTV falls to $4,500 the NPV = Breakeven Sensitivity Analysis Price per unit Market share (%) Market size (Year 1) Growth rate in market size beginning in Year 2 Unit variable cost Fixed cost Tax rate Cost of capital Investment in NWC Analysis: Critical % Change Critical Value The above analysis suggests that the two key value drivers are price per unit and unit variable cost! 5 PROBLEM 2-2 a. Initial investment Annual Cash Flows Project Life IRR = Given $4,000,000.00 500,000.00 10 Solution: 4.28% Given b. IRR = Year Solution: Cash Flow 0 $(4,000,000.00) 1 500,000.00 2 500,000.00 3 500,000.00 4 500,000.00 5 300,000.00 6 500,000.00 7 500,000.00 8 500,000.00 9 500,000.00 10 500,000.00 11 500,000.00 12 500,000.00 13 500,000.00 14 500,000.00 Solution Legend = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Problem 23 PROBLEM 2-3 Given Growth rate in EBIT for years 1-5 EBIT (year 1) CAPEX for year 0 Depreciable life of fixed assets Tax rate New working capital for years 1-5 Solution Legend = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output 5% $100,000 $400,000 5 years 30% 20% of EBIT Solution Year 0 EBIT Taxes NOPAT Plus: Depreciation Less: CAPEX Less: Net working capital needs (See Note 1) Plus: Salvage value of the fixed assets in year 5 Firm Free Cash Flow (FFCF) Net Fixed Assets (beginning of the year) Plus: CAPEX Less: Depreciation Expense for the Year Net Fixed Assets (end of the year) 1 2 3 4 5 (400,000) (20,000) $100,000 30,000 $70,000 160,000 (80,000) (1,000) $105,000 31,500 $73,500 160,000 (80,000) (1,050) $110,250 33,075 $77,175 160,000 (80,000) (1,103) $115,763 34,729 $81,034 160,000 (80,000) (1,158) $121,551 36,465 $85,085 85,085 (80,000) 24,310 $(420,000) $149,000 $152,450 $156,073 $159,876 $189,396 $400,000 80,000 (160,000) $320,000 $320,000 80,000 (160,000) $240,000 $240,000 80,000 (160,000) $160,000 $160,000 80,000 (160,000) $80,000 $80,000 80,000 (160,000) $- 400,000 $400,000 Page 2 Note 1: At the end of year 5 the firm liquidates all of it's investment in net operating working capital. PROBLEM 2-7 Given Incremental revenue growth for years 1-5 Revenues (current) Incremental Revenues CAPEX for year 0 CAPEX for years 1-5 Maintenance expense for years 1-5 Depreciable life of ovens Depreciation Expense Tax rate Discount Rate Solution Legend 10.0% $9,000,000 $900,000 $1,860,000 $$120,000 per year 5 years = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output 30% 9% Solution Year 0 Incremental Revenues Incremental Depreciation Incremental maintenance expense Incremental EBIT Taxes NOPAT Plus: Depreciation Less: CAPEX Less: New working capital needs Project Free Cash Flow (PFCF) Net Present Value Internal Rate of Return 1 $900,000 - 2 $900,000 - 3 $900,000 - 4 $900,000 - 5 $900,000 - - Problem 210 PROBLEM 2-10 Given Cost of Tester (Year 0) Installation and training costs CAPEX (Year 5) Annual cost savings Salvage value Depreciation Project Life Tax rate Cost of Capital Solution Legend $250,000 $10,000 $100,000 $70,000 $5,000 Straight Line 10 years 30% 12% = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Solution a. 0 1 2 3 Year 5 4 6 7 8 Investment Outlays Equipment purchases Installation costs Initial Outlay After-tax salvage value Free Cash Flows Operating Expense Savings Less: Depreciation Expense Additional Operating Income Less: Taxes NOPAT Plus: Depreciation Less: CAPEX Free Cash Flow b. Net Present Value Internal Rate of Return NPV Profile Discount Rates NPV 0% 2% 4% 6% 8% 10% 12% 14% 16% 18% 20% 22% 24% 26% 28% 30% 32% 34% 36% 38% 40% NPV Profile 12 Net Present Value 10 8 6 4 2 0 0% Page 4 5% 10% 15% 20% 25% 30% 35% 4 0% 4 5% Discount Rat es 9 10 Problem 3-1 Solution Legend Enter the given values from the text book here Best Case 10,000 $125.00 $75.00 $250,000.00 $1,000,000.00 10 0 0 StraightLine $100,000.00 10.00% 34.00% Enter the given values from the text book here Worst case 9000 $112.50 $82.50 $275,000.00 Initial cost of equipment Project and equipment life Salvage value of equipment Working capital requirement Depreciation method Depreciation expense Discount rate Tax rate Base case Unit sales Price per unit Variable cost per unit Fixed costs Given Best Case 11000 $137.50 $67.50 $225,000.00 Solution Revenues Variable cost Fixed Expenses Gross profit Depreciation Net operating income Income tax expense Net income Cash flow NPV Expected Case Solution Revenues Variable cost Fixed Expenses Gross profit Depreciation Net operating income Income tax expense NOPAT plus: Depreciation less: CAPEX less: Working capital investment Free cash flow NPV Worst Case Revenues Variable cost Fixed Expenses Gross profit Depreciation Net operating income Income tax expense Net income Cash flow NPV=PV(E12,E7,D50)E6 NPV Assuming the negative tax credit obtained here can used somewhere else or carried forward Solution Enter the given values from the text book here = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output PROBLEM 3-4: Clayton Manufacturing Company Given EBITDA (Year 1) Growth Rate in EBITDA Initial investment Depreciation (Straight line) over Estimated salvage value Tax rate Cost of capital Solution Legend $200,000 5% $800,000 5 years $35% 12% = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Solution Years a. EBITDA Less: Depreciation Expense EBIT Less: Taxes NOPAT Plus: Depreciation Expense Less: CAPEX Less: Change in Working Capital Project FCF 0 1 $200,000 2 b. NPV c. Using "Goal Seek" to solve for the EBITDA in year 1 (C5) that yields a NPV of 0 (C28). Breakeven Year 1 EBITDA 3 4 5 PROBLEM 3-4: TitMar Motor Company Given Assumptions and Predictions Price per unit Market share (%) Market size (Year 1) Growth rate in market size beginning in Year 2 Unit variable cost Fixed cost Tax rate Cost of capital Investment in NWC Initial investment in PP&E Depreciation (5 year life wo salvage) Solution Legend Estimates $4,895 15.00% $200,000 units 5.00% $4,250 $9,000,000 50.00% 18.00% of the predicted change in firm 5.00% revenues. $7,000,000 $1,400,000 = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Part b. Substitute $4,500 for the price per unit. Part a. Substitute 5% for market share (%) . Solution Year 0 1 2 3 4 Investment Revenue Variable Cost Fixed cost Depreciation EBT(Net Operating Income) Tax Net Operating Profit after Tax (NOPAT) Plus: Depreciation expense Less: Capex Less: Change in NWC Free Cash Flow Net Present Value Internal Rate of Return Units Sold a. If the market share is only 5% then the project's NPV = b. If market share = 15% and the price of the PTV falls to $4,500 the NPV = Breakeven Sensitivity Analysis Price per unit Market share (%) Market size (Year 1) Growth rate in market size beginning in Year 2 Unit variable cost Fixed cost Tax rate Cost of capital Investment in NWC Analysis: Critical % Change Critical Value The above analysis suggests that the two key value drivers are price per unit and unit variable cost! 5 PROBLEM 2-2 a. Initial investment Annual Cash Flows Project Life IRR = Given $4,000,000.00 500,000.00 10 Solution: 4.28% Given b. IRR = Year Solution: Cash Flow 0 $(4,000,000.00) 1 500,000.00 2 500,000.00 3 500,000.00 4 500,000.00 5 300,000.00 6 500,000.00 7 500,000.00 8 500,000.00 9 500,000.00 10 500,000.00 11 500,000.00 12 500,000.00 13 500,000.00 14 500,000.00 Solution Legend = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Problem 23 PROBLEM 2-3 Given Growth rate in EBIT for years 1-5 EBIT (year 1) CAPEX for year 0 Depreciable life of fixed assets Tax rate New working capital for years 1-5 Solution Legend = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output 5% $100,000 $400,000 5 years 30% 20% of EBIT Solution Year 0 EBIT Taxes NOPAT Plus: Depreciation Less: CAPEX Less: Net working capital needs (See Note 1) Plus: Salvage value of the fixed assets in year 5 Firm Free Cash Flow (FFCF) Net Fixed Assets (beginning of the year) Plus: CAPEX Less: Depreciation Expense for the Year Net Fixed Assets (end of the year) 1 2 3 4 5 (400,000) (20,000) $100,000 30,000 $70,000 160,000 (80,000) (1,000) $105,000 31,500 $73,500 160,000 (80,000) (1,050) $110,250 33,075 $77,175 160,000 (80,000) (1,103) $115,763 34,729 $81,034 160,000 (80,000) (1,158) $121,551 36,465 $85,085 85,085 (80,000) 24,310 $(420,000) $149,000 $152,450 $156,073 $159,876 $189,396 $400,000 80,000 (160,000) $320,000 $320,000 80,000 (160,000) $240,000 $240,000 80,000 (160,000) $160,000 $160,000 80,000 (160,000) $80,000 $80,000 80,000 (160,000) $- 400,000 $400,000 Page 2 Note 1: At the end of year 5 the firm liquidates all of it's investment in net operating working capital. PROBLEM 2-7 Given Incremental revenue growth for years 1-5 Revenues (current) Incremental Revenues CAPEX for year 0 CAPEX for years 1-5 Maintenance expense for years 1-5 Depreciable life of ovens Depreciation Expense Tax rate Discount Rate Solution Legend 10.0% $9,000,000 $900,000 $1,860,000 $$120,000 per year 5 years = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output 30% 9% Solution Year 0 Incremental Revenues Incremental Depreciation Incremental maintenance expense Incremental EBIT Taxes NOPAT Plus: Depreciation Less: CAPEX Less: New working capital needs Project Free Cash Flow (PFCF) Net Present Value Internal Rate of Return 1 $900,000 - 2 $900,000 - 3 $900,000 - 4 $900,000 - 5 $900,000 - - Problem 210 PROBLEM 2-10 Given Cost of Tester (Year 0) Installation and training costs CAPEX (Year 5) Annual cost savings Salvage value Depreciation Project Life Tax rate Cost of Capital Solution Legend $250,000 $10,000 $100,000 $70,000 $5,000 Straight Line 10 years 30% 12% = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Solution a. 0 1 2 3 Year 5 4 6 7 8 Investment Outlays Equipment purchases Installation costs Initial Outlay After-tax salvage value Free Cash Flows Operating Expense Savings Less: Depreciation Expense Additional Operating Income Less: Taxes NOPAT Plus: Depreciation Less: CAPEX Free Cash Flow b. Net Present Value Internal Rate of Return NPV Profile Discount Rates NPV 0% 2% 4% 6% 8% 10% 12% 14% 16% 18% 20% 22% 24% 26% 28% 30% 32% 34% 36% 38% 40% NPV Profile 12 Net Present Value 10 8 6 4 2 0 0% Page 4 5% 10% 15% 20% 25% 30% 35% 4 0% 4 5% Discount Rat es 9 10 Problem 3-1 Solution Legend Enter the given values from the text book here Best Case 10,000 $125.00 $75.00 $250,000.00 $1,000,000.00 10 0 0 StraightLine $100,000.00 10.00% 34.00% Enter the given values from the text book here Worst case 9000 $112.50 $82.50 $275,000.00 Initial cost of equipment Project and equipment life Salvage value of equipment Working capital requirement Depreciation method Depreciation expense Discount rate Tax rate Base case Unit sales Price per unit Variable cost per unit Fixed costs Given Best Case 11000 $137.50 $67.50 $225,000.00 Solution Revenues Variable cost Fixed Expenses Gross profit Depreciation Net operating income Income tax expense Net income Cash flow NPV Expected Case Solution Revenues Variable cost Fixed Expenses Gross profit Depreciation Net operating income Income tax expense NOPAT plus: Depreciation less: CAPEX less: Working capital investment Free cash flow NPV Worst Case Revenues Variable cost Fixed Expenses Gross profit Depreciation Net operating income Income tax expense Net income Cash flow NPV=PV(E12,E7,D50)E6 NPV Assuming the negative tax credit obtained here can used somewhere else or carried forward Solution Enter the given values from the text book here = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output PROBLEM 3-4: Clayton Manufacturing Company Given EBITDA (Year 1) Growth Rate in EBITDA Initial investment Depreciation (Straight line) over Estimated salvage value Tax rate Cost of capital Solution Legend $200,000 5% $800,000 5 years $35% 12% = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Solution Years a. EBITDA Less: Depreciation Expense EBIT Less: Taxes NOPAT Plus: Depreciation Expense Less: CAPEX Less: Change in Working Capital Project FCF 0 1 $200,000 2 b. NPV c. Using "Goal Seek" to solve for the EBITDA in year 1 (C5) that yields a NPV of 0 (C28). Breakeven Year 1 EBITDA 3 4 5 PROBLEM 3-4: TitMar Motor Company Given Assumptions and Predictions Price per unit Market share (%) Market size (Year 1) Growth rate in market size beginning in Year 2 Unit variable cost Fixed cost Tax rate Cost of capital Investment in NWC Initial investment in PP&E Depreciation (5 year life wo salvage) Solution Legend Estimates $4,895 15.00% $200,000 units 5.00% $4,250 $9,000,000 50.00% 18.00% of the predicted change in firm 5.00% revenues. $7,000,000 $1,400,000 = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Part b. Substitute $4,500 for the price per unit. Part a. Substitute 5% for market share (%) . Solution Year 0 1 2 3 4 Investment Revenue Variable Cost Fixed cost Depreciation EBT(Net Operating Income) Tax Net Operating Profit after Tax (NOPAT) Plus: Depreciation expense Less: Capex Less: Change in NWC Free Cash Flow Net Present Value Internal Rate of Return Units Sold a. If the market share is only 5% then the project's NPV = b. If market share = 15% and the price of the PTV falls to $4,500 the NPV = Breakeven Sensitivity Analysis Price per unit Market share (%) Market size (Year 1) Growth rate in market size beginning in Year 2 Unit variable cost Fixed cost Tax rate Cost of capital Investment in NWC Analysis: Critical % Change Critical Value The above analysis suggests that the two key value drivers are price per unit and unit variable cost! 5 PROBLEM 2-2 a. Initial investment Annual Cash Flows Project Life IRR = Given $4,000,000.00 500,000.00 10 Solution: 4.28% Given b. IRR = Year Solution: Cash Flow 0 $(4,000,000.00) 1 500,000.00 2 500,000.00 3 500,000.00 4 500,000.00 5 300,000.00 6 500,000.00 7 500,000.00 8 500,000.00 9 500,000.00 10 500,000.00 11 500,000.00 12 500,000.00 13 500,000.00 14 500,000.00 Solution Legend = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Problem 23 PROBLEM 2-3 Given Growth rate in EBIT for years 1-5 EBIT (year 1) CAPEX for year 0 Depreciable life of fixed assets Tax rate New working capital for years 1-5 Solution Legend = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output 5% $100,000 $400,000 5 years 30% 20% of EBIT Solution Year 0 EBIT Taxes NOPAT Plus: Depreciation Less: CAPEX Less: Net working capital needs (See Note 1) Plus: Salvage value of the fixed assets in year 5 Firm Free Cash Flow (FFCF) Net Fixed Assets (beginning of the year) Plus: CAPEX Less: Depreciation Expense for the Year Net Fixed Assets (end of the year) 1 2 3 4 5 (400,000) (20,000) $100,000 30,000 $70,000 160,000 (80,000) (1,000) $105,000 31,500 $73,500 160,000 (80,000) (1,050) $110,250 33,075 $77,175 160,000 (80,000) (1,103) $115,763 34,729 $81,034 160,000 (80,000) (1,158) $121,551 36,465 $85,085 85,085 (80,000) 24,310 $(420,000) $149,000 $152,450 $156,073 $159,876 $189,396 $400,000 80,000 (160,000) $320,000 $320,000 80,000 (160,000) $240,000 $240,000 80,000 (160,000) $160,000 $160,000 80,000 (160,000) $80,000 $80,000 80,000 (160,000) $- 400,000 $400,000 Page 2 Note 1: At the end of year 5 the firm liquidates all of it's investment in net operating working capital. PROBLEM 2-7 Given Incremental revenue growth for years 1-5 Revenues (current) Incremental Revenues CAPEX for year 0 CAPEX for years 1-5 Maintenance expense for years 1-5 Depreciable life of ovens Depreciation Expense Tax rate Discount Rate Solution Legend 10.0% $9,000,000 $900,000 $1,860,000 $$120,000 per year 5 years = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output 30% 9% Solution Year 0 Incremental Revenues Incremental Depreciation Incremental maintenance expense Incremental EBIT Taxes NOPAT Plus: Depreciation Less: CAPEX Less: New working capital needs Project Free Cash Flow (PFCF) Net Present Value Internal Rate of Return 1 $900,000 - 2 $900,000 - 3 $900,000 - 4 $900,000 - 5 $900,000 - - Problem 210 PROBLEM 2-10 Given Cost of Tester (Year 0) Installation and training costs CAPEX (Year 5) Annual cost savings Salvage value Depreciation Project Life Tax rate Cost of Capital Solution Legend $250,000 $10,000 $100,000 $70,000 $5,000 Straight Line 10 years 30% 12% = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Solution a. 0 1 2 3 Year 5 4 6 7 8 Investment Outlays Equipment purchases Installation costs Initial Outlay After-tax salvage value Free Cash Flows Operating Expense Savings Less: Depreciation Expense Additional Operating Income Less: Taxes NOPAT Plus: Depreciation Less: CAPEX Free Cash Flow b. Net Present Value Internal Rate of Return NPV Profile Discount Rates NPV 0% 2% 4% 6% 8% 10% 12% 14% 16% 18% 20% 22% 24% 26% 28% 30% 32% 34% 36% 38% 40% NPV Profile 12 Net Present Value 10 8 6 4 2 0 0% Page 4 5% 10% 15% 20% 25% 30% 35% 4 0% 4 5% Discount Rat es 9 10 Problem 3-1 Solution Legend Enter the given values from the text book here Best Case 10,000 $125.00 $75.00 $250,000.00 $1,000,000.00 10 0 0 StraightLine $100,000.00 10.00% 34.00% Enter the given values from the text book here Worst case 9000 $112.50 $82.50 $275,000.00 Initial cost of equipment Project and equipment life Salvage value of equipment Working capital requirement Depreciation method Depreciation expense Discount rate Tax rate Base case Unit sales Price per unit Variable cost per unit Fixed costs Given Best Case 11000 $137.50 $67.50 $225,000.00 Solution Revenues Variable cost Fixed Expenses Gross profit Depreciation Net operating income Income tax expense Net income Cash flow NPV Expected Case Solution Revenues Variable cost Fixed Expenses Gross profit Depreciation Net operating income Income tax expense NOPAT plus: Depreciation less: CAPEX less: Working capital investment Free cash flow NPV Worst Case Revenues Variable cost Fixed Expenses Gross profit Depreciation Net operating income Income tax expense Net income Cash flow NPV=PV(E12,E7,D50)E6 NPV Assuming the negative tax credit obtained here can used somewhere else or carried forward Solution Enter the given values from the text book here = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output PROBLEM 3-4: Clayton Manufacturing Company Given EBITDA (Year 1) Growth Rate in EBITDA Initial investment Depreciation (Straight line) over Estimated salvage value Tax rate Cost of capital Solution Legend $200,000 5% $800,000 5 years $35% 12% = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Solution Years a. EBITDA Less: Depreciation Expense EBIT Less: Taxes NOPAT Plus: Depreciation Expense Less: CAPEX Less: Change in Working Capital Project FCF 0 1 $200,000 2 b. NPV c. Using "Goal Seek" to solve for the EBITDA in year 1 (C5) that yields a NPV of 0 (C28). Breakeven Year 1 EBITDA 3 4 5 PROBLEM 3-4: TitMar Motor Company Given Assumptions and Predictions Price per unit Market share (%) Market size (Year 1) Growth rate in market size beginning in Year 2 Unit variable cost Fixed cost Tax rate Cost of capital Investment in NWC Initial investment in PP&E Depreciation (5 year life wo salvage) Solution Legend Estimates $4,895 15.00% $200,000 units 5.00% $4,250 $9,000,000 50.00% 18.00% of the predicted change in firm 5.00% revenues. $7,000,000 $1,400,000 = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Part b. Substitute $4,500 for the price per unit. Part a. Substitute 5% for market share (%) . Solution Year 0 1 2 3 4 Investment Revenue Variable Cost Fixed cost Depreciation EBT(Net Operating Income) Tax Net Operating Profit after Tax (NOPAT) Plus: Depreciation expense Less: Capex Less: Change in NWC Free Cash Flow Net Present Value Internal Rate of Return Units Sold a. If the market share is only 5% then the project's NPV = b. If market share = 15% and the price of the PTV falls to $4,500 the NPV = Breakeven Sensitivity Analysis Price per unit Market share (%) Market size (Year 1) Growth rate in market size beginning in Year 2 Unit variable cost Fixed cost Tax rate Cost of capital Investment in NWC Analysis: Critical % Change Critical Value The above analysis suggests that the two key value drivers are price per unit and unit variable cost! 5
