I need a excel spreadsheet with the work and a 1 page word doc on the resultsthe professor stated that the spreadsheet should have 9 work sheets in it for each step that must be done, please let me know if you have any questions
Capital Budgeting Capital Budgeting Process 1).Asset Investment 2).Depreciable Life 3).Net Income: Revenues - Operating Expenses 4).Net Cash Flow: Depreciation / Working Capital / Disposal (Salvage Value) 5).Return Analysis: Net Present Value / Internal Rate of Return / Profitability Index / Payback 6).Risk Analysis: Scenario / Sensitivity WACC Weighted Average Cost of CapitalChapter 9 Debt to Assets % = 60% Tax Rate:30% Interest Rate on Debt9%Cost of Debt AT6.30% Risk Free Interest Rate2.50%Cost of Market Rate of Return10%Equity CAPM12.63% Firm Beta1.35 WACC:Debt %Equity %WACC W debt60%6.30%3.78% W equity40%12.63%5.05% 8.83% &"-,Bold"&12Week 6 In Class Review Base Analysis Capital Budget Analysis Text Ref: 416 - 431AverageYear:012345678910Total InputsBase Case Equipment Cost$ 1,000,000Investment:$ (1,000,000) Salvage Value, Year 10$ 50,000 Depreciation Per Year$ 100,000Sales$ 500,000$ 512,500$ 525,312$ 538,445$ 551,906$ 565,704$ 579,847$ 594,343$ 609,201$ 624,431$ 5,601,691 Units Sold Every Year25,000 Sales price per unit, Year 1$ 20.00Expenses: Annual change in sales price, after Year 12.5%VC$ 200,000$ 204,000$ 208,080$ 212,242$ 216,486$ 220,816$ 225,232$ 229,737$ 234,332$ 239,019$ 2,189,944 Variable cost per unit (VC), Year 1$ 8.00FC$ 50,000$ 50,500$ 51,005$ 51,515$ 52,030$ 52,551$ 53,076$ 53,607$ 54,143$ 54,684$ 523,111 Annual change in VC, after Year 12.0%Depreciation$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 1,000,000 Fixed Cost (FC), Year 1$ 50,000EBT$ 150,000$ 158,000$ 166,227$ 174,689$ 183,390$ 192,337$ 201,538$ 210,999$ 220,727$ 230,729$ 1,888,636 Annual change in FC, after Year 11.0% Project WACC8.83%Tax (30%)$ 45,000$ 47,400$ 49,868$ 52,407$ 55,017$ 57,701$ 60,461$ 63,300$ 66,218$ 69,219$ 566,591 Tax Rate30.0%Net Income$ 105,000$ 110,600$ 116,359$ 122,282$ 128,373$ 134,636$ 141,077$ 147,699$ 154,509$ 161,510$ 1,322,045 Working Capital as % of next year's sales12.0% Salvage Value$ 35,000$ 35,000 Risk AnalysisDepreciation$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 1,000,000 Scenario Analysis Text Ref: 471 - 474NOWC$ (60,000)$ (1,500)$ (1,537)$ (1,576)$ (1,615)$ (1,656)$ (1,697)$ (1,740)$ (1,783)$ (1,828)$ 74,932$ 60,000 Weak Sales = 12,500 units (25% probability)Net Cash Flow$ (1,060,000)$ 203,500$ 209,062$ 214,783$ 220,667$ 226,717$ 232,939$ 239,337$ 245,916$ 252,681$ 371,442$ 2,417,045 Average Sales = 25,000 units (50% probability)DCF$1,510,832 Strong Sales = 35,000 units (25% probability)NPV$450,832Return Analysis in Dollars$500,000 IRR17.03%Return Analysis in Percentage18.00% Sensitivity Analysis text Ref: 468 - 470PI1.43Return Analysis in Ratio1.5 Variables:Payback Years4.94Return Analysis in Years (Time)4 Years Sales Price / unit$ (856,500)$ (647,438)$ (432,654)$ (211,987)$ 14,730$ 247,669 Variable Cost / unit0.94 WACC Deviations: +/- 5%, 10%, 15% Risk Analysis AverageStrongWeak Scenario25,00035,00012,500 50%25%25% NPV$450,832$1,041,626($287,269) Probability$ 225,416$ 260,407($71,817)$ 414,005 Sensitivity $20.00$8.008.83% DeviationSP/UnitVC/UnitWACC -15%$ 82,628$ 597,489$ 550,566 -10%$ 205,363$ 548,604$ 516,248 -5%$ 328,098$ 499,718$ 483,017 0%$ 450,832$ 450,832$ 450,832 5%$ 573,567$ 401,947$ 419,653 10%$ 696,302$ 353,601$ 389,439 15%$ 819,037$ 304,176$ 360,156 Range$ 736,409$ 293,313$ 190,410 SP/Unit-0.15-0.1-0.0500.050.10.1582628205363328098450832573567696302819037VC/Unit-0.15-0.1-0.0500.050.10.15597489548604499718450832401947353601304176WACC-0.15-0.1-0.0500.050.10.15550566516248483017450832419653389439360156 Question 10-7 YearProject A0123 Net Cash Flow$ (15,000,000)$ 5,000,000$ 10,000,000$ 20,000,000 Cost of Capital 5%DCF:$31,108,952 NPV:$16,108,952 Cost of Capital 10%DCF:$27,836,213 NPV:$12,836,213 Cost of Capital 15%DCF:$25,059,587 NPV:$10,059,587 IRR:43.97% YearProject B0123 Net Cash Flow$ (15,000,000)$ 20,000,000$ 10,000,000$ 6,000,000 Cost of Capital 5%DCF:$33,300,939 NPV:$18,300,939 Cost of Capital 10%DCF:$30,954,170 NPV:$15,954,170 Cost of Capital 15%DCF:$28,897,838 NPV:$13,897,838 IRR:82.03% Sensitivity Analysis Capital Budget Analysis Text Ref: 439 - 450AverageYear:012345678910Total InputsBase Case Equipment Cost$ 1,000,000Investment:$ (1,000,000) Salvage Value, Year 10$ 50,000 Depreciation Per Year$ 100,000Sales$ 500,000$ 512,500$ 525,312$ 538,445$ 551,906$ 565,704$ 579,847$ 594,343$ 609,201$ 624,431$ 5,601,691 Units Sold Every Year25,000 Sales price per unit, Year 1$ 20.00Expenses: Annual change in sales price, after Year 12.5%VC$ 200,000$ 204,000$ 208,080$ 212,242$ 216,486$ 220,816$ 225,232$ 229,737$ 234,332$ 239,019$ 2,189,944 Variable cost per unit (VC), Year 1$ 8.00FC$ 50,000$ 50,500$ 51,005$ 51,515$ 52,030$ 52,551$ 53,076$ 53,607$ 54,143$ 54,684$ 523,111 Annual change in VC, after Year 12.0%Depreciation$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 1,000,000 Fixed Cost (FC), Year 1$ 50,000EBT$ 150,000$ 158,000$ 166,227$ 174,689$ 183,390$ 192,337$ 201,538$ 210,999$ 220,727$ 230,729$ 1,888,636 Annual change in FC, after Year 11.0% Project WACC8.83%Tax (30%)$ 45,000$ 47,400$ 49,868$ 52,407$ 55,017$ 57,701$ 60,461$ 63,300$ 66,218$ 69,219$ 566,591 Tax Rate30.0%Net Income$ 105,000$ 110,600$ 116,359$ 122,282$ 128,373$ 134,636$ 141,077$ 147,699$ 154,509$ 161,510$ 1,322,045 Working Capital as % of next year's sales12.0% Salvage Value$ 35,000$ 35,000 Risk AnalysisDepreciation$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 100,000$ 1,000,000 Scenario Analysis Text Ref: 455-457NOWC$ (60,000)$ (1,500)$ (1,537)$ (1,576)$ (1,615)$ (1,656)$ (1,697)$ (1,740)$ (1,783)$ (1,828)$ 74,932$ 60,000 Weak Sales = 12,500 units (25% probability)Net Cash Flow$ (1,060,000)$ 203,500$ 209,062$ 214,783$ 220,667$ 226,717$ 232,939$ 239,337$ 245,916$ 252,681$ 371,442$ 2,417,045 Average Sales = 25,000 units (50% probability)DCF$1,510,832 Strong Sales = 35,000 units (25% probability)NPV$450,832 IRR17.03% Sensitivity Analysis text Ref: 451-454PI1.43 Variables:Payback Years4.94$ (856,500)$ (647,438)$ (432,654)$ (211,987)$ 14,730 Sales Price / unit0.94 Variable Cost / unit WACC Deviations: +/- 5%, 10%, 15%