Tableau Lesson 52 – Financial Analysis | Dataplexa

Section IV — Lesson 52

Financial Analysis Project

Financial dashboards answer a different kind of question than sales dashboards — they track variance, cumulative totals, and budget adherence over time. This project builds a P&L dashboard with a waterfall chart, running totals, budget vs actuals, and variance analysis using one dataset and six calculated fields.

The Dataset

A 28-row monthly P&L table covering January through December across four cost categories plus revenue. Save as financials.csv.

financials.csv

Month,Month_Num,Line_Item,Type,Actual,Budget
Jan,1,Revenue,Income,142000,135000
Jan,1,Cost of Goods,Expense,58000,55000
Jan,1,Salaries,Expense,32000,32000
Jan,1,Marketing,Expense,12000,14000
Feb,2,Revenue,Income,155000,140000
Feb,2,Cost of Goods,Expense,63000,58000
Feb,2,Salaries,Expense,32000,32000
Feb,2,Marketing,Expense,14000,14000
Mar,3,Revenue,Income,168000,145000
Mar,3,Cost of Goods,Expense,69000,60000
Mar,3,Salaries,Expense,33000,32000
Mar,3,Marketing,Expense,15000,14000
Apr,4,Revenue,Income,161000,150000
Apr,4,Cost of Goods,Expense,66000,62000
Apr,4,Salaries,Expense,33000,33000
Apr,4,Marketing,Expense,13000,15000
May,5,Revenue,Income,174000,155000
May,5,Cost of Goods,Expense,71000,64000
May,5,Salaries,Expense,34000,33000
May,5,Marketing,Expense,16000,15000
Jun,6,Revenue,Income,183000,160000
Jun,6,Cost of Goods,Expense,75000,66000
Jun,6,Salaries,Expense,34000,33000
Jun,6,Marketing,Expense,17000,16000
Jul,7,Revenue,Income,178000,165000
Jul,7,Cost of Goods,Expense,73000,68000
Jul,7,Salaries,Expense,35000,34000
Jul,7,Marketing,Expense,15000,16000

The Five Business Questions

#	Business Question	Technique	Chart
Q1	How does monthly net profit build up from revenue to expenses?	Waterfall chart (Gantt bars)	Waterfall
Q2	Is cumulative revenue tracking ahead of or behind budget?	Running total (table calculation)	Dual-axis area + line
Q3	Which months are over or under budget — and by how much?	Variance calc + diverging colours	Bullet bar with reference line
Q4	How does each expense line compare to budget as a percentage?	Variance % calc + highlight table	Highlight table
Q5	What is the gross margin trend across months?	Gross Margin % (LOD + basic calc)	Line chart with reference band

Step 1 — Connect and Prepare

Connect to financials.csv. Verify types: Month_Num as Number (whole), Actual and Budget as Number (decimal), all others as String.

Sort the Month field correctly: right-click Month in the Data pane → Default Properties → Sort → Sort by Field → Month_Num → Ascending. Without this, months sort alphabetically (Apr, Aug, Dec…) instead of chronologically.

Create an extract immediately: Data → Extract Data → Extract.

Step 2 — Build the Calculated Fields

Calc 1 — Net Profit (signed)

IF [Type] = "Income" THEN [Actual]
ELSEIF [Type] = "Expense" THEN -[Actual]
END

Flips expense rows to negative so summing all rows for a month gives net profit. Revenue stays positive, all cost lines become negative — the waterfall chart uses this signed value.

Calc 2 — Budget Variance (£ absolute)

SUM([Actual]) - SUM([Budget])

Positive = over budget for expenses (bad), over budget for revenue (good). Negative = under budget for expenses (good), under budget for revenue (bad). Context matters — the chart title clarifies direction.

Calc 3 — Budget Variance %

(SUM([Actual]) - SUM([Budget])) / SUM([Budget])

Calc 4 — Revenue Only (FIXED LOD)

{FIXED [Month] : SUM(IF [Line_Item] = "Revenue" THEN [Actual] END)}

Returns the Revenue row's Actual value locked to each Month regardless of which Line_Item rows are in the view. Used to compute Gross Margin without needing to filter the view to Revenue rows only.

Calc 5 — COGS Only (FIXED LOD)

{FIXED [Month] : SUM(IF [Line_Item] = "Cost of Goods" THEN [Actual] END)}

Calc 6 — Gross Margin %

([Revenue Only] - [COGS Only]) / [Revenue Only]

Standard gross margin formula. Uses the two FIXED LODs so it computes correctly at any level of aggregation in the view — even when the view is grouped by Line_Item.

Set default formats now: Budget Variance → Currency $#,##0;-$#,##0. Budget Variance % → Percentage 1dp. Gross Margin % → Percentage 1dp.

Step 3 — Chart 1: Waterfall Chart (Q1)

A waterfall chart in Tableau uses Gantt bars with a running total table calculation to position each bar where the previous one ended. It shows how each line item contributes positively or negatively to the final total.

