Syntha Lab RU

Financial model (FinModel): sheets, links, checks, scenarios

A single spreadsheet or program in which assumptions are linked to the P&L, cash flow and balance sheet: change the exchange rate or demand and get recalculated profit, cash and covenants. It supports decisions on which scenario to adopt, which purchase to confirm and how much credit to request.

Finance Brand and retail Verified: 2026-11-13

Open interactively →

Why this document

When reports live in separate files, every change must be recalculated by hand and three places show three different numbers. A model keeps all assumptions in one place and shows the consequences at once in profit, cash and balance. The finance director tests decisions before making them, the owner compares scenarios, and the bank and investor get figures they can check. That is why checks are built in: the balance must tie and cash must not go negative. Without a model the budget, business plan and valuation drift apart.

At a glance

What is this document and why is it needed?
A single spreadsheet or program in which assumptions are linked to the P&L, cash flow and balance sheet: change the exchange rate or demand and get recalculated profit, cash and covenants. It supports decisions on which scenario to adopt, which purchase to confirm and how much credit to request.
Who owns the document and how often is it rebuilt?
The finance director or a modeling analyst; updated monthly with actuals and quarterly with assumptions; versions are stored with date and author; the owner receives the summary sheet and scenarios.
What mistakes are the most common?
Numbers hard-coded in formulas → nobody knows where they come from → assumptions only on a separate sheet. The balance sheet is plugged with a balancing line → errors hidden → forbid plugs and find the cause of the gap. No checks sheet → the model is released with an error → block release on non-zero checks.

For whom

Brand
A brand model is built from the collection: sheets by channel (wholesale, site, marketplace), production and purchases (quantity × cost × FX), stock and reserves, then the P&L, cash flow and balance sheet. The key difference is a long cycle and currency dependence. In Model B FX +10% cuts EBITDA by RUB 5.628 million (import share of cost 60%), and the in-year minimum cash by 4,448.
Retail
A retailer model is built from stores and categories: traffic, conversion, basket, price reductions, then purchases via OTB and IMU, rent and staff by outlet, openings and closings. FX acts via the purchase price: in Model R +10% cuts EBITDA by 13,621 (import share 50%). A specific: the model must show stores separately, otherwise a closing decision cannot be taken.

What it consists of

Model sheets

  • 1. Assumptions (Inputs): all source figures in one place: prices, volumes, FX, rates, terms, shares. Numbers are typed in only here; cells are colored so an input differs from a formula.
  • Retailer: 2. Sales: by channel, category, season, store; drivers and totals.
  • 3. Purchases and inventory: demand, OTB, orders, closing stock, reserve.
  • 4. Expenses and staff: variable and fixed, payroll, rent, marketing.
  • 5. P&L, 6. Cash flow, 7. Balance sheet: the three main statements, automatically linked.
  • 8. Debt and investment: loan schedule, interest, CAPEX, depreciation.
  • 9. Scenarios and sensitivity: a scenario switch, a results table, a tornado chart.
  • 10. Checks and summary: control sums; the owner page.

Links between sheets

  • Sales → P&L revenue → cash flow receipts → balance sheet receivables. Purchases → balance sheet stock → cash flow supplier payments. Profit → equity; depreciation and CAPEX → fixed assets; loan → P&L interest → balance sheet debt.
  • Identities: cash flow cash equals balance sheet cash; equity growth equals net profit less dividends; direct and indirect cash flow give the same operating flow.

Checks and quality

  • A “Checks” sheet with an error flag: the balance sheet ties, channel sums equal the total, quarters equal the year, cash flow methods are equal. Any non-zero value is colored and blocks release.
  • Versions: a file name with date and author, a changelog of assumptions, a frozen plan version for plan versus actual.
  • Do not hard-code numbers in formulas; avoid links to other files; do not paste values in place of formulas.

Scenarios and FX sensitivity

  • A scenario switch on the assumptions sheet changes FX, demand, rate, commission; results by scenario are collected in a table.
  • FX sensitivity: share of imported components in cost × FX change = cost change; compute separately for materials, sewing and freight, and mind the lag: a new rate enters batch cost with a shift by stock.

