Question

Hello, Could you please provide me with the FORMULAS used to solve problem 20.5 (chapter 20/Accounting Information Systems). The solution you have is the correct solution, but I need the formulas used.

The Question: Rossco is considering the purchase of a new computer with the following estimated costs: initial system design:$54,000, hardware:$74,000,software:$35,000,one-time initial training:$11,000, system installation:$20,000 and file conversion:$12,000. A net reduction of 3 employees is expected with average yearly salary of $40,000.The system will decrease average yearly inventory by $150,000. Annual operating costs will be $30,000.

The expected life of the machine is 4 years, with estimated salvage value of Zero. The effective Tax Rate is 40%. All computer purchase costs will e depreciated over its 4 years life. Rossco can invest money made available from the reduction in inventory at its cost of capital of 11%. All cash flows, except for the initial investment and start-up costs,are at the end of the year. Assume 365 days in a year.

Required: Use a spreadsheet to perform feasibility analysis. compute the following as part of the analysis: Initial Investment - after-Tax cash flows for years 1-4 , payback period - Net Present Value - Internal Rate of Return.

_______________________________________________

Note: please Clearly Write the Formulas to be entered into Excel ( NP - NPV - IRR .... etc)

Answer 1

With Formulas

Answer Tax Rate Desired Rate of Return 40% 1Initial Investment Initial system design Hardware Software One time initial training Istallation Conversion Total Initial Investment 54,000 74,000 35,000 11,000 20,000 12,000 206,000 0 Avr. Sala Tax savin Net cash saved Reduced Employees 3 40,000 60.00%) $ 72,000 Invento Cost of cap. Savings Net of tax Net cash provided Inventory Savings (working capital) 16,500 60%) $ 9,900 4 Increase cost Net of tax Net cash cost Comuter operating cost 30,000 18,000 5 Depreciation Tax savings Net cash saved basis 1 206,000 2 206,000 3 206,000 4 $ 206,000 ear 25%) $ 25%) $ 25%) $ 25%) $ 51,500 51,500 51,500 51,500 20,600 20,600 20,600 20,600 6 Year 0 1 4 Cash Flows Initial Investment Reduced Emloyees Inventory Savings Cumpter operating costs Depreciation Net Cash Flows PV Factor PV of Net cash flows Cumulative Cash flows Net Present Value IRR Payback period 2 years +37000/84500 $(206,000 72,000 $ 9,900 18,000) 20,600 72,000 72,000 72,000 9,900 18,000 20,600 9,900 (18,000) 20,600 9,900 18,000 20,600 $ (206,000) $ 84,500 $ 84,500 84,500 84,500 206,000 (206,000)(121,500) 0.81162 68,582 37,000) 0.73119 61,786 47,500 0.65873 55,663 132,000 76,126 6,157 23.23% 2.44 vears