Common formulas: cash and balance sheet
Formulas that carry a cash or balance-sheet balance from one period to the next.
A balance carries forward. Each period starts from the balance of the period before and adds the movement of this period. Cash, retained earnings, and receivables all work this way.
The formulas on this page come from the Three Statement Model, Balance Sheet & Cashflow, and P&L and Runway templates. To start from a template, go to Scenario planning → New model, type the template name, and press Enter.
Cash from net income
The Three Statement Model builds the P&L, then rolls cash forward from net income.
| Variable | Formula |
|---|---|
| Gross profit | Revenue - COGS |
| Net income | Gross profit - Operating expenses |
| Cash | Cash[previous] + Net income |
In the first period, Cash[previous] is blank. A blank counts as zero in arithmetic, so the first period shows that period's net income.
Start from an opening balance
The Balance Sheet & Cashflow template starts each balance from an input. It tests for blank to find the first period.
| Variable | Formula |
|---|---|
| Retained earnings | if Retained earnings[previous] = blank then Starting retained earnings + Net income - Dividends else Retained earnings[previous] + Net income - Dividends |
| Operating cash flow | Net income - Change in working capital |
| Cash balance | if Cash balance[previous] = blank then Starting cash + Operating cash flow - Dividends else Cash balance[previous] + Operating cash flow - Dividends |
| Cash flow yield | Operating cash flow / Cash balance |
The blank test works when Blanks in data is Treat as blank. That is the setting in the templates.
Starting cash uses the Initial time aggregation. A quarter or year total of that row shows the opening value, not a sum of the periods.
Burn and runway
The P&L and Runway template shows how many months the cash lasts.
| Variable | Formula |
|---|---|
| Net burn | Operating expenses - Revenue |
| Cash balance | if Cash balance[previous] = blank then Starting cash - Net burn else Cash balance[previous] - Net burn |
| Runway (months) | Cash balance / Net burn |
A division by zero gives an error. If Net burn can be zero or negative, test it first:
if Net burn <= 0 then blank else Cash balance / Net burnPatterns in Formula templates
In the formula bar, select Formula templates and search for these names. Eigenn inserts the pattern with placeholders. Press Tab to move to the next placeholder.
| Template | Example after you fill it |
|---|---|
| Retained earnings | Retained earnings[previous] + Net income - Dividends |
| Balance check | Total assets - Total liabilities - Total equity |
| Change in working capital | Working capital[previous] - Working capital |
Put Balance check on its own row. It shows zero in each period where the balance sheet balances.