Follow the Money — Clearstone: Budget, Receivables, Cash

Beginner Finance 🕒 ~20 hours 📦 0+ published takes

Someone has sent you four files from a company's books and one question: is the company in good shape? Clearstone has a budget of a thousand lines, a ledger of a thousand invoices, a thousand cash movements and five departments. Answer the question with SQL, and then make the answer reach the people who have to act on it.

Work through the seventeen tasks with the book Follow the Money beside you: each task follows one chapter, and each chapter ends with a Try It box that describes the same task. Ask the tables for exactly the rows you want, total and group them, age the invoices from their due dates, join the budget to its departments and build questions from other questions. Then find what the books get wrong (a reversal, a status column that disagrees with the dates, due dates that run to 2041, a department with no budget), log every decision, save the rules as views, and build the finance scorecard. Finally present it: the reader and the decision, the story, a report, a dashboard or a single number, and charts that do not mislead.

The project is one notebook for the whole book. Add each chapter's blocks below the previous chapter's, in order, and assign them to that chapter's task. Later tasks read objects built by earlier ones (cleaning_decisions from Task 9, the views clean_budget and invoices from Task 10, finance_scorecard from Task 11), so keep the blocks that create an object above the blocks that use it. Each task asks you to save a table or a view with a name like ch01_reversal, and its automatic check reads it. The as-of date for everything time-based is 1 January 2027, written as DATE '2027-01-01'. Views and tables you create are gone after a page reload until you press Run all, so after reopening the project press Run all before running anything else.

You need no SQL to start: the book teaches it from the first query. Your notebook is the deliverable: assign every block to its task, write the notes each task asks for, run the automatic checks, press Run all until it passes from top to bottom, and submit.

🧪 Runs in your browser

Dataset → Profile → Clean → SQL → Chart → Quality → Python → Output

Real SQL (DuckDB) and real Python (pandas) run entirely in your browser — nothing to install, and your data never leaves it. ▶▶ Run all replays the whole notebook top-to-bottom; a clean pass marks the published repo as reproducible ⛓.

☑️ The tasks

Set the reversal aside

Chapter 1. In SQL blocks, run the chapter's queries: the first rows of each of the four tables, the two totals of receivables and of cash, the count of lines over budget, and the filters on text, numbers and dates. Then keep the reversal line as a table of its own: CREATE OR REPLACE TABLE ch01_reversal AS SELECT * FROM budget WHERE 0 > actual_amount; and the check reads it. Write one query for each of the four questions of the Try It box (the R&D lines, the departments of more than 30 people, the invoice due on 2 January 2022, the cash movements with an outflow above 49,900), and in a note one sentence for each saying what the result means in the language of the books. Assign the blocks to Task 1.

Profile the four tables

Chapter 2. Run the chapter's queries: COUNT(*), DESCRIBE, SELECT DISTINCT, MIN and MAX on each table, the key test on receivables and departments, and SUMMARIZE. Keep your profile of receivables as a table: CREATE OR REPLACE TABLE ch02_receivables_profile AS SELECT column_name, column_type, min, max, approx_unique, null_percentage FROM (SUMMARIZE receivables); and the check reads its last due date, written as the number 20410224. Do the same for budget and cash_flow with names of your own. In a note, write for each of the four tables the grain sentence (one row is one ...), the key or the lack of one, and three things a finance team would want that the table does not contain. Assign the blocks to Task 2.

Build the overspend list

Chapter 3. Run the chapter's queries, including the filter with and without its parentheses. Then build the list the head of finance asked for: every Sales and Marketing line over budget by more than 5,000 euros, with its variance in euros and as a percentage of its budget, largest variance first, ties broken by category. Save it as a table named ch03_overspend_list with the columns category, budgeted_amount, actual_amount, variance and variance_pct; the check counts its rows. Count the two departments separately and show in a note that they add up to the list. Assign the blocks to Task 3.

Summarize the budget in one row

Chapter 4. Run the chapter's queries. Then write the one-row summary a controller wants at the top of a budget pack, on the lines with an actual of zero or more: total budget, total spent, the variance in euros (in a column named variance) and as a percentage, the number of lines over budget, the total overspend and the total underspend. Save it as a table named ch04_budget_summary; the check reads its variance, which is right only if the reversal was left out. In a note, write one sentence a board member could read and one that tells the controller what the total hides. Assign the blocks to Task 4.

