Inventory Analysis

Project Title: MIS Food Inventory Analysis

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

About Dataset & Files

Dataset Source: Udemy

Introductory Key Points:

The Dataset is Contained with 7 tables. Those are pointed below:

➡️ 5 dimension tables:

Calendar (Primary Key Column Name: Transaction_Date)
Customer Lookup (Primary Key Column Name: customer_id)
Employee Lookup (Primary Key Column Name: staff_id)
Product Lookup (Primary Key Column Name: product_id)
Store Lookup (Primary Key Column Name: store_id)

➡️ 2 fact tables:

- - - - Food Inventory (this table has relationship with 3 dimension tables; Calendar, Product Lookup, and Store Lookup)
        Sales by Store (this table has relationship with all 5 dimension tables)
The report is Contained with 5 Dashboards.

Click Here to Download the Report File

Dashboard - 1: Customer Profiles in Sales

Visual Description:

There are 2 Card visuals in this dashboard which are briefly explained below:
- - 1. Total Customers: This card shows the total number of Customers reported in the dataset.
    2. Customer Average Orders: This card displays the average orders made by a single customer. The value of this visual is formed by dividing the total orders (reported in the dataset) by the total number of customers (reported in the dataset).
The Table visual, titled "Top 10 Customer Info with Highest Orders", shows the profile of top 10 customers who made the highest orders among all the customers reported in the dataset. The profile includes the IDs of the customer, their email IDs, their purchase card number, and total number of goods they ordered.
The Pie Chart, titled "% of Customer by Age Category", shows a comparative data of percentages between two age categories of customers, i.e., 50 years or less and more than 50 years. From this visual, it is observed that the survey younger customers (over 64%) are more reported in the dataset than older customers (under 36%).

The Scatter Chart, titled "Customer IDs by Revenue & Subscription Months", displays the points of customers IDs based on their subscription duration in months (vertical axis) & total revenues earned by them (Horizontal axis). From this visual, it is seen that most revenues were earned between $100 to $10,000 (especially around $1,000) by the customers, although subscription months were equally varied between 54 months to 81 months.
The Donut Chart, titled "% of Customer by Gender", shows a comparative data of percentages among the gender of the customers. From this visual, it is proven that most customers reported in the survey of the dataset are female (over 43%).

Dashboard - 2: Sales Analysis by Products & Stores

Visual Description:

The Treemap visual, titled "Top 5 Product Categories with Highest Cost", displays the comparison of top 5 products with highest amount of costs spent. It is clearly seen that the highest amount is spent on hot drinks, where coffee has the highest cost (over $400) and tea is the second highest (near $300).
The Pie Chart, titled "Number of product by TAX Inclusion, shows the comparison in amount between TAX included & TAX excluded product, where most products (72 out of 88) has inclusion of TAX.
There are 2 Slicer visuals in this dashboard. Both are user-defined, and some visuals on this dashboard will automatically be changed based on the selection of both slicers. Those are briefly introduced below:
- - 1. Select Product ID: This slicer contains all the IDs of listed products in the dataset. Report user should be able to select a product id.
    2. Select Store ID: This slicer contains the major 3 store IDs (Store-3, Store-5, and Store-8) reported in the dataset. Report user should be able to select one of those three store IDs.
On the basis of the filtering property of "Select Product ID" slicer, there are 2 card visuals which will automatically change. Those card visuals are introduced below:
- - 1. Product Name: This card shows the name of the product of the selected ID.
    2. Product Type: This card shows the product type of the selected ID.
