Motive/Background
The following is my analysis of PwC's publicly available case study, originally intended for students to analyze the inventory and sales data of the fictitious liquor store chain "Bibitor".
The following prompt is provided by PwC:
Data Validation & Transformation Process
Below is the Google Drive link to the PDF report created in RMarkdown detailing the entire data validation/cleaning/transformation process.
Here, I checked the data for integrity and cleanliness by running some tests, then transformed and formatted the data into appropriate tables which would then be exported as CSVs to Tableau.
You can find previews of the data tables provided for this case study on pages 2-7. From this point onward, the rest of this report will simply go over the findings and visualizations from the data.
Findings/Analysis
Since there was no specific question in particular being asked of us (only to "complete due diligence" for the company), I ended up choosing the following 6 different metrics to analyze:
Top 10 Units by Sales
For the first and perhaps most obvious metric to analyze, I set out to find the top ten units by sales across all Bibitor stores. As you can see, Jack Daniels No. 7 Black comes in at the #1 spot with ~$5.1M in sales, followed by Tito's Handmade Vodka as well as many other popular and well known liquors.
While this metric gives us good insight into the types of products that drive high sales at Bibitor, it does not tell the whole story. Knowing how much these units cost each store to buy is another important variable that must be considered, and therefore looking at profits may paint a better picture when considering data-driven business decisions for the company.
Top 10 Units by Profit
Accounting for both revenue AND cost of each item, we can now examine the top 10 most profitable units across all Bibitor stores. Just off first glance alone, one can already see the difference between the top sales and top profit charts.
Our previous #1 unit in sales (Jack Daniels) is now 10th place in profitability. As a matter of fact, only three of the products in the last chart still remain in the top 10 most profitable items, the rest being completely new. The most profitable item across all stores happens to be Kendall Jackson Chardonnay Vintners Reserve at ~$904k in profit, with Grey Goose Vodka a close second at ~$882k in profit.
Top 10 Vendors by Amount Invoiced
By examining vendor invoices, we can see which companies Bibitor stores pay the most in dollar amount. At spot #1, we have Diageo North America Inc as the vendor that Bibitor has paid the most to by a landslide. Over the entire year, Bibitor stores have purchased almost $51M worth of goods from Diageo, with Martignetti Companies in second place at ~$28M and Jim Beam third at ~$24M.
Top 10 Vendors by Units Purchased
Looking at the quantity of units purchased from each vendor (as opposed to dollar amounts), Diageo is still #1 by a large margin, with ~5.5M units purchased over the whole year. Jim Beam and Martignetti still trail behind -- albeit now in switched positions. Brown-Forman Corp completely disappears, while Sazerac emerges in 8th place.
Inventory Purchased & Sold per Month
As far as inventory goes, I wanted to figure out how each month varied by the amount of units purchased and sold. As you can see, the first four months of the year saw the least amount of inventory being moved until July which spiked up to ~6.7M units bought and sold. From there, inventory dwindles for a few months until finally peaking once again in December at ~7.1M units bought and sold.
The most likely explanation for these two largest peaks is that July sees a lot of volume due to it being a prime summer month (more people taking vacations and enjoying time off = more drinking) and December obviously being the main holiday month with Christmas around the corner (people drink more during festivities and special events).
While this chart gives us a good visualization of the volume of total inventory ordered and sold per month, it may be even more beneficial to view these numbers in a net-stock context. This way, we will be able to easily identify any shortcomings in inventory per month so that Bibitor can make better data-driven decisions.
Net Inventory per Month
Here we can see the net stock/inventory across all stores combined by month. Now we can clearly notice some obvious discrepancies and potential inefficiency with Bibitor's inventory. The months of April, July, and December are the only ones with an inventory deficit (more units were sold than ordered, indicating higher demand), with December being the largest one at a deficit of ~375k units. On the other hand, the month of August saw the largest surplus with ~436k units in excess.
This once again coincides with our previous hypothesis: there is large demand for alcoholic beverages in July and December due to summer and holiday months, and it seems as though the stores have failed to account for this. Using this information, Bibitor can better gauge just how much inventory to buy (or not buy) across it's stores over the year.