Save the department variance report

Chapter 5. Run the chapter's queries, and show the net cash by activity as a bar chart. Then save the department variance report as a table named ch05_variance_report with one row per department and the columns category, lines, budget, actual, variance and pct_variance, where pct_variance is the variance as a percentage of the department's total budget, not rounded and not an average of line percentages. The check adds up the variance of the departments more than 0.1 percent under budget. In a note, say which departments those are and whether any variance is material. Assign the blocks to Task 5.

Age every invoice

Chapter 6. Run the chapter's queries, and show the aging report as a bar chart. Then save a full aging table named ch06_aging with one row per bucket, including Paid and Not yet due, and the columns aging_bucket, invoices and amount, built with the seven-branch CASE of the chapter on all 1,000 invoices (no WHERE). Check in a note that its invoices add up to 1,000. The check adds up the amount of the five overdue buckets, which is right only if overdue was decided by the dates as of 1 January 2027. Assign the blocks to Task 6.

Summarize the departments

Chapter 7. Run the chapter's queries; the scratch table ch07_departments_dup is rebuilt by Run all. Then build one table, ch07_department_summary, with one row for each of the five departments and the columns category, dept_head, office, headcount, budget_lines, actual and spend_per_head, with Legal present, its lines counted as 0 and its actual shown as 0. Aggregate the budget first, then join it to departments with a left join. The check adds up the headcount, which is 162 only if every department is present once. In a note, say what an inner join and a join before aggregating would have given. Assign the blocks to Task 7.

Rank the overspends

Chapter 8. Run the chapter's queries, and show the running total of overdue receivables as a line chart. Then create a table named ch08_top_overspends with the columns category, budgeted_amount, actual_amount, variance and rnk, holding the lines ranked 1 to 3 by overspend within each department, using RANK and a window ORDER BY you could defend. The check adds up their variance, which is right only for the right twelve lines. In a note, say why a ranking needs a tie-breaker and why RANK suits a list of lines to review. Assign the blocks to Task 8.

Bridge the two overdue figures

Chapter 9. Run the chapter's queries in order, including CREATE OR REPLACE TABLE cleaning_decisions and its five INSERT statements; Tasks 10 to 17 read the log, so keep these blocks above them. SELECT SUM(rows_affected) FROM cleaning_decisions should return 865. Then build the overdue bridge as a table named ch09_overdue_bridge, with two columns, step and amount, and three rows: the amount labeled Overdue, the amount labeled Overdue but not yet due (as a negative number), and the amount labeled Pending but past due, each computed with a query, not typed. The check adds the three rows, and the bridge closes at the overdue amount by the dates. In a note, write the five decisions of the log with their move (exclude, correct or flag) and who should agree to each. Assign the blocks to Task 9.

List the invoices nobody chases

Chapter 10. This task reads cleaning_decisions from Task 9. Run the chapter's queries, including CREATE OR REPLACE VIEW clean_budget and invoices; Tasks 11 to 17 read them, so keep their blocks above. SELECT SUM(days_overdue) FROM invoices should return 141908. Then build a report view for the collections team, ch10_unchased, listing the invoices that are overdue by the dates but still labeled Pending, with the columns due_date, receivable_amount, days_overdue and aging_bucket. It must read from invoices, not from receivables. The check adds up its amounts. In a note, write the comment block for the invoices view: its grain, purpose, source, rules with their decision numbers, as-of date and owner. Assign the blocks to Task 10.

Build the scorecard and tie it out

Chapter 11. This task reads clean_budget and invoices from Task 10. Run the chapter's queries, including CREATE OR REPLACE VIEW finance_scorecard; Tasks 12 to 17 read it. SELECT overdue_amount FROM finance_scorecard should return 4015590. Add a Python block for the cross-check: df = await lp.sql('SELECT * FROM receivables') returns the table as a pandas DataFrame; apply the overdue rule to it and write the result in a note, because the reviewer cannot see Python output. Then build a companion view, ch11_receivables_risk, with one row and three columns: over_year (the overdue amount more than a year late), pending_overdue (the overdue amount labeled Pending) and pct_over_year (the share of the overdue amount more than a year late, rounded to one decimal). The check reads over_year. Write a tie-out query that checks it against the aging table, and in a note the answer to is the company in good shape, with a number behind every clause. Assign the blocks to Task 11.

