Data Analysis of a Small Shop's ERP System using MS Access and Excel

Past papers and solved assignments for students. This project uses MS Access and Excel for data analysis.

Introduction
In this part of assignment data, the warehouse is being generated using MS Access. MS
Access is proprietary software which is being used for creating a database. The database is
being based on the ERP system of a small shop which contains productid, an order summary
table which will show all orders which have been placed till now. Different tables are made
to fulfill the requirements of the ERP system of a small shop. After creating a database, pivot
tables are being made using MS Access and then the pivot table made is being imported to
excel file to generate a pivot chart and dashboard.

Different Decision-Making Cases and Their Screenshot:
Figure 1 Design View of Database
In the above figure design view of the database is being displayed in which different tables and their
relations with each other are being displayed. In the above image, prodCategories table has two
attributes CategoryID and category in which three categories is being stored at a different category.
prodProduts table stores information about different products which is being available for sale.
Order Detail table will have information regarding order which is being made by the customers.
Customer table will have information about the customer like its first name, city, state and zip code
etc. Order Summary will give information of order summary which is being made by any customer.
First decision-making pivot table:
Figure 2 First Pivot table
In this table, the Sum of OrderQuantity which is being placed by different customers with different
categories of products is being displayed above. Row labels will store the value of different

customers which will order different category products like spices, tea and Coffee is being displayed
in the column of the table. A grand total of every row and column is being displayed in the right
most corner and bottom corner of the table.
Pivot Chart for the first Pivot table
Figure 3 Pivot chart For the first Pivot Table
In the above figure bar chart is being plotted using pivot table which is being generated with the
help of the database. The above chart will use for visualizing sales of different categories of products
according to various customers of the shop. Customer First name is being displayed in the x-axis and
the sum of order quantity is being displayed on the y-axis of the graph.
Second Pivot table

Figure 4 Second Pivot table
The image shown above is the pivot table of second decision making. In this pivot table data of
various categories of products with its subcategories are being displayed in rows. The column of the
table holds the value of different cities which were mentioned in the database. The total sum of
Order quantity is being displayed according to the decision. A grand total of every row and column is
being displayed at the last row or column accordingly.
Pivot Chart for Second Pivot Table:
Figure 5 Second Pivot Chart
In this image the bar graph is being plotted for various products according to its categories and
subcategories is being plotted against various cities where customers belong to. Various colors are
being used to differentiate between each city and subcategories which are displayed in the x-axis is
the different dishes which is being made by three categories of products. From the above graph, De
caf is being the most sold item among all the products made by either spice, tea or coffee.

Third Pivot Table
Figure 6 Third Pivot Table
In this image, the pivot table of various order cost per unit according to various category and
different states is being displayed. Column shown above will display the different states in which
there were different order cost per unit according to different categories. From the above table, CO
and NY state have the lowest order cost per unit for Coffee. In the case of spices, VT state is the
lowest among all state.
Pivot Chart for third Pivot Table
Figure 7 Pivot Chart for third Pivot Table
In the above image bar graph is being plotted for Order cost price per unit. Three categories of the
product are being displayed on the Y-axis of the bar with respect to the various state. Different
states are being displayed in various colors. From the above graph, various results can be made like
the minimum cost price of the product among the various state.

Conclusion
In this File, various pivot table and their pivot chart are being attached. Pivot table and chart which is
being attached used for decision making in the ERP system. The database which is being used is of
small café shop which sells its product to various cities and states. The database is being created and
the table is being created in MS Access and analysis part like a pivot table and pivot chart is being
generated in MS Excel.