Linking to Excel to Use Period Numbers in Calculations

Earlier this week, I wrote about how to divide headcount by the period number.

This post can be found at How To Divide Headcount by the Period Number. In the realm of “there’s more than one way to get downtown”, today I’ll revisit using the period number in calculations. Today’s method uses a link to Excel to pull in the period number.

This writeup is provided compliments of Jeff Cutting, a lead developer of FRx and its successor, Management Reporter. THANK YOU, Jeff!

Overview

How do you setup a calculation in FRx using the period number (without hard-coding the period number)?
 

Instructions/Procedure

This can be accomplished using an Excel spreadsheet and the /CPO modifier for links to worksheets.

1. Setup an Excel worksheet with one row and 13 columns. It will look like this:

2. Save this worksheet as something similar to “periods.xls”.

3. Change the Link to GL column in the FRx Row Format to a GL + Worksheet Column. On the Link menu, click Open Link Window. In the Link Type box, select GL + Worksheet. Then, in the Worksheet File Name box, select the periods.xls worksheet.

4. Insert a new row at the bottom of the row format. In the Link to GL + Worksheet column, enter “@WKS(B=A1)/CPO” (without quotes). This tells FRx to place the value from the Excel worksheet into this row in column B of the report. The /CPO modifier tells FRx to move to the right x columns, where x is the number of the BASE period in the Catalog. (For example, if the base period were 7, FRx would pull the value from cell H1 in the worksheet). In order to keep this period number from printing in the report, also place an NP in the Print Control column of the new row.

The figure below shows a sample row format with the above modifications made:

 
5. In the CALC column of the column layout, reference the new row in the calculation formula; for example, a calculation formula of C/B430 would divide each number in column C by the number in column B, row 430 (in this case, the period number you pulled in from Excel).

NOTE: This method can also be used to divide other rows by the period number. For example, a row with a Format Code of CAL can be used with a formula of @1000/B430. This would divide the value in each column of row 1000 by the value in column B, row 430.

Again, a big thank you to Jeff for this writeup.