Specific Use Cases

The Forecast Generator is a powerful tool for generating complex and varied forecasts. Some explanation and example cases are as follows:

The simplest mode by which to understand the Forecast Generator is when Periodicity is set to Daily. This mode propagates the same average over each business day within the forecast date range specified. The bottom half of the Propagate Criteria section are not used in this mode. Keep in mind that the section above that, Transform Criteria, still very much applies.

You’ll most likely want to Sum By Business Daily, which aggregates transactions analyzed from the Source (typically historical Cashbooks) by day. You could propagate a weekly or monthly average by day as well — though that doesn’t make much sense! There are also options to apply a multiplier or rounding if you need to adjust your forecasts for growth or inflation.

ZBA case

One primary use for the Daily Periodicity would be forecasting and managing ZBA account activity. You can set up filters in the Filter History section both for a specific set of Accounts and Cashflow Categories and then use the Not indicator to reverse things — in the case of ZBA!

This brings us to using Profiles to manage our various forecasts:

Priority is a simple but very powerful field that determines if and when forecasts are generated automatically by this module during the EOD process. If Priority is >0, the module will execute each profile and create records for all forecasts generated in the order of lowest priority value first. This becomes particularly useful in the case of our ZBA example. We’re going to offset the aggregation of a set of forecasts with an opposite amount and then populate that amount into a different account. This will require two sets of Forecasts with a Source Forecasts, with different Priorities (via separate profiles) in the appropriately logical corresponding order. To deactivate a profile temporarily, the same Priority setting can be set to -1.

A final feature likely to be utilized in this example is the Assignments section. The system can assign a Cashflow Category or several other fields to a given forecast record based on what is filled out in the Assignments section. This is particularly useful if you’re analyzing a sum of multiple accounts and/or cashflow categories.

Weekly case

The only two Periodicity modes used are D (Daily) and NA (everything else). One of the most common NA would be Weekly. This is achieved selecting the appropriate Day Of Week in the bottom half of the section. Again, make sure you choose the correct settings in the Filter History and Transform Criteria sections what you’re trying to accomplish. If you have activity throughout the week but you’re only trying to pull activity Wednesdays make sure you don’t select Sum By weekly. Play around with the settings and you should start to get the hang of being able to generate what you intend.

Bi-weekly payroll case

You pay payroll on a bi-weekly basis. You want to forecast payroll for 3 months into the future using an average of the last 6 months of payroll. The forecast generator allows you to analyze a past range of values, get an average, and propagate that average forward across your forecast range through given criteria.

Make sure the Biweekly filter is set to Yes, Periodicity set to NA, and the appropriate day of week selected from the Day Of Week filter. Also ensure the first day of your intended Forecast is after your start date — but in the same week!

Additionally, you can easily shape your range based on a number of criteria, such as value, if for example you payroll was higher during one period for bonuses paid out, by adjusting the Transaction Amount or Summary Amount filters.

Lockbox Receipts — Daily Avg Auto

You know the amount of your Lockbox deposits depends on both what day of the week it is and also whether it’s the beginning, middle or end of the month. You want to forecast Lockbox receipts for 3 months into the future using an average of the last 6 months that takes this timing sensitivity into account.

The forecast generator can automatically analyze historical activity by Day of Week and Week of Month (i.e. take an average of all 1st Mondays, 3rd Thursday, etc.) and generate one comprehensive forecast.

Daily Avg Auto actually runs 20+ forecasts at once, and then combines these into one forecast at the end! It’s as if you set the Day Of Week and # Day Of Week filters in both Filter Value Date and Propagate Criteria sections to Monday and 1, generated a forecast, then changed those 2 filters to cover every possible permutation. In fact, when the Daily Avg Auto is switched to Yes, it deactivates the rest of the fields in the Filter Value Date section to achieve this effect. It also changes the Sum By filter to Specified Term, the Average Ind to # of Days Observed, as well as deactivating most of the fields in the Propagate Criteria section.

AR — Shift case

You know there are seasonal trends in your Accounts Receivable, with the biggest day being at the end of the month. You also know the activity is sporadic, and want to capture all of this by overlaying last year’s activity into the same period for this year.

Shift subtracts the Value Date (Filter History section) start date from the Start Date on the Propagate section to get a “shift constant”. It then takes historical transactions and “shifts” them forward by the number of days calculated, accommodating for weekends, etc. given the Date Except Ind setting.

Make sure Shift is set to Yes, and that Periodicity is set to NA. It usually works best if Sum By is Business Daily and Average Ind is None.

Filter History Section

