Excel for Accountants: Reconciliations, Ledgers & Month-End

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.

• Turn a raw general-ledger export into a trial balance with SUMIFS, test whether debits equal credits and track down the line that breaks it.
• Map 21 ledger accounts to their balance sheet and income statement lines with an XLOOKUP mapping table, summarise the TB by line and prove that the statements balance.
• Match the cash book against the bank statement line by line, let Excel list the unmatched items and reconcile both balances to the cent.
• Age Quillmark's open invoices: days past due, a bucket label per invoice with IFS, bucket totals with SUMIFS and a column chart for the credit-control meeting.
• Build a release schedule that spreads four prepayments over their months, track the prepaid balance, calculate a utilities accrual and post the September entries.
• Complete Quillmark's fixed asset register (annual straight-line charge, accumulated depreciation capped at the depreciable amount, net book value) and build a declining-balance schedule for the van.
• Turn September's budget vs actual into a management-ready variance report: variance %, a favourable/unfavourable label that knows revenue from cost, a two-condition materiality flag and red highlights.
• Turn the close checklist into due dates with WORKDAY, measure progress, count overdue tasks and test the intercompany balances before they are eliminated.