Produce a fully functional and well documented spreadsheet that solves a problem.
Montsaye College AVCE ICT Unit Three Spreadsheet Assignment
__________________________________________________________________________
“Online Direct Computers”
ASSIGNMENT TASK
For this task we were asked to produce a fully functional and well documented spreadsheet that solves a problem.
A SUMMARY OF “ONLINE DIRECT COMPUTERS”
The company sells to small corner shops in the east midlands.
All the sales to shops are currently recorded on an order form that will be processed in the company’s main office. They use calculators to find out the total cost.
They have a total of four company reps that they use to deliver orders and take new orders if required. Each of the rep carries a number of documents including order forms and a catalogue including a price list.
The current system used to take orders can no longer handle the increasing quantity of trade and a new system is required. The company decided to solve this problem by giving each rep a laptop computer. A package will be installed into the laptops to process the data.
The company will require the following features in the package:
- An automated order form to replace the current one.
- A database containing each item of stock.
- A facility to update the sales sheet.
- A summary sheet to raise the stock order from the warehouse so they can be delivered on the next round.
- A total sales sheet so it is possible to view any commission earned by the rep.
- A home page that can be used to navigate to the other pages.
- An option to print so there is hard evidence of the documents.
CURRENT METHODS USED
- All of the sales to the shops are manually recorded on an order form that is processed in the main office.
- The reps use calculators to calculate the costs.
- The reps carry catalogues containing all the products available along with price lists.
- The order forms are separate sheets.
- The reps visit the shops about once a week and they make new orders if necessary
- The reps receive 2% commission for any order over £1000.
WORKING SPECIFICATION
Front Page
This sheet will link up with the rest of the sheets in the spreadsheet so users can search it easily.
Order form
This sheet will be used when customers want to order products from the company. Information to go into the order form will include:
- Date of order
- Time of order
- Sales persons name
- Customer name and Address
- Customer contact numbers
- Name of product
- Description
- Price
- Stock number
- Quantity
- Total cost
Print out form
This sheet will be identical to the order form and will show what the customers have ordered. There will be a print option so the customers have a receipt for what they have bought.
Stock and price list
This sheet will show all the products available from the company along with a price list.
Address Sheet
This will include contact details for all the customers.
DATA CAPTURE METHODS
There are many of methods used to input data into a cell. Some are listed below.
- Manually keying in information.
- Selecting data from drop down lists
- Macro programming
- Hot keys
Below I have drawn a table to show how the information will be captured.
Below is a table to show what processing needs to be done to achieve the required output.