Aug.

23

2010

# Simplify FRx Totals and Calculations

by

## 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.

Be Sociable, Share!

Jerry November 3, 2010 at 9:32 pm

I have a column layout that uses:
sept2010, %of sales, sept2009, %of sales.
How can I get %of Sales for 2009 use the Baserow (Sales) for 2010?

Thank you

Loa November 4, 2010 at 1:53 pm

Hi Jan! Great site! Is there a way to perform different calculations in the same column? For example I would like to show average employees for the quarter in the same column as quarterly sales totals.

Jan Harrigan CPA November 10, 2010 at 6:53 pm

Hi Jerry…I think that instead of dividing column D by BASE, you’ll probably have to divide by B200 (if B is the column for 2010 actuals and 200 is the row code for sales). Jan

Jan Harrigan CPA November 10, 2010 at 6:57 pm

Thanks Loa! Yes definitely. Do a search (on this site) for calculations and also for headcount. You might also take a look in Help for CAL or calculations. The CAL format code in the row allows you to multiply and divide. Jan

ching February 21, 2011 at 5:34 am

Hi,

I wonder if someone can help me with the calculation.
If i need one row shows
= the balance of one ledger account plus a constant value (let’s say, +100 )

How I can create that formula?

Thank you a lot if you can help :)

Jan Harrigan CPA February 22, 2011 at 12:27 pm

Sure. You’ll have the GL link to your one ledger account in column H. Let’s say it’s on Row 230 (column A in the row format). On a second row, usually one row down, you’ll enter a CAL calculation formula as “@230 +100″. You’ll probably want to make row 230 NP nonprinting. Cheers—Jan

Darryl Schroeder April 7, 2011 at 2:30 pm

How can you divide by the row code “sales” in a column formula?

I need the row to stay static across multiple column formats and row numbers can change but alpha does not.
Thank you.

Jan Harrigan CPA April 7, 2011 at 8:15 pm

I know what you mean. I tried that until I was blue in the face just a couple months ago. Couldn’t get it to work. Maybe Jeff from Microsoft will chime in if there’s a solution. :)

Paolo March 2, 2012 at 1:39 pm

Hi Jan,

In a P&L, revenue variances of actual vs. budget are usually stated as positive numbers when actual result are greater than budget and as negative numbers when expenses actual are greater than budget. How can I make FRx reflect these variances for every row of revenues and expenses.

Thank you.
Paolo

Jan Harrigan CPA March 2, 2012 at 2:38 pm

Hi Paolo…I have some information on this in tip #7 in the Top 12 FRx Tricks post:
http://www.frxbuzz.com/top-12-frx-tricks/

It will walk you through the 3 things to check.

Cheers…Jan

Stacey March 21, 2012 at 12:55 pm

i would like to divide totals on my income statement by the # patients for that month. i have the # patients for a month in a non financial gl account so i can update it each month. i can figure out how to have it print on the statement in the row format. i can’t figure out how to divide the actual totals by this on the column format.

Jan Harrigan CPA March 22, 2012 at 4:24 pm

Hi Stacey…do a search in FRx Help for “Fmt Code Column” and look for CBR. Essentially, you’ll use a CBR code in the row format to point to your stat row, then in the column you’ll divide the appropriate column by BASE: for instance B/BASE. Good luck…Jan

Stacey March 23, 2012 at 6:56 am

thank you

Jerry McLellan August 10, 2012 at 9:55 am

Jan,
I just figured out a problem with my reports with a % Variance Column. I thought I might find a trick or two that you had published, and got some ideas, but wondering if you have an easier solution – the problem is that the variance % column is totalling the %’s, not recalulating based on the totals of budget and variance amounts. I know there are the non-printing row concept that you have in this post that I can use, but the row format is used with several different column layouts, so the variance column is not only not in the same place in every column, but in some, it doesn’t even exist. Rough guess would be that I would need to set up and maintain around 15 different versions of the row format to handle the different column formats that use the row format – not feasible in my world!

Let me know if you have any general ideas!

Jan Harrigan CPA August 10, 2012 at 11:05 am

Jerry! Hey man, long time no see!

What you should do is go to the Advanced tab and change the calculation priority to ‘calculate columns first’. This is always a factor when you have a CAL row intersecting with a CALC column. One of those calculations has to ‘win’, and by default, the column wins. What ‘calc columns first’ means is this: “calculate the columns first, THEN calculate the rows”…so the row formula will win.

In Management Reporter, calculation priority is found on the Settings tab, and the verbiage is “Perform column calculation first and then row”. So the designers have made it clearer and more meaningful in MR in my opinion.

Cheers! Jan

Jerry McLellan August 10, 2012 at 11:39 am

I do have calulate columns first chosen, but the row is a TOT not a CAL. I understand changing it to a CAL, and doing a different CAL for different columns, but that is where my problem lies – multiple varied columns formats, or are you saying use a CAL that is really the same as a TOT?

And yes, it has been a very long time!

Jan Harrigan CPA August 10, 2012 at 11:56 am

Sounds like the calc priority shouldn’t have an impact since it’s a TOT row, but it also sounds like there shouldn’t be a problem in the first place. You might play with priority settings but I’d be concerned about the impact on your other reports from switching it to a CAL row. Yikes, good luck!

Patrick August 17, 2012 at 4:11 pm

I am very new to FRx. I am trying to create a P&L report that has 4 columns. Col 1 is the current month’s data. Column 3 is the YTD data. I want Columns 2 and 4 to be the % of Sales. What is the formula for this calculation?

Jan Harrigan CPA August 20, 2012 at 10:17 am

Hi Patrick…this is called relational reporting and is fairly easy to do. Look at Help in FRx, search for ‘CBR’, and click on the resulting ‘Fmt Code Column’ in the list. On the right hand side, you’ll see two screenshots that will tell you exactly what to do in the row, and what to do in the column. Good luck…Jan

Pat Herman September 28, 2012 at 3:59 pm

This is a great site. Is there a way to have parent units add the results of calculations (CALC) in detail units rather than recalculate based on the summarized data? The application is to summarize revenue volume, mix, and rate variances within an organization instead of recalculating rates at the total levels.

Jan Harrigan CPA October 4, 2012 at 11:50 am

Thanks for the compliment Pat. If I follow your question, this post from a while back may help. It’s about keeping totals from rolling up at the summary level. Good luck…Jan

Sara Russell February 20, 2013 at 11:21 am

Jan,
This is a great reference tool for FRX. I am running into errors when I enter the formula to calculate Net Revenue per Case (Net Revenue/Volume). I have the units pulling and the net rev calculations coming through just fine, but the formula is not recognizing the cell reference but the actual numbers. For example, I want to divide row 36 by row 1…the result is 36. What am I doing wrong? Thanks!

Jan Harrigan CPA February 20, 2013 at 2:53 pm

Thanks Sara. Re your calculation, you’ll use a CAL in column C, and in column D the formula will be @36/@1. The @ sign tells the calculation that this is a row number and not a ‘number’ number. This holds true for FRx or MR. If FRx, you might need to put a format mask on the row with the formula to make it show to 2 decimal places; if MR you can use column E for the format mask. Check out help for how to do that. Good luck! Jan

Deeann April 30, 2013 at 12:39 pm

Jan,
Great website! Can you help me with a calculation? I have a 12 month financial statement with monthly % calculations at the bottom, which work fine. I have one column adding up totals for YTD so the % calculations at the bottom are merely adding up the YTD %’s instead of averaging them. How do I fix this?

Jan Harrigan CPA April 30, 2013 at 12:51 pm

Take a look at calculation priority in the catalog. Gotta run: on vacation! :)

Next post: