Perform financial analysis for a project using the formats provided in Figure 4-4, Figure 4-5 and Figure 4-6. Assume the projected costs and benefits for this project are spread over four years as follows: Estimated costs are $180,000 in Year 0 and 50,000 each year in Years 1, 2, and 3. estimated benefits are $0 in Year 0 and $190,000 each year in Years 1, 2, and 3. Assume that costs and benefits are estimated at the beginning of the year. Use a 8.5 percent discount rate, and round the discount factors to two decimal places to do the following: 1) Calculate NPV using Excel NPV formula in a spreadsheet (similar to Fig 4-4). 2) Create a spreadsheet (similar to Fig 4-5) to calculate NPV, ROI, and payback period. 3) Show using a chart (similar to Fig 4-6) in which year the payback occurs. In addition, 4) write a paragraph explaining whether you would recommend investing in this project, based on your financial analysis. (Be sure to show all computations using formula in Microsoft Excel)

State First is a Fortune 500 insurance company that has specialized in writing liability, physical damage and motor truck cargo for the nation's transportation industry since the early fifties. It also provides liability and physical damage insurance for various classes of business and public auto including contractor fleets, waste haulers, dump trucks, limousines and airport shuttle vans. In addition, it provides professional liability coverage for accounting firms, architect and engineer firms, hospitality and lodging, law firms, and restaurants. The company has field offices throughout the country. The corporate claims department is staffed with experienced liability, cargo, and heavy equipment physical damage specialists to provide the best professional claims service possible. The company provides 24 hour telephone access to its field offices for adjusters for those claims which occur after normal business hours. The company has more than 30,000 full-time employees and more than 7,500 part-time employees. The State First has recently updated its strategic plan, and key goals include reducing internal costs, increasing cross-selling, and exploiting new Web-based technologies to help employees, customers, agents, and suppliers work together to improve the development and delivery of its insurance products and services. Given below are summaries of four project ideas the Information Technology department has developed for supporting these strategic goals:

A. Automated Claim Processing System: Develop an automated system to replace the current manual claim processing system. In the current manual claim processing system the claimants contact an agent via phone and the agent records all the information of the claimants manually. The field offices arrange inspection and makes estimates of the claim. This information from the field insurance offices is then faxed over to the claims processing department of the corporate office. The corporate office then makes the payment to the claimants. You estimate that the automation project would save your company $1.5 million each year for the next three years. The project is estimated to take 18 months and cost about $750,000 for development and maintenance.

B. Health Coverage Cost Monitoring System: Develop an application to track employee health care expenses and company health care costs. Health care premiums continue to increase, and the company has changed insurance carriers several times in the past ten years. This application should allow business modeling of various scenarios as well as tracking and analyzing current and past employee health care expenses and company health care costs. This could access it and download selective data for further analysis. The new application must also import data from the current systems that track employee expenses submitted to the company and the companys costs to the insurance provider. You believe that having this data will help you revise policies concerning employee contributions to health care premiums and help you negotiate for lower premiums with insurance companies. You estimate that this application would save your company about $20/employee/year for full-time employees over the next four years and cost about $100,000 to develop.

C. Web Portal for Customer Services: Develop a Web-based application to improve the delivery of products and services. The web portal will serve the existing as well as potential customers. Through this web-based interface the prospective and existing customers will be able to get an insurance quote, existing customers can login to manage their policies with abilities to add or remove coverage, view bills online, make online make payments, and file claims electronically. This interface will also serve as the main web interface for the company. You estimate that this system would save your company about $2 million each year for three years after implementation. You estimate it will take one year and $3 million to develop and require 10 percent of development costs each year to maintain.

D. Payroll System Upgrade Project: Develop a new software and hardware solution to replace the companys age-old payroll system which has been limited in functionality and runs on old outdated technology. The current system also requires payroll data to be entered manually from paperbased time-card submitted by employees. The proposed upgrade will allow employees to enter time-card online and all employee data will be saved in a large centralized database. The proposed new system will process direct deposit and print electronic payment slips. This centralized payroll system will be run from the corporate head quarters and bring all the employees of the field offices under this system. This new centralized system will also eliminate the separate payroll systems run by field offices thus reducing costs. You estimated that this proposed system would increase company profit by $1 million each year for the next three years and cost about $800,000 each year for development and maintenance.

Prepare a weighted scoring model to compare the above four projects (Project A, Project B, Project C, and Project D) using Microsoft Excel and following the guideline and format discussed in pages 154-156 of the textbook. Develop at least ten criteria (you can adopt five of those criteria from the book and develop five new criteria), assign weights to each criterion, assign scores, and then calculate the weighted scores. Your weighted scoring model should be similar to Figure 4-7. It must include Bar Chart of the Model. Note that you do not need to compute any exact financial parameters from the above projects (A-D). You can make assumptions for scores of any financial criteria you want to use from the above projects. (Be sure to show all computations of weighted scoring model using Excel formula in Microsoft Excel).