Mdingi Building Supply

The close is an Excel macro workbook, since Excel is where analysts close months. The month is set on the Control Centre sheet and six steps run in order: import and validate, reconcile the bank, run the controls, review the exceptions, produce the management pack, close the month.

Below the steps the sheet records the month's state: checks passed, items still open, the preparer and the reviewer.

The month's extract is validated first. Each table's row count and control total must match its manifest. The trial balance must balance for the month and to date, every account must be in the chart, and the bank's running balance must hold.

Book and bank are then matched by reference, so the difference is a list of named items rather than a balancing figure. In February, 26 items explain the whole R1.10m. Three are still open seven bank days later, and they become exceptions.

The controls look for duplicate supplier invoices and payments, invoices billed over the goods received, supplier VAT that does not recompute, unverified changes to a supplier's bank details, risky manual journals, manual entries to control, bank, fixed asset and revenue accounts, postings after a month has closed, and subledgers that disagree with the ledger.

February raises ten exceptions. One is a supplier invoice paid twice. Another is a R240,000 journal to sales labelled only 'Adjustment': it trips three checks and puts the debtors ledger out by the same amount. The rest are items carried from earlier months, each shown with last month's conclusion beside it.

An exception counts as reviewed only with a decision and a written reason. Each decision is stamped with who made it and when.

The management pack is formulas over the trial balance and nothing else. It shows the month and the year to date against budget, each branch, the balance sheet, and debtor, stock and creditor days.

Because it has no macros, the pack works with macros switched off. A test recalculates it outside Excel with pycel and compares it with Python.

Close month is refused while any exception is open, before the previous month has closed, if the bank reconciliation leaves anything unexplained, or if the pack is older than the last import. The first run records the preparer's sign-off, and a second run by the same person is refused, so a different reviewer has to close the month. A closed month cannot be re-run.

The workbook's checks are written in VBA, separately from the Python ones. A self-test inside Excel compares the two across all 24 months: the same 241 exceptions, none missing and none extra.

The board's scenario is sales down 15%, stock suppliers paid in 30 days and stock 8% dearer. Scaling last year's cash lines by a percentage does not capture this, because the timing depends on what each customer owes and how late they usually pay, which Friday payment run each supplier invoice falls into, and when salaries, SARS and finance costs go out. The forecast is therefore recomputed from the books for each scenario.

In that scenario the bank is R1.0m overdrawn in the week of 20 April, with R9.0m of the facility unused. Customers paying 20 days later has a larger effect than the three changes combined. On a company it was not built on, the forecast error was R0.8m one week ahead, R1.1m four weeks ahead and R3.3m at week thirteen.