Archive for the ‘Financial Reporting’ Category

Running checklinks after multicurrency registration and setup in Dynamics GP

imageAfter registering and setting up Multicurrency in Dynamics GP the system prompts the user to run check links on the Multicurrency tables.

This usually isn’t that big of a deal except when there are thousands of transactions and records that need to be updated. This problem occurred for one of our clients today. The person updating the system to include multicurrency selected to add all financial tables to check links on. That was 5pm yesterday. The process completed at 10 am today and had 354k pages in the report.

It’s true. I’m not exaggerating this time. Check it out for yourself.


What’s worse is the user stayed up all night to get this process done at the office. By the time I was called at 6:30 am I had one crabby user when I mentioned “you’re calling me pretty early”. Her reply was a disgruntled “It’s late, not early. I haven’t been to bed.”

Maybe I don’t have the war wounds some of you out in the field consultants do but I can’t remember a check links process returning that many pages of updates. I’ve seen a couple hundred thousand pages before but this almost doubled it.

How about you? What’s the highest number of page results you’ve seen returned by the check links process.

Take this as a warning. Make sure you leave plenty of time when running check links on the financial tables particularly the CM tables.

Or at least bring a pillow and/or have a full pot of coffee ready.

Excel–Changing Values and reversing sign

Good day to all, here is another post that comes from working with budgets and excel spreadsheets.

Recently I had a case where the customer was trying to import budgets into Dynamics GP. All spreadsheets seemed normal, they uploaded correctly and we were under theMe impression that everything was ok for all the years we imported.

But to our surprise the customer called in and said “It appears that the sign is reversed, we verified and we entered the budgets with the wrong sign instead of (52,250.00) should be 52,250.00 can you change it? And by the way we deleted the budgets can you import again?.


Ok so my coworker started adding columns to the left and multiplying the numbers by –1 to reverse the sign, I saw that this process was a little painful and slow considering there were many spreadsheets to work on.

So here is how I helped them out. The following is a made up sample of the budget sheetEX1

I take an empty cell, and type –1, then I select that cell and copy the value to clipboard or I press Ctrl-C


After I have the value copied I select the range of values to change. Notice that the copied cell is still marked.


Then I right click on the selection and choose Paste Special


The paste special window has a section called operations, this section is able to Add, Subtract, Multiply and Divide, so I select Multiply


And after selecting the operation I click ok and my values are updated with the reversed sign.



Just imagine the potential and time saving opportunity here, if you want to subtract a fixed value to all columns or add a fixed value, this makes it relatively easy and fast.

Another example will be when someone asks you to take the numbers from last year and add 25% its so simple to just add a 1.25 to a cell, copy the value, select your range and select multiply. Try it with variations and you will experience this hidden gem from Excel.

I would like to point out that its not just for budgets but for many tasks in real life. Why I added to a Dynamics GP blog? because it originated from working with the budgets.


Have a nice day and until my next post.


Francisco G. Hillyer

Dynamics GP Fixed Assets Depreciation and Reporting

The Dynamics GP Fixed Assets module can easily depreciate assets and send that information to the general ledger. If you want to see what future asset depreciation will be you can run a depreciation projection report.

There are two steps to running the depreciation projection report:

  1. Run the projection routine: Routines >> Fixed Assets >> Projection. Specify the date to which you want to run the projection.
  2. Run the projection report: Reports >> Fixed Assets >> Projection. You can run the report at a summary or detail level, and show either annual or periodic depreciation amounts.

Depreciating the assets is simple. Just navigate to: Routines >> Fixed Assets >> Depreciate. Select the book(s) you want to run the depreciation for, and the “depreciate to” date. If you go back and look at an asset you will see the individual depreciation records created for each period.

Periodically you will want to transfer the Fixed Assets transactions to the general ledger. To do this, run the GL Posting routine: Routines >> Fixed Assets >> GL Posting. I generally specify 0000-000 as the beginning period to ensure that I pick up all transactions in the fixed assets system. You will want to be careful when you specify the Ending Period because this routine will only make one journal entry per run. So if you haven’t run this routine for awhile, you will want to run it separately for each period; starting with the earliest period first.

I have always liked the standard reports in the Fixed Assets module. The reports are basically lists of assets. These are handy to address requirements for financial audits and property tax returns. The Property Ledger is the one I find most useful: Reports >> Fixed Assets >>Inventory >> Property Ledger.

There are also three SmartList objects that are very useful. Each one has dozens of additional fields you can add as desired.

Here is a quick video about Dynamics GP Fixed Assets Depreciation and Reporting:

Intercompany Processing with Dynamics GP

Intercompany Processing is a standard feature of Dynamics GP.  It allows you to create transactions in one company and simultaneously create corresponding entries in one or more other company databases.  This functionality ensures that company databases are kept properly balanced, corresponding transactions in all companies are recorded, and maintains accurate related party balances.  The setup is really simple, and transactions can be processed in the general ledger and accounts payable modules.

In the setup screen you identify the “Due to” and “Due From” accounts that will be used when intercompany transactions are posted.  This helps you identify intercompany balances.  Here’s an example of the setup screen:

When you enter an intercompany transaction, you will need to specify that it is an intercompany transaction, and the transaction will need to be posted in a batch.  Transactional posting is not allowed.  You will specify which company or companies will be used in the transaction.  If you look-up account numbers for the distribution, you will be looking at the chart of accounts for the pertinent company.

