CAPITAL BUDGETING CALCULATIONS AND ANALYSIS NOTE: Cells shaded in YELLOW are the cells you must provide the calculations. NOTE: Cells shaded in RED are the cells that will automatically calculate. ALWAYS USE CELL REFERENCES to the correct values on your worksheets to do your calculations. HELP: Be sure to review the "18-SAMPLE COMPANY CAPITAL BUDGETING CALCULATIONS & ANALYSIS.pdf" provided to help you setup your spreadsheet for these CAPITAL BUDGETING calculations. CAPITAL BUDGETING CALCULATIONS Cumulative cash flows = PREVIOUS YEAR'S Cumulative CASH FLOW + CURRENT YEAR'S NET CASH FLOW Discounted cash flows = -PV(RATE=Target Rate of Return, Nper= Year Number, PMT=0,FV=Net Cash Flow each year, Type=0) Cumulative discounted cash flows = PREVIOUS YEAR'S Cumulative DISCOUNTED CASH FLOW + CURRENT YEAR'S DISCOUNTED CASH FLOW CAPITAL PROJECT #1 Required Rate of Return = 0.000% Automatically transfers from your WACC worksheet. Annual Cash Flows At End of Year Year Number YEAR 0 2 3 4 2024 2025 2026 2027 2028 ANNUAL CASH FLOW = -$4,461,200 $1,411,100 $1,474,300 $1,640,300 $2,819,500 Cumulative cash flows $0 $0 $0 $0 (See Problem 5-1 Part a-1) Discounted cash flows $0 $0 $0 $0 $0 (See Problems 5-3 & 5-4) Cumulative discounted cash flows (See Problems 5-3 & 5-4) CAPITAL BUDGETING CALCULATIONS FOR CAPITAL PROJECT #1 Net Present Value (NPV) Round to the nearest whole dollar (e.g., $1,234) (See Problems 5-11 (Part c-1) & 5-12 (Part b-1)) Internal Rate of Return (IRR) CALCULATE $0 $0 $0 $0 $0 $0 $0 ANALYSIS: ACCEPTABLE or UNACCEPTABLE? Enter as a percentage rounded to 2 decimals (e.g., 1.23%) 0.00% (See Problems 5-5 & 5-6) Modified Internal Rate of Return (MIRR) (using the COMBINATION APPROACH) Enter as a percentage rounded to 2 decimals (e.g., 1.23%) (See Problem 5-20c) Reinvestment rate and Finance Rate 0.00% for MIRR should both be the Required Rate of Return. Capital Budgeting General Information Kim Holland, CFO 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 concerned 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. Ms. Holland knows that selecting projects purely on that basis would be incorrect; but she wasn't sure of her ability to convince the CEO, 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 the CEO's office, Ms. Holland 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 her for their capital projects to be considered and she will need your help to evaluate these projects: CAPITAL PROJECT 1 - Global Manufacturing is considering expanding its production of very successful model LD1103 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, Ms. Holland 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). She knew that the CEO liked all projects to payback within 4 years. Another constraint that she must deal with is that the CEO 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, the Board of Directors has imposed a capital spending limit (called Capital Rationing) this year which is shown on your CASE STUDY DATA SHEET 5) CALCULATE: Internal Rate of Return (IRR) (Do not include the percent sign (%). Round your answer to 2 decimal places. (e.g., 32.16)) i. ANALYSIS: (Select from a "drop down list" 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: Modified Internal Rate of Return (MIRR) using the combination approach only. (Do not include the percent sign (%). Round your answer to 2 decimal places. (e.g., 32.16)) i. ANALYSIS: (Select from a "drop down list" question): After you have completed your calculations for MIRR evaluate each project based on your findings and determine if each project is either ACCEPTABLE or NOT ACCEPTABLE based on MIRR acceptance criterion. 7) CALCULATE: Profitability Index (PI) (Round your answer to 2 decimal places. (e.g., 32.16)) i. ANALYSIS: (Select from a "drop down list" question): After you have completed your calculations for Pl evaluate each project based on your findings and determine if each project is either ACCEPTABLE or NOT ACCEPTABLE based on PI acceptance criterion. 8) CALCULATE: Payback Period (Round your answer to 2 decimal places. (e.g., 32.16). If payback does not occur in 4 years enter 0.00 for NO PAYBACK) i. ANALYSIS: (Select from a "drop down list" 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 payback period the CEO expects projects to payback within 4 years.) 9) CALCULATE: Discounted Payback Period (Round) answer to 2 decimal places. (e.g., 32.16). If payback does not occur in 4 years enter 0.00 for NO PAYBACK) 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 period the CEO expects projects to payback within 4 years.) Profitability Index (PI) Enter as a value rounded to 3 decimals (e.g., 1.234) (See Problem 5-8 Part a) 0.000 Payback Period (PP) Enter as a value rounded to 2 decimals (e.g., 1.23) (See Problem 5-1, Part a-1) 0.00 Discounted Payback Period (DPP) Enter as a value rounded to 2 decimals (e.g., 1.23) (See Problems 5-3 & 5-4) CAPITAL PROJECT #2 Required Rate of Return= 0.00 0.000% Annual Cash Flows At End of Year 2 3 4 2027 2028 $1,846,300 2026 $2,486,700 Year Number YEAR 0 2024 2025 ANNUAL CASH FLOW = -$5,181,500 Cumulative cash flows (See Problem 5-1 Part a-1) $0 $0 $0 $2,431,100 $0 $3,123,100 $0