We are working on this team assignment and not agreeing on whether the spreadsheet and graphs are correct. Is it possible for this to be double checked to see if we are on the right path or set us down the right path. If it's wrong can we get an explanation of why it should be changed? thank you!

Alisha

MBA/MSF 655, Financial Management and Strategy Wk06, Capital Budgeting Team Activity Page 1 of 3 MBA/MSF 655, Financial Management and Strategy Capital Budgeting Project: Excel Analysis Model, Word Report and PowerPoint Presentation Assignment Background This capital budgeting analysis and professional report project is a required component of the MBA/MSF 655 course and is also an activity you should save as part of your personal MBA/MSF eportfolio as an example & representation of work completed during the MBA/MSF program. This project is to be completed through engaged and collaborative teamwork within your MBA/MSF 655, Financial Management and Strategy groups. To maximize the educational value and return on investment of this analysis activity, each individual team member should thoroughly engage in and understand all aspects of the project's analysis, organization and presentation. You will be able to utilize this example of your work to demonstrate your knowledge of core valuation and capital budgeting analysis concepts, as well as your organization, presentation and communication skills. Submission of your team's final work: Your team's final work should be submitted to your team's Group Discussion Board area via attachments to posts with a \"final product\" subject line. This final product is a graded component for the course. Personal portfolio of work: I encourage everyone to keep a copy of your capital budgeting activity for your personal e-portfolio as an example and representation of work you completed during your MBA/MSF program. To maintain individual confidentiality to your individual e-portfolio and to also clearly acknowledge the teamwork experience (which employers appreciate & value), each team member should include the following statement on your individual e-portfolio copy: \"I, (insert name), submit the following project as an example and representation of the work I was actively engaged in and contributed to during my MBA/MSF Program. This project was completed through engaged and collaborative teamwork with my MBA/MSF team members for this course.\" Assignment Tasks, Resources, Requirements & Deliverables This project integrates multiple elements of valuation, capital budgeting, financial management, communication, and presentation principles. Resources: The parameters of the capital budgeting problem are provided below. An Excel template is provided for your team's use in your Excel analysis. The Excel template provides a general structure for building the analysis within Excel. Requirements and Deliverables: Your team should compile and complete the Excel analysis of the capital budgeting problem, and then prepare a professional quality Word document report and a PowerPoint presentation of capital budgeting in general and of this analysis. All deliverables (analysis, report, PPT) should be of professional quality and representative of materials appropriate to deliver to your Board of Directors or your client. The required deliverables are listed below. MBA/MSF 655, Financial Management and Strategy Wk06, Capital Budgeting Team Activity Page 2 of 3 Deliverables: 1. Excel spreadsheet analysis, with an NPV Profile graph, and NPV Scenario / Risk Analysis Grids (see template). The NPV Scenario / Risk Analysis Grids will present NPV values versus changing values of the cost of capital, salvage value, and sales. Use the Excel template provided for this assignment for completing the Excel analysis. Your Excel spreadsheet should include the names of all team members who contributed to the work. 2. Word document professional report that provides an overview of capital budget and your analysis, including the items recommended below. Your Word report should be of the quality that you would be proud to submit to your Board of Directors or investors (or other clients) regarding your firm's capital budgeting processes and your capital budgeting analysis of this particular project. Your report and PPT should NOT refer to \"the assignment\" but rather it should address the case as though it is your firm's project. Similarly, your reports (Word & PPT) should have a table of contents (Word) and agenda (PPT) for all the appropriate sections, and should NOT address the items below in an \"A, B, C... \" format, but rather you should integrate the items into your report and your sections as appropriate.: 2A. An overview and summary of the general capital budgeting process and how it is implemented within organizations, 2B. A glossary of capital budgeting related concepts and terms (alphabetically listed) with definitions and summary comments regarding the implications of the concepts and terms. This would likely best be presented in an appendix, and thus referred to within your review of capital budgeting key concepts, processes and applications. These definitions MUST BE WRITTEN IN YOUR OWN WORDS and CANNOT BE COPIED DIRECTLY FROM ANY SOURCE. You should provide a meaningful definition, as well as some discussion/comments regarding the use and application of the concept. At a minimum, the key concepts and terms should include: 1. ATSV, 2. Cash flow analysis (time zero initial investment cash flows, operating life cash flows, terminal period end of project cash flows), 3. cash flow timeline, 4. conventional cash flow stream, non-conventional cash flow stream, 5. discount rate & cost of capital concept (and implications to valuation) 6. independent projects, 7. IRR - multiple IRRs, 8. IRR, 9. mutually exclusive projects, 10. NPV = 0 meaning and implications, 11. NPV Profile, 12. NPV, 13. opportunity cost, 14. Payback Period, 15. profitability index, 16. ranking conflict of NPV vs. IRR, 17. sunk cost, 2C. A summary report of your capital budgeting analysis, including tables and graphs illustrating the analysis and the results of the analysis. 2D. Based on your NPV Scenario / Risk Analysis Grids, is NPV more sensitive to changing cost of capital or changing salvage values? How do you determine this? MBA/MSF 655, Financial Management and Strategy Wk06, Capital Budgeting Team Activity Page 3 of 3 2E. Based on your NPV Scenario / Risk Analysis Grids, is NPV more sensitive to changing cost of capital or changing year one sales level assumptions? How do you determine this? 3. PowerPoint professional presentation that provides the following: 3A. An overview & presentation of the key ideas & fundamental concepts of the general capital budgeting concept and process and how it is implemented within organizations. Your PPT presentation should explain all key terms relevant to the capital budgeting process & implementation. Some terms/concepts may be in the main body of the PPT, while you may choose to put some terms/concepts in an Appendix or Glossary. 3B. A thorough presentation of your capital budgeting analysis (i.e. the problem being analyzed). This portion should include an introduction / overview / summary to the problem, the analysis presentation, and then the conclusions & recommendations for that project with some explanation / indication of why those recommendations are being made. Additionally, this portion should include tables and graphs illustrating the analysis and the results of the analysis. Note: your PPT presentation should demonstrate PPT best practices. In particular, there should not be excessively wordy slides. Bulleted lists and key words should be utilized. Each slide should convey a clear message. Graphs and tables should be clear, with proper labels and captions. Capital Budgeting Problem Parameters: Consider the following expansion capital budgeting problem. A capital budgeting decision is being considered that would involve an expansion and simultaneous replacement of old equipment. The project is expected to have a 6 year life for the firm. This project will replace some existing equipment which currently has a book value (BV) of $200k and an estimated market salvage value of $375k. The new project will require new equipment costing $2000k, which will be depreciated straight-line to a book value of $200k at the end of 6 years. Due to new energy efficient technology, replacing the old equipment with the new more efficient equipment will generate an immediate tax credit of 5% of the equipment's cost. The expansion will require an additional investment in NWC of $200k. Sales are expected to increase by $1000k the first year and grow by 15% in years 2 and 3, then by 5% annually during the remaining 6 year life. Cost of goods sold is forecasted to be 45% of the increased sales, and other selling and general administrative expenses are forecasted to be 10% of the increased sales. It is forecasted that the new equipment will have a salvage value of $300k at the end of the project's 6 year life. The firm's weighted average cost of capital (WACC) for projects of this risk level is 8%. The firm's marginal tax rate is T = 40%. Use the Excel template to complete the capital budgeting analysis. Your Excel analysis should clearly indicate the cash flow analysis timeline and should provide the project's NPV, IRR, PBP, PI, and also illustrate the project's NPV Profile. *** END *** 2 Yellow highlighted cells are cells for inputs. Team should verify all other calculations & formats 3C D E F G H I J K 4 Inputs 5 ATSV old @ t=0 305000 ATSV formula = SV - (SV-BV)*T 6 Equipment 2,000,000 7 Tax Credit 100,000 8 Depreciaton per year 300,000 9 Sales period 1 1,000,000 growth: g yrs 2-3 = 15% g yrs 4-6 = 5% 10 CoGS %of sales 45% 11 SG&A exp. %of sales 10% 12 ATSV new @ t=6 10% 13 14 Operating Life CFs 15 Time 0 1 2 3 4 5 6 16 Sales $1,000,000 $1,150,000 $1,322,500 $1,388,625 $1,458,056.25 $1,530,959 17 - COGS 450,000 517,500 595,125 624,881 656,125 688,932 18 - SG&A expenses 100,000 115,000 132,250 138,863 145,806 153,096 19 - Depreciation 300,000 300,000 300,000 300,000 300,000 300,000 20 = EBIT 150,000 217,500 295,125 324,881 356,125 388,932 21 -Taxes (40%) 60,000 87,000 118,050 129,953 142,450 155,573 22 = Net Income 90,000 130,500 177,075 194,929 213,675 233,359 23 + Depreciation 300,000 300,000 300,000 300,000 300,000 300,000 24 = Operating CF 390,000 430,500 477,075 494,929 513,675 533,359 25 26 Time 0 Investments 27 Equipment 2,000,000 28 ATSV old 305,000 29 Tax credit 100,000 30 NWC 200,000 31 32 Terminal Non-OCF: 33 ATSV new @ t=6 260,000 34 NWC 200,000 35 = Net Cash Flow $2,605,000 $390,000 $430,500 $477,075 $494,929 $513,675 $993,359 36 = Cummulative CF $2,605,000 $2,995,000 $3,425,500 $3,902,575 $4,397,504 $4,911,179 $5,904,538 37 38 Cost of Capital 8% 39 NPV $158,406 40 IRR = 10.4% 41 PBP = 4.4 42 PI = 43 44 NPV Data Table 45 Rates 46 -2,000,000 47 390,000 48 430,500 49 477,075 50 494,929 51 513,675 52 533,359 53 NPV 54 55 56 L 1.08 PI= NPV -2,000,000 361111.111 369084.362 378717.516 363787.406 349598.701 336106.609 158,406 M N P Q R S NPV Scenario / Risk Analysis: Complete the grids below to report 5x5 Grids of NPV vs input variable changes noted NPV Analysis Grid: NPV vs Discount Rate & Salvage Value Ranges SV -> SV-20% SV-10% SV Base SV+10% 240000 270000 300,000 330000 ATSV -> ATSV-20% ATSV-10% ATSV Base ATSV+10% $NPV in Cells: Cost of Capital 4% 6% 8% $41,594 10% 12% SV+20% 360000 ATSV+20% NPV Analysis Grid: NPV vs Discount Rate & Year 1 Sales Ranges Sales Yr.1 -> $NPV in Cells: Cost of Capital 4% 6% 8% 10% 12% 361111.111 yr1 369084.362 yr2 378717.516 yr3 363787.406 yr4 349598.701 yr5 336106.609 yr6 1.07920285 PI O Sales Yr.1 -20% 800000 -$1,430,769 -$1,445,283 -$1,459,259 -$1,472,727 -$1,485,714 Sales Yr.1 -10% 900000 -$1,334,615 -$1,350,943 -$1,366,667 -$1,381,818 -$1,396,428 Sales Yr.1 Base 1,000,000 -$1,238,461 -$1,256,603 -$1,274,074 -$1,290,909 -$1,307,142 Sales Yr.1 +10% 1100000 -$1,142,308 -$1,162,264 -$1,181,481 -$1,200,000 -$1,217,857 Sales Yr.1 +20% 1200000 -$1,162,264 -$1,067,925 -$1,088,889 -$1,109,091 -$1,128,571 NPV Analysis Grid: NPV vs Discount Rate & Year 1 Sales Ranges NPV Analysis Grid: NPV vs Discount Rate/ Sales Ranges 1400000 1200000 1000000 1100000 1200000 Sales Yr.1 Sales Yr.1 Sales Yr.1 Sales Yr.1 Sales Yr.1 Sales Yr.1 -> -20% -10% Base +10% +20% $NPV in Cells: 800000 900000 1000000 1100000 1200000 Cost of Capital 4% -1430769 -1334615 -1238461 -1142308 -1162264 0.06 -1445283 -1350943 -1256603 -1162264 -1067925 0.08 -1459259 -1366667 -1274074 -1181481 -1088889 0.1 -1472727 -1381818 -1290909 -1200000 -1109091 T