This edit list shows the original distributions that were entered into the transaction and the “Due to” and “Due From” distributions that are automatically created based on the Intercompany Setup.

Once you post the batch, the appropriate transactions will be created in the specified company datababases, and will be held in a general ledger batch awaiting your review and posting.

Note:  If you’re using Integration Manager to create Intercompany Transactions, you will need to omit the segment separators from your account number strings.

Here is a simple demo I made to show this in action:

IRS Released 941 Report – Payroll Round 4

Hello all Francisco here again with payroll updates,

Last week the IRS released the updated 941 report and the Dynamics GP Team has been working on the update non-stop so here it is what’s coming.


This update is a code and tax table update (Oregon) to receive all the changes.  This update is all inclusive of prior updates.

It will be noted on Microsoft’s tax update pages a target release date week of 3/28/2011, as it gets closer, we will have more details.

Items included in this update (NO table changes)

I must add that this update will need to be installed on each machine so you receive the “new” 941 form.

-941 form changes (941 form, prep report and cross company reports, this is in the code)

-Oregon state tax table and code changes

-Bug fixes 10 and GP 2010 around HRP.

              New Employer Manual check amount does not distribute to GL correctly

              US Payroll edit tax liability report the 941 amount incorrect

              Electronic W2 file is incorrect for 2010 when you have HIRE Act employees and 2 or more companies


GP 2010 only Payroll cannot process manual checks with AA active Error Distributions do not match transaction amounts – still a problem after you apply fix in US payroll year end

GP 2010 only issue Company Payroll Summary Inquiry displays incorrectly

Other modules fixed in this update: Project Accounting, Dexterity, eConnect, Field Service, Analytical Accounting, Grant Management, and Manufacturing

***Supported version GP 10 and GP 2010 only***

Thanks to our friend theley

Budget Import & Export

Dynamics GP has a very easy to use budget import and export function that allows you to create budgets in Excel and update budgets in GP, based on amounts you have in Excel.

All this functionality is available from the Budget Selection screen.  Navigate to:  Cards  >>  Financial  >>  Budgets


I created this short video to show this functionality in action.

One very important caveat:  Make sure that you validate the information in GP after importing or updating budget information from Excel.  There is no error reporting on the import function if the import does not properly import the data.

I usually create a quick FRx report for this purpose.  But you could also simply export the information from GP, to a new spreadsheet (make sure not to overwrite your original spreadsheet).

Account Rollup Inquiry

The Account Rollup Inquiry screen in Dynamics GP allows you to summarize information from several accounts, and display information of several types, including: Actual, Budget, Prior Years, Other Currency, and Calculated.

Here is the summary level inquiry:


Here is the first drill down on the actual number that shows the accounts included in the rollup summary:


I created a short video to show this functionality.

Cash Flow Forecast

This is a screen in Dynamics GP that I suspect gets little use.  But it really can be quite useful because it gives you a time sequenced view of your payables and receivables, and allows you to drill down to see the detail records.

This is the basic screen:


If you click one of the days, it will bring up the Cash Flow Explorer that will allow you to see the details:


The set up is very easy, and allows you to create several “profiles” so you can take different views of the information in your system.

I created a short video to show this functionality.

Multi-company SmartLists

Here’s a nice little option in SmartList Builder which I’m surprised few people know about.  SmartList Builder allows you to create SmartLists that can report information from multiple companies in one list.

We have several real estate companies and family office clients that have dozens of GP company databases from which they want to report on rather simple things, e.g. all bank accounts, all fixed assets.  SmartLists is perfect for this type of reporting.

To have a SmartList look at multiple companies, go to the “Options” button, check the “Multicompany SmartList” check box, and select the companies you want included.


Here’s an example of a multiple company bank account list:


The problem in this solution is to find a field in the data that identifies the company to which the results belong.  In my example, this is easy because the CM Checkbook Master table includes the Company ID field that can be linked to the Company Master table, to return the name of the company.

I found this query on CodeSnippets to find all the tables in the company database with the column name “CMPANYID”

FROM sysobjects
JOIN syscolumns ON =
JOIN systypes ON syscolumns.xtype=systypes.xtype

These are the results:


If you have SmartList Builder and several GP company databases, you should try this out.

Creating Custom Word Templates using the Letter Writing Assistant

You may have used the standard Letter Writing Assistant templates that come out-of-the-box with GP, that allow you to create Word documents from SmartList data.  There are actually some nice ones that I like to show when I demo GP.

But you can also modify the standard templates, and create your own.:

  • To access the Letter Writing Assistant, navigate to:  Reports  >>  Letter Writing Assistant
  • Select, “Customize the letters by adding…”
  • Select the letter category that corresponds to the SmartList from which you want to pull data
  • Select one of the four options available
  • Select the document or action in the window:


  • Word will then open, with a blank document or the existing document you identified
  • Select the “Add-Ins” tab from the Word menu
  • Move the cursor to the spot on the Word document, at which you want the added field to appear
  • Select the field


  • The selected field(s) will then be added to the Word document
  • Save the Word document
  • Cancel the Letter Writing Assistant


You’re now ready to use the Word template with SmartList.  The template will show up with the appropriate SmartList on the Word drop-down tab.

It’s easy to do.  Try it out.

I recorded a quick video that shows this in action.

Subscription Options:
Subscribe via RSS
Articles Categories