Forecast Pro TRAC only
You enter formulas by clicking on the cell you wish to modify and typing in the formula. You may enter an override cell reference by selecting a cell on the override grid while typing the formula, much like you can in Excel. If you wish to edit a previously entered formula, select the cell and press the F2 key. You can copy and paste formulas from cell to cell.
Item-specific calculated rows will be calculated only for the item for which calculations were entered and cannot be aggregated or disaggregated.
By default, the formulas in a specific Global calculated row will be calculated at the single level of the hierarchy on which they were entered. You can change the hierarchy level using the Row Properties tab on the Overrides Settings. Any values you elect to display at other levels will be either aggregations or disaggregation of the calculated values when calculating at a single level. Depending upon your formulas, this may or may not be equivalent to applying the defined formula at the group level.
If you wish to change the default behavior for a row and calculate the formulas at all levels you can do so using the Row Properties tab. Be aware that this option may slow down certain operations, including applying overrides.
Formulas are not case sensitive. Formulas are entered in the format:
= expressionThe expression can involve any of the following components.
| Numbers | Any real number |
| Operations | + – * / ^ |
| Parenthesis | () |
| Comparison operators | = < > <= >= |
| Functions | Listed below |
| Tokens | Listed below |
| Rows | {RowName} |
| Conversion factors | {#ConversionFactorName} |
Note that when a row name is used as part of a definition it must appear in braces (curly brackets).
The conversion factors are a special token that returns the value of the specified conversion factor. The token must appear in braces and have the # symbol prior to the conversion factor name. For example, if Dollars is a defined conversion factor, then {#Dollars} would be the corresponding token referring to an item’s price in dollars. In this example, {Dollars} would refer to the converted final forecast value.
Token and row references can also include an offset by appending a [-n] to the token or row name. For example:
| {rowname} | Returns the value of rowname for the current period. |
| {rowname}[-1] | Returns the value of rowname for the previous period. |
| {rowname}[1] | Returns the value of rowname for the next period. |
Forecast Pro supports the following functions:
| ABS(n) | Returns the absolute value of n |
| EXP(n) | Returns e raised to the nth |
| LOG(n) | Returns the natural logarithm of n |
| LOG10(n) | Returns the base-10 logarithm of n |
| SIGN(n) | Determines the sign of n. Returns 1 if n is positive, 0 if n is zero and -1 if n is negative. |
| SQR(n) | Returns n squared |
| SQRT(n) | Returns the square root of n |
| IF(condition, value1, value2) | Returns value1 if condition is true or value2 if condition is false |
| INT(n) | Rounds n down to the nearest integer |
| TRUNC(n) | Truncates n to an integer |
| EVEN(n) | Rounds n up to the nearest even integer |
| ODD(n) | Rounds n up to the nearest odd integer |
| ROUND(n, decimal places) | Rounds n to the specified number of decimal places |
| MROUND(n, basis) | Rounds n to the nearest multiple of basis |
| ROUNDUP(n, decimal places) | Rounds n up to the specified number of decimal places |
| ROUNDDOWN(n, decimal places) | Rounds n down to the specified number of decimal places |
| MIN(x, y, …) | Returns the smallest value in the specified set of values |
| MAX(x, y, …) | Returns the largest value in the specified set of values |
| AVERAGE(x, y, …) | Returns the average of the specified set of values |
| STDEV(x, y, …) | Returns the standard deviation of the specified set of values |
| SUM(x, y, …) | Returns the sum of the specified set of values |
| MEDIAN(x, y, …) | Returns the median of the specified set of values |
| MODE(n) | Returns the mode (most frequently occurring value) of the specified set of values |
| NVL(n, alternative) | Null value function. If n is blank returns alternative. If n is not blank returns n. |
| ISNUMBER(n) | Returns 1 if n is a number, 0 if n is blank. |
| ISNULL(n) | Returns 1 if is blank, 0 if n is a number. |
If you use Excel, you may be familiar with these functions. Forecast Pro implements these functions in the same way as Excel with one exception—cell ranges are not yet supported. For example, the following formula will return the sum of the statistical forecast for last period, this period and next period:
=SUM(STAT[-1],STAT,STAT[1])
The following, however, is not supported: =SUM(STAT[-1]:STAT[1])
Forecast Pro supports the following tokens:
| FORECAST | Forecast row |
| PFC# | Forecast from ‘#’ periods prior |
| PST# | Statistical forecast from ‘#’ periods prior |
| PBL# | Baseline forecast from ‘#’ periods prior |
| FCLT# | Forecast at lead time ‘#’ |
| STLT# | Statistical forecast at lead time ‘#’ |
| BLLT# | Baseline forecast at lead time ‘#’ |
| YOY | Returns the percentage difference between a forecast value and the previous year’s historic value. |
| YTD | Returns the calendar year-to-date using the history and forecast values as applicable. |
| QTD | Returns the calendar quarter-to-date using the history and forecast values as applicable. |
| STAT | Statistical forecast row |
| BASE | Baseline forecast row |
| INTFC | Integer Forecast. Only available if the Integer Forecast is added to the grid in the Override Settings dialog. |
| INTSTATFC | Integer Statistical Forecast. Only available if the Integer Forecast is added to the grid in the Override Settings dialog. |
| OVR1 | The first override row |
| OVR# | Override row “#” |
| HIST[-n] | Returns the history value at -n |
| HIST# | The historic row “#” years prior |
| FCHIST[-n] | Returns the Forecast History value at -n |
| HISTBL[-n] | Returns either the history value or the baseline forecast value at -n. Intended for use in formulas where it may point to either a forecast or a historic period. |
| HISTFC[-n] | Returns either the history value or the forecast value at -n. Intended for use in formulas where it may point to either a forecast or a historic period. |
| HISTST[-n] | Returns either the history value or the statistical forecast value at -n. Intended for use in formulas where it may point to either a forecast or a historic period. |
| INHER | Inherited override row. |
| LOWER | Lower confidence limit |
| UPPER | Upper confidence limit |
| LEADTIME | The item’s specified lead time |
| SAFETY | The safety stock at the item’s specified lead time |
| SAFETY_ALL | The safety stock corresponding to the lead time of the current cell |
| DDLT | The demand during the item’s specified lead time |
| DDLT_ALL | The demand corresponding to the lead time of the current cell |
| REORDER | The reorder point at the item’s specified lead time |
| REORDER_ALL | The reorder point corresponding to the lead time of the current cell |
| MEAN | The mean of the historic data |
| SDEV | The standard deviation of the historic data |
| MAD | The within-sample mean absolute deviation |
| MAPE | The within-sample mean average percent error |
| SMAPE | The within-sample symmetric mean average percent error |
| BIC | The Bayesian Information Criterion |
| {#Conversion} | Returns the conversion factor for “Conversion”. |
Important notes:
1. All functions require that the referenced system row is added to the override grid, though it does not necessarily need to be displayed. For example, the integer system row needs to be in the grid for INTFC to return a value. Referenced system rows can be added using the Add or Remove button in override settings. You can uncheck the Display checkbox in the Overrides Settings meaning the row will not be displayed in the Override grid, but it’s values will be read into memory so it can be referenced in a calculated row.
2. Tokens that reference baseline or final forecasts are only available in calculated rows.