Brand and retailer: what is computed differently

  • Brand: the brand model is built from the collection and channels: wholesale, site and marketplace sheets, production and purchasing in foreign currency, the FX lag through stock, the factory prepayment as a separate line.
  • Retailer: the retailer model is built from stores and categories: traffic, conversion, check, markdowns, OTB and IMU, store openings and closings, rent with indexation.

Brand: how the document differs and what to add

  • Brand: the brand model is run by collection season rather than calendar month: the purchase and sale of one collection lie in different periods.
  • Brand: the sales sheet is split into channels with their own prices, returns, commissions and payment terms (wholesale, site, marketplace).
  • Brand: the cost sheet holds factory price, FX rate, freight and duty; FX sensitivity is set by an import share of 60%.
  • Brand: the inventory sheet computes DIO by season and the reserve by collection age: last season’s stock is not mixed with the current one.
  • Brand: the working-capital sheet holds the factory advance, the wholesale deferral and the marketplace payout term: the main levers of brand cash.
  • Brand: the financing sheet tracks loans against stock and receivables, the collateral base and covenants: the model warns of a breach before the bank report.
  • Brand: the collection development sheet collects sample and showroom costs that are expensed before season revenue.
  • Brand: brand scenarios include a factory delay, a fall in wholesale preorders and a platform commission rise; a trigger is written for each.

Retailer: how the document differs and what to add

  • Retailer: the retailer model is built by store: each store is a row with space, traffic, conversion, ticket, rent and staff.
  • Retailer: the sales sheet separates comparable stores, new points with a ramp curve and online with its return share.
  • Retailer: the inventory sheet runs OTB by category and season, markdowns by age and shrink by stocktakes.
  • Retailer: the lease sheet stores all contracts with indexation, turnover rent and terms; under FSBU 25/2018 the right of use and the liability are computed from it.
  • Retailer: the staff sheet computes shifts, hours and the variable part from traffic; store payroll depends on the schedule, not just headcount.
  • Retailer: the loyalty sheet tracks accruals, redemptions, breakage and the liability; its figure goes straight into the balance sheet.
  • Retailer: the capex sheet collects openings and refurbishments project by project and ties depreciation to the lease term.
  • Retailer: retailer scenarios include a traffic fall, rent indexation, a delivery delay and growth of online returns; a trigger is written for each.

How to read the result and which decisions follow

  • The model failed at least one check (balance, cash, flows): it is not used for decisions; first find the error source, then recompute scenarios.
  • The sensitivity of one assumption exceeds all others together: it needs its own trigger and owner while the others are kept under monitoring.
  • The model shows cash growing when demand falls: check whether DIO is held constant; with a fixed purchase stock should grow and cash fall.

Control checks

  • The balance sheet agrees in the base and in every scenario: brand 116,326 = 116,326; retailer 176,341 = 176,341.
  • Assumption cells are separated from formulas by color and protection; formulas contain no hand-typed numbers instead of references.
  • Typical traps: a circular reference through interest, a scenario that does not change every sheet, a model version without a date and author, a total that disagrees after a sheet is copied.

Where the data comes from

Data sourceFields
Accounting data for 2 to 3 yearsP&L, cash flow, balance sheet by month, sales and stock by SKU
Functional planssales plan, OTB, headcount plan, CAPEX plan, contracts
Macro and market assumptionsFX, inflation, rates, indexation; the source and date of each figure
Bank agreements and covenant termsrate, schedule, threshold values

