I need help with someone to look over this budgeting and forecasting worksheet and make sure that I did it right

 ATTACHMENT PREVIEW Download attachment5b539b378f315FeF5b22dd00316b3900ae28413.xlsx107/26/2017BUSN 278 Budgeting and ForecastingInstructor: Walter A. Corrigan, MBA, CMA, CFMWeek 4 CapEx Investment Metrics WorksheetProject with uneven cash flowsYearYearYearYearYearYearDescriptionMetric012345ARR accounting rate of return205,000211,150217,300223,450229,60025.0%51,25052,78854,32555,86357,40047,05723,52915,68611,7649,4114,19329,25938,63944,09847,9892.1%NPV net present value8.0%Enter net operating cash flows from line 12 above51,25052,78854,32555,86357,400(78,428)20.0%$15,685.60Equals the net operating and investing cash flows years 0 to 5(78,428)51,25052,78854,32555,86373,0861.00000.92590.85730.79380.73500.6806Calculate each column’s PV cash flows (row 22 x row 23).(78,428)47,45245,25543,12341,05949,742305,060IRR Internal rate of return62.7%PI Profitability Index2.89PaybackPayback table >>>YrInvestmentAnn CF0(78,428)(78,428)Enter the net cash flows for years 1 to 5 from D22 to H22 in cells E36 to E40.151,250(27,178)Payback is achieved when cumulative cash flow is $0252,78825,610354,32579,935455,863135,798573,086208,884This CapEx investment metrics worksheet can be used to make an initial evaluation of a capital expenditures project, using some simplifying assumptions.It will allow theanalyst to calculate investment metrics.In a real world situation this work would be followed up with more extensive analysis. This worksheet uses a five year planning horizon.In capital budgeting analysis it is assumed that on paper, the investment will be sold at the end of the planning horizon, and that positive cash flows will be recovered from thedisposal of the working capital and fixed assets.Students should enter data in the blue cells.The orange cells have formulas and should not need to be edited. If thestudent adds or deletes rows or columns in this worksheet the formulas provided will no longer work.Enter total annual sales years 1 to 5 from for your course project company’sWeek 2 Five Year Sales ForecastCalculate net operating cash flows years 1 to 5(assume = 25% sales).Enter straight line annual depreciation years 1 to 5 = (initial investment – salvagevalue)/5.Enter the amount of the annual depreciation expense as a positive $amount.Annual accounting income/(loss) years 1 to 5 equals the net operating cash flowon row 12 minus annual depreciation on row 13.Compute ARR = average accounting income for years 1 to 5 (which is the sum ofaccounting income/(loss) (D14 to H14)/5, divided by the amount of the initialinvestmentwith no minus signfrom cell C20 below.Project interest rate(assume = 8%)Enter initial investment in year 0 as a negative amount (a cash outflow is anegative amount).Add cash inflow (positive amount) from receipt of salvage value (aka FMV fairmarket value) in year 5 (assume = 20% of initial investment in C20)PV $1 factor using project discount rate.The PV factors were calculated usingthe formula for PV $1, but a present value $1 table posted in Files could beconsulted instead using the project interest rate in B18.Compute NPV $ years 0 to 5.This equsls the sum of the PV cash flows on row24 from C24 to H24.IRR Internal rate of return %:Use Excel formula =IRR (type =IRR in cell B28)and enter the range of cash flows for years 0 to 5 from C22 to H22.Using =IRR,the values can be enteredas an array C22:H22by dragging your cursor overthose cells.Is equal to the sum of the PV cash flows for years 1 to 5 from D24 to H24,divided by the PV cash flow in year 0 inC24 with no minus sign.CumulativeProject CashFlowEnter initial investment from year 0 shown in C20 (cash outflow is negative) incell D35.Payback is achieved in which year (include a decimal such as 3.3 years or 3.5years)

Looking for a Similar Assignment? Order now and Get 10% Discount! Use Coupon Code "Newclient"