The company which I have decided to do my project for is AutoPlastics, a company that deals primarily with the supply and repair of plastic car bumpers to both the public and insurance companies. The owner of the company is my end user.
The company is currently experiencing problems with its ordering system. When a person phones and places an order or asks for a quote their details are taken and written into a book. A customer is then contacted within a day and provided with a quote and the company then arranges a suitable delivery time with the customer. The quote given to the customer is based upon the price at which the bumper must be purchased from the manufacturer if they do not currently have it in stock.
If a bumper must be ordered from the manufacturer, 15% is added to the price to cover the cost of ordering and installation of the bumper. The problem occurs when the price of a bumper in stock is required that is currently in stock as the company have such an extensive range. The prices of the more popular models vary frequently (in order to meet with the competition), and are entered from memory. As a result mistakes are often made resulting in inaccurate quotes being given to customers. If an individual feels that they have been overcharged the result will be an unhappy customer who may choose not to use AutoPlastics again, and the companies overall profits will decrease.
The paper based system currently employed also makes it difficult to keep track of orders made by customers and keep an accurate record of the most popular bumpers and frequent customers. The company is also in need of a printed form of recipt because at present recipts are written onto recipt slips by hand and the company has no record of sales. There have also been a number of customers who request V.A.T recipts which the company is unable to provide at present.
Company Background Information
AutoPlastics is a relatively small company which receive upto 50 orders per week, given the size of this company and the fact that there are only 3 employees a computer based system would be beneficial to the company. The need for such a syatem is justified by the improvement to the company that it will provide and may also result in a net profit to the company.
End user requirements
Auto Plastic Bumpers are in need of an ordering system which will keep computerised records of customers orders so that they can easily be recalled when needed for reference purposes, allowing the company to keep an account file for frequent customers such as insurance companies. The orders may also be printed as used as receipts. The system must also indicate only currently available stock as to indicate wether or not new stock must be ordered to meet the needs of the customer.
Tasks to be completed on the system
1. Produce spreadsheets which will make up the booking and ordering system for the company
2. Create a booking system which will enable the customers details to be easily entered into the spreadsheet and then be printed to function as a recipt.
3. Adapt the ordering system spreadsheet to automatically calculate the total of a customers order and display prices both inclusive and exclusive of V.A.T
4. Create a backup system to ensure that data will not be lost due to computer error.
5. Introduce a front end into the spreadsheet to enable easy and direct access to the required function.
Implemetation free specification
1. Increase the speed at which customer bills are calculated from approximately 1-5minutes to 30seconds-1minute.
2. To ensure that calculated totals are accurate. Currently totals are calculated manulally which often leads to errors. Using a spreadsheet will eliminate calculation errors.
3. Reducing the amount of time spent calculating totals will enable members of staff to spend a greater amount of time on other tasks.
4. Achieve a higher customer satisfaction and improve company image. A higher Customer satisfaction will also result in a greater chance of a customer will use Auto Plastics again.
Computer resources available to the company:
* Processor-Intel Pentium II processor operating at 400Mhz
* Hard Disk-16Gb
* OS-MS Windows ME
* Software-MS Office XP Professional Edition + Nero Burning Rom
* Printer-Epson Stylus 640 Inkjet printer
* HP CD-Writer
Human resources and End user’s IT literacy
The company currently employ 3 members of staff all of which have a basic understanding of computers and are familiar with MS Office and associated applications. This will mean that my soluction will not be that limited in its complexity and may not be required to be automated.
The software and hardware available will not really provide me with any limitations other than the fact that invoices will really need to be A4 in size as this is the standard size used by the printer. I believe that given the current price of CDR media (25pence) I will be able to use this type of media as a backup system for the company.
The combination of the problems mentioned within the Problem Statement has seriously affected the efficiency of the business and as a result company profits are not as high as they potentially could be. The ordering system currently employed is insufficient and inaccurate. A new system is required which will enable the company staff to enter the details of the required bumper(s), the system must give a price both inclusive and exclusive of V.A.T which can be given to the customer. If the bumper is currently not in stock the system must indicate this and reflect this in the price accordingly.
I believe an IT based solution would most adequately meet this specification enabling all the required criteria to be met and satisfied.
Skills: current/to be aquired
At present I believe I am able to carry out all of the needed fuctions except the introduction of a main menu using macros. This is a topic I will have research in some depth.
Input, Process and Output
The template spreadsheet will be a blank form which will have the customers details entered into it by a member of staff. This file will be read only:
Every time a new order is placed this file will be loaded via some sort of menu and the relevant data will be entered into it
The price details of the required bumper will then be entered from the manufacturers/suppliers catalogue. Using this data the spreadsheet will then automatically calculate the total cost. At this stage a copy of the spreadsheet is also printed which will function as a receipt for the customer.
The spreadsheet will then be saved with the customer name, surname and unique customer number as follows:
smith john 1234
This naming system will enable customer’s details to be easily located. Every time a new order is placed by an existing customer it will be saved as a new workbook within the same spreadsheet.
Every week in addition to storing the data on the computers hard disk the data will be backed up on CDR disks to prevent loss of data.
This method of data backup will also allow the company to easily keep records of data for 7 years as is required for tax purposes. Due to the high capacity of CDR disks it will be possible for the company to store a few months worth of accounts on one CDR disk.
How the system will fit together
Tasks to be carried out
Create an order form spreadsheet:
* Set up a template which contains sections in which the relevant data can be entered by the end user
* Macro to calculate totals both inclusive and exclusive of V.A.T
* Macro to calculate the total amount of business provided by a customer based upon the information within a workbook
* Macro create a new workbook using a standard design
* Macro to print a receipt
* Protect/lock cells to prevent heading/formulae from being accidentally altered by user
* Create a mocro which gives the user the option to open a customers spreadsheet
* Create a macro which will enable the user to delete a customers file when no longer required
Spreadsheet design for ‘Auto Plastics – Order form’
Main menu design
I will create a user form for my end user which will enable them to carry out the following functions:
New Customer – Create an account spreadsheet for a new customer
Existing Customer – Place a new order for an existing customer
Delete customer – Remove a customer’s spreadsheet from the company database
Cancel – Closes the Front End
Macro designs for the main menu
Method of activation
This macro will load a blank copy of the AutoPlastics order form ready to be filled in
This macro will load the Auto Plastics order form. The user must then enter the required data using, then save button on the Auto Plastics order form.
A button labelled ‘New Customer’ will be used to activate this macro.
Method of activation
This macro will allow the user to load a customers spreadsheet and will create a new workbook in which their new order will be filled in
This macro loads a customers spreadsheet and creates within the spreadsheet creates a new workbook. The user must then enter the details of the new order into the new workbook and save the file when finished.
This macro will be activated by clicking the ‘Existing Customer’ button
Method of activation
Will allow a customers spreadsheet to be deleted
When selected this macro will enable the user to delete a customer spreadsheet. Either because it is no longer required or because an order has been cancelled.
Clicking the ‘Delete Customer’ button will give the user this ability
Method of activation
This button will close Excel
When pressed this button will activate the macro which will close Excel
Clicking the ‘Cancel’ button will activate this macro.
Spreadsheet designs for AutoPlastics ordering form
Text – V.A.T rate
Text -‘AutoPlastics – Order Form’ title
Text – Customer detail titles
User fills in the make of the bumper eg ford
Number – a value which represents V.A.T eg 17.5
Text – surname title
User fills in the model of the bumper eg Mondeo
The year of the required bumper is entered eg 1996
The location of the bumper is entered by the user. E.g. front
The number of bumpers required is entered
The price per required unit is entered by the user
The total per row is calculated by the spreadsheet
Total including V.A.T
Total excluding V.A.T
Eg J14 – =H14*G14
The implementation of my project was begun by setting up a template workbook which contained an order sheet in which the customer’s details could be entered. The template will be protected and will only enable the user to save it with a different file name than that of the template. As a result this template can be used reused permanently. The template was successfully created:
The headings used within the the template were specified by the the company Manager.
Many of the cells themselves within the template will be protected to prevent accidental deletion/modification of fields and formulas. The following diagram shows which fields the user will be permitted to enter data into:
Shaded areas show where data can be entered by the user.
These cells permit data entry because it is in these that it is necessary to create a usable receipt. In the highlighted cells both numeric and alphanumeric data can be entered by the user. All of the data will be left aligned to keep a neat and tidy look to the project.
The following diagram shows which fields the user may not enter data into.
Shaded areas indicate where the user will not be able to enter data
The highlighted cells will be protected mainly so that formulas cannot be changed. If this was to occur inaccurate Totals would be calculated which was one of the problems facing AutoPlastics initially. Altered headings would simply make no sense within the spreadsheet and cause it to look untidy.
The next step was to create a menu spread sheet which would enable the user to load the blank template and create a new customer account, open an existing customers account in order to place a new order, delete a customers account or close excel.
This menu will be located within the excel spreadsheet called ‘AutoPlastics Main Menu’. This file will be situated on the desktop to enable the user easy access. Once loaded the Menu will contain 4 distinct options:
* New Customer
* Existing Customer
* Delete Customer
A menu will be created because it enables easy access to the facility required by the user.
Clicking the ‘New Customer’ button will load the AutoPlastics template workbook by activating the new_customer macro. The user will then enter the customer’s details and print/save the file with a unique file name.
Using the ‘SAVE AND PRINT’ button at the top left of the AutoPlastics template workbook will be used to save and print the file. A single save and print button has been created simply because it will be easier and quicker to use. It will also automatically generate the required file name.
The ‘SAVE AND PRINT’ will automatically activate the save and print macros which will save the file with a unique filename…
…using the customers name as the filename and print the spreadsheet which will then function as a receipt. If two customers share the same name the following dialog box will appear
Clicking ‘No’ at this point will enable the user to rename the file to make it unique. If this occurs (as is expected) the user will simply add a four digit number to the end of the file name to make it unique. This number will consist of two digits representing the day of the month e.g. 09. The second two digits represent the month e.g. 06.
Clicking the ‘Existing Customer’ button will load the excel open option:
Enabling the user to load the existing customers account details. This will be achieved by the button activating the existing_customer macro. Another macro simultaneously activated will also create a new workbook in which the customers new order details can be entered, called new_workbook.
The next step is the ‘Delete Customer’ option. This will load the delete_customer macro. This macro will load the delete dialog box and enable the user to select a file for deletion.
By clicking the final ‘Cancel’ button the user will activate the cancel macro and cause excel to close. Before closing the customer will be prompted to save or not in case they have not done so:
AutoPlastics – Order Form
a diagram to show the entire AutoPlastics order form
The order sheet is divided into two main sections, simply because that is all that was required by the user. One for customer details, one for order details and one for totals and V.A.T.
In this section the user simply enters the customer’s details. This is so that AutoPlastics are aware of where the bumper(s) must be delivered to.
This section contains formulae which, when the necessary data has been entered, will calculate a total for a particular item.
Totals will be calculated at high speeds using formulae within the spreadsheet instead of manually as in the previous system. This will increase accuracy and now enable the customer to see prices exclusive of V.A.T.
AutoPlastics Order Form
The results of my questionnaire seem to indicate that my system meets the needs of my end user. It met any and all criteria specified by them and appeared to be faster than the previous system at calculating a total which could be given to the customer. In order to test this, the times taken to enter 6 orders were taken, 3 using the previous system and 3 using the new one. The times were as follows:
As the table clearly indicates there has been a clear time improvement which will ultimately result in less time spent calculating and more time spent in other areas of work.
Testing the formulae of AutoPlastics Order Form – quantity, price per unit and Total test
The best way to test the formulae will be to enter a variety of different values and to check whether the values are as they should be.
Test the formula entered within cell J14
A correct result of ï¿½360 was given indicating that this formula and the rest down the row were also correct.
Testing the formulae of AutoPlastics Order Form – V.A.T values
G30, I30, B30
To test that the V.A.T exclusive value formulae (I30) is correct.
ï¿½297. This indicates that the formulae are functioning properly.
This process was repeated several times using a range of vales and cell references. Each test resulted in the expected result
Testing that cells are correctly protected
This area was tested by attempting to enter data into cells which contained formulae. The result was the following message:
This indicated that the test had been successful and that the cells were adequately protected.
AutoPlastics – Main Menu
In order to test the menu he buttons were simply pressed multiple times to test their reaction and check for any differences in response. Luckily, in each case they functiond as required and activated the appropriate macros.
Produce spreadsheets which will make up the booking and ordering system for the company
The AutoPlastics Order form template was set up to meet these specified criteria. It enables the user to enter the required data where necessary and will keep a permanent record.
Create a booking system which will enable the customers details to be easily entered into the spreadsheet and then be printed to function as a recipt
A macro was created which will both save the spreadsheet and print a copy. This copy is then given to the customer as a recipt, a copy is also saved onto the computers hard drive and kept for future reference.
Adapt the ordering system spreadsheet to automatically calculate the total of a customers order and display prices both inclusive and exclusive of V.A.T
A forula within the spreadsheet made this possible and also enables the user to alter the V.A.T value without modifying the formula
Create a backup system to ensure that data will not be lost due to computer error
Given the time constraints of the project and the ease of backing up data onto computer, the company have decided that they will simply create ISO compliant discs using Nero Burning Rom.
Introduce a front end into the spreadsheet to enable easy and direct access to the required function
A menu spreadsheet was produced which is located on the desktop of the companys computer. It gives the user access to all the required functions quickly and easily.
I have successfully met the criteria required by my end user as was stated in their interview. My system is not very flexable, however this was not something required by my end user. It is relatively user-friendly with no problems occurring to date. Its interface was described as “very simple” by the end user and the companys employees found it easy to use. The system could be easily transferred to another computer by simply copying and pasting the spreadsheet files to a new location although they would need to be in the same directory. My product is cost effective as it does not require the user to purchase any additional equipment and makes use of a computer previously only used for games. Due to its simplistic nature and interface the system was easily used by the company Staff and they required no training as they found the User Documentation sufficient.
Starting the software
From the desktop start (by double clicking) the ‘AutoPlastics’ Main Menu
If faced with this option click ‘Enable Macros’
Select your option from the menu:
This option will load a blank copy of the company order form spreadsheet and enable the relevant details to be entered:
Enter the required details and watch as your totals are calculated!
Note: If you receive the following message don’t worry, it simply means that you have attempted to enter data into a protected cell.
Click ‘OK’ and continue as normal.
Clicking this will open the following dialog box:
Simply select the required account and click ‘Open’. You will then be presented with the following spreadsheet:
When finished save using the ‘SAVE AND PRINT’ button. This will save the file using the customer name and print a copy of the current Sheet to be used as a receipt.
This button will load the following box:
Simply select the file you wish to delete and press the delete button.
Caution: Once a file ahs been deleted it will be permanently removed from your system! …Unless restored from the recycle bin.
This option will close excel and return the user to the desktop.