S31 · stakeholder simulation

The revenue dashboard that reads 3.7% low

People, data and outputs in this scenario are simulated. Each stage is recorded when you commit; the reveal comes after.

4stages
35 minabout
0stages committed

The brief

The synthetic airline's revenue dashboard sums revenue from flight_day_raw (2022-2023). Finance, which reconciles against the cleaned flight_day table, says the dashboard is wrong. The chief commercial officer (simulated) asks you for a one-page briefing before the board pack is frozen on Friday.

Facts recomputed from the sample database (all synthetic):

flight_day_raw has 13,887 rows; the cleaned table has 13,870 flight-days (key: date + flight_no).
110 flight-days appear twice. In 55 of those pairs the revenue values differ (a late revenue update).
280 flight-days (after removing duplicates) have a null revenue; 93 flight-days are absent altogether, including a 9-day gap on IST-TZX in March 2023.
Dashboard total, all raw rows: 392.31 million. Finance total, cleaned: 407.40 million.

Stage 1 of 4

The dashboard sum skips nulls and counts both rows of a duplicate pair. You separate the pieces: extra duplicate rows, null revenue values, and flight-days that are missing from the raw table.

Which decomposition of the 3.7% shortfall is correct?

Duplicates are the main problem: 110 repeated flight-days double-count revenue, so the dashboard should read high, and the shortfall comes from deleting the wrong copy when finance cleans.Only the 93 missing flight-days matter (-0.6%): the sum skips nulls, so null revenue cannot bias the total, and duplicates cancel against the gaps.Missing flight-days and null revenue together explain about 4.5%; duplicates also reduce revenue, because they are removed from the dashboard's own total.Duplicates add about 3.3 million (+0.8%); null revenue removes about 15.7 million (-3.9%); missing flight-days remove about 2.6 million (-0.6%). Nulls dominate and the net is -3.7%.