Skip to main content
Forecasts Guide

Building Data Streams, 4 Forecasting Methods, Assembly, Layout, Stream Maintenance, and Fine-tuning

David Taylor avatar
Written by David Taylor
Updated over a week ago

Introduction to Forecasts

To start forecasting in Trovata, you first need to define the purpose of the forecast and your intended audience.

Some examples may include: general liquidity planning, anticipating liquidity choke points due to growth, investments, black swan events, or simply to communicate the health of the company to key stakeholders.

Here is a summary of how to create Forecasts inside Trovata:

  1. Tag 90-100% of historical transactions (based on total cash amount) for the cash flows you'd like represented in the forecast

  2. Define the assumptions for all the sources and uses of cash via data streams

  3. Use your assumption methods to create data streams in Trovata

  4. Create the forecast and choose the data streams you've created

  5. If applicable, create forecast scenarios and forecast roll-ups

Based on your goals and the intended audience, you will first create Tags inside Trovata. These tags need to match the categories of cash flows that are important for your audience to see when reviewing your forecast (i.e. AR, AP, Payroll, Taxes, etc.). These tags will also be used to track actuals for variance reporting.

Once Tags are created, you'll navigate to the Forecasts module inside Trovata to begin building Forecast data streams. These data streams represent the cash flow activity within your forecast. A stream can represent a cash inflow or outflow.

Within Trovata, Data Streams are the main building blocks of each forecast. There are various methods for creating data streams. These methods can be used interchangeably within a forecast, meaning you can build a forecast using multiple assumptions methods.


Overview of Data Streams

The Stream types are as follows:

  • Data Stream (Consists of Machine Learning, Repeat History, and Manual input Data Streams)

  • Invoice Stream

Data Streams utilize historical transaction data to predict future cash flows in the Forecast model. These streams can be driven by machine learning, repeating historical data, or manual input.

Invoice Streams leverage open receivable and open payable invoices from your ERP system to predict future cash flow in the Forecast model.

As you may have learned in previous training sessions, Tags are the foundation to reporting and forecasting in Trovata. The Machine Learning and Repeat History data streams are most valuable & effective when leveraging Tags. As a refresher, tags help users categorize their historical data into the respective cash flow activity buckets (i.e. AR, AP, Payroll, etc.). Within forecasting, users can use these pre-identified datasets (tagged data) to make future cash flow projections.

Data Stream creation is a straightforward process but will differ slightly depending on the stream type. The following modules will explain the uniqueness of each stream!


Machine Learning Data Stream

Machine Learning Stream: Data stream produced by providing historical transaction data to the system in order to run multiple Machine Learning (ML) scenario models.

Use Cases:

  • Recurring, high volume, high frequency cash flow activities.

  • Examples: Accounts Receivable, Daily Credit Card Receipts, Daily Deposits, etc.

  • Note: Successful users should expect to use the ML output as a baseline, and will likely need to make manual inputs on top of this to account for sharper spikes in cash flow.

Min/Max Value: These fields are used to disallow values above or below a specified threshold.

  • Example 1: Min field set to 0 means no negative values can appear in the data produced by the model.

  • Example 2: Max field set to 1,000,000 means no value higher than 1,000,000 can appear in the data produced by the model.

4 Model Outputs (General Guidelines)

  • Model 1: Accurate and fast, meshes well with weekly seasonal trends but poorly on stock-like graphs, built from multiple regressors.

  • Model 2: Meshes well with general seasonal trends but poorly on stock-like graphs, built from multiple regressors.

  • Model 3: Great at weekly, monthly, and quarterly seasonal trends, performs poorly on stock-like data, built from lagged based regressors.

  • Model 4: Works well for arbitrary stock-like data, performs poorly on fluctuating seasonal trends, built from a system of neural networks.


Repeat History Data Stream

Repeat History Stream: Data stream that uses a section of historical data (i.e. 3 months or 13 weeks, etc.) and copies it forward. This is typically used in conjunction with percentage growth/decline factors.

Use Cases:

  • Historically low volume/infrequent cash flow activities that are transacted on a regular cadence (i.e. monthly or weekly)

  • Examples: Payroll, Benefits, Taxes, etc.

