Analysis of the Database and Documented Internal Control - Report Example

REPORT FOR THE SENIOR AUDIT PARTNER Results of the analysis of the database and documented internal control The analysis of the database shows that the process related to purchase lacks a systematic structure. Stock order and purchasing are the two main processes involved in the department’s day to day functioning. A strong rapport between the two processes is required to ensure smooth functioning of the department. The activities involved in the retail store is shown in the diagram: Stock level below minimum taking the list of items to be ordered Forwarding to the purchase department preparing the order The current database contains the list of stocks at the beginning. It also reflects the regular outflow of the stocks. The list of stock left is not shown by default on the database. At the time of placing a new order, the storekeeper needs to take the list of the stocks the quantities of which have fallen below the minimum limits. It also becomes necessary for the storekeeper to make a continuous examination of the stock for serving the purpose. The stocks which are depleting in quantity are not indicated on the database. The stock order is prepared by the store management and sent to the purchase department. The stock order is returned when it contains an order for stocks that are not listed in the stock catalogue. Only when the order reaches the purchasing department it is known whether the stock is listed or non listed. There is no provision for examination of stock catalogue in the stores department. This consumes time and also may create chaos in the process. The activities of the purchasing department is represented in the following diagram: Receipt of stock order retail store Whether listed in stock order? (no) (yes) (approved) Whether the stock is from preferred supplier?(no) purchase manager (no approval) (yes) (for approval) Purchasing staff financial controller (no approval) (for approval) (approved) To suppliers Duplicity occurs in the process at the third stage in the purchasing department. The process evolves into a very complicated framework when the order differs in terms of preferred and non-preferred suppliers. If the stock is to be ordered from a preferred supplier, the order is made by the purchasing staff on the approval of the purchase manager. If the stock is from a non-preferred supplier the order is made by the purchase manager on the approval of the financial controller. This is in fact a duplication of the work since the same job is being done at two levels. Recommendations for the improvement of Database and internal controls In Dick Flicks Electronics, Stock Order Process involves Store Management, Purchasing Department and Financial Department. The entire coordination between these departments is a critical factor in order to achieve the goals and objectives of Dick Flick’s Electronics. The entire department needs to be aware of the process done by each department. Therefore, the firm should use a centralized database to share necessary data. The following data flow diagram gives the total dedication and will bring out perfection among all the departments. (Software Design Centre. HOW TO DRAW DATA FLOW DIAGRAMS). Process at Store Management: Updating and keeping current all the necessary fields in the Inventory table. Checking the stock of quantity and replenish the stock of items which are falling below the minimum level. While preparing a Stock Order Request form, they also need to check the Stock Catalogue Table to see whether that particular item is available in a retail store. This will help to avoid returning of Stock Order Request form from purchasing department. Process at Purchasing Department: Stock Catalogue should be kept updated and apart from making necessary changes authorised by the Purchasing Manager. They keep registered/recognized suppliers file updated. Whenever a new Stock Order Request form arrives, purchasing staff should verify the recognised suppliers’ file to identify the supplier for those particular item/items. Purchasing order can be made only to a registered supplier. Purchasing department is giving preference to a preferred supplier and purchasing staff makes a purchase order by the approval of Purchasing Manager. In the absence of preferred supplier, the order is prepared by Purchasing Manager and approval from Financial Controller is obtained. In the absence of approval, Stock Order Request will be returned to Store Management. This process helps in reducing the chances of fraud and apart from facilitating proper negotiation with suppliers. The printed purchasing order will be either mailed or faxed to the supplier. Process at Financial Department: The Financial Controller verifies the purchasing order request from Purchasing Manager to purchase the stores from a non-preferred supplier. If the request is inconformity to the companies rules and regulations, he approves it. Otherwise, he will return back to Purchasing Department. Financial department makes payment to all certified invoices as necessary. The SQL Codes used to Develop Query are: The tables contained in this database are Inventory table, StockCatalogue table, RegSupplier table, Preferred Supplier table, Items table. The fields included in these tables are: Inventory (item_id, item_name, quantity, min_qty, order_qty), Stock Catalogue (item_id, item_name) Reg Supplier (supplier_id, supplier_name) Preferred Supplier (pref_id, supplier_id) Items (supplier_id, item_id, price). (Gangolly, 2000). Store Management Query: SELECT item_id, item_name, quantity, order_qty FROM Inventory WHERE quantity = min_qty; The above query will select all the details of items to be replenished such as name of the item, current stock level, and order quantity. SELECT * FROM StockCatalogue WHERE item_id = ‘ ‘; This query results whether the item is listed in the stock catalogue. Purchasing Department Query: SELECT supplier_id, price FROM Items WHERE item_id = ‘ ‘; This will result the list of all the registered suppliers who are supplying the particular item. SELECT pref_id FROM Preferred Supplier WHERE supplier_id = ‘ ‘; Compare the price of items among preferred suppliers and make a purchase order by the approval of purchase manager. If there is no result, compare the price of items among all the resulted ‘supplier_id’ and prepare a purchasing order and forward to Financial Controller for the approval. ((ebook pdf) Teach Yourself SQL in 21 Days.pdf. Second Edition. (2005-2007)). Works Cited Software Design Center. HOW TO DRAW DATA FLOW DIAGRAMS. SmartDraw. The World’s Most Popular Business Graphics Software. Retrieved 1 September 2007 from http://www.smartdraw.com/tutorials/software/dfd/tutorial_01.htm Gangolly, Jagdish. (2000). A Toy Sales Order Entry & Processing System (Example). Retrieved 1 September 2007 from http://www.albany.edu/acc/courses/acc681.fall00/681book/node32.html#SECTION00450000000000000000 (ebook pdf) Teach Yourself SQL in 21 Days.pdf. Second Edition. (2005-2007). Macmillan Computer Publishing. 4 Shared. Free File Sharing. Retrieved 1 September 2007 from http://www.4shared.com/file/3334362/ee44c260/_ebook_pdf__teach_yourself_sql_in_21_days.html?s=1 Read More