Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Explain a planned or hypothetical Capital Budgeting project for Apple,Inc and estimate the cash flows (minimum of 7 years).Determine the WACC for the company and

image text in transcribed

Explain a planned or hypothetical Capital Budgeting project forApple,Incand estimate the cash flows (minimum of 7 years).Determine the WACC for the company and show the calculations; use this WACC in the analysis.

Develop your own Excel model to evaluate the capital budgeting project using automated Excel functions for NPV, IRR, MIRR, and PI.Use the WACC as the discount rate for the analysis.And written analysis and recommendation for the capital budgeting project.

I have attached the instruction for the assignment, and I also have attached how the assignment should look like usingthe Gap Inc examples

image text in transcribed Week 4 Capital Budgeting Investment, Model and Analysis (Rev. 06/09/17) Capital Budgeting Investment, Model, and Analysis Use the company that was selected for the class assignments. This assignment involves development of an Excel-based capital budgeting model and an analysis of a proposed capital budgeting project. Prepare Capital Budgeting Model and Analysis Describe a capital budgeting project (i.e., an investment in fixed assets) that might be undertaken by the company that you have selected. Make sure that the project has an initial investment in Year 0, followed by a series of annual cash flows for at least seven (7) years. In addition, determine the discount rate, or hurdle rate, that is appropriate for this project and explain the determination of that rate. You may find some information about proposed capital budgeting projects in the Form 10K, news releases, or other company information sources. More than likely, it will be necessary to estimate the cash flows associated with the project. Develop your own Excel spreadsheet model that can be used to determine the Net Present Value (NPV), Internal Rate of Return (IRR), Modified Internal Rate of Return (MIRR), and Profitability Index (PI). The Excel spreadsheet that you develop must use Excel's automated financial functions for determining the NPV, IRR, MIRR, and PI. Following the completion of the spreadsheet analysis, explain whether, or not, the project should be implemented and why? Also, discuss what the various indicators (i.e., NPV, IRR, MIRR and PI) mean? For this assignment, it is necessary to design and develop your own Excel spreadsheet; it needs to be submitted. Beyond the traditional inputs, outputs, and assessment criteria, what new and interesting features can you incorporate into the model? The analysis needs to include: 1. Description of Proposed Capital Budgeting Project - Describe a capital budgeting project (i.e., an investment in fixed assets) that might be undertaken by the company that you have selected. Make sure that the project has an initial investment in Year 0, followed by a series of annual cash flows for at least seven (7) years. In addition, determine the discount rate, or hurdle rate, that is appropriate for this project and explain the determination of that rate. 2. Explanation of the Excel Capital Budgeting Model - Develop your own Excel spreadsheet model that can be used to determine the Net Present Value (NPV), Internal Rate of Return (IRR), Modified Internal Rate of Return (MIRR), and Profitability Index (PI). Explain how the model is constructed and the required inputs. Discuss the outputs of the model. 3. Explanation and Interpretation of Capital Budgeting Criteria - Prepare a written analysis of the results including NPV,IRR, MIRR, PI, and Profitability Analysis. 1 4. Recommendation About Implementation of Capital Budgeting Project - Develop and defend a recommendation about whether the proposed project should be implemented or not. Writing Instructions The discussion portion of the analysis should be three to five pages in length, double spaced, and should employ APA style and format for reference citations. Supporting data (e.g., figures, tables, etc.) and references should be submitted limited to four separate attachments in an appendix after the written portion of the paper. The paper should begin with a short introduction and then proceed to examine the four topics outlined in the previous section. The subheadings used in the paper should be: 1. Introduction 2. Description of Proposed Capital Budgeting Project 3. Explanation of the Excel Capital Budgeting Model 4. Explanation and Interpretation of Capital Budgeting Criteria 5. Recommendation About Implementation of Capital Budgeting Project Evaluation: 12.5% of final course grade. Completeness of analysis: The analysis must demonstrate a solid understanding of capital budgeting and the analysis of corporate investments. All assumptions used in preparing the projections for the project need to be thoroughly explained. Organization: The paper should be well-organized and follow a logical pattern of analysis and discussion. Presentation: Papers should meet professional business standards and meet APA formatting requirements. Spelling, punctuation, and grammar: There should be few errors in grammar and punctuation. All sentences must be complete and well-structured. Submission and Format: The completed paper is to be submitted to the \"Gradebook\" location designated for the assignment. The paper must be in Word format otherwise no credit is earned for the assignment. The Excel capital budgeting model is to be submitted. The paper is also to be submitted to the Online Classroom. This will allow students to examine and discuss the various projects. 2 3 4 USING FINANCIAL FUNCTIONS ON EXCEL Period 0 1 2 3 4 5 Present Cash Flow Values Future Values ($50,000) ($50,000.00) $20,000 $18,182 $29,282 $10,000 $8,264 $13,310 $20,000 $15,026 $24,200 $20,000 $13,660 $22,000 $20,000 $12,418 $20,000 PV of Outflows ($50,000) PV of Inflows $67,551 FV of Inflows $108,792 Net Present Value Discount Rate $17,551.27 =NPV(I3,B5:B9)+D4 Internal Rate of Return 22.50% =IRR(B4:B9,0.01) Modified IRR 16.82% =MIRR(B4:B9,I3,I3) Discount Rate 0.00% 6.00% 7.00% 8.00% 9.00% 10.00% 11.00% 12.00% 13.00% 20.00% 30.00% Net Present Value $40,000.00 $25,347.31 $23,269.56 $21,280.81 $19,376.23 $17,551.27 $15,801.72 $14,123.59 $12,513.16 $2,867.80 ($7,205.76) 5 10.00% ($50,000) 0 $108,792 Net Present Value Calculator MIRR N I PV of Outflows PMT FV of Inflows 10.00% Excel Functions MIRR = 16.82% =MIRR(B4:B9,I3,I3) Net Present Value Profile $50,000.00 $40,000.00 $30,000.00 $20,000.00 $10,000.00 $0.00 0.00% ($10,000.00) 5.00% 10.00% 15.00% 20.00% 25.00% 30.00% 35.00% Discount Rate Net Present Value USING FINANCIAL FUNCTIONS ON EXCEL Period 0 1 2 3 4 5 Present Future Cash Flow Values Values ($50,000) ($50,000.00) $20,000 $18,182 $29,282 ($10,000) ($8,264) $20,000 $15,026 $24,200 $20,000 $13,660 $22,000 $20,000 $12,418 $20,000 PV of Outflows ($58,264) PV of Inflows $59,287 FV of Inflows $95,482 Discount Rate Net Present Value $1,022.35 =NPV(I3,B5:B9)+D4 Internal Rate of Return 10.70% =IRR(B4:B9,0.01) Modified IRR 10.38% =MIRR(B4:B9,I3,I3) Discount Net Present Rate Value 0.00% $20,000.00 6.00% $7,547.38 7.00% $5,800.79 8.00% $4,134.04 9.00% $2,542.63 10.00% $1,022.35 11.00% ($430.73) 12.00% ($1,820.29) 13.00% ($3,149.78) 20.00% ($11,021.09) 30.00% ($19,040.08) 5 10.00% ($58,264) 0 $95,482 Net Present Value Calculator MIRR N I PV of Outflows PMT FV of Inflows 10.00% MIRR = 10.38% =RATE(D20,D23,D22,D24) Net Present Value Profile $25,000.00 $20,000.00 $15,000.00 $10,000.00 $5,000.00 $0.00 0.00% 5.00% 10.00%15.00%20.00%25.00%30.00%35.00% ($5,000.00) ($10,000.00) ($15,000.00) ($20,000.00) ($25,000.00) Discount Rate Net Present Value PAYBACK METHOD Payback Period Payback period is the number of years it takes to recover the initial investment It is the number of years until the cumulative cash benefit equals the cash invested Because of simplicity, Payback was a principal capital budgeting criterion in practice for a number of years Often considered to be a risk measure - managers think that the longer it takes to recover an investment, the riskier the project Decisions Accept Project if Payback Period Maximum Acceptable Period Advantages Uses cash flows Easy to calculate and to understand Useful as a rough screening device and indicator of risk Weaknesses Ignores the time value of money Ignores all cash flows after the payback period Selection of the payback period is arbitrary Does not provide any indication of the absolute change in shareholder wealth Other Often used by companies for investments involving small amounts Helpful decision tool for companies that are short of cash Inappropriate for large, significant investment decisions PAYBACK PERIOD EXAMPLE Period 0 1 2 3 4 5 6 7 Payback Period = Payback = Payback 2 Year Before Full Recovery of Investment = = 1 1 Cash Flows Accumulated Cash Flows ($10,000) $6,000 $4,000 $3,000 $2,000 $1,000 $1,000 $1,000 2.00 ($4,000) $0 $3,000 $5,000 $6,000 $7,000 $8,000 Years + Unrecovered Cost at Start of Year Total Cash Flow During Year + + $4000 / $4000 1 DISCOUNTED PAYBACK METHOD Discounted Payback Period Discounted payback period is the number of years it takes to recover the initial investment from discounted cash flows Decisions Accept Project if Discounted Payback Period Maximum Acceptable Period Advantages Uses discounted cash flows Relatively easy to calculate and to understand Useful as a rough screening device and indicator of risk Recognizes the time value of money Weaknesses Ignores all cash flows after the payback period Selection of the payback period is arbitrary Does not provide any indication of the absolute change in shareholder wealth Other Often used by companies for investments involving small amounts Helpful decision tool for companies that are short of cash Inappropriate for large, significant investment decisions DISCOUNTED PAYBACK PERIOD EXAMPLE Period 0 1 2 3 4 5 6 7 17.000% Discount Rate Cash Flows Discounted Cash Flows ($10,000) $6,000 $4,000 $3,000 $2,000 $1,000 $1,000 $1,000 $5,128 $2,922 $1,873 $1,067 $456 $390 $333 Payback Period = Year Before Full Recovery of Investment Using Discounted Cash Flows Payback = Payback 3.07 = = 3 3 Accumulated Cash Flows Unrecovered Cash Flows $5,128 $8,050 $9,923 $10,991 $11,447 $11,837 $12,170 3.07 + + + ($4,872) ($1,950) ($77) $991 $1,447 $1,837 $2,170 Years Unrecovered Discounted Cash Flow at Start of Year Total Discounted Cash Flow During Year $77 / $1067 0.07 NET PRESENT VALUE (NPV) METHOD Net Present Value Present value of the annual cash flows, less the project's initial outlay NPV = CF 0 + CF 1 + CF 2 + CF 3 + ( 1 + k )1 ( 1 + k )2 ( 1 + k )3 CF n ------------- ( 1 + k )n Decisions Accept if the NPV > 0, which indicates that the cash flows are sufficient to cover return of invested capital and provide positive return NPV > 0 means that the project pays all of its costs, including financing costs, and provides adequate returns to shareholders Reject the project if the NPV 0 NET PRESENT VALUE (NPV) EXAMPLE 17.000% Period 0 1 2 3 4 5 6 7 8 9 10 Discount Rate Present Value Factors Cash Flows ($10,000) $6,000 $4,000 $3,000 $2,000 $1,000 $1,000 $1,000 $1,000 $1,000 $1,000 NPV = $2,906 $2,906 Discounted Cash Flows 0.8547 0.7305 0.6244 0.5337 0.4561 0.3898 0.3332 0.2848 0.2434 0.2080 $5,128 $2,922 $1,873 $1,067 $456 $390 $333 $285 $243 $208 Present Value of CFs $12,906 + Initial Investment ($10,000) + ($10,000) + Excel Function $12,906 =NPV(B4,B9:B18) Calculator Solution 1. Enter the CFLO Register 2. Enter Cash Flows Cash Flow 0 ($10,000) Cash Flow 1 $6,000 Cash Flow 2 $4,000 Cash Flow 3 $3,000 Cash Flow 4 $2,000 Cash Flow 5 $1,000 Cash Flow 6 $1,000 Cash Flow 7 $1,000 Cash Flow 8 $1,000 Cash Flow 9 $1,000 Cash Flow 10 $1,000 3. Exit Cash Flow Register 4. Push "CALC" Key Enter 1 time 1 time 1 time 1 time 1 time 1 time 1 time 1 time 1 time I time 5. Enter "Hurdle Rate" 6. Push "NPV" Key INTERNAL RATE OF RETURN (IRR) METHOD Internal Rate of Return The IRR is the discount rate that equates the present value of a project's future cash flows with the project's initial outlay It is the discount rate that produces a NPV = 0 Tries to find a single number that summarizes the merit of a project IRR = 0 = CF 0 + CF 1 + CF 2 + CF 3 + 2 CF n ------------- ( 1 + IRR ) ( 1 + IRR ) ( 1 + IRR ) 1 3 ( 1 + IRR )n Decisions If IRR > "Hurdle Rate" accept the project If IRR "Hurdle Rate" then shareholder wealth is created by a project NPV and IRR can give conflicting signals about "mutually exclusive" projects Advantages Uses cash flows - does not use accounting constructs like earnings Recognizes the time value of money; all cash flows are discounted Consistent with goal of shareholder wealth maximization Utilizes all cash flows of the project Easy to compute with financial calculator or computer Weaknesses Possibility of multiple IRRs; there may be an IRR for each time that there is a change in the direction of cash flows (Descartes' Rule of Signs) IRR assumes reinvestment of cash flows at IRR; NPV assumes reinvestment of cash flows at company's required rate of return or "Hurdle Rate" Difficult to compute without calculator or computer INTERNAL RATE OF RETURN (IRR) EXAMPLE Period 0 1 2 3 4 5 6 7 8 9 10 IRR = Cash Flows ($10,000) $6,000 $4,000 $3,000 $2,000 $1,000 $1,000 $1,000 $1,000 $1,000 $1,000 Excel Function 29.85% =IRR(D7:D17,0.01) Calculator Solution 1. Enter the CFLO Register 2. Enter Cash Flows Cash Flow 0 ($10,000) Cash Flow 1 $6,000 Cash Flow 2 $4,000 Cash Flow 3 $3,000 Cash Flow 4 $2,000 Cash Flow 5 $1,000 Cash Flow 6 $1,000 Cash Flow 7 $1,000 Cash Flow 8 $1,000 Cash Flow 9 $1,000 Cash Flow 10 $1,000 3. Exit Cash Flow Register 4. Push "CALC" Key 5. Push "IRR" Key Enter 1 time 1 time 1 time 1 time 1 time 1 time 1 time 1 time 1 time I time PROFITABILITY INDEX (PI) METHOD Profitability Index Ratio of the present value of the future cash flows to the initial outlay Objective is to develop a measure for comparing the relative profitability of different projects The PI shows the value increase per dollar invested PI is an index number for NPV If the NPV is positive, the PI will be greater than 1.0; if the NPV is negative, the PI will be less than 1.0 Profitability Index = Present Value of Cash Flows Subsequent to Initial Investment Initial Investment Decisions For independent projects, accept the project if the PI > 1.0; reject the project if the PI "Hurdle Rate" accept the project If MIRR "Hurdle Rate" then shareholder wealth is created by a project Advantages Uses discounted cash flows Assumes reinvestment of cash flows at "hurdle rate" correcting the deficiency of IRR Eliminates possibility of multiple rates of return, a problem with IRR Weaknesses NPV still provides a better measure of overall contribution of project to shareholder wealth MODIFIED INTERNAL RATE OF RETURN (MIRR) EXAMPLE 17.000% Discount Rate Period 0 1 2 3 4 5 6 7 8 9 10 Leave Cells Blank for Periods After the End of the Project Cash Flows ($10,000) $6,000 $4,000 $3,000 $2,000 $1,000 $1,000 $1,000 $1,000 $1,000 $1,000 Present Value of Outflows Future Value Factors ($10,000) 4.108 3.511 3.001 2.565 2.192 1.874 1.602 1.369 1.170 1.000 MIRR Excel Function MIRR = 20.02% =MIRR(E7:E17,E3,E3) IRR Calculated for Comparison IRR = 29.85% Calculator Solution N 10 I ? 20.02% PV ($10,000) Future Value of Inflows PMT 0 FV $62,037 $24,650 $14,046 $9,004 $5,130 $2,192 $1,874 $1,602 $1,369 $1,170 $1,000 $62,037 Running Header: GAP INC. CAPITAL PROJECT The Gap, Inc. - Analysis of Capital Project Proposal University of Maryland, University College FINC 430 1 GAP INC. CAPITAL PROJECT 2 The Gap, Inc. - Analysis of Capital Project Proposal I. Introduction Historically, The Gap, Inc. (GPS) has invested quite heavily in capital projects to enhance competitive advantage. The majority of GPS' capital expenditures in recent years have been related to the opening of new stores or enhancement of current stores among its five brands: Gap, Old Navy, Banana Republic, Athleta, and Intermix [The16]. In 2015, 77% of GPS' sales were in the United States, 5% in Europe, 7% in Canada, 10% in Asia, and 1% in \"Other regions\" [Gra02]. To continue expanding internationally and compete in the global apparel industry, it is necessary for GPS to consider capital projects and conduct analysis to determine which project/projects add the most value for GPS. This paper will first describe the proposed capital budgeting project for GPS. An explanation of the Excel Capital Budgeting Model used in the analysis will follow. All criteria used in the analysis, including the weighted average cost of capital (WACC), the net present value (NPV), the internal rate of return (IRR), the modified internal rate of return (MIRR), and the profitability index (PI) will be explained and interpreted. The final section of this paper will recommend whether or not the proposed project should be implemented by The Gap, Inc. II. Description of Proposed Capital Budgeting Project Among GPS' brands, Old Navy has been performing better than the others in recent years. In 2015, Old Navy's global sales increased by one percent, compared to sales decreases in all other brands [The16]. Old Navy is the only brand which has seen increases in sales during the last three fiscal years [The16]. In fact, Old Navy's sales made up the largest portion of GPS' sales in 2014 and in 2015. According to GPS' 2015 Annual Report, GPS will \"continue to invest... GAP INC. CAPITAL PROJECT 3 through significant investments in... international expansion...\" [The16]. With expanding its global presence a top priority, GPS has been adding stores around the world. Asia has been a particularly profitable region for Old Navy in recent years. GPS opened 25 new Old Navy stores in Asia in 2014, while it opened 22 new Old Navy stores in Asia in 2015. Sales have increased for Old Navy Asia stores lately; sales were $77 million in 2013, $149 in 2014, and $194 million in 2015 [The16]. The proposed project for GPS is to continue the expansion of the Old Navy brand by opening 30 new Old Navy stores in Asia. The acquisition of the required property and equipment needed for these 30 new stores would cost approximately $77 million dollars. An additional $15 million of inventory would be needed to open these 30 stores. In addition to the aforementioned initial outlay of $92 million, operating expenses for each store would cost over $1 million annually, totaling approximately $33.8 million annually for the thirty stores. Additional advertising expenses would cost an estimated $4.7 million annually. In the first year, GPS would experience increases in sales of approximately $61.4 million. The project will be financed through the issuance of debt. III. Explanation of the Excel Capital Budgeting Model The attached Excel Capital Budgeting Model was created to calculate various indicators used to evaluate a capital project. The first tab uses inputs from the second tab to calculate all cash flows for seven years, the WACC, NPV, IRR, MIRR, and PI. The second tab, \"Supporting Calculations and Input\

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Fundamentals of Investments, Valuation and Management

Authors: Bradford Jordan, Thomas Miller, Steve Dolvin

8th edition

1259720697, 1259720691, 1260109437, 9781260109436, 978-1259720697

More Books

Students also viewed these Finance questions

Question

c. What is the persons contact information?

Answered: 1 week ago