Give every head a list

Chapter 12. This task reads clean_budget and finance_scorecard. Run the chapter's queries, including CREATE OR REPLACE VIEW ch12_owners; SELECT COUNT(*) FROM ch12_owners should return 5. Show the chart-ready table of overspent and underspent amounts as a bar chart. Then build the list each head receives before the review: a view named ch12_material_lines with one row per materially over line (over by more than 10 percent and by more than 5,000 euros) and the columns dept_head, category, budgeted_amount, actual_amount and variance, joining departments to clean_budget. The check adds up its variance. In a note, write the three rungs of the ladder (what happened, so what, now what) for one department head, each with its number, the one-sentence version for the CFO, and check them against the five tests of the chapter. Assign the blocks to Task 12.

Tell the story in one row

Chapter 13. This task reads cleaning_decisions, clean_budget, invoices and finance_scorecard. Run the chapter's queries, including CREATE OR REPLACE VIEW ch13_thread. Then build one view, ch13_story, that holds the numbers of the narrative in one row, with the columns overdue_amount, over_a_year_amount, pct_over_a_year (the share of the overdue money more than a year late, as a percentage rounded to one decimal place), net_cash and times_net_cash (the overdue amount divided by the net cash outflow, rounded to two places). The check reads pct_over_a_year. In a note, write the five-step narrative for the finance director (insight, breakdown, investigation, explanation, action), answer first, taking every number from the view, with its two limits stated once. Assign the blocks to Task 13.

Build the collections dashboard

Chapter 14. This task reads cleaning_decisions, clean_budget, invoices and finance_scorecard. Run the chapter's queries, including the history table ch14_scorecard_history. Then design a second dashboard, for the collections lead rather than the finance lead: in a note, write the three or four questions that person asks every Monday, and build one view, ch14_collections, with one column per question, including at least overdue_invoices, overdue_amount, over_a_year_amount, pending_past_due (the count of past-due invoices labeled Pending) and oldest_days_overdue (the largest days_overdue). The check reads oldest_days_overdue. Under it, write the qualifier line for each tile. Assign the blocks to Task 14.

Draw the aging chart for its finding

Chapter 15. This task reads clean_budget, invoices and finance_scorecard. Run the chapter's queries, including CREATE OR REPLACE VIEW ch15_variance_chart, and show each chart-ready table as the chart the chapter chooses for it. Then build the chart-ready table for Figure 15-5 as a view named ch15_aging_chart, with one row per aging bucket of the overdue invoices and the columns bucket, invoices, amount, sort_key and highlight, where highlight is true only for the bucket with the largest amount. The check adds up the amount of the highlighted bucket. Show it as a bar chart, and in a note write its title, which states the finding, and its subtitle, which states the basis and the as-of date. Assign the blocks to Task 15.

Turn five checks into rules

Chapter 16. This task reads the objects of Tasks 9 to 11. Run the chapter's ten checks. Then create a view named ch16_rules with two columns, rule and failures, and one row for each of these rules, where failures counts the rows that break it: budget actuals are not negative; the Overdue label agrees with is_overdue in invoices; every department has budget lines; no invoice is due after 31 December 2030; there is one invoice per due date (rows minus distinct due dates). Combine the five with UNION ALL. The check returns 1 when the five counts, smallest first, are 0, 1, 1, 333 and 530. In a note, say for each failing rule whether it is excluded, corrected, flagged or disclosed, and which of the ten checks it comes from. Assign the blocks to Task 16.

Fix a chart and submit

Chapter 17. This task reads clean_budget, invoices and finance_scorecard. Run the chapter's queries. Then fix item 3 for one chart: create a view named ch17_overdue_by_year with one row for each year whose invoices had fallen due by 1 January 2027, and three columns: year_due, due_amount (all receivables due that year, paid or not) and overdue_amount (the part still overdue by the dates). Show it as a bar chart, and in a note write a title that states the share. The check computes the share of the money fallen due that is still overdue. Then run the chapter's checklist on one chart of your notebook and note what you changed. Assign the blocks to Task 17, press Run all until it passes from top to bottom, and submit with a one-line description.