Again, based on the selection of both "Select Product ID" and "Select Store ID" slicers, there are another 4 card visuals which will automatically change. Those card visuals are introduced below:
- - 1. Cost Spent: This card shows the amount of cost spent on that selected product in that selected store. (For example, when this visual shows $1.27K , it means that $1.27K was spent on Product ID no. 3 in store-5)
    2. Revenue Earned: This card shows the amount of revenue earned from that selected product in that selected store. (For example, when this visual shows $6.35K, it means that $6.35K revenue was earned from Product ID no. 3 in store-5)
    3. Profit Earned: This card shows the amount of profit made from the revenue of that selected product in that selected store. (For example, when this visual shows $5.08K, it means that $5.08K profit was made form Product ID no. 3 in store-5)
    4. Order Placed: This card shows the total amount of orders cost placed by the customers for that selected product in that selected store. (For example, when this visual shows 353, it means that total 353 orders were placed by the customers for Product ID no. 3 in store-5)
The Funnel Chart, titled "Number of Products by Groups", shows the total number of products in 5 different product groups reported in the dataset. From this chart, it is observed that more than half reported products are denoted from the "Beverages" product category (This visual is absolute & doesn't change by any slicer filter context).

There is a group of visuals in the footnote of the dashboard titled "Monthly Sale Comparison with Retail & Wholesale Price" . In this group, 4 KPI Cards are placed where the visuals of those cards change based on the selection of both "Select Product ID" and "Select Store ID" slicers. All those cards, whose trend axis changes by the Start of Months, are introduced below:
- - 1. Target Revenue by Retail Price: This KPI card shows whether the revenue of a particular product in a particular store (based on the selection of both slicers) fulfill the target retail revenue or not. Here the big number represent the normal store revenue earned for the product, and the value in "Target" represents the possible revenue if the product is sold in retail price. The percentage value in the bracket shows how far the earned revenue is far from the target revenue. When the color of the card is "Green", that means the target is filled, otherwise it will show "Red" color. For example, if this visual shows $342.00 with green color, it means that the revenue earned from that product in that store is equal or bigger than the target revenue.
    2. Target Revenue by Wholesale Price: This KPI card displays similar value like the previous "Target Revenue by Retail Price" KPI card. The only difference is, its' target revenue is set by the wholesale price of the product.
    3. Target Profit by Retail Price: This KPI card shows whether the profit earned from a product (based on the selection of both slicers) fill the target retail profit or not. Here the big number represent the profit from normal store, and the value in "Target" represents the approximate profit if the product is sold in retail price. The percentage value in the bracket shows how far the earned revenue is near the target revenue. When the color of the card is "Green", that means the target is met, otherwise it will show "Red" color. For example, if this visual shows $273.60 with green color, it means that the profit made from that product in that store is equal or bigger than the target profit.
    4. Target Profit by Wholesale Price: This KPI card displays similar value like the previous "Target Profit by Retail Price" KPI card, where the target profit is fixed by the wholesale price of the product.

Dashboard - 3: Food Inventory Products by Total Quantity Sold

Visual Description:

The Treemap visual, titled "Food Inventory Products by Total Quantity Sold", displays the comparison of all the listed food inventory products based on their sold quantity. From this visual, it is seen that there are total 11 inventory products listed in the report dataset. In this visual, each product is represented by different color, and their box sizes symbolize their sold amounts. Moreover, the highest number (over 19K) was sold for "Chocolate Croissant", where "Ginger biscotti" was sold in the lowest amount (11,007).

Dashboard - 4: Employee Profiles

Visual Description:

There are 4 Card visuals in this dashboard which are independent, static, and do not follow any filter context. Those cards are briefly described below:
- - 1. Total Staff: This card shows the total number of employees reported in the dataset.
    2. Total No. of Active Staff: This card shows number of employees among total employees who earned revenues for the company (Employees who earned zero dollar revenue are not listed in this number).
    3. Total Designation: This card shows the number of positions occupied by the employees in the company.
    4. Total Job Locations: This card shows the number of locations reported in the dataset where employees worked in.
The single Slicer of this dashboard, titled "Select Active Staff ID", contains all the IDs of listed employees in the dataset who earned more than $0 revenue for the company.
There are another 3 Card visuals in this dashboard whose values change based on the selection of the "Select Active Staff ID" slicer. Those are briefly described below:
- - 1. Full Name: This card displays the full name of the employee of the selected ID.
    2. Position: This card displays the job designation of the employee of the selected ID.
    3. Location ID: This card displays the ID of the location in which the the employee of the selected ID worked.
The Gauge Chart, titled "Yearly Profit Margin vs Target", shows how close the selected staff's yearly profit margin is to the target margin (74.5%). Here, all profit margins of the active employees are ranged between 73% to 76%.
There are 2 KPI cards in this dashboard whose value alters based on the filtering property of the "Select Active Staff ID" slicer. Those KPI visuals are briefly described below:
- - 1. Yearly Revenue Target: This KPI card shows how close the employees' average yearly earned revenue is close to the target revenue. The big number represents the average yearly revenue earned by the employee, and the value in "Target" represents the average yearly revenue of the all active employees. The percentage value in the bracket shows how far the earned revenue is far from the target revenue. When the color of the card is "Green", that means the target is filled, otherwise it will show "Red" color. For example, if this visual shows $11.42 with red color, it means that the employees average yearly earned revenue is smaller than the target.
    2. Yearly Profit Target: This KPI card is similar to "Yearly Revenue Target" KPI card, only difference is the valuation is done for "Profit" instead of "Revenue".

The Waterfall Chart, titled "Average Yearly Completed Orders by Employees", displays a waterfall scenario visual based on the values of the average Yearly Orders achieved by active Employees. From this chart, it can be observed that the highest average yearly completed order is acquired by Employee ID no. 12 (211 orders), where the lowest average is done by Employee ID no. 3 (only 3 orders per year).
The Treemap visual, titled "Number of Staff by Job Experience", displays the comparison of the number of employees with their respective Job Experience. In this visual, it is seen that highest number of employees fall under the work experience of 11-19 Years, where number of experience employees (20 or More years of experience) are the lowest.
The Pie Chart, titled "Number of Staff by Revenue Earned for Company", displays a comparative study of the number of employees based on their earned revenues for the company. This visual shows that most employees (30) were not able to earn any revenue ($0 revenue), where 17 employees brought more than $0.1M revenue for the company.

Dashboard - 5: Sales Analysis by Date

Visual Description:

A single Slicer visual, titled "Select the Range of Date", holds all the dates reported in the dataset. Report user should be able to select a range of dates from this slicer. Based on the date range selected by user, all the other visuals of this dashboard will change accordingly.
The Aria Chart, titled "15-days Rolling Profit", shows the calculated rolling profit for every 15 days plotted against the transaction date.
The Stacked Aria Chart, titled "Orders in Different Weekdays", shows the total amount of orders placed in different weekdays for all transaction dates. Report user should be able to see different weekdays order while placing the cursor on the visual as tooltips.
The Line & Clustered Column Chart, titled "Total Profit vs Moving Monthly Average Profit", shows both the total profit and the calculated moving average profit (monthly basis) together against the transaction date. In this visual, the Clustered Column Chart represents the total earned profit, while the line chart represents the moving monthly average profit.

A Table visual, titled "(Year-Quarter-Month)-to-Date Revenue Profile", shows the normal totals & cumulative totals (based on years, quarters, and months) of the earned revenue. There are total 5 columns in this table:
- - 1. Start of Week: This column holds all the dates that represent the start of the week in the "Calendar" table of the dataset.
    2. Total Revenue: This column displays the total amount of weekly revenue earned, and the values are placed for every start of the week.
    3. YTD Revenue: This column shows the cumulative total Year-to-Date revenue for every start of the week, where the cumulative total ends in the completion of every year.
    4. QTD Revenue: This column shows the cumulative total Quarter-to-Date revenue for every start of the week, where the cumulative total ends in the completion of every quarter.
    5. MTD Revenue: This column shows the cumulative total Month-to-Date revenue for every start of the week, where the cumulative total ends in the completion of every month.

Page updated

Google Sites

Report abuse