---

Workflow

1. Consolidate all data

2. Build a data model

3. Established foundational DAX measures for KPIs

4. Store each KPI in the Power Pivot

5. Develop a dashboard designed for end users, allowing them to understand the results easily.

---

Tools

Tools:
MS Excel, Power Query, Power Pivot, Data Modeling, and DAX (Data Analysis Expressions)
Dataset:
CSV, MS Access Database

---

Consolidating the data

I created a folder to store all the sales data, which will be the source folder for Power Query.

I connected the folder in Power Query using a folder connection.

Once the dataset was consolidated, I named my query fTransaction to distinguish it from my dimension tables. The "F" stands for fact table.

The next step is to load the two other dimension tables into Power Query.

I use a Database connector for my second dimension because it is an MS Access file.

These steps imported all tables, including the fact table and dimension tables (Sales, Sales Rep, Products), into Power Query, which serves as the front-end tool for our Power Pivot Data Model.

Building the data model

In the Power Pivot UI, you can access the Diagram View in two ways: by using the Home Tab button or the View Selector.

In the Diagram View,I drag and drop my dimension tables onto the fact table, and the final result looks like this:

Dimension (dSalesRep[SalesRepID]) → Fact (fTransactions[SalesRepID])
Dimension (dProduct[ProductID]) → Fact (fTransactions[ProductID])

Building relationships is more efficient than using helper columns and lookup functions. This is also a core requirement for using Power Pivot. It allows users to view multiple tables in the Pivot Table fields. Lastly, relationships enable DAX formulas to create measures and access a table on the other side of the relationship.

Measures and Power Pivot

Based on the consolidated data, I am interested in uncovering the main KPIs, such as Total Revenue, Average Daily Revenue, % Gross Profit, Average Transaction Revenue, and Cumulative Yearly Revenue.

There are two types of measures. Implicit and Explicit. For this report, I will use an Explicit measure, and here’s why I prefer it

Before creating Measures, I need to add a dimension table for dates to use the Time Intelligence functions of DAX. This is ideal because the dataset already has a date column, which allows me to use the Power Pivot user interface effectively.

Power Pivot provides an option to create a table based on the Date column from the fact table, which is very convenient. You can find it in the Design tab under the Calendar ribbon's Date Table drop-down menu.

The final data model output would be below:

The first KPI I will calculate is Total Revenue. In my fact table, I have UnitsSold, and because there is a relationship with my dimension table, I can use a DAX function called RELATED to apply the filter context for RetailPrice.

After defining Total Revenue, I will calculate Ave. Daily Revenue, and the results are shown below:

To evaluate the company's performance, I calculated the Total Units Sold, Gross Profit, and the % Gross Profit, and the results are shown below:

Overall Performance:

• Strong Total Units Sold: With nearly 100 million units sold, the company demonstrates a significant market presence and a high volume of activity.

• Substantial Gross Profit: A gross profit of over $390 million indicates a considerable amount of revenue remaining after accounting for the cost of goods sold.

• Reasonable Overall Gross Profit Margin: The grand total gross profit margin of 24.99% suggests that, on average, the company is retaining a healthy portion of its sales revenue as profit before operating expenses.

Performance by Product/Category:

• High Performers (High Units Sold and Good Profitability): Several products stand out with both high unit sales and decent to good gross profit margins. Examples include Aspen, Beaut, Bellen, Eagle, Elevate, Quad, Sunset, and Yanaki. These are likely key contributors to the company's overall success.

• High Profit Margin Products (Lower Units Sold but High Percentage): Products like Sunshine and Trifly have very high gross profit margins (34.44% and 35.92% respectively), even though their unit sales are lower compared to the top performers. These could be niche products or premium offerings.

• Concerning Low/Negative Profitability: Vrang stands out with a significantly negative gross profit margin (-62.82%). This is a major concern, indicating that the cost of goods sold for this product exceeds its revenue. This product is severely impacting overall profitability and needs immediate attention.

• Moderate Performers: Products like Carlota, Flattop, Kangaroo, LongRang, and NaturalElbow show moderate unit sales and gross profit margins. They contribute to the overall performance but might have room for improvement or could be considered stable but not high-growth products.

Key Observations and Potential Areas for Action:

