Color Coded Budget Variances Using FRx and Excel

Using conditional formatting in Excel to fool with FRx budget variances

Well, here’s where we’re going to go today: ‘stoplights’, or red color coding, for unfavorable budget variances. You do know we’re going to Excel for this, right?

Here’s the initial budget variance report in FRx, and I’ll explain some of the primary concepts, then we’ll flip over to Excel for the fun color coding part. (OK, conditional formatting.)

Notice in the report above that all the unfavorable variances are negatives regardless of whether the account is revenue or expense related. If you were creating this report in Excel, you’d have to either reverse the formula or flip the signs for either revenue or expense in order to get the sign right. Well, FRx will do that for you in the column with a print control called XCR. See image below. No flipping signs, no complicated if/then statements. Nothing but an XCR in the variance column. It works in conjunction with the ‘C’ sign flips in the related row (more in a minute).

The only trick to the XCR in the budget variance column is that you need to have ‘C’ sign flips in the Normal Balance column all the way down…on all the rows that have a total using a sign flipped row, like Income from Operations and Net Income below, to name just a couple. Again, this will ensure that the sign on the variance is correct.

So those are the high points of the report in FRx. For the color coding, we’re going to Excel. You can do this from the drilldown viewer of course, but here it is going straight to Formatted Excel from the catalog. Be sure to mark ‘Activate Workbook’ and the 3 checkboxes below that in ‘Worksheet Options’. (Also note that this is 6.7. In 6.5, Formatted Excel is called Excel via OLE.)

Once you’ve got your report sent to Excel, highlight the variance columns, choose the Format menu, and choose Conditional Formatting. Choose the options shown below, then click the Format button:

When you click the Format button, choose red in the Color dropdown and click OK, then OK again on the Conditional Formatting box.

And here you have it…variances in red any time they’re unfavorable:

You can also experiment with adding other conditions, perhaps green when the variances are more than $5,000 favorable. Lots of things you can do with this. Enjoy!