Working with External Data and Calculated Rows

tipsandtricksIn Forecast Pro TRAC’s Override View, users have the ability to display five types of information: historical data; forecasts; overrides; external data & calculated rows. At times, when working with your forecasts, visibility to other information such as alternative forecasts generated outside of Forecast Pro or non-forecast data such as current orders or inventory levels can greatly aid the forecasting process.

Forecast Pro TRAC users have the ability to import external data and alternative forecasts into the Forecast Override View and to create customized, calculated rows. This installment of Tips and Tricks includes several examples of how to use this functionality and illustrates the value that this new capability brings to the forecasting process.

Let’s look at a few examples which take advantage of this new functionality.

Establishing a Forecast Consensus

Sometimes forecasters are faced with multiple, differing sets of forecasts. For example, in addition to the statistical forecast, we may also have forecasts from management, sales or customers.

In the following example, in addition to generating a statistical forecast using Forecast Pro, we have imported an external forecast labeled Customer Forecast.

1

Notice that there is also a calculated row beneath the external forecast row, Consensus. In order to create consensus among the final adjusted forecast and the customer forecast, we have added the Consensus row that displays an average of the adjusted forecast and the customer forecast. It is also possible to view and compare any of this information side-by-side in the graph.

Calculated rows are added to the grid by right-clicking the Forecast row or any row below it, and selecting “Add Row”. You will be prompted to enter a name for your new row which will serve as its label.

Formulas for the calculated rows support most basic mathematical operators (+ – * / and others). They can also reference other rows within the same date/column using either the row name enclosed in braces (curly brackets) or “tokens” which are pre-defined keywords.

Some commonly used tokens are:

  • STAT – The statistical forecast.
  • FORECAST – The final forecast.
  • OVR# – The override rows (the “#” sign indicates the row number).
  • UPPER- The upper confidence limit.
  • SAFETY- The safety stock at the items specified lead time
  • DDLT- The demand during the items specified lead time.
  • REORDER- The reorder point at the item’s specified lead time.

For a full list of supported operators, functions, and tokens, please refer to the help system or consult the reference section of the Forecast Pro TRAC User’s Guide.

2A formula that takes the average of the customer forecast and the final forecast.

Analyzing the “Gap” Between Two Conflicting Forecasts

In our second example, we have imported an external forecast from management.

3

To decide where we may want to adjust the forecast to reflect the management forecast, we have added a calculated row that shows us the difference, or “the gap”, between the two forecasts using the following formula:

= STAT- {Management Forecast}

The Forecast Override View also allows us to format our grid to facilitate analysis. For example, we may wish to highlight in red wherever a value in the The Gap row is negative, so we can quickly identify where we need to make an adjustment.

To do this, we simply right-click anywhere on the override grid and open the Forecast Overrides Design box which provides a variety of formatting options and allows you to specify the order in which the rows are displayed.

4The Forecast Override Design box allows us to format our grid, including changing row colors, adding bold/italics, adding dollar/percentage signs, setting decimal precision and displaying negative numbers in red.

Calculating Available to Promise

In addition to bringing in external forecasts, we can also bring in any kind of external information we want, provided that the external data is in a format that is consistent with our default hierarchy. In our final example, we have imported “Current Orders” into the Forecast Pro project.

5

We also have added a calculated row for Available to promise, which is simply the difference between the final forecast and our current orders:

= FORECAST- {CURRENT ORDERS}

Once again we are displaying negative numbers in red, to easily identify where orders exceed our current forecast, allowing us to make adjustments accordingly.

Summary

Forecast Pro TRAC provides the ability to import external data and alternative forecasts into the Forecast Override View and to create customized, calculated rows. This article presented several examples of how to use this functionality and illustrated the value that this new capability brings to the forecasting process.

If you would like to learn more about Forecast Pro TRAC, please visit this page, or contact us directly to schedule a live demo via WebEx.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s