Chessington Motor Centre (CMC) is a small garage specialising in replacing and servicing Vauxhall motorcars.
Drivers bring their cars to CMC and have the option of leaving their vehicle while the work is carried out or waiting at the centre, while the mechanics do the necessary work.
Work at CMC progresses quite efficiently taking into account the facility only has space for three jobs to be done at once and a further three parking spaces for cars that have been left by owners needing repair. The car owners are then charged according to the parts used and the length of time spent by the mechanics carrying out the repair work.
There are three methods of payment accepted by CMC including cash, cheque or most popular – by credit card. Copies of invoices are made allowing one to be kept for the financial records of CMC and another to be taken by the customer for their own records. In the case of credit card, an additional invoice is made as well as the credit card receipt. At the end of each month invoices are used to help calculate CMC’s finances.
Deliveries of new parts are made every two weeks and it is up to CMC to place orders themselves for new parts allowing adequate time for the large distributor to ship them.
CMC is owned and managed by Mr R. Bailey who employs 3 full time mechanics as well as part time secretary/administrator, Mrs Giles.
The current system
The manager at CMC was interviewed, to ascertain the current workings. Mr Bailey made clear the following:
* When a vehicle is bought into CMC the damage/repair work is assessed. If it cannot be worked on straight away it is parked on site and left until space is available for the work to be carried out.
* On completion of the work, the mechanic who carried out the work completes an invoice based on the parts used and in some cases the scale of the job that needed to be done. The mechanic also completes a similar form showing the time taken to complete the task and other details relating who actually did the job.
* The customer is then charged either and payment can be received in three methods: cash, credit card or cheque.
* The current system is largely paper based with the only use of computers being to word process letters and extremely basic financial calculations.
* Completed invoices are checked and filed. This results in a large amount of paper in the main office. Invoices are filled and kept for both the financial records of the company and in case there is a problem with a job that was done, CMC will have proof that the job was carried out and properly paid for.
* Stock checking involves Mr Bailey or another mechanic checking the stock area at the end of every day and noting what parts may soon need to be re-ordered or if necessary actually re-ordering stock.
* Financial calculations rely solely on the proper completion of invoices by the mechanics and by re-counting the weekly takings Friday after CMC has closed and no longer accepts customers. Primarily, the calculations of the finances are carried out by Mr Bailey and Mrs Giles
The current problem
* The main problem with the current system is that it is far to slow and takes up valuable time that could be better spent on other tasks.
* The large number of invoices that CMC may accumulate in one day means that the office at CMC are frequently full up with large amounts of paper. As a result of this, there has been the occasional loss of an invoice, which has caused some financial repercussions. Mr Bailey made it very clear that the paper problem needs to be sorted out and if not will continue to cause problems. The large number of invoices would be much better recorded on computer or in a different format. This was again another point brought up by Mr Bailey.
* The process of stock taking and checking is a very long winded one and usually requires Mr Bailey or other member of staff checking the stock area at the end of the day for what stock may soon need to be re-ordered or indeed re-ordering it if necessary.
On the next page is an example of and invoice that would be completed by a mechanic after completing a job.
Customer Name Vehicle
Part Number Description Quantity Price +VAT
Vehicle Registration Number
Date of job (dd/mm/yy) ___/___/___
Time of start of job (hh:mm) ___:___
Time of finish of job ___:___
Type of Job
Objectives of the new system
The manager at CMC has requested that the new system to be implemented, must:
* Allow workers to complete and store invoice.
* Be usable such that a user with limited ICT skills can use it.
* Be easy to operate and generally user friendly.
Data flow diagram of the current system
Record of Invoice
The dashed line surrounding part of the data flow diagram, indicates the part of the system that will hopefully be covered in this project.
Mr Bailey and Mrs Giles control the Finance and Stocktaking department.
Performance of the new system
The points listed below are indicators that will be used in designing the project:
1. Somebody with limited ICT skills should be able to operate the system
2. All sheets should be easily accessible by a main menu screen, from which the user should be able to select his/her chosen sheet by clicking a button or selecting something from a menu
3. Accidental deletion of formulae and heading etc. must be almost impossible
4. Help on how to operate the system must be readily available
5. The time taken to enter each invoice must be under 1 minute
6. Macros should be smoothly executed and the user should not see “the working”
Availability of software and hardware
CMC has available for use 3 PC’s each with 9Gb hard drives and 128Mb of RAM. A single laser printer is used to produce “hard” copies of documents, as all 3 computers are linked on a network. As for software, the CMC network includes Windows 2000 as well as Office 2000, which, allows access to programs such as Microsoft Word, Excel and Access.
Production of the system will take place both on a networked school system, which gives all students a user area to which work can be saved. Work will also be carried out at home on a stand alone PC with similar specifications to those mentioned at CMC.
To aid the transfer of work between home and school a floppy disk will have to be used. If the size of the system and its documentation exceeds the capacity of the disk, several may have to be used for home to school transportation. If for any reason the system exceeds the storage capacity of the disk then the system will be e-mailed as an attachment to a web-based e-mail account the retrieved and worked on from home. Then similarly sent back to the school e-mail system.
Skill level of users
Mr Bailey, the manager of the company has an excellent knowledge of Excel and is extremely computer literate. Similarly the Administrator is recognised as having almost the same level of knowledge as the Manager.
The three mechanics have an extremely basic computer literacy; however Mr Bailey has stated that he will finance basic training in Excel to the required level of knowledge.
The system will be implemented using Excel 2000 as this allows many advanced features that will help improve the current system at CMC.
The advanced features that will be included are:
* Ability to create and fill in a blank invoice
* Ability to lock individual work sheets to prevent accidental changing of template
* Macros to automate changing between worksheets
* Creation of templates to help employees filling out invoices
Design of the worksheets
Sheet1 will be the MAIN MENU and subsequently the first sheet seen by the user when they access the system. To make sure it is the first seen, I will use an “Auto_Open” macro. The sheet will provide the user with clear and quick links to other sheets by buttons that must be selected. There will be 6 buttons to choose from; Exit, New Invoice, New Timesheet, List of Invoices, List of Timesheets and Stock and Price List. Also displayed will be the company logo so that the system is personalised to CMC.
Sheet 2 will be a blank invoice. The invoice will be laid out in much the same way as the old paper invoices CMC used to use. Validation will be used on the cells containing the date, invoice number and vehicle registration number. The following formulae will be used in the following cells:
Also on the sheet will be 3 buttons. The first will link this sheet to the Main Menu and the second to the Stock and Price List. The third will save all entered data to the List of Invoices sheet.
Sheet 3 will be a blank timesheet. Similarly to the Invoice sheet, this will be laid out in a similar way to the old paper Timesheets that used to be used. Validation will be in the cells containing date and time. The following formulae will be used in the following cells: A drop down menu is available for specifying the type of work carried out. Buttons on this sheet link it to the Main Menu. There is also a save button which will save any data entered to the List of Timesheets sheet.
Sheet 4 will be a list of the last 25 Invoices saved. Only key parts of the invoice can be saved; the Invoice Number, date, Number of Parts used and Total price for the whole invoice. A Main Menu button will be in the bottom corner to take the user back to the Main Menu.
Sheet 5 will be a list of the last 25 Timesheets saved. Once again, only key parts of the Timesheet can be saved: Invoice Number, Start and Finish time and the individual Mechanic ID code. A Main Menu button will be in the bottom corner to take the user back to the Main Menu
Sheet 6 will present the user with a price list of all parts kept on site at CMC and all jobs that can be carried out. Each part will also have a unique code. Once again, on this sheet is a button linking this sheet to the Main Menu and the Invoice sheets.
Entering of data and validation
Entering of the date – dates must be manually entered. The date will always be presented to the user in the form dd/mm/yy but can be typed in any format. For example: 12 Dec 2001 will automatically change to 12/12/01
Invoice number – cells in which an invoice number must be entered have been validated to only accept a whole number between 1 and 9999999999.
Time – the time like the date, must be manually entered in the form hh:mm on the 24-hour clock. Cells in which time must be entered have been validated to only accept times in between the opening hours of CMC, 09:00 to 17:00