Submission of your team’s final work: Your team’s final work should be submitted to your team’s Group Discussion Board, Capital Budgeting folder area via attachments to posts with a “Final Product”...

1 answer below »
Please see the word doc and excel sheet


Submission of your team’s final work: Your team’s final work should be submitted to your team’s Group Discussion Board, Capital Budgeting folder 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. Academic Honesty Expectations:  In addition to the University’s Academic Honesty expectations, the following expectations apply for this course.  All work in this course should be solely your own individual work and creation, written/prepared in your own words, and prepared solely for credit in this course.  For any team activity, your team’s work should be solely the result of engaged and collaborative teamwork by your team members for this course. Any evidence of using other people’s work (or your own work without prior authorization) from prior or current courses with similar assignments may result in a score of zero for the activity, a grade of F for the course and/or additional discipline by the College of Business and/or the University. 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 25 different NPV values associated with changing values of the cost of capital, cost of goods sold percentage (CoGS %), and sales. Use the Excel template provided for this assignment for completing the Excel analysis. At a minimum, your Excel spreadsheet analysis should include: the names of all team members who contributed to the work, the date of your analysis; your NPV Profile, use of Excel’s NPV and IRR functions. 1. Word document professional report that provides an overview of capital budget and your specific capital budgeting analysis, including items noted 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 materials 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. At a minimum, your Word report and PPT presentation should include: the names of all team members who contributed to the work, the date of your analysis; a table of contents (PPT agenda); an overview and summary of the general capital budgeting process and how it is implemented within organizations; how CFs are modeled along with the complete CF Model for the project; the key capital budgeting decision tools, how they are computed, their pros & cons, and how they are used to make a decision regarding projects; an NPV Profile and discussion of the NPV profile.. In addition to the thorough overview of the capital budgeting process, tools, technique and key terms in general, your Word report and PPT should provide specific information regarding the case analysis, such as input data/assumptions, cash flow modeling (tables to illustrate), analysis & decision tools applications and results (tables of results), NPV Profile and discussion, etc. 2B. A glossary of capital budgeting concepts and terms (alphabetical) with definitions and comments regarding the implications of the concepts. Provide this in an appendix (at the end), and refer to it 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, 1. Cash flow analysis (time zero initial investment cash flows, operating life cash flows, terminal period end of project cash flows), 1. cash flow timeline, 1. conventional cash flow stream, non-conventional cash flow stream, 1. discount rate & cost of capital concept (and implications to valuation) 1. independent projects, 1. IRR - multiple IRRs, 1. IRR, 1. mutually exclusive projects, 1. NPV = 0 meaning and implications, 1. NPV Profile, 1. NPV, 1. opportunity cost, 1. Payback Period, 1. profitability index, 1. ranking conflict of NPV vs. IRR, 1. sunk cost, 2C. REMINDER: As noted in 2A, thoroughly address 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 Cost of Goods Sold (CoGS) percentages? How do you determine this? Discussions of items 2D & 2E can be relatively brief. 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? 1. 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, (see 2A above for additional details) 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 model analysis timeline and should provide the project’s: 1. NPV, 1. IRR, 1. PBP, 1. PI, 1. Scenario Analysis => completion of the scenario analysis grids which provide NPV of the entire cash flow model under the different scenarios indicated in the grids, and 1. the project’s NPV Profile graph (with proper labels). *** END *** CapBud Template 1 2Yellow highlighted cells are cells for inputs. Team should verify all other calculations & formats 3CDEFGHIJKLMNOPQRST 4Inputs 5ATSV old @ t=0ATSV formula =Names of all members who contributed:Include an NPV Profile 6Equipment1 7Tax Credit2 8Depreciaton per year3 9Sales period 1growth:g yrs 2-3 =15%g yrs 4-6 =5%4 10CoGS %of sales5 11SG&A exp. %of sales6 12ATSV new @ t=6 13 14Operating Life CFs 15Time0123456 16Sales 17- COGS 18- SG&A expenses 19- Depreciation 20= EBIT 21-Taxes (40%) 22= Net IncomeNPV Scenario / Risk Analysis: 23+ DepreciationComplete the grids below to report 5x5 Grids of NPV vs input variable changes noted 24= Operating CF 25 26Time 0 InvestmentsNPV Analysis Grid: NPV vs Discount Rate & Cost of Goods Sold (CoGS) Percent Ranges 27Equipment 28ATSV oldCoGS ->CoGSbase%-20%CoGSbase%-10%CoGS Base % CoGSbase%+10%CoGSbase%+20% 29Tax creditCoGS ->i.e base%*0.8i.e base%*0.9i.e base%*1.0i.e base%*1.1i.e base%*1.2 30NWCCoGS -> 31Cost of Capital 4% 32Terminal Non-OCF:6% 33ATSV new @ t=68% 34NWC10% 35= Net Cash Flow$0$0$0$0$0$0$012% 36= Cummulative CF$0$0$0$0$0$0$0 37 38Cost of Capital 8% 39NPV 40IRR =NPV Analysis Grid: NPV vs Discount Rate & Year 1 Sales Ranges 41PBP = 42PI =Sales Yr.1 ->Sales Yr.1 -20%Sales Yr.1 -10%Sales Yr.1 BaseSales Yr.1 +10%Sales Yr.1 +20% 43$NPV
Answered Same DayOct 10, 2021

Answer To: Submission of your team’s final work: Your team’s final work should be submitted to your team’s...

Ishmeet Singh answered on Oct 13 2021
143 Votes
Problem Statement:
Inputs Given:
    All Values are in Thousands
    
    
    
    
    Yellow highlighted cells are cell
s for inputs. Team should verify all other calculations & formats
    
    
    Inputs
    
    
    
    
    
    ATSV old @ t=0
    305
    
    ATSV formula =
    SV - taxrate(SV-BV)
    
    
    Equipment
    2000
    
    
    375-40%*(375-200)
    
    Tax Credit
    5%
    
    
    
    
    
    Depreciaton per year
    
    200
    
    
    
    
    
    Sales period 1
    
    growth:
    g yrs 2-3 =
    15%
    g yrs 4-6 =
    5%
    CoGS %of sales
    45%
    
    
    
    
    
    SG&A exp. %of sales
    10%
    
    
    in thousands
    
    
    ATSV new @ t=6
    260
    
    
    as salvage value at t=6 is $300
    
Other Inputs from problem statement
APPROACH:
Initially a scenario manager has been made in excel using the what if analysis by the name of Grid -1 & Grid – 2. So, that change in values of NPV can be formulated. Two dummy variable were made by the name of Coefficient of Change in COGS & Sales.
Based on these values 2 scenario managers were made. The value inherited from these outputs was used as an input in data tables of sensitivity analysis.
ANALYSIS:
    NPV Scenario...
SOLUTION.PDF

Answer To This Question Is Available To Download