Financial model review checklist: find and fix common errors

Review a financial model by checking its dates and units, following the formulas behind the main outputs, and rebuilding the cash movement independently. Then change a few inputs whose effects you can calculate in advance. Start with the numbers you will act on: sales, operating profit, the lowest cash balance and the amount of funding required.

The checklist below applies to an operating forecast in Excel. The cell references are small teaching examples; adapt them to your file. Formulas use English function names and commas as separators.

Financial model review checklist

Work in a copy and keep the original inputs. Use this list to locate the first problem, then use the examples below to trace and repair it.

Check dates, units and the actual-to-forecast boundary

Suppose actual sales cover January through March and the forecast begins in March. Adding both sections counts March twice. Repair the boundary so the forecast starts in April, then compare the twelve monthly revenue cells with the annual total. Check the boundary separately in costs, working capital and debt schedules; correcting the revenue header does not repair another schedule's references.

Use real dates in the time axis. For example, =DATE(2026,4,1) creates 1 April 2026; a short entry such as 04/05/26 is ambiguous to a reader. Imported date text may also need conversion before date calculations work. Confirm the intended day, month and year before converting it. Microsoft explains how DATE constructs a date and how to convert dates stored as text.

Normalise units before comparing results. If a source shows costs of 80 in $ thousands, the comparable amount is $80,000. If annual rent is $120,000 and the lease is paid evenly each month, the monthly forecast input is $10,000. Putting $120,000 into every month overstates annual rent by $1,320,000. A lease with quarterly payments needs a different cash schedule even if the annual expense is the same.

Check rates in the formula bar. A stored value of 5% is 0.05; a stored value of 5 means 500% when formatted as a percentage. On $20,000 of sales, those values produce $1,000 and $100,000 respectively. Changing the display format alone does not turn the second input into the intended rate.

Repair a formula that points at the wrong input

Assume April sales are in B6, May sales in B7, and the commission rate is in E2. Sales are $20,000 in April and $25,000 in May; E2 is 5% and E3 is blank.

The sales reference should move to the next row; the shared rate should stay at E2. That is why the dollar signs belong on the rate reference. Microsoft documents how relative and absolute references behave when copied.

A hardcoded rate creates a different failure. =B6*5% produces the right April commission initially, but changing E2 to 6% does nothing. Replace the embedded assumption with =B6*$E$2. At 6%, April and May commission should become $1,200 and $1,500. Constants such as twelve months in a year can be intentional; concentrate on values that are supposed to change with the business assumptions.

For a result you cannot explain, select its cell and use Formulas → Trace Precedents to follow its inputs. In Excel for Windows, Evaluate Formula steps through a calculation. These tools help locate the wrong reference or branch; the business meaning still needs checking. Microsoft's instructions cover tracing cell relationships and investigating formula errors.

Find totals that silently omit a value

A total can be wrong without showing an Excel error. Suppose B2 contains 9,000, B3 contains the text '11000, and B4 contains 10,000. =SUM(B2:B4) returns 19,000 because SUM ignores text in referenced cells. The intended total is 30,000. Use =ISNUMBER(B3) to identify the nonnumeric input, confirm its meaning and convert it to the number 11,000. Recheck the total after conversion. See Microsoft's SUM documentation.

Also inspect the range itself. If monthly revenue occupies B10:M10, =SUM(B10:L10) omits December. Repair it to =SUM(B10:M10). For a simple annual summary, revenue and expenses are flows to add across months; December closing cash and closing debt are balances to carry into the year-end column. Adding twelve closing cash balances produces a number with no useful meaning as year-end cash.

Inspect error-handling formulas rather than accepting their zeroes. With Revenue and Units defined as named input cells, =IFERROR(Revenue/Units,0) returns zero when Units is zero, hiding an undefined average selling price. A clearer display for that case is =IF(Units=0,"No units",Revenue/Units), provided downstream formulas handle the text explicitly. If revenue is positive while units are zero, investigate the input relationship. IFERROR replaces a formula error with the supplied value; it does not correct the underlying calculation.

Reconcile profit to cash: a complete monthly example

A profitable month can still reduce the bank balance. Profit includes earned revenue that may not yet have been collected, while equipment purchases and loan principal use cash without being current operating expenses. The SEC's financial-statement guide explains the distinction between net income and cash flow, including the adjustment for noncash depreciation.

For this example, assume one month in $. Revenue is earned during the month; all operating costs are paid in the same month. There are no taxes, interest, inventory changes, payable changes or new financing. The only working-capital movement is uncollected customer revenue. Enter the inputs below in column B.

Rebuild the answer directly from bank movements: $20,000 opening cash + $18,000 receipts − $20,000 operating payments − $6,000 equipment − $3,000 principal = $9,000 closing cash. The cross-check =B13-(B2+B4-B5-B7-B8) should equal zero.

If the model uses opening cash plus profit, it shows $28,000 and overstates the available cash by $19,000. The repair is to account for collections and payments. In the profit-to-cash bridge, add back depreciation, subtract the receivables increase, and include equipment and principal payments once. Do not subtract depreciation again in the direct cash schedule, and do not deduct the equipment payment twice.

The result matters to a payment decision: a proposed $12,000 payment cannot be covered by the $9,000 closing balance alone. Check its actual due date and any other available funding. A month-end balance can still hide a shortfall earlier in the month. The 13-week cash forecast guide develops that payment calendar.

Change an input and calculate the expected response

Make one change at a time, then restore the base inputs. In Excel, check Formulas → Calculation Options; if calculation is manual, recalculate before judging the result. Microsoft describes automatic and manual recalculation. Do not enable iteration simply to make an unexplained circular reference disappear.

Check whether the output can support the decision

A corrected formula can still describe an impossible operating plan. If the model needs 600 jobs a month to cover its costs but staffed capacity is 500, a break-even output of 600 is a capacity problem to solve. Follow the break-even and capacity calculation before treating the threshold as an achievable sales target.

For a funding decision, use the lowest dated cash balance and distinguish committed funding from a proposed loan. For a pricing decision, inspect contribution after the relevant variable costs and the volume required to replace a price cut; the contribution-per-order guide shows that calculation. For a three-statement model, also check that assets equal liabilities plus equity and that closing debt equals opening debt plus borrowing less principal repaid, adjusted for any other identified debt movements.

Finish with the few outputs that drove the review. They should agree with their supporting schedules, use the intended dates and units, and respond correctly to the changes above. Where a missing input could change the decision, obtain it before treating the displayed result as an answer.