Table of Contents
101: Intro to 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.
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:
Tag 90-100% of historical transactions (based on total cash amount) for the cash flows you'd like represented in the forecast
Define the assumptions for all the sources and uses of cash via data streams
Use your assumption methods to create data streams in Trovata
Create the forecast and choose the data streams you've created
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.
201: The Best Way to Create a Stream
Overview
Data streams are the building blocks of each forecast in Trovata.
If you would like to learn how to create a new data stream, keep reading.
Combinations of the methods described below can be used within a forecast, meaning you can build Forecasts using multiple stream creation methods.
There are two stream types:
Data stream
Invoice stream
Data Streams utilize a direct entry method to managed cash forecast values. Data streams can be populated using machine learning, repeating historical data, or manual entry.
Invoice Streams use open receivable and open payable invoices from an ERP system to predict future cash forecast values.
The following sections will explain how to create each stream type and explain the unique features available for each stream creation method.
Trovata forecast streams support forecast data up to one (1) year.
Stream creation basics
To create a stream, access the Data Stream library by clicking Data Stream in the Trovata menu.
To create a new stream, click New Stream in the top-right corner.
Using Machine Learning
Machine Learning (ML) based streams are produced by providing historical transaction data to multiple forecasting models.
Use Cases:
Recurring, high volume, high frequency, or seasonal cash flow activities.
Examples: Accounts Receivable, Daily Credit Card Receipts, Daily Deposits, etc.
To create a Machine Learning (ML) based streams click New Stream → Data Stream.
Input the Stream Name and choose a Currency for this Data Stream.
Click Next.
Choose the Analysis option and click Next.
Using the Search bar or Filtering menu, Data Sources can be found by searching the Trovata database of bank transactions.
Trovata best practices suggests choosing previously Tagged data as the Forecasted Data Source when using Machine Learning.
After choosing a Tag (or group of Tags) as the Forecasted Data Source, it is visible for reference under the Search bar.
By default, the previous 13 weeks of transaction data will be used for the Machine Learning analysis.
Click the Date Range picker to choose a specific period of time to use for the Machine Learning analysis methods.
There technically is no minimum or maximum when using the model date range selector, but the generally, the more data the better.
Trovata best practices suggest setting the start of the Model Date Range on the day a transaction occurred and end on the day a transaction occurred.
Trovata best practices suggest that if your business experienced turbulence in a certain month that isn't representative of your cash flow, it may be helpful to not include that month in the Model Date Range.
The transactions result from the search and date range option appear below the search bar.
Click Next: Forecast Method → Machine Learning (ML).
As a result, Trovata’s Machine Learning models will analyze the Forecasted Data Source and provide four (4) scenario models.
Trovata uses Facebook’s Prophet forecasting procedure for forecasting time series data based on an additive model where non-linear trends are fit with yearly, weekly, and daily seasonality, plus holiday effects.
Facebook’s Prophet works best with time series that have strong seasonal effects and several seasons of historical data.
Facebook’s Prophet is robust to missing data and shifts in the trend, and typically handles outliers well.
Prophet is open source software released by Facebook's Core Data Science team.
General Guidelines for Model Outputs
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.
Minimum and maximum values for the Machine Learning results can be applied to restrict the result values.
Min/Max Value: These fields are used to disallow values above or below a specified threshold.
Example 1: For positive value results, if the Min Value field is set to 0, then no negative values can appear in the data produced by the model.
Example 2: For positive value results, if the Max Value field set to 1,000,000, then no value higher than 1,000,000 can appear in the data produced by the model.
Example 3: For negative value results, if the Min Value field is set to -600,000, then no negative values less than -600,000 can appear in the data produced by the model.
Example 4: For negative value results, if the Max Value field is set to -300,000, then no negative values greater than -300,000 can appear in the data produced by the model.
Click Done.
Review the data configuration and click Next.
For variance reporting, bank actuals need to be reported against the forecasted stream data.
Review the selection for how to manage actuals data.
Trovata best practices suggests leaving the pre-selected options as Trovata will track actuals for this stream based on the criteria of the included Tag using for the Machine Learning Analysis.
If the Actuals data configuration needs adjustment, click the three dots to the right, otherwise click Next.
Once a review is complete, click Confirm to finalized the creation of this stream.
Upon stream creation, a graph and data table of the forecast results are shown.
The data table includes forecasted data, the current day actual transactions, and variance by number and percentage.
Actuals will be visible for the current day if intraday reporting is available.
Users should expect to use the ML output as a baseline, and will likely need to make manual changes as assumptions change.
Using Repeat History
Repeat History data stream uses a selection of historical bank data (i.e. 13 weeks, 12 months, etc.) and copies it forward.
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.
Trovata best practices suggesting using Repeat History in conjunction with percentage growth or decline factors, or when historical data is a strong indicator of future projections.
To create a Repeat History based streams click New Stream → Data Stream.
Input the Stream Name and choose the Currency.
Click Next.
Choose the Analysis option and click Next.
Using the Search bar or Filtering menu, Data Sources can be found by searching the database of bank transactions in Trovata.
Trovata best practices suggests choosing previously Tagged data as the Forecasted Data Source when using Repeat History.
After choosing a Tag (or group of Tags) as the Forecasted Data Source, it is visible for reference under the Search bar.
By default, the previous 13 weeks of transaction data will be used for Repeat History.
Click the Date Range picker to choose a specific period of time to use for the Repeat History method.
The transactions result from the search and date range option appear in the graph below, followed by the data table.
Click Next: Forecast Method → Repeat History.
The date in which the Repeat History value begins in the forecasted data is determined by the Import Start Date.
Click Import Start Date and choose from the date picker to adjust.
The historical values can be copied exactly, or a change to be applied to adjust the forecasted values by a percentage or dollar amount.
Click Amount ($) to define a change to the historical data by a dollar amount or percentage (i.e. Amount ($) or Percentage (%) from the dropdown list).
Click Change field to input the value of the dollar or percentage change.
Amount ($) or Percentage (%): These inputs are used to adjust forecast values based on user-defined assumptions. Note that the value here respects absolute values and accepts a negative (-) for assuming a reduction in forecasted values. The results are not compounding.
Example 1: If an expense assumption is being made and a change in the Amount ($) of 100 is applied, the result of the forecast data would be such that each day’s forecasted expense value would be reduced by 100. Meaning, if the historical data value provided was -$2,500, it would be adjusted to -$2,400.
Example 2: If an expense assumption is being made and a change in the Amount ($) of -100 is applied, the result of the forecast data would be such that each day’s forecasted expense value would be increased by 100. Meaning, if the historical data value provided was -$2,500, it would be adjusted to -$2,600.
Example 3: If an deposit assumption is being made and a change in the Amount ($) of 100 is applied, the result of the forecast data would be such that each day’s forecasted deposit value would be increased by 100. Meaning, if the historical data value provided was $1,400, it would be adjusted to $1,500.
Example 4: If an deposit assumption is being made and a change in the Amount ($) of -100 is applied, the result of the forecast data would be such that each day’s forecasted deposit value would be reduced by 100. Meaning, if the historical data value provided was $1,400, it would be adjusted to $1,300.
Example 5: If an expense assumption is being made and a change in the Percentage (%) of 10 is applied, the result of the forecast data would be such that each day’s forecasted expense value would be increased by 10%. Meaning, if the historical data value provided was -$7,500, it would be adjusted to -$8,250 (i.e. 10% of -$7,500 is -$750, added together equal -$8,250).
Example 6: If an expense assumption is being made and a change in the Percentage (%) of -10 is applied, the result of the forecast data would be such that each day’s forecasted expense value would be decreased by 10%. Meaning, if the historical data value provided was -$7,500, it would be adjusted to -$6,750 (i.e. -10% of -$7,500 is $750, added together equal -$6,750).
Example 7: If an deposit assumption is being made and a change in the Percentage (%) of 10 is applied, the result of the forecast data would be such that each day’s forecasted expense value would be increased by 10%. Meaning, if the historical data value provided was $8,500, it would be adjusted to $9,350 (i.e. 10% of $8,500 is $850, added together equal $9,350).
Example 8: If an deposit assumption is being made and a change in the Percentage (%) of -10 is applied, the result of the forecast data would be such that each day’s forecasted expense value would be reduced by 10%. Meaning, if the historical data value provided was $8,500, it would be adjusted to $7,650 (i.e. -10% of $8,500 is -$850, added together equal $7,650).
Click # 1 field here to determine the number of times that the historical data should be repeated in the forecasted data stream.
This defaults value of “1” means that the range of historical data will only be repeated one time.
Example: If 30 days of historical data was chosen, by default only 30 days of forecasted data will be added to the stream. After the 30th day, $0 will be forecasted each day thereafter. However, if the value was changed to six (6), meaning that the 30 days of historical data would be repeated six (6) times. In this case, the forecasted stream would included one hundred and eighty (180) days worth of forecasted data. After the 180th day, $0 will be forecasted each day thereafter.
Click Done.
Review the data configuration and click Next.
For variance reporting, bank actuals need to be reported against the forecasted stream data.
Review the selection for how to manage actuals data.
Trovata best practices suggests leaving the pre-selected options as Trovata will track actuals for this stream based on the criteria of the included Tag using for the Machine Learning Analysis.
If the Actuals data configuration needs adjustment, click the three dots to the right, otherwise click Next.
Once a review is complete, click Confirm to finalized the creation of this stream.
Upon stream creation, a graph and data table of the forecast results are shown.
The data table includes forecasted data, the current day actual transactions, and variance by number and percentage.
Actuals will be reported for the current day if intraday reporting is available for the enabled accounts.
Using Manual Entry
Manual Entry data stream constructed entirely by one-off user inputs and/or a CSV file upload.
Manual data can be entered into the forecasted data stream in the following ways:
Direct Entry
Copy and Paste
Analysis
Auto Entry
CSV 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.
To create a Manual Entry based streams click New Stream → Data Stream.
A New Data Stream creation wizard will open. Begin with Basic Info by entering the Stream Name and choosing the Currency for this Data Stream.
Click Next.
Choose the Manual option and click Next.
1. Direct Entry
Much like a spreadsheet, the Forecast Value column will accept forecast values directly.
Click into the Forecasted Value cell of the row associated with the forecast Date and Type the value as a positive (receipt of cash) or negative number (disbursement of cash).
2. Copy and Paste
Again, like a spreadsheet, the Forecast Value cells can be populated using the Copy and Paste keyboard shortcuts
Copy and Pate Shortcuts
PC: Ctrl + c for Copy and Ctrl + v for Paste.
Mac: ⌘ + c for Copy and ⌘ + v for Paste.
Note that Trovata will respect if dates are skipped in the spreadsheet data, as in the example above.
3. Analysis
Using the Analysis method is the exact same as using the Repeat History method described earlier in this guide.
Click Input → Analysis to access the configuration wizard described previously within the Repeat History section of this guide.
4. Auto-Entry
Auto-Entry provides tools create forecast assumptions By Cadence, using Repeat Data to carrying forward existing data within the stream, or to adjust Weekend Data.
Click Input → Auto-Entry to access the Auto-Entry tools.
4.1 By Cadence
From a particular date onwards, forecast values can be replaced or added to an existing amount and repeated at specific cadence and for a number of periods.
Trovata best practices are to use By Cadence for common recurring cash flows like payroll, debt repayment, or occupancy costs.
Choose a Start Date.
Select Entry Type, Replace existing or Add to existing.
Replace existing will replace whatever existing values are in the Forecast Value field.
Add to existing will add the new value to the existing value in the Forecast Value field.
Input Amount.
Choose a Cadence from the list: Daily, Weekly, Bi-Weekly, or Monthly.
Input the number of Periods that amount should be repeated at the selected Cadence.
Click Submit to save.
By Cadence Scenario
Example: Acme Company’s payroll is $45,000 per week, every Friday. There are also monthly commission payments, paid on the 30th, that average $75,000 per month. To keep things simple, management wants to see both weekly payroll and commission payments consolidated into one data stream within Trovata.
How to create this assumption:
Pick the upcoming Friday payroll payment date from the Start Date picker.
Choose Add to existing from the Entry Type.
Input -45,000 into the Amount field.
Choose Weekly from the Cadence drop down.
Input the value 52 in to the Periods field to repeat the assumption for the next 52 periods (i.e. 52 weeks, or 1 year).
Click Submit. The Forecast Value cells should now reflect that assumption.
Next, click Input → Auto-Entry to open the By Cadence tool again.
Pick the upcoming commission payment date from the Start Date picker.
Choose Add to existing from the Entry Type.
Input -75,000 into the Amount field.
Choose Monthly from the Cadence drop down.
Input the value 12 in to the Periods field to repeat the assumption for the next 12 periods (i.e. 12 month, or 1 year).
After further analysis, Acme management has come back and revealed that commission payments are expected to be much higher than previously expected. The monthly commission value needs to be updated to reflect an average of $160,000 per month.
How to adjust this assumption using the Replace existing Entry Type:
Click Input → Auto-Entry to open the By Cadence tool.
Pick the upcoming commission payment date from the Start Date picker.
Choose Replace existing from the Entry Type.
Input -160,000 into the Amount field.
Choose Monthly from the Cadence drop down.
Input the value 12 in to the Periods field to repeat the assumption for the next 12 periods (i.e. 12 month, or 1 year).
Note: In this example, if a Friday and the 30th of the month fall on the same day, the forecast values will reflect the total amount for the two assumptions (i.e. -160,000 + -45,000 = -205,000).
4.2 Repeat Data
The Repeat Data tool will copy a section of forecast data that has already been entered in a stream and copy it forward.
Forecasted values need to be populated in the stream for this tool to work.
Choose a Date Range to select a range of existing forecast values to repeat.
Similar to the Repeat History method describe in the section above, a Change by Amount ($) or Percentage (%) can be applied to the daily forecast values.
Pick the Start Date that the Repeat Data selection should begin.
Input a value for the Times Repeated field.
Repeat Data Scenario
Example: Acme Company has multiple deposits that occur throughout the week, but are very reliable. Management wants to make an assumption for these deposits to occur every Monday, Thursday, and Friday in the amounts of $7,500, $19,000, and $75,000, respectively, for the next 13 weeks.
How to create this assumption:
Enter the 1st week’s assumption in the Forecasted Value column on the appropriate dates as described in the Direct Entry method in the section above.
Click the Input icon.
Choose Auto Entry from the list of options.
Click the Repeat Data tab.
Choose the Date Range of data to repeat (in this case, the Monday of the first forecasted value through the following Sundays since the entire week’s forecasted values should be repeat).
Since the values will not change, leave the Change field set to 0 and leave the change option default to Amount.
Set the Start Date to the following Monday (i.e. the first week that the data should repeat).
Set the Times Repeated value to 13.
Click Submit.
4.3 Weekend Data
Many companies do not have cash activity over the weekend. In these cases, weekend values that are populated in the forecast stream using any of the methods above can be adjusted.
Options include:
Spread over week (either the previous week or the following week)
Add to day (previous Friday or following Monday)
Split evenly (over the previous Friday and following Monday)
Split
Remove (removes all weekend data)
Forecasted Values need to be present for this tool to work.
Choose a Start Date, which will limited to every Saturday in the date picker.
Input the number of Periods that weekend data should be adjusted.
Pick the Weekend Data option from the dropdown.
Weekend Data Options
Spread over week: this option spreads the original weekend data values evenly across either the previous week or the following week.
Example: The original daily forecast values are -$1,000, even on weekends. After review, management now expects no weekend activity and wants to spread the sum of the weekend across each day of the week.
If the previous week option is chosen, the result would be that each forecasted value for the previous week would be increased by -$400 per each (i.e. -$1,000 [Saturday value] + -$1,000 [Sunday value] = -$2,0000 divided by 5 weekdays. = -$400).
In the case where following week is chosen instead, the result would be the same, but for the following week.
Add to day: this option will move original week data values to a particular day of the week, either the previous Friday, or following Monday.
Example: The original daily forecast values are -$1,000, even on weekends. After review, management now expects weekend activity to occur during the week.
If the previous Friday option is chosen, the result would be the forecasted value for the prior Friday will be increased by -$2,000 (i.e. -$1,000 [Saturday value] + -$1,000 [Sunday value] = -$2,0000 added the current -$1,000 Friday value = -$3,000 forecasted Friday value).
In the case where following Monday is chosen instead, the result would be the same, but for the Monday following the weekend.
Split evenly: this option will take the combined value of the weekend data and split that value evenly across the prior Friday and following Monday.
Example: The original daily forecast values are -$6,000 on Saturday and -$7,000 on Sunday. With Split evenly applied the result would be that the combined value of -$13,000 would be divided by 2, equaling -$6,500. That value, -$6,500 would be applied to the prior Friday forecast value and following Monday forecast value.
Split: this option will take the Saturday forecasted value and move it to the prior Friday, as well as take the Sunday forecasted value and move it to the following Monday.
Example: The original daily forecast values are-$5,000 on Friday, -$6,000 on Saturday, -$7,000 on Sunday, and -$1,000 on Monday. With Split applied the result would be that Friday forecast value would be -$11,000 (i.e. -$5,000 + -$6,000) and the Monday forecast value would be -$8,000 (i.e. -$7,000 + -$1,000).
Remove: this option will remove the forecasted values from the weekend dates.
Example: The original daily forecast values are -$6,000 on Saturday and -$7,000 on Sunday. With Remove applied the result would be that Saturday and Sunday’s forecasts values are both $0.
5. CSV Upload
In addition to copying and pasting data from spreadsheet, .csv files can be uploaded.
To upload a .csv file, click CSV Upload and choose the appropriate file from your device.
Upload .csv files must contain columns for Date and Value.
Trovata best practices are to store forecast values for individual streams across multiple .csv files.
Note that Trovata will respect if dates are skipped in the .csv spreadsheet data.
Click Done.
Review the data configuration and click Next.
Tracking Actuals to Manual Streams
For variance reporting, bank actuals need to be reported against the forecasted stream data.
Review the selection for how to manage actuals data.
Since Manual data entry was used for the forecast method, Manual data entry for reporting actuals will be the default.
Trovata best practices suggest using Tags to track Actuals, even if manual entry is being used to populate forecast values.
To choose an existing Tag to managed Actuals data, click Analysis.
Using the Search bar or Filtering menu, Data Sources can be found by searching the database of bank transactions in Trovata.
After choosing a Tag (or group of Tags) as the Forecasted Data Source, it is visible for reference under the Search bar.
Click Done.
Once actuals data configuration is complete, review and click Next.
Once a review is complete, click Confirm to finalized the creation of this stream.
Upon stream creation, a graph and data table of the forecast results are shown.
The data table includes forecasted data, the current day actual transactions, and variance by number and percentage.
Actuals will be reported for the current day if intraday reporting is available for the enabled accounts.
Using Invoice Streams
Invoice Streams use open receivable and open payable invoices from your ERP system to predict future cash flows.
All invoices data available to use in Trovata can be found within the Open Invoices submenu.
To create an Invoice Stream, click the Data Streams menu item. Then click New Stream → Invoice Stream.
Input the Stream Name, Currency, and click Next.
Click Configure data source.
From this page, the Search bar can be used to find specific invoice data, like Customer/Vendor, ERP, or Invoice Type.
Alternatively, the Filter to the right can be used.
Once the invoice data is defined, a Max Age can be set.
Max Age is used to define the maximum aging of invoices included in the forecast stream results.
Example: An Invoice stream is created to forecast all A/R invoices based on due date and amount. However, there are invoices older than 90 days that management does not expect to receive. In this case, the Max Age can be set to 90 to restrict any invoices with any aging more than 90 days, from being included in the forecast stream results.
Click Done.
Click Next.
Review and click Confirm.
Be aware that Actuals are NOT tracked against Invoice based streams in Trovata.
Trovata best practices suggest that when an Invoice Stream is in play, use Stream Groups to consolidate both an Invoice Stream and Data Stream together to accurately reporting variances.
301: Creating a forecast
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.
Your forecast view will be impacted by your currency selection, which can be edited later at any time.
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.
Trovata best practices suggest using "All Accounts" when creating a company-wide Forecast model and use "Select Accounts" when creating a more specific, entity based Forecast model.
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” (see below).
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.
Forecast-Roll Ups
Forecast-Roll Ups can be used to consolidate forecasts across multiple Entities into a consolidated forecast view.
To create a Forecast-Roll Up, click New Forecast and complete Step 1 Basic Info, followed by choosing the accounts associated with all the Forecasts being consolidated.
Click Add Sources → Select source(s).
Click the Forecasts tab and select the Forecasts from the list that are to be consolidated into a single Forecast-Roll Up.
Trovata best practices suggest creating the individual Entity forecasts first, then create the Forecast-Roll Up.
401: Scenario Modeling
Scenario modeling a forecast in any software is just as much art as it is science.
The #1 question to always ask is: “What question are we trying to answer by modeling this scenario?”
The tools in Trovata to create and manage various scenarios will be covered in detail below:
Duplicate Forecasts
Appending Values
Factors
Duplicate Forecasts
To being modeling additional scenarios in Trovata, it’s suggested to begin by duplicating a “base case” forecast scenario. Usually, these base case scenarios make conservative estimates for all inflows and outflows.
In the “base case” forecast, navigate to the top right corner, select the 3 dots, and then click Duplicate.
Input a New Forecast Name for this new forecast and click Save.
The forecast has now been duplicated with the exact same values, forecast assumptions, and settings. At this point, this new forecast is exactly the same as the original.
Now, tools like Appending Values or Factors can be used to manuipulate the forecast data and create a new scenario.
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 section)
Use Auto Entry (see previous sections)
Upload a CSV (explored in the video displayed in the Manual Data Stream section)
Factors
Factors can be applied to individual data streams or entire forecasts.
There are two types of factors.
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.
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.
501: Forecast maintenance and reconciliation
Forecast Maintenance
Forecast maintenance requires diligence.
The built-in features of Trovata make it easy to report actuals against forecast. However there are areas where human intervention is required, like editing streams, updating accounts, and adjusting assumptions.
Editing Streams
A common reasons to edit a streams is when there is a new type of cash flow.
Updating Accounts
Occasionally account strutures change. In a forecast, this means that cash flows associated with certain accounts may not occur anymore or may occur in a different account.
Trovata best practice is that all accounts associated with a stream’s cash activity are included in the Forecast settings.
Adjusting Assumptions
As the business evolves, so do the assumptions within the forecast. Adjusting assumptions may mean appending values, applying new growth factors, or rebuilding the stream structure in a forecast altogether.
Forecast Reconciliation
Forecast reconciliation is the exercise of comparing what was forecast vs. what actually occurred, then adjusting the assumptions for future forecasts accordingly.
Within Trovata, current period Actuals can be investigate by directly clicking on the Actuals value in the forecast.
From there, adjust future forecasts as necessary by appending the stream values using the Update Data setting of the stream.
Appendix: UI Navigation and Settings
Stream Settings
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:
Forecast Settings Menu
General
You can change general details like the name of the forecast, default date range, currency, cadence, and rounding figures.
Accounts
Here you can add or remove accounts to your forecast.
Data Sources
Here you can add or remove streams or forecasts to/from your forecast.
Groups
Here you can create Groups and bucket your data streams. Example: Receipts & Disbursements or Inflows & Outflows.
Factors
Here you can add factors to individual data streams or entire forecasts (covered previously).
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.