Anybody need FRx to calculate an average?
Today’s post is compliments of Rick Tenorio. Rick is the moderator at the newsgroup at microsoft.public.frx, and he has collected a number of helpful and practical documents on using FRx. This initial post covers how to divide a headcount statistic by the period number in order to arrive at an average headcount.
Note that you can also use this technique to calculate average sales per period (use YTD revenue divided by the period number). I know a number of my clients do this in order to get an initial sense of whether their MTD revenue looks right.
This post is a little different because I’ve attached the document that Rick sent me. Here’s the link:
How To Divide Head Count By Period Report
Rick suggested that I point out that the headcount used in this example is an FRx book code used in the FW demo database. It’s not a valid book code for most companies. You should replace it with the book code used by your headcount (if your headcount stats live in a separate ledger).
Thank you Rick!
There’s another way you can accomplish dividing by the period number – this is the method I used to provide to customers years ago when I was in Support. Both methods should accomplish the same thing, but I feel this is a little easier to set up and maintain, especially if you already have a lot of columns. With this method, you do not need to add any additional columns – just one row. This makes it easier to add to your reports, and you don’t need to worry about having a column for every period – you can use this method for 12 periods, 365 periods, or whatever the number happens to be. You also do not have to change your calculation priority, which is a big deal if you are performing a lot of other calculations in the report.
1. Create an Excel worksheet with one row and 13 columns. It should look like this:
A1 B1 C1 D1 E1 F1
Periods 1 2 3 4 5
Just increment the number across the columns, up to the number of periods you need (13 columns for 12 periods, 366 columns for 365 periods, etc).
2. Save the worksheet as something like periods.xls.
3. Change the Link to GL column in your row format to a GL+Worksheet column. In the worksheet file name, specify the path to your periods.xls file (it’s best for the file to be in a shared location).
4. Insert a row somewhere in your row format. In the Link to GL+Worksheet column, enter “@WKS(B=A1)/CPO” (without quotes). The /CPO modifier tells FRx to move to the right n columns, where n is the number of the BASE period in the catalog. For example, if the base period is 7, FRx will pull the value from cell H1 in the worksheet. Add an NP to the row to prevent the number from printing.
* If you want to divide a row by the period number, use a CAL row and divide by the period row. For example, if you have an amount in row 100, and your Period Number row is row 1000, you would use a CAL formula of @100/B1000 to divide by the period number.
* If you want to divide an entire column by the period number, add the CALC column and divide by the period row. For example, if your period number row is row 1000, your CALC formula might be B/B1000 or C/B1000.
(Jan, I have a document that describes this with some screenshots – let me know if you’d like me to e-mail it to you).
Jeff
I’ve done some thinking about this today and came up with another idea. Create 12 column layouts that are identical except for the divisor in the period number in this calculation. Then use effective dates on the catalogue to specify which column layout to use each period.
I have used this method and it works great to calculate the monthly average for a calendarized income statement.
Anyone have any ideas on how to calculate weighted average? I’ve spent hours and have given up at this point.
Thanks.
Weighted by what? Would unit accounts help?
Jan,
The link provided in the original article doesn’t appear to be the correct file location. The link doesn’t load a word document. It opens a Zip file with XML files in it.
You know, someone else let me know that a few weeks ago when I was working out of town, and then I forgot to add it to my to do list. So needless to say, it didn’t get done. Will fix soon and thanks for the reminder!
Docx and xlsx files just zip files with XML parts. Somewhere along the way, the doc’s being recognized as a zip file and its extension is getting changed back to .zip. Anyway, just save it to your computer, change the extension to .docx, and it’ll open in Word.
It shows up as a .docx on my server but downloads as a .zip. I just converted it to a .doc and now everything appears to be working.
Just wanted to say THANKS. This helped me today!
Thank you Mel!!!