Leveraging the Enhanced Grid Features in Forecast Pro TRAC v3

The override grid in Forecast Pro TRAC was greatly enhanced in Version 3 with new capabilities for importing external data and/or forecasts and creating new calculated rows based on this information. With the latest maintenance release of TRAC v3, the grid’s capabilities are further expanded—you can now define formulas using offset references and a wider array of Excel-like functions and tokens. In this article, we show a few examples of how you can leverage the new grid functionality to create powerful and efficient worksheets in Forecast Pro TRAC.

For a complete list of the newly added functions, please see this article.

Example 1: Viewing Quarterly and Annual Totals

Forecast Pro TRAC Version 3 enables you to create new rows in the override grid using formulas that can reference other cells for the current period. With the latest maintenance release, you can now reference any cell in the grid. In this example, we will set up a worksheet that shows both quarterly and annual totals for our forecasts.

By default, Forecast Pro TRAC displays the forecasts in the current periodicity, or time bucket; in other words, if you forecast by month, you can view your forecasts and adjustments by month, or if you forecast by week, you see those forecasts and adjust them by week. The Forecast Report allows us to view quarterly and annual totals; however, what if we want to see those same totals in the grid? This can now be done by adding calculated rows to the grid.

Notice that we have defined two rows entitled “Quarterly Total” and “Annual Total.” The values displayed in these rows sum up the forecasts for the quarter and the year, respectively, utilizing the following formulas:

Quarterly Total:

=SUM(FORECAST,FORECAST[-1],FORECAST[-2])

The FORECAST token refers to the Forecast row. By using the “[-n]” syntax, we can refer to other periods’ forecasts. For example, using the FORECAST token in the September 2012 column references the forecast for September 2012, while using FORECAST[-1] references the forecast one period prior to the current period, which is August 2012.

We can reference any “offset” cell in the grid in this way.

Annual Total:

=SUM(FORECAST,FORECAST[-1],FORECAST[-2],FORECAST[-3],FORECAST[-4],
FORECAST[-5],HIST[-6],HIST[-7],HIST[-8],HIST[-9],HIST[-10],HIST[-11])

Notice that since our first forecast period is July, calculating the annual total requires summing both historic data (using the HIST token) and forecasts.

Example 2: Using the Average Function

Forecast Pro now supports a number of mathematical functions, including ROUND, MEDIAN, MODE and AVERAGE, which will be familiar if you use Excel. Our next example shows how we can set up the grid to take an average of two forecasts.

In this example, we imported the row “Sales Forecast.” We then created a row calculated as the average of our statistical forecast and our sales forecast, and are using that as our baseline forecast (i.e., the top row in the grid).

The formula used for the “Average Forecast” row looks like this:

=AVERAGE({STATISTICAL},{SALES FORECAST})

Example 3: Conditional Statements

Our final example will look at how we can use conditional IF functions when creating new rows in the override grid in Forecast Pro TRAC.

Consider the following worksheet:

We have imported an additional row in this example: “Current Orders”, which displays the orders received for this particular part number for each month.

Our formula, defined in the row “Orders or Stat” (which we are also using as our baseline forecast), compares our statistical forecast against our current orders. If our current orders exceed the statistical forecast, we set “Orders or Stat” to current orders. If our current orders do not exceed the statistical forecast, we set “Orders or Stat” to the statistical forecast.

To accomplish this, we use a conditional statement, sometimes referred to as an “if-else” statement. The formula is defined as follows:

=IF({CURRENT ORDERS}>STAT,{CURRENT ORDERS},STAT)

This specifies that if our current orders are greater than the statistical forecast for the current period, the value of “Current Orders” is displayed; otherwise, the statistical forecast is displayed.

Defining the “Orders or Stat” row as our baseline forecast sets the baseline to the current orders when they exceed the statistical forecast and to the statistical forecast when they do not.

Summary

In previous versions of Forecast Pro TRAC, the override grid allowed you to view and adjust current forecasts. In Version 3 we’ve added the ability to import and display external information and create calculated rows. The latest maintenance release of Forecast Pro TRAC expands these capabilities by implementing a number of Excel-like functions and tokens which make it easier to create customized worksheets with the specific information you need to make better, faster decisions.

One thought on “Leveraging the Enhanced Grid Features in Forecast Pro TRAC v3

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