1. Inventory Analysis with Power BI
I’m Danny, and I’ll be your instructor for this case study.
2. What is a case study?
A case study is an examination of a particular real-world problem. Doing a case study helps you strengthen your skills by practicing what you have learned in other courses. So, the focus is on combining your skills to bring solutions to a real-world problem.
For this case study, you might need concepts from these courses.
3. Steps of the analysis
Before analyzing our business problem, it is important to clearly understand the steps involved.
First, start by making sure the data needed is present and clean. For example, structuring the data, correcting typing mistakes, removing duplicate values, and other possible errors.
Then, explore your data. It can help you get familiar with the information, think of possible calculations, and identify potential side problems and ways to solve them.
The following step is to analyze and visualize the data. Here you focus on the problem and try multiple ways to solve it, to obtain the best possible result.
The last step is to prepare a report where you can show the most important results from your analysis in a clear way with one or more dashboards and communicate insights.
4. The problem
In this case study, you will use a fictitious dataset from a company called WarmeHands Incorporated. You are hired as a consultant to analyze the inventory data and give some insights into which would be the best items for renewing or increasing inventory.
5. What is an inventory analysis?
What is an inventory analysis? Inventory analysis is a management technique that can help you optimize inventory control. For instance, it can help you reduce storage expenses, item costs, and unused goods and to increase profit. There are many ways to do inventory analysis, but for this case study a basic approach will be used.
6. Starting with the inventory analysis
Starting with a basic approach, you will use some important calculations and methods for inventory analysis that will be shown gradually throughout the course. In this first part, you will use revenue, cost of goods sold, and profit calculations.
7. Calculating revenue
Revenue is the amount of money a company receives in exchange for its goods and services.
The simplest way companies calculate revenue is by multiplying the total number of units sold by their selling price.
8. Cost of goods sold
Cost of goods sold, or COGS, refers to the cost of producing or acquiring products that a company sells in a determined period. It only accounts for production costs such as raw materials, labor, and manufacturing overhead, excluding the cost of selling the product and transportation.
For example, the COGS of a wooden chair for a traditional wooden chair-making company would be the price of wood, glue, finishers, and labor, but not the distribution of the product. So, our COGS for a wooden chair would be the price of wood plus the price of glue plus the price of wood finishers plus the price of labor.
9. Exploring gross profit
The gross profit is the amount resulting after subtracting costs associated with making or acquiring products. It is calculated by subtracting COGS from total revenue.
10. Dataset
The WarmeHands dataset contains six tables where you will find information on the ID code of the items (written also as "SKU" or "SKU-ID"), their description, initial stock quantities, individual orders with their respective quantities and dates, order country of precedence, and price.
It is your job to make the best use of this information.
11. Let's practice!
It's time to check your understanding of the steps needed in the analysis development process. Let's practice!