50 min read

Budget Capstone

Build the complete NorthgateBudget.xlsx: budgets, formulas, formatting, expenses table, chart, PivotTable, and print setup.

What the Capstone Proves

Individual lessons teach one Ribbon path at a time. The capstone proves you can build the file Dr. Helen Marsh actually uses — data, formulas, chart, pivot, and print. James Okonkwo assembles the governor pack at Northgate Academy: Budgets for planned spend, tblExpenses for purchases, BudgetChart for the meeting slide, PivotSummary for totals, and a print-ready PDF. This lesson is a guided checklist . Work through it in order in real Microsoft Excel on NorthgateBudget.xlsx . Small example figures for learning. A real school budget workbook may have many more rows and sheets.

Step 1 — Workbook and Sheets

Create or open NorthgateBudget and save as NorthgateBudget.xlsx . Rename Sheet1 to Budgets . Add a sheet Expenses . You will add BudgetChart and PivotSummary later from chart and PivotTable commands. Press Ctrl+S / Cmd+S after each step. If Excel closes unexpectedly, you lose at most one section of work.

Step 2 — Budget Grid and Formulas

Enter headers and all figures from the Department budgets table in the Entering Data lesson. In F1, type Annual Total (£) . In F2, type =SUM(B2:E2) — copy to F6 with the fill handle. Check English F2 = 50500 . Add Avg Quarter in G with =AVERAGE(B2:E2), contingency rate 0.05 in I1, With contingency in J with **=F2*(1+ I 1), and Status in K with =IF(F2>50000,"Review","OK")** — copy each formula down through row 6.

Budgets sheet — target layout
Budgets sheet — target layout

Type data first, then formulas, then copy with the fill handle.

Ribbon Paths for Formulas

Task Ribbon path Annual total Home \to Editing \to AutoSum (or Formulas \to AutoSum) Contingency Type **=F2*(1+ I 1) manually — lock I 1** Status flag Formulas \to Insert Function \to IF Click F2 after each formula — the formula bar should show the formula, not only the number.

Task	Ribbon path
Annual total	Home → Editing → AutoSum (or Formulas → AutoSum)
Contingency	Type *=F2(1+ $I$ 1) manually — lock $I$ 1**
Status flag	Formulas → Insert Function → IF

Step 3 — Formatting

Select B2:E6 and total amount columns \to Home \to Number \to Currency . Select A1:K1 \to Home \to Font \to Bold . Select A1:K6 \to Home \to Font \to Borders \to All Borders . Select B2:E6 \to Home \to Styles \to Conditional Formatting \to Greater Than \to 15000 . Science Q4 in E4 should highlight.

Step 4 — Expenses and Table

On Expenses, enter the eight sample rows from the course. Then select A1:E9 \to Insert \to Table \to tick My table has headers . Table Design \to Table Name \to tblExpenses . Home \to Sort & Filter \to Largest to Smallest on Amount — verify sort works. Filter Department to Science — expect two rows (~ £795 total), then clear filter.

Expenses
Expenses

Enter these rows, then convert A1:E9 to a table named tblExpenses.

Step 5 — Chart and PivotTable

On Budgets, select A1:E6 \to Insert \to Charts \to Clustered Column . Chart Design \to Move Chart \to new sheet BudgetChart . Title: Northgate Academy — Department budgets by quarter . On Expenses, Insert \to PivotTable from tblExpenses \to new sheet PivotSummary . Rows: Department . Values: Sum of Amount . Science total \approx £795 .

Step 6 — Print Setup

On Budgets : Page Layout \to Orientation \to Landscape . Select A1:K6 \to Page Layout \to Print Area \to Set Print Area . Page Setup \to Header/Footer \to centre header Northgate Academy — Department budget summary . File \to Print — preview fits one page width (adjust Scale to Fit if needed). Save NorthgateBudget.xlsx .

Final Self-Check

Check Pass? File name is NorthgateBudget.xlsx Budgets has 5 departments \times 4 quarters F2 = 50500 for English tblExpenses has filter arrows BudgetChart tab exists PivotSummary shows department totals Print preview shows only the budget block James Okonkwo can hand this file to Dr. Helen Marsh at Northgate Academy — budgets, spending summary, and a chart in one workbook. Work through the checklist top to bottom. If a check fails, jump back to the lesson that covers that skill rather than guessing formulas.

Check	Pass?
File name is NorthgateBudget.xlsx
Budgets has 5 departments × 4 quarters
F2 = 50500 for English
tblExpenses has filter arrows
BudgetChart tab exists
PivotSummary shows department totals
Print preview shows only the budget block

