Excel cash flow prognosis for Exact Online (consolidated across companies)

Go to Dutch version

In this article you’ll learn how to easily create and maintain a cashflow prognosis statement almost automatically in Excel for your company(s) based on financial data from one or more Exact Online companies. For this you can download and use the free Excel model. The cash flow prognosis in the completed Excel template can form the basis for a financial plan or a rationale for increasing the current account to ensure there is always sufficient liquidity for operations or investments.

The Excel template can also be used as a cashflow prognosis statement in combination with other accounting systems or manual use as long as the cash, expected income and expected expenses are entered automatically or manually in the specified worksheets.

This Excel prognosis works on the basis of individual cash flows. For simple column balances across one or more companies, the Exact Online Excel formulas described in The most popular Exact Online Excel add-in formulas will suffice.

The first time you use this model with the Excel add-in for Exact Online you will receive one hour of free introduction. Upon installation a link will be automatically sent to you by email. The automatic filling of the cash flow prognosis can be further discussed and explained during this hour.

Get started right away with the cash flow prognosis

No time to read everything? Establish a forecast for your businesses right away with the following steps:

  • Download the free Excel model.
  • Download and install Invantive Control for Excel.
  • Close Excel completely.
  • Restart Excel.
  • Choose six months of free use with the “Try all features” button.
  • Open the Excel file.
  • A login window to Exact Online appears.
  • Enter username and password.
  • Click on “Sign In.”
  • Enter the verification code.
  • The link with Exact Online is established automatically.
  • Choose “All Companies” and “Allow” if this is the first time you are using Invantive Office on Exact.
  • (Optional) Select the desired companies. Or leave the default Exact company selected.
  • Choose “Yes” to the question if you want to download data or use the green “Sync” button in the ribbon “Invantive Control” in Excel.
  • The figures and charts in the cash flow statement will be updated.
  • Vary settings such as the start date on worksheet “Parameters”, delays for sales and purchase orders or play with the one-time and repeating items on worksheets “Manual” and “Repeat”.

Cash flow prognosis (also known as “cash flow forecast”)

A cash flow prognosis provides guidance for assessing whether the company has sufficient capital and whether the cash flow is proceeding as desired. The real-time link with Exact Online gives you instant financial insight and saves you overhead costs because the preparation of the cash flow forecast is largely automatic at the touch of a button. The integration with Exact Online also ensures that the financial details can be easily traced and reconciled with the accounting records.

In the Excel cash flow prognosis, these elements are taken from Exact Online:

  • Current bank balances and any cash positions, found under “Financial” and then “Bank and Cash”.
  • Payable purchase invoices, found in Exact under “Purchasing” and “Outstanding items”.
  • Payable sales invoices, found under “Sales” and “Open items”.
  • Open purchase orders, found in Exact under “Purchasing” and then “Orders”.
  • Open sales orders, found under “Sales” and then “Orders”.

The cash flow forecast also works when the Exact Online trading module is not used or is not in the Exact subscription. In this case the income and expenses for orders are not used and numbers are calculated solely based on the other financial figures such as invoices.

The financial data from Exact Online is combined with cash flows for income and expenses which are each in a separate Excel worksheet:

  • One-time expenses and income, for example for budgeted or planned investments or large real estate transactions,
  • Recurring expenses, for example, holiday money every May, rent bills every three months, and salary every month.

With the one-time and recurring income and expenses, also sub-ledgers which are not recorded in Exact Online can be included in the cash flow forecast. For example a complex project register, portfolio management or complex subscriptions.

Next to a graphical representation of the cash flow as shown above, a detailed overview is also shown per type of cash flow and incoming/outgoing direction:

The Excel sheet also works without an integration with Exact Online. In order to automatically load the financial business data from Exact Online into the Excel sheet Invantive Control for Excel is required. The first six months of use are free of charge. If after that you still want to use it, most users choose for Invantive Office for Entrepreneurs. This costs EUR 49 for the first 100 companies and two users. Within Invantive Office Invantive Cloud is also included with the Power BI driver for Exact Online.

Consolidating Multiple Exact Online companies

The cash flow statement is just as easily based on figures from one as from multiple Exact Online companies. Also the consolidated analysis of the cash flow statement over 100 companies in one go is easy. The list of open sales invoices for example no longer contains solely one company but 100 companies.

