TB-mapped linked statements
Turn a raw trial balance into a monthly management P&L, balance sheet and cash flow through an editable account-mapping table and a per-month data readiness check.
The advanced reports use a new synthetic dataset. It is not a drop-in replacement for the original pack files, so the figures on this page cannot be compared with reports 01–07 even though the company name and currency are the same. This set also uses cash COGS with depreciation below EBITDA, a different cost definition from the core manufacturing model.
The report as it appears in Excel
Captured from 08_TB_Mapped_Statements_EN.xlsx, sheet Summary, after recalculation in Microsoft Excel. Click the image to zoom. Open full-size image
Figures from the sample workbook
| Measure | Latest month | Trailing 12 months |
|---|---|---|
| Revenue | 6,230 | 70,567 |
| EBITDA | 1,081 | 11,527 |
| Net profit | 750 | 7,869 |
| Operating cash flow | 1,025 | 8,363 |
THB thousands · The left column is the latest month, the right column the trailing 12 months
Reading order
- Check Mapping A:E: unique account codes, and a report line, statement type and presentation sign for every account. Several accounts may share one line.
- Paste values only into TB_Input A:D. Every mapped account must appear in every month, including accounts with a zero balance. Both sides are nonnegative and at least one side is zero.
- Register each month once in Periods A, including the preceding month as the opening balance, then complete Cash_Supplement for every registered period. A known zero is entered as zero.
- Recalculate, then read Checks, Statements and Summary in that order. Never insert a cash or equity plug to make a failed reconciliation disappear.
Reading the figures in a worked case
The latest month shows THB 6,230k of revenue and THB 750k of net profit, but only THB 1,025k of operating cash flow. The gap sits in THB 7,034k of receivables and THB 5,426k of inventory that have not turned into cash, leaving THB 12,388k of closing cash. The first three figures are flows for the month; cash is a balance at the month end, so they are read differently.
How it is calculated, in plain language
The trial balance must carry closing balance-sheet balances plus current-year cumulative P&L balances before the year-end closing journal. Do not paste monthly movements, monthly P&L activity or a post-close December trial balance into this layout without converting the basis first: December must still carry its P&L before it closes into retained earnings.
See this number → check what → weigh which decision
Example cases from the synthetic August 2026 data. Every figure comes from the same Excel dataset; the interpretation is a starting point, not a business conclusion.
- You see
Net profit for the latest month is THB 750k while operating cash flow is only THB 1,025k, with THB 7,034k still sitting in receivables.
- Check next
Read the increases in receivables and inventory at Statements rows 56–57, then check overdue invoices and slow-moving stock line by line.
- Decision to weigh
Assign collection or purchasing actions based on the underlying transactions, not on an adjustment that makes the report look better.
- You see
The PPE check on Checks shows REVIEW even though the balance sheet still balances.
- Check next
Compare cash CAPEX, noncash additions, depreciation and the net book value of disposals with the asset register.
- Decision to weigh
Correct the source schedule to match the register instead of adding an unexplained cash adjustment to make the check pass.
- You see
January profit looks unusually high next to every other month.
- Check next
Confirm whether the pasted data is a year-to-date balance or a monthly movement, and whether the prior December has already been closed into retained earnings.
- Decision to weigh
Convert the basis before interpreting the result: a swapped basis counts a month of profit twice or drops it entirely.
Core calculations in the workbook
| Measure | Definition |
|---|---|
| Net debit | Debit − Credit |
| Presentation amount | Net debit × mapping sign |
| Retained earnings | TB retained earnings + current-year net profit |
| Operating cash flow | Net profit + D&A − disposal gain − ΔAR − Δinventory + ΔAP |
| Investing cash flow | −cash CAPEX + disposal proceeds |
| Financing cash flow | Debt draw − principal repayment + cash equity issue − dividends |
How to use this report in the workbook
Purpose
Use when the source is a closing trial balance and account mapping is the main task. Summary reports the latest month and the trailing twelve months; F6 selects the last month of that window.
Account mapping
Mapping A9:E108 holds up to 100 account mappings and H:J is the supported line catalog. Several accounts may map to one line, but each account code appears once. Unmapped accounts are reported, never dropped silently.
The calculation horizon
Register each source month once in Periods A9:A69, including the preceding opening month. B:G calculate readiness and detect missing rows, duplicates, an unbalanced TB and an incomplete cash schedule.
Cash flow
Cash_Supplement A9:J69 takes one numeric row per registered period for supported cash and noncash transactions. Closing cash flow cash must equal TB cash; PPE, debt, capital and retained earnings reconcile separately.
What is not modelled
TB_Input takes 1,200 rows and Mapping 100 accounts. Adding rows beyond those documented ranges does not extend the formulas, validation or controls; every capacity change must be applied to all of them together.
Reading and editing essentials
- Always check the company, reporting period and currency scale. A displayed value of 1,000 in a THB-thousands table means THB 1,000,000.
- Actual means recorded results, Budget means plan, and Forecast means estimate. Do not describe a forecast as an achieved result.
- The usual arithmetic variance is Actual minus Budget. Higher revenue or profit is generally favourable; higher costs require investigation of overspending and activity levels.
- Revenue and profit are flows that can be summed across months. Cash, receivables and inventory are balances measured at a particular date.
- Calculate aggregate margin as total profit divided by total revenue. A value of n.a. means a ratio cannot be calculated under the applicable conditions; it should not be replaced with zero without justification.
- Thai and English workbooks are independent files. Editing one does not update the other. Choose a working master, retain backups and refresh related files consistently.
- Before using company data, reconcile the accounts and check dates, version names and formula ranges. Adding rows or months may require extending formulas, charts and controls.
- Read the Checks sheet, but do not treat OK as assurance over everything. Source coverage controls focus on the selected actual month; budget and forecast coverage also need review.
- All data is illustrative. Adapt costing, tax, calendar and funding policies to the business. The pack is a starting point for management reporting.
Related workbook
08_TB_Mapped_Statements_EN.xlsx
Main sheet Summary / Statements · Sources TB_Input + Cash_Supplement + Mapping · The workbook and Markdown guide are in the extension pack, which is separate from reports 01–07
Use your browser’s print command to save this page as a PDF.
