# Tarnlea Build & Retrofit Ltd (fictional): Construction and Renovation Finance Pack, model guide and validation

Prepared by BriQs Group as a demonstration of the monthly finance pack an outsourced finance department runs for a founder-led contractor. **Everything in the pack is fictional**: the company, clients, subcontractors, contracts, invoices and every number. Nothing describes a real client. Tax rates and filing rules reflect published Irish rules as at September 2026.

Workbook: `Tarnlea_Build_Retrofit_Construction_Finance_Pack_Aug26_FICTIONAL.xlsx` (31 sheets, 32,946 formulas). Reporting month Aug-26 (eight actual months), forecast Sep-26 to Dec-27. Currency EUR. Jurisdiction: Ireland, chosen because it pairs EUR reporting with the full set of construction mechanics a UK or EU contractor recognises: withholding on subcontractor payments (RCT), domestic reverse charge VAT, retentions, payment claims and grant-funded retrofit.

## The business modelled

Four lines of business with different economics: deep home retrofit as an SEAI-registered One Stop Shop (grant deducted from the homeowner price and claimed from SEAI after the post-works BER), private renovation and extensions (deposits, progress billing, snag retention), commercial fit-out (monthly valuations, 3 to 5% retention, performance bonds) and council retrofit under a framework (reverse charge VAT, RCT at 0% on the company). About 29 site operatives rising to 32, nine office staff, a van fleet and hire purchase.

## Validation results

Recalculated with LibreOffice under each scenario (Assumptions!C11 = 1, 2, 3):

| Scenario | Formula errors | Integrity checks | Overall status |
|---|---|---|---|
| Base | 0 | 37 of 37 OK | OK |
| Downside | 0 | 37 of 37 OK | OK |
| Upside | 0 | 37 of 37 OK | OK |

The checks, all live formulas on the Checks sheet:

- Balance sheet balances in every month: value 0.24, OK
- Cash flow: indirect method equals direct method every month: value 0.00, OK
- Closing cash on the cash flow equals balance sheet cash: value 0.00, OK
- Retained earnings roll: closing = opening + profit - dividends: value 0.00, OK
- EBITDA on the cash flow equals EBITDA on the P&L: value 0.00, OK
- Opening balance sheet at 31-Dec-25 balances: value 0.01, OK
- Revenue recognised in actual months equals % complete x contract revenue: value 0.00, OK
- Forecast cost spread equals cost to complete, contracts finishing within the horizon: value 0.00, OK
- Variations in contract value at the last actual month agree with the register: value 0.00, OK
- Percentage complete stays between 0% and 100%: value 0.00, OK
- Retrofit revenue shares sum to 100%: value 0.00, OK
- Renovation revenue shares sum to 100%: value 0.00, OK
- Retrofit homeowner stage invoices sum to 100%: value 0.00, OK
- Renovation billing (deposit + progress + snag) sums to 100%: value 0.00, OK
- Programme cost mixes sum to 100%: value 0.00, OK
- Named contract cost mixes: preliminaries share not negative (minimum): value 0.05, OK
- Cost to complete covers every remaining commitment (minimum uncommitted balance): value 94,307.60, OK
- Project profitability: allocated net contributions equal EBITDA: value 0.00, OK
- AR aging: commercial ties to the ledger: value 0.00, OK
- AR aging: council ties to the ledger: value 0.00, OK
- AR aging: homeowners ties to the ledger: value 0.00, OK
- AR aging: seai grants ties to the ledger: value 0.00, OK
- AR aging: other ties to the ledger: value 0.00, OK
- AP aging: subcontractors ties to the Payables schedule: value 0.00, OK
- AP aging: materials ties to the Payables schedule: value 0.00, OK
- AP aging: plant and site services ties to the Payables schedule: value 0.00, OK
- AP aging: overheads ties to the Payables schedule: value 0.00, OK
- 13-week cash forecast reconciles to the monthly balance sheet at 31-Dec-26: value 0.00, OK
- Scenario snapshots are current for the selected scenario: value 0.00, OK
- Budget vs actual gross profit bridge reconciles to the P&L variance: value 0.00, OK
- Trade receivables never negative: value 277,981.90, OK
- SEAI grant receivable never negative: value 108,387.05, OK
- Retentions receivable never negative: value 116,889.74, OK
- Trade payables never negative: value 430,174.39, OK
- Fixed asset net book value never negative: value 168,589.92, OK
- Hire purchase balance never negative: value 112,831.25, OK
- Timeline: months flagged actual equal the last actual month number: value 0.00, OK

What they prove: the balance sheet balances in all 24 months; the indirect cash flow equals a direct build of every schedule cash line to the cent; revenue booked in every actual month equals percentage of completion times contract revenue on every contract; the S-curve spreads exactly the cost to complete; the WIP engine and the variations register apply the same recognition policy; percentage of completion never exceeds 100%; every contract's cost to complete covers its remaining commitments; the project profitability allocation adds back to EBITDA; all nine aging tie-outs agree with the ledger; the 13-week forecast reconciles to the December balance sheet; the scenario snapshots are current; the budget bridge reconciles.

## Contract accounting

- Revenue over time on the input method: % complete = cost to date / estimated cost at completion (IFRS 15.B18; the same principle as FRS 102 Section 23).
- Contract revenue = original contract + approved variations + undecided variations at a 50% policy constraint (IFRS 15.56) + pending variations at the scenario agreed share once their expected decision date passes.
- Expected losses are provided in full as onerous contract provisions (IAS 37) and unwind as the contract completes.
- Contract asset or liability per contract = revenue to date less gross value certified; retention is a separate receivable.
- Forecast cost: the project manager's cost to complete at the last actual month (plus the scenario overrun) is spread on an S-curve to the effective completion date; cost types follow the contract mix.