The selection of companies is done in the regular way with Invantive Control for Excel:

To compare apples to apples, the companies do need to have similar setup, the most important of which are:

  • similar type of business in terms of business model,
  • same currency for accounting,
  • accounts updated for bank balances, purchases and sales.

In general it is not necessary for each company to have the same financial calendar in Exact Online in terms of number of financial periods or financial period length. Most companies usually use month as financial period, but a financial period by quarter or year has no impact on the cash flow prognosis.

If there are multiple companies then it is convenient when they are all either member of the same fiscal unit for VAT and taxes, or all companies have the same VAT period length.

In case of doubt always discuss the principles and the liquidity budget with the accountant before you draw any conclusions!

The real time import from Exact Online usually takes a few seconds per selected company. If multiple companies are selected then usually eight companies will be downloaded at the same time.

Free Excel cash flow forecast model

The cash flow statement works on the basis of a free Excel calculation model. As an entrepreneur, a cash flow prognosis gives you insight into where the business stands and when a business may be tight on cash. A handy entrepreneur detects the possible risky situations and adjusts his behavior accordingly by, for example, delaying payments, postponing investments or bringing future revenues into the present.

You may refine, modify, extend, distribute or share the cash flow model as you see fit. You may also sell your own improvements as a new calculation model without mentioning the source.

Besides the cash flow model you will need the Windows version of Excel 2013 or newer, plus Invantive Control for Excel:

How reliable is a forecast?

The free cash flow model is a simple model for SMEs that primarily trade goods and is offered free of charge as a sample. The Excel model can forecast liquidity for such companies. However, for other typologies of business, adjustment is often required; for example, for real estate development, this model will not suffice as it does not look at the typical cash flows when developing properties or commercial real estate.

For project organizations with Exact Online Hours & Projects, Get My Report includes a useful report for cash flow forecasting: see Kostenboekingen uren voor Exact Online Uren & Projecten (Dutch).

A word of warning: a cash flow forecast is also not a certainty. A cash flow forecast is just a forecast of what the future might look like. With adjusted numbers or behavior, the forecast for your business changes. A cash flow forecast can additionally be based on wrong assumptions or quite simply because just wrong numbers are in Exact Online or the accounting records are not updated at all or partly. So always take a critical look at the outcome of the cash flow statement!

The model is deliberately kept as simple as possible; countless refinements can be thought of but we have learned that SME entrepreneurs appreciate the confidence in traceability and verifiability of a forecast more than complex cash flow forecasts with relatively small corrections. An S-curve (sigmoid function) such as in Invantive Estate works great to model large complex processes, but the underlying methodology is not easy for every entrepreneur to grasp and apply.

The Excel model is deliberately not based on a cash flow forecast based on actuals in the general ledger and possibly budget on general ledger account level; an approach based on the general ledger is only useful for SME entrepreneurs if the turnover and costs show a long-term stable development and if, for example, a recurring seasonal pattern is present every year.

The VAT is only included in this model because the bank balances of the current period may contain a provision for the VAT that has yet to be settled with the state. Depending on the VAT period length and - assuming the invoice system - an adjustment may be required for the distribution of costs and revenues. Such a one-time or varying VAT correction can easily be added as a line in, respectively, the worksheet “Manual” or “Recurring”.

Finally, note that this Excel model is about cash flow prognosis; it is about how much money is available. Accounting reality may be different. For example, depreciation is not found in a cash flow statement. A depreciation is just shifting money on paper. Once a piece of equipment or car is technically written off and a replacement is needed for continuity, the investment will have to be made with real money. That investment in company fixed assets then shows up under “Manual” as a one-time investment.

How is the forecast determined?

How is liquidity determined?

The cash flow forecast is determined by increasing and decreasing current liquidity (bank and cash) based on planned income and expenses over time. The planned expenses and income are also called the cash flow. A more detailed explanation of cash flows and the Net Present Value (NPV) can be found in this topic, including pictures (Dutch).

All planned expenses and income are each put down to a future date. The amount of cash flow is the amount of money involved, for example, the outstanding amount of a sales or purchase invoice. The future date is determined by the moment at which the money is expected to arrive or be paid. For a sales or purchase invoice, this is the due date. For orders it is again somewhat more complex; the logic of this can be read further on under “Period length and settings”.

