Financial model review

Units, signs and periods in financial models

Catch unit, sign and timing mistakes in Excel financial forecasts. Work through examples of currency scales, cash-flow signs, annual rates and period alignment.

Financial report cards beside calendar tiles, measuring objects and contrasting direction arrows

Some financial-model mistakes produce a normal number instead of an Excel error. A calculation can multiply the wrong units, reverse a cash outflow or combine a yearly figure with a monthly balance. To review it, ask three questions: what does each input measure, which direction does it represent, and what period does it cover?

The examples below are synthetic and deliberately small. They explain arithmetic and review techniques, not recommended financial assumptions or accounting policies.

Euros and thousands of euros are different inputs

Suppose a sales report records annual revenue as 120, labelled EUR thousands. A cost schedule contains 90000, labelled EUR. Subtracting the raw values gives 120 - 90000 = -89880, which mixes scales and has no useful financial meaning.

Convert both inputs to the same scale before subtracting. In euros, the intended calculation is:

=120*1000-90000

That gives EUR 30,000. In thousands of euros, =120-90000/1000 gives 30, representing the same amount. Decide where conversion belongs and label it so that a later formula does not convert the value twice.

Also distinguish display from stored value. A cell showing a rounded number or a currency symbol does not establish the underlying scale or currency. Microsoft's number-format guide explains that formatting changes how a value is displayed without changing the number used in calculations.

Inspect the formula bar and source labels. Write a simple unit description beside important rows: EUR, EUR thousands, units sold, EUR per unit, or percentage per month. Different currencies need a stated conversion rate and date, not merely matching currency symbols.

Separate expense inputs from signed cash movements

Models often store an expense as a positive input and subtract it in the calculation. Other models store outflows as negative movements and add them. Either convention can work when consistently defined.

For an invented month, opening cash is EUR 10,000, customer receipts are EUR 4,000 and supplier payments are EUR 1,500. With a positive payment input:

=10000+4000-1500

Closing cash is EUR 12,500. With supplier payments already stored as -1500, the matching calculation is =10000+4000+(-1500), also EUR 12,500.

Subtracting the negative movement gives =10000+4000-(-1500), or EUR 15,500. That incorrectly increases cash by the payment amount. Review the sign at the source and again where it enters the total.

A useful test in a separate copy is to increase the payment from EUR 1,500 to EUR 2,000 while holding other inputs unchanged. Closing cash should decrease by EUR 500 in this simplified example. If it rises, follow the subtraction and conversion steps. More complex models may include linked financing or tax effects, so establish the expected relationships before interpreting a change.

Name a rate's period and compounding convention

A cell labelled only 12% is incomplete information. Is it a monthly growth rate, an annual effective rate, or a nominal annual interest rate with monthly compounding?

For an invented 12% effective annual rate spread through equal monthly compounding, the equivalent monthly rate is:

=(1+12%)^(1/12)-1

It is approximately 0.948879%. Compounding the unrounded monthly rate for twelve months returns 12%. Dividing 12% by 12 instead gives 1% per month, whose compounded annual rate is approximately 12.682503%.

Division by 12 does fit a 12% nominal annual rate compounded monthly: its periodic rate is 1%. Excel's =EFFECT(12%,12) returns the corresponding effective annual rate. Microsoft's EFFECT documentation defines the function's nominal-rate and compounding-period inputs.

These relationships assume equal periods and the stated compounding structure. A simple annual budget allocation of EUR 12,000 into twelve equal EUR 1,000 months is a different operation. Contract terms, day-count conventions and seasonal activity may require other treatment. Do not apply a rate conversion just because the forecast has monthly columns.

Match a balance to the activity period

Revenue measures activity over a period; receivables measure an amount outstanding at a date. Adding twelve month-end receivables balances does not produce annual receivables: it counts successive snapshots.

Consider opening receivables of EUR 20,000 and closing receivables of EUR 40,000. Their simple endpoint average is EUR 30,000. Against annual credit sales of EUR 120,000, a simplified 365-day collection-days illustration is:

=30000/120000*365

The result is 91.25 days. Combining the same balance with one month's EUR 10,000 sales but still multiplying by 365 gives 1,095 days. The numerator, sales period and day multiplier no longer describe the same calculation.

An endpoint average can misrepresent a seasonal business. More frequent balances may be needed, and definitions must match: credit sales, currency, scope and any included taxes matter. Define the metric before choosing the cells.

Period labels need real dates underneath them. The date and timeline review works through month ends, leap years and a receipt shifted into the wrong cash-flow period, with checks to perform in a separate review copy.

Review labels before interpreting the score

  1. Record the unit, currency and scale of each important input.
  2. State whether expense rows are positive amounts or negative movements.
  3. Label every rate's period and compounding convention.
  4. Align forecast headings, input periods and aggregation formulas.
  5. Distinguish balances at a date from activity across a period.
  6. Test one simple change in a copy and explain the resulting movement.

FinModelCheck does not automatically detect unit, sign or period logic, formula inconsistency, or financial-statement reconciliation. It inspects supported saved Excel errors, broken references, external workbook links and embedded numeric coefficients without recalculating Excel. A high provisional score does not establish financial correctness.

Try the checker for those supported checks. The detailed PDF is free in the current valid owning browser session without registration; see the example report. For the next manual review step, read how to check forecast formulas and our common financial model errors guide.

Keep reviewing

Continue with a related guide.

Browse all guides