Question

Chapter 14 Running Case Assignment: Improving Decision Making: Using Spreadsheet Software to Analyze the Return on a New System Investment Software skills: Spreadsheet formulas and functions Business skills: Capital budgeting This project provides you with an opportunity to use spreadsheet software to use the capital budgeting models discussed in this chapter to analyze the return on a new information system investment for a real-world company. Dirt Bikes's management would like to analyze the return on its investment in its employee training and skills tracking system described in Chapter 13. The system runs on the human resources specialists' PCs using PC database software. Because the entire corporate administrative staff recently received new desktop PC systems with database and other productivity software, there are no additional hardware and software purchase costs. The main costs include the initial cost of designing and implementing the database (business staff cost of $5,000; information systems staff cost of $15,000), gathering and adding employee skills and training data to the database ($5,500 initial data conversion cost plus $1,000 annual data entry costs), and ongoing maintenance and support ($3,000 annually). Human resources staff members believe the new application could save each of them two hours of work per week. (Their annual salaries are $37,000 and $42.000 each.) The company would also save about $11,000 annually in employee recruiting costs because it would be able to fill many vacant positions with existing employees, thereby reducing its costs for recruiting outside the company. The system would not be installed until the end of 2018 and would return benefits from 2019 to 2023. Prepare a report for management analyzing the return on the investment for this system over a five-year period using the following capital budgeting models: net present value, return on investment (ROI), internal rate of return (IRR), and payback method. Assume a 5 percent interest rate for your net present value calculations. Use spreadsheet software for your calculations. (Optional) If possible use electronic presentation software to summarize your findings for management. .

Answer 1

Please see below complete step wise analysis to solve above problem , The above problem based on Capital Budgeting model

Initial Cost of designing and Implementation data base
	Amount ($)
Business Staff Cost	5,000
Information system Staff cost	15,000
Initial data Conversion cost	5,500
Total Initial cost	25,500
( Cash Outflow at Initial Level)
Cash inflow calculation
Annual Salaries	79,000
( $ 37000+$42000)
Annual Data Entry cost	1,000
Ongoing maintenance & Support cost	3,000
Total Yearly cost	4,000
Annual Salaries	79,000
( $ 37000+$42000)
No of week in a Year	52
Week salaraies ($)	1,519	( Salary cost/No of Week)
No of Working hours in Week	40	( roughy 8 hrs per say)- 5 days a week)
Hourly Salary($)	38
Savings ( 2hrs per week each of them)	76	( Hrs savings * Hourly salary)
( 2 Hrs savings)
Annual Savings for 52 week	3,950	( No of week * Savings in 4 hr per week)
Recruitment cost savings annually ( $)	11,000
Total Savings yearly	14,950
Less yearly we paid
Annual Data Entry cost	1,000
Ongoing maintenance & Support cost	3,000
Total Yearly cost	4,000
Net Savings / Inflow	10,950

NPV - formula based calculation . To calculate NPv , three component must i) Initial Cash outflow ii) Yearly cash inflow iii) Return % ( in this Question -5%) .We need to factor return % on yearly inflow amount .

See formula of NPV = Cash flow/ ( 1+i)t- Initial Investment

i represents = Required return or discount rate ( 5% here )

t - time period ( in this Question year 19 - 23 - 5 year )

Capital Budgeting data structure		Cash Inflow ($)	NPV ($)
Initil Outflow - Year 0			-25,500
Year 19		10,950	10,429
Year 20		10,950	9,932
Year 21		10,950	9,459
Year 22		10,950	9,009
Year 23		10,950	8,580
NPV ( net Present Value)			21,908
IRR			26%

IRR - excel base formula . Just collected all above data - like Cash outflow and NPV amount ( discounted cash flow ) - as seen in the calculation ( $10.429 ----$8580) and then use IRR formula ( IRR=SUM(Year 0 to Year 23) = IRR %

Return On Investment
Annual Savings ( Return)	10,950
Initial Investment	25,500
ROI	43%

Pay Back Period		Cash Flow	Cumulative Cash flow
Initial Outflow - Year 0		-25500	-25,500
Year 19		10,950	-14,550
Year 20		10,950	-3,600
Year 21		10,950	7,350
Year 22		10,950	18,300
Year 23		10,950	29,250
Pay back Period	2.33

Pay Back period - In financial terms , pay back period mean the period ( time ) when company can recover initial Investment amount . In the above example , company can recover initial Investment money in Year 2 and Year 3 in between .( Fraction of period)