## Irish rules modelled and their sources

- VAT: 13.5% on construction services, including renovation and retrofit; the Budget 2026 9% rate covers new apartments only (Revenue; Budget 2026 VAT briefings). Council contracts and subcontractor services are reverse charged where RCT applies (Revenue, reverse charge in construction). Bi-monthly returns, paid by the 19th, or the 23rd via ROS (Revenue, When VAT becomes payable).
- RCT: deduction at 0%, 20% or 35% as Revenue authorises for each subcontractor; paid by the 23rd of the following month via ROS (Revenue, RCT guidance and Tax and Duty Manual Part 18-02-01).
- Employer PRSI Class A: 11.25% to 30-Sep-26, 11.40% from 1-Oct-26 (gov.ie PRSI Class A rates). PAYE, USC and PRSI remitted by the 14th, or the 23rd via ROS (Revenue, Paying your employees' tax).
- Corporation tax 12.5% on trading profits; small company preliminary tax by the 23rd of month 11 at 100% of the prior year, balance by the 23rd of month 9 after year end (Revenue, Tax and Duty Manual Part 41A-07-02).
- SEAI National Home Energy Upgrade Scheme: grants discounted from the homeowner price and paid to the One Stop Shop (SEAI homeowner guide). The model treats the grant as part of the VAT-inclusive consideration; confirm with the VAT adviser for a live client.

## Headline results (Base scenario)

- FY2026 outlook: revenue EUR 9,197k, gross margin 18.3%, EBITDA EUR 424k (4.6%), net profit EUR 241k. Year to date revenue is 19.5% behind budget: late contract starts, a retrofit coordinator vacancy and margin fade on one office fit-out.
- FY2027: revenue EUR 9,703k, EBITDA EUR 264k (2.7%); overheads and site hires rise ahead of secured work.
- Cash: EUR 633k at Dec-26; 13-week low EUR 230k in the week ending 2026-11-27, when PAYE, RCT, VAT and preliminary tax fall due in the same week as the subcontractor payment run; EUR 320k of tax leaves in the weeks containing the 23rd.
- Order book: secured work in hand EUR 5,341k at Aug-26 (6.5 months), EUR 2,860k by Dec-26; weighted pipeline EUR 3,107k. Peak bond facility utilisation 88.3%.
- Downside: FY2027 EBITDA EUR -181k, lowest cash EUR -27k (overdraft needed). Upside: FY2027 EBITDA EUR 735k.

## What the workbook contains

- **Cover, Guide, Checks, CFO_Report, KPIs**: Front page, method, 37 live checks, the management report with live figures, KPI dashboard with four charts
- **PL, BS, CF**: Monthly three statements by segment; balance sheet check row; indirect cash flow reconciled to direct
- **BvA, Scenarios, Cash_13W**: Budget vs actual with a gross profit bridge (volume vs margin by segment); three scenarios; 13-week direct cash forecast with day-of-month timing rules and reconciliation
- **Project_Profitability**: Margins per contract and programme, then full allocation of labour recovery variance, fleet and overheads to EBITDA; tender vs forecast margin; cash collected less cost
- **WIP, Residential**: Percentage of completion engine per named contract; volume-driven residential programmes with billing stages and SEAI claims
- **Pipeline, Variations, Commitments**: Order book, cover and book-to-bill; variations register with policy and scenario outcome; commitments vs cost to complete
- **Retentions, Receivables, Payables, VAT_RCT, AR_Aging, AP_Aging**: Retentions both ways and legacy disputes; receivables by customer group; payables with RCT; VAT and RCT rolls; agings tied to the ledger
- **Resources, Overheads, Fixed_Assets, Debt**: Site headcount, utilisation and labour recovery, agency and fleet; office payroll and overheads; assets; hire purchase, overdraft and performance bonds
- **Project_Register, Assumptions, Inputs_Actuals, Budget_FY26**: Contract master data; rates, timeline and scenario drivers; actual months and opening balances; the original budget

## JSON files for the website

One file per scenario plus a summary, evaluated values only: `meta` (status, validation), `timeline`, `sheets` (every monthly sheet as keyed rows with label, basis, section, format, 24 monthly values and FY totals) and `tables` (assumptions and drivers, project register, variations, commitments, project profitability, pipeline, BvA with bridge, scenarios, 13-week cash with weekly values and levers, checks, agings, CFO report sections). `pct` values are ratios; costs are negative where the sheet shows them negative; dates are ISO strings.

## Simplifications (documented, not hidden)

- Forecast receipts and payments are sized by payment days on invoiced amounts; actual months use bank figures.
- Residential contract assets and liabilities are netted at programme level.
- Own labour is charged to jobs as a share of direct cost at a standard rate that recovers payroll at 85% utilisation; hours above capacity are bought from an agency.
- Corporation tax accrues on cumulative profit per year with no loss carry-forward; capital allowances assumed equal to depreciation.
- Performance bonds run from award to practical completion; reduction during the defects period is not modelled.
- The information-needed list on the Guide sheet and in section 9 of the CFO report names the documents that turn each simplification into evidence.

## Conventions

Blue on yellow = input, black = formula, green = link from another sheet. Row 5 flags actual (A) and forecast (F) months; formulas switch on it. No hard-coded numbers inside formulas. Arial throughout, BriQs colours.