All cash flows in a bucket with dates are added together and lead to a change of the liquidity over that cash flow period:

new liquidity = old liquidity + income - expenses

An overview of cash flows by period is provided on the Overview worksheet. Transaction-level details can be found in:

  • Banks and cash: worksheets “Cash Accounts” and “Cash”.
  • Sales invoices: worksheet “AR”.
  • Purchase invoices: worksheet “AP”.
  • Sales orders: worksheet “SO”.
  • Purchase orders: worksheet “PO”.

On the worksheet “Balances” you will find the balance positions of all general ledger accounts in all selected Exact Online companies.

Cash flow period length and settings

The cash flow statement can be set up per day, week or month; all planned incoming and outgoing cash flows in this period are then aggregated into one amount per period across all companies. Supplement this amount with sufficient funds from outside so that there are sufficient available funds to meet all obligations. Any gaps in the financial position in a given period can be resolved by, for example:

  • increasing the current account by the entrepreneur
  • increase the current account credit
  • attracting loan capital
  • improving debtor management by prompt payment
  • delaying expenditure

This mathematical model also shows the planned total incoming and total outgoing cash flows in a cash flow period. This makes it easier to see whether risks are created by a large payment arriving a few days late, while a large amount must be paid.

For the short term, cash flows are usually forecast per week; for the longer term, it is usually per month. The dynamics are also different: cash flows entering and leaving the company far in the future can often be influenced more easily, for example by applying different conditions.

The period length for cash flows can be set in the “Parameters” worksheet:

Set period length for cash flows

The start date on which the projection of cash flows starts can also be set here. Usually today’s date is set here or the date when the records were last updated.

For purchase orders and sales orders a shift in time can be specified if the cash flow date cannot be determined based on the related invoices as visible in the above picture. The logic for determining the cash flow date of sales orders and purchase orders is identical:

  • if invoiced: invoice due date.
  • if delivered: delivery date plus number of days after delivery as set above.
  • other: order date plus number of days after order placement as set above.

And if they don’t pay now?

Payment problems are of all times. As a business owner you can anticipate payment problems. Sales invoices, but also purchase invoices, that have already expired are a source of concern. In the cash flow report they are therefore put in a separate row in the “Overview” worksheet: see the red 2 between the initial liquidity (red 1) and the first period:

Overdue invoices

In the example above, there is 1.6 million in planned revenue and 2.3 million in planned expenses that should have already occurred. Often these are relatively silly items such as credit notes that have not yet been settled with the original note. The real details can be found in worksheets such as “AR” (sales invoices) as “AP” (purchase invoices). But a look at the individual types of cash flows that have not been realized often already gives an insight into where the problem is:

Overdue cash flows

In this case, the bulk of the amounts are in credit notes that have not been settled, and especially in unpaid sales orders (9 tons) and purchase orders (1.3 million). So the purchasing/sales department can provide clarity on this, and the financial department can figure out why there are still so many credit notes not processed.

Manual Refinements.

As previously reported, you can adjust the forecast for one-time and recurring items that are not administered in Exact Online or that the model does not use.

The one-time transactions can be recorded in the “Manual” worksheet:

Repating transactions can be recorded in the “Repat” worksheet:

Which Exact Online tables are used?

Some users prefer to recreate the Excel model in Power BI, for example. That’s no problem. In this chapter I will explain which data is used through Invantive SQL which also powers the Power BI connector.

The Excel model reads multiple Exact Online tables across all selected companies. The cash flow forecast uses the following Exact Online tables:

These tables and associated queries can be found through the Modeler ribbon in Excel:

  • Click on the “Design Mode” button.
  • Click on the “Edit” button.
  • Click on a block of your choice such as “ar”.
  • In the “Facts” tab, the query is such as:

or as Invantive SQL:

select division_code
,      outstandingitems_ap_account_code_attr
,      outstandingitems_ap_account_name
,      number_attr
,      duedate
,      invoiceamtdc
,      outstandingamtdc
,      description
from   exactonlinexml..APOutstandingItems
order
by     outstandingitems_ap_account_code_attr
,      duedate