In the spreadsheet I am going to design I am going to use the following ‘Source Data’. The data to go into the catalogue has been supplied by the company. The data to be used is written below: The information in the table will be displayed on four different spreadsheets but they will be used in different contexts. On one of the screens it will be displayed as it is above, as a simple list of options. On other screens it will be used as a form of stock control, an order form and as a list on an invoice. Another function that I will use on some of my spreadsheets is the AutoSum.
This will be used to add up the price of all of the items odered on the Order form. In the workbook I am going to create I will use macros to make the user interaction to a minimal. This means that there is less chance of human error in the calculations that have to be done and that it is easier for the operator to use. All of the macros will be controlled by the buttons that they will be attached to. The only button that will create an ‘hard copy’ output will be the Print Invoive button. A copy of the invoive will be included in the User guide.
In the workbook I will try and include as many of the complex facilities (see list below) as I can. The ones that I am most likely to use are highlighted in the list. Lists and tables – sorting, lookup tables, subtotals and totals List boxes and drop – down boxes to select data for entry Styles to create a customised cell format Named cells and ranges for use in formulae Auto-fill lists – for lists of dates or days of the week Validation – restricting data input to acceptable data values Templates – creating standard spreadsheet layouts for repeated use
Protecting cells by hiding and locking cells Sort – to sort single and multiple columns of data Control buttons – to initiate macros Multiple sheets with links between them Multiple views or windows. Although the key of the workbook design is functionality I will also have to think about the aesthetics of the design. The aspects that I will have to look at are things like font size and colour, continuity of the colour scheme and the layout and spacing of control buttons within the individual pages. I have chosen blue (******** ). This is for the following reasons.
==> It highlights the white areas of the page that the users are supposed to be looking at ==> It is an imposing colour that works well for the purpose of continuity in the entire workbook On the ‘Home Page’ i will also use the designated blue colour. In my workbook I will include a home page that will allow the user to access the whole program with very little hassle. The home page I will construct will utilise the use of macros that are attached to control buttons. This makes navigation of the workbook a lot easier. The GNVQ Sports Catalogue Group: Technical Documentation
The above screen dump shows the home page of the system. When the ‘Home’ button is pressed this is the screen that is presented. The Macros that run on this page are: When the Catalogue button is pressed: When the Customer button is pressed: When the New Customer button is pressed: When the Stock button is pressed: When the Stock Chart button is pressed: When the orders button is pressed: When the Invoice button is pressed: The above view is that of the Customer page. The macros active on this page are: When the Home button is pressed: When the New Customer button is pressed: see macro listed on previous page.
When the Sort List button is pressed: The Stock screen above has the following macros running on it: When the Home button is pressed: see macro listed on previous page When the Replenish Stock button is pressed: The order screen has the following macros placed on it. Complete Order: too see this macro look at appendix 1(attached sheet) Invoice: this macro is the same as the one on the ‘Home’ page Home: this is the same macro as the one placed on the previous page. On the Invoice the macros that are present are: Billing Address: See appendix 2 Print Invoice:
Home: see the Home macro on the previous sheets All of the macros that are present in my system are attached to buttons that are appropriately. In my program I used the VLOOKUP function to import information from one worksheet to another. I also linked this function to the IF function. This means that the information is only imported if certain criteria are met. This function is evident in the ordering process. I use this so that the user only has to type in the customer ID and the program does the rest.
This function looks to see if the customer ID is typed. If it is then it looks for the same customer ID on the customer page and imports the surname. There are other examples of formula like this in my program but these are the ones that illustrate its use the most. Data entry: to enter data, i. e. new customers, the system uses data entry forms. The example for this is in the user guide. When I started to employ the idea of using forms I came across a problem. The program would not run the macro for the data entry form. I overcame this problem by not selecting the header of the page. This allowed the macro to run. Stock Chart: as instructed I have included a chart that is linked to the stock levels.
The columns of the chart adjust according to the values on the stock worksheet. (See example on Appendix 3) The Link Between The Chart And Sheet: for the example of the linked stock graph see Appendix 3. Functions used: in this spreadsheet I use the following functions that are supported: Function Purpose Data Protection This is used to stop unauthorised people accessing and changing any of the prices on the Catalogue worksheet VLOOKUP This is used to import information from one worksheet to another. f function This is used in conjunction with the VLOOKUP so that information is only imported if criteria are met.
Macro’s These are used to make the program as easy to use as possible. They are used to complete the order and move from page to page. Buttons These are used to initiate the macros. 1. Navigating the Program When the software is first turned on you are presented with a title screen and navigation menu (see screen dump) All of the buttons that are visible take you to the spreadsheets that are relevant to the titles. I. e. the Stock command button takes you to the screen that controls all of the stock issues. This allows for the user to easily navigate the system and makes it easier for the operator to fulfil the needs of the customer faster. All of the screens have a ‘Home’ button that allows you to go direct to the title screen and navigationmenu.
2. Entering New Customers If the user wants to enter a new customer it can be done in two ways. ; From the Title screen using the ‘New Customer’ button ; From the Customer Screen again using the ‘New Customer’ Button Both ways of entering anew customer use the same method. This is a form that appears when you press the button. (Example of form shown below) Once the data has been entered a Customer ID needs to be created. This is created by using the first two letters of the surname, the first four digits of the phone number and then putting the whole surname on the end.
(The customer must be made aware of their ID, as it is needed when they wish to order. ) Once the new customer has been entered you must press the ‘sort list’ button because other wise the program will not work. 3. Customer Ordering If the customers are not sure of the price or specified use of the football there is a catalogue screen showing all of these. When the user wishes to commence with the order they need to go to the order screen. (Shown below) Pressing the ‘Orders’ button on the ‘home screen’ does this. The operator will then have to take customer ID and type it into the space shown.
This will then automatically look up the rest of the customer’s details and fill them into the adjoining cells. To make the order all the user has to do is enter the quantity of each football wanted into the ‘Qty’ column. When this is done the total price is calculated. When the order is complete the user then has to press the ‘Complete Order’ button. When this is pressed all of the information on the order form is placed onto the ‘Invoice’ screen of the system. 3. 1 Invoicing the Customer The user then has to press the ‘Invoice’ button to get to invoice screen. (see next page for example).
Once on the ‘Invoice’ screen then all that is left to do is press a few buttons. The billing address for the invoice is generated automatically when the billing address button is pressed. To print the invoices simply press the ‘Print Invoice’ button. Example of Printed invoice Invoice Product Name Product Name Price Qty Sub Total GNVQ football G00 i? To Mark Hancock From GNVQ Sports Catalogue 30 Orchard Way Brook Lane Ind Estate North Bradley Westbury Wiltshire Wiltshire BA14 0SU BA14 0DJ 4. Stock Control Most of the stock in this program is automated.
This includes the updating of the stock once an order is placed. Another way of seeing the company stock is to view the ‘Stock Graph’. Pressing the button that is situated on the ‘Home’ page easily does this. This function also allows the user to easily produce a hard copy of the stock situation. The only thing that needs to be done manually is the replenishment of the stock. This is easily done by pressing the ‘Replenish Stock’ button. 5. Closing the Program To log off of the program simply return to the ‘Home’ page and use the cross button in the top right hand corner of the window.