• The "Vrang" Issue is Critical: The negative gross profit for Vrang needs immediate investigation. The company needs to understand why this product is so unprofitable. Potential reasons could include high production costs, low selling prices, significant returns, or inventory issues. Depending on the cause, the company might need to adjust pricing, reduce costs, re-evaluate the product's viability, or even discontinue it.

• Leverage High-Performing Products: The company should focus on strategies to further grow the sales and potentially optimize the profitability of their high-performing products (Aspen, Beaut, Bellen, Eagle, Elevate, Quad, Sunset, Yanaki).

• Analyze Moderate Performers: It would be beneficial to analyze the moderate performers to identify opportunities for improvement in either sales volume or profit margins.

• Consider Strategies for High-Margin, Lower-Volume Products: For products like Sunshine and Trifly, the company could explore strategies to potentially increase sales volume without sacrificing the high profit margin.

• Overall Healthy but Needs Attention to Underperformers: While the overall performance looks reasonably healthy due to strong sales and a decent average profit margin, the significant loss from "Vrang" is a major red flag that needs to be addressed to improve overall profitability.

For the last KPI, Average Transaction Revenue

• Significant Variation in Customer Spending: There's a substantial range in the average transaction revenue across different products. Customers buying "Elevate" tend to spend significantly more per transaction ($2,412.78) compared to those buying "Vrang" ($637.95). This highlights different purchasing behaviors or pricing strategies associated with these products.

• "Elevate," "LongRang," and "Quad" as High-Value Transactions: These products consistently generate the highest average revenue per transaction. This could be due to higher unit prices, customers purchasing multiple units together, or these products often being bundled or sold with expensive accessories. Strategies to further promote these products or understand the drivers behind these higher values could be beneficial.

• "Vrang" as a Low-Value Transaction: The extremely low average transaction revenue for "Vrang" aligns with its negative gross profit margin. Customers are spending very little on average per purchase of this product. This reinforces the need to critically evaluate "Vrang's" viability and strategy.

• "Eagle" and "Sunshine" - High Volume, Lower Value? While the "Eagle" has the highest total units sold, it reveals a relatively lower average transaction revenue ($974.94). This suggests that "Eagle" might be a high-volume product with a lower average selling price or fewer items per transaction. "Sunshine" shows a similar pattern with a low average transaction revenue ($970.67).

• Understanding Customer Purchase Behavior: The "Ave. Transaction Revenue" data provides valuable insights into how customers interact with different products. It helps understand which products drive higher spending per purchase and which ones are associated with lower individual transaction values.

Connecting to Gross Profit Margin:

• No Direct Correlation is Obvious: There isn't a perfect direct correlation between high average transaction revenue and high gross profit margin. For example, "Trifly" and "Sunshine" have some of the highest gross profit margins but moderate to low average transaction revenues. This suggests that profitability isn't solely driven by the amount spent per transaction but also by the cost of goods sold.

• "Vrang's" Consistent Underperformance: The data consistently paints a negative picture for "Vrang" – both in terms of a negative gross profit margin and a very low average transaction revenue.

Potential Actions and Further Analysis:

• Investigate High ATR Products: Understand why "Elevate," "LongRang," and "Quad" have such high average transaction values. Can these insights be leveraged for other products? Are there opportunities for bundling or cross-selling?

• Address Low ATR Products: Analyze why "Vrang," "Eagle," and "Sunshine" have lower average transaction revenues. Is it pricing, product type, or customer behavior? For "Vrang," drastic action seems necessary. For "Eagle" and "Sunshine," strategies to increase the average spend per customer could be explored.

• Consider Transaction Volume: While ATR is important, remember to consider the volume of transactions for each product. A product with a lower ATR but very high transaction volume can still be a significant revenue driver (as potentially seen with "Eagle").

• Customer Segmentation: Further analysis could involve segmenting customers to see if different groups have varying average transaction revenues for specific products.

Report and Dashboard

I created a dashboard that highlights the key metrics most affecting my findings. The dashboard is shown below.

References

https://dax.guide/

https://support.microsoft.com/en-us/office/create-a-data-model-in-excel-87e7a54c-87dc-488e-9410-5c75dbcb0f7b

Dataset

You can find the dataset and the final output on Kaggle

Disclaimer

This portfolio includes insights developed with the assistance of Artificial Intelligence tools. I do not have an academic background in finance; the content presented is based on my professional experience, including learnings from previous employers, self-directed study, and participation in masterclasses. All information is shared for demonstration purposes only and should not be considered professional financial advice.