AdvancedLesson 30 of 306 min read

Capstone: build and document a workbook system

Finish the course with a complete, documented workbook: README, inputs, data, calculations, dashboard and checks, with a real model built and verified.

In this lesson you will

  • Plan a capstone with all six parts
  • Build checks first and test like a stranger
  • Write a clear README
  • Present results honestly

About 20 minutes

One piece of work that proves you can do it

Noor has finished twenty-nine lessons. The last step is a capstone: a single workbook that shows what she has learned, and that a stranger could open, understand, trust and update. This is what employers and clients are really asking when they say ‘show me something you built’. In this lesson you will plan, build and document a small but complete workbook system, with inputs, data, calculations, a dashboard, checks and a README.

What your capstone must include

Part What it shows
README sheet Purpose, how to update, who to ask
Inputs sheet Targets, rates, lists: every assumption in one place
Data sheet A clean table, with data validation where people type
Calc sheet Formulas: SUMIFS, lookups, IF, dates or text as needed
Dashboard 4 to 6 KPIs and 2 or 3 charts, nothing typed
Checks sheet Tests that must all show TRUE

Step 1: choose a real problem

Pick something you understand: a shop’s monthly sales, a class marks sheet, a club’s finances, a personal budget with goals, a project tracker, a stock list. It should have real inputs (rows of data), a few rules and a question to answer. Keep it small enough to finish in a week.

Step 2: write the brief and the acceptance tests

Use lesson 29. Half a page: the purpose, the users, the inputs, the outputs, the rules and what it must never do. Then list four to six checks that will tell you it works, such as ‘revenue by region adds up to total revenue’, ‘no region is blank’ and ‘every row has units and a price’. Write the checks before you build.

Step 3: build it in this order

  1. Inputs and Data sheets, with validation and a Table.
  2. The Calc sheet, one formula pattern at a time, copied down and checked.
  3. The Checks sheet, so you can see problems early.
  4. The Dashboard, using only cells from Calc.
  5. Formatting: number formats, frozen headers, colours for input cells, a legend.
  6. Protection: lock formulas, leave inputs open.
  7. README last, but keep notes as you go.

A model capstone, built and checked

The program below builds the model capstone workbook: README, Inputs, Data (twelve sales rows), Calc, Dashboard and Checks. It writes the formulas, recalculates the file in a spreadsheet program and prints the dashboard and check results. The sample data is invented. The output is real:

import openpyxl, subprocess, os
wb = openpyxl.Workbook()
readme = wb.active; readme.title = "README"
for i, line in enumerate(["Sales report v1.0", "Purpose: monthly sales by region for the owner", "Inputs: Data sheet (paste the export) and Inputs sheet (target, tax)",
                          "Outputs: Dashboard sheet", "Checks: Checks sheet must show all TRUE before sending", "Update: paste new rows, refresh, read Checks"], start=1):
    readme[f"A{i}"] = line
inp = wb.create_sheet("Inputs"); inp["A1"], inp["B1"] = "Target per region", 1000; inp["A2"], inp["B2"] = "Tax rate", 0.18
data = wb.create_sheet("Data"); data.append(["Region", "Units", "Price", "Revenue"])
rows = [("North", 10, 20), ("South", 5, 60), ("North", 8, 60), ("East", 20, 20), ("East", 3, 60), ("South", 15, 20),
        ("West", 6, 60), ("North", 12, 20), ("East", 9, 60), ("South", 4, 60), ("West", 25, 20), ("North", 7, 60)]
for i, (r, u, p) in enumerate(rows, start=2):
    data.append([r, u, p, f"=B{i}*C{i}"])
calc = wb.create_sheet("Calc"); calc.append(["Region", "Revenue", "Status", "With tax"])
for i, r in enumerate(["North", "South", "East", "West"], start=2):
    calc.append([r, f"=SUMIF(Data!A2:A13,A{i},Data!D2:D13)", f'=IF(B{i}>=Inputs!$B$1,"On target","Below target")', f"=B{i}*(1+Inputs!$B$2)"])
