Monthly financial report template: income statement, balance sheet and cash flows from one trial balance

The monthly financial report is what the leadership team, the owners, the lender and the board read once the month-end close is done. This template builds it from one trial balance export: a summary page, the income statement against budget and prior year, the balance sheet, the statement of cash flows, AR and AP aging, commentary, and a page of checks that proves every statement ties to the general ledger. It is set up for a US company reporting in dollars on the same accrual basis as its US GAAP financial statements, with a fiscal year that can start in any month.

What the report has to answer

Each page answers one question, in the order a reader asks them:

No accounting standard sets this layout. US GAAP, codified in the FASB Accounting Standards Codification (ASC), governs the financial statements a company issues to outsiders; an internal monthly report can be laid out however its readers find useful. Good practice is to keep the accounting the same as the year-end statements, so that twelve monthly reports add up to the audited or reviewed year, and to add what external statements leave out: the budget, the prior-year columns, a handful of KPIs and the commentary.

How the workbook is built

Everything starts from the TB sheet. Paste the trial balance as monthly activity, one row per account per month: account code, account name, month end, debit, credit. Begin with the balances at a fiscal year end, then add one block per month, including the whole prior fiscal year so the comparison columns fill. The sample runs from balances at December 31, 2024 to activity for October 2026.

The Mapping sheet lists each account code once, with the report line it belongs to, the statement (IS or BS) and a sign. The sign belongs to the line, not to the account. Revenue, liability and equity lines take −1, because the ledger holds them as credits; expense and asset lines take 1. A contra account takes the sign of the line it sits in. In the sample, 4100 Sales returns and allowances is a debit-balance account mapped to Revenue with −1, so it reduces revenue, and 1590 Accumulated depreciation is a credit-balance account mapped to Property and equipment, net with 1, so it reduces the asset. A row check column marks any row whose statement or sign is blank or invalid as FIX.

Each income statement line is then one SUMIFS over a date window. Named ranges keep the formulas readable:

Month actual:       =SUMIFS(TB_Signed,TB_Line,$A5,TB_Date,">"&LastMonthEnd,TB_Date,"<="&PeriodEnd)
Fiscal YTD actual:  =SUMIFS(TB_Signed,TB_Line,$A5,TB_Date,">"&PriorFYE,TB_Date,"<="&PeriodEnd)
Same month, prior:  =SUMIFS(TB_Signed,TB_Line,$A5,TB_Date,">"&PYLastMonthEnd,TB_Date,"<="&PYMonthEnd)

TB_Signed is debit minus credit times the mapping's sign, so revenue comes out positive. Because every period is a date window, a trial balance dated on the first of each month works as well as one dated at month end. The budget columns read the Budget sheet with INDEX and MATCH. Variance is actual minus budget on every line, and the F/U column reads that sign according to the line. For a cost line:

Variance:  =B6-C6
F/U:       =IF(ROUND(D6,2)=0,"-",IF(D6<0,"F","U"))

Balance sheet lines add every movement up to the column's date, opening balances included:

=SUMIFS(TB_Signed,TB_Line,$A6,TB_Date,"<="&B$4)

Retained earnings take the opening balance, add net income to date and deduct dividends, so the balance sheet balances in every month without a closing entry. The workbook uses SUMIFS, COUNTIFS, INDEX/MATCH, EOMONTH and IFERROR, with no XLOOKUP or dynamic arrays, so it opens in Excel 2016 and 2019 as well as Microsoft 365.

Fiscal years that do not start in January

For a fiscal year that ends June 30, type 7 in Cover B5; for one that ends September 30, type 10. The Cover sheet works out the fiscal month, the fiscal year (named for the calendar year it ends in) and the prior fiscal year end, and every year-to-date column, budget column and balance sheet comparison follows them. With 7, October 31, 2026 is fiscal month 4 of fiscal 2027: the year to date runs from July 1, and the balance sheet compares with June 30, 2026. The Budget sheet's month labels move too, so its first month column reads Jul. A blank B5 is read as 1, a calendar year.

The template works in calendar months. A 4-4-5 or 52-53 week calendar ends its periods on a fixed day of the week rather than at month end, so it needs its own period-end dates; see fiscal calendars and period cuts.

Worked example: October 2026

Example Trading Inc. is a wholesale distributor with a calendar fiscal year, so October is fiscal month 10. Its income statement for the month:

The reading: revenue beat budget by $21,000, but cost of goods sold rose faster than sales. Gross margin was 37.5% against 38.0% budgeted after the main carrier added a freight surcharge, so gross profit is only $4,600 ahead. Two warehouse hires in September put salaries and benefits $7,500 over budget. EBITDA is $4,200 short of budget, and net income of $27,465 is $3,150 under. For comparison, October 2025 revenue was $594,000. Fiscal year to date, revenue is $6,197,000 against $6,145,000 budgeted, and net income $194,562 against $203,037, $8,475 short.

