(For more resources on Microsoft, see here.)
Gaining additional reporting control with Account Rollups
Microsoft Dynamics GP provides great functionality for analyzing and reviewing individual accounts and sequential groups of accounts. Many users don’t know that it also provides impressive functionality for analyzing non-sequential groups of accounts via a feature known as Account Rollup.
Account Rollups are inquiries built to allow users to see different GP accounts rolled up together and to provide drill back capability to the details. Additionally, these queries can include calculations for things such as budget versus actual comparisons and calculations.
FRx Reporter provides similar functionality and Account Rollup allows users to access this functionality without the wait time of starting up FRx. Let’s see how to mix up some account rollups in this recipe.
Before using Account Rollups it’s important to understand how to set them up.
- To set up Account Rollups, select Financial from the Navigation Pane. Then select Account Rollup in the Inquiry section to open the Account Rollup Inquiry Options window.
- In the Option ID field enter the name Actual vs. Budget and press Tab. Select Yes to add the option. On the right, set the number of columns to 3.
- In the first row type Actual in the Column Heading field and set the Type to Actuals.
- In the second row type Budget in the Column Heading field and set the Type to Budget. In the Selection column click on the lookup button (indicated by a magnifying glass) and select BUDGET 2008.
- In the third row type Difference in the Column Heading field. Set the Type to Calculated. Click on the blue arrow next to Selection to open up the Account Rollup Inquiry Calculated Column window.
- In the Column field select Actual and click on the double arrow (>>). Then click on the minus (-) button. Back in the Column field select Budget and click on the double arrow (>>). Click on OK.
- Back on the Account Rollup Inquiry Options window, select the Segment field, and then select Segment2. Use the lookup buttons (indicated by a magnifying glass) in the From and To fields to add account 4130 and click on Insert. Repeat this process to insert 4120 and then 4100 into the Restrictions box below. Click on Save and close the window
Notice when looking up these accounts for selection that these numbers are not sequential; there are a number of accounts in between.
How to do it…
Now that we’ve built an account rollup let’s see how to make it work:
- Select Account Rollup under Inquiry on the Financial Area Page.
- In the Option ID field look up Actual vs. Budget with the help of the lookup button (indicated by a magnifying glass).
- The screen will show Actual, Budget, and Difference for each period in this year. The year can be changed at the top and the Display can be changed to show either Net Change or the Period Balance for each period in the year along with a Total at the bottom, using the controls next to the Year. The Difference field is the Actual minus Budget calculation that we created when setting up the rollup:
- Click on a period with an amount in the Actual column and select the blue Actual link at the top. A new window will open with the included accounts and the actual amounts for each account.
- On drilling down to the Account Rollup Detail Inquiry Zoom window, Dynamics GP provides a checkbox option to show accounts even if these have zero balance. Additionally, an option at the top controls the printing of Account Rollup information. The rollup can be printed in Summary or in Detail.
- Selecting a line and clicking on Balance from the Account Rollup Detail Inquiry Zoom window drills back to the detailed transactions behind the balance:
How it works…
Account Rollups combine the account totals from disparate accounts for reporting. This is great for tying back multiple accounts that roll up into a single line on the financial statements. Account Rollups also work well for analyzing a single segment, such as a department, across multiple accounts. In the past, I’ve used this for easy comparisons of Fixed Asset general ledger accounts to the subledger and for rolling up full-time equivalent of unit accounts to get the number of employees across the company with drill back to the employees in each department.
Remembering processes with an Ad hoc workflow
Dynamics GP provides options for robust workflow functionality integrated with Microsoft Office SharePoint Server (MOSS) or Windows SharePoint Services (WSS). However, for many users this is more functionality than they need. Additionally, many organizations don’t feel they are ready for the cost and complexity of MOSS. For users who only need a simple workflow to ensure that they remember the steps for a particular task, a basic workflow can be built using shortcuts and folders.
This process works well for irregular tasks such as month-end or quarter-end processes where tasks are performed infrequently enough, thus making it easy to forget the steps. For this recipe, we’ll look at setting up a basic month-end workflow.
The basic steps of this task are to create a folder to hold the workflow and then to add the steps in order to that folder. For our example, we will assume that a month-end financial closing workflow includes posting a Quick Journal, processing a Clearing Entry, and closing the month.
How to do it…
Here are the steps to create a basic Ad hoc workflow:
- Click on the Home button from the Navigation Pane on the left. This makes the Shortcut Bar available on the top left.
- Right-click on the Shortcut Bar and select Add | Folder to add a folder to the Shortcut Bar that can be used to organize entries. Name the folder Month End and press the Enter key. Now, there is a folder to hold month-end entries.
- The next step is to add our three sample entries. Select the Month End folder on the Shortcut Bar. Right-click on the folder and select Add | Add Window:
- Click on the plus sign (+) next to Microsoft Dynamics GP. Click on the plus sign (+) next to Financial and select the window named Quick Journal Entry. Change the name at the top to 1) Quick Journal Entry. Click on Add. Putting a number in front of the text prevents this shortcut from interfering if the same shortcut appears somewhere else on the Shortcut Bar:
- Next, select Clearing Entry also under Microsoft Dynamics GP and Financial. Rename it to 2) Clearing Entry in the Name field and click on Add.
- Finally, click on the plus sign (+) next to Company under Microsoft Dynamics GP and select Fiscal Periods Setup. Rename this to 3) Close Period and click on Add. Select Done to finish.
- These items will now appear on the Shortcut Bar on the left under the Month End folder. Selecting an item with the left mouse button will allow moving of these items around to adjust the order if necessary.
How it works…
Ad hoc workflows provide an option to group a set of steps together and make these all available in one place. Clicking on the arrow to the left of the folder closes it up and keeps the steps out of the way until these are needed. Clicking on the arrow again reopens the folder to run the steps. Some common uses include creating a basic set of steps for new users, monthend and quarter-end processes, and any other process where it is important to ensure that all of the steps are followed.