Capital Budgeting General Information Kim Holland, Chief Financial Officer for the Global Manufacturing, Inc., has just completed summarizing the financial aspects of four capital investment projects that are open to Global Manufacturing in the coming year, and she was faced with the task of recommending which one(s) should be selected. What concemed her was the knowledge that her boss, Phil Jackson, the CEO of Global Manufacturing, who only has a little background in financial management, would immediately favor the project that promised the quickest payback. Kim knows that selecting projects purely on that basis would be incorrect, but she wasn't sure of her ability to convince Phil, who tended to assume financial analysts thought up fancy methods just to show how smart they were. OPERATING LIFE: All projects have an estimated 4-year operating life. As she prepared to enter Phil's office, Kim pulled her summary sheets from her briefcase and quickly reviewed the details of the projects, all four projects she considered to be independent projects and have the same risk as the firm's current capital investments. Every division has submitted their information to Kim for their capital projects to be considered and Kim will need your help to evaluate these projects: CAPITAL PROJECT 1 - Global Manufacturing is considering expanding its production of very successful model LD 1103 Basic Desk Lamp. CAPITAL PROJECT 2 - Global Manufacturing's LED lighting division has proposed manufacturing and selling a new line of LED Dimmable Desk Lamp. CAPITAL PROJECT 3 - Global Manufacturing is considering producing and selling a new line of Flexible LED USB desk lamps. CAPITAL PROJECT 4 - Global Manufacturing is considering producing the components of its LED Desk Lamp Adjustable Eye-caring Table Lamp in Taiwan and then ship the components to the U.S. and assemble them in the same plant where they make LD1103 Basic Desk Lamp. This would allow the products to be labeled Assembled in the USA and sell at a slightly higher price. In her mind, Kim quickly went over the evaluation methods she had used in the past: payback period, discounted payback period, net present value (NPV), internal rate of return (IRR), and the profitability index (PI). Kim knew that Phil liked all projects to payback within 4 years. Another constraint that Kim has to deal with is that Phil and the Board of Directors are following the Sustainable Growth approach which does not allow the firm to issue new common stock or allow the firm to exceed its current debt- equity ratio. As a result, Phil has imposed a capital spending limit (called Capital Rationing) this year which is shown on the CAPITAL BUDGETING ANALYSIS worksheet. CAPITAL BUDGETING CALCULATIONS Be sure to read the CAPITAL BUDGETING INSTRUCTIONS carefully before starting your calculations on this worksheet. Capital Budgeting Calculations You will find the Cash Flow estimates for each of the four projects and the Target Rate of Return for 2021 developed by the Finance Department on the CAPITAL BUDGETING CALCULATIONS worksheet. Use this information to complete the following calculations: For Capital Projects 1, 2, 3, and 4 do the following 1) CALCULATE: Cumulative Cash Flows (Do not include the dollar sign (S). Negative amounts must be indicated by a minus sign to be considered correct. Round your answer to the nearest whole dollar. (e.g., 3.216)). 2) CALCULATE: Discounted Cash Flows (Do not include the dollar sign (S). Negative amounts must be indicated by a minus sign to be considered correct. Round your answer to the nearest whole dollar. (e.g., 3.216)). 3) CALCULATE: Cumulative Discounted Cash Flows (Do not include the dollar sign (S). Negative amounts must be indicated by a minus sign to be considered correct. Round your answer to the nearest whole . dollar. (e.g., 3,216)). 4) CALCULATE: Net Present Value (NPV) (Do not include the dollar sign (5). Negative amounts must be indicated by a minus sign to be considered correct Round your answer to the nearest whole dolar (eg, 3216)) i. ANALYSIS: (Select from a drop down list question): After you have completed your calculations for NPV evaluate each project based on your findings and determine if each project is either ACCEPTABLE or NOT ACCEPTABLE based on NPV acceptance criterion 5) CALCULATE: Internal Rate of Return (IRR) (Do not include the percent sign (%). Round your answer to 2 decimal places. (e... 32.16) i. ANALYSIS: (Select from a drop down is question: After you have completed your calculations for IRR evaluate each project based on your findings and determine if each project is either ACCEPTABLE or NOT ACCEPTABLE based on IRR acceptance criterion. 6) CALCULATE: Profitability Index (P) (Round your answer to 2 decimal places (eg 32.15)) i. ANALYSIS: (Select from a drop down list question): After you have completed your calculations for F1 evaluate each project based on your findings and determine if each project is either ACCEPTABLE or NOT ACCEPTARLE based on PI acceptance criterion 7) CALCULATE: Modified Internal Rate of Return (MIRR) using the combination approach only. (Do not include the percent sign (%). Round your answer to 2 decimal places (eg. 32. 16) i. ANALYSIS: (Select from a drop down list question): After you have completed your calculations for MIRR ACCEPTABLE based on MIRR acceptance criterion. evaluate each project based on your findings and determine if each project is either ACCEPTABLE or NOT 8) CALCULATE: Payback period (Round your answer to 2 decimal places. (e 9.32 16). If payback does not occur in 4 years enter 0.00 for NO PAYBACK ANALYSIS: (Select from a drop down ist question: After you have completed your calculations for Payback period evaluate each project based on your findings and determine if each project is either ACCEPTABLE OR NOT ACCEPTABLE based on Payback period acceptance criterion (Remember, for the bayback period the CEO expects projects to payback within 4 years) 9) CALCULATE: Discounted Payback Period (Round your answer to 2 decimal places (eg, 32.16). If payback does not occur in 4 years enter 0.00 for NO PAYBACK CK) i ANALYSIS: (Select from a drop down list question: After you have completed your calculations for Discounted Payback Period evaluate each project based on your findings and determine if each project is either ACCEPTABLE or NOT ACCEPTABLE based on Discounted Payback Period acceptance criterion (Remember, for the discounted payback penod the CEO expects projects to payback within 4 years.) HELP: See the CASE STUDY HELP ble named "10-SAMPLE COMPANY CAPITAL BUDGETING CALCULATIONS & ANALYSIS.pdf" provided to help you selup your : - spreadsheet for these capital budgeting calculations. NOTE: Cells shaded In YELLOW are the cell you must provide the calculations NOTE: Call shaded in RED are the calls that will automatically calculate 0 1 CASH FLOWS OVER PROJECT #1's LIFE Target Rate of Return= WACC = 0.000% Auto Transferred from your WACC worksheet Net Cashonchwar Year Number YEAR 2021 2022 2023 2024 CAPITAL PRODUCT #1 -$6,185,800 $1,876,800 $2,165,100 $2,923,000 3 2025 $3,741,400 4 Cumulative cash flows (See Problem 5-1) ) $0 $ $0 $0 $0 $0 5 Discounted cash flows (See Problems 5-3 Parta) & 54 Part) ) $0 $0 $0 $ $0 so 6 Cumulative discounted cash flows (See Problems 5-3 Parta) & 54 Part $0 $0 $0 $0 $ $0 7 3 CAPITAL BUDGETING CALCULATIONS PRODUCT #1 9 CALCULATE ANALYSIS: ACCEPTABLE or UNACCEPTABLE? 0 Net Present Value (NPV) See Problems 5-11 Parte) & 5-12 (Part $0 0.00% 0.000 Internal Rate of Return (IRR) Soe Problems 55 & 5-6) 2 Profitability Index (PI) See Problem 5-8) Modified Internal Rate of Return (MIRR) (using the COMBINATION APPROACH) 4 ison Problem 5-201 Payback Period (PP) ) See Problem 5-1) 0.00% Reinvestment rate and Finance Rate for MRR should both be equal to the Target Rate of Return 0.00 Discounted Payback Period (DPP) See Problems 5-3 Parta) & 54 Part 0.00 CASH FLOWS OVER PROJECT #2's TIFF 8 0 4 2025 CASH FLOWS OVER PROJECT #2's LIFE Target Rate of Retum = WACC = 0.000% Auto Transferred from your WACC worksheet Net Cash Flows each year Year Number O 1 2 3 YEAR 2021 2022 2023 2024 CAPITAL PRODUCT #2 - $7,186 400 $1,968,300 $1,970,100 $1,745,100 Cumulative cash flows $0 $ $0 $0 $0 (See Problem 5-1) Discounted cash flows $0 $0 $0 $0 (See Problems 5-3 (Parta) & 5-4 (Part b)) Cumulative discounted cash flows $0 $0 (See Problems 5-3 (Part a) & 5-4 (Part b)) $0 $0 $4,546,000 $0 $ 4 $0 5 $0 6 7 3 CAPITAL BUDGETING CALCULATIONS PRODUCT #2 ANALYSIS: CALCULATE ACCEPTABLE or UNACCEPTABLE? Net Present Value (NPV) $o (See Problems 5-11 (Part c) & 5-12 (Part b)) 0.00% Internal Rate of Return (IRR) (See Problems 5-5 & 5-6) 1 Profitability Index (PI) (See Problem 5-8) 2 Modified Internal Rate of Return (MIRR) (using the COMBINATION APPROACH) See Problem 5-20) 0.000 0.00% Reinvestment rate and Finance Rate for MIRR should both be equal to the Target Rate of Roburn Payback Period (PP) (See Problem 5-1) 0.00 Discounted Payback Period (DPP) (See Problems 5-3 (Part a) & 5-4 (Part b)) 0.00 7 8 CASH FLOWS OVER PROJECT #3's LIFE Target Rate of Retum = 0.000% Auto Transferred from your WACC worksheet Net Cash Flows each vear Year Number 0 3 YEAR 2021 2022 2023 2024 CAPITAL PRODUCT #3 -$5,662,800 $1,097,900 $2,040,200 $2,846,100 0 1 2 4 1 2025 $3,942.000 so $0 $0 $0 $0 3 Cumulative cash flows (See Problem 5-1) Discounted cash flows (See Problems 5-3 (Parta) & 5-4 (Part b)) Cumulative discounted cash flows (See Problems 5-3 (Parta) & 5-4 (Part b)) $0 $0 $0 $0 $0 4 $0 $0 $0 $0 $0 s 6 7 CAPITAL BUDGETING CALCULATIONS PRODUCT #3 # ANALYSIS: CALCULATE ACCEPTABLE or UNACCEPTABLE? Net Present Value (NPV) $0 (See Problems 5-11 (Part c) & 5-12 (Part b)) Internal Rate of Return (IRR) (See Problems 5-5 & 5-6) 0.00% Profitability Index (PI) See Problem 5-8) 0.000 1 Modified Internal Rate of Return (MIRR) (using the COMBINATION APPROACH) (See Problem 5-20) 0.00% Reinvestment rate and Finance Rate for MIRR should both be equal to the Target Rate of Return Payback Period (PP) (See Problem 5-1) 0.00 Discounted Payback Period (DPP) (See Problems 5-3 (Part a) & 5-4 (Part b)) 0.00 4 CASH FLOWS OVER PROJECT #4's LIFE Target Rate of Return 0.000% Auto Transferred from your WACC worksheet Net Cash Flows each year Year Number YEAR 2021 1 2 2022 2023 2024 2025 CAPITAL PRODUCT #4 - $7,991,400 $1,917,200 $2,298,600 $3,255,800 $5,212,400 $0 $0 $0 $0 $0 Cumulative cash flows (See Problem 5-1) Discounted cash flows (See Problems 5-3 (Parta) & 5-4 (Part b)) Cumulative discounted cash flows (See Problems 5-3 (Parta) & 5-4 (Part b)) $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 CAPITAL BUDGETING CALCULATIONS PRODUCT #4 CALCULATE ANALYSIS: ACCEPTABLE or UNACCEPTABLE? Net Present Value (NPV) (See Problems 5-11 (Partc) & 5-12 (Part b)) $0 internal Rate of Return (IRR) (See Problems 5-5 & 5-6) 0.00% 0.000 Profitability Index (PI) See Problem 5-8) Modified Internal Rate of Return (MIRR) (using the COMBINATION APPROACH) (See Problem 5-20) Payback Period (PP) (See Problem 5-1) 0.00% Reinvestment rats and Finance Rato for MRR should both be equal to the Target Rate of Return 0.00 Discounted Payback Period (DPP) (See Problems 5-3 (Part a) & 5-4 (Part b)) 0.00 Capital Budgeting General Information Kim Holland, Chief Financial Officer for the Global Manufacturing, Inc., has just completed summarizing the financial aspects of four capital investment projects that are open to Global Manufacturing in the coming year, and she was faced with the task of recommending which one(s) should be selected. What concemed her was the knowledge that her boss, Phil Jackson, the CEO of Global Manufacturing, who only has a little background in financial management, would immediately favor the project that promised the quickest payback. Kim knows that selecting projects purely on that basis would be incorrect, but she wasn't sure of her ability to convince Phil, who tended to assume financial analysts thought up fancy methods just to show how smart they were. OPERATING LIFE: All projects have an estimated 4-year operating life. As she prepared to enter Phil's office, Kim pulled her summary sheets from her briefcase and quickly reviewed the details of the projects, all four projects she considered to be independent projects and have the same risk as the firm's current capital investments. Every division has submitted their information to Kim for their capital projects to be considered and Kim will need your help to evaluate these projects: CAPITAL PROJECT 1 - Global Manufacturing is considering expanding its production of very successful model LD 1103 Basic Desk Lamp. CAPITAL PROJECT 2 - Global Manufacturing's LED lighting division has proposed manufacturing and selling a new line of LED Dimmable Desk Lamp. CAPITAL PROJECT 3 - Global Manufacturing is considering producing and selling a new line of Flexible LED USB desk lamps. CAPITAL PROJECT 4 - Global Manufacturing is considering producing the components of its LED Desk Lamp Adjustable Eye-caring Table Lamp in Taiwan and then ship the components to the U.S. and assemble them in the same plant where they make LD1103 Basic Desk Lamp. This would allow the products to be labeled Assembled in the USA and sell at a slightly higher price. In her mind, Kim quickly went over the evaluation methods she had used in the past: payback period, discounted payback period, net present value (NPV), internal rate of return (IRR), and the profitability index (PI). Kim knew that Phil liked all projects to payback within 4 years. Another constraint that Kim has to deal with is that Phil and the Board of Directors are following the Sustainable Growth approach which does not allow the firm to issue new common stock or allow the firm to exceed its current debt- equity ratio. As a result, Phil has imposed a capital spending limit (called Capital Rationing) this year which is shown on the CAPITAL BUDGETING ANALYSIS worksheet. CAPITAL BUDGETING CALCULATIONS Be sure to read the CAPITAL BUDGETING INSTRUCTIONS carefully before starting your calculations on this worksheet. Capital Budgeting Calculations You will find the Cash Flow estimates for each of the four projects and the Target Rate of Return for 2021 developed by the Finance Department on the CAPITAL BUDGETING CALCULATIONS worksheet. Use this information to complete the following calculations: For Capital Projects 1, 2, 3, and 4 do the following 1) CALCULATE: Cumulative Cash Flows (Do not include the dollar sign (S). Negative amounts must be indicated by a minus sign to be considered correct. Round your answer to the nearest whole dollar. (e.g., 3.216)). 2) CALCULATE: Discounted Cash Flows (Do not include the dollar sign (S). Negative amounts must be indicated by a minus sign to be considered correct. Round your answer to the nearest whole dollar. (e.g., 3.216)). 3) CALCULATE: Cumulative Discounted Cash Flows (Do not include the dollar sign (S). Negative amounts must be indicated by a minus sign to be considered correct. Round your answer to the nearest whole . dollar. (e.g., 3,216)). 4) CALCULATE: Net Present Value (NPV) (Do not include the dollar sign (5). Negative amounts must be indicated by a minus sign to be considered correct Round your answer to the nearest whole dolar (eg, 3216)) i. ANALYSIS: (Select from a drop down list question): After you have completed your calculations for NPV evaluate each project based on your findings and determine if each project is either ACCEPTABLE or NOT ACCEPTABLE based on NPV acceptance criterion 5) CALCULATE: Internal Rate of Return (IRR) (Do not include the percent sign (%). Round your answer to 2 decimal places. (e... 32.16) i. ANALYSIS: (Select from a drop down is question: After you have completed your calculations for IRR evaluate each project based on your findings and determine if each project is either ACCEPTABLE or NOT ACCEPTABLE based on IRR acceptance criterion. 6) CALCULATE: Profitability Index (P) (Round your answer to 2 decimal places (eg 32.15)) i. ANALYSIS: (Select from a drop down list question): After you have completed your calculations for F1 evaluate each project based on your findings and determine if each project is either ACCEPTABLE or NOT ACCEPTARLE based on PI acceptance criterion 7) CALCULATE: Modified Internal Rate of Return (MIRR) using the combination approach only. (Do not include the percent sign (%). Round your answer to 2 decimal places (eg. 32. 16) i. ANALYSIS: (Select from a drop down list question): After you have completed your calculations for MIRR ACCEPTABLE based on MIRR acceptance criterion. evaluate each project based on your findings and determine if each project is either ACCEPTABLE or NOT 8) CALCULATE: Payback period (Round your answer to 2 decimal places. (e 9.32 16). If payback does not occur in 4 years enter 0.00 for NO PAYBACK ANALYSIS: (Select from a drop down ist question: After you have completed your calculations for Payback period evaluate each project based on your findings and determine if each project is either ACCEPTABLE OR NOT ACCEPTABLE based on Payback period acceptance criterion (Remember, for the bayback period the CEO expects projects to payback within 4 years) 9) CALCULATE: Discounted Payback Period (Round your answer to 2 decimal places (eg, 32.16). If payback does not occur in 4 years enter 0.00 for NO PAYBACK CK) i ANALYSIS: (Select from a drop down list question: After you have completed your calculations for Discounted Payback Period evaluate each project based on your findings and determine if each project is either ACCEPTABLE or NOT ACCEPTABLE based on Discounted Payback Period acceptance criterion (Remember, for the discounted payback penod the CEO expects projects to payback within 4 years.) HELP: See the CASE STUDY HELP ble named "10-SAMPLE COMPANY CAPITAL BUDGETING CALCULATIONS & ANALYSIS.pdf" provided to help you selup your : - spreadsheet for these capital budgeting calculations. NOTE: Cells shaded In YELLOW are the cell you must provide the calculations NOTE: Call shaded in RED are the calls that will automatically calculate 0 1 CASH FLOWS OVER PROJECT #1's LIFE Target Rate of Return= WACC = 0.000% Auto Transferred from your WACC worksheet Net Cashonchwar Year Number YEAR 2021 2022 2023 2024 CAPITAL PRODUCT #1 -$6,185,800 $1,876,800 $2,165,100 $2,923,000 3 2025 $3,741,400 4 Cumulative cash flows (See Problem 5-1) ) $0 $ $0 $0 $0 $0 5 Discounted cash flows (See Problems 5-3 Parta) & 54 Part) ) $0 $0 $0 $ $0 so 6 Cumulative discounted cash flows (See Problems 5-3 Parta) & 54 Part $0 $0 $0 $0 $ $0 7 3 CAPITAL BUDGETING CALCULATIONS PRODUCT #1 9 CALCULATE ANALYSIS: ACCEPTABLE or UNACCEPTABLE? 0 Net Present Value (NPV) See Problems 5-11 Parte) & 5-12 (Part $0 0.00% 0.000 Internal Rate of Return (IRR) Soe Problems 55 & 5-6) 2 Profitability Index (PI) See Problem 5-8) Modified Internal Rate of Return (MIRR) (using the COMBINATION APPROACH) 4 ison Problem 5-201 Payback Period (PP) ) See Problem 5-1) 0.00% Reinvestment rate and Finance Rate for MRR should both be equal to the Target Rate of Return 0.00 Discounted Payback Period (DPP) See Problems 5-3 Parta) & 54 Part 0.00 CASH FLOWS OVER PROJECT #2's TIFF 8 0 4 2025 CASH FLOWS OVER PROJECT #2's LIFE Target Rate of Retum = WACC = 0.000% Auto Transferred from your WACC worksheet Net Cash Flows each year Year Number O 1 2 3 YEAR 2021 2022 2023 2024 CAPITAL PRODUCT #2 - $7,186 400 $1,968,300 $1,970,100 $1,745,100 Cumulative cash flows $0 $ $0 $0 $0 (See Problem 5-1) Discounted cash flows $0 $0 $0 $0 (See Problems 5-3 (Parta) & 5-4 (Part b)) Cumulative discounted cash flows $0 $0 (See Problems 5-3 (Part a) & 5-4 (Part b)) $0 $0 $4,546,000 $0 $ 4 $0 5 $0 6 7 3 CAPITAL BUDGETING CALCULATIONS PRODUCT #2 ANALYSIS: CALCULATE ACCEPTABLE or UNACCEPTABLE? Net Present Value (NPV) $o (See Problems 5-11 (Part c) & 5-12 (Part b)) 0.00% Internal Rate of Return (IRR) (See Problems 5-5 & 5-6) 1 Profitability Index (PI) (See Problem 5-8) 2 Modified Internal Rate of Return (MIRR) (using the COMBINATION APPROACH) See Problem 5-20) 0.000 0.00% Reinvestment rate and Finance Rate for MIRR should both be equal to the Target Rate of Roburn Payback Period (PP) (See Problem 5-1) 0.00 Discounted Payback Period (DPP) (See Problems 5-3 (Part a) & 5-4 (Part b)) 0.00 7 8 CASH FLOWS OVER PROJECT #3's LIFE Target Rate of Retum = 0.000% Auto Transferred from your WACC worksheet Net Cash Flows each vear Year Number 0 3 YEAR 2021 2022 2023 2024 CAPITAL PRODUCT #3 -$5,662,800 $1,097,900 $2,040,200 $2,846,100 0 1 2 4 1 2025 $3,942.000 so $0 $0 $0 $0 3 Cumulative cash flows (See Problem 5-1) Discounted cash flows (See Problems 5-3 (Parta) & 5-4 (Part b)) Cumulative discounted cash flows (See Problems 5-3 (Parta) & 5-4 (Part b)) $0 $0 $0 $0 $0 4 $0 $0 $0 $0 $0 s 6 7 CAPITAL BUDGETING CALCULATIONS PRODUCT #3 # ANALYSIS: CALCULATE ACCEPTABLE or UNACCEPTABLE? Net Present Value (NPV) $0 (See Problems 5-11 (Part c) & 5-12 (Part b)) Internal Rate of Return (IRR) (See Problems 5-5 & 5-6) 0.00% Profitability Index (PI) See Problem 5-8) 0.000 1 Modified Internal Rate of Return (MIRR) (using the COMBINATION APPROACH) (See Problem 5-20) 0.00% Reinvestment rate and Finance Rate for MIRR should both be equal to the Target Rate of Return Payback Period (PP) (See Problem 5-1) 0.00 Discounted Payback Period (DPP) (See Problems 5-3 (Part a) & 5-4 (Part b)) 0.00 4 CASH FLOWS OVER PROJECT #4's LIFE Target Rate of Return 0.000% Auto Transferred from your WACC worksheet Net Cash Flows each year Year Number YEAR 2021 1 2 2022 2023 2024 2025 CAPITAL PRODUCT #4 - $7,991,400 $1,917,200 $2,298,600 $3,255,800 $5,212,400 $0 $0 $0 $0 $0 Cumulative cash flows (See Problem 5-1) Discounted cash flows (See Problems 5-3 (Parta) & 5-4 (Part b)) Cumulative discounted cash flows (See Problems 5-3 (Parta) & 5-4 (Part b)) $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 CAPITAL BUDGETING CALCULATIONS PRODUCT #4 CALCULATE ANALYSIS: ACCEPTABLE or UNACCEPTABLE? Net Present Value (NPV) (See Problems 5-11 (Partc) & 5-12 (Part b)) $0 internal Rate of Return (IRR) (See Problems 5-5 & 5-6) 0.00% 0.000 Profitability Index (PI) See Problem 5-8) Modified Internal Rate of Return (MIRR) (using the COMBINATION APPROACH) (See Problem 5-20) Payback Period (PP) (See Problem 5-1) 0.00% Reinvestment rats and Finance Rato for MRR should both be equal to the Target Rate of Return 0.00 Discounted Payback Period (DPP) (See Problems 5-3 (Part a) & 5-4 (Part b)) 0.00
