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:
Back in the row, the syntax used is this:
@WKS(C=A3, E=A17, G=A3, I=A17, K=A3, M=A17)/CPO
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:
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!