USE EXCEL

Note: Use formula for the calculated cells. For example: to calculate the cost for the item "2 systems analysts", you have to use formula =2*400*50.

Mini Case Question

Martin is working to develop a preliminary cost-benefit analysis for a new client server system. He has identified a number of cost factors and values for the new system, summarized in the following tables:

Development Cost - Personnel

2 - System Analyst 400 hours/ea @ $50/hour

4 - Programmer Analyst 250 hours/ea @ $35/hour

1 - GIU Designer 200 hours/ea @ $40/hour

1 - Telecommunications Specialist 50 hours/ea @ $50/hour

1 - System Architect 100 hours/ea @ $50/hour

1 - Database Specialist 15 hours/ea @ $45/hour

1 - System Librarian 250 hours/ea @ $15/hour

Development Costs - Training

4 - Oracle training registration $3500/student

Development Costs - New Hardware and Software

1 - Development Server $18,700

1 - Server Software (OS, misc.) $1500

1 - DBMS Server Software $7500

7 - DBMS Client Software $950/client

Annual Operating Costs - Personnel

2 - Programmer Analyst 125 hours/ea @ $35/hour

1 - System Librarian 20 hours/ea @ $15/hour

Annual Operating Costs - New Hardware, Software and Misc.

1 - Maintenance agreement $995

for server

1 - Maintenance agreement $525

for server DBMS software

Preprinted Forms 15,000/year @ $2.22/form

The benefits of the new system are expected to come from two sources: increased sales and lower inventory levels. Sales are expected to increase by $30,000 in the first year of the system's operation and will grow at a rate of 10% each year thereafter. Savings from lower inventory levels are expected to be $15,000 per year for each year of the project's life.

Develop a spreadsheet that summarizes this project's cash flow, assuming a four year useful life after the project is developed. Compute the present value of the cash flows, using an interest rate of 9%.

What is the NPV of this project?

What is the ROI of this project?

What is the break-even point?

Should this project be accepted by the approval committee?

Follow the given format.

2012 2013 2014 2015 Total Benefits Increased sales from individual music downloads 757 500 787,800 811,434 2,356,734 Increased sales from customer subscriptions 950 000 988,000 1,017 640 2,955,640 Increased sales from instore or website CD sales 205,000 213,200 219,596 637,796 Increased sales from music download gift cards 153,000 1.59,120 163,894 476,014 Total Benefits 2,065,500 2,148,120 2,212,564 6,426,184 Present Value Total Benefits 1,948,585 1,911,819 1,857,711 5,718,115 Development Costs Labor: Analysis and design 42,000 42,000 Labor: Implementation 120,000 120,000 Consultant fees 50,000 50,000 Development fraining 5,000 5,000 Office space and equipment 2,000 050050000 900000000 05005000 0 2,000 Instore kiosks 25,000 25,000 Software 10,000 10,000 Hardware 25,000 25,000 Total Development Costs 279,000 279,000 Operational Costs Labor: Webmaster 85,000 87,550 90, 177 262,727 Labor: Network technician 60,000 61,800 63,654 185,454 Labor: Computer operations 50,000 51,500 53,045 154,545 Labor: Business manager 60,000 61,800 63,654 185,454 Labor: Assistant manager 45,000 46, 350 47,741 139,091 Labor: Three staff 90,000 92,700 95 481 278, 181 Software upgrades 1,D00 1,000 1,000 3,000 Software liceraces 3,000 1,000 1,000 5,000 Hardware upgrades 5,000 3,000 3,000 11,000 User training 2,000 1,000 1,000 4,000 Additional ISP charges 15,000 17,000 18, 500 50,500 Communications charges 20.000 20,000 20.000 60,000 Marketing expenses 25,000 25,000 25,000 75,000 Total Operational Costs 461,000 469,700 483,251 1,413,951 Total Costs 279,000 461,000 469,700 483,251 1,692,951 Total Benefits - Total Costs (279,000) 1,604,500 1,678,420 1,729,313 4,733,233 Cumulative Net Cash Flow (279,000) 1,325,500 3,003,920 4,733,233 Present Value Total Costs 279,000 434,906 418/031 405,747 1,537,684 Return on Investment (ROI] 280% (6,426,184/1,692,951) Break-Even Point 0.17 years [costs are fully recovered in the first year; (1,604,500 - 1,325,500]/1,604,500) NPV [PV Total Benefits - PV Total Costs) 4,180,431 (5,718,115 - 1,537,684) Intangible Benefits: Improved customer sofisfaction Enhanced market position