Simplify FRx Totals and Calculations

This is the first of a series of posts on how to simplify FRx.

It’s so easy to make FRx harder than it should be. And it’s not only harder to set up but way tougher to maintain.

In this post I’ll give some real world examples of simplifying FRx totals and calculations. I’m using Before and After screenshots, so these are very practical examples. Without further ado, let’s dive in!

Simplifying FRx Totals

This first example is a balance sheet from the FRx Demos. Yes, even it can be simplified. The original is on the left and my simplified version is on the right. I couldn’t resist getting rid of that dreadful blue (and the italics), but other than that, two formulas are all I changed.

Here’s what I simplified: my TOT formulas include other TOT formulas in their range. This is not a very well known point, but by design TOTs ignore other TOTs. So that means your formula can be just one long range and you don’t have to worry about picking the subtotal row codes one by one. Granted, there’s not a huge benefit in this particular example, but this is a nice timesaver if you’ve got a long row format. I use it every day.

Faster, simpler, easier.

When to be careful? Well, if you’ve got a CAL in the middle of the range, the TOT definitely will pick that up.

Be careful on a P&L too. It’s likely that you’re flipping signs in column E Normal Balance, and TOTs and CALs work with a row after it has had its sign flipped. So if your range includes both C sign flipped rows and normal balance rows, you shouldn’t use a TOT range including both types. For example, if you’ve got sign-flipped revenues and normal balance cost of goods, then you wouldn’t want to use a TOT to pull in the entire range for gross profit. Instead, the TOT formula should be revenue minus cost of goods. Easy enough.

Simplifying FRx Calculations

This next example shows the user jumping through a lot of hoops to get to a total row: first a nonprinting TOT row, then a CAL calculation row based on the totals, then another CAL row based on the first CAL. (And the formulas go way off the page!)

It hurts my head just looking at it—and I do this every day. Can you imagine a new staff person trying to follow this logic?

As it turned out, all that was needed was just a very simple, very easy TOT row.

BTW, the technique used in rows 1063 and 1065 is called ‘column placement’. Sometimes you need to, but don’t do it if you don’t have to!

Here is another FRx calculation simplification. The user in the Before shot is dividing gross profit by a sales subtotal (that’s also being calculated in the formula), and placing the results in columns B and C (more column placement). My tipoff that this could be simplified was that the formulas are the same except for the column letters (B and C).

In the After example, I added a single row to subtotal sales, and decided to use an alpha row code SALES because it makes calculation formulas easier to read. I used the @ sign preceding the row code: this tells FRx to put this calculation in every column.

So there you have it—a few Before and After simplifications. As you can tell, the Afters are so much easier to deal with! There are so many ways to get downtown in FRx. These Befores did give valid answers, but at what price? I think the moral of the story is to ask yourself if there’s an easier way. I hear all the time from people who just instinctively know that “there’s got to be a better way!”

PS. Oh! One more thing I see is using CAL formulas (instead of TOT) to do a simple add or subtract. It’s way simpler to just use a TOT.

Tagged as: Calculations, Report Design, Simplify