Skip to main content
Forecast VAT by modelling two movements: VAT captured during the month, and settlements with the tax authority (either payments or refunds depending on your VAT position). Balance sheet forecasting is always a function of last period’s value, additions, and detractions. See BS fundamentals. For VAT, this means VAT balance last period + sales VAT in period − cost VAT in period − settlements with authorities.

​
What approach to use

Driver-based is the right default. Link sales and cost VAT dynamically to the P&L lines that generate them. When revenue and cost assumptions change, the VAT balance updates automatically. Use statistical when VAT exposure is stable month to month. A rolling average of recent actuals is accurate enough for most businesses and avoids rebuilding the dynamic link from scratch. Hardcoded is rarely the right fit for VAT. The most common case is copying an existing Excel budget directly into Francis. Use it as a starting point and migrate to driver-based once the model is set up.

​
Where to forecast VAT

  • Supporting sheet: right for most cases. Put the Income incl. VAT and Costs incl. VAT subtotals in a separate sheet as intermediate calculations. It keeps the VAT logic out of the P&L and makes the dependency explicit. The VAT rows on the balance sheet reference those subtotals directly.
  • Directly on the balance sheet: possible for simpler setups or when using statistical. Tracing which income and expense lines feed the VAT balance becomes harder to follow as the model grows.

​
Forecasting approaches

Set up the supporting sheet
  1. Create a dedicated sheet and add a section called VAT forecast.
  2. Add a VAT % row and enter the applicable rate (25% in Denmark).
  3. Create a group called Costs incl. VAT. Add a calculation row for each cost line subject to VAT at the granularity your model needs: one per GL account, or one per cost category. Each calculation pulls in that cost value from the P&L, so the group subtotal gives total costs incl. VAT.
  4. Create a group called Income incl. VAT and do the same for income lines.
  5. Add a Captured cost VAT row below the groups:
= "Costs incl. VAT"[0] * "VAT %"[0]
  1. Add a Captured income VAT row:
= "Income incl. VAT"[0] * "VAT %"[0]
On the balance sheet, use three rows.Cost VAT takes last month’s balance and adds this month’s newly captured cost VAT:
= "Cost VAT"[-1] + "Captured cost VAT"[0]
Sales VAT does the same for income:
= "Sales VAT"[-1] + "Captured income VAT"[0]
Sales VAT and Cost VAT individually grow in perpetuity. Each month adds to the prior balance without any settlement logic on those rows. The Settlement row provides the offset. What matters is the sum of all three rows, which represents the net VAT position on the balance sheet.
The Settlement row formula depends on your settlement frequency.Monthly settlementSettlement clears the prior month’s accumulated balance every month:
= "Settlement"[-1]
- "Sales VAT"[-1]
- "Cost VAT"[-1]
After settlement fires, the sum of all three rows reflects only the VAT captured in the current month. All prior accumulated balances have been cleared by the settlement row.Quarterly settlementSettlement fires in the third month of each quarter and clears the prior quarter’s net by taking the difference between two consecutive quarter-end balances:
= "Settlement"[-1]
+ if_quarter_month(3) * ("Sales VAT"[-3] + "Cost VAT"[-3] - "Sales VAT"[-6] - "Cost VAT"[-6])
This settles Q1 in June, Q2 in September, Q3 in December, and Q4 in March, aligned with the standard Danish quarterly VAT schedule. Adjust the month offset if your jurisdiction settles on a different cycle. Between settlements, the NET VAT balance accumulates and represents the outstanding obligation for the current quarter to date.The cash flow picks up the change in the Settlement row each period automatically. When cost VAT consistently exceeds sales VAT (common in export-led, investment-heavy, or B2B businesses with limited domestic sales), the balance carries a receivable and the settlement becomes a cash inflow. The sign of the balance tells Francis whether a payment goes out or a refund comes in.
When setting up the supporting sheet, pull the opening VAT balance with a formula, so the starting value sits in the actuals layer and feeds the forecast automatically. As you move the Forecast Start date forward, periods that were forecast become actuals, and the formula keeps sourcing each opening balance from the actuals layer with no manual update.