Filter the view to Month = Jan only (drag Month to Filters → select Jan). Drag Line_Item to Columns — sort order: Revenue, Cost of Goods, Salaries, Marketing. Drag Net Profit to Rows. Change mark type to Gantt Bar.

Drag Net Profit to the Size shelf. Right-click the Size pill → Add Table Calculation → Running Total → Sum → Compute Using Table (across). This positions each bar at the end of the previous cumulative total.

Drag Net Profit to Colour. Edit colours: set a stepped diverging palette — positive values #22c55e, negative values #ef4444, centred on zero. The Revenue bar turns green, all expense bars turn red.

Remove the Month filter to show all months — replace it with a Month parameter so the viewer can select which month to waterfall. Add Net Profit to Label shelf. Format axis as $#,##0. Title: January: $40K Net Profit After All Costs. Tab: Waterfall.

Waterfall chart — January breakdown

Step 4 — Chart 2: Cumulative Revenue vs Budget (Q2)

New sheet. Filter to Line_Item = Revenue. Drag Month to Columns (sort by Month_Num). Drag Actual to Rows. Right-click the Actual pill → Add Table Calculation → Running Total → Sum. Mark type: Area. Colour: #bfdbfe (light blue).

Add Budget to Rows as a second measure. Right-click → Add Table Calculation → Running Total. Right-click the Budget pill → Dual Axis → Synchronise Axes. Change Budget mark type to Line, colour #f97316, dashed.

Format both axes as $#,##0,,\M (abbreviated millions). Title: Cumulative Revenue Tracking $118K Ahead of Budget. Tab: Cumulative Revenue.

Step 5 — Chart 3: Monthly Budget Variance (Q3)

New sheet. Filter to Line_Item = Revenue. Drag Month to Columns, Budget Variance to Rows. Mark type: Bar.

Drag Budget Variance to Colour. Edit colours → Green-Red Diverging centred on 0 — positive variance (revenue beat budget) turns green, negative turns red. Add Budget Variance to Label shelf.

Add a reference line at zero: right-click the axis → Add Reference Line → value = 0 → line style dashed grey. Title: Revenue Beat Budget Every Month — $13K Ahead in June. Tab: Budget Variance.

Step 6 — Chart 4: Expense Variance Highlight Table (Q4)

New sheet. Filter to Type = Expense. Drag Month to Columns and Line_Item to Rows. Drag Budget Variance % to Colour and to Text. Mark type: Square.

Edit colours → Red-Green Diverging centred on 0. For expenses, a positive variance means over budget (bad = red), negative means under budget (good = green). Format text as Percentage 1dp. Title: Cost of Goods Consistently Over Budget. Tab: Expense Variance.

Expense variance highlight table — expected pattern

Step 7 — Chart 5: Gross Margin Trend (Q5)

New sheet. No row filter needed — the FIXED LODs in Gross Margin % handle the row-level isolation. Drag Month to Columns and Gross Margin % to Rows. Mark type: Line. Colour: #0ea5e9.

Add a reference band: right-click the axis → Add Reference Line → Band → set From = 0.55 and To = 0.60 → shade light yellow → label "Target range: 55–60%". This shows the acceptable gross margin corridor.

Add Gross Margin % to Label. Format axis as Percentage 0dp. Title: Gross Margin Slipping — COGS Growth Squeezing the Margin. Tab: Gross Margin Trend.

Step 8 — Assemble the Dashboard

New dashboard → Automatic size → background #f8fafc. Title text: Financial Performance — Jan–Jul 2024, 22pt bold.

Row 1 (Horizontal container): Waterfall (left 40%) + Cumulative Revenue (right 60%).

Row 2 (Horizontal container): Budget Variance bars (left 50%) + Gross Margin Trend (right 50%).

Row 3: Expense Variance highlight table — full width. Add Month parameter control for the Waterfall in the top-left. Set all container Outer Padding to 6.

📌 Teacher's Note

The waterfall chart is the single most useful financial visualisation in Tableau, and the trickiest to build because it requires two separate table calculations — one on the Rows shelf (running total to set the bar's starting position) and one on the Size shelf (the bar height). Getting the direction right is the common stumbling block: expenses must be negative in the Net Profit field, otherwise the running total stacks them upward instead of downward from the revenue bar. Sort the Line_Item column manually — Revenue first, then expenses in order of size — so the waterfall reads logically from left to right.

Practice Questions

1. The waterfall chart uses two separate table calculations — one on Rows and one on Size. What does each one control, and why are both needed?

2. The Month field is a string so Tableau sorts it alphabetically by default. How do you make it sort chronologically from January to July?

3. Gross Margin % uses two FIXED LODs to extract Revenue and COGS values. Why is this necessary instead of simply writing SUM(Revenue) / SUM(Revenue) - SUM(COGS) directly?

Next up — Lesson 53: HR Analysis Project — building a workforce dashboard covering headcount trends, attrition analysis, salary distribution, and department-level performance comparisons.

← Previous Course Index Next →

Tableau Course