WOW them with FRx Monthly & Year-end Forecast Report

One of my favorite FRx reports is a monthly and year-end forecast report that is a snap to create. Here’s a sample of the report:

 

 

The reason it is so easy to create is that the column layout has already been created and exists in the FRx sample company, Fabrikam Works. You can open the report in Fabrikam Works and then do a file>save as to save the column layout in your company’s spec set. The reason that you’ll wow the report recipients is because it provides actual and forecast information all on one page and because you’ll be able to create the report so quickly. Once you’ve saved the column layout to your company’s spec set, all you’ll have to do is change the Budget ID to the appropriate one for your company in each budget column (a simple copy/paste, paste, paste, etc. will do the trick). Then the report will generate properly each period, pulling actual amounts for the appropriate months and budget amounts for the other months, just with the click of the generate button!

Here are the detailed steps:

1. Launch FRx and switch the Default Company to “Fabrikam Works”
2. Open the catalog ID “IS_Dynamic_GL”
3. Open the Column ID for that report, “Dynamic_GL” (you could skip step #2 and go directly to this column layout, if you prefer). This is what the column layout looks like:

As you can see, there are 2 columns for each period, one for actual and one for budget. The key factor here is the Print Control line. Notice that the Actual columns have “P<=B” which means, print this column if the period of this column is less than or equal to the Base period. The Budget columns have “P>B” meaning, print this column if the period of this column is greater than the base period. Since these settings are mutually exclusive, only one column will print for each period. For example, if the report is run for the Base Period 4, then the Actual amount will print for Period 1 to Period 4 columns and the Budget amount will print for Period 5 to Period 12 columns (pretty nice!)

Where it gets really fancy is in the column header section. Look back at the report sample up above. Notice that the Actual and Budget headers stretch over the appropriate columns. FRx does this automatically for you. No matter what period you run the report for, FRx figures it out! Take a look at the details for each header. The Actual header looks as follows:

By telling FRx to Spread the header from B to BASE, it will always stretch over only the actual columns!

 

The Budget header looks as follows:

By telling FRx to Spread the header from BASE+1 to Y, it will always stretch over only the budget columns! And, Font Style = Column Shade makes the distinction even better.

Anyway, close the Header Options window and move on to step 4.

1. Click File>Save as. This window will open:

The key here is to click on the drop-down button for Specification set and select your company’s spec set. (Normally your company’s spec set will be “Default”. If you have more choices than FRxDemo and Default, and you’re not sure which one to pick, cancel out and go to Company>Information. Click on your company on the left and view the spec set displayed on the right. Do NOT do anything else on this window! Click Cancel and go back to the column layout.) You can then change the name and/or description of the column layout if you want and click OK. You will then get this message:

It did what you asked it to do so, just click OK and move on to step 5.

1. Close the column layout
2. Close the report catalog
3. Switch the Default Company back to your company
4. Open the column layout you saved in step #4 and change the Budget ID in each budget column to the appropriate Budget ID for your company
5. Create a report catalog (or modify an existing one, if you prefer) with your desired row format, the new column layout, and your desired reporting tree (if applicable)
6. Wow your boss and others with this informative report!