Budget Control with Commitments: Purchase Orders, Encumbrances, Available Budget, Year-End Carry-Forwards & Fund Reports

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Set up Fernhill Institute's five budget lines, post the year's budget transfers with SUMIFS and compute the revised budget per line – the base for every later check.
• Read Fernhill's purchase-order register, add up the open commitments per budget line with SUMIFS while leaving out cancelled orders, and find the line whose budget is already most committed.
• Match supplier invoices to their purchase orders, compute the invoiced amount, the commitment released and the open balance of every PO, cap the balance at zero with MAX and mark the POs that are fully invoiced.
• Compute the available budget per line as revised budget − actual spend − open commitments, flag overcommitted lines, run a funds check that approves or holds new purchase requests and colour the names of the lines in the red.
• Compare invoices with their PO lines, compute the price difference, apply a fictional tolerance rule to decide «Pay» or «Hold», work out what stays committed for goods not yet delivered and how much spend never had a commitment.
• Project each line's year-end out-turn in two ways – a simple run rate and a commitment-based forecast – compare the result with the revised budget and count the lines heading for an overspend.
• Apply a fictional carry-forward policy to the POs still open at year-end, split them into carried-forward and released commitments, and compute each line's opening available budget for the new year.
• Finish Fernhill's quarterly budget report: actual spend to date from the month table, the share of each budget used including commitments, a red-amber-green status with IFS, the line that needs attention first and a chart of the last three months.