#	Field Name	Field Description	Mandatory (Y/N)
1.	Source	Cashbooks or Forecasts — whether the source data is pulling form Cashbooks or Forecasts	Y
2.	Center	Operating Center applicable to rule	Y
3.	Funding Entity	The applicable Funding Entity of the source data	Y
4.	Counterparty	The applicable Counterparty of the source data	N
5.	Bank	Can be turned on to isolate a particular Bank within source data	N
6.	Account	Can be turned on to isolate a particular Bank Account within source data	N
7.	Currency	Can be turned on to isolate a particular Currency within source data	N
8.	Cashflow Category	Primary driver by which source data is categorized. Can be multiple values or "not" value for advanced forecasting	Y
9.	Category Group	Can we used to isolate a Category Group within the source data	N
10.	Pay/Rec	This field drives pulling only Debts, Credits, or both from the source data	N
11.	Transaction Amount	Value filter for amounts on the transaction-level	N
12.	Summary Amount	Value filter for amounts on the daily-sum level	N
13.	Detail	Hide/Show for the Det Hist tab	N
14.	Value Date	The range for which the source data is being pulled	Y
15.	Product	Can be turned on to isolate a particular Product within source data	Y
16.	Sub-Product	Can be turned on to isolate a particular Sub-Product within source data	N
17.	Affiliate	Can be turned on to isolate a particular Affiliate within source data	N
18.	Region	Can be turned on to isolate a particular Region within source data	N
19.	Unit	Can be turned on to isolate a particular Unit within source data	N
20.	Source System	Can be turned on to isolate a particular Source System code within source data	N

Filter Value Date Section

#	Field Name	Field Description	Mandatory (Y/N)
1.	Month	Filters the source data for a particular month of the year	N
2.	Week of Month	Filters the source data for a particular week of the month over the given range	N
3.	Day of Week	Filters the source data for a particular day of the week (e.g. Tuesdays) over the given range	N
4.	# Day of Week	Filters the source data for a particular instance of day (e.g. 2nd Tuesday when paired with Day of Week) over the given range	N
5.	Week of Year	Filters the source data for a particular week of the year, converted to a number value	N
6.	Calendar Day of Month	Filters the source data for particular calendar days of the month by number for each month	N
7.	Business Day of Month	Filters the source data for particular business days of the month by number for each month	N
8.	Daily Avg Auto	This is a complex setting that averages each unique permutation of DOW + # DOW across the given Value Date range. This setting changes some other overrides some other settings. Conflicts are handled via error messaging.	N

Transform Criteria Section

#	Field Name	Field Description	Mandatory (Y/N)
1.	Sum By	Indicates the sum by Daily, over the entire period, weekly, monthly	Y
2.	Average Ind	The denominator of the equation: no denominator, # instances observed, # business days in period	Y
3.	Amount Multiplier (%)	Takes the forecast result and multiplies by a %	Y
4.	Rounding Ind	Rounds by 1000, unit, or none	Y

Propagate Criteria Section

#	Field Name	Field Description	Mandatory (Y/N)
1.	Inactivate Prior	Inactivated previous Forecasts with source code FORECAST GEN for the same value date and Cashflow Category	N
2.	Start Date	Start date of the desired forecast	Y
3.	Date Except Ind	Handling for forecast values that fall on non-business days	Y
4.	Periodicity	Two modes: D for daily and NA to bypass to other filters	Y
5.	Fill In Ind	Choose whether or not to fill in interim day	N
6.	Month	Propagates the forecast to a monthly value	N
7.	Week of Month	Propagates the forecast to a values based on week of month	N
8.	Day of Week	Propagates the forecast to a values based on day of week (e.g. Tuesdays)	N
9.	# Day Of Week	Propagates the forecast to a values based on # instance by week (e.g. 2nd Tuesdays when paired with Day of Week)	N
10.	Suppress Zero Ind	Suppresses generating records with a value of 0	N
11.	End Date	End date of the desired forecast	Y
12.	Holiday	Indicates which holiday calendar to pull from	Y
14.	Shift	"Transposes" activity forward, doing a best-fit of historical value patterns over the given forecast date range	N
15.	Aggregate nonBus	Aggregates values shifted over multiple, concurrent nonbusiness days into one amount	N
16.	Absolute Value	Takes an absolute value if debits and credits would be combined	N
17.	Week of Year	Propagates the forecast to a values based on week of year (based on a number)	N
18.	Calendar Day of Month	Propagates the forecast to a values based on calendar day of month (based on a number)	N
19.	Business Day of Month	Propagates the forecast to a values based on business day of month (based on a number)	N
20.	Biweekly	Propagates the forest to values based on even or odd Week of Year	N

Assignments Section