Oftentimes manual adjustments and factors are used in conjunction with one another to have data stream values fall on the correct respective days. For example, when importing a repeat history data stream, users will need to line up the day(s) of the payroll release with the new calendar day. If payroll typically releases biweekly on Thursdays, users will need to ensure the values for the repeat of history are imported/reflected on the proper day.


Manual Data Stream

Manual Stream: Data stream constructed entirely by one-off user inputs and/or a CSV file upload.

Use Cases:

  • One off, sporadic cash flow activities and/or recurring low volume cash flow activities

  • Examples: Debt financing, Rent payments, Stock repurchasing, Acquisitions, etc.


Invoice Data Stream

Invoice Stream: Data stream created using open AR/AP invoices that have been pulled into Trovata via a direct ERP connection.

Use Cases:

  • Project AR/AP directly from an ERP (Netsuite, SAGE, Oracle Fusion).


Stream Maintenance

Appending Values

It is important to note that once you create a data stream you can always make edits. To make changes to the existing setup, navigate to the top right corner, select the 3 dots, and then select the ‘Append Values’ option.

Within the 'Append Values' menu, there are various actions you can perform:

  • Manually Update Forecast or Actual Data

    • To make changes, simply type over the existing values

  • Import Data from an Analysis configuration (explored in the Repeat History Data Stream module)

  • Use Auto Entry (explored below)

  • Upload a CSV (explored in the video displayed in the Manual Data Stream module)

Auto Entry Options

Note: These options can be applied to either Forecasted or Actual values

  • By Cadence: From a particular date onwards, you can create, replace, or add to an existing amount and repeat it out for a particular cadence and number of periods. This is commonly used for cash flow streams like payroll. Example: Weekly payroll amount of $500,000 for the next 52 weeks.

  • Repeat Data: Serves in the same function as the Repeat History Data Stream by taking a set of data (over a specified data range) and repeats it moving forward, with the option to add factors.

  • Weekend Data: If values in the data stream fall over the weekend you can choose how you would like for them to be treated. Options include:

  1. Spread over the week (previous week or following week)

  2. Add to day (previous Friday or following Monday)

  3. Split evenly (over the previous Friday and following Monday)

  4. Split (Saturday's value is added to the previous Friday and Sunday's value is added to the following Monday)

  5. Remove (removes all weekend data)


Assembling the Forecast

The main ingredients we need to assemble a forecast are:

  • Data Streams

  • Knowledge of accounts to be included (this will determine the cash balance represented in the Forecast)

  • Other existing forecasts (optional). This is common and applicable for companies who want to forecast for multiple business units (Entities/Regions/Divisions), often in different functional currencies, yet want a consolidated view to roll up into their group reporting currency.

To create the Forecast, choose New Forecast in the top right hand corner of the screen.

Give your Forecast a name, choose the base currency for the Forecast, as well as the default Cadence in which you wish to view your forecast output in - e.g. daily, weekly, monthly, or quarterly. You also have the ability here to select your rounding treatment. (No Rounding is the default method and most commonly used). All of these options can be changed later on.

Next, choose the bank accounts that correspond with the cash flows you are forecasting. This may be all accounts if you are forecasting for the company as a whole, or just a select few if you are only forecasting for a specific operating account or an entity.

Choose the data sources for the forecast. These are most likely going to be the data streams you created earlier. You may notice that you can also choose other forecasts as data sources - giving you the ability to create a Forecast of Forecasts, or also know as “Forecast Roll-Ups”.

As an option, you can apply Global Factors to your forecast. This feature is popular when creating multiple forecast scenarios or for more advanced forecasting.

Finally, you will be able to review all the settings and inputs to your forecast before clicking Submit.



Understanding the Forecast Layout

Congratulations! You should now see your forecast inside of Trovata! Much like Reports, Forecasts are laid out with a visualization component on the top side of the screen, and a table of all your data down below. In order to override or update the forecast or actual data, you will need to visit the underlying data stream and select edit to append any values.

The Forecast line items will each have a letter next to them indicating what type of line item is being represented. The different letters stand for the following:

S = Stream

