Excel macros or a workflow system for daily branch reports

Each branch emails a workbook. A coordinator saves the files into a shared network folder, opens the master file and clicks once, which starts a macro that copies a named range from every workbook. The summary sheet builds itself.

Named ranges, a hidden lookup tab and conditional formatting put sales, footfall, cash banked and stock-outs in the same cells every day. One person knows the template well.

Cost is the strength: a good macro takes one day to write, needs no new licence and runs on the Excel the branches already have installed. Nothing else is needed.

Late branches come first. At six the macro runs with nine of twelve files, and the summary quietly shows nine branches without a warning that three workbooks never arrived. Someone has to notice which three are missing and rerun the macro later, usually after the area manager has phoned head office.

Then the template drifts. A manager inserts a row for a new category, another changes a formula to match a local discount, and the copied range now points at the wrong cell. The total looks plausible but is wrong.

Branch 7 works from last quarter’s file saved on a desktop, and nobody knows until a figure looks odd to the finance team. When the macro author leaves, the VBA module becomes a black box that nobody dares open, and every new branch then needs a rewrite nobody can review.

Branches stop sending files. Each manager opens a form on a phone or browser, and validation rejects a negative cash figure or a missing field before anything is saved. The locked fields cover opening cash, sales by category, bills raised, walk-ins and stock-outs, identical for every branch, so no manager can rename a column.

The system knows who owes a report. At a set time it messages the late branches and escalates to the area manager, while head office sees live status of submitted, late and not yet started.

The summary builds itself from stored records, so each figure has one version, and every correction goes through a logged edit showing who changed what and when. A pharmacy chain, for instance, can see one outlet’s shift-wise cash and expiry write-offs without opening a single attachment or asking the branch manager to resend.

Start from the daily summary itself. Look at the page, not the tool. Ask which numbers the director reads first, and which exceptions earn a phone call to a branch manager, then build the comparison around those instead of features.

Score each option on the same questions. Does a late branch show up quickly? Can a formula change by accident? A macro usually scores well on cost and poorly on how many versions exist at once and how soon a missing file is noticed.

Access control often tips the choice, because area managers should see their own branches, finance should see all, and a branch should never see a rival branch’s margin. Test access rules early.

With four branches, one coordinator and a stable template, keep the macro, lock the sheet, count the files received and store the code in a versioned repository.

With fifteen branches, quarterly new fields or daily chasing, move to a workflow system with the same fields and the same summary layout. Head office notices only punctuality.

Pilot with three branches for two weeks, running the old file beside the new form and comparing totals each evening until the figures match to the last unit. Petty cash and returns must match too.