Miss Harvey owns a Taxi & Limousine firm and wants to keep a breakdown of the income from each driver and calculate her total income. She needs to calculate each driver’s weekly wage plus any bonuses or commission. She earns a percentage of the total income of all of her drivers. The firm consists of 20 drivers and each driver has their own car. Their rates charged are related to the quality of their car – it’s age, prestige etc., and the limousine drivers demanding the highest rates. Through my research I feel that the introduction of a system on a computer would make it easier for Miss Harvey to work out each driver’s weekly wage and any bonuses they have earned. Hopefully the new system will reduce the amount of paper they use at the moment.
1.2 Current System
At the moment a calculator does all calculations and the wages of each driver is written down on paper and then stored. They have a piece with all the cars and how much it costs to hire them. This is used to determine how much the driver is likely to get paid. The miles covered and days on duty are also written down and the driver’s income is based on how many days he/she worked and how many miles they had covered.
A copy of the calculations is written down to give to each driver and one is stored in the company’s office for monthly figures. The Boss’ income is based on all the tax that has been deducted from each driver altogether. So if the total tax for one month is ï¿½432.89 then that would also be the boss’ monthly wage. However because the monthly tax varies, then so will the Boss’. The final calculations are done by one person and are all kept in a book, which is stored in the office. At the moment there are 20 drivers but if the company want to expand then there would be a problem with storing all pieces of paper and there might be some mistakes in the calculations.
1.3 Problems with the current system
Ms Harvey is concerned that all manual calculations will lead to mistakes being made. This can give drivers more or less money than they actually deserve and Ms Harvey’s wage will not be accurate. As well as accuracy there might be a problem with storing all of the data, as time goes by the amount of paper will double and there might be a problem with looking back at months if it’s all in one pile. For example if there was a mistake made in the month of January then it would take some time to find the correct piece of paper and change all the records including yearly records.
Ms Harvey may also have to employ more accounts staff if she wants the records to be more accurate and done in a fairly fast time. If Ms Harvey has only one person doing the calculations then it would take more time and sometimes there is no one to go over the calculations to see if they are all correct. Some problems may arise if Ms Harvey wants change the prices of cars and the tax rate, a computer system will easily be able to change these numbers and there would be no loss of time.
1.4. Input requirements
* Driver’s name
* Car used
* Days on duty
* Value of Friday Night Bonus
* Value of Full Week’s Work Bonus
* Tax rate
* Mileage cost rates
* Friday Night Bonus to be given or not
* Full Week’s Work Bonus to be given or not
1.5. Processing requirements
* A combo box is used to bring up a list of Driver’s Names from the Drivers worksheet.
* The driver’s sex is looked up from a table in the Drivers worksheet.
* Another combo box is used to bring up a list of Cars from the Cars worksheet.
* The car’s details, such as number of Passenger seats and Hire Cost are looked up from the Cars worksheet.
* The Duty Pay of the driver is calculated by multiplying the Days on Duty multiplied by the Hire Cost of the car.
* The Total Income of the driver is calculated by adding Duty pay to Total Bonus Income.
* A Tax of 15% should be subtracted from the Total Income if it is equal to or less than ï¿½80.
* Data from the Calculations worksheet can be transferred into another worksheet amongst past records for future reference.
* Data from the Calculations worksheet will also displayed in another worksheet, which consists of a template ready for a presentable printout, with the simple click of a macro.
1.6. Output requirements
* Full income and work details on screen
* A printout of a driver’s income and work details (days on duty, car used, mileage etc.)
* A printout of all of the drivers’ wages, which have been recorded.
* Visual Indications of a driver who earns less than ï¿½80, over ï¿½300, and in between.
* Averages of all of the drivers Income, and highest and lowest values.
1.7. Objectives of the new system
1. A system is needed to keep a track record of each driver’s income and be able to calculate Miss Harvey’s (the boss) total income.
2. Whether a driver is earning very well or not so well must be visually indicated.
3. If a driver fails to earn a certain amount of income, the system must be able to recognise that no tax should be taken from that particular driver’s income.
4. The driver’s name, days on duty, vehicle used and income earned and other work details need to be shown in a separate sheet, which is presented in a way ready for printing out.
5. All of the drivers’ names, days on duty, vehicles used and income earned, need to be filed in one sheet where they can all be compared.
6. Outputs to show the Income averages, highest and lowest Income values from out of all the drivers. Income values being: Bonus Income Total, Income Total, and After Tax Income.
7. The system must be able to produce Printouts of useful information such as a document with the Income earned by the drivers and by Miss Harvey
8. The user must be able to navigate around the system from sheet to sheet easily.
9. The system must be user friendly and have a professional lay out which isn’t prone to being edited accidentally i.e. you shouldn’t be able to edit buttons, borders, shapes and images etc.
10. All buttons, combo boxes and cell links must be fully working.
11. All formulas, which perform the calculations, must be fully working
1.8. Performance Indicators
The following performance indictors will be used:
1. It should take no longer than 2 minutes to file each wage calculation.
2. It must be impossible to accidentally erase formulae, headings and logos.
3. Each Macro should fully work and should enable the user to fulfil various tasks.
4. The system must cater of any number of titles to be added at a future date as the company expands.
5. Data entry should be so simple that a overall income and the boss’ wages will be easily identifiable.
6. The Driver’s bonus total, all driver’s total income and Boss’ wages should be produced in one operation.
7. The system should be easy to use for anyone with an basic knowledge of Excel.
1.9. Hardware and software
Harvey’s has a Pentium PC with 64 MB of RAM and 3GB hard disk. Windows 97, Excel 97 and Office 97 are loaded on this PC. A laser printer is used for hard copy.
Development work will be carried out partly on a school network and partly on a standalone Pentium similar to the one described above. All the files need o fit on a 3 1/2 floppy disk for easy transportation between home and school. They might need to be compressed if the file is too big.
The school network stations are 120MHz Pentium PCs with 16Mb of RAM attached to a laser printer. There are no user areas on hard disk, so floppy disks are used to store student work.
This system will be implemented using Excel 97. This ideal as is includes many features that can be used in customising the application, such as:
* Ability to create a blank template
* Ability to link product details entered in one workbook with invoice details entered in a second workbook through a table lookup.
* Pivot tables to create monthly summaries
* Sophisticated report facilities with the ability to format text, set margins and page layout, import a company logo if desired and preview before printing.
* Charting facilities including the ability to add a trend line
* Ability to protect worksheets and workbooks so that the user cannot accidentally destroy formulae or headings.
* Formatting to help make it clear to the user where to enter data.
* Macros to automate various functions. That will execute automatically on opening and closing workbooks.
* Customised menus and toolbars.
* Auditing tools to help ensure that there are no errors in the workbook.
1.10. User’s Skill Level
There will be Ms Harvey and the accountant using the system. The person who does the accounts is very computer-literate and generally has a good basic knowledge of Excel. With my computer system they will only need a basic knowledge of Excel because my system will be very simplistic and easy to use.