4 A B B D E E F G 1 I. Given the following data on proposed capital budgeting project. (Numerical Inputs Expected from you are highlighted in yellow and Formula/Function Inputs are highlighted in blue) Parameters Economic life of project in years. 4 Market Value of Equity # of Shares Outstanding 5 Price of New Equipment $ 243,260.00 Market Value of Debt # of Bonds Outstanding 6 Change in NWC Total Market Value $ Market Price of Bonds S 957.00 7 Fixed Costs Weight of Equity Market Price of Stock $ 80.00 8 Variable Costs (% of Revenue) Weight of Debt 9 Salvage value of New Equipment E(R) 10 Marginal Tax Rate R Bond Info Provided for your convenience 11 First Year Unit Sales 4700 B Years to Maturity 14 12 Sales Growth Rate 24.00% Cost of Equity (r.) PMT 13 Unit Sale Price $ 100.00 Cost of Debt (ra) FV $1,000 14 First Year Revenue After-Tax Cost of Debt Before tax YTM WACC Before tax YTM * 2 Note Cells C21 and C22 include the initial (today's) cash flows. 17 Column D through G are the operating cash flows. 18 Spreadsheet for determining Cash Flows Cells G38-G40 contain the terminal cash flows. 19 Timeline: Year 0 0 2 3 3 20 II. Net Investment Outlay = Initial CFS Price of Equipment S (243,260.00) Change in NWC 23 III. Cash Flows from Operations Revenue Generation Unit Sales 4700 26 Unit Sale Price $ 100.00 27 Revenues $ 470,000.00 28 Costs 29 Variable Costs 30 Fixed Costs Depreciation Earnings Before Taxes 33 Taxes Net Income 35 Depreciation 36 Net operating CFS 37 IV. Terminal Cash Flows 38 Salvage Value 39 Tax on Salvage Value 40 Return of NWC 41 V. Final Cash Flow 42 Cash Flows Present Value of CFS 44 45 NPV of Project 46 47 Summarize Answers for NPV under three cases in area below 48 Sales Growth Rate NPV Accept? 49 Best Case 34.0% :gggggggggggggc=s=ses no mee - 50 Most Likely 24.00% 51 Worst Case 2.4% A B C D E F G H L M N 0 I J K Most-Likely Case - NPV Profile Year CF PV(CF) 0 1 PVCF) PVCF) PVCF) PV(CF) 2 3 4 NPV Discount Rate 0% Most-Likely Case NPV Profile $1 1 A Sample NPV Profile 2 Year CF PVCF) PVCF) PV(CF) PVCF) PVCF) 3 0 -$265,000.00 -$265,000.00 -$265,000.00 -$265,000.00 -$265,000.00 -$265,000.00 4 1 $52,673.75 $52,673.75 $51,640.93 $50,647.84 $49,692.22 $48,771.99 5 2 $63,212.50 $63,212.50 $60,757.88 $58,443.51 $56,258.90 $54,194.53 6 3 $73,742.50 $73,742.50 $69,489.20 $65,556.81 $61,915.62 $58,539.17 7 4 $122,628.50 $122,628.50 $113,289.78 $104,823.36 $97,133.26 $90,135.61 8 NPV $47,257.25 $30,177.80 $14,471.52 $0.00 -$13,358.70 9 Discount Rate 2% 4% 6% 8% 10 11 12 A Sample NPV Profile 13 14 $60,000 15 $50,000 16 $40,000 17 18 $30,000 19 $ 20,000 20 $10,000 21 $0 22 0% 1% 2% 3% 4% 5% 6% 8% 9% 23 -$10,000 24 -$20,000 25 Discount Rate ------> 26 27 28 29 30 31 $1 $1 $1 NPV ---------> NPV $0 $0 $0 0% 20% 40% 60% 80% 100% 120% Discount Rate ---- 32 33 4 A B B D E E F G 1 I. Given the following data on proposed capital budgeting project. (Numerical Inputs Expected from you are highlighted in yellow and Formula/Function Inputs are highlighted in blue) Parameters Economic life of project in years. 4 Market Value of Equity # of Shares Outstanding 5 Price of New Equipment $ 243,260.00 Market Value of Debt # of Bonds Outstanding 6 Change in NWC Total Market Value $ Market Price of Bonds S 957.00 7 Fixed Costs Weight of Equity Market Price of Stock $ 80.00 8 Variable Costs (% of Revenue) Weight of Debt 9 Salvage value of New Equipment E(R) 10 Marginal Tax Rate R Bond Info Provided for your convenience 11 First Year Unit Sales 4700 B Years to Maturity 14 12 Sales Growth Rate 24.00% Cost of Equity (r.) PMT 13 Unit Sale Price $ 100.00 Cost of Debt (ra) FV $1,000 14 First Year Revenue After-Tax Cost of Debt Before tax YTM WACC Before tax YTM * 2 Note Cells C21 and C22 include the initial (today's) cash flows. 17 Column D through G are the operating cash flows. 18 Spreadsheet for determining Cash Flows Cells G38-G40 contain the terminal cash flows. 19 Timeline: Year 0 0 2 3 3 20 II. Net Investment Outlay = Initial CFS Price of Equipment S (243,260.00) Change in NWC 23 III. Cash Flows from Operations Revenue Generation Unit Sales 4700 26 Unit Sale Price $ 100.00 27 Revenues $ 470,000.00 28 Costs 29 Variable Costs 30 Fixed Costs Depreciation Earnings Before Taxes 33 Taxes Net Income 35 Depreciation 36 Net operating CFS 37 IV. Terminal Cash Flows 38 Salvage Value 39 Tax on Salvage Value 40 Return of NWC 41 V. Final Cash Flow 42 Cash Flows Present Value of CFS 44 45 NPV of Project 46 47 Summarize Answers for NPV under three cases in area below 48 Sales Growth Rate NPV Accept? 49 Best Case 34.0% :gggggggggggggc=s=ses no mee - 50 Most Likely 24.00% 51 Worst Case 2.4% A B C D E F G H L M N 0 I J K Most-Likely Case - NPV Profile Year CF PV(CF) 0 1 PVCF) PVCF) PVCF) PV(CF) 2 3 4 NPV Discount Rate 0% Most-Likely Case NPV Profile $1 1 A Sample NPV Profile 2 Year CF PVCF) PVCF) PV(CF) PVCF) PVCF) 3 0 -$265,000.00 -$265,000.00 -$265,000.00 -$265,000.00 -$265,000.00 -$265,000.00 4 1 $52,673.75 $52,673.75 $51,640.93 $50,647.84 $49,692.22 $48,771.99 5 2 $63,212.50 $63,212.50 $60,757.88 $58,443.51 $56,258.90 $54,194.53 6 3 $73,742.50 $73,742.50 $69,489.20 $65,556.81 $61,915.62 $58,539.17 7 4 $122,628.50 $122,628.50 $113,289.78 $104,823.36 $97,133.26 $90,135.61 8 NPV $47,257.25 $30,177.80 $14,471.52 $0.00 -$13,358.70 9 Discount Rate 2% 4% 6% 8% 10 11 12 A Sample NPV Profile 13 14 $60,000 15 $50,000 16 $40,000 17 18 $30,000 19 $ 20,000 20 $10,000 21 $0 22 0% 1% 2% 3% 4% 5% 6% 8% 9% 23 -$10,000 24 -$20,000 25 Discount Rate ------> 26 27 28 29 30 31 $1 $1 $1 NPV ---------> NPV $0 $0 $0 0% 20% 40% 60% 80% 100% 120% Discount Rate ---- 32 33