Cash and Stock Control - Assignment Example

Cash and Stock Control A. Concept design The Cash_Stock workbook is composed of worksheets named as: Main Page, Product Table, Order Form, Goods In, Deliveries, and Sales Invoice. The worksheets can be accessed through the labeled tabs at the bottom of the worksheet and the navigational buttons (activated by left click of mouse) on the worksheet itself. Table 1 gives an overview of the different worksheets and buttons. Table 1. Brief descriptions of the worksheets and buttons. Worksheet Worksheet description Buttons Button description Main Page Company name Product Table Go to “Product Table” sheet Deliveries Go to “Deliveries” sheet Goods In Go to “Goods In” sheet Order Form Go to “Order Form” sheet Sales Invoice Go to “Sales Invoice” sheet Product Table Contains list of Main Page Return to Main Page Products sold by the Company Add New Product Enter new product description (ID), stock, prices, need to re-order, etc. Sort Table by ID Sorts the list of products in ascending order based on the “Product ID” which is a text Sort Table by Type Sorts the list of products alphabetically based on the “Description” Re-Order List Filters the products whose “Stock” levels are below the “Re-order Level” Restore Table Removes the filter and restores the re-order short list back to the original product table Print Print preview of sheet Order Form Table of up to 5 Main Page Return to Main Page item ordered by company from New Order Data entry form for entering orders suppliers Print Print preview of sheet Goods In Table of products Main Page Return to Main Page from suppliers. Product descriptions taken New Item Data entry form for entering New items taken in from suppliers Products table using VLOOKUP function Update Stock Update the stock levels of products procured in the “Product Table” Print Print preview of sheet Deliveries Data base of Main Page Return to Main Page Customers containing their names and addresses Add Customer Data form for entering customer data Sales invoice Table of up to 5 Main Page Return to Main Page products sold to a customer (using VLOOKUP function) Add item Data form for entering products sold (Product ID and Quantity), other data are given by the VLOOKUP function Update Stock Update the stock levels of products procured in the “Product Table” Print Print preview of sheet B. Print outs of various screens and tables 1. Main Page 2. Product Table 2.a. Sort by Product ID 2.b. Sort by Product Type 2.c. Re-Order List 2.d. Print preview of Product Table 2.e. Add new product 3. Order form 4. Goods In 5. Deliveries 6. Sales Invoice 6.a. Sales Invoice Screen before Updating Sock 6. b. Product Table before Updating Stock The second row of data (product ID 0002) is one of the stocks affected by the update from sales invoice table. Before the update its stock is at 23, 2 below the re-order level of 25, re-order stock is 2, invoice is 1, and Re-order value is £190. 6. c. Product Table after Update After the update, the stock volume is reduced by 1, re-order stock is 3 and its re-order value increased to £ 285. C. User Manual You should have at least MS Excel 2003 installed in your computer with the security level reduced to low level, or else the macros are disabled and cannot operate at higher security levels. You should also have an antivirus program installed in your computer. To reduce the security level, click Tools | Macro | Security | Set Security Level to Low. To use the Cash_stock.xls: 1. File| Open “Cash_stock.xls” 2. You can navigate through the different worksheets by: a. Clicking the left button of the mouse on the action button on the left hand column of the Main Page sheet b. Clicking on the tabs found at the bottom of the worksheet 3. Go directly to the sheet you wish to work on a. Product Table – to enter new products and their prices To use “Re-order list” be sure that a cell inside table is highlighted before clicking on the “Re-Order List” button b. Order Form – to order products from suppliers c. Goods In – to process new products procured and update product table d. Deliveries – to update customer data base e. Sales invoice – to process products sold and update product table 4. At any time you may want to print preview or print hardcopy from File|Print menu D. Definition of any special features included in the program A print button is included in all sheets so you can preview or have hard copy of the transaction made. A VLookup function (vertical look-up function) is used on the product table to help “automate” the entry of product type and descriptions in the other sheets, so the user may not need to re-enter these information. Data can be entered directly on the spreadsheet or by using the data form entry button provided. Data | Filter | AutoFilter was used in the “Re-order List” to include only the products that need to be re-ordered, whose stocks fall below the re-order levels after the sales invoice updates. B. Test Plan – sample data and test evidence To test the program, set the re-order level a little bit higher to increase the chance of the need to re-order. The following can be tested using artificial data: to transfer new product data from the input screen to the product table – done through the “add new product” button, a product table form pops up, when this button is clicked to sort the product table by product type and then possibly by description – two sort buttons were designed to sort by product ID and product type to define the total value of products in stock – done by the sum function in excel, on the stock value column to produce a list of all stock requiring reorder and define the order value – done by the “re-order list” button which uses the data filter function of excel to process a sale – the “add item” button opens the form for entering data into the sales invoice; “update stock” updates the product table after the sale. The sample test table was modified for this program. Worksheet Test Condition Expected Outcome Program Outcome Is Outcome Correct Y/N Main Page Product Table- Button Open Product Table Open Product Table Yes Sales Invoice – Button Open Sales Invoice Open Sales Invoice Yes Goods In – Button Open Goods In Open Goods In Yes Deliveries – Button Open Deliveries Open Deliveries Yes Order Form – Button Open Order Form Open Order Form Yes Product Table Main Page- Button Opens Main Page Opens Main Page Yes Add New Product – Button Product Table Form Should Open Opens Product Table Form Yes Sort by ID – Button Product ID 0010 should go after Product ID 0009 Product ID 0010 goes after Product ID 0009 Yes Sort by Type – Button Product types arranged alphabetically Product types arranged alphabetically Yes Re-Order List – Button Only products that require re-ordering are listed Only products that require re-ordering are listed Yes Re-Store Table – Button After re-order listing, table is re-stored to its original order After re-order listing, table is re-stored to its original order Yes R.R.P Formula Product ID 0001 Product ID 0002 Product ID 0001 = (£150.00*50)/100+ £150.00= £225.00 Product ID 0002 = (£95.00*50)/100+ £95.00 = 142.50 Product ID 0001 = £225.00 Product ID 0002 =142.50 Yes Price Rise Formula Current Price Rise = 50% Decrease By 25% 0001 = 187.50 0002 = 118.75 0003 = 312.50 0001 = 187.50 0002 = 118.75 0003 = 312.50 Yes Stock Value Product ID 0001 Product ID 0002 Product ID 0001 = £150.00*20 = £3000.00 Product ID 0002 = £95.00*20 = £1900.00 Product ID 0001 = £3000.00 Product ID 0002 = £1900.00 Yes Total Stock Value £3000.00 + £1900.00 + £5000.00 + £3000.00 + £900.00 + £1900.00 + £2200.00+£900.00+£1100.00 = £19900.00 £19900.00 Yes Goods In Main Page- Button Open Main Page Open Main Page Yes Update Stock – Button Stock Default = 20 Add 5 to Product ID 1 Add 5 to Product ID 2 Add 5 to Product ID 3 Product ID 1 = 25 Product ID 2 = 25 Product ID 3 = 25 Product ID 1 = 25 Product ID 2 = 25 Product ID 3 = 25 Yes Print - Button Goes To Print Preview Goes To Print Preview Yes Deliveries Add Customer – Button Customer Form Should Open Opens Customer Form Yes Main Page - Button Opens Main Page Opens Main Page Yes Sales Invoice Main Page – Button Open Main Page Open Main Page Yes Update Stock – Button Stock Default = 20 Minus 5 to Product ID 1 Minus 5 to Product ID 2 Minus 5 to Product ID 3 Product ID 1 = 20 Product ID 2 = 20 Product ID 3 = 20 Product ID 1 = 20 Product ID 2 = 20 Product ID 3 = 20 Yes Print - Button Goes To Print Preview Goes To Print Preview Yes Customer ID – VLOOKUP D1 Customer ID D1 details should be shown Customer ID D1 details are shown Yes Customer ID – VLOOKUP D2 Customer ID D2 details should be shown Customer ID D2 details are shown Yes Order Form Main Page – Button Open Main Page Open Main Page Yes New Order - Button Open Order Form Open Order Form Yes Read More