Canadian University DubaiSchool of Managementcud.ac.ae ↗
EXPLORE ANALYTICS / CASE 01

Sales are up.
So why is profit down?

Filter the business, compare two periods, and test the effect of a discount change. Every number below comes from the downloadable synthetic order data.

Synthetic teaching caseSQL · Business intelligence · Decisions
Percentage points; quantity held constant.
Loading case data…

Revenue by product

Previous period: blue · Current scenario: red

Contribution by product

After product cost; before fixed overhead.

Inspect the numbers
Filtered comparison, AED
PeriodProductUnitsRevenueContributionMargin
See the SQL behind the baseline

This SQLite query computes the unadjusted data by period, channel and product. Dashboard filters combine the matching groups; the slider adds percentage points to current-period discounts only.

-- SQLite. Synthetic teaching data; amounts are illustrative AED.
-- Import products.csv as products and orders.csv as orders.
-- Contribution excludes fixed overhead and tax.
SELECT o.period, o.channel, p.product,
       SUM(o.quantity) AS units,
       ROUND(SUM(o.quantity * p.price * (1-o.discount_pct)), 2) AS revenue,
       ROUND(SUM(o.quantity * (p.price * (1-o.discount_pct)-p.cost)), 2) AS contribution,
       ROUND(100.0 * SUM(o.quantity * (p.price * (1-o.discount_pct)-p.cost)) /
         NULLIF(SUM(o.quantity * p.price * (1-o.discount_pct)),0), 2) AS margin_pct
FROM orders o JOIN products p ON o.product_id=p.product_id
GROUP BY o.period, o.channel, p.product
ORDER BY o.period, o.channel, p.product;
Download SQL ↓
What this scenario assumes

Revenue = quantity × list price × (1 − discount). Contribution = revenue − quantity × unit cost. The slider holds demand, product costs and mix constant; it is an accounting scenario, not an estimated causal effect or demand forecast. Discounts are bounded to 0–60%. Real pricing decisions need evidence about demand response, returns and operating costs.

TRY A RESEARCH TASK

What would you test next?

Separate the effect of volume, product mix and discounts. Then explain which additional data you would need before recommending a pricing change.

Contribute as a Research Volunteer ↗

Keep exploring

Case 02 · Which customers return? →

Case 03 · How long is the wait? →

Case 04 · Where is inventory stuck? →