Mini project: a cost tracker

Why it matters

Every career in this course tracks costs somewhere: a lab's consumables, a site's materials, a training budget, a project's spend against plan. A small, well-built tracker is also a great portfolio piece because it shows clean structure, correct formulas and clear communication. This project brings together every lesson in the course.

What you will build

A workbook with four sheets:

Step 1: the Entries table

Headers: Date, Description, Category, Amount, PaidBy. Apply what you learned:

• Dates in ISO format and a date number format; Amount as currency with 2 decimals.
• Freeze the header row; no blank rows; no merged cells.
• Validation: Category from Lists!A2:A6, Amount a decimal greater than 0, Date within the project or year.

Enter at least 15 realistic rows. Use your own context (lab supplies, a renovation, a training course, a month of household costs) or invent a project. Do not use real personal or confidential data in a file you share.

Step 2: the Summary sheet

Formulas in row 2, filled down:

C2: =SUMIF(Entries!$C:$C, A2, Entries!$D:$D)
D2: =B2-C2
E2: =IF(C2>B2, "Over budget", IF(C2>0.9*B2, "Near limit", "OK"))

Add a totals row (=SUM(B2:B6) and so on) and an overall percent used: =C7/B7, formatted as Percent. Check that the total Actual equals =SUM(Entries!D:D). If it does not, an entry has a category that is not on the list.

Optionally put the 90% threshold in a labelled cell (say H1) and use $H$1 in the formula, so the warning level is easy to change.

Step 3: highlight and chart

• Conditional formatting on the Status column: light red for "Over budget", amber for "Near limit".
• A clustered bar chart of Budget versus Actual per category, axis from zero, titled with the main finding ("Equipment is 12% over budget"), axis titled with the currency.
• Optional: a pivot table of Amount by month (add a helper column =TEXT(A2, "yyyy-mm") to Entries) and a line or column chart of monthly spend.

Step 4: make it trustworthy

A tracker other people rely on needs a little documentation:

• Notes sheet: purpose, data source, who updates it, the date of last update, what each status means.
• Protect formula cells from accidental edits: Data > Protect sheets and ranges (Sheets), Tools > Protect Sheet with only input cells unlocked (Calc: Format Cells > Cell Protection), or Review > Protect Sheet (Excel).
• Test it: add an entry that pushes one category over budget and confirm the status, colour and chart all update. Then try entering an invalid category and a negative amount, and confirm validation stops you.

Worked example (partial)

Budget Equipment 500, Consumables 300. Entries include a 420.00 microscope camera and a 140.00 tripod (Equipment), plus 95.00 and 180.00 in Consumables.

• Equipment: Actual 560.00, Remaining -60.00, Status Over budget.
• Consumables: Actual 275.00, Remaining 25.00, 275 is more than 90% of 300 (270), so Status Near limit.

Common mistakes

• Typing totals by hand instead of with formulas.
• Category text in Entries that does not exactly match the Summary labels (use the drop-down).
• Summing whole columns that include a totals row on the same sheet (keep totals on Summary).
• A chart nobody can read without you explaining it.

Quick recap

You used: a tidy table and formatting (lesson 1), absolute references (lesson 2), SUMIF and nested IF (lesson 3), validation and conditional formatting (lesson 4), clean consistent categories (lessons 5 and 6), an optional pivot (lesson 7) and a labelled chart (lesson 8).

Try it

Open the Do tab and build your tracker. When it is done, it is worth adding to your portfolio with a one-paragraph description of what it does.