I am working on a personal project to keep for a portfolio but am needing help getting started and figuring out excel equations. I am having trouble

E Arial 11 A A A A BIU - ab *, * A-D-A- AL E - . 11 Normal 1 No Spac... Heading 1 Heading 2 Title Subtitle Font Paragraph Styles 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 *** Focus to search o Bi e a H U = W H - CapBud Assign Template V3 POST (I) team project 1015 - E. Auto save on File Home katie_sandage yahoo.com Insert Page Layout Formulas Data Review View Help B1 - * f 1 IDEFGHK No a su v Inputs 5 5 ATSV old @t-o ATSV formula 6 6 Equipment 7 zTax Credit 8 8 Depreciaton per year 9 9 Sales period 1 growth: 2 yrs 2-3 15% e yrs 4-6: 5% 10 10 COGS Sof sales 11 11 SG&A exp. Sof sales 12 12 ATSV new t=5 13 13 14 14 Operating Life CFS 15 15 Time 0 1 2 3 4 5 16 16 Sales 17 17 - COGS 18 18 - SG&A expenses 19 19 - Depreciation 20 20 - EBIT 21 21 Taxes (40%) 22 22 - Net Income NPV Scenario / Risk Analysis: 23 23 Depreciation Complete the grids below to report 5x5 Grids of NPV vs input variable changes noted 24 24 - Operating CF 25 25 26 26 Time 0 Investments INPV Analysis Grid: NPV vs Discount Rate & Salvage Value Ranges SV -> 27 Equipment SV-20% SV-10% SV Base SV-10% SV+20% 300 28 28 ATSV old ATSV -> 29 Tax credit 29 ATSV-20% ATSV-10% ATSV Base ATSV 10% ATSV 30 30 NWC SNPV in Cells: 31 Cost of Capital 4% 32 32 Terminal Non-OCF: 33 33 ATSV new @t=6 34 34 NWC 35 50 35 = Net Cash Flow 50 129 50 50 36 36 - Cummulative CF 50 50 50 50 37 37 CapBud Template Sheet3 + SS Type here to search