Please see word document. The Taxi spreadsheet is for Part A and the Risk Analysis spreadsheet is for part B
Project: using debt and equity; one-year taxi investment with simple Risk Analysis. The firm is formed to purchase and operate a vehicle. The purpose of the vehicle is to operate a taxi service for one year. The life of the vehicle is only one year, after which time the vehicle is worthless. The debt will be repaid with interest and the firm will be shut down and capital returned to shareholder at year end. The firm is contemplating the following: Vehicle acquisition cost $ 30,000 Years of useful life (economic life) 1 Tax rate 20% Required rate of return on equity 10% Required return on debt 5% Debt ratio 50% Annual revenues$ 145,000 Operating expenses (excluding depreciation)$ 100,000 Tips: 1.Depreciate straight-line over the year of useful life, down to $0 over one year. Assume Market value of vehicle = Book value of vehicle. 2. The maximum dividend is paid at year end. 3. Ignore any working capital effects. 4. Capital charge will be based on the assets at the beginning of each year. Please include in the analysis: Part A: 1. P&L 1. OCF analysis 1. EVA analysis 1. Does this project deserve consideration? 1. What is the WACC? 1. What is the NPV of this investment? 1. What is the project’s IRR? 1. Report how many dollars are distributed at year end to: a. To debt holder: principal and interest b. To tax authority c. To shareholder 1. What is the shareholder's total rate of return? What is base case operating margin? 1. Part B: PROJECT RISK ANALYSIS (sensitivity to base case assumptions): using base case sales, and 9.5% operating margin, and also 10.5% operating margin; Do likewise using base case values; with sales 5% lower than base case, and also 5% higher than base case. Report the highest NPV and the lowest NPV values. How different are these than the base case NPV? Sheet1 Car cost (investment at 0)30,000Taxi Project Useful life (years)2 Tax rate0 Required equity return10%Annual Depreciation15,000 Each year, for two years $ yr 1 yr 2 Revenue128,000 OCF = EBIT (1-t) + Dep Opexp OCF =13,000+15,000=28,00028,000 Dep15,000 SG&A0"Invest $30,000, OCF=$28,000 each year, for two years" Driver + gas100,000 Total115,000 yr 1 yr 2 EBIT13,000 EVA = EBIT(1-t) -capital charge Interest0 EVA=13,000-3,000=10,00011,500 EBT13,000 Tax0"Invest $30,000. Year 2 Assets = 15,000 after depreciation" NI13,000 Total012 EVA analysisEVA = 10,00011,500 PV of EVAs18,5959,0919,504 OCF analysis OCF =28,00028,000 PV of OCFs48,59525,45523,140 Investment at 030,000 PV less investment18,595 Sheet1 Scenarios Op Margin Scenario0.120.150.17 ($0.05)764,4981,018,0001,188,247 If all FX rates base847,8951,113,5171,290,599 $0.05931,2921,208,0001,392,950 NPV for alternative scenarios 0.12 ($0.05)base$0.05 7644988478959312920.15 ($0.05)base$0.05 1018000111351712080000.17 ($0.05)base$0.05 118824712905991392950 ($0.05)base$0.05 Sheet2 NPV for alternative scenarios 0.12 ($0.05)base$0.05 7644988478959312920.15 ($0.05)base$0.05 1018000111351712080000.17 ($0.05)base$0.05 118824712905991392950 ($0.05)base$0.05