Data Table Analysis

Kudler Fine Foods Data Table Analysis

ACC 542

Kudler Fine Foods Data Table Analysis

TO: Kudler Fine Foods Management

FROM:

CC:

DATE:

SUBJECT: Data Table Analysis

Congratulations on the success of Kudler Fine Foods. Accounting Firm A is preparing this brief to evaluate the design of Kudler Fine Foods’ existing data tables, make recommendations for improvements to the existing data tables, and provide information on how pivot tables can be used to help management improve decision making for inventory management. These recommendations will add value to the accounting operations, inventory management and help with the continued success of Kudler Fine Foods. An entity relationship diagram is attached to illustrate the existing relationships between data tables. A pivot table is also attached to provide an example for how it can be used to make decision inventory purchases.

Data Tables Design Elements Evaluation

The design of the inventory table is essential to inventory management. In order to get the most flexibility out of a database, the data needs to be organized into tables so that redundancies do not occur. Kudler Fine Foods existing inventory tables is set-up to organize data by store, item, department, unit cost, and quantity on hand. The general ledger inventory data report is created based on the data from the inventory table. The general ledger code is distinct and assigned based on the store location, department and inventory item. The value amount of inventory on hand is determined by the quantity on hand and unit cost for the item. End users of the general ledger inventory report are able to quickly identify the products by store location and department from the general ledger code. The first two numbers of the general ledger code represent the store location, the third number represents the department, and the last two numbers represent the inventory item, while the third number indicates the department the inventory belongs to, and the last two numbers indicate the product. End users use the general ledger inventory data report to determine the amount of inventory to be ordered for each store location.

One critical aspect of inventory management is having a database that helps keep track of inventory movement, inventory item details, purchase orders, identify inventory trends, and replenishment needs. Unfortunately, Kudler Fine Foods existing database does not provide this functionality. Improvements in the existing database tables will streamline the inventory management process.

Entity Relationship (E-R) Diagram

According to Bagranoff, Simkin, and Strand Norman (2008), a database should contain a table for each entity which are often related to either directly or indirectly (“Chapter 13, Identify Relationships among Entities”). An entity-relationship (E-R) diagram documents the hierarchy of database tables and relationships between database tables. Many E-R diagrams techniques consists of four symbols: rectangles to represent entities, diamonds to describe relationship, ovals describe the entity, characteristics, and connecting lines depicts relationships (Bagranoff, Simkin, & Strand Norman, “Chapter 13, Create Entity-Relationship Diagrams,” 2008). Other diagrams include cardinalities for a business process. In Microsoft Access, the E-R diagram uses rectangles to represent the entities. A dashed line from one rectangle to another indicates the relationship. The cardinalities are noted by the primary key, number 1 or the infinity symbol. For example, a one-to-many relationship is depicted in the attached E-R diagram between the Item and Inventory entities. The primary key for side of the one to many relation is denoted by a number 1 and the foreign key side is denoted by an infinity symbol.

Pivot Table

As discussed above, end users should be able to use the general ledger inventory report to determine how much inventory is on hand for each store location to make inventory management decisions. Unfortunately, the current report that is generated is not very user friendly. Kudler Fine Foods management team could make great use of an Excel Pivot Table. A Pivot Table allows user to view a set of data in different ways. In addition, Pivot Tables allow users to change views by filtering on the drop down menus located at the top of each column. The end user can select a specific set of criteria to focus on. For example, the attached Pivot Table can be used to determine the highest or lowest value amount of inventory on hand at each store location for specific items or it can be used to view Inventory on hand by store location and department. Kudler can improve their decision making and inventory management through Pivot Tables.

Conclusion

Kudler Fine Foods inventory is primarily perishable goods. It is critical for the inventory to be track properly. In the long term, improvements in the database tables will allow management it effectively manage inventory. In the short term, the use of a pivot table for the inventory management and reporting, will allow management to gain control over inventory replenishment by keeping enough product in stores to meet customer needs and minimize excess inventory.

Attachments: Pivot Table, E-R Diagram

References

Bagranoff, N. A., Simkin, M. G., & Strand Norman, C. (2008). Core Concepts of Accounting Information Systems (10th ed.). Retrieved from The University of Phoenix eBook Collection database.

Kudler Fine Foods website. (2013). Retrieved from University of Phoenix Virtual Business Portal.