Final Case Project I. Given the following data on proposed capital budgeting project. Economic life of...

Transcribed Image Text:

Final Case Project I. Given the following data on proposed capital budgeting project. Economic life of project in years. Price of New Equipment Fixed Costs Salvage value of New Equipment Effect on NWC: First Year Revenues Variable Costs Marginal Tax Rate 10 11 Growth Rate Note Cells C17 and C18 include the initial cash flows today. Column D through Gare the operating cash flows. Cells D30, D31, and D32 include terminal cash flows. PLEASE NOTE: You may need to change inputs provided in Column E at left. WACC Total Equity WACC Beta Total Debt Total Value $ Re 0 1 2 3 4 Rd 14 Spreadsheet for determining Cash Flows (in Thousands) 15 Timeline: 16 II. Net Investment Outlay = Initial CFs Price Increase in NWC 19 III. Cash Flows from Operations 20 -2345678922231562222222222222333333333 Total Revenues Variable Costs Fixed Costs Depreciation Earnings Before Taxes Taxes Net Income Depreciation Net operating CFs 29 IV. Terminal Cash Flows Salvage Value 30 36 Calculate: Tax on Salvage Value Return of NWC Cash Flows Present Value of CFs NPV Decision: NPV Year Accept? Reject? Tax Rate WACC: Cost of Debt N Periods I/Y PMT PV FV Cost of Equity Risk Free Rate Market Risk Premium Beta CAPM: 40 A B C D 1 Creating a NPV Profile (Most Likely Scenario) 2 Discount Rate: 0% PV(CF) 2% PV(CF) 3 Year CF 4 0 $ 5 1 $ 6 2 $ 7 3 $ 8 4 $ 9 NPV 10 Discount Rate: 11 $ % #DIV/0! #DIV/O! E F G H J K L M N P Q R S Create a NPV by creating a line graph of rows 9 and 10. 4% PV(CF) 6% PV(CF) 8% PV(CF) 0% 2% 4% 6% 8% Cells B4 to B8 in this worksheet can link to cells C32 to G32 in the Blank Template worksheet. Find the present value of cash flows by referencing row 2 for the discount rate. You can do column C the same way as you did C33 to G33 in the Blank Template worksheet. Rows 9 & 10 are the table that are used to create the NPV profile graph. Final Case Project I. Given the following data on proposed capital budgeting project. Economic life of project in years. Price of New Equipment Fixed Costs Salvage value of New Equipment Effect on NWC: First Year Revenues Variable Costs Marginal Tax Rate 10 11 Growth Rate Note Cells C17 and C18 include the initial cash flows today. Column D through Gare the operating cash flows. Cells D30, D31, and D32 include terminal cash flows. PLEASE NOTE: You may need to change inputs provided in Column E at left. WACC Total Equity WACC Beta Total Debt Total Value $ Re 0 1 2 3 4 Rd 14 Spreadsheet for determining Cash Flows (in Thousands) 15 Timeline: 16 II. Net Investment Outlay = Initial CFs Price Increase in NWC 19 III. Cash Flows from Operations 20 -2345678922231562222222222222333333333 Total Revenues Variable Costs Fixed Costs Depreciation Earnings Before Taxes Taxes Net Income Depreciation Net operating CFs 29 IV. Terminal Cash Flows Salvage Value 30 36 Calculate: Tax on Salvage Value Return of NWC Cash Flows Present Value of CFs NPV Decision: NPV Year Accept? Reject? Tax Rate WACC: Cost of Debt N Periods I/Y PMT PV FV Cost of Equity Risk Free Rate Market Risk Premium Beta CAPM: 40 A B C D 1 Creating a NPV Profile (Most Likely Scenario) 2 Discount Rate: 0% PV(CF) 2% PV(CF) 3 Year CF 4 0 $ 5 1 $ 6 2 $ 7 3 $ 8 4 $ 9 NPV 10 Discount Rate: 11 $ % #DIV/0! #DIV/O! E F G H J K L M N P Q R S Create a NPV by creating a line graph of rows 9 and 10. 4% PV(CF) 6% PV(CF) 8% PV(CF) 0% 2% 4% 6% 8% Cells B4 to B8 in this worksheet can link to cells C32 to G32 in the Blank Template worksheet. Find the present value of cash flows by referencing row 2 for the discount rate. You can do column C the same way as you did C33 to G33 in the Blank Template worksheet. Rows 9 & 10 are the table that are used to create the NPV profile graph.