How to build it step by step

  1. Step 1: Define the purpose of the model (budget, investment, valuation, bank talks) and the horizon; this sets the detail.
  2. Step 2: Draw the sheet and link diagram on one page before building.
  3. Step 3: Build the assumptions sheet: a source, date and owner for every number.
  4. Step 4: Build sales and purchases from drivers; backtest on last year.
  5. Step 5: Build expenses and the P&L; compare with last year on key ratios (margin, expenses to revenue).
  6. Step 6: Build the balance sheet and cash flow: first working capital by turnover days, then cash flow; make the balance sheet tie.
  7. Step 7: Add a checks sheet; do not release the model until all checks are zero.
  8. Step 8: Add scenarios and sensitivity (FX ±10%, demand ±10%, rate, commission) and a summary table.
  9. Step 9: Hand the model to an independent review (a second person): formulas, references, units, signs.
  10. Step 10: Set a regulation: who updates, when, how a version is recorded; freeze the plan copy.

Formulas

MeasureFormulasNote
Working capital by daysReceivables = Revenue incl. VAT × DSO ÷ 365; Stock = COGS × DIO ÷ 365; Payables = Purchases incl. VAT × DPO ÷ 365For a seasonal business use quarterly or monthly days, not the annual average.
EquityEquity_closing = Equity_opening + Net profit − DividendsAn identity; a break means an error in one of the statements.
FX sensitivityΔCost = Cost × Import share × ΔFXAdd the stock lag: today batch FX affects COGS through DIO.
Interest coverICR = EBITDA ÷ InterestMonitor when rates rise; banks set a threshold.
Depreciation and the loan scheduleDepreciation_t = (Cost − Residual value) ÷ Life; Debt_closing = Debt_opening − Repayment + Drawdown; Interest = Rate × (Debt_opening + Debt_closing) ÷ 2Interest on average debt without a revolving line: there is no circular reference because drawdowns and repayments are set by a schedule. If the model has an automatic revolver, interest depends on cash and cash on interest: switch iterations on or compute interest on the opening debt and mark the assumption.
Scenario switchActive assumption = CHOOSE(scenario no.; base; scenario 1; scenario 2; …)Example: scenario 2 changes only three cells (volume × 0.9, FX × 1.15, purchasing held at the base level); results of all scenarios are pasted into a table as values and dated. Stock does not shrink by itself when demand falls: fix the purchase, otherwise cash comes out better than the base.

Formulas in notation

Balance check
Chk = A − L − E, |Chk| < ε
A is assets, L is liabilities, E is equity; the model is accepted only if the check is zero in every period and scenario.
Cash identity
Cash_1 − Cash_0 = CFO + CFI + CFF
The change in cash on the balance sheet equals the sum of the three cash-flow sections.
Sensitivity
S_X = ΔNP / ΔX
The change in net profit per unit change of an assumption; it allows comparing FX, demand and rate on one scale.
Cash-flow control
CFO_direct − CFO_indirect = VAT_capex
The difference between direct and indirect methods equals VAT on capex; a non-zero remainder means a missing line.

Worked example

Integrity checks of the end-to-end model and FX sensitivity, RUB thousand

Check or metricBrand (Model B)Retailer (Model R)
INTEGRITY CHECKS (all must be 0)
Opening balance: assets − liabilities − equity00
Closing balance: assets − liabilities − equity00
Direct − indirect cash flow00
Cash: opening + cash flow − balance sheet cash00
Sum of quarters − year (cash)00
Sum of channels − P&L revenue00
Profit − (equity growth + dividends)00
FX SENSITIVITY (import share of cost 60% and 50%)
FX +10%: change in EBITDA−5,628−13,621
FX −10%: change in EBITDA5,62713,622
FX +10%: change in the in-year minimum cash−4,448−10,440
FX +10%: change in closing cash−8,059−13,920

All integrity checks are zero: the opening and closing balance sheets tie, the direct and indirect cash flow agree, cash and equity are linked to profit. This is what the “Checks” sheet of a working model looks like. In this simplified model cost depends linearly on FX, so the +10% and −10% effects are mirror images; in real life it is asymmetric because of stock lags and price decisions, so add a lag and a price reaction. The import shares of 60% and 50% are assumptions of the example.

Figures

