Blog · Territory, capacity and quota planning · Finance and FP&A teams
Build a reusable cash burn workbook from monthly receipts, payments, and bank balances. This layout includes spreadsheet formulas, a worked reconciliation, and checks for runway inputs.
A cash burn calculator needs a monthly cash workbook with separate operating receipts, operating payments, and available cash. The layout below produces reusable burn and runway calculations while proving that the movements tie to your bank balances. For definitions and the underlying metric conventions, use the existing burn rate guide.
Choose one business, one currency, and the bank accounts included in the calculation. Record the reporting cutoff and the months used for the historical average. Keep actuals separate from projections so a planned collection cannot quietly become received cash.
Use cash dates rather than invoice dates. The SEC's guide to financial statements explains that cash flow statements track cash inflows and outflows and separate operating, investing, and financing activities. Preserve those distinctions in the workbook even when management uses its own burn convention.
This layout defines burn from operating cash movements. Equipment purchases, debt principal, and funding sit in a separate column. If management wants an additional measure that includes capital spending, add a clearly labeled supplementary calculation rather than changing the meaning of an existing column.
Name the first worksheet Transactions. Use one row per cash movement, retain the source reference, and store receipts as positive amounts and payments as negative amounts. Standardize activity labels to Operating, Investing, Financing, or Transfer.
Here are the July movements for an illustrative US business-services company. Dates shown as month names should be stored as actual spreadsheet dates.
| A: Cash date | B: Source ID | C: Activity | D: Signed cash (USD) | E: Description |
|---|---|---|---|---|
| July 8, 2026 | R-JUL | Operating | 210,000 | Customer collection |
| July 15, 2026 | P-JUL | Operating | -180,000 | Payroll |
| July 22, 2026 | S-JUL | Operating | -150,000 | Supplier payments |
| July 28, 2026 | F-JUL | Operating | -60,000 | Facilities payments |
| July 30, 2026 | E-JUL | Investing | -60,000 | Equipment purchase |
Operating payments total $390,000: $180,000 + $150,000 + $60,000. Keep transfers between included accounts outside burn and ensure their paired entries net to zero. An unmatched transfer means either the export is incomplete or the workbook boundary needs clarification.
Name the second worksheet Monthly. Put real month-start dates in column A. Fill formulas down, extending every source range together when the export grows.
| Column | Heading | Formula or input in row 2 |
|---|---|---|
| A | Month | July 1, 2026 |
| B | Operating receipts | Positive operating movements in that month |
| C | Operating payments | Absolute value of negative operating movements |
| D | Gross burn | =C2 |
| E | Net burn | =C2-B2 |
| F | Nonoperating net | Investing plus financing movements |
| G | Opening cash | Reconciled opening balance; later rows use prior H |
| H | Calculated closing cash | =G2+B2-C2+F2 |
| I | Statement closing cash | Input from included bank statements |
| J | Difference | =H2-I2 |
Enter this formula in B2:
=SUMIFS(Transactions!$D$2:$D$10000,Transactions!$C$2:$C$10000,"Operating",Transactions!$D$2:$D$10000,">0",Transactions!$A$2:$A$10000,">="&A2,Transactions!$A$2:$A$10000,"<"&EDATE(A2,1))
For C2, copy that formula, change ">0" to "<0", and place a minus sign before SUMIFS. Payments then display as positive amounts. For F2, sum the following expression separately for Investing and Financing, then add the results:
=SUMIFS(Transactions!$D$2:$D$10000,Transactions!$C$2:$C$10000,"Investing",Transactions!$A$2:$A$10000,">="&A2,Transactions!$A$2:$A$10000,"<"&EDATE(A2,1))
Both Microsoft's SUMIFS documentation and Google's SUMIFS documentation describe summing amounts that satisfy multiple criteria. Matching activity, sign, and date keeps each monthly subtotal reproducible in Excel or Google Sheets.
The company starts July with $1,800,000. Its August nonoperating inflow is funding; July and September nonoperating outflows are equipment payments. Later transaction rows follow the same structure as July's export.
| Month | Receipts (USD) | Payments / gross burn (USD) | Net burn (USD) | Nonoperating net (USD) | Opening cash (USD) | Closing cash (USD) |
|---|---|---|---|---|---|---|
| July | 210,000 | 390,000 | 180,000 | -60,000 | 1,800,000 | 1,560,000 |
| August | 240,000 | 420,000 | 180,000 | 500,000 | 1,560,000 | 1,880,000 |
| September | 270,000 | 450,000 | 180,000 | -30,000 | 1,880,000 | 1,670,000 |
| Total movements | 720,000 | 1,260,000 | 540,000 | 410,000 |
The full-period identity is $1,800,000 + $720,000 - $1,260,000 + $410,000 = $1,670,000. Each monthly difference in column J must also be zero after matching the statements. Funding explains why the cash balance falls by less than operating net burn over the period.
Keep the lookback and cash exclusions visible. The cash runway glossary explains the interpretation; this panel makes the inputs auditable.
| Panel item | Cell | Formula or input | Result |
|---|---|---|---|
| Average monthly gross burn (USD) | B8 | =AVERAGE(D2:D4) |
420,000 |
| Average monthly net burn (USD) | B9 | =AVERAGE(E2:E4) |
180,000 |
| Latest closing cash (USD) | B10 | =H4 |
1,670,000 |
| Management reserve (USD) | B11 | Input | 320,000 |
| Cash after reserve (USD) | B12 | =MAX(0,B10-B11) |
1,350,000 |
| Runway after reserve (months) | B13 | =IF(B9>0,B12/B9,"No depletion estimate") |
7.5 |
Using the full closing balance gives $1,670,000 / $180,000 = 9.3 months, rounded to one decimal. The reserve-adjusted result is $1,350,000 / $180,000 = 7.5 months. Document whether the reserve is a management buffer or cash that is legally unavailable; those are different constraints.
Covirage is not affiliated with Microsoft or Google.
Bring your cash receipt and payment export with the monthly opening and closing balances. Covirage maps the columns, and its tools compute and check the figures; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See forecast analysis for the supporting planning context.
Use actual cash movements for the workbook. An income statement includes revenue before collection and expenses before payment, as well as noncash charges. Start with a bank or cash-ledger export, then classify the receipts and payments consistently.
Return a text label rather than dividing by zero or displaying negative months. Zero or negative historical net burn means the selected period does not produce a cash-depletion estimate. Future payment timing and commitments still require a cash forecast.
Keep funding outside operating burn. Equity proceeds and borrowing increase the cash balance, so include them in the bank reconciliation and available cash when appropriate. Putting them in operating receipts can make a business appear self-funding when operations still consume cash.