Technical Documentation for a data processing problem. Spreadsheet

Technical

Documentation for

a data processing

problem

Spreadsheet

User Requirements

Background to the problem

Elegant Attire is a small local family business set in Northern Ireland.

The company specialise in formal Hire for both men and women. Their customer base is largely focused on A Level students and they cater for all the needs for the Christmas or end of year affairs. Elegant Attire is very aware that this is an extremely important occasion and aspires to ensure full satisfaction of their customers.

Elegant Attire is a small sized company that sells a variety of formal wear for both men and women. Customers can place an order over the phone or by filling out a simple order form and posting it or by calling into the shop.

They have always used a paper based system but as their company is expanding they have found it necessary to introduce ICT to help them with their orders and invoicing.

There has been, on a few occasions that stock has not been returned on time to have it washed and ready for another big formal. They are hoping that this new system can easily create invoices for customers.

Aims and Objectives

I have decided to design and develop a spreadsheet model. It requires storing customer details, products and invoices and orders.

The spreadsheet model should offer the facility to produce a standardised invoice based on the order details. A discount should be offered if the customer spends over £200.

In order to identify themselves customers are required to include an account identification number. The systems should be able to automatically pick up the customer details from the store of customer details based on this number.

There should be a facility for the managers to review the total number of invoices and view information in a graphical format for decision making.

The following worksheets are required: • Main Menu – Main navigational menu

• Customer details – Stores all names and addresses of the customers • Stock details - all details of stock

• Invoice – Should calculate totals of items purchased and vat • Invoice store – stores all completed invoices

• Sale statistics – Graphical view of the last 5 invoices, should show the average invoice total, maximum invoice total and total number of invoices.

The spreadsheet model should offer the facility to produce a standardised invoice based on the order details. A discount should be offered if the customer spends over £200.

In order to identify themselves customers are required to include an account identification number. The systems should be able to automatically pick up the customer details from the store of customer details based on this number.

Main Menu – Main navigation menu

Elegant Attire

Logo

Formal Hire

The colour scheme of the Spreadsheet should be Background: Yellow

Protection

The page should be protected.

Hyperlinks

The six hyperlinks should link to the following worksheets: These should be macros

• Customer details • Stock details • Invoice • Invoice store • Sale statistics Menus

The main Microsoft excel menus should be visible. All formatting and standard menus should be removed All horizontal and vertical scrollbars should be removed.

Customer Details Worksheet

Customer

Details

Table of Customer Details

This table must be at the top of the worksheet

The customer information includes customer number, forename, surname, house number, street, area, post code and contact number.

Field Format Validation

customer number Text Field Between 1 and 15 characters forename Text Field Between 1 and 15 characters surname Text Field Between 1 and 15 characters house number Number Number allowed between 1 and

3000 digits

street Text Field Between 1 and 15 characters area Text Field Between 1 and 10 characters post code Text Field Between 1 and 10 characters contact number Number Number allowed between 1 and

Details for the following Buttons:

The add customer button is a form and is used for adding, deleting and searching for customers. Add Customer

Sort by

Number The sort by number button is a macro that sorts the customer details by their number.

Sort by Surname

The sort by Surname button is a macro that sorts the customer details by their surname.

Stock Details Worksheet

Home

Stock Details

Stock Details Table

Logo Add Stock Item Sort by Price Sort by Name Sort by Number

Their stock information includes the stock item number, item name, description, quantity required, size and price.

Field Format Validation

Stock number Number Field Between 1 and 200

Stock Name Text Field Between 1 and 15 characters Description Text Field Between 1 and 50 characters Price Currency Number allowed between 1 and

500

Size Text Field Between 1 and 15 characters

The details for the following button are:

Add Stock Item

This option should be a form to add , delete and search for stock

Sort by Price

This option should be macro to sort the stock by price

Sort by Name

This option should be macro to sort the stock by name

Sort by

Creating an Invoice

The spreadsheet model should offer the facility to produce a standardised invoice based on the order details.

Home Clear Print View Invoices Store Invoice

Totals including vat and discount Stock Items Purchased

Date

Unique invoice number Customer Details

Address of Elegant Attire

Date

Invoice forms should have the current date read from the system =Date() It should automatically generate a unique invoice number.

Invoice Number

The invoice number of the last order should be stored in a cell called count and used in a formula to increment the invoice number by one. =Count +1

Customer Details

In order to identify themselves customers are required to include an account identification number. The systems should be able to automatically pick up the customer details from the store of customer details based on this number.

The Customer details should be included on the invoice. In order to ensure that the customer details are correct the customer number should be chosen from a data validation list. All the other customer details should automatically fill in using lookup formula and viewing the customer details worksheet.

Stock details

The Stock details should be included on the invoice. In order to ensure that the stock details are correct the stock number should be chosen from a data validation list. All the other stock details should automatically fill in using lookup formula and viewing the stock details worksheet.

The customer should be allowed to order only 5 items per invoice

Totals

Invoice forms should automatically calculate the total cost of all goods and the cost of the total order and the VAT at 17.5%. This will speed up the ordering process and reduce the likelihood of human error.

Discount

Store Invoice This is a macro that copies the details of the invoice to the invoice store worksheet to be stored permanently.

View Invoices

This is a macro that opens invoice store worksheet

Print This is a macro that prints the invoice

Invoice Store Worksheet

Invoice Number Date Customer ID Stock Id Size Subtotal Vat Sub Total Discount To Home

The following details are important and should be copied automatically from the invoice to the invoice store

Sales Worksheet

Macro to create graph form sales invoice worksheet

Clear Sales data and current graph Click here to see a graphical view of the last 5 sales

Statistics

Statistics

Graph showing last 5 invoice

details

Details used to create graph

Macro to delete graph and details used to create graph.

Statistics

Total Number of Invoices = Count formula

Average Invoice Price = Average formula