Invoicing, Ordering and Stock System Gandalph Office Supplies is a company which sells office supplies to small businesses. It only deals with organisations registered on its database. The system has been set up with very small databases just so that you can investigate what it does. The system automates certain aspects of the business, It is not a complete automation system. You are asked to explore what it does, find out how it works, and make suggestions for its development. Exercise 1 Finding out what it does 1. Copy the Excel Workbook called Gandalph_system2 into your user area 2. Before the system will work you must create two folders in the same folder that you put Gandalph_system2, one called “orders” one called “invoices”. 3. Open the “Gandalph_system2” file. From the front menu To make up in invoice you have to imagine a customer calling in and asking for some supplies. You have to fill in the boxes shaded pale blue to complete the invoice. Enter an imaginary invoice for one of the customers. 4. Press the button to process the order. Keep a copy of the print out. You will find that a copy of the order has been stored in the orders folder. What is its filename? 5. Go back to the menu (Tab at bottom of window)and choose to monitor the stock. You will see a stock list. Look at it carefully to see which items need re-ordering. Click on the Create Orders button. It should print off orders to the different suppliers and also keep copies of the files in the invoices folder. Keep the printed orders and find out the file name of the files which have been saved. Exercise 2 Finding out how it works. 6. Validation tables. On the Invoice sheet you are only allowed to enter specific data into the cells. What is the purpose of this? By clicking on one of the validated cells and going to Data-Validation on the Excel menu find out how it works. What sort of validation is applied and where is the list stored? 7. Naming areas. Go to the Insert-NamesDefine menu and find out what names have been defined in this system. What is the purpose in defining names on a worksheet? 8. Lookup functions. When you enter the code number of a customer in cell E7 of the invoice the details of the customer are automatically filled in. Look at the formula and explain what each part of the formula does. 9. If statements. Look at the formula in the H15. It is quite complex. Can you work out what it does. Paste a copy of the formula into your answer and explain the logic of the command. 10. Trapping Errors. Look at the formula in Invoice J15. This detects an error and tells the system what to do if there is an error. Change the formula and see why this has been done. 11. Extending the system. Add three new customers, and new suppliers and new catalogue items. You should be able to change the pointers in the Lookup functions so that they can be included in the system. You will need to change the definition of the names in the Insert – Define Names Menu. NB. if you add more catalogue items you will also need to add items to the stock lists. You will have to change the validation tables as well. Validation tables have to be on the same sheet as the ranges to which they apply so you will have to set up pointers over a greater range of cells, or copy new cells in. Exercise 3 Exploring Programming 12. Active-X Controls. Go to the menus and investigate how the controls on the menu work. Go to View-ToolbarsControl Toolbox and add the toolbox. Click the edit button (top left). Then by pressing the right button on the menu buttons you can explore their formats and properties. Change their colour, size etc. Find out what code is activated when you click on them. List the code in your answers and explain what it does. 13. Form Control Buttons The buttons on the top of the Invoice sheets are\not activeX controls they are Form controls which are inserted from the Form Menu. They refer to sections of code. Go to the Invoice sheet and right click on the ClearInvoice button. Explore the properties of this button. 14. Recorded code. The code in the macro for “ClearInvoice” is all recorded automatically. From the button properties go to Assign Macro and then edit the code. Can you see what the code does? 15. Handwritten code. Look at the code for the Process Invoice button. The first part of this is recorded code which selects the invoice, prints it out, opens a new workbook, copies the selection into the new sheet and then saves it under a suitable unique filename (see your answer to Exercise 1 Question 4). Can you work out how the filename is set? 16. At the end of the Process Invoice Macro, it calls another macro called “Update Stock”. This is code which could not be written automatically. It has a number of help commands in it… can you see what it does? Can you follow the help comments (in green) well enough to see how the code works. You will probably need a teacher’s help to see how it works. 17. Write a flow diagram to show how Update Stock works. 18. Write a new feature to the system. When an invoice is processed the despatch department needs to know what is to be sent off and to whom. Can you record a macro that copies the relevant information to a new worksheet and prints it off for the despatch department? Attach your code to a Form Control button and test to see that it works. 19. Have a look at the code which operates this component of the system. There are comment lines in green to help you understand what it does but you will certainly need help in understanding the way in which the offsets step through the different lists. Copy the code into your answer and write you own comments about how it works. 20. Write a flow diagram to show the operation of the Update Stock section. Exercise 5 The Journal for the Gandalph system does not work at all. A journal is a complete list of all transactions that go through the system. So every invoice and every order would need to be recorded in the journal. Can you add code so that the journal is updated each time and order is created or an invoice is processed? Designing an MIS for this business Consider what this very incomplete system does for the business. Think about what happens whenever a customer rings in with a request. The person taking the order will type the requests into the invoice. This creates an invoice. The invoice is printed and a copy kept in the files. You have written an extension so that a despatch order goes to the despatch department. Think about the needs of the finance department. How does the information about orders get to the finance department? The invoice system should write a line to the company ledger. How does the company check that the invoices have been paid? The invoice should not be sent until the goods have been despatched. How would partly completed orders be dealt with? How does the finance department get to know about orders and when to pay the bills? Write down your ideas ( as a system diagram) showing how this spreadsheet could be developed into a more complete order processing system.