Question

Create a realistic and error-free financial model for the problem sent in e-mail by your workshop leader. Use MS Excel for creating the necessary spreadsheets in one workbook and MS Word for the user's manual. Define clearly the different types of variables and the users of the model. For parameters, input and output variables use colour coding. Protect your model. Use easily understandable formulas, data validation and minimum hard coding. Make your model user friendly by using appropriate numbers formatting; cell comments, spinners etc. create a flexible les, scenarios and charts. Test your model. Include the model description in your user's manual. Marking criteria: error free realistic easy to use protection easily understandable formulas appropriate numbers formatting well organized and easy to follow by formatting O fonts O colours O patterns o borders cell comments data validation chart data table scenario user friendly tests user's manual O model description o underlying theories, formulas inputs and outputs assumptions o illustrations of the model o references

Problem

Baldwin Corp. is considering replacing one of its existing machines with a new, more automated and efficient one. The old machine has a book value of $100,000. It could be sold today for $50,000. The remaining book value is being strait line depreciated to 0 salvage value, at the rate of $20,000/year. The new machine costs $400,000. If introduced, the company estimates that will save annually $60,000 on a before tax basis. This is considered a year end cash flow. The corporate tax rate is 40%; discount rate used is 15%. The new machine will be straight line depreciated on ten year life to 0 salvage value. In order to encourage the replacement of the old machine the state Environmental Protection Agency is offering a replacement subsidy. This is paid one year after the replacement. What is the minimum subsidy that will make the replacement worthwhile?

Answer 1

All amounts are in $ 1 Initial Outlay Purchase value of New Machine Sale proceeds of existing Machine Net Initial Outflow 400,000.00 (50,000.00) 350,000.00

2 Present Value of Inflows and outflows Year 10 60,000.00 60,000.00 60,000.00 60,000.00 60,000.00 60,000.00 60,000.00 60,000.00 60,000.00 60,000.00 60.000.00 60,000.00 60,000.00 60,000.00 60,000.00 60,000.00 60,000.00 60,000.00 Benefits Annual Savings 60,000.00 60,000.00 Expenses Depreciation of Old Machine 20,000.00 Depreciation of New Machine 40,000.00 60,000.00 20,000.00 10,000.00 40,000.00 40,000.00 60,000.00 50,000.00 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 cra-b Net Benefit d=c*40% Tax Expenses @40% e-c-d Net Benefit after tax Add: Depreciation f=e+b Net Inflow Present Value Factor @ 15% h=f*g Present value of benefits 60,000.00 60,000.00 0.8696 52,173.91 10,000.00 400.00 9,600.00 60,000.00 50,000.00 60,000.00 59,600.00 0.7561 0.6575 45,368.62 39,187.97 20,000.00 20,000.00 20,000.00 20,000.00 20,000.00 20,000.00 20,000.00 800.00 800.00 800.00 800.00 800.00 800.00 800.00 19,200.00 19,200.00 19,200.00 19,200.00 19,200.00 19,200.00 19,200.00 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 40,000.00 59,200.00 59,200.00 59,200.00 59,200.00 59,200.00 59,200.00 59,200.00 0.5718 0.4972 0.4323 0.3759 0.3269 0.2843 0.2472 33,847.79 29,432.86 25,593.79 22,255.47 19,352.59 16,828.33 14,633.33

3 Sum of present value of yearly inflows Initial outflow 298,674.68 350,000.00 Unrecovered Investment amount 51,325.32

4 Minimum Subsidy Required The minimum subsidy requirement is the unrecovered initial investment. since the subsidy will be received only after year 1, the required amount of subsidy will be future value of unrecovered amount after year 1 compounded @15% which is 51325.32*115% = 59,024.12

5 Depreciation of old Machine Book Value after sales 50,000.00 Depreciation Year 1 Balance 20,000.00 30,000.00 Depreciation Year 2 Balance 20,000.00 10,000.00 Depreciation Year 3 10,000.00 Balance

6 Depreciation of New Machine 400,000.00 Purchase Cost Less Salvage Depreciable Amount Useful Life Yearly Depreciation 400,000.00 10 years 40,000.00