GNVQ Sports Catalogue Group.

Komal Kumari Spreadsheet Specification Unit 3

GNVQ Sports Catalogue Group

The GNVQ Sports Catalogue Group is a reasonable sized business that sells a wide variety of mail order sporting goods. The GNVQ Sports Catalogue Group store their customers and product details on paper. The paper-based method is time consuming because on a spreadsheet the calculations are done for you automatically (when you have entered in the function) and all you have to do is print the final outcome of the data. When it is paper based you will have lots of papers and you have to do the calculations by hand. That is why I must design a spreadsheet that meets customer requirements.

The GNVQ Sports Catalogue Group store their customers and product details in paper. The paper-based method is time consuming because on a spreadsheet the calculations are done for you automatically (when you have entered in the function) and all you have to do is print the final outcome of the data. When it is paper based you will have lots of papers and you have to do the calculations by hand.

To meet the users needs the spreadsheet must be the following things: -

Make it easy to use.
Create macros to make the user use less buttons.

To produce a good spreadsheet, good use of at least six or more complex spreadsheet facilities.

Here is the information that has to be inputted into the spreadsheet

Input

The data that has to be inputted into the spreadsheet are the following.

The data that has to be inputted into the Customer table comes from the customer. The details of the customer must include: -

Customer name
Address
Postal code
Phone number
ID number
E-mail address

Another spreadsheet must give details about sport products that the company needs to order.

The information that needs to be presented: -

Product identification number
Product name
Product description

This is a preview of the whole essay

Here is the information that has to be inputted into the spreadsheet

Input

The data that has to be inputted into the spreadsheet are the following.

The data that has to be inputted into the Customer table comes from the customer. The details of the customer must include: -

Customer name
Address
Postal code
Phone number
ID number
E-mail address

Another spreadsheet must give details about sport products that the company needs to order.

The information that needs to be presented: -

Product identification number
Product name
Product description
Price
Quantity

An invoice needs to be made so that the customer knows add the company knows that they have ordered. The information will be bought up automatically when come of the six complex spreadsheet facilities is used. The following information will be bought up automatically: -

Customer information
Date
Quantity (how much of the product the customer has ordered)
Description
Unit price
Total
Sub total
Shipping and handling
VAT
Official total

The only detail that has to be done by hand is the payment details.

All this information is essential because one of the company’s employees may need to contact the customer about an order that may have fell through.

Processing

The data processing methods that need to be done to get the required output are: -

Validate the data to see if it is accurate
Search for a particular item on the spreadsheet
Sort the spreadsheet data entries into some order

Validating the data is needed as you need to know if your information is correct. Searching a precise item on the spreadsheet is quick and easy than searching through lots of paper. Sorting the spreadsheet data into an order so that the user can understand in a way they can so that the user can understand it in some way they can.

The aids that can be provided to help with the data processing are error messages. When you are validating data something may go wrong. An error message will appear telling you what’s wrong and then you have to go and resolve it.

After the data has been processed into the spreadsheet this data needs to be presented in a different way apart from the spreadsheet itself. The output information can be presented in either a bar chart or a pie chart. This is an easier of identifying on how much the company need to order on a certain product.

Macros will also be used within the spreadsheet. The macro will be used in the spreadsheet called in the customer table. When you want to add a new customer you click on the new customer button and a blank customer profile will appear. If you want to clear the details click the clear details button.

Templates – these will be created for example when the user clicks on new customer a blank template will appear for new information to be put in.

Drop down menus will be used to select quantity and dates of an order

Functions and formulae - The formulae will calculate the totals for each data type once it has been entered. The results will show up on the page where specified. To check that orders have been paid for check boxes can be used to tick off when a customer has paid for the goods.

Links will be made to the spreadsheets. The links will have to let the customer go from sheet to sheet. This will be easier for the employee to look for data.

Comments – in the corner of the cell where there is data a comment will pop up if there is care needed to put the right information into the cell.

The spreadsheet should use several data processes the main one being look-up, which will allow the user to input a piece of data and then according to the data entered the spreadsheet will bring up addition data. For example the user could use look-up to bring up a customers details by imputing their ID or name. I suggest that the front page should have 3 sections (Customer details, product details and order details) each section will be a link to designated parts of the spreadsheet. For example if the user clicked on the customer details link it would bring up a spreadsheet of customer details. To add up the product price and delivery cost SUM can be used to create a total cost amount. There will be messages that will appear to tell the user what is needed to be done in each cell, which they click on. So if a customers ID number is required then it will pop up and tell the user that a they should input an ID number, and where there is a drop down menu it will tell the user to select the appropriate data from the drop down list. The formulae will calculate the totals for each data type once it has been entered.

The following formats will be used:

Alphanumeric – Customer name, address, postal code, e-mail address, product name, product description,

Number – Product ID, customer ID

Numeric – Quantity, phone number, date

Currency – Product cost, delivery cost and total price

Output

The output information will need to include the following things: -

Customer Details (customer name, address, postal code, phone number, ID number and e-mail address)
Product details (product name, description, ID number, quantity and price)
Order details (Customer name, product name, product ID, date ordered, paid, quantity, product cost, delivery cost and total price)