AutoPlastics I.T. System

* 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.

Final choice

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

Stage 1

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

Stage 2

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.

Stage 3

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.

Stage 4

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

Existing customer:

* Create a mocro which gives the user the option to open a customers spreadsheet

Delete customer:

* 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

Name

Brief description

Steps involved

Method of activation

new_customer

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.

Name

Brief description

Steps involved

Method of activation

existing_customer

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

Name

Brief description

Steps involved

Method of activation

delete_customer

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

Name

Brief description

Steps involved

Method of activation

cancel

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.

Testing Plan

Spreadsheet designs for AutoPlastics ordering form

Column

Row

Description

Formulae

A

B

B

B

B

C

C

D

E

G

H

J

G

I

30

2

4-10

4-27

30

5

4-27

4-27

4-27

4-27

4-27

4-27

30

30

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

N/A

N/A

N/A

N/A

N/A

N/A

N/A

N/A

N/A

N/A

N/A

Eg J14 - =H14*G14

=SUM(J14:J27)

=G30-B30

Implementation

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

* Cancel

A menu will be created because it enables easy access to the facility required by the user.

New Customer

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.

Existing Customer

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.

Delete Customer

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.

Cancel

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.

Customer Details

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.

Order Details

This section contains formulae which, when the necessary data has been entered, will calculate a total for a particular item.

Totals

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.

Testing

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:

Previous system

New System

Test

number

Time

taken

Test

number

Time

taken

6:00

2:05

2

5:00

2

2:32

3

5:30

3

2:58

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.

Cells Used

Data entered

Reason

Result

G14, H14

3, £120.00

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

Cells Used

Data entered

Reason

Result

G30, I30, B30

=SUM(J14:J27)

[£360]

=G30-B30 [£359.83]

7.5%

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.

Evaluation

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.

User Documentation

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:

New Customer

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.

Existing Customer

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.

Delete Customer

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.

Cancel

This option will close excel and return the user to the desktop.

- 1 -