Dec.

1

2006

Link FRx to Excel to Calculate Statistics

by Jan Harrigan CPA

Pulling data from Excel into FRx

RevPAR (revenue per available room), Occupancy Rates, Census, Revenue per Employee, RPU (revenue per user), Utilization, Realization Rate, Per Client Profitability…these are just a few of the many performance metrics that can be measured using FRx. Every industry has its own measures of performance, and all are handled using FRx calculations.

The calculations depend on whether the statistical data lives in the GL or in Excel. For the purposes of this explanation, we’ll assume it is maintained in Excel. (For what it’s worth, I much prefer to see it in the GL, but that’s another story.)

So here’s how to pull that statistic in from Excel. There are two different ways to use Excel with FRx, and we’ll focus on the one that’s most commonly used with a single statistic, the Combined Method.

In the row for the catalog (report) that you want to link to Excel, doubleclick the column heading for column H, Link to General Ledger. The Links dialog box opens. Change the Link Type to GL + WorkSheet, and add the excel file name as shown:

2006-1201-metrics-1.jpg   

Back in the row, the syntax used is this:
@WKS(C=A3, E=A17, G=A3, I=A17, K=A3, M=A17)/CPO

 2006-1201-metrics-3.jpg

where C, E, G, I, K and M are the FRx column layout headings, and A3 and A17 are the Excel cell references.

The /CPO will make FRx pull the correct data for whatever period is being run, so you can use one Excel file with January through December in columns B through M, and FRx will count the number of periods over from column A (in this example) and pull the correct month’s data.

When the link is working correctly, you can continue in the row with a CAL format code to divide the statistic into revenue or gross margin or whatever needs to be used for your calculation. Might need to set the format as well, and it will look something like this if it’s a percentage:

2006-1201-metrics-4.jpg 

Depending on the report design, you may need to set the calculation priority in the catalog, Report Options tab, Advanced tab, to Calculate Columns First.

Note that you can also link to multiple worksheets in a workbook, but the technique is different. You use the Separate Method, but I’ll save that for another day!

Be Sociable, Share!

    { 16 comments… read them below or add one }

    yomero July 12, 2007 at 1:41 pm

    woooow!!!!!!
    I am very interest in this process, you can explain a little more
    whats happens whit the columm layout?
    thanks for you answer

    Aimee Crowe April 14, 2009 at 1:06 pm

    Can you tell me how you would do this if your information was stored in GL instead of Excel?

    Jan Harrigan CPA April 17, 2009 at 9:14 am

    Much easier then, Aimee. You just use the account number and possibly a row modifier depending on which ledger stores the stat.

    Aimee Crowe April 17, 2009 at 9:20 am

    Can you have a statistic ledger in GP? If so, where do you go to set it up?

    Jan Harrigan CPA April 17, 2009 at 9:24 am

    I only work with FRx, so you should talk to your reseller to find out how to use stats in GP. (Ask about unit codes!)

    Aimee Crowe April 17, 2009 at 9:26 am

    We have unit accounts set up. I just noticed that in the column format in FRX, you can choose a book code and the example in the manual shows what looks like statistical options. But I don’t know how to get my stuff into a book code. I’ll check with them.

    Thanks.

    Laura Osborn July 9, 2010 at 6:49 pm

    Hi, Saw your comment that you prefer statistical data to live in the gl.
    How does that work?

    Jan Harrigan CPA July 12, 2010 at 11:33 am

    Hi Laura…most mid-market GLs have different ‘books’ or ‘ledgers’ that you can use for non-financial information. They use account numbers just like the GL. Then FRx accesses these books in the ‘Book Code’ field in the column layout (and in the row format with a row modifier). So instead of having to link to Excel to pull in your stats, point to the Book that contains the stats and you enter an account number. In many cases this is much simpler than linking to Excel. Jan

    Beatrice Ackerman August 20, 2010 at 11:21 am

    I used FRx between 2005 and 2007, and have not looked at it since then. I need to know if there is a website where I can get a quick refresher and also see the features that have been added since that time.

    Thanks,
    Beatrice

    Jan Harrigan CPA August 23, 2010 at 8:44 am

    Hi Beatrice…because FRx is being phased out, there hasn’t been much new development on it, so there won’t be much change. You can always review the blog posts at this site and see if that helps any. I don’t know any other sites that focus only on FRx like I do. If you still want more, I have 2 FRx training manuals for sale, an introduction and an intermediate. They are self-study tutorials designed to give a solid foundation. You might be able to skip straight to the intermediate! Good luck! Jan

    PS. Here’s the link to the manuals: http://www.frxbuzz.com/frx-training-manuals/

    Linh Huynh September 29, 2010 at 3:56 pm

    Does the file path have to be in the IO_data folder?

    Jan Harrigan CPA September 30, 2010 at 1:30 pm

    If the file is in the IO_data folder, then you can normally just put the filename. (I say normally because IO_data is the default folder for input/output in the Company Information screen.) You can put the file elsewhere, but the full path has to be there. Cheers—Jan

    Diego Chahin November 2, 2010 at 12:59 pm

    Hi Jan

    I am pulling info from a worksheet. However, for accounts with credit balance, whenever I set the Normal Balance column to “C” it doesn’t change the sign as with regular GL links. Is this even supported?

    Thanks,

    Diego

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

    Hi Diego…whatever sign you want to pull into the report…that’s what you should put in the worksheet. Good luck…Jan

    Akber October 10, 2012 at 8:42 am

    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.

    Jan Harrigan CPA October 12, 2012 at 11:21 am

    Hi Akber…You can add the excel link to one of the rows that the TOT row references instead of the TOT row itself. For a forecast like this, though, I like to see budgets loaded in the GL, not in excel. Makes the report design a lot easier. Good luck…Jan

    Leave a Comment

    Previous post:

    Next post: