15.4 Excel refresher

Excel is effective for small-to-medium auditable models, assumption tables, data dictionaries, review logs, and outputs used by non-programming audiences. It becomes fragile when transformations depend on hidden manual steps or formulas are copied inconsistently.

15.4.1 Separate inputs, calculations, and outputs

Use clearly labelled sections or sheets. Inputs should be typed values with units and sources. Calculations should reference inputs rather than embed assumptions. Outputs should present the results needed for review.

15.4.2 References

If a formula copied down should always use the discount rate in cell B3, use an absolute reference such as $B$3. If the year should change by row, use a relative reference such as A8.

15.4.3 Tables, lookups, and pivots

Excel tables create structured ranges that expand consistently. Use lookups with stable keys and inspect unmatched values. Pivot tables are useful for rapid summaries, but their source range, refresh state, filters, and calculated fields must be documented.

15.4.4 Formula auditing

Check for inconsistent formulas, hard-coded values, omitted rows, hidden cells, text-formatted numbers, and error suppression. Use reasonableness tests and reconcile totals with the source.

Worked Example: Building an Auditable CLV Workbook

NVRW’s workbook contains an assumptions sheet for revenue, margin, retention, discount rate, horizon, and CAC. A calculation sheet shows yearly survival probability, margin, discount factor, present value, and cumulative CLV. A summary sheet compares scenarios. Changing one assumption updates the complete model without editing formulas.