Alym LabsExcel for Accounting
← Back to the syllabus
Challenges

Apply what you’ve learned

Work through realistic accounting scenarios and put your Excel knowledge into practice. You’ll need to decide which functions to use—and, as the difficulty rises, how to combine them.

Each challenge has a difficulty score. Work your way from Excel newbie to Excel ninja as you build your skills, solve tougher problems, and hopefully have some fun along the way.

Difficulty 1 · 0 of 12 solved

Read the question

One function each, and nothing tells you which. The work is deciding whether the answer is a count or an amount, and which column holds it.

  1. 1Ledger totalPayables ageing — October.xlsx
  2. 2Overdue invoicesPayables ageing — October.xlsx
  3. 3Largest invoicePayables ageing — October.xlsx
  4. 4Total claimedExpense claims — October.xlsx
  5. 5Claims with no receiptExpense claims — October.xlsx
  6. 6Total budgetBudget versus actual — October.xlsx
  7. 7Closed accountsChart of accounts — extract.xlsx
  8. 8Journal differenceGL journal — October.xlsx
  9. 9Unreconciled differenceBank reconciliation — October.xlsx
  10. 10Oldest invoice dateReceivables ageing — October.xlsx
  11. 11Rows not postedSupplier import — raw.xlsx
  12. 12Typical invoicePayables ageing — October.xlsx
Difficulty 2 · 0 of 16 solved

Two moving parts

One column decides which rows count and another holds what gets measured. Most of these need two functions, or one function doing two jobs.

  1. 13Value overduePayables ageing — October.xlsx
  2. 14Value past 60 daysPayables ageing — October.xlsx
  3. 15Travel claimedExpense claims — October.xlsx
  4. 16Rory Vance, largest claimExpense claims — October.xlsx
  5. 17Operations actualBudget versus actual — October.xlsx
  6. 18Overall varianceBudget versus actual — October.xlsx
  7. 19Balance sheet accountsChart of accounts — extract.xlsx
  8. 20Posted to 6100GL journal — October.xlsx
  9. 21Charged after OctoberGL journal — October.xlsx
  10. 22Amounts that are textSupplier import — raw.xlsx
  11. 23Account code, row 2Supplier import — raw.xlsx
  12. 24Age of the oldest debtReceivables ageing — October.xlsx
  13. 25Owed by Calder UtilitiesReceivables ageing — October.xlsx
  14. 26On the bank, not the ledgerBank reconciliation — October.xlsx
  15. 27Travel with no receiptExpense claims — October.xlsx
  16. 28Name of account 4000Chart of accounts — extract.xlsx
Difficulty 3 · 0 of 20 solved

Put them together

No single function answers these. A figure has to be fetched before it can be compared, or worked out before it can be divided.

  1. 29Overdue and past 30 daysPayables ageing — October.xlsx
  2. 30Share past 90 daysPayables ageing — October.xlsx
  3. 31Row 2 against its termsPayables ageing — October.xlsx
  4. 32Weighted average daysPayables ageing — October.xlsx
  5. 33Row 2 past dueReceivables ageing — October.xlsx
  6. 34Days past due, row 4Receivables ageing — October.xlsx
  7. 35Invoiced before OctoberReceivables ageing — October.xlsx
  8. 36Outside the periodGL journal — October.xlsx
  9. 37Entries hitting payablesGL journal — October.xlsx
  10. 38Travel over its limitExpense claims — October.xlsx
  11. 39Largest claim, no receiptExpense claims — October.xlsx
  12. 40Operations over budgetBudget versus actual — October.xlsx
  13. 41Account 6100 varianceBudget versus actual — October.xlsx
  14. 42Overspend as a shareBudget versus actual — October.xlsx
  15. 43Active P and L accountsChart of accounts — extract.xlsx
  16. 44Statement for 2300Chart of accounts — extract.xlsx
  17. 45Clean name, row 2Supplier import — raw.xlsx
  18. 46Account name, row 2Supplier import — raw.xlsx
  19. 47Unmatched, both waysBank reconciliation — October.xlsx
  20. 48Does the break explain itselfBank reconciliation — October.xlsx
Difficulty 4 · 0 of 10 solved

Built to last

The same questions, asked so the answer survives next month's rows being pasted underneath and the reference table being edited.

  1. 49Missing receipts, room to growExpense claims — October.xlsx
  2. 50Completeness, room to growPayables ageing — October.xlsx
  3. 51Unposted rows, room to growSupplier import — raw.xlsx
  4. 52Rows carrying an amountPayables ageing — October.xlsx
  5. 53Oldest debt, past dueReceivables ageing — October.xlsx
  6. 54Worst old invoicePayables ageing — October.xlsx
  7. 55Biggest spender, namedBudget versus actual — October.xlsx
  8. 56Largest charge, describedGL journal — October.xlsx
  9. 57Active balance sheet accountsChart of accounts — extract.xlsx
  10. 58Code with a fallbackSupplier import — raw.xlsx
Difficulty 5 · 0 of 6 solved

Close the ledger

Reconciliations, cut-off tests and provisions. Several conditional totals in one formula, and an answer of nought that has to mean something.

  1. 59Payable within limitsExpense claims — October.xlsx
  2. 60Reconciliation signed offBank reconciliation — October.xlsx
  3. 61Does October alone balanceGL journal — October.xlsx
  4. 62Provision on the oldest debtPayables ageing — October.xlsx
  5. 63Invoiced in the monthReceivables ageing — October.xlsx
  6. 64Worst cost centreBudget versus actual — October.xlsx