#	Field Name	Field Description	Mandatory (Y/N)
1.	Cashflow Category	Overrides the Cashflow category on the forecast, changing it to the value displayed here (is usually set the same value as source)	Y
2.	Bank	Overrides the Bank on the forecast, changing it to the value displayed here (is usually blank / set the same value as source)	N
3.	Account	Overrides the Bank Account on the forecast, changing it to the value displayed here (is usually blank / set the same value as source)	N
4.	Funding Entity	Overrides the Funding Entity on the forecast, changing it to the value displayed here (is usually blank / set the same value as source)	N
5.	Pay/Rec	Overrides the DR_CR_IND	N
6.	Description	Generates data in the Description field	N
7.	Source Ref Auto Populate	Populates the Source Reference field with a concatenated string as such: Source Ref ID Prefix&Account&Pay/Rec&Cashflow Category&Value Date(dd-mm-yyyy)	N
8.	Source Ref ID Prefix	Populates the Source Reference field with data or adds a prefix to the auto-concatenated string	N

Sum Hist Tab

#	Field Name	Field Description	Mandatory (Y/N)
1.	No.	Assigning number	N/A
2.	Create	Checkbox used for propagating forecast line items	N/A
3.	Status	Status of forecast creation	N/A
4.	Account	Bank Account for forecast line item	N/A
5.	Original Amt	Original amount of source data for forecast line item	N/A
6.	Forecast Amt	Amount of forecast line item	N/A
7.	Pay/Rec	Debit/Credit indicator for forecast line item	N/A
8.	Cashflow Cat	Cashflow category of forecast line item	N/A
9.	Value Date	Value date of forecast line item	N/A
10.	Description	Description of forecast line item	N/A
11.	WOY	Applicable week of year for forecast line item	N/A
12.	WOM	Applicable week of month for forecast line item	N/A
13.	DOW	Applicable day of week for forecast line item	N/A
14.	# DOW	Applicable # day of week for forecast line item	N/A
15.	Cal DOM	Applicable calendar day of month for forecast line item	N/A
16.	Bus DOM	Applicable business day of month for forecast line item	N/A
17.	Source System	Source system (always FORECAST GEN)	N/A
18.	Affiliate		N/A
19.	Funding		N/A
20.	Center		N/A
21.	Currency		N/A
22.	Hedgeable		N/A

3.3 Forecast Tab

#	Field Name	Field Description	Mandatory (Y/N)
1.	No.	Assigning number	N/A
2.	Create	Checkbox used for propagating forecast line items	N/A
3.	Forecast ID	Used when forecast created	N/A
4.	Status	Status of forecast creation	N/A
5.	Bank	Bank for forecast line item	N/A
6.	Account	Bank Account for forecast line item	N/A
7.	Original Amt	Original amount of source data for forecast line item	N/A
8.	Forecast Amt	Amount of forecast line item	N/A
9.	Pay/Rec	Debit/Credit indicator for forecast line item	N/A
10.	Cashflow Cat	Cashflow category of forecast line item	N/A
11.	Value Date	Value date of forecast line item	N/A
12.	Description	Description of forecast line item	N/A
13.	WOY	Applicable week of year for forecast line item	N/A
14.	WOM	Applicable week of month for forecast line item	N/A
15.	DOW	Applicable day of week for forecast line item	N/A
16.	# DOW	Applicable # day of week for forecast line item	N/A
17.	Cal DOM	Applicable calendar day of month for forecast line item	N/A
18.	Bus DOM	Applicable business day of month for forecast line item	N/A
19.	Source System	Source system (always FORECAST GEN)	N/A
20.	Source Reference	Can be automatically concatenated from several fields in Assignments and/or free-form prefix	N/A
21.	Affiliate
22.	Funding
23.	Center
24.	Currency
25.	Hedgeable
26.	Post Date
27.	Exposure Type
28.	ID Number
29.	Ref Field 1
30.	Ref Field 2
31.	Ref Field 3
32.	Ref Field 4
33.	Ref Field 5
34.	Error Description

Det Hist Tab

#	Field Name	Field Description	Mandatory (Y/N)
1.	No.	Assigning number	N/A
2.	Create	Checkbox used for propagating forecast line items	N/A
3.	Bank	Bank for forecast line item	N/A
4.	Account	Bank Account for forecast line item	N/A
5.	Original Amt	Original amount of source data for forecast line item	N/A
6.	Pay/Rec	Debit/Credit indicator for forecast line item	N/A
7.	Cashflow Cat	Cashflow category of forecast line item	N/A
8.	Value Date	Value date of forecast line item	N/A
9.	Description	Description of forecast line item	N/A
10.	WOY	Applicable week of year for forecast line item	N/A
11.	WOM	Applicable week of month for forecast line item	N/A
12.	DOW	Applicable day of week for forecast line item	N/A
13.	# DOW	Applicable # day of week for forecast line item	N/A
14.	Cal DOM	Applicable calendar day of month for forecast line item	N/A
15.	Bus DOM	Applicable business day of month for forecast line item	N/A
16.	Source System	Source system (always FORECAST GEN)	N/A
17.	Affiliate		N/A
18.	Funding
19.	Center
20.	Currency
21.	Hedgeable

