Recalculation drift and macro prompts in a distributed capital-planning model
Priya maintains a 38 MB capital-planning model for a mid-size lender: 24 worksheets and roughly 40,000 formulas, of which about 6,000 are volatile OFFSET and INDIRECT calls, so a full recalculation takes nearly 90 seconds. While building it she set calculation to Manual and saved the file that way. The model now goes to 14 reviewers on mixed Excel builds. Two of them report total funding requirements that differ from Priya's figures and from each other's. On three machines a yellow security bar blocks the workbook's four macros, while on the other eleven the macros run with no prompt at all. Two reviewers have already deleted and re-created working sheets "to tidy up", breaking three cross-sheet references. Priya must make results reproducible, make macro behaviour predictable, and stop structural changes while still letting reviewers enter assumptions, sort the deal list and drive two slicers. She has no administrator rights and cannot change Group Policy.
1. Two reviewers see totals that disagree with Priya's and with each other's. Diagnose the cause and set out the fix and the working practice reviewers should follow.
Read the worked answer
Diagnosis. Calculation mode is a workbook-level setting that travels inside the file. Because Priya saved while Manual was selected, the file opens in Manual for everyone, whatever the recipients' own defaults are. Each reviewer therefore reads whatever values were last calculated on their own machine, which is why the totals disagree with each other and with Priya's.
Fix, in order:
- Set File > Options > Formulas > Workbook calculation to Automatic, press F9, then save. Every future open now recalculates.
- If 90 seconds is genuinely unacceptable, keep Manual but change the workflow rather than the setting: reviewers must press Ctrl+Alt+F9 (full recalculation of all dependencies in all open workbooks) before reading any total, and never trust a figure immediately after entering data. Shift+F9 recalculates only the active sheet and is not enough for cross-sheet dependencies.
- Attack the root cost: replace OFFSET and INDIRECT with INDEX or structured table references. Both are non-volatile, so recalculation time drops sharply without changing a single business rule.
Why it matters: a manual-mode file is a silent source of wrong decisions, and the disagreement between reviewers is evidence that people are already quoting stale numbers.
2. Three machines show a security bar and eleven do not. Explain what determines this, what Priya can ship to fix it durably, and what she must not ask people to do.
Read the worked answer
Why the split. Macro trust is decided by the receiving machine, not by the workbook, so Priya cannot set it inside the file. The eleven silent machines most likely have the workbook's folder in Trust Center > Trusted Locations, or have "Enable all macros" selected. The three blocked machines are on the default "Disable all macros with notification", which raises the yellow bar. Note too that a file arriving by email carries the Mark-of-the-Web and opens in Protected View before macros are even considered.
Two durable fixes she can ship:
- Sign the VBA project with a code-signing certificate (Developer > Visual Basic > Tools > Digital Signature). Reviewers trust that publisher once and are never prompted again, while the protection stays intact.
- Distribute from a trusted location - a controlled network share added to Trusted Locations - and tell reviewers to open it from there rather than from an email attachment.
What not to do: never instruct users to enable all macros globally or to click through the bar without thinking; that trades a prompt for a real risk. Do not rely on Mark as Final either, since it is only a flag and reviewers can edit anyway. Also confirm the file is saved as .xlsm, or the code is silently discarded on save.
3. Reviewers must still type assumptions, sort the deal list and click two slicers, but must not add, delete, rename or hide sheets. Which protection features, applied how?
Read the worked answer
Two separate protections, because they do different jobs.
Protect Workbook > Structure (Review tab). This blocks inserting, deleting, renaming, moving, hiding and unhiding sheets, which is exactly the damage the two reviewers caused. It does not restrict cell editing, so it is the right outer layer. Add a password, but understand it deters rather than encrypts; use File > Info > Protect Workbook > Encrypt with Password if confidentiality matters.
Protect Sheet on each assumption sheet, keeping the intended input cells editable. Select the input cells, Ctrl+1 > Protection, clear Locked, then protect the sheet with a password. In the Protect Sheet dialog tick Sort, Use AutoFilter, Use PivotTable and PivotChart, and crucially Edit objects - slicers are objects, so leaving them protected makes the slicers unclickable even though they look normal.
Sequence matters: unlock cells first, protect the sheet second. If Locked was set after protection, nothing is editable and the symptom looks like a broken model rather than a permissions problem.
Wrong tools for this job: Mark as Final, and the read-only recommended option, both of which a reviewer can dismiss in one click.