英语论文网

he Insurance Premium that customers will pay if they choose to insure with Slick Jim’s.
Use the Insurance Rates.xls spreadsheet provided, which includes information about rates of Insurance Premiums charged by the Slick Jim’s. All premiums include a base charge of $100.00. The remaining charge depends on the price that the client payed to Slick Jim’s when they purchased the car. Charges are on a sliding scale. The table shows a starting fee for each price break and a percentage fee for any amount above that price break. As the price increases the percentage charged changes.
To check your spreadsheet, for a car costing $27,990 the insurance premium charge should work out to be $839.70.
A robust model
The spreadsheet calculator needs to be made more robust. Your task is to alter the input, calculation and report sheets (if necessary) to accommodate more data or less data. Your systems should be able to accommodate:
Up to one hundred (100) cars in the calculator Up to ten (10) different staff In addition, it should be possible to add and remove cars and/or staff from the input sheets and still have the processing and report/output sheets operate correctly.
Make sure your model can accommodate this amount of data by:
§           Extending the formulae on the calculation sheets to allow for the above quantity of data
§           Ensuring that calculations operate correctly.
You will find that if you copy the formulae on your calculation sheets down to other rows on the sheet, you will generate error messages (0, #VALUE, #N/A and the like) where the formulae reference data does not exist. You must use Excel’s error management functions to prevent error messages such as these appearing in these cells. Some of the error formulae you might need to use include:
§           ISERROR
§           ISNUMBER
§           ISBLANK
Note: Error correction must be applied to ALL your formulae – not just those extending beyond the current set of data.
·         Test your calculator by temporarily removing one of the cars and/or one of the staff. If you see Excel error messages, you have more work to do!
Additional Functionality
This is where you must be creative! You must think about Jim Slick’s business needs and investigate and experiment with the capabilities of Excel spreadsheets (maybe look through the Excel Help on the various functions that come with Excel and the extension work in the three Excel Workshops).
You should:
Identify TWO possible additions to the spreadsheet you have developed above that you believe would be of value to Jim; Implement those additions in your spreadsheet; and Write a one page report, which explains to Jim what extra functionality the spreadsheet additions will provide, and how these will help him conduct his business. To start you thinking, you might like to have a look at some of the extension work in your three Excel Workshops, including work on: Goal Seek; NPER and other Financial Functions; Nested Ifs; Pivot Tables; and Statistical Functions. You might also think about how you might pr