Inventory Analysis with Power BI

Case Study: Inventory Analysis in Power BI

Danny Cevallos

Statistician and Data Scientist

What is a case study?

  • Examination of a particular real-world problem
  • Practice your skills
  • No new Power BI concepts

$$

Prerequisite courses

  • Introduction to Power BI
  • Data Preparation in Power BI
  • DAX Functions in Power BI

Cartoon image of a piece of paper with insights, being examined by a magnifying glass.

Case Study: Inventory Analysis in Power BI

Steps of the analysis

Data analysis process, showing the five steps in the analysis process.

1) Verify the necessary data is present and clean

2) Explore the data

3) Analyze and visualize information

4) Create dashboards and report insights

Case Study: Inventory Analysis in Power BI

The problem

Help a management team make the best decisions.

  • WarmeHands Inc.
  • Objective: to communicate insights for the renewal of items

Cartoon image of a factory storage building and a transport lorry being loaded with packages.

Case Study: Inventory Analysis in Power BI

What is an inventory analysis?

  • Optimizing the management of inventory

  • Some benefits of inventory analysis:

    • Reduction of storage expenses
    • Reduction of purchase costs
    • Reduction of unused goods
    • Helps to increase profit

Cartoon image of a group of workers stack different boxes with different key components of inventory analysis displayed on them.

Case Study: Inventory Analysis in Power BI

Starting with the inventory analysis

Some important calculations:

  • Revenue
  • Cost of Goods Sold (COGS)
  • Profit
Case Study: Inventory Analysis in Power BI

Calculating revenue

Revenue is the money generated from normal business operations, calculated as the sales price times the number of units sold

 

${Revenue = quantity\;of\;units\;sold\;\;x\;\;selling\;price}$

1 https://www.investopedia.com/terms/r/revenue.asp
Case Study: Inventory Analysis in Power BI

Cost of goods sold

Cost of producing or acquiring products that a company sells, including:

  • Raw materials
  • Labor
  • Factory overhead or costs related to production facilities

 

Example

${E.g.\;Wooden\;chair\;COGS = wood + glue + wood\;finishers + labor}$

1 https://www.irs.gov/publications/p535
Case Study: Inventory Analysis in Power BI

Exploring gross profit

Profit a company makes after deducting the costs associated
with making and selling its products

 

Simplified formula

${Gross\;Profit = Revenue - COGS }$

1 https://www.investopedia.com/terms/g/grossprofit.asp
Case Study: Inventory Analysis in Power BI

Dataset

Six tables with information on item description, initial stock, orders, dates, countries, and prices.

 

Image showing the seven dataset tables that will be used as part of the case study.

Case Study: Inventory Analysis in Power BI

Let's practice!

Case Study: Inventory Analysis in Power BI

Preparing Video For Download...