Project Excell Final Government Finance

Excel Project Webmasters.com has developed a powerful new server that would be used for corporationsÕ Internet activities. It equipment necessary to manufacture the server, and it would require net operating working capital equal to $2 mi $22,000 per unit, and Webmasters believes that variable costs would amount to $17,000 per unit. After the first ye costs would increase at the inflation rate of 3%. The companyÕs fixed costs would be $1 million per year, and wou take one year to buy the required equipment and set up operations, and the server project would have a life of 4 ye must be continued for the entire 4 years. Also, the projectÕs returns are expected to be highly correlated with retu firm believes it could sell 1,500 units per year. The equipment would be depreciated over a 7-year period, using MACRS rates. The estimated market value of th projectÕs 4-year life is $1.5 million. WebmastersÕ federal-plus-state tax rate is 40%. Its cost of capital is 10% for a a. Develop a spreadsheet model and use it to find the projectÕs NPV, IRR, and payback. Key Output: Part 1. Input Data (in thousands of dollars) Equipment cost NOWC needed First year sales (in units) Sales price per unit Variable cost per unit Fixed costs 10.000 $2.000 1.500 $22,00 $17,00 $1.000 Part 2. Depreciation and Amortization Schedule Year Initial Cost Equipment Depr'n Rate Equipment Depr'n, Dollars Ending Bk Val: Cost - Accum Dep'rn 10.000.000 Part 3. Net Salvage Values, in Year 4 Estimated Market Value in Year 4 Book Value in Year 4 Expected Gain or Loss Taxes paid or tax credit Net cash flow from salvage Market value of equipment in Ye Tax rate WACC Inflation 1 14,3% $204.143 9.795.857 Years 2 24,6% $401.467 9.394.390 Equipment $1.500.000 #WAARDE! #WAARDE! #WAARDE! #WAARDE! Page 1 3 17,5% $335.442 9.058.948 Part 4. Projected Net Cash Flows (Time line of annual cash flows) Years 0 Investment Outlays at Time Zero: Equipment 10.000.000 NOWC needed 2.000.000 1 2 1.500 $22.000,00 $17.000,00 1.500 $22.660,00 $17.510,00 $33.000.000 ($25.500.000) (1.000.000) (204.143) 6.295.857 2.518.343 3.777.514 204.143 $4.500.000 $33.990.000 ($26.265.000) (1.000.000) (401.467) 6.323.533 2.529.413 3.794.120 401.467 $6.725.000 ($12.000.000) $4.500.000 $6.725.000 1 0,909 0,826 (12.000.000) 4.090.500 5.554.850 0 $12.000.000 1 $4.090.500 2 $5.554.850 1 0,909 0,826 Operating Cash Flows over the Project's Life: Units sold Sales price per unit Variable costs per unit Sales revenue Variable costs Fixed operating costs Depreciation (equipment) Oper. income before taxes (EBIT) Taxes on operating income (40%) Net Operating Profit After Taxes (NOPAT) Add back depreciation Operating cash flow Terminal Year Cash Flows: Recovery of NOWC Terminal Year Cash Flows: Net salvage value Net Cash Flow (Time line of cash flows) Part 5. Key Output: Appraisal of the Proposed Project Net Present Value (at 10%) IRR MIRR $18.162.857 38,38% Payback (See calculation below) Data for Payback Years Cumulative CF (Based on Row 75) IF Function to find payback (12.000.001,00) Page 2 4.090.500,91 5.554.850,83 would cost $10 million to buy the illion. The servers would sell for ear, the sales price and variable ld increase with inflation. It would ears. If the project is undertaken, it rns on the firmÕs other assets. The e equipment at the end of the average risk projects. NPV = IRR = MIRR = $18.162.857 38,4% 38,4% t in Year 4 $1.500 40% 10% 3,0% 4 12,5% $301.776 $8.757.172,51 Accum'd Depr'n $1.242.827 Page 3 3 4 1.500 $23.339,80 $18.035,30 1.500 $24.039,99 $18.576,36 $35.009.700 ($27.052.950) (1.000.000) (335.442) 6.621.308 2.648.523 3.972.785 335.442 $6.956.750 $36.059.991 ($27.864.539) (1.000.000) (301.776) 6.893.677 2.757.471 4.136.206 #WAARDE! $9.195.453 $2.000.000 11.195.453 $6.956.750 $22.390.905 0,751 0,683 5.224.519 15.292.988 3 $5.224.519 0,751 4 $15.292.988 0,683 5.224.520,00 15.292.988,80 Page 4 Yr 0 $ Initial Investment Yr 1 $ Yr 2 $ Yr 3 $ (10.000.000) Units Selling Price Total Revenue 1500 1500 1500 22000 22660 23340 33.990.000 35.009.700 33.000.000 Variable Cost / unit 17000 17510 18035 Total Variable Cost (25.500.000) (26.265.000) (27.052.950) (1.000.000) (1.000.000) (1.000.000) 4.500.000 6.725.000 6.956.750 Fixed Cost Working Capital (2.000.000) Equipment Sale Net Cashflow (12.000.000) Yr 4 $ 1500 24040,0 36.059.991 18576 (27.864.539) (1.000.000) 2.000.000 1.500.000 9.195.453