Please see both files attached. This assignment is a capital budget risk analysis. The pdf file has the instructions for the assignment. The excel file is a template that could be used if you would like or you could do it by scratch. Please note to remove all unnecessary info from the template that does not pertain to the case study. That template was just used for lecture.
University of La Verne College of Business and Public Management Bus 630 – Corporate Finance Case Study #3 Summer 2021 Post your work to the Case #3 Blackboard file folder, due Tuesday July 27 at Midnight, please make sure your name is in the file(s) name. This Case work represents 15% of your total course grade. All analysis work should be in a spreadsheet file, question answers/interpretation may be submitted in a Word or PDF format file. Capital Budget Risk Analysis Based on the inputs below prepare a capital budget analysis for this Base Case using the Net Present Value, Internal Rate of Return, Profitability Index and Payback in Years determining whether the project is feasible. All you work should be in a spreadsheet file. After your analysis is completed answer these follow-up questions: 1). How does this capital budget risk analysis fit into a Balanced Scorecard analysis? 2). Would you recommend approval of this project? Explain. Does the risk analysis effect your decision? Bus 635 Case 3: Hasse 2 Project Inputs: WACC – Determine the cost of capital for your discounted cash flow. Debt to Equity is 75% Interest rate on the debt is 5.50% Current Risk-Free Rate is 1.13% Current Market Premium Rate is 8.25% The firm’s beta is 1.30 Project Investment Outlay, Year 0 - $700,000 Project Investment Life – 7 years Project Depreciation - $100,000 / year Project Salvage Value - $55,000 Working Capital Base of Annual Sales – 8% Expected inflation rate per year, Selling Price Per Unit – 2.00% Expected inflation rate per year, Manufacturing Cost per unit – 1.50% Expected inflation rate per year, Fixed operating costs per year – 1.50% Project Tax Rate – 20% Units sold per year – 40,000 Selling Price per Unit, Year 1 - $45.00 Fixed operating costs per year excluding depreciation - $75,000 Manufacturing costs per unit, Year 1 - $35.00 Bus 635 Case 3: Hasse 3 Inputs continued: In addition to your base case analysis, please provide a scenario and sensitivity analysis based on the following: Worst Case Scenario = 20% decrease in sales units sold per year: 25% Probability Base Case Scenario = 40,000 units sold per year: 55% Probability Best Case Scenario = 20% increase in sales units sold per year: 20% Probability Sensitive Variables: • Selling Price Per Unit • Manufacturing (variable) Cost Per Unit • Weighted Average Cost of Capital +- 10%, 20%, 30% How does this risk analysis effect Net Present Value? Please show the calculations. 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 IRR17.03%Return Analysis in Percentage Sensitivity Analysis text Ref: 468 - 470PI1.43Return Analysis in Ratio Variables:Payback Years4.94Return Analysis in Years (Time) Sales Price / unit$ (856,500)$ (647,438)$ (432,654)$ (211,987)$ 14,730$ 247,669 Variable Cost / unit0.94 WACC Deviations: +/- 5%, 10%, 15% Base Analysis (2) 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)$