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 formattingColouring cells automatically by rule, such as light red for "Over budget" and amber for "Near limit". on the Status column: light red for "Over budget", amber for "Near limit".
• A clustered bar chartA bar chart that shows two series side by side for each category, such as Budget and Actual. 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.
• ProtectLocking formula cells so they cannot be edited by accident, leaving only input cells open. 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.