Invoicing_ Ordering and Stock System

Document Sample
Invoicing_ Ordering and Stock System Powered By Docstoc
					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
    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-NamesDefine menu and find out what names
       have been defined in this system. What is the purpose in defining names on a
    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-ToolbarsControl 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
   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.