Run a basic inventory analysis for a fictional bicycle company (Velotics Inc.) using SAP to run inventory and form a Data Analysis to present for upper management (i.e. the company buyer and CEO). This analysis will convey fullly completed product inventory in the following order;
- Data Sources
- Tools
- Enterprise Explanation
- Data Procurement
- Data Reporting
- Data Visualization & Dashboard
- Presentation & Analysis
The primary data source is SAP S/4HANA Cloud Public Edition 14-Day Free Trial which is accessible via website link: https://www.sap.com/products/erp/s4hana/trial.html
- SAP S/4HANA Cloud (Public Edition) - Data Procurement
- Microsoft Excel - Data Conversion & Data cleaning
- SAP S/4HANA Cloud - Reporting
- Microsoft Excel - Reporting
- Tableau - Data Visualization & Dashboard
- Mac Keynote - Presentation
- Microsoft Excel - Final Presentaion & Analysis
The illustation above provides the enterprise concept as it exists in a fictional entity. Velotics Inc. utilizes a ERP (Enterprise Respurce Planning) system, which in this case uses SAP to organize the businesses functions. We will use this ERP to produce an inventory report and analysis to help the company buyer know what inventory levels are. Therfore, the inventory report will guide the buyer and other related upper managment to better strategize product inventory and or sales targets.
Velotics Inc. produces bikes in the follwowing lines/ ranges:
As pictured, Velotics produces racing bikes, mountain bikes, cruise bikes, youth bikes, e-bikes and an in home exercise bike. All of these bikes have corresponding model numbers and encomapss ranges in competitive bicycle market segmets
Disclaimer: For this inventory analysis, I will only cover "Fully produced product" as each product model contains numerous parts and componemts that they themselves have stock/ part #'s to categorize e.g. see the model part example as shown in the depiction below
To find, collect and consilidate all the KPI's needed for the inventory report. I first access SAP using my credentials to run a live inventory based on single material (i.e. finished product) stock. the following SAP hompage is shown :
Next, I navigate the homepage to Manufacturing & Supply Chain to access the correct fiori Launchpad. The navigation to run inventory
is as follows:
Manufacturing & Supply Chain > Warehouse Management > Stock *Sinlge Material*
Subsequently, I then query each finished product model by manually entering each one since the SAP s/4HANA free trial version does now allow you to download the inventory into a file to start the spreadsheet development. Normally I would download or run ERP runctions to obtain the finished product model's inventory in one single action, but for all intensive purposes I manually queried each into the fiori launchpad function (Single Stock Material).
Lastly, I ran a final stock search to proivde final details about each model's stock by the following navigation:
Manufacturing & Supply chain > Warehouse Management > Manage Stock
To continue on with the next phase of the inventory analysis, the development of a daily inventory report was created to distribute daily inventory report about completed product in stock for upper managment to assess.
This report feautures quick Microsoft Excel formulations to easliy report inventory. such as:
- Availible Unrestricted total:
=SUM(F36-G36-H36-J36)for the total sellable stock in inventory - Pending Stock total:
=SUM(G36+H36+J36)includes quality inspection, retunrs and blocked stock (uncertainty of stock) - Average Coverage of Days:
=AVERAGE(P6:P34)gives the total average rate of 30 day product consumption based on current movement on all items
Product model is replicated if there are multiple storage locations associated with current inventory. Therefore there are multiple of the same product models to further detail where exacly that inventory is at what stage of stock. For example:
MZ-FG-M525 (Mid-Range mountain bike) has 5 different stock locations: 227 peices are under QI, 3,835 are in std. storage 1 while 99 peices are stored in the warehouse. there are no peices that are blocked.
*this example inventory report can be executed in the SAP fiori lauchpad query by:
Manufacturing & Supply Chain > Warehouse Management > Stock *Sinlge Material* > Material > (type the model numer).
Lastly, this report also features what exacly needs to be orderd/considered given pruchasing material for finished product.
Such as formulating an if statement before each product model :
=IF(AND(F6<=0, E6="Std. storage 1 171A",P6>0), "Yes", "No")
The condition is as follows:
...f6<=0, ...Unrestricted-Use Stock is less than or equal to 0... E6="Std. storage 1 171A", ...only for stock in storage 171A is used...P6>0 ...the 30-Day supply coverage needs to be greater than 0. optimized for stocking moving product
therfore, after applying the formula parameters to determine which finished product needs order prioriry to balance product movement. Here, the result is model MZ-FG-26EMN (Mountain E-Bike) with 0 Unrestricted stock at a 3.57 consumption rate. Therefore, the finished product needs order raw material consideration in order to meet supply, which can affect sales and procurement
SAP does provide built-in interative dashboards and data visualization as part of an ERP system.
In this case, to view the SAP automated overall inventory dashboard, navigate by the following:
Manufacturing & Supply Chain > Warehouse Management > Overview Stock Management > Plant (Select "Plant 1 US (1710)" > Go
which produces the following interactive live dashboard
We can interact with areas on concern for Velotics such as Overdue Materials tile:
Which if we click on it; we can see in futher detail what the overdue material is to account for current inventory stock.
Here, we can see the only item that the ERP system is accounting for. 2 pruchase orders of a forklift with a window of a 7 day transit period. (which may be accounted for the plant 1 US 171's inventory count once the forklifts arrive)
Another data visualization that can be used for the inventory analysis would be to create pivot tables and or data visualizations within the daily FERT inventory report. That way when the report is sent to upper management, they can visually assess the inventory for the day and make key decisions to help with product movement.