dash = wb.create_sheet("Dashboard")
dash["A1"], dash["B1"] = "Total revenue", "=SUM(Calc!B2:B5)"
dash["A2"], dash["B2"] = "Regions on target", '=COUNTIF(Calc!C2:C5,"On target")'
dash["A3"], dash["B3"] = "Best region", "=INDEX(Calc!A2:A5,MATCH(MAX(Calc!B2:B5),Calc!B2:B5,0))"
chk = wb.create_sheet("Checks")
chk["A1"], chk["B1"] = "Regions add up to the data", "=SUM(Calc!B2:B5)=SUM(Data!D2:D13)"
chk["A2"], chk["B2"] = "No blank regions in data", '=COUNTBLANK(Data!A2:A13)=0'
chk["A3"], chk["B3"] = "Every row has units and price", "=COUNT(Data!B2:C13)=24"
chk["A4"], chk["B4"] = "All checks pass", "=AND(B1:B3)"
wb.save("capstone.xlsx")
os.makedirs("out", exist_ok=True)
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "out", "capstone.xlsx"], capture_output=True, timeout=120)
v = openpyxl.load_workbook("out/capstone.xlsx", data_only=True)
print("sheets:", ", ".join(wb.sheetnames))
for r in range(1, 4):
    print(f"Dashboard  {v['Dashboard'][f'A{r}'].value:18s}", v["Dashboard"][f"B{r}"].value)
for r in range(1, 5):
    print(f"Checks     {v['Checks'][f'A{r}'].value:32s}", v["Checks"][f"B{r}"].value)
sheets: README, Inputs, Data, Calc, Dashboard, Checks
Dashboard  Total revenue      4160
Dashboard  Regions on target  2
Dashboard  Best region        North
Checks     Regions add up to the data       True
Checks     No blank regions in data         True
Checks     Every row has units and price    True
Checks     All checks pass                  True

The total revenue of 4160 and the best region North match an independent calculation, two regions are on target and all four checks show TRUE. The last check combines the others with AND, so a single glance tells you whether it is safe to send.

Step 4: test like a stranger

  • Paste in new data with an extra region and see whether the Calc sheet notices (a Check should fail).
  • Change a target and an input rate, and watch results update.
  • Try to type a wrong value in a validated cell.
  • Ask someone else to update it using only your README.

Step 5: write the README

Write for someone who has never met you. State the purpose, the sheets, how to update step by step, what each check means, what to do if one fails, the version and date, known limits and your contact. A good README fits on one screen.

Step 6: present it

  1. State the problem in one sentence.
  2. Show the dashboard and say what it tells you.
  3. Show the checks and one failure you found and fixed.
  4. Name the limits honestly.
  5. Offer the README and the file.

Self-review checklist

  • No typed numbers hidden inside formulas.
  • Every assumption is in the Inputs sheet.
  • Every KPI has been checked by hand once.
  • Checks all show TRUE and failing checks would show clearly.
  • No private data is included.
  • Someone else has updated it using only the README.

Where to go next

Keep building. Add Power Query for your regular imports, learn Power BI or SQL if your data grows, and teach what you know. Add this capstone and two others to a portfolio, and describe what each one saved or improved in plain terms. You now have the skills to start, and the method to keep improving.

Practice

Complete your capstone with all six parts. Have one person follow your README without your help, and write down every place they get stuck. Fix those places in the README and the workbook, and add a line to a Change Log.

Practice answer

Typical gaps found by a second person: the paste location was unclear, the meaning of a check was missing, or an input cell looked like a calculation. Add colour and labels for inputs, a line per check and a numbered update guide.

Common mistake

Showing only a polished dashboard. The checks, the notes and the failures you fixed are the best evidence that you can be trusted with someone else’s numbers.

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

Which is NOT part of the capstone?

  1. A single sheet with typed totals and no checks
  2. README sheet
  3. Inputs sheet
  4. Checks sheet

The capstone is a system, not a quick sheet.

Why write the checks before building?

  1. They look nice
  2. They define what working means
  3. Excel needs them
  4. They shorten the file

It avoids moving the goalposts.

What should the Dashboard contain?

  1. Typed numbers
  2. Private data
  3. Macros only
  4. Only values pulled from Calc

Nothing typed, so it stays correct.

What does the final AND check do?

  1. Adds the data
  2. Tells you at a glance if all checks pass
  3. Sorts rows
  4. Hides errors

One cell, one answer.

What is the best test of your README?

  1. You read it twice
  2. It is long
  3. It has colours
  4. A stranger updates the file using only it

Their stuck points are your missing lines.

Summary

  • A capstone is a system: README, inputs, data, calculations, dashboard, checks.
  • Write checks before you build.
  • Nothing typed on the dashboard; assumptions in Inputs.
  • Let a stranger test your README.

Next lesson

You have completed the Excel course. Well done.

Questions

How long should the capstone take?

A small, focused one takes a week of part-time work. Keep the scope small and finish it.

Can I use my own data?

Yes, if it is yours and contains no private details about other people. Otherwise use invented data.

What if a check fails at the end?

Investigate before presenting. A failing check that you explain and fix is better evidence than a perfect-looking sheet.

Progress is saved in this browser.