Hi, I'm Alistair!
I Help You Understand Data
If you’re managing a business, you have lots of day to day data. I help you understand what the data tells us about your operation in a fun, visual format.
See some of the projects I've worked on:
No Cleaning or Presentations. Only Queries & Visualizations.
Inventory Database Analysis
NB: All data and insights are purely hypothetical, the data is fake, there are no mentions of any Comapny or Organization, any similarities to any Organization are purely coincidental.
This project focused on looking at Inventory Optimization in a logistics (FMCG) setting.
To understand our process, let’s quickly walk through it step by step:
- Sit with the client to understand the project and their expectations, get all the questions they want answered and in what format.
- Create a plan to gather all the required tools and information, creating a project dashboard, schedules and sharing all information with relevant parties.
- Gather only relevant data in different formats (CSV, Streams, XML, API, Databases, etc) – Update shared project file.
- Isolate and scan data files for security threats using relevant tools – update shared project file.
- Clean Data (this step we left out in this project, as an example only) – update shared project file.
- Store Data into local and cloud based repositories/Databases to query later – update shared project file.
- Make a Copy of data, clearly labelling each version and documenting each step – update shared project file.
- Write queries in the relevant language, ie Python, SQL or simple Excel commands. Document the queries – update shared project file.
- Create visualizations from results of queries – update shared project file.
- Compile all data insights and visualizations into the required presentation format (Slide Presentation, Video or step by step walkthrough) – update shared project file.
- Deliver presentation to client, getting feedback.
Go through data to find any other recommendations to make outside of their requests, offering more value for money.
Insights Requested From This Data
- Whats the current stock level across categories?
- Whats the proportion of stock?
- What are the 5 fastest movers?
- What are the current minimum stock levels?
- Who are our main suppliers?
- What are the monthly demand/Sales numbers?
I used ChatGPT to help me formulate some data table headers.
I then used Mockaroo to create the fake data and downloaded it in CSV format.
I scanned the downloaded .csv file with my local Spybot softwre to check for any threats, there were none.
The data was saved into my local SQlite database.
I then used ChatGPT to write code and inserted the resulting code into my Jupyter Notebook, running on Annaconda.
The resulting code can be found below, in the embedded PDF.
I also used ChatGPT to write a brief Report about the Analysis, the Report is below.
Let's Help You Understand Data
01.
Understand
You explain your business so we understand what you want to now
02.
Plan
We create a plan to collect the necessary information to do the analysis.
03.
Investigate
We investigate and uncover the sources of data that will be needed to do the analysis
04.
Collect Data
Data is collected from various sources and in multiple formats as necessary
05.
Scanned
Collected data is scanned for viruses and other threats before being moved
06.
Clean, Format
Data is cleaned and refotmatted so that it can be safely stored in a database
07.
Query
We use multiple tools from SQL to excel to query the data and uncover answers
08.
Presentation
A complete presentation is delivered answering questions
Inventory Database Analysis
Introduction
NB: All data and insights are purely hypothetical, the data is fake, there are no mentions of any Comapny or Organization, any similarities to any Organization are purely coincidental.
This report provides a comprehensive description of the queries and analysis performed on the Inventory database.
The database contains records of inventory items, their quantities, suppliers, and other relevant details necessary for understanding inventory levels, movements, and insights to assist management within the business.
The focus of this report is to explain the methods and approaches we applied to analyze the inventory database.
Database Structure Overview
The Inventory database is structured to maintain comprehensive records of inventory items.
The main table, named ‘Inventory’, contains the following columns:
– Item_ID: A unique identifier for each inventory item.
– Item_Name: The descriptive name of the item.
– Supplier: The source or supplier of the item.
– Quantity: The current stock level for the item.
– Category: The classification of the item (e.g., electronics, consumables, raw materials).
– Reorder_Level: The minimum quantity threshold for reordering.
– Last_Updated: The date and time when the record was last updated.
Understanding the database structure is critical to designing meaningful queries and visualizations that support inventory management and decision-making processes.
Types of Queries Performed
To analyze the inventory database, several types of queries were run.
These include:
- **Total Inventory per Category:** Aggregating quantities to understand overall stock levels by category, which helps identify areas of overstock or potential shortages.
- **Supplier Analysis:** Grouping items by supplier to track inventory distribution and evaluate supplier performance in terms of timely delivery and stock availability.
- **Stock Movement Over Time:** Examining the change in inventory levels over time, allowing trends in consumption, replenishment, and seasonal fluctuations to be identified.
- **Reorder Alerts:** Identifying items below their reorder threshold to ensure timely procurement and prevent stockouts.
- **High-Value Inventory:** Highlighting items with high values that may require careful tracking and security.
- **Monthly Inventory Trends:** Breaking down inventory changes by month to visualize patterns and support strategic planning.
These queries provide insights that allow for inventory optimization, cost management, and operational efficiency.
Visualization Approaches
Visualization is a key part of data analysis. Once data is collected and formulated through queries and charts; – dashboards are created to allow for ease of viewing, understanding and decision-making.
Common visualizations include:
– **Monthly Stock Levels:** Line or bar charts showing inventory trends by month.
– **Category Distribution:** Pie charts or stacked bar charts illustrating the proportion of items by category.
– **Supplier Contribution:** Bar charts showing quantities provided by each supplier.
– **Reorder Alerts:** Highlighting items below reorder thresholds with conditional formatting or heatmaps.
– **High-Value Items:** Bubble charts or tables emphasizing items with significant financial impact.
These visualizations allow you or your management staff to quickly grasp the status of inventory and make informed decisions regarding procurement, storage, and allocation.
Analytical Insights and Reporting
By performing the queries and visualizations, several analytical insights can be drawn.
For example:
– Identification of categories or items with slow turnover.
– Recognition of suppliers who consistently meet or fail to meet inventory expectations.
– Insights into seasonal demand patterns for strategic stock planning.
– Monitoring financial exposure through high-value inventory items.
– Timely alerts for items nearing depletion to maintain operational continuity.
These insights help optimize inventory, reduce holding times/costs, minimize stagnation, and improve overall supply chain efficiency.
This information provides a structured analysis of inventory management, allowing management to review and act upon the findings accordingly.
Conclusion
In summary, the Inventory database offers a structured repository of essential information regarding stock items, suppliers, and current holding levels.
By applying well-designed queries, we can analyze stock distribution, supplier performance, and monthly trends.
This report outlines the analytical approaches, types of queries, visualization techniques, and insights that we generated from the inventory database.
Our methods ensure that your inventory management decisions are data-driven, efficient, and aligned with your business objectives.
About Me
Your Data, Your Business!
Every second, your business generates data.
To most people, the data is a pile of numbers and letters. To the trained analyst though, the data tells a story.
I help uncover the stories and present it in a fun visual format, that’s easy for anyone to understand.