Practice

Swipe or use the arrows to move between questions.

1 / 3

Which sheet holds the quarterly department figures?

English annual total should be:

Which sheet summarises total spend by department from tblExpenses?

More lessons in Microsoft Excel · Next: Course Exam · Previous: Protecting a Workbook

What the Capstone Proves

Individual lessons teach one Ribbon path at a time. The capstone proves you can build the file Dr. Helen Marsh actually uses — data, formulas, chart, pivot, and print. James Okonkwo assembles the governor pack at Northgate Academy: Budgets for planned spend, tblExpenses for purchases, BudgetChart for the meeting slide, PivotSummary for totals, and a print-ready PDF. This lesson is a guided checklist . Work through it in order in real Microsoft Excel on NorthgateBudget.xlsx . Small example figures for learning. A real school budget workbook may have many more rows and sheets.

Step 1 — Workbook and Sheets

Create or open NorthgateBudget and save as NorthgateBudget.xlsx . Rename Sheet1 to Budgets . Add a sheet Expenses . You will add BudgetChart and PivotSummary later from chart and PivotTable commands. Press Ctrl+S / Cmd+S after each step. If Excel closes unexpectedly, you lose at most one section of work.

Step 2 — Budget Grid and Formulas

Enter headers and all figures from the Department budgets table in the Entering Data lesson. In F1, type Annual Total (£) . In F2, type =SUM(B2:E2) — copy to F6 with the fill handle. Check English F2 = 50500 . Add Avg Quarter in G with =AVERAGE(B2:E2), contingency rate 0.05 in I1, With contingency in J with **=F2*(1+ I 1), and Status in K with =IF(F2>50000,"Review","OK")** — copy each formula down through row 6.

Budgets sheet — target layout
Budgets sheet — target layout

Type data first, then formulas, then copy with the fill handle.

Task	Ribbon path
Annual total	Home → Editing → AutoSum (or Formulas → AutoSum)
Contingency	Type *=F2(1+ $I$ 1) manually — lock $I$ 1**
Status flag	Formulas → Insert Function → IF

Task

Ribbon path

Annual total

Home → Editing → AutoSum (or Formulas → AutoSum)

Contingency

Type **=F2*(1+

I

1) manually — lock

I

1**

Status flag

Formulas → Insert Function → IF

Step 4 — Expenses and Table

On Expenses, enter the eight sample rows from the course. Then select A1:E9 \to Insert \to Table \to tick My table has headers . Table Design \to Table Name \to tblExpenses . Home \to Sort & Filter \to Largest to Smallest on Amount — verify sort works. Filter Department to Science — expect two rows (~ £795 total), then clear filter.

Expenses
Expenses

Enter these rows, then convert A1:E9 to a table named tblExpenses.

Step 5 — Chart and PivotTable

On Budgets, select A1:E6 \to Insert \to Charts \to Clustered Column . Chart Design \to Move Chart \to new sheet BudgetChart . Title: Northgate Academy — Department budgets by quarter . On Expenses, Insert \to PivotTable from tblExpenses \to new sheet PivotSummary . Rows: Department . Values: Sum of Amount . Science total \approx £795 .

Step 6 — Print Setup

On Budgets : Page Layout \to Orientation \to Landscape . Select A1:K6 \to Page Layout \to Print Area \to Set Print Area . Page Setup \to Header/Footer \to centre header Northgate Academy — Department budget summary . File \to Print — preview fits one page width (adjust Scale to Fit if needed). Save NorthgateBudget.xlsx .

Final Self-Check

Check Pass? File name is NorthgateBudget.xlsx Budgets has 5 departments \times 4 quarters F2 = 50500 for English tblExpenses has filter arrows BudgetChart tab exists PivotSummary shows department totals Print preview shows only the budget block James Okonkwo can hand this file to Dr. Helen Marsh at Northgate Academy — budgets, spending summary, and a chart in one workbook. Work through the checklist top to bottom. If a check fails, jump back to the lesson that covers that skill rather than guessing formulas.

Check	Pass?
File name is NorthgateBudget.xlsx
Budgets has 5 departments × 4 quarters
F2 = 50500 for English
tblExpenses has filter arrows
BudgetChart tab exists
PivotSummary shows department totals
Print preview shows only the budget block

Practice

Swipe or use the arrows to move between questions.

1 / 3

Which sheet holds the quarterly department figures?

English annual total should be:

Which sheet summarises total spend by department from tblExpenses?