GPTip42day - Reconcile Accounts Payable Account

There are a number of causes for control accounts not tying to their sub ledgers, including manual journal entries to the control account, transaction posting to incorrect accounts (should have posted to AP but posted to a different account), posting to incorrect periods, voided documents posted to the incorrect period, incorrect settings in the AP Trial Balance report definition, etc., etc.  (Make sure you're running the 'Historical Aged Trial Balance' and in the 'Exclude' section, that all boxes are checked EXCEPT for 'Credit Balance'.)

The task of reconciling used to be a hunt and peck process before GP10. If you’re using GP10, then you have the 'Reconcile to GL' tool available to you. This tool analyzes the transactions in the sub ledger (in this case, the AP Trial Balance) and compares those transactions to transactions in the GL. Where a definite match occurs, such is indicated on the Excel file produced by the Reconcile tool. Where a match doesn't exist, or the match is not 100% definite, those are indicated in the Excel file also.

However, if you're on GP9 or a prior version, then the task is more difficult. You'll need to analyze your AP account transactions to determine the source of the transactions and whether they hit the correct period. SmartList is a great tool for analyzing individual transactions. Once you have the offending transactions isolated, fix what you can. Any remaining difference will likely require a journal entry to bring the AP account into balance with the APTB. Once you're reconciled, go to the AP Account Card and uncheck the 'Allow Account Entry' box to prevent manual journal entries posting to the account. In fact, you should do this for all your control accounts.

I also suggest to my clients to reconcile the account more frequently than monthly. Biweekly or even weekly may make sense, depending on the transaction volume in your environment. It's a lot easier to find these little glitches and correct them when the volume of data to pore over is less.

Oh, one other thing, check out The Closer from http://www.reporting-central.com/. This is a 'Reconcile to GL' tool on steroids that works with any version of GP. If I were responsible for a GP environment, this would definitely be in the toolbox.

GPTip42day - Writing Off Damaged Inventory

Prior to writing off Damaged (or In Service or In Use) inventory, you must transfer the quantity to write off to the On Hand Quantity Type. 

Inventory>Transactions>Transfer Entry

* click image to enlarge

After entering the item number, quantity to transfer, and Site ID, click on the Quantity Type button which opens the Quantity Type Entry window.  Select Damaged as the From Type and On Hand as the To Type,  Click OK.  (If the item is serial or lot controlled, the serial or lot selection window will open when you click OK).

Click Post.  The quantity will be transferred to On Hand, from where you can perform Transaction Entry to adjust the quantity out of inventory.

GPTip42day - Print Separate Remittance for Payables Checks

Did you know you can choose to print a separate remittance for your records when printing a check run?  When processing the check run, check the Separate Remittance box in the Print Payables Checks window.

* click image to enlarge

Checking this box will produce a remittance for each check in the run like the one below.

* click image to enlarge

Note: Any zero dollar check or check with more than 12 document numbers listed on the stub will automatically produce a remittance.

GPTip42day - SA vs. DYNSA

What are the primary differences between SA and DYNSA users in GP? 

1.  SA is the SQL database system administrator and has full  access to all databases on the SQL Server, including non-GP databases.  DYNSA is the GP database administrator and has access to all GP databases only.

2.  SA and DYNSA are both granted Power User roles automatically in GP Security. 

3.  SA is required for certain tasks such as initial system installation, payroll updates, and to administer some 3rd party applications.  Generally speaking though, DYNSA can perform most of the security and maintenance tasks in GP that SA can.

4.  From a SQL database security perspective, DYNSA should be used by your GP administrator for day-to-day administrative duties.  SA use should be reserved for SQL administrators where possible to reduce the possibility of tampering in non-GP databases.

GPTip42day - FRx - How do I setup my Balance Sheet to reflect current YTD balances before I've closed the previous fiscal year?

This is a common issue when you leave the previous fiscal year open into the new fiscal year.  Because the new BS balances haven't been rolled forward yet, the balance sheet is inaccurate.  Here's how to resolve it.

1.  In the Row Format, use 3 rows to calculate Retained Earnings.  The first row reflects the prior year RE account balance.  The second row reflects the total of all P&L accounts.  Make these two rows non-printing.  The third row is the total of the first two.  Make this a printing row.  In conjunction with what we're going to do in the Column Layout, Retained Earnings for the current year will be correct.

* click image to enlarge

2.  In the Column Layout, use 3 columns to calculate the current year balances.  The first column reflects the prior fiscal year account balances.  The second column reflects current year activity.  Make these two columns non-printing.  The third column is the total of the first two.  Make this a printing column.

* click image to enlarge

Voila.  A current year balance sheet before the prior year is closed.

GPTip42day - Activity Tracking

If you need to track certain user activities in GP such as master data creation or transaction posting activity, use Activity Tracking to do so.

Administration>Setup>System>Activity Tracking

* click image to enlarge

To turn on Activity Tracking, select the Activity Type, place a checkmark in the Activity, select User(s) and place a checkmark in the appropriate Company(s).

You can track multiple activities but beware that the Activity Tracking table can grow very large in a short period of time if many activities are tracked.  Therefore, turn  tracking on for only those activities that are essential and clear the table on a regular basis via Administration>Utilities>System>Activity Detail.

GPTip42day - Adjust Cost

If you need to change the cost of an inventory item, here's how to do that.

Inventory>Utilities>Adjust Cost

* click image to enlarge

1.  Select the Item Number

2.  Select the Site

3.  Select a Date Range, if desired

4.  Identify the receipt(s) to be changed

5.  Change the value in the Cost field to the desired cost

6.  Click Process

When you close this window, a general posting journal will be printed if there are changes to  Cost of Goods Sold. COGS changes can occur if a quantity of the receipt has already been sold or consumed.

A GLTRX batch is created that includes an entry to record the adjustment to Cost of Goods Sold (if appropriate) and an entry to adjust the GL inventory value of the item adjusted.

For more information on the Adjust Cost Utility, see the Help file by clicking on the Help button in the Adjust Cost window.

GPTip42day - Unit Cost in SOP Transactions

Need to prevent the display of Unit Cost in the Sales Transaction Entry window?

* click image to enlarge

Go Sales>Setup>Sales Order Processing and uncheck the Display Item Unit Cost checkbox.

* click image to enlarge

GPTip42day - Revalue Inventory for Cost Variance

When this box is checked in Item Class Setup, any variance between Shipment Receipt Cost and Invoice Receipt Cost will be posted to inventory and the Item Cost will be updated to reflect the Invoice Cost.  This is fine if you still have the items in stock when the invoice is matched to the receipt.  However, if there's a lag between the shipment receipt and posting of the vendor invoice, it's possible the item(s) could have been sold or otherwise consumed.  If that's the case, the variance will still be posted to the inventory GL account but there will be no on-hand balance to adjust, creating a potential inventory reconciliation issue.

Inventory>Setup>Item Class

* click image to enlarge

The preferred option is to uncheck this box and allow any cost variances to flow to a 'PO-Invoice Variance' account in the P&L (or, alternatively the balance sheet).  This will help maintain the integrity of your book inventory values.

GPTip42day - Transferring Inventory Items between Projects

How do you transfer an inventory item from one project to another in Project Accounting?  Use the Inventory Transfer transaction.

Project>Transactions>Inventory Transfer

* click image to enlarge

Use the Return Transaction Type to return the item to inventory from Project A, then use the Standard Transaction Type to transfer the item from inventory to Project B.

GPTip42day - Editing the GP Launch File

When GP and any associated 3rd party applications are installed, a file is created called Dynamics.set, aka the Launch File.  The Launch File contains instructions as to which products to initiate when starting GP and where the applications, forms, and reports dictionaries are stored.

Should you need to edit this file, for instance, to change the location of the reports dictionary to a shared directory, go

Administration>System>Edit Launch File 

* click image to enlarge

From this window you can select the Launch File to be edited, the relevant Product (GP, Project Accounting, Fixed Assets, etc), and modify the path to the respective dictionaries.  Click OK to save changes.

The Launch File can also be edited using a text editor such as Notepad by navigating to the Dynamics.set file on the workstation, making changes directly in the file, and saving those changes.  When doing so, it's best practice to save a copy of the file prior to making changes in case you need to revert back to the original file settings.

GPTip42day - Mass Modify Chart of Accounts

This feature is very helpful if you need to copy a certain range of accounts to create a new range.  For example, let's say you need to setup a new department in you chart of accounts.  Rather than setting each account up one-by-one, use Mass Modify to set them all up at once.

Financial>Cards>Mass Modify

* click image to enlarge

The first step in the process is to select the type of change you want to make.  In this example, we choose Copy.   Then we select the origin for the description - Existing Accounts.  Next, enter the new department number in the Account Mask.  You can choose to sort the accounts in the scrolling window by account or by main segment.  You can also choose to display all accounts or just the selected range of accounts in this window.  This is just a viewing preference and has no affect on the accounts to be copied.

Click Modify.  All new accounts will be created with a department number of 15.  If there are any existing accounts that fall in that range, you will be warned that duplicate accounts exist and given the option to copy these accounts or not.  However, because GP doesn't allow duplicate account numbers, you will have only one of each account after the copy process.

After the process completes, check your chart of accounts to ensure that the new accounts were created.

Mass Modify works on Posting, Unit, Fixed, and Variable Allocation accounts.

ERP Implementation Strategies

Our friends over at http://www.softwareadvice.com/manufacturing are taking an informal survey about ERP implementation strategies at http://www.softwareadvice.com/articles/manufacturing/erp-implementation-strategies-1031101/. They want to determine which implementation strategies are the most popular and most successful. Big bang, phased rollout or parallel adoption.

If you have a chance, click on the link and give them your input.  Thanks!

GPTip42day - Miscellaneous Recurring Batch

Need to do a recurring batch but none of the standard recurrence frequencies suit your needs?  Use the Miscellaneous frequency.  The Miscellaneous frequency allows you to specify the number of recurrences and the number of days between each recurrence.  For instance, let's say you need to post the same transaction daily for 30 days.  Here's what it would look like -

* click image to enlarge

GPTip42day - The Purchase Receipts report

Have you experienced the frustration of trying to perform a Returns Transaction in Purchasing, only to have the system tell you there are no quantities available to return against a PO?  Here's how to determine if there are quantities remaining on hand for a specific receipt.

Inventory>Reports>Activity>Purchase Receipts

* click image to enlarge

Note that each receipt provides the Quantity Received and Quantity Sold.  When the Quantity Sold equals or exceeds the Quantity Received, no returns can be processed against that specific receipt. 

Using this report will help you determine which receipts have quantities left that can be returned.

GPTip42day - Printing MICR checks

If you print MICR (Magnetic Ink Character Recognition) checks, are you still buying special MICR cartridges for your checks printer?  Check with your bank to make sure MICR toner is really required to process your checks.  Optical Character Recognition (OCR) software and hardware has improved over the years to the point that many banks use OCR readers in place of or in addition to MICR readers.   In addition, the regular toner used in LaserJet printers is somewhat magnetic and can usually be read by banks that are not using OCR.

A phone call may save you big $$ in toner costs!

GPTip42day - Lock that Budget!

Ever notice the little padlock-looking button next to the Budget ID field in Budget Maintenance?  The button allows you to password-protect your budgets so that other users can't change the data in them.  Just click on the padlock, enter a password, and you're good to go.

* click image to enlarge

GPTip42day - Where do I enter Manufacturing Registration Keys?

Typically, upon installation of Manufacturing, the Manufacturing Series Registration window opens upon first login to GP.  However, if you don't choose to enter the keys at that point, how do you find the registration window later?  The MS developers stuck the window in a neat little hiding place in GP10.

Administration>Setup>System>Registration

* click image to enlarge

With the registration window open, click Additional and Manufacturing Registration.  The Manufacturing Series Registration window opens.

* click image to enlarge

There it is!

GPTip42day - Miscellaneous Charges, Services, & Flat Fees

What are these Item Types, how do they differ, and how do they differ from Sales Inventory and Discontinued? 

These Item Types differ from Sales Inventory and Discontinued items in that Sales Inventory and Discontinued items track both cost and quantities.  The above Item Types do not track both.  Also, Sales Inventory and Discontinued items represent traditional inventory items that can be stocked, costed, and planned.

GPTip42day - Excluding non-1099 amounts from Payables Transactions

This tip comes compliments of fellow MVP Leslie Vail.  Sometimes, especially where subcontractors are concerned, you need to post an invoice which includes both 1099 and non-1099 amounts.  For instance, you may be paying the subcontractor for reimbursable expenses they have incurred in the course of providing service to you. 

Here's the easy way to segregate the two amounts.

Transactions>Purchasing>Payables Transaction Entry

* click image to enlarge

Enter the full invoice amount in the Purchases field.  Enter any discount, tax, freight, etc.  Then, enter the 1099 Amount in the 1099 Amount field.  Note that you can also open the Payables 1099 Entry window by clicking on the blue arrow next to the 1099 Amount field.  Opening this window allows you select the Tax Type and the 1099 Box Number you want the amount to appear in.  It's that easy!

A couple of notes -

1.  You can perform the same actions in the Receivings Transaction Entry & the Enter/Match Invoices windows.

2.  The 1099 Amount isn't posted until the invoice is paid.  If there are any discrepancies between the 1099 Amount entered and the actual amount paid, the 1099 Amount is adjusted to reflect the actual payment.

GPTip42day - Resolve Batch 'Stuck' in Posting

Occasionally, a batch will 'hang' during the posting process and not show as available to be recovered in Batch Recovery. When this happens, follow these steps to 'unstick' the batch. Note: to be performed by GP Administrators only.

1. Run the following script against the company database. Replace XXX with the batch number or the name of the batch that you are trying to post.

UPDATE SY00500 SET MKDTOPST=0, BCHSTTUS=0 where BACHNUMB='XXX'

Note: The value of BACHNUMB is the same as the Batch ID in GP.

2. View the contents of the SY00800 table located in the DYNAMICS database to determine the value of the DEX_ROW_ID field for the batch that you are trying to recover. You can view the contents of this table by running the following script:

SELECT * FROM DYNAMICS..SY00800

Note: If no results are returned, go to step 5.

3. Run the following delete script, based on the results received in step 3. Replace XXX with the DEX_ROW_ID value of the batch that you are trying to recover:

DELETE DYNAMICS..SY00800 where DEX_ROW_ID= 'XXX'

The preceding steps will free up the batch for edit and posting. Once the batch is again available:

4. Verify the accuracy of the transactions.

5. Verify that you can edit and post the batches.

Sharpen the Axe - Part 1

Have a look at my latest article on MSDynamicsWorld.  This is the first of three articles leading up to my presentation at MSDynamicsWorld's virtual conference - Decisions2010 in May.

http://msdynamicsworld.com/story/accounting/sharpen-axe-and-increase-your-microsoft-dynamics-gp-productivity

GPTip42day - Remove Bank History

If you find that loading transactions when using Checkbook Balance Inquiry takes a long time, you might want to Remove Bank History. 

Tools>Utilities>Financial>Remove Bank History

* click image to enlarge

This process allows you to remove transactions from completed bank reconciliations from the database, thereby allowing inquiries to run faster.  Of course, prior to running this process, yoiu should take a backup of the database and archive it, in case you ever need to restore the transactions you're about to remove.

Start by selecting the History Type you want to remove.  Then, you might want to select Print Report Only to view the transactions you're about to remove prior to the removal process.

Next, select Reconcile Audit Trail Code (the completed bank rec) in the Ranges dropdown and select the code(s) you want to remove.  Click Process and the report will print.

Finally, run through these steps again only this time, select Remove History and Print Report.  The report will print after the removal process completes.  Print the report to a .pdf file for archive purposes.

You may have to run through this process several times, depending on the type of transactions you want to remove.

GPTip42day - What does Checklinks do?

Occasionally, data corruption can occur in tables due to power failures, printer failures, network failures, etc. The most common indicator of data corruption is an alert message that indicates an error in a specific table. However, data corruption may not always be this obvious and it may be more difficult to identify in which table or tables it has occurred. Other indicators of data damage can include:

    - Alert messages that you can’t explain

    - Inaccurate data in windows or on reports

    - Unusual characters in windows or on reports

    - Windows you’re unable to open

When data corruption is suspected, running Check Links is a method to resolve the issue. Some of the information in table groups is stored in two or more of the tables that comprise the group. For instance, the Sales History table group includes the

    - Sales Batch History

    - Sales Transaction History

    - Sales Deposit History

    - Sales Transaction Amounts History

tables. If information in one table is missing or damaged, the Check Links program examines related tables where the same information is stored, and re-creates the damaged record in the first table.

File>Maintenance>Check Links

* click image to enlarge

Note: If the damaged table is in the System or Company series, you want to Reconcile the damaged table or tables instead of Checking Links. In these series, table groups contain only one table, so there are no other tables for the damaged table to be compared to.