COST OF NEW EQUIPMENT :$240000
1 Capital Budgeting Group Assignment Principles of Financial Management FINC 3310 - Fall 2022 Professor Ken Globerman Assignment Learning Objectives: (a) Develop a proforma project income statement using an Excel spreadsheet (b) Compute net project cash flows, NPV, IRR and Payback period (c) Perform sensitivity and scenario analyses (d) Develop problem-solving and critical thinking skills Assignment Instructions: There are 3 parts to this assignment, which combines using Excel to perform calculations and answering the eight questions on the "Additional Questions" tab. The use of formulas and cell references (rather than hard typing in numbers) should be used in all cases where they can. To guide you, the cells shaded gray are where 12 formulas, numerical inputs or calcuations are needed in order to complete the assignment. good use of Excel functionality and spreadsheet formatting, and the completeness of your answers to the 13 questions. In this assignment, you are evaluating a potential capital investment project that has a 4 year life. The financial details are as follows: 1) Useful life of the equipment (years) 2) New equipment cost 5) Inventory increase 6) Accounts payable increase $5,000 7) Equipment salvage value (end of life) $15,000 8) Projected sales for year 1 9) Projected sales growth per year Low Case Base Case High Case 10) Operating cost (as a \% of sale 60% 11) Depreciation (straight line) per year 12) Corporate marginal tax rate (T) \begin{tabular}{|l|} \hline 2% \\ \hline 5% \\ \hline 8% \\ \hline \end{tabular} 13) Cost of capital or discount rate (r) $56,25 21% 10% PART 1A: Use the Excel cells below to calculate all estimated project cash flows for the BASE CASE revenue PART 2A: Do a similar analysis as in Part 1 to analyze a new scenario where the equipment is fully depreciated in the first year. All other inputs remain the same. Re-calculate NPV, IRR and Payback period of the project. HINT: negative EBIT creates a tax credit! 72 New equipment cost \#\#\#\#\#\#\# 73 Net Working Capital needs at start $20,000 74 Total Initial Outlay \#\#\#\#\#\# $$$ 75 Operations: Profit Margin (\%) Add back Depreciation Total Operating Cash Flow Terminal Cash Flows (end of Year 4) 1) Change in net WC 2) Salvage value (after tax) [= Salvage value before tax * (1T)] Total Project Net Cash Flows NPV (New Scenario) = IRR= Payback = 11 Question 3: Would you accept the project based on this scenario? 12 Question 4:As the firm's CFO, explain the differences between the two depreciation alternatives and how they impact your NPV, IRR and Payback calculations. 13 14 Answer 3 : 15 Answer 4: 1 Capital Budgeting Group Assignment Principles of Financial Management FINC 3310 - Fall 2022 Professor Ken Globerman Assignment Learning Objectives: (a) Develop a proforma project income statement using an Excel spreadsheet (b) Compute net project cash flows, NPV, IRR and Payback period (c) Perform sensitivity and scenario analyses (d) Develop problem-solving and critical thinking skills Assignment Instructions: There are 3 parts to this assignment, which combines using Excel to perform calculations and answering the eight questions on the "Additional Questions" tab. The use of formulas and cell references (rather than hard typing in numbers) should be used in all cases where they can. To guide you, the cells shaded gray are where 12 formulas, numerical inputs or calcuations are needed in order to complete the assignment. good use of Excel functionality and spreadsheet formatting, and the completeness of your answers to the 13 questions. In this assignment, you are evaluating a potential capital investment project that has a 4 year life. The financial details are as follows: 1) Useful life of the equipment (years) 2) New equipment cost 5) Inventory increase 6) Accounts payable increase $5,000 7) Equipment salvage value (end of life) $15,000 8) Projected sales for year 1 9) Projected sales growth per year Low Case Base Case High Case 10) Operating cost (as a \% of sale 60% 11) Depreciation (straight line) per year 12) Corporate marginal tax rate (T) \begin{tabular}{|l|} \hline 2% \\ \hline 5% \\ \hline 8% \\ \hline \end{tabular} 13) Cost of capital or discount rate (r) $56,25 21% 10% PART 1A: Use the Excel cells below to calculate all estimated project cash flows for the BASE CASE revenue PART 2A: Do a similar analysis as in Part 1 to analyze a new scenario where the equipment is fully depreciated in the first year. All other inputs remain the same. Re-calculate NPV, IRR and Payback period of the project. HINT: negative EBIT creates a tax credit! 72 New equipment cost \#\#\#\#\#\#\# 73 Net Working Capital needs at start $20,000 74 Total Initial Outlay \#\#\#\#\#\# $$$ 75 Operations: Profit Margin (\%) Add back Depreciation Total Operating Cash Flow Terminal Cash Flows (end of Year 4) 1) Change in net WC 2) Salvage value (after tax) [= Salvage value before tax * (1T)] Total Project Net Cash Flows NPV (New Scenario) = IRR= Payback = 11 Question 3: Would you accept the project based on this scenario? 12 Question 4:As the firm's CFO, explain the differences between the two depreciation alternatives and how they impact your NPV, IRR and Payback calculations. 13 14 Answer 3 : 15 Answer 4
