Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 35 ยท Projects

Project: Profitability Analysis and Forecast Story

Find where a business loses money in Tableau: discount bins, waterfall, scatter plot and a forecast, then present it as a four-point story. Checked with pandas.

Sales go up, profit goes down. It is one of the most common puzzles in business, and a good Tableau analyst can explain it. This project walks through a profitability investigation from first question to finished story: find where the money is lost, show the cause, forecast what happens if nothing changes, and recommend an action.

You will use bins, a waterfall chart, a scatter plot, a calculated field, a forecast and a four-point story. Tableau runs outside this page, so the build steps are instructions and Tableau formulas are read-only. The runnable pandas code is a stand-in that computes exactly what each chart shows, so you can confirm your Tableau results.

The question

The finance director says: "Revenue grew 25% this year, but profit is flat. Where is it going?" Hypotheses: (1) discounts, (2) a few unprofitable products, (3) a rising cost of goods. The data is 20 order lines with a discount, sales, profit and quantity.

The investigation

Question Revenue up, profit flat. Why?
Discount bins Does profit fall as discounts rise?
Waterfall Where does revenue turn into profit?
Forecast What if nothing changes?
1 / 2

Step 1: bin the discounts

A bin groups a continuous number into equal-width buckets. In Tableau, right-click the Discount measure > Create > Bins, set the size to 0.1 (10 points), and you get a dimension. Bins are the engine behind histograms, and here they let you ask "what is the average profit per discount band?" The stand-in below uses pd.cut, which also produces equal-width bins.

Python Profit by discount band

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The picture is clear: margins fall steadily with each band, and the "30%+" band loses money. This is your first finding. In Tableau, drag the Discount (bin) to Columns and SUM([Profit]) to Rows, and colour by sign (a calculated field SUM([Profit]) > 0) so the negative bars are red. Add a reference line at 0.

Step 2: confirm with a scatter plot

A scatter plot shows each order line as a dot: Discount on Columns (as a measure, not a bin), Profit on Rows, Order ID on Detail to avoid aggregation. Add a trend line (trend lines) and read Describe Trend Model. A strongly negative slope and a high R-squared say the discount explains most of the variation in profit. Check the number:

Python Does discount explain profit?

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The fitted line crosses zero profit at about a 35% discount, a useful number for the story: "Above roughly 35% off, a typical order loses money." Treat it as an estimate, since a straight line is a simplification.

Correlation is not proof of cause. Here the mechanism is plausible and also visible in the margins, but a good analyst checks that the deep discounts are not all in one loss-making product. Add Product to Color to find out.

Step 3: a profit waterfall

A waterfall chart shows how revenue turns into profit, one step at a time. Tableau builds it with a Gantt bar mark: put a running sum of the steps on Rows as a table calculation (running total), and use the negative size trick: the bar's size is minus the value so that each bar starts where the last one ended. For this data the steps are: sales at list price, discounts given, cost of goods, profit. The calculation needs list sales (sales divided by one minus the discount) and cost (sales minus profit).

Text
// List Sales
SUM([Sales] / (1 - [Discount]))

// Discount Amount
SUM([Sales] / (1 - [Discount])) - SUM([Sales])

// Cost
SUM([Sales]) - SUM([Profit])

Here is the arithmetic behind the bars:

Python Waterfall steps

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The "Discounts" bar is the second biggest step in the chain, which supports the discount hypothesis rather than a cost problem. In the chart, "List sales", "Net sales" and "Profit" are drawn as full bars from zero, while "Discounts" and "Cost of goods" float down from the previous total.

Step 4: forecast the consequence

Suppose last year's monthly profit is flat but discounts are rising by about a point each month. If nothing changes, what happens to profit? In Tableau, build a line chart of monthly profit and drag Forecast onto it, setting the model to Additive trend, no seasonality for a short history, and a forecast length of six months. The stand-in below does the same arithmetic with a straight-line projection:

Python Six-month outlook

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Profit is slipping by a little under one unit a month, so the forecast sinks further, with a band that shows the uncertainty. Remember the cautions from Trend Lines and Forecasting: a forecast extends a pattern and cannot know about a policy change. That is the point of the story: "this is the future if we keep discounting the same way".

Step 5: the story

Now assemble it (Stories and Word Clouds). Create a story with four points, each holding one sheet or dashboard and a one-sentence caption:

  1. Context: line chart of sales and profit by month. Caption: Sales rose 25%, profit did not.
  2. Finding: discount-band bars. Caption: Margins fall with every extra 10 points of discount.
  3. Cause: scatter plot with trend line, plus the waterfall. Caption: Orders above about 35% off lose money.
  4. Outlook and action: forecast with a reference line at the break-even level. Caption: If we do nothing, profit keeps slipping. Cap discounts at 20%.

Use a parameter called Max Discount in the last sheet to let the audience see what profit would have been under different caps. The calculation is small:

Text
// Profit with Capped Discount (approximation: removes discounted sales above the cap)
IF [Discount] > [Max Discount] THEN 0 ELSE [Profit] END

This is an approximation, since removing a deal also removes the customer, which is a business judgment, not a calculation. Say so on the slide.

Python What if discounts were capped?

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Capping at 30% lifts profit from 743 to 919, and capping at 20% gives 904, even though fewer lines are sold. The gain from 20% to 30% is small, so the conservative 20% cap is a sensible recommendation, with its caveat stated.

Before you present

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is a bin in Tableau?

    A bin turns a continuous measure into a discrete dimension with equal-size ranges.
  2. 2. In the scatter plot, a steep negative trend line with a high R-squared suggests:

    A negative slope means profit falls as discount rises; a high R-squared means the line fits well.
  3. 3. Which mark type builds a waterfall chart in Tableau?

    A Gantt bar with a running total on Rows and a negative size creates floating bars.
  4. 4. Why show a prediction band with the forecast in the story?

    Honest forecasts show a range; the band widens as the horizon gets longer.

Summary

  • Start with the business question and list hypotheses; test them with bins, a scatter plot and a waterfall.
  • Bins on Discount reveal where margins turn negative; a trend line puts a number on the break-even level.
  • A forecast shows the outlook if nothing changes; present the band, not just the line.
  • Assemble a four-point story: context, finding, cause, action, with one-sentence captions.
  • The pandas stand-ins reproduce each chart's arithmetic so you can verify Tableau.

Next: Calculation Practice with Solutions