Choose and Set Up Finance Spreadsheet Templates
Compare finance spreadsheet templates and build a checked 13-week cash-flow forecast in Google Sheets or Excel.
Spreadsheet templates give finance and FP&A teams a reusable structure for recurring work. The right starting point depends on the decision the workbook supports, the required input detail, the reporting period, collaboration needs, and formula complexity.
This guide compares 8 finance template categories, then explains how to build an illustrative 13-week cash-flow forecast in Google Sheets or Excel. The completed workbook includes transaction controls, weekly balances, and expected results for checking the formulas.
What You'll Need
- Google Sheets or Microsoft Excel with edit access
- Familiarity with
IF,SUMIFS, absolute references, and copying formulas - An opening cash balance and dated cash transactions for a live implementation
- Permission to create data-validation rules
Step-by-Step Guide
Match the spreadsheet template to the finance task
A spreadsheet template defines a repeatable input structure, calculation method, and output format. It should make assumptions visible and apply consistent controls whenever someone copies the workbook.
Start by creating a workbook with 2 tabs:
Template Index13-Week Cash Flow
On Template Index, enter these headers in A1:F1:
| Cell | Header |
|---|---|
| A1 | Category |
| B1 | Template |
| C1 | Starting point when |
| D1 | Complexity |
| E1 | Collaboration consideration |
| F1 | Required calculations |
Next, enter the following index in rows 2 through 9. These are template categories, not downloadable ModelMonkey templates.
| Category | Template | Starting point when | Complexity | Collaboration consideration | Required calculations |
|---|---|---|---|---|---|
| Budgeting | Monthly budget | Planning revenue and costs by month or department | Moderate | Assign responsibility for assumptions and submissions | Monthly totals, allocations, and consolidated budget |
| Expenses | Expense log | Recording individual purchases or reimbursements | Low | Define who can enter, classify, and review transactions | Category totals and period summaries |
| Cash flow | 13-week forecast | Managing short-term payment timing and liquidity | Moderate | Set ownership for updating receipts and payments | Weekly inflows, outflows, net movement, and closing cash |
| Analysis | Budget-versus-actual variance | Comparing an approved plan with recorded results | Moderate | Keep budget versions and actual-data cutoffs consistent | Amount variance and percentage variance |
| Reporting | Monthly KPI report | Reporting recurring metrics with stable definitions | Moderate | Document metric owners and definitions | Period totals, ratios, and comparisons |
| Investments | Holdings tracker | Monitoring positions and gains or losses | Moderate | Control price dates and transaction inputs | Position value, cost basis, and gain or loss |
| Receivables | Invoice tracker | Monitoring amounts, due dates, and collection status | Low | Assign status and follow-up ownership | Outstanding amount and due-date analysis |
| Debt | Debt schedule | Tracking balances, interest, and repayments | High | Restrict changes to contractual assumptions | Roll-forward balances, interest, and scheduled payments |
Consider these factors when choosing a template:
- Input detail: Use an expense or invoice tracker for transaction-level records. Use a budget or KPI report for summarized monthly inputs.
- Time horizon: A 13-week forecast addresses near-term liquidity. A multiyear projection supports longer-term planning; see Build a 5-Year Projection in Google Sheets.
- Collaboration: Choose a structure with clear input ownership when several contributors update the workbook.
- Formula requirements: A debt schedule or integrated model requires more calculation logic than an expense log. For linked financial statements, see Build a Startup 3-Statement Financial Model.
The rest of this guide focuses on the 13-week cash-flow template.
Add the cash-flow setup controls
On 13-Week Cash Flow, enter this setup block:
| Cell | Entry |
|---|---|
| A1 | Opening balance (USD) |
| B1 | 10000 |
| A2 | Start date |
| B2 | =DATE(2026,1,5) |
| A3 | End date (exclusive) |
| B3 | =DATE(2026,4,6) |
| A4 | Setup status |
Format B1 as USD with 2 decimal places, then format B2:B3 as YYYY-MM-DD.
The interval from January 5 through April 5 contains 91 days, or 13 complete weeks. Because April 6 is the exclusive end date, transactions dated April 6 aren’t eligible for inclusion.
In B4, enter:
=IF(NOT(ISNUMBER(B1)),"Review opening balance",IF(B1<0,"Review opening balance",IF(NOT(ISNUMBER(B2)),"Review start date",IF(B2<>INT(B2),"Review start date",IF(NOT(ISNUMBER(B3)),"Review end date",IF(B3<>INT(B3),"Review end date",IF(B3<=B2,"Review date range","Ready")))))))
With the stated inputs, B4 should return:
Ready
This formula requires a numeric, nonnegative opening balance, whole-date values without hidden times, and an exclusive end date after the start date. It doesn’t determine whether those dates match your reporting cycle, so you’ll still need to confirm them manually.
Create the transaction table and control rows
Enter these headers in A5:E5:
| Cell | Header |
|---|---|
| A5 | Date |
| B5 | Direction |
| C5 | Description |
| D5 | Amount (USD) |
| E5 | Row status |
Reserve rows 6 through 100 for transactions. Format A6:A100 as YYYY-MM-DD and D6:D100 as USD with 2 decimal places.
In this template, Date, Direction, Description, and Amount are required for every populated transaction row.
Enter the following formula in E6, then copy it through E100:
=IF(COUNTA(A6:D6)=0,"",IF($B$4<>"Ready","Review",IF(NOT(ISNUMBER(A6)),"Review",IF(NOT(ISNUMBER(D6)),"Review",IF(AND(A6>=$B$2,A6<$B$3,C6<>"",D6>=0,OR(EXACT(B6,"Inflow"),EXACT(B6,"Outflow"))),"Include","Review")))))
The initial COUNTA check leaves completely unused rows blank. Once any transaction field is populated, the other checks require:
- A numeric date within the reporting period
- An exact direction of
InfloworOutflow - A nonblank description
- A numeric, nonnegative amount
A status of Include means the row is eligible for the weekly calculation. It isn’t an approval status or evidence that the transaction is accurate, complete in its source system, or unique.
Before applying data validation, enter the illustrative sample below only in columns A:D. Use the displayed DATE formulas in column A; after applying the YYYY-MM-DD format, they will display as the corresponding ISO dates while remaining numeric spreadsheet dates across locales.
Don’t type the Expected status values into column E. Instead, verify the results returned by the formulas already copied there.
| Row | Date entry in column A | Displayed date | Direction | Description | Amount (USD) | Expected status in column E |
|---|---|---|---|---|---|---|
| 6 | =DATE(2026,1,5) | 2026-01-05 | Inflow | Customer receipt | 3000 | Include |
| 7 | =DATE(2026,1,7) | 2026-01-07 | Outflow | Payroll | 2500 | Include |
| 8 | =DATE(2026,1,12) | 2026-01-12 | Inflow | Customer receipt | 1500 | Include |
| 9 | =DATE(2026,1,16) | 2026-01-16 | Outflow | Rent | 1000 | Include |
| 10 | =DATE(2026,4,6) | 2026-04-06 | Inflow | Boundary receipt | 700 | Review |
| 11 | =DATE(2026,1,8) | 2026-01-08 | inflow | Lowercase test | 900 | Review |
Rows 10 and 11 are deliberate control cases. Entering them before configuring validation keeps the example reproducible, even if operational validation later rejects invalid manual entries.
Row 10 falls on the exclusive end date, while row 11 fails the case-sensitive direction check. Unused rows 12 through 100 should remain blank in column E.
For imported transaction dates, verify that =ISNUMBER(A6) returns TRUE for a representative populated row. A value that displays as 2026-01-05 can still be stored as text, in which case it will return Review.
Add entry validation
Configure these rules for rows 6 through 100:
A6:A100: permit dates from=DATE(2026,1,5)through=DATE(2026,4,5), equivalent to$B$2through$B$3-1.B6:B100: permit values from the listInflow,Outflow.D6:D100: permit numeric values greater than or equal to zero.
For a test copy that retains rows 10 and 11, use warning-style validation:
- In Google Sheets, choose Show a warning for invalid data.
- In Excel, use Warning as the Data Validation error-alert style.
A validation warning doesn’t exclude a row from calculations. The Row status formula provides that control, while the weekly formulas require column E to equal Include.
For an operational copy, choose validation behavior that matches your input process. Pasted or imported values still need review through the status column and comparison with source records.
Build the bounded 13-week summary
Enter these headers in G5:K5:
| Cell | Header |
|---|---|
| G5 | Week start |
| H5 | Inflows (USD) |
| I5 | Outflows (USD) |
| J5 | Net cash flow (USD) |
| K5 | Closing balance (USD) |
In G6, enter:
=IF($B$4="Ready",$B$2,"")
Then enter this bounded week-start formula in G7 and copy it through G18:
=IF(NOT(ISNUMBER(G6)),"",IF(G6+7<$B$3,G6+7,""))
The week starts should run from 2026-01-05 in G6 through 2026-03-30 in G18. The final row covers March 30 through April 5.
If you shorten the reporting interval, the end-date condition prevents week starts on or after the exclusive end date from appearing.
In H6, enter the weekly inflow formula and copy it through H18:
=IF(NOT(ISNUMBER(G6)),"",SUMIFS($D$6:$D$100,$A$6:$A$100,">="&G6,$A$6:$A$100,"<"&MIN(G6+7,$B$3),$B$6:$B$100,"Inflow",$E$6:$E$100,"Include"))
Next, enter the weekly outflow formula in I6 and copy it through I18:
=IF(NOT(ISNUMBER(G6)),"",SUMIFS($D$6:$D$100,$A$6:$A$100,">="&G6,$A$6:$A$100,"<"&MIN(G6+7,$B$3),$B$6:$B$100,"Outflow",$E$6:$E$100,"Include"))
Although SUMIFS text comparisons may not distinguish capitalization, the column E criterion keeps the lowercase inflow control row out of the summary.
In J6, enter the net-cash-flow formula and copy it through J18:
=IF(OR(NOT(ISNUMBER(H6)),NOT(ISNUMBER(I6))),"",H6-I6)
In K6, enter:
=IF(OR($B$4<>"Ready",NOT(ISNUMBER(J6))),"",$B$1+J6)
Finally, enter this rolling-balance formula in K7 and copy it through K18:
=IF(OR($B$4<>"Ready",NOT(ISNUMBER(K6)),NOT(ISNUMBER(J7))),"",K6+J7)
Format G6:G18 as YYYY-MM-DD and H6:K18 as USD with 2 decimal places.
These formulas use commas as argument separators. If your spreadsheet locale uses semicolons, replace the commas with semicolons.
Verify the baseline calculations
For the week beginning January 5, the included transactions are the $3,000 receipt and $2,500 payroll payment:
Net cash flow = 3000 - 2500 = 500
Closing balance = 10000 + 500 = 10500
Confirm these results:
| Cell | Expected result |
|---|---|
| G6 | 2026-01-05 |
| H6 | $3,000.00 |
| I6 | $2,500.00 |
| J6 | $500.00 |
| K6 | $10,500.00 |
For the week beginning January 12:
Net cash flow = 1500 - 1000 = 500
Closing balance = 10500 + 500 = 11000
Confirm these results:
| Cell | Expected result |
|---|---|
| G7 | 2026-01-12 |
| H7 | $1,500.00 |
| I7 | $1,000.00 |
| J7 | $500.00 |
| K7 | $11,000.00 |
Together, these control totals check the links among the opening balance, weekly movements, and rolling balance. For a broader runway model based on forecast assumptions, see Forecast Cash Runway in Excel or Google Sheets.
Check boundaries and invalid inputs
Run these cases in a separate test copy. Enter test dates with DATE formulas so they remain numeric regardless of spreadsheet locale. The results are expectations derived from the formulas, not claims that the tests have been executed.
| Test | Input or change | Expected result |
|---|---|---|
| Empty reserved row | Leave all cells in A:D blank | Row status remains blank and the row contributes nothing |
| Missing description | Enter a numeric date, valid direction, and amount but leave Description blank | Status is Review; the row contributes nothing |
| Other partial row | Leave any other required transaction field blank | Status is Review; the row contributes nothing |
| Zero amount | Add =DATE(2026,1,20), Inflow, Zero test, and 0 | Status is Include; the weekly inflow and closing balance are unchanged |
| Blank amount | Enter a numeric date, valid direction, and description but leave Amount blank | Status is Review; the row contributes nothing |
| Text amount | Enter one hundred in Amount | Status is Review; the text isn’t used in arithmetic |
| Negative amount | Enter -25 in Amount | Status is Review; use a nonnegative amount and the appropriate direction |
| Invalid direction | Enter Transfer, a blank value, or lowercase inflow | Each populated row returns Review |
| No matching rows | Leave a week without included transactions | Inflows, outflows, and net cash flow are 0; closing cash equals the prior week |
| Start boundary | Enter a valid transaction dated =DATE(2026,1,5) | The transaction is included in the first week |
| Before start | Enter a transaction dated =DATE(2026,1,4) | Status is Review |
| Weekly boundary | Use the valid receipt dated =DATE(2026,1,12) | It is excluded from the January 5 week and included in the January 12 week |
| Final eligible date | Enter a valid transaction dated =DATE(2026,4,5) | It is included in the final week beginning March 30 |
| Exclusive end date | Use the sample transaction dated =DATE(2026,4,6) | Status is Review |
| Text-formatted date | Enter '2026-01-05 as text in an unvalidated test row | Status is Review because ISNUMBER returns FALSE |
| Duplicate entry | Copy the January 5 receipt into another row | First-week inflows become $6,000, net cash flow becomes $3,500, and closing cash becomes $13,500 |
| Shortened interval | Change B3 to =DATE(2026,1,12) | G6 remains January 5; G7:G18 remain blank |
| Validation limitation | Enter an invalid value using warning-style validation | The warning doesn’t exclude the row; its status becomes Review, and the summary excludes it |
Test the setup controls separately:
| Setup test | Expected B4 result |
|---|---|
| Blank or text opening balance | Review opening balance |
| Negative opening balance | Review opening balance |
| Blank or text start date | Review start date |
| Start date with a fractional time component | Review start date |
| Blank or text end date | Review end date |
| End date with a fractional time component | Review end date |
| End date equal to or before the start date | Review date range |
Whenever B4 isn’t Ready, the weekly summary remains blank. Completely unused transaction rows also stay blank, while populated incomplete rows display Review.
The duplicate test highlights a limitation of this template: it counts every eligible row but doesn’t determine whether similar transactions are legitimate or duplicated.
Complete the review checklist
Before using the template for an operating forecast:
- Confirm the opening balance against the relevant bank statement or accounting record.
- Confirm the reporting currency and start and end dates.
- Verify that imported dates are numeric spreadsheet dates, not text that only looks like a date.
- Investigate every populated transaction marked
Review. - Spot-check transaction dates, descriptions, and amounts against source records.
- Review apparent duplicates; the formulas don’t identify or exclude them.
- Confirm formulas extend only through the intended input and summary ranges.
- Test one known inflow and outflow against the corresponding weekly total.
- Protect formula columns and summary cells if contributors should edit only transaction inputs.
- Document the workbook owner, review date, and source-data cutoff.
- Save a clean master and create a copy for each forecast cycle.
Inspect and adjust the template with ModelMonkey
ModelMonkey is an AI assistant for spreadsheet work in Google Sheets and Microsoft Excel. Using workbook context, it can help read ranges, find content, analyze tables, organize data, and adjust formulas, formatting, or charts.
For this cash-flow template, you could use requests such as:
Inspect A5:E100 and list the populated rows where Row status is Review. Do not change the sheet.
Check H6:K18 for inconsistent formula references. Explain any differences before proposing edits.
Summarize the weekly cash movement in G5:K18 and identify the lowest projected closing balance.
ModelMonkey’s formulas and conclusions still require finance review. When the relevant workflow presents a preview, inspect the proposed spreadsheet changes before proceeding.
Wrapping Up
This workbook combines an index of 8 common finance spreadsheet template categories with a 13-week cash-flow forecast. In the illustrative example, the expected first-week closing balance is $10,500, and the second-week closing balance is $11,000.
Completely empty transaction rows remain blank. Populated rows with a missing description, invalid direction, invalid amount, text date, or out-of-range date are excluded when their status is Review, provided the summary formulas continue to require Include.
Keep the template as a reusable master only after confirming its control totals, reporting boundaries, source balance, and transaction completeness.
Frequently Asked Questions
Should I start with a budget template or a cash-flow template?
Use a budget template to plan revenue and expenses by accounting period. Choose a cash-flow template when payment timing and available liquidity matter. The templates answer different questions, even when they share some assumptions.
Can I extend this template beyond 13 weeks?
Yes. Add weekly rows below row 18, copy the formulas in columns G:K, update B3 to the new exclusive end date, and extend the permitted transaction-date range. Then verify the first added week against known transactions.
Why is April 6 the exclusive end date?
Thirteen complete weeks from January 5 end after April 5. Using April 6 as the exclusive boundary makes April 5 eligible for the final week while preventing April 6 transactions from entering the forecast.
Why should I enter sample dates with the DATE function?
DATE creates a numeric spreadsheet date regardless of how the workbook locale interprets a typed value such as 2026-01-05. The status formula requires numeric dates, so a date stored as text returns Review even if it looks correct on screen.
Is the transaction description required?
Yes. The C6<>"" condition requires a description for every populated transaction. A row with a valid date, direction, and amount but a blank description returns Review and is excluded from the weekly summary.
Why are unused transaction rows blank instead of marked Review?
The COUNTA guard leaves a row blank when every cell in A:D is empty. Once any field is entered, the remaining checks return either Include or Review. This keeps unused reserved rows out of the exception list while still flagging incomplete transactions.
Does the template detect duplicate transactions?
No. Every row marked Include is counted. A copied $3,000 receipt therefore adds another $3,000 to first-week inflows. Review possible duplicates against source records.
Why use both data validation and a Row status formula?
Validation guides data entry. The status formula independently determines whether a populated row satisfies the template’s description, type, date, amount, and direction conditions. Only rows whose status is Include enter the summary.
Can I use this template in Google Sheets and Excel?
Yes. The displayed functions are available in both applications. Validation, formatting, and protection menus differ, and locales using semicolon separators require formula punctuation changes.