Bottoms up financial model: drivers, profit and cash

A bottoms up financial model, usually called a bottom-up financial model, builds profit and cash from operating drivers: units, realized prices, unit costs, staffing and payment timing. The test is whether those drivers reconcile to transaction files and whether the cash schedule explains the bank balance.

Start with rows the business can explain

Use monthly sales lines with product, quantity, net sales and cost; payroll by department; operating expenses by account; and opening receivables, inventory, payables and cash. Keep returns and credits in the same product mapping as sales. Give every assumption an owner, an effective month and a source.

This is a linked financial plan rather than only a spending allocation. A business budget layout can organize departments and months; the additional work here connects demand to product cost, staffing and working capital. Use one consistent sales roll-up hierarchy when matching the plan to sales exports.

Separate recurring overhead from launch or equipment spending. The SBA's startup-cost guidance distinguishes one-time expenses from monthly expenses. That distinction also helps an established company keep a temporary cash requirement out of its recurring operating run rate.

Build revenue and direct cost from drivers

Consider a US industrial distributor's monthly plan. The actual sales export shows the same total units but a different product mix and lower realized prices. All example amounts are USD; whole-dollar results are exact, with no rounding required.

Put these rows in a sheet named Drivers. The letters in the headings identify the spreadsheet columns; data occupy rows 2 through 5.

Enter these formulas, then fill down:

F2 = C2*D2
G2 = C2*E2
H2 = F2-G2

The labels before each equals sign identify the destination cells. Plan revenue is =SUM(F2:F3); actual revenue is =SUM(F4:F5). Calculate the totals from dollars rather than averaging product prices or gross profit margins.

The product-level revenue bridge separates two effects. Quantity and mix at planned prices contribute (900-1000)*80+(600-500)*120, or $4,000. Price changes on actual quantities contribute 900*(78-80)+600*(118-120), or negative $3,000. Together they explain the $1,000 revenue increase.

This order assigns the interaction between quantity and price to the price effect. Keep the same convention each month so the explanation remains comparable.

Add staffing and overhead without duplicating costs

The distributor plans four employees at a monthly loaded cost of $6,000 each. Actual loaded cost is $6,200 each. Loaded cost includes the payroll expenses covered by this simplified example; there is no production payroll inside the product-cost rows.

For a separate Summary sheet with these lines in rows 2 through 7, plan in B and actual in C:

B2 = SUM(Drivers!F2:F3)
C2 = SUM(Drivers!F4:F5)
B3 = SUM(Drivers!G2:G3)
C3 = SUM(Drivers!G4:G5)
B4 = B2-B3
C4 = C2-C3
B5 = 4*6000
C5 = 4*6200
B7 = B4-B5-B6
C7 = C4-C5-C6

Enter the other-expense inputs in B6 and C6. Profit falls by $4,400: revenue improves by $1,000, product cost rises by $3,600, payroll rises by $800 and other expenses rise by $1,000. Assign each movement to its operating owner before revising the forecast.

Connect profit to receipts, purchases and cash

The SBA's finance guidance identifies receivables, payables, available cash and bank reconciliation as essential finance responsibilities. Those are the balances that turn an operating plan into a cash plan.

Assume all sales are on credit, all product purchases pass through payables, and payroll and other expenses are paid in the month incurred. There are no write-offs, taxes, depreciation, interest or financing movements. The equipment payment below has no expense in this simplified period. Real workbooks need separate schedules for omitted items.

Opening operating working capital is 30000+25000-20000, or $35,000. Planned closing working capital is 35000+28000-21000, or $42,000; actual is 40000+30000-22000, or $48,000.

The independent cash check therefore gives 44000-(42000-35000)=37000 for plan and 39600-(48000-35000)=26600 for actual operating cash. Both equal the direct cash schedule. Closing cash misses plan by $10,400: the $4,400 profit shortfall plus $6,000 of additional working capital.

Where it goes wrong

• Growth percentages overwrite drivers. Increasing total revenue without changing quantities or prices breaks the connection to operating activity.
• Staffing starts too early. A signed offer is different from a full month of payroll. Phase each position by its actual start date.
• Inventory purchases equal product cost. Product cost follows sales; purchases also fund inventory movements. Treating them as equal hides cash needs.
• Actual prices use gross invoices. Discounts and credit memos change realized prices. Reconcile net sales to the ledger before explaining price movements.
• Cash inputs conceal a missing schedule. A manually entered closing cash balance can make the workbook appear balanced. Calculate it and compare it with bank reconciliation instead.

Compare your drivers and actual results in Covirage

Bring your prepared driver workbook and matching sales, expense and balance exports. Covirage maps the columns; its tools compute and check the supplied figures, and the external AI model explains the result with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data; account creation takes about a minute.

See Covirage for finance teams for how this work fits the monthly planning review.