Constraints; input processing and output table for Computer Creations
Rebecca Gracian Constraints For the system to be well used, the user must have IT skills in knowing how to control different types of commands made on spreadsheets. If the employee, who has to use the systems, has no IT skills, they need detailed training to make sure that they know fully well how to use the system. They need training on how to enter data using the macros. If the employee uses the wrong command button at the wrong time, the data entered is likely to get lost or deleted. Also, if the system is used in a different way from how it is instructed, there is a possible chance that the layout and the designs will completely change. Input, process and output of the new system WorksheetDescription of data requiredData source, collection and timing Method of data inputData processingData outputData sheet- where all the details of the products are stored:-Computers, laptops, hardware, software and the price of each product.- 8 computer brands with their prices- 5 laptop brands with their prices- 6 hardware with their prices- 4 software with their prices These data will be provided by the manager of the company. The information of all the products will be entered using the keyboard. The font used on the sheet is Arial with the font size 10 and the colour is grey. The font of the main title is Lucida Calligraphy. The macros on this page are:-- The computer logo which will take the user to the main menu- A command button ‘Quote’ which will take the user to the quote sheet- - A command button ‘Invoice’ which will take the user to the invoice sheet- A command button ‘Customers’ which will take the user to the Customer sheetThe background on this sheet will be an image, accessed from the company files, named ‘Harmless Smoke’. The computer details section will be from cell A5 to C12. The laptops section will be from E5 to G9. The hardware section will be from I5 to
K10. The software section will be from M5 to O8. The data on this sheet can only be changed once there is a change in the price of a product. This change can only made by the manager. On this sheet, when a macro is pressed the user will be taken to the desired sheet. Quote sheet- shows the cost of all the products and allows the user to print out the detailsThe information on this page will be from the data sheetAs before, the information will be given by the manager and accessed from the data sheetThis sheet shows ...
This is a preview of the whole essay
K10. The software section will be from M5 to O8. The data on this sheet can only be changed once there is a change in the price of a product. This change can only made by the manager. On this sheet, when a macro is pressed the user will be taken to the desired sheet. Quote sheet- shows the cost of all the products and allows the user to print out the detailsThe information on this page will be from the data sheetAs before, the information will be given by the manager and accessed from the data sheetThis sheet shows the price of the products, therefore the user has to use a mouse to choose an option from the drop down lists. The font used on the sheet is Arial with the font size 10 and the colour is grey. The font of the main title is Lucida Calligraphy.The computer drop down list is linked to the cell C6. The cell F6 has the formula =VLOOKUP(C6,Computers,3)- this will look up the price of the computer from the data sheet. The laptop drop down list is linked to the cell C8. The cell F8 has the formula =VLOOKUP(C8,Laptops,3)- this will look up the price of the laptop from the data sheet. The hardware drop down list is linked to the cell C10. The cell F10 has the formula =VLOOKUP(C10,Hardware,3)- this will look up the price of the hardware from the data sheet. The software drop down list is linked to the cell C12. The cell F12 has the formula =VLOOKUP(C12,Software,3)- this will look up the price of the software from the data sheet. When all the options have been chosen, the subtotal will appear in the cell F14. The user can choose whether the customer wants VAT or not, using a mouse, if the customer wants a VAT, the price will appear on the cell F16. The cell has the equation =IF(B16=TRUE,0.175*F14,0). The cell F18 will show the total price worked out by the formula =F14+F16The background on this sheet will be the colour ‘dark blue’.The macros on this page are:-- The computer logo which takes the user to the main menu- A command button ‘Data’ which will take the user to the data sheet- A command button ‘Invoice’ which will take the user to the invoice sheet- A command button ‘Customers’ which will take the user to the Customer sheet- A command button ‘View Quote’ which will open a userform, displaying all the details from the page, and will also allow the user to print out the quote. The data which can be changed on this sheet is the product’s prices. The user has to choose an option from the drop down list.Once an option is chosen from the drop down list, the price of the item will automatically appear. The macro ‘View Quote’, loads the userform, showing all the details on the page and on this userform, there will be a command button which once pressed will load the printing options. Invoice sheet- shows all the customer details with their desired products. Also shows the total price of all the products (with or without the VAT) Data needed are of the products:-- Computer with catalogue number and price- Laptop with catalogue number and price- Hardware with catalogue number and price- Software with catalogue number and priceAll the data on this sheet will come from the data sheetThis sheet shows the product details, the subtotal, total price and the customer details. The data will entered using a keyboard and a mouse. The date will be shown in the cell I5. It will automatically update itself as it has the following formula =NOW().The customer details section will be from cells I10 to I15. I10- customer invoice number; I11- customer name; I12- address 1; I13- address 2; I14- post code; I15- telephone number.The quantity values:- B6- computer; B7- laptop; B8- hardware; B7- softwareThe catalogue numbers:- C6- computers; C7- laptops; C8- hardware; C9- softwareFormulas for looking up the product in the data sheet using the catalogue numbers:-D6 (looking up the computer item)-=IF(C6="","",VLOOKUP(C6,Computers,2))D7 (looking up the laptop item)-=IF(C7="","",VLOOKUP(C7,Laptops,2))D8 (looking up the hardware item)-=IF(C8="","",VLOOKUP(C8,Hardware,2))D9 (looking up the software item)-=IF(C9="","",VLOOKUP(C9,Software,2))Formulas for looking up the unit price of the products:-E6 (looking up unit price of the computer)- =IF(C6="","",VLOOKUP(C6,Computers,3))E7 (looking up unit price of the laptop)- =IF(C7="","",VLOOKUP(C7,Laptops,3))E8 (looking up unit price of the hardware)- =IF(C8="","",VLOOKUP(C8,Hardware,3))E9 (looking up unit price of the software)- =IF(C9="","",VLOOKUP(C9,Software,3))Formula for working out the total price of the item times by the quantityF6- =IF(B6="","",E6*B6)- computersF7- =IF(B7="","",E7*B7)- laptopsF8- =IF(B8="","",E8*B8)- hardwareF9- =IF(B9="","",E9*B9)- softwareFormula for working out the subtotal-=SUM(F6:F9) Formula for working out the VAT=IF(E14=TRUE,0.175*F12,0)Formula for working out the total price spent by the customer=F12+F14Macros on this sheet:-- The computer logo which takes the user to the main menu- A command button ‘Data’ which will take the user to the data sheet- A command button ‘Quote’ which will take the user to the quote sheet- A command button ‘Customers’ which will take the user to the customers sheet- A command button ‘Clear’ which will allow the user to clear all the data entered on this sheet- A command button ‘Store Customer Details’ which stores all the customer details and the total price they have spent. - A command button ‘View Product details’ which will load a userform showing all the product details with their catalogue number and the price- A command button ‘Enter Customer Details’ which will allow the user to enter customer details- A command button ‘Enter Product Details’ which will allow the user to enter product detailsThere are three userforms on this sheet- one which shows the details of the products; one which allows the user to enter customer details using the keyboard and the last which allows the user to enter the product details- for this userform, a mouse has to be used as it has drop down listsThe data that can be changed in this sheet is the customer details, quantity and the product and the catalogue number. This can be done by the user, using the userforms available.The output is the details being stored onto the customer sheet. Also, the userforms loading and appearing on the screen once the macro button has been pressed.Customers sheet- shows the customer details with the products that they have purchased and the total money they have spentAll the information on this sheet, will be directly taken from the invoice sheet. These details include- invoice number of the customer- Name, address 1 and 2, telephone number and the post code of the customer- The total price that the customer has spentAll the information will be gained from the invoice sheet The font used on this sheet is Arial with font size 10. The font of the main title is Lucida Calligraphy.This sheet will store the details directly from the invoice sheet, therefore the following text should be entered:-- Invoice number in cell A4- this column (A) should store all the customer invoice numbers- Customer name in cell B4- - this column (B) should store all the customer’s name- Address 1 in cell C4 and Address 2 in cell D4 - these columns (C and D) should store all the customer’s address lines- Post Code in cell E4- this column (E) should store all the customer’s post code - Telephone number in cell F4- this column (F) should store all the customer’s telephone number- Total price in cell G4- this column (G) should store the total money spend by the customer. The macros on this page are:-- The computer logo which takes the user to the main menu- A command button ‘Data’ which will take the user to the data sheet- A command button ‘Quote’ which will take the user to the quote sheet- A command button ‘Invoice’ which will take the user to the invoice sheetThere is no changes that is needed to be made in this sheetThere is no output data on this sheet
