Produce a fully functional Essay Example for Free

Produce a fully functional Essay 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 companys 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. Typing into cells Selecting data from a list Automatic Hot keys Customer name Rep name Date Print Address Stock description Time Saving work done Contact numbers Stock model no. Total cost Quantity Total quote cost Below is a table to show what processing needs to be done to achieve the required output. Stock Code Lookup information within a database. Display stock in a cell. Rep Name Process index number from the rep table. Display the data in a cell. Customer Information Lookup information within the database. Display stock in a cell. Time and Date Process the system date and time. Display time and date in the cell chosen. Commission Finds out a total if over i 1000, and works out 2% of it. Displays commission total in a cell chosen. For a spreadsheet database to be successful, it should be as error free as possible. Information should be easily entered. An excel spreadsheet can provide the following functions. Tips and Help, to make it easier to use. Formatting of cells.   Labels and titles.   Highlighting and background colours   Drop down lists Validation and verification DESIGN AND DEVELOPMENT By designing the spreadsheet first, it will be easy to put it together. I will provide ways of easily entering data into the spreadsheet. These will include   Data entry forms   Form like sheets I am going to enter some tips to help the user use the database. These will include.   Data entry messages   Validation messages Marcos will play a big roll in the design of the spreadsheet. I will use it to make the spreadsheet user-friendlier. It will be used to   Simplify data input.   Produce printed reports I will use more complex spreadsheet facilities to make it even easier to use.   Drop down lists- so data can be selected from a choice and not typed in every time.   Named cells- some of the cells will be named so it will be easy to use when carrying out formulae. Validation- this will restrict what data can be put into certain areas so it is easier for the users.   Lists and tables- these will be used to sort the data so it can be looked up. The Front Page I will place a variety of links and macro buttons on this page so that users can search the spreadsheet easily. The buttons will all be placed on the page. I will include a company to show the user that they are on the right program. I may colour code it to make it user friendly. The order form The order form will contain many formulas. I will use a combo box to create an area where the customer information goes. When the user selects their name in this box, the rest of the information will be shown. I will use V-lookup to do this. V-lookup will also be used to find products that the user wishes to order. When the stock code is typed in, information on that product will be shown. The column headings will be stock code, stock and model number, description, and price. I may colour code the different columns to make it more user friendly. In order to find the total cost for the products the customer wishes to buy, I must multiply the cost of the product by the quantity. Calculation Sheet This sheet is private and can only be viewed by the sales representatives. All calculations the reps need to make will be done on this page. For example they can mark up prices on this page. As this page is private I will insert a lock function with a password so customers cannot enter it. Commission form The function of this page is to calculate the amount of commission. The formula to calculate this will be: . This means that the rep will only get commission is a sale is over i 1000. ORDER FORM DEVELOPMENT This order form includes all the relevant information needed. I have neatly lined up the buttons next to the customer details box. However there are a few problems with it. It looks very bland. None of it is emboldened so important parts dont stand out. There is no title to show what the sheet is for. I will include these improvements in my next design. Certain important words have been emboldened. This makes it much easier to read and looks more professional. I have placed Order Form at the top so the user can see what the page is for. The buttons are in an odd position and should be rearranged to get a better balance. The form should include Online Direct Computers address in case the used needs it. Also an option should be added to show the sales person. Also it currently shows the price without VAT. Another calculation must be added to show the price with VAT added. It is still very dull so some colour needs to be added to make it more attractive and more user friendly. By adding colour and rearranging the buttons has improved the image and it now looks finished. The sales person name can now be added from a drop down list box. I will maintain this image throughout the program. TESTING I carried out a number of tests in order to check that the spreadsheet is running correctly and to check whether it matches up with the specification. There were four tests that I carried out: Continuous Testing   Testing Data   Invalid Data Testing   Specification test Continuous Testing- I will test all the formulas that I have put into the database including all the combo boxes and buttons. Testing Data- I am going to test data in the spreadsheet to see if it all works properly. I will test: Product information   Customer details   Sales representative names Invalid Data Testing-I will deliberately input some invalid data into some of the cells to see whether the error messages, that I created, appear. Specification test- I will compare my original specification to with my finished package. This will tell me whether the package will be able to do the job the user requires.E-Mail [emailprotected] com Invalid Data If I enter a stock code that isnt between 1000 and 1047 an error message will appear because there is no stock out side of this. If letters are typed in by accident an error message with appear. Specification Test Original Specification Comments Target Met Front Page This sheet will link up with the rest of the sheets in the spreadsheet so users can search it easily. I have created a front page that links up all of the pages in the database. 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 Date and time of order have been added into order form using the formula =NOW() The sales person names have been inserted into a drop down list that can be selected. There is space in a box at the top of the form for the customer name. This automatically there address and contact numbers. There is a box on the order form for stock codes to be typed in. This automatically brings up details including a 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. I have included an option on the order form to print so customers will have a receipt of what they have bought. Stock and price list This sheet will show all the products available from the company along with a price list I have produced a list of all the products available from online direct computers Address Sheet This will include contact details for all the customers I have produced a list of all the customers and there contact details. Examples of the sheets.