The statement of cash flows for the month, by the indirect method:

Ending cash of $300,120 equals cash on the balance sheet. Net income was $27,465, yet cash fell $11,880: $44,000 went into receivables and inventory, $18,000 bought a forklift and $10,000 repaid the term loan. Two supplemental lines show what the indirect method otherwise hides, cash paid for interest ($2,280) and for income taxes ($0 in October; the sample pays estimated tax in April, June, September and December).

On the Summary sheet, DSO is $982,000 of receivables / $1,964,000 of revenue for August to October × 92 days = 46.0 days, against net 30 terms. DPO is $318,000 of payables / $1,223,600 of cost of goods sold × 92 = 23.9 days.

AR and AP aging

Paste both agings from the ledger, run as of the period end, in five buckets by days past due. The sample's receivables:

The AR aging total of $982,000 equals accounts receivable on the balance sheet, and the AP aging total of $318,000 equals accounts payable. Accrued liabilities stay out of the AP aging: they are costs incurred but not yet billed or paid, such as accrued payroll, so there is no invoice to age. Two things distort an aging without breaking the tie-out. Unapplied cash, a customer payment not yet matched to its invoices, sits as a credit while the old invoices stay in the late buckets, so the aging overstates what is late. And the sample carries no allowance for credit losses; if yours does, map it to its own line and add that line to the Balance sheet and Cash flows sheets, because the aging ties to gross receivables.

The checks built into the template

The Checks sheet shows ten differences and a result line. The result reads "All checks pass" only when every difference is 0, and "No TB rows dated in this month" when the Cover date has no activity yet, so a month that has not been pasted cannot pass.

1. The trial balance nets to zero: =ROUND(SUM(TB_Net),2) across every row.
2. No unmapped codes: =COUNTIFS(TB_Line,"UNMAPPED"). A new ledger account would otherwise drop out of the report.
3. No mapping rows marked FIX. A blank or zero sign is flagged, not read as 0.
4. Net income equals the ledger: the income statement's net income against the trial balance's income statement accounts for the month. An income statement account with the wrong sign, such as sales returns mapped with 1, shows up here.
5. The balance sheet balances: total assets minus total liabilities and stockholders' equity. A balance sheet account with the wrong sign, such as accumulated depreciation mapped with −1, shows up here.
6. Net income equals the change in retained earnings, before dividends. A difference means an entry was posted straight to retained earnings.
7. The statement of cash flows reaches balance sheet cash for the month,
8. and for the fiscal year to date.
9. AR aging equals accounts receivable ($982,000 in the sample).
10. AP aging equals accounts payable ($318,000).

On every input that feeds these checks, blank and zero give the same answer: an empty aging bucket or an empty debit cell counts as 0. Read the Checks sheet before any other page.

Writing the commentary

Write for the reader who will not open the statements: lead with the answer, then up to three bullets per page. The sample's threshold is both $5,000 and 5% of budget. In October only salaries and benefits crosses both, so it gets its own explanation; revenue and EBITDA still lead the summary, because they are what leadership will ask about first. Name the cause and the next step. "Cost of goods sold was $16,400 over budget" is already in the table; "the carrier surcharge costs about half a point of margin until the freight contract is renegotiated" is commentary. For the method behind the variance columns, see variance analysis.

Where it goes wrong

• A year-to-date trial balance pasted as monthly activity. Every month after the first comes out several times too large. Export activity by period.
• Accruals missed at the close. Payroll for the days between the last pay date and month end, or a supplier invoice still in someone's inbox: one month is low and the next is high. The month-end close checklist lists the entries that are easy to miss.
• A budget loaded by calendar month for a July fiscal year. Column B of the Budget sheet is the first fiscal month: paste July there, not January.
• Agings run on a different date. An AR aging run on business day 4, after the first receipts of the new month, will not tie to the month-end balance sheet. Run both as of the period end.
• New accounts not in Mapping. They show UNMAPPED on TB and fail check 2 until they are mapped.
• Income tax on a pass-through entity. The sample is a C corporation that accrues income tax every month. An S corporation or an LLC taxed as a partnership usually has no federal income tax line; leave it empty or use it for any entity-level state tax.

Beyond the template

A workbook report works for one entity and one ledger. It strains once there are several entities to consolidate, intercompany balances to eliminate, or a leadership team asking follow-up questions the report does not answer. Upload the trial balance and budget and Covirage's tools compute the variances, the statement of cash flows and DSO with every check run and every figure traced to its rows; the external AI model drafts the commentary from the finished figures and never does the arithmetic. See FP&A reporting. For the board version of the report, see the board report template; for the early read before the close finishes, the flash report. Reporting a UK company in GBP under FRS 102, with accounts filed at Companies House? Use the management accounts template instead.