🔮 Treasury Forecast Modeling Approaches

---

1️⃣ Direct Method (Bottom-Up)

How it works: Build forecasts from individual transaction-level data — each expected cash inflow and outflow is identified and scheduled.

Best for:

Short-term forecasts (0–30 days)
High-value, predictable transactions (debt payments, payroll, known receivables)

Pros: Very accurate for near-term | Cons: Labor-intensive, hard to scale long-term

---

2️⃣ Indirect Method (Top-Down)

How it works: Starts with net income and adjusts for non-cash items, working capital changes, and balance sheet movements.

Best for:

Medium to long-term forecasts (90 days+)
Strategic planning and board-level reporting

Pros: Aligned with financial statements | Cons: Less granular, less accurate short-term

---

3️⃣ Historical Run-Rate Model

How it works: Uses past actuals as the baseline and applies a growth/change factor.

Best for:

Recurring, predictable cash flows (payroll, rent, utilities)
Quick forecasts with minimal setup

Example: (Just like we did earlier!) — Take last year's payroll actuals → apply 3% increase → get this year's forecast

Pros: Fast, data-driven | Cons: Assumes past = future, misses structural changes

---

4️⃣ Statistical / Time Series Models

How it works: Uses mathematical models to identify patterns in historical data.

Model	Description
Moving Average	Smooths short-term fluctuations, highlights trends
Exponential Smoothing	Weights recent data more heavily
ARIMA	Auto-regressive model for complex seasonal patterns
Linear Regression	Identifies correlation between cash flows and drivers

Best for: Medium-term forecasts with seasonal patterns (retail, consumer goods)

Pros: Objective, quantitative | Cons: Requires data quality, complex to maintain

---

5️⃣ Driver-Based Model

How it works: Links cash flows to business drivers (revenue, headcount, units sold) rather than just historical amounts.

Examples:

Payroll = Headcount × Average Salary
Collections = Revenue × Days Sales Outstanding (DSO)
Inventory payments = Units Produced × Unit Cost

Best for: Companies with strong FP&A integration, operational forecasting

Pros: Adapts to business changes automatically | Cons: Requires clean driver data from ERP/HR systems

---

6️⃣ Scenario-Based Model

How it works: Creates multiple forecast versions under different assumptions.

Scenario	Description
Base Case	Most likely outcome
Bull Case	Optimistic assumptions
Bear Case	Pessimistic/stress assumptions
Sensitivity	What-if analysis on key variables

Best for: Risk management, board presentations, M&A planning

Pros: Shows range of outcomes | Cons: More maintenance, can create confusion without clear governance

---

7️⃣ Machine Learning / AI Model

How it works: Algorithms learn patterns from large datasets (transactions, market data, ERP data) to generate probabilistic forecasts.

Techniques:

Random Forest
Neural Networks (LSTM for time series)
Gradient Boosting

Best for: Large enterprises with rich transaction history, complex multi-entity structures

Pros: Highly accurate, self-improving | Cons: Black box, requires data science resources

---

8️⃣ Hybrid Model (Best Practice)

How it works: Combines multiple approaches based on the cash flow type:

Short-term (0-30 days)  →  Direct Method + Known Transactions Medium-term (30-90 days) →  Driver-Based + Statistical Long-term (90+ days)    →  Indirect Method + Scenario Analysis

Best for: Most corporate treasury departments — balances accuracy with practicality

---

🎯 Choosing the Right Model

Company Profile	Recommended Model
Small/simple cash flows	Historical Run-Rate
Seasonal business	Time Series (ARIMA)
Growth company	Driver-Based
Risk-focused treasury	Scenario-Based
Large multinational	Hybrid + ML
Quick & dirty forecast	Historical Run-Rate (what we used for payroll!)

---

💡 In Trovata TMS

Trovata TMS supports several of these natively:

✅ Historical Run-Rate — query actuals, apply factor (as we did with payroll)
✅ Direct Method — enter known transactions into cash forecast module
✅ Scenario-Based — multiple forecast versions/scenarios
✅ Driver-Based — via integration with ERP/HR data feeds