Please make a Feasibility analysis based on the scenario. Rossco\'s Feasibility
ID: 2341525 • Letter: P
Question
Please make a Feasibility analysis based on the scenario. Rossco's Feasibility Analysis File Home Insert Page Layout Formulas Dsta Review View Developer Help T3 1David is considering the purchase of a new computer with the following estimated costs: initial 2 systems 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 new reduction of three employees is expected, with average salaries of $40,000. The system will decrease average 5 yearly inventory by $150,000. Annual operating costs will be $30,000 per year. 7 The expected life of the machine is four years, with an estimated salvage value of zero. The 81 effective tax rate is 21%. All computer purchase costs will be depreciated using the straight- 9 line method over its four-year life. Rossco can invest money made available from the reduction 101 in inventory at its cost of capital of 10% All cash flows, except for the initial investment and 11 start-up costs, are at the end of the year. 13 REQUIRED 14 Use a spreadsheet to perform a feasibility analysis to determine whether Rossco should 15 purchase the computer. Compute the following as part of the analysis: initial investment, after- 16 tax cash flows for years 1 through 4, payback period, net present value, and internal rate of 7 return. Answer the question "should Ressco purchase the new computer and why? 18 s In oarder to conduct sensitivity analysis, create a spin buttom to allow the tax rate to be altered 201 from 15% to 30% at an increment of 1%. Also create a scroll bar to allow the cost of capital to 21 | be altered from 5% to 15% at an increment of 196. 24 27 Rossco Case Feasibility Ana lysis ) Type here to searchExplanation / Answer
Rossco Initial Investment Initial System Design $ 54,000.00 Hardware $ 74,000.00 Software $ 35,000.00 Initial system training $ 11,000.00 Installation $ 20,000.00 Conversion $ 12,000.00 Total $ 2,06,000.00 Tax rate 21% Cost of capital 10% Useful life of assets 4 Years (A) (B) (C ) Number Average Salary Tax Savings Net Cash saved Reduced Employees 3 40000 (100-21)=79% $ 94,800.00 (A) (B) (C )=(A)*(B) (D) (C )*(D) Amount Interest Rate Savings before tax Increased Taxes Net cash provided Inventory Savings 150000 10% 15000 (100-21)=79% $ 11,850.00 Increase in cost Tax savings Net Cash Expended Computer Operating costs 30000 (100-21)=79% $ 23,700.00 (A) (B) (A)*(B) Year Basis Depreciation Percentage Depreciation Amount Tax Savings Net Cash Saved Depreciation 1 206000 (1/4)=25% 51500 21% 10815 2 206000 (1/4)=25% 51500 21% 10815 3 206000 (1/4)=25% 51500 21% 10815 4 206000 (1/4)=25% 51500 21% 10815 Purchase New Computer System Tax Rate 21% Cost of capital 10% 39 B C D E F G 0 1 2 3 4 41 Cash Flows 42 Initial Investment $ -2,06,000.00 43 Reduced Employees $ 94,800.00 $ 94,800.00 $ 94,800.00 $ 94,800.00 44 Inventory savings $ 11,850.00 $ 11,850.00 $ 11,850.00 $ 11,850.00 45 Computer Operating cost $ -23,700.00 $ -23,700.00 $ -23,700.00 $ -23,700.00 46 Depreciation $ 10,815.00 $ 10,815.00 $ 10,815.00 $ 10,815.00 47 Net Cash Flow=(A) $ -2,06,000.00 $ 93,765.00 $ 93,765.00 $ 93,765.00 $ 93,765.00 48 P.V Factor(10% for 4 years)=(B) 1 0.909 0.826 0.751 0.683 49 P.V=(A)*(B) $ -2,06,000.00 $ 85,232.39 $ 77,449.89 $ 70,417.52 $ 64,041.50 50 NPV=(-$206000+$85232.39+$77449.89+$70417.52+$64041.50) 91141.285 Internal rate of return IRR(C47:G47) 29% Year P.V Cumulative P.V 0 $ -2,06,000.00 $ -2,06,000.00 1 $ 85,232.39 $ -1,20,767.62 2 $ 77,449.89 $ -43,317.73 3 $ 70,417.52 $ 27,099.79 4 $ 64,041.50 $ 91,141.29 Payback=2+($43317.73/70417.52) 2.62 Years Rossco should purchase the assets because NPV is positive & IRR is also greater then the hurdle rate of 10% and Payback period is also 2.62 years