Module 11 · Excel for Data Analysis
"Mini project: what do discounts cost?"
A guided analysis of Kolanut's discounts from question to recommendation, using everything in the course.
About 45 minutes
The problem
Kolanut's finance manager raises a concern: "We give discounts all the time. How much are they costing us, who gets them, and are we getting anything back?"
This mini project walks through the full analysis. The course's final project then asks you to produce a complete sales performance review on your own.
The concept
Discount cost for an order line is what Kolanut would have earned at full price minus what it actually earned:
Excel formula
=[@quantity]*[@unit_price]*[@discount_pct]/100Summed over lines, that's the naira value of discounts given away.
Questions to answer
- How much did discounts cost in total, and as a % of gross sales?
- Which channel receives most of the discount value?
- What share of each channel's order lines is discounted?
- Do discounted lines order more packs than undiscounted ones? (If discounts buy bigger orders, they may pay for themselves.)
Example
The results, for reference once you've done your own:
| Channel | Order lines | Lines discounted | Discount cost (₦) | Share of discount cost |
|---|---|---|---|---|
| Wholesale | 2,181 | 1,347 (62%) | 25,417,295 | 87.4% |
| Supermarket | 1,308 | 358 (27%) | 3,560,935 | 12.2% |
| Kiosk | 777 | 42 (5%) | 108,825 | 0.4% |
| Total | 4,266 | 1,747 | 29,087,055 | 100% |
Discounts cost ₦29.1m, 3.4% of gross sales, and nearly nine naira in ten of it went to wholesalers.
Walkthrough
- Set up. On your Orders table (with
channellooked up from Customers), addgross = [@quantity]*[@unit_price]anddiscount_cost = [@gross]*[@discount_pct]/100. - Total cost.
=SUM(Orders[discount_cost]), and as a share of=SUM(Orders[gross]). - By channel. Pivot:
channelin Rows;discount_costin Values (Sum, then a second copy as % of Grand Total);order_idin Values as Count. - Discounted share of lines. Add
discounted = IF([@discount_pct]>0, "Yes", "No"), put it in Columns of a count pivot, or use COUNTIFS per channel. - Do discounts buy bigger orders? Pivot for Wholesale only (slicer):
discountedin Rows, Average of quantity in Values. Compare Yes and No. - Write it up on a Summary sheet: three numbers, one chart (discount cost by channel), two or three sentences, one recommendation.
Practice
Practice
What did discounts cost Kolanut in total across all order lines, to the nearest naira?
Practice
Among Wholesale order lines, what is the average quantity on lines with a discount, to one decimal place?
Challenge
Challenge · optional
And the average quantity on Wholesale lines without a discount, to one decimal place?
Check your understanding
Answer every question to check.
When you've finished, take the final assessment, then start the final project from the course page.