If you click into this line item you will be directed to the data stream. Here you can append the forecasted and actual values so long as you have the required access to do so.

G = Group

Here you are able to bucket a number of data streams under one group and see the total amount for these streams rolled up. You can click into the group at any stage to drill down into the individual data streams and see their individual amounts.

F = Forecast

Here you are able to see an overall forecast view. You can click into the forecast to further see the line items and detail that make up this forecast.

Each line item will include:

  • (F) a line for the forecasted amount.

  • (A) a line for the actual amount. If this amount is made up of multiple transactions you will be able to click into the amount to see the underlying transaction breakdown of the amount.

  • (%) variance difference between forecast and actual amounts shown as a percentage.

Total Cashflow: The sum of the total line items above.

Actual Cash Balance: The sum of the closing account balances for that day.

Daily Cadence Forecasted Cash Balance: Forecasted cash balance for current day is equal to the previous day actual cash balance + current day forecasted total cash flow.

Weekly Cadence Forecasted Cash Balance: Forecasted cash balance for current week is equal to the previous week actual cash balance (Actual CB shows the balance as of the close on Friday) + current week forecasted total cash flow.

Monthly Cadence Forecasted Cash Balance: Forecasted cash balance for current month is equal to the previous month actual cash balance (Actual CB shows the balance as of the close of the last day of the month) + current month forecasted total cash flow.

Quarterly Cadence Forecasted Cash Balance: Forecasted cash balance for current quarter is equal to the previous quarter actual cash balance (Actual CB shows the balance as of the close of the last day of the quarter) + current quarter forecasted total cash flow.


Fine-tuning the Forecast

As previously mentioned, once you create a forecast, it's very simple to make changes to the existing setup. Simply navigate to the top right corner and select the 3 dots. More detail on the various actions you can perform can be found below:

Settings - General

You can change general details like the name of the forecast, default date range, currency, cadence, and rounding figures.

Settings - Accounts

Here you can add or remove accounts to your forecast.

Settings - Data Sources

Here you can add or remove streams or forecasts to/from your forecast.

Settings - Groups

Here you can create Groups and bucket your data streams. Example: Receipts & Disbursements or Inflows & Outflows.

Settings - Factors

Here you can add factors to individual data streams or entire forecasts. There are two types of factors.

  1. Growth - These are typically used for scenario analysis. You can apply a positive or negative percentage growth factor to a specific date range. For example, you might apply a 2% increase in payroll for the next quarter.

  2. Shift - This is used to shift values forward or backwards by a specific number of days. E.g. If Payroll moves from a Monday to a Wednesday, you can use the Shift option to easily manage and reflect this change.

Duplicate

Duplicate a forecast for scenario analysis or simply just to use a previous forecast as a baseline.

Export

Ability to export the forecast to PDF.

Version History

An audit trail to track if and when changes are made to the forecasts.

Delete

You will be prompted with an “Are you sure?" warning message "Deleting this forecast is permanent and cannot be undone". Click ‘Yes’ to proceed or ‘No’ to cancel.

Changing the Cadence View

You can change the cadence view of your forecast at any time, either through settings or an ‘on the fly’ change. Daily, Weekly, Monthly & quarterly are your current selection options.

Edit Stream and Group Sort Order

Data Stream and Stream Group ordering can be changed by toggling the pencil icon.

Toggle on/off Actuals and Variances

Here you have the ability to show or hide either actuals or variances in your view.

Exporting to Spreadsheets

If you want to export this data into a spreadsheet, you can use Grid Mode via the waffle icon and click the top left most cell to highlight everything for a copy/paste of the data.


Summary of Forecasts

Cash forecasting is important because it provides visibility into all the variables that could affect future liquidity. The most important aspects of cash forecasting are the assumptions used to create the forecast. We designed forecasting in Trovata to give our customers an Excel alternative to store and manage those assumptions, as well as provide automated reporting of actual cash flows for cleaner variance reporting.

After creating the forecast and monitoring for variances, you will learn new insights about your cash flows. Also, by centralizing your Forecasts inside Trovata, you can give your team access to own and control their data, all while avoiding Excel errors, file corruption, and manually reporting cash flow actuals.

Did this answer your question?