23 July 2009

Weekly Review: Account Rollup

I stumbled on this little gem in v 7.5 so I don't even know what version it was added in to but it definitely still works well in v10. If you need to look at groups of accounts this is the inquiry for you. You can even perform calculations!

What is it?
Account Rollup is an inquiry that you build which allows you to see different GL accounts rolled up together. Additionally, you can perform calculations on those accounts.

We use this for all kinds of things like:

  • What's the Fixed Asset total across all divisions?
  • What's the commission allocation based on sales?

Those kinds of things. Yes FRx will do this, but freqently, I don't want to launch FRx and run financial statements just to get a breakdown of my FA accounts. So how does this work?

In Dynamics GP, select Inquiry->Financial->Account Rollup
Enter an Option ID, tab and click yes to add the option.

You set the number of columns you want to see up in the right side. If all you want is totals, one column is fine. For calculations, use at least 2 columns.

In the center, you set the types of columns, but we'll come back to that. At the bottom, you add criteria like accounts, departments, divisions, etc. UNLIKE OTHER GP criteria areas, you can multiple independent criteria here. So if you want to see Chicago and New York but not LA. You add Chicago, then add New York.

Save and click redisplay. Controls on the right let you see net change or period balance totals. If you want to see what makes up the total number, select a month and click the blue column header.

Now, to get really fancy, you can ad calculated columns to a rollup. Simply create or modify (with the button) a rollup.

Change the type from Actual to Calculated. In the Selection column, click the blue arrow.

Back to my commission comment earlier. Let's say, I accrue 8.9% of sale as a commission accrual. My first column is my monthly sales. So I'll pick the only available column in the column box and hit the double arrows (>>) then add the multiply sign (*) then in the constant box I'll add .089 and hit the double arrows (>>).

You formula now looks something like C1 * 0.089. Save and refresh and you get a report with sales and the commission allocation for each month.

This is great for all those estimates you do every month. You can just take the numbers and do a JE.

Originally Posted by Mark 9/19/2005 02:12:00 PM