Model sheets and links between them (brand, Model B). Assumptions feed three statements, the statements check each other; checks show an error before a reader sees it.

  1. Assumptions and scenario → P&L: profit 21,051; Working capital and capex
  2. P&L: profit 21,051 → Cash flow: cash 14,351; Balance sheet: assets 116,326
  3. Working capital and capex → Cash flow: cash 14,351; Balance sheet: assets 116,326
  4. Cash flow: cash 14,351 → Balance sheet: assets 116,326; Metrics, covenants, checks
  5. Balance sheet: assets 116,326 → Metrics, covenants, checks
  6. Metrics, covenants, checks
EBITDA by model scenario
Bar chart: Base case 35,048, Demand −10% in all channels 26,017, FX rate +15% (import share of cost 60% and 50%) 26,607, Loan rate +5 pp 35,048, Brand: platform commission +3 pp; retailer: rent +10% 34,071, Demand −10% and FX +15% together 18,621−10,000010,00020,00030,00040,000Base caseBase caseBrand: 35,04835,048Retailer: 34,80034,800Demand −10% in all channelsDemand −10% in allchannelsBrand: 26,01726,017Retailer: 12,51112,511FX rate +15% (import share of cost 60% and 50%)FX rate +15% (importshare of cost 60%…Brand: 26,60726,607Retailer: 14,36814,368Loan rate +5 ppLoan rate +5 ppBrand: 35,04835,048Retailer: 34,80034,800Brand: platform commission +3 pp; retailer: rent +10%Brand: platformcommission +3 pp;…Brand: 34,07134,071Retailer: 30,56630,566Demand −10% and FX +15% togetherDemand −10% and FX+15% togetherBrand: 18,62118,621Retailer: −5,501−5,501RUB thousand
  • Brand
  • Retailer
Data as a table
MeasureBrandRetailer
Base case35,04834,800
Demand −10% in all channels26,01712,511
FX rate +15% (import share of cost 60% and 50%)26,60714,368
Loan rate +5 pp35,04834,800
Brand: platform commission +3 pp; retailer: rent +10%34,07130,566
Demand −10% and FX +15% together18,621−5,501

EBITDA by model scenario. A scenario switch in one model: the brand and the retailer react to the same scenario differently.

Specifics by category

Apparel
Apparel: sheets by season and collection; assumptions on size curve and returns; two seasons plus capsules.
Footwear
Footwear: add last, size run and minimum batch as purchase drivers; production is longer, so the model horizon is at least 18 months.
Accessories
Accessories: FX sensitivity through leather, hardware, metals; the model is simpler by size but more complex by SKU and materials.
Kids
Kids: drivers by age group and the school calendar; certification and labeling as a separate cost block.
Men
Menswear: a steadier model, scenarios change structure less; milder seasonality.
Women
Womenswear: more scenarios on fashion and discount depth; drivers by share of new and carryover items.

Common mistakes

  • Numbers hard-coded in formulas → nobody knows where they come from → assumptions only on a separate sheet.
  • The balance sheet is plugged with a balancing line → errors hidden → forbid plugs and find the cause of the gap.
  • No checks sheet → the model is released with an error → block release on non-zero checks.
  • One version edited by everyone → what changed cannot be restored → versions with date and author, an assumptions log.
  • An annual model without quarterly split → the gap is invisible → model by months or quarters.
  • FX as one number for the year → risk underestimated → FX scenarios and a stock lag.

Russian specifics

  • The model uses VAT of 22% from 2026 (Law 425-FZ); for preferential goods and regimes (USN) rates and procedures differ: put them in assumptions. Rule: Law 425-FZ of 28.11.2025: Federal Law of 28.11.2025 No. 425-FZ (amendments to parts one and two of the Tax Code; VAT 22%) (rule check not completed).
  • A bank and the tax office need statutory data: the model must link to the accounting system and have a mapping table of lines (management and statutory). Rule: Order 157n of 04.10.2023: Ministry of Finance Order of 04.10.2023 No. 157n (FSBU 4/2023 Financial statements) (rule check not completed).
  • Import currency settlements go through currency control; include payment terms and the bank fee in the model. Rule: Law 173-FZ of 10.12.2003: Federal Law of 10.12.2003 No. 173-FZ on currency regulation and control (rule check not completed).

Open interactively →