Need to calculate an individual cell in FRx? You can. The technique is called column placement.
I’ve been focused on the future of FRx for so long that it’s been a while since I posted a How To. By special request from the Wednesday presentation at Convergence (sorry again for the dreadful visual capabilities of the room we were in!), here’s how to do column placement.
Quite often in FRx you simply need to put the value in one column into a different column. This is easy in excel with by just entering an ‘=’ with the cell reference. It’s not quite as easy in FRx but it’s still fairly straightforward.
It’s easier if you think of the intersection of each row and column as having a cell reference, just as in Excel. So the reference for FRx column B and row code 1410 would be simply B1410.
But let’s look at an example. This is from the cashflow example in the FRxDemo spec set. In it, Column E calculates the YTD change in all accounts (because that’s what you need for cashflow). Yet for Cash at the Beginning of Period, we don’t want the change, we want the actual Beginning Balance. The YTD Beginning Balance is contained in column C. So we’ll use column placement to set the value in column E equal to the value in column C:
Row 1360 contains the link to the GL.
Row 1390 is the column placement row:
- Use a CAL format code.
- FRx column B is set equal to its own value for row 1360, in this case the beginning balance (due to the row modifier /BB) for GL accounts 1100 to 1110 +1600. The formula is B=B1360.
- You’d get the same value in column B for row 1360 if it weren’t a nonprint.
- FRx columns C and D are nonprinting so we don’t need to assign values in this particular case.
- FRx column E is set equal to column C’s beginning balance for the GL accounts in row 1360. The formula is E=C1360, meaning put column C’s balance into column E.
One last item: for column placement to work, you must set the calculation priority in the catalog to Calculate Columns First. What this means is that columns are calculated first, then the rows are calculated, so row calculations are dominant.
BTW I don’t do cashflows like this, but this was the simplest example I could dig up quickly.
This is a simple example but it can get complex quickly. This last screenshot shows a LOT going on in this column placement and it’s waaay too complex to boot (there are several ways to simplify this), but it does show that calculations can be done with column placement. Don’t try this at home!
Don’t forget the catalog setting: Report Options tab, Advanced, Calculation Priority: Calculate Columns First.
Hi Jan,
Good post. I just wanted to clarify one thing …
You only need to change the calculation priority to columns first if there’s an intersection of row and column calculations. In the example above, if you were setting column D=C1360, you would not need to change the calculation priority because there’s only one calculation. However, because you’re setting column E’s value and column E is a CALC column, FRx needs to know which of the conflicting calculations should take priority (i.e., which should happen first). It’s a little strange, but that’s the way it works.
Hope that helps.
Jeff
Excellent point, thanks Jeff! You’re exactly right (of course!): the calculation priority is only necessary in reports where a CAL in the row intersects with a CALC in the column.
FRx has to know which calculation should prevail, and defaults to the CALC in the column (meaning calculation priority is set to ‘calculate rows first’).
Makes sense that if D=C1360 was the only calculation in this example, you wouldn’t have to change the default priority. Thanks for pointing that out…Jan
I’ve used the formula in FRx but have yet to find a solution in Management Reporter that will return the same results. Seems MS forgot this one.
Jan,
I am trying to display revenue and expense columns by program in FRx. so our row description will have our programs and I would like to insert 2 seperate GL columns one for the revenue accounts and the other for the Expense accounts.
Ex=g
Program A Revenue A Expense A Profit A
Program B Revenue B Expense B Profit B
Program C Revenue C Expense C Profit C
How do we get this to come out correctly onthe catalog. The key is to have a revenue account and expense in one line (row).
Thanks!
The short answer is to use the Account Filter cell in the columns. The best answer depends on your account structure: I would probably simplify and use just the programs in the row, but that’s an advanced design that would likely require a bit of consulting to set up correctly. Use the contact form if you want to go that route. I consult with end users and consultants alike! Cheers…Jan
Jan –
This works great but one thing that has always stumped me is getting a GL column to calculate when the column is set to NP. For example, when creating a trailing twelve month calculation (revenue or EBITDA) you have columns for the past 23 months and create calc columns to add each month trailing twelve numbers. The calc columns can have NP and it hides the column and calculate fine as long as the first 11 months don’t have NP on the print control. Show it shows all 23 months and the TTM calc is perfect for the last twelve months. As soon as you add NP to the first 11 months it ignores them in the CALC columns. Have you found a way around this besides showing all columns and dumping to excel to hide the first 11 months?
Hi John…this is by design and works brilliantly for a forecast where depending on the report generation period, you get either an actual or budget column. You can just specify B to X and it picks up just the printing columns. To include a NP column in a calculation, you have to specify it by name instead of including it in a range. For instance, b+c+d+e+f will include all those columns regardless of whether they print or not.
BTW, I do trailing 12 month calculations in the ROW, using row modifiers. Way easier than trying to get it in the column, I think. Cheers! Jan
Hi, I have one TOT column and wanted to develop the Dynamic Forecast report, however you can not bind the WKS column with total column as the error message coming “a link may not be added to a row when format code is present (in column c)”.
HOw to solve this issue. Can you share your expeirence for this how I can print 9 months actual data and 3 months budgeted data from Excel file.
You can add the link to one of the rows that the TOT row references, so that way it pulls it in and totals it too. For a forecast like this, though, I like to see budgets loaded in the GL, not in excel. Good luck…Jan
Is it possible to have different columns linked to different G/L accounts? This is a cash flow method we use that takes Opening balance in one column, Cash flow (capital costs incurred) in another column, Profit (revenue and expense accounts related), and then Ending balance.
The problem for us is once you set up the accounts (i.e. asset accounts 1000’s) in the Row format it will only pull those. We want to pull 1000’s for opening and closing balance, but need to pull 1000-5 accounts for cash flow columns and 5000 accounts for profit column. The net will give you the ending balance.
Did I explain that well? We need mulitple columns pulling from different links to GL.
Is this possible?
Thanks, Michael
You bet. Check out the account filter cell in the column in FRx (dimension filter in MR). Make sure all the accounts you need are in the row. The row pulls them, the column filters them. Good luck!
Is there a way to reference other columns by position instead of hard-coded? For example, if I wanted to reference the column immediately to the left, is there a way to write something like [CURRENT COLUMN – 1] so that column C would reference column B, and D would reference C, etc? We have a report that has a reference like this, and I can’t see a way to have various columnsets without having to create a separate rowset for those reference fields. It would be possible to make sure that the reference columns were always in the same position relative to the “active” column, though (since they are all non-printing, anyway).
Hi Emily…the row either references all columns, using the @rowcode syntax, or hardcodes specific columns by letter, i.e. B, C, D, etc. So there’s nothing available for current column-1, unless of course a Base-1 in the Period cell would do what you want. But I’m guessing that wouldn’t work since you asked about referencing columns by position instead of hardcoding. Cheers…Jan
Hi Jan,
In the Statement of Cash Flows, is there a way to add a date instead of saying “Beggining Balance”? I would like the row format to say “Cash and Cash Equivalents, Sept 30, 2012” and “Cash and Cash Equivalents, Oct 31, 2012”. I’m not sure how to add these, or if it’s even possible?
Thanks much!
Jan
Hi Jan…in FRx, no…in MR, not that I know of. Sorry! Jan
Hi Jan,
I am creating a quarterly report and all is working fine except for one issue, I have a unit account where I only want the latest or period 3 amount……is there anyway to specify B and C Columns=0 and D column is value pulled from the GL?
For example Jan=0, Feb=0, March = GL Value without having to specify the value as it will come from the GL.
I can’t specify it in the column because the rest of the GL values are the sum of 3 months.
Any ideas?
Hi Grant…sounds like you need to use a row modifier to pull only the current period mtd amount (period 3 in this case). A row modifier overrides what’s in the columns. Then use column placement on the next row to set periods 1, 2 and 3 as needed. Good luck! Jan
I am using the NP print control in column layout, however my columns are still printing. Why? How can I eliminate printing the columns while still using the columns in calculations?
Thank you for your help.
Hi Jane…you might make sure you’re on the latest FRx service pack. You might also try selecting NP from the dropdown list in the print control cell of the column layout. I usually like to just type these in, but if you’re troubleshooting, that’s something to try. Good luck…Jan
Hi Jan,
I am trying to build a cash flow statement that included columns for month/ytd budget, prior month/ytd actual, and full year budget and forecast. I have the current month and ytd working but can’t figure out how to add the other columns. Thanks.
Hi Doug…here’s what I suggest you do: go snag a cashflow report (the whole thing) out of the demo reports that install with either FRx or MR. Use it to go by. There are calculation priority settings in the catalog to be aware of too. Meanwhile, I’m writing another manual on my method of creating cashflows. It’s actually far easier than what the demo reports use. Good luck and hope this works for you. Jan
Hi Jan, I am trying to get a column on an MR report that would show a percentage but only on certain lines. For example:
Revenue A is $2000
Revenue B is $5000
Total Revenue = $7000
%
COGS A is $1500 75
COGS B is $2500 50
Total COGS = $4000
Is there any way to produce this?
Hi Brenda…yes, you bet. The 75 and 50% (not easily seen in the comment above due to web reformatting, sorry) will be in a calculated column that sounds like you already know how to do. So the question is how to restrict and keep the non-COGS rows from printing the calculated column. The way you do that is in the row definition. In FRx, it’s column G “Column” and in MR it’s column H “Column Restriction”. You use this column to specify only the columns you want to print. So if amounts are in column B and percentages are in column C, for the revenue rows where you don’t want percentages to print, put a B in the row definition’s column H Column Restriction. (This’d be easier with a screenshot!) For the COGS rows, leave Column Restriction blank and everything will print as usual. Back to ‘Total COGS’, enter B in Column Restriction again.
The downside of this technique—it ties the row definition to this one column definition, so it can’t be used to mix and match to create multiple reports.
Good luck…Jan
Hi Jan,
When do you expect to have your manual on creating cash flow statements available?!
Thanks!
Hi Caitlin…have been so busy that it’s still on the drawing board, although I’ve gathered the materials I need. I think about it often though since I’ve had a number of questions like yours. If I tackle one, it will cover both cash flow and linking to Excel, and it will use Management Reporter screenshots. Although the technique will still be the same for FRx. Sorry I don’t have a more definitive timeline for you! Jan
Update on cashflow manual—I AM in the midst of writing it. It shows an easy cashflow (much easier than the cashflow in the demo reports), and a harder cashflow that links to Excel to split fixed assets and notes. It’s a bear to write. Hope to finish by the end of this month. Jan
In Frx, is it possible to utilize column placement and limited a row to specific units on a reporting tree? In this example, the Operations Manager wants Expense Groups across the columns (all Payroll Exp GL codes in Col B, Marketing expenses in Col C, All other expenses in Col D) with Rows limited to specific Reporting Units on the Tree (approx 400 units). Thank you for sharing your expertise.
Hi Laurie…yes you can definitely do this (although it technically doesn’t use column placement). The Column D dropdown box in the row will allow you to select the appropriate tree, then select the reporting unit you want to restrict the row to. The link to GL column (link to Financial Dimensions in MR) should contain all the natural accounts specified in the column. The columns will then filter them by payroll, marketing, etc. This solution works for MR too. Cheers…Jan
PS. There are other ways to design this as well.
Thank you for the fast response. I am still unclear on how to get Payroll Accounts in one column, Marketing in a second column and total other expenses in a third column. Could you provide more guidance on how to restrict the GL natural account segment in different side by side columns?
Sure…in FRx you’ll use the Account Filter cell in the column layout. (In MR, it’s called the Dimension Filter.) You’ll probably want to wildcard the account numbers (if your COA allows) for payroll and marketing, then depending on what’s left, you can either list them all (in the account filter) or use a calculated column to subtract the first 2 columns from all accounts. Good luck. Jan
PS. FWIW, I’ve written 2 FRx training manuals. Chapter 5 in the second one (intermediate) covers side by side departmental reporting using an account filter. Lots of screenshots, highlights, tells you what to watch out for etc. Although it doesn’t cover the dropdown box in column D to restrict the row!
In FRx I have a report with Forecast Column that is set u to add two NP (Non Printin) columns while one of the rows is set to format coded as CAL to calculate a % by dividing one of the rows by the other (like @650/@290 “#,##0.0%;-#,##0.0%”).
In the intersection of the Forecast Column and the % Row, I am getting the addition of the of the % values for the two NP colums rather than the calculation of the % for forecast column.
How can I fix this problem?
Your help is highly appreciated
Regards,
Bayo
Hi Bayo…if FRx, go to Report Options tab > Advanced tab and change the calculation priority. If MR, go to the Settings tab in the report def and change the calculation priority. This can be a concern any time you have a calc’d row intersecting with a calc’d column. You can also search this site for calculation priority. Cheers!
Caitlin (and others)…I just finished writing the cash flow manual! Thank you for asking!
Hi Jan,
I’m considering purchasing your cash flow manual to overcome a specific hurdle, but I’m guessing it doesn’t cover this eventuality, so I thought I’d drop you a post first.
I’m doing a consolidated cash flow for 5 companies, two of which are in GP with different COA’s, and three are in Excel. I have a row format with three dimensions, and the cash flow generates fine when I do it for one month only.
The issue arises in that this client does a 12-month forecast cash flow where they use actual if there is actual data, and budget if there is no actual. I’ve already built the balance sheet and income statement this way. While they are monsters and there is some MR functionality that complicates them, they do work. However, this becomes an issue for beginning cash balance because they want last actual balance plus budget net activity thereafter. If I refer to the prior month ending balance using an advanced cell placement, I get a circular reference result even though there is not truly a circular reference. Instead, I have to do the beginning balance calculation as part of the advanced cell placement formula itself which creates a HUGE formula.
If I create the same type of formula as I did for the BS using the column layout, I need to use the PB in order to get my calculations to work. However, I actually need these to be hidden columns altogether as I don’t want them to be part of the 12-month rolling cash flow report. I may also be pushing the allowable number of columns.
I know I have the option of tying back to my balance sheet directly, which I’ve been avoiding for a number of reasons, but I may try that option just because I can’t imagine it’s going to be any more difficult than trying to do the beginning balance calculation within the row or column.
If you have any advice or think your cash flow manual would help, I’m all ears!
Hi Kimberley…no, unfortunately my cash flow manual won’t help in this situation. I’ll ping you offline on another thought. Cheers…Jan
Hi Kimberley…might as well post this online for everyone’s benefit: it’s my experience (and humble opinion) that FRx and MR just aren’t designed for a cashflow forecast like this. You can conceivably get it to work, but not without chewing gum and baling wire, and it’s so easy to break. And no one can follow the work. I’d suggest getting it started in MR, but export to excel and add formulas there. My 2 cents….good luck…Jan
PS. It’s very easy to do a 12 month trend cashflow without any column placement. And the manual definitely helps with that. Although I’m just recalling that you’ve got companies with different COAs and some pulling from excel…so that definitely complicates everything.
Hi Jan,
Thanks for your prompt reply!
I’m not sure how I managed to post on this string as I thought I had posted on the Cash Flow string.
I’m now attempting down the path of linking to another report, and I’m going to see if I can get the results that I want that way. It seems like MR has the functionality required, but when you try to use all of the functionality in the same report, you start to reach its limits.
Do you know if people are using Forecaster for this kind of reporting? I don’t hear much about it anymore.
Cheers!
Kimberley
Hi Kimberley…it’s because it’s the column placement post! Meanwhile, yell if you get it. I haven’t seen Forecaster used in this particular manner. Good luck…Jan
Just letting you know that I got the report to work using a combination of advanced placement formulas and adding a separate NP column. This report was extremely complicated, and too much to post on your blog, but if someone else has this challenge in the future, they are welcome to contact me at kstevens@rarebirds.ca. I will also add that the limit for advanced cell placement formulas is 1,024 characters. I couldn’t find this anywhere in the documentation, so I ended up finding out the hard way! Cheers!
Glad to hear the good news, Kimberley, and thanks for the generous offer. And I understand about finding out the hard way! Jan
I want to know that while creating Cash flow statement in Management Reporter .How would I calculate Beginning Balance of Cash & Cash Equivalents in order to reconcile my Cash flow.
Hi Syed…I’m sooo sorry, have been out of town and your comment somehow slipped through the cracks. Cash flow is historically a tough one. I’ve written a training manual on how to do a cash flow in Management Reporter. But it’s not free. An alternative is to look at the MR demo reports to see how Microsoft does their cash flow. If you elect to go the Microsoft route, be sure to change the calculation priority in the report definition, or you will want to rip your hair out. My book illustrates a completely different technique, and is much easier. Hope this helps…best…Jan
Thank you for your response!
One more thing I would like to share that my fiscal year is 1 jan to 31st dec 2014, after closing when I view report for cash flow in Jan-15 with Base 1/31/15 no beginning balance appear..
Hi Syed…I’ve seen this a lot on GP systems. GP leaves beginning balances at zero until you close the year. Sigh. In this case, neither my technique nor microsoft’s version of cash flow will work. Until the year is closed, the best bet is to export to excel and fix it there for 2015. Cheers…Jan
Hello Jan
I really enjoy the topics you raise here :)
Nice Work
i have a question if you can help me :
I have Frx working with Analytical accounting and when i try to do some statistics in regard to number of employees or any such usage of unit account it does not apply the calculation from the row format
do you have any idea why is this happening?
Thank you for this blog
and thank you anyway even if u do not know how to help me
Hello Mohamed…thanks for the thanks! First make sure you have your statistical or unit accounts pulling from GP correctly. Then on your CAL row, make sure your formula has a format. There’s an example in my Simplify FRx Totals and Calculations post in the ‘gross profit to sales’ example. A missing format (thus rounding the calculation) is the most common problem with calculations not printing. Best…Jan