Episode 2:
Star Departments
- Sales Trends by Year
- Financial Metrics
- Navigating DAX
- Top-Selling Departments
In the previous episode, we saw a significant growth in customers, departments, and products in 2017 compared to 2015 and 2016. Naturally, this raises an important question: did sales and revenue increase as well?
To answer this, we need to understand key financial terms like gross sales, net sales, and profit.
In this episode, we’ll break down these concepts, analyze our data, and uncover the real impact of business growth on financial performance. Let’s dive in!
1. Sales Trends by Year
At first glance, 2017 should have been our strongest year—more customers and more product variety… yet something didn’t add up. Despite these expansions, the total quantity sold was lower than in 2015, and profit remained nearly the same.
Since 2015 and 2016 had similar products and departments with no significant changes, it raises some important questions:
- What changed in 2017?
- Did the addition of new departments affect our top-performing products?
- Were customers buying differently?
To uncover the real story, we’ll begin by analyzing the key financial metrics from 2015 and compare them to 2017. Along the way, we’ll answer essential questions such as:
- What’s the difference between net sales and gross sales?
- How do we calculate profit, and what role does it play in understanding business performance?
- What exactly is profit margin, and why is it important for measuring profitability?
Let’s dive in to uncover the full picture.
2. Breaking Down Financial Metrics
Gross Sales
- Definition: The total revenue generated from all sales before any deductions are made.
- Formula: Gross Sales=Total Sales Revenue
- Example: The business has achieved a remarkable 12.34 million dollars in gross sales, reflecting strong revenue generation before any expenses are accounted for.
Net Sales
- Definition: The revenue from sales after accounting for deductions (Returns, Discounts and Allowances).
- Formula: Net Sales= Gross Sales−(Returns+Discounts+Allowances)
- Example: The business has generated 12.34 million dollars in gross sales, with net sales of 11.09 million dollars. The difference of 1.25 million dollars represents the returns, discounts, and allowances that were deducted from the gross sales figure.
Profit
- Definition: The amount of money a business keeps after subtracting all costs from net sales.
- Formula: Profit = Net Sales−(Cost of Goods Sold (COGS)+Operating Expenses+Other Expenses)
- Example: The business has net sales of 11.09 million dollars, with a profit of 1.32 million dollars. The difference of 9.77 million dollars represents the expenses, including costs of goods sold, operating expenses, and other financial obligations, that were deducted from the net sales to arrive at the profit.
Profit Margin
- Definition: A percentage that shows how much profit a company makes for each dollar of revenue. It helps measure a business’s efficiency and profitability.
- Formula: Profit Margin = (Profit/Net Sales) x 100
- Example: The business has a profit of 1.32 million dollars on net sales of 11.09 million dollars, resulting in a profit margin of approximately 11.9%, which indicates the percentage of net sales that turned into profit after all expenses.
3. Navigating DAX
Total Net Sales =
SUM(table[Net Sales])
SUM adds up all the values in the Net Sales column from the table.
This gives us the total amount of net sales across all transactions.
Profit Margin =
DIVIDE([Total Profit], [Total Net Sales], 0)
DIVIDE function safely performs division, handling errors when the denominator is zero. The 0 ensures that if net sales are zero, we don’t get an error and instead return 0.
4. Star Departments: Top-Selling Departments
The Fan Shop department dominated net sales in 2015 with $6M, far surpassing other departments, which recorded $2M and $1M.
Smaller departments contributed $261K and $73K, reinforcing the Fan Shop’s significant role in overall sales.
In the next episode, we will zoom into the 2017 sales to uncover the mystery of what went off track and why those numbers took a dip, especially with the Fan Shop department shining as a star performer in 2015!
Stay tuned!