Management Reporter: Dynamic Forecast (Conditional Columns)

By using the Conditional Print Control available in the Management Reporter column layout a user can easily create a forecast incorporating both Actual and Budgeted amounts.

For example, let’s assume we have a 12 month calendar fiscal year. Create a column layout with 26 columns. The first column would be for the row description and the last column would be a Total. There would then be 2 columns for each period of our fiscal year. Each period would have a column for Actual and another column denoting Budgeted amounts. So, Column B would be Period 1 Actual and Column C would be Period 1 Budget. Column E would be Period 2 Actual and Column E would be Period 2 Budget…and so on.

We now want to Conditionally hide columns based on report date (base period). For each PERIODIC column with ACTUAL, put “P<=B” in the Print Control row. For each PERIODIC column with BUDGET, put “P>B”. By doing this, actuals will show in columns where the Period in the column is less than or equal to the report period. Budgets will show in columns where the period is greater than the report period.

For example, if I run my report as of 4/30/2014, actual amounts will show in my Period 1 thru 4 columns and budget amounts will show in Period 5-12. The total column will then add these amounts creating a forecast total.

This is a great way to build your layout one time and have the actual and budget amounts populate solely based on the report date.