The Not So New World Order- Cost Variances and Dynamics GP

Once upon a time, Dynamics GP calculated cost variances for FIFO/LIFO perpetual valuation method items in purchase order processing, sales order processing, and/or inventory control and didn't always post to the general ledger.  This was widely, I think, understood and the importance of printing and reviewing the Cost Variance Journal was drummed in to users collective noggins everywhere.  And then came Dynamics GP 9.0.  Yes.  9. Point. O.

Why am I writing about this now?  Well, although there are definitely articles out there on cost variances, it seems like most of them are from the pre-9.0 world or have not been completely updated correctly.  So, after a lovely chat with Pam Peterson at Microsoft today, I thought...heck, why not, let's lay it all out for everyone!

In the purchase order processing training manual, there is a section (at the end of Receivings Transaction Entry) that outlines three different kinds of cost variances related to FIFO/LIFO perpetual items.  Here they are in a nutshell.

Scenario #1

• Item has a quantity on hand of 5 with a current cost of $1
• A quantity of 15 is sold, causing the item to go negative, this sale would be recorded with a unit cost of $1
• So, in the IV10200 (Inventory Purchase Receipts table), you would now have an override layer for the amount that was overriden (in this case, 10)
• A quantity of 20 is now received and invoiced in purchasing, with a unit cost of $1.25
• This creates a variance of $2.50 (.25 variance X 10) for COGS and Inventory
• This variance will print on the Cost Variance Journal when the receipt is posted
• Now, here is where it gets tricky...
• In GP 8.0 and earlier, this variance would NOT be automatically created in the GL
• In GP 9.0 and later, this variance IS automatically created in its own GL entry

On a side note, the adjustment outlined above is only created automatically if the transaction that caused the override was recorded in the system in GP 9.0 or later.  This ensures that the "outflow" record is properly recorded in the IV10201 (Inventory Purchase Receipts DetaiL) table.  When upgrading from versions earlier than 9.0, the system arbitrarily selects the most recent receipt for an item and creates a dummy outflow for it only.  For this reason, if you test this process using an item in Fabrikam that is already negative, it will most likely NOT create the adjustment automatically.  This is due to the fact that the data in Fabrikam has been upgraded from version to version.  If you want to test out this scenario, make sure you do the whole process, and don't just start with an item that is already negative.

Scenario #2

• Item has a positive quantity on hand of 10
• A quantity of 10 is received in purchasing, with a unit cost of $1
• A quantity of 10 is then invoiced in purchasing, with a unit cost of $1.25
• This results in a $2.50 variance between the original cost posted and the the invoice (.25 variance X 10)
• In this case, the difference is handled in the posting of the invoice.  It will be recorded to the inventory account or to the purchase price variance account (depending on if revalue has been selected on the Item Purchasing Options, Cards-Inventory-Item Purchasing Options).
• The Purchasing Invoice Cost Variance Journal will print to detail the variance and will note that no manual adjustment needs to be made to COGS/Inventory.

Scenario #3

• Item has a positive quantity on hand of 10
• A quantity of 10 is received in purchasing, with a unit cost of $1
• A quantity of 15 is sold to a customer, with a unit cost of $1
• A quantity of 10 is then invoiced in purchasing, with a unit cost of $1.25 (price to customer $3/each)
• This results in a $1.25 variance between the original cost posted and the the invoice (.25 variance X 5)
• In this case, the difference is also handled in the posting of the invoice.  It will be recorded to cost of goods sold, inventory, and purchase price variance (if the item is not marked to revalue).
• The Purchasing Invoice Cost Variance Journal will print to detail the variance and will note that no manual adjustment needs to be made to COGS/Inventory.

Hope this helps to simplify cost variances, and the associated GL impact in GP.

For more information on the GL entry that is created by the variances, refer to TK #2448193.  And for more information on cost variances and perpetual valuation methods, including average perpetual, refer to this article.

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

In Defense of Classes (Inventory)

So, last week I was on phone support (which sometimes happens when the stars align and our regular support folks actually want to take the vacation they have earned), I had a case come through that was a great reminder of the benefits of classes in Dynamics GP.


Of course, there are the generally accepted benefits:

• Provide defaults for new records


• Roll down changes to records


• Group records for reporting and other processes

In all modules except Fixed Assets, classes are completely optional. However, there are some very practical reasons to use classes which I was reminded of this past week. First, let's talk inventory. When you set up an item from scratch, using Cards>>Inventory>>Item, the maintain history checkboxes are not marked by default in Cards>>Inventory>>Item>>Options. This presents a couple of issues. First, will someone actually remember to click the Options button and choose to track history for item? Odds are, sometimes they will and sometimes they won't. So, then, what happens? Well, I hate to be obvious but...inventory history will NOT be tracked for the selected item. Yes. You heard that right. No inventory history, which will then interfere with the printing of historical reports like the historical stock status. Ugh.

So...how does a class help this? Well, you can set up your item classes and mark the check boxes to track history. Then when an item is placed in the classes, those settings can default. Which can be a huge lifesaver, since items that are not marked to maintain history behave just like items that do maintain history. Trust me, the issue will only come out when you go looking for inventory and there is none.

Now, a similar issue can occur if new item classes are set up and the maintain history check boxes are not marked for the new class. How can we ensure that these options are marked by default on new classes? It's as easy as marking the "default" check box on a class.

The "default" check box is often misunderstood. It does NOT mean the selected class is the default class for new items. What it DOES mean is that the selected class is the default template for any future classes that are created. So, pick a basic class with the correct settings, and make it your default. Then when you set up a new class, it will assume the settings of the default class and then you can make modifications based on the requirements of the new class. easy.

Next up, in defense of employee classes later this week :)

Using eConnect to import Inventory Adjustments with Multi-Bin

I recently fielded a question on the GP Developer newsgroup about an eConnect error that occurred when trying to import inventory adjustments with multi-bin.

The developer was able to import positive inventory adjustments, but negative adjustments would fail with this error:

The QTY entered has to be > 0

After poking around in the GP Item Transaction Entry window, I found the inconsistency that was causing the problem.

The Item Transaction Entry window is one of the few places in GP where you can enter a negative quantity. I admit that I truly enjoy being able to type a negative number in the Quantity field, since it's such a rare treat in GP.



The negative quantity is convenient and logical for decreasing inventory quantities; however, it is not necessarily consistent with the rest of GP, and when it comes to the Bin Quantity Entry window, GP suddenly reverts to the typical positive-only behavior.



In the Bin Quantity Entry window, you must enter a positive value in the Quantity Selected field, even though the transaction is a negative inventory adjustment. Yet there isn't any field or option on the Bin Quantity Entry to specify increase or decrease.

Once you insert the bin and quantity selection, note that the Extended Quantity is negative, but the Selected Quantity is positive.



This is a pretty poor, inconsistent design that can cause confusion in both the GP user interface, as well as the eConnect API.

If you look at the eConnect documentation for the taIVTransactionMultiBinInsert node, you'll see a node called ADJTYPE, which can be 0=Increase or 1=Decrease. Why this exists in eConnect but not in the GP user interface, well, I'll leave that for others to ponder.

So to summarize, when you import a negative inventory adjustment via eConnect using multi-bin, you have to first create a negative inventory adjustment, then you have to create a positive multi-bin record, and then you also have to specify that the multi-bin record is a Decrease adjustment. Obvious, right?

Why the Bin quantity entry window (and taIVTransactionMultiBinInsert) accepts only positive values for a negative inventory adjustment is yet another historical GP mystery that I'm sure has some highly rationalized explanation, but for now I'm going to chalk it up to them wanting to keep us on our toes.

Item Setup: The source of it all

Well, first I should apologize for being buried in Sure Step curriculum lately, so I have been blog-absent :) But recently I have been reminded of the importance of item setup. It seems like the bulk of issues and misunderstandings in Sales Order Processing and Purchase Order Processing can be tracked back to decisions made during the setup of items. Sometimes, users assume the system functions one way or another, but really it is the item setup that is controlling the behavior-- which could be easily changed in a lot of instances. I chalk this up the fact that the users who set up items are often not the same users entering transactions, or so much time has passed since item setup was initially done (and understood) that no one remembers why certain decisions were made (yet another argument for documentation...can you hear the collective groan from my coworkers?).

When I teach the Microsoft Dynamics GP Partner Academy for Distribution, new GP consultants are often overwhelmed by the number of item setup screens in inventory, as well as which ones may or may not be required based on how users may or may not be using SOP or POP (confused yet?). So a couple of years ago, I put together a hand out that I share with students that breaks out the different setup screens and which modules they impact. I will summarize that handout here (it's nothing fancy), but I am happy to share the actual handout with anyone who is interested-- just post a comment on this blog or email me at christinap@theknastergroup.com. Keep in mind, this is meant to be a basic breakdown to keep it simple and clear for students, as there is a lot of grey area :)

Module Settings:
Options to control how module behaves as well as defaults
Inventory Page>>Setup>>Inventory Control
Purchasing Page>>Setup>>Purchase Order Processing
Sales Page>>Setup>>Sales Order Processing

General Item Setup:
Item Class: Inventory Page>>Setup>>Item Class
Provides defaults for:
--Item: Inventory Page>>Cards>>Items
--Item Currency>>Inventory Page>>Cards>>Item Currency
--Used to determine which currencies are valid for an item in POP and SOP
Count Cycle Assignment: Inventory Page>>Cards>>Count Cycle Assignment

General Site Setup:
Site Maintenance: Inventory Page>>Cards>>Sites
Site Resource Planning: Inventory Page>>Cards>>Site Resource Planning
Provides order policy and order point defaults from site for item resource planning (below)

General Landed Cost Setup:
Landed Cost: Inventory Page>>Cards>>Landed Cost
Individual landed costs to be grouped together
Landed Cost Group: Inventory Page>>Cards>>Landed Cost Group
Landed cost groups to be assigned to item/site combinations below

Purchasing Item Setup:
Item Quantities Maintenance: Inventory Page>>Cards>>Quantities/Sites
Provides item default site, primary vendor and landed group
Item Vendor Maintenance: Inventory Page>>Cards>>Vendors
Provides vendor item number, vendor item description, default unit of measure, last originating invoice cost, and EOQ for Purchase Order Generator
Item Purchasing Options: Inventory Page>>Cards>>Purchasing Options
Provides revalue option/tolerance and U of M options
Item Resource Planning: Inventory Page>>Cards>>Item Resource Planning
Provides order policy (use PO gen), order point, order up to level, and PO gen options (along with mass update capability)

Sales Item Setup:
Item Price List: Inventory Page>>Cards>>Price List
Provides default price level (if customer or RM setup not defined with price level), default selling unit of measure, and U of M options

So, that's it :) Easy enough, right? Have a great Tuesday!

Kits and BOMs and Components, Oh My!

It all seems simple enough when we look at the basic definition of a Kit and a BOM (Bill of Materials) in Dynamics GP.

• A Kit is a group of items grouped at the time of sale
• A BOM is a group of items assembled in to a finished good prior to sale

Simple enough, right? I normally tell people that a Kit is a SALES concept, while a BOM is an INVENTORY concept. A Kit is pulled at the time you sell it, a BOM is something that happens before the Item exists to be sold (before it is in Inventory).

Simple as it may seem, this is frequent subject of debate in discovery sessions and even among myself and coworkers, as both of these functions can be useful in different situations, leading us to use them in non-traditional ways. This week, I was part of one of those discussions regarding which feature was most appropriate for the situation. And then later this week I had another series of emails with another client debating the same thing. Practically speaking, they both sell Kits. However, they currently use BOM functionality and take advantage of some of the benefits of BOMs that support their business requirements (e.g., control over components, serial number linking, etc). However, they are also subject to the negative aspects of BOMs as well (e.g., less visibility to components for reporting, need for prior assembly, etc). So, what to do?

Well, it seems to come down to which one is the BEST fit given the requirements. So I like to break it down in to the pros and cons of each.

Bill of Materials (BOM)
Pros:

• Control over what is included in the BOM, avoiding substitutions that could affect currency amount pricing
• Ability to serialize the top level of the BOM, and link back the component serial numbers
• Ability to track kits that are sometimes pre-assembled (grouped together for quicker picking/packing/shipping) during slow periods
• Quantities tracked at finished good level (this could be a Pro or a Con, depending on your needs)

Cons:

• Have to assemble prior to sale (unless you look at the Blue Moon add on for SOP to BOM linking)
• No flexibility on sales transaction to manipulate configuration/components
• Less visibility to the components that make up the item being sold, specifically when dealing with serial numbered components (yes, there is visibility, just not as much as with a kit...my subjective opinion)
• Packing slips, picking tickets, and other documents show finished good only (not components)
• Difficulty if the return of a component is allowed, since the component is not listed on invoice

Kits

Pros:

• Flexibility during sales transaction entry to substitute items, change configuration, without setting up additional finished items/BOMs
• Not technically "assembled" , so no need to enter a separate transaction to assembly the Kit
• Packing Slips, Picking Tickets, and other documents can print the Kit item or the Kit item including components
• All quantities tracked at component level
• High visibility to components sold (in my opinion, since the components are available, including serial numbers, on the document itself)
• Ability to base Cost of Goods Sold account on the Kit or the Kit components

Cons

• Only has ability to serialize the components, not the top level Kit
• No ability to track pre-assembled quantities
• Lack of control over changes made during data entry, including substitutions which should impact currency pricing

In my mind, there is no "right" answer, and one method is not necessarily better than the other. But, one method may very well meet the specific client requirements better. And, of course, there are plenty of companies that use both (in a clearly delineated way). So the rule of thumb that I try to use is to make sure that the requirements and process are discussed thoroughly, and that the client understands the pros and cons of each approach.

Please share your own thoughts about the pros and cons of Kits and BOMs, I would love to update this post with any additional comments :)

Land of the Landed Costs- Part 2

So, here I am with part two of the landed cost saga finally. Last time, we went through the process of setting up landed costs. In our example, we were setting up insurance costs so that the invoice from the insurance vendor could be matched back to the shipments received from the product vendor. But, before we get down the road that far, let's overview the three key ways that landed costs can be used.

Landed Costs by line item

• Specify a landed cost group on a line item when recieved (can default from the item setup, from the purchase order, or manually entered)
• Calculated amounts can be adjusted by line item on receipt

Landed Costs by apportionment

• Specify an individual landed cost for the entire receipt document
• Apportion the amount across the items on the receipt by qty, extended cost, or weight
• Landed cost must use flat amount cost method

Landed Costs by invoice match

• Landed cost group specified by line item on receipt (default from item setup, or from the purchase order, or manually entered)
• Does not have to calculate an estimate amount on receipt, but it can.
• Individual landed cost must be set up for Invoice Match, and for Revalue for Cost Variance if applicable
• Use Enter/Match Invoices to enter an invoice from the landed cost vendor, and match it to the shipment from the product vendor

So, let's take a look at each of these options. In Landed Costs by line item, we can specify a Landed Cost Group on the Purchase Order line item.

Transactions>>Purchasing>>Purchase Order Entry, click on a line item, and then click the expansion arrow to the right of the Item header.

Remember, this value defaults from the item/site combination in Cards>>Inventory>>Quantities/Sites. However, it can be changed here to provide a different default to the receipt.

So, when we get to Transactions>>Purchasing>>Receivings Transaction Entry, the landed cost group will appear on the line item once again. You can view it in the same way as you did on the purchase order, by clicking on the line item and then clicking on the Item header expansion arrow. However, you may find it more helpful to view the actual landed costs being calculated for the line item, based on the landed cost group assigned. This can be done by clicking on the line item, and then clicking on the expansion arrow to the right of the Unit Cost header.

You can override the amounts specified here. The percentage or amount fields will be available based on the calculation method you defined for the landed cost.

Also, on the receipt, we can do Landed Costs by Apportionment. To do this, we simply click on the Landed Costs button at the bottom of the Receivings Transaction Entry window to open the Receivings Landed Cost Apportionment window.

In this window, you can select Landed Costs to apportion across the entire receipt. You must select a landed cost that is setup with a flat amount calculation method in order to select quantity, value, or weight in the "apportion by" field.

The system calculates each method as follows:

• Quantity: (Line item's quantity shipped - the quantity rejected)/(Sum of all line items' quantity shipped- the quantity rejected)
• Value: [(Line item's quantity shipped -the quantity rejected)*Originating Unit Cost]/Sum of all line items [(quantity shipped -the quantity rejected)*Originating Unit Cost]
• Weight: (Line item's extended shipping weight)/(Sum of all line item's extended shipping weight)

In either case, Landed Cost by Apportionment or Landed Cost by Item, the distributions that result are the same:

• Debit to Inventory
• Credit to Accrued Purchases for Landed Cost (per Landed Cost Maintenance)

So, that leaves us with the last method of Landed Cost by Invoice Match. In this example, let's assume that we posted the estimated landed cost of 10% of extended for the INSCARRIER landed cost on the shipment receipt. Now, we have received an invoice from the actual insurance carrier, Associated Insurance, and find that the costs were actually much greater. Since we set up the INSCARRIER landed cost for invoice match, we can now record the invoice from Associated Insurance and match it back to the shipment from Advanced Office Systems.

To do this, we go to Transactions>>Purchasing>>Enter/Match Invoice and enter an invoice for Associated Insurance (NOT Advanced Office Systems):

There are just a few key differences in how you enter the landed cost invoice:

• Vendor ID is the landed cost vendor
• Mark the "LC" checkbox for the line item to identify it as a landed cost
• Select the landed cost to match for the item (rather than an actual item)
• Match the landed cost to the original shipment from the product vendor using the Matched to Shipment expansion button

The distributions that result depend on whether you have selected to Revalue Inventory for Cost Variance:

If you are revaluing:

• Debit to Inventory (if cost is greater than receipt)
• Debit to Accrued Purchases for Landed Cost
• Credit to Accounts Payable

If you are not revaluing:

• Debit to Purchase Price Variance for Landed Cost (if cost is greater than receipt)
• Debit to Accrued Purchases for Landed Cost
• Credit to Accounts Payable

So, I hope this helps clarify the different ways to approach landed cost in Dynamics GP. I find that some clients will use all three methods, but many also settle on one or two ways that work best for their goals. Please share your experiences, questions, etc.

Land of the Landed Costs- Part 1

Landed cost is the total cost it takes to "land" an item on your doorstep, including additional charges like freight, customs, and processing. More and more, I find that companies are interested in landed cost as they increase the use of international suppliers and manufacturers. By using landed cost, the "total" cost of the item is reflected in inventory and is valued as an asset, rather than immediately expensing those additional costs. Take the following example, where I purchase a widget from my European supplier:

• Purchase 100 widgets @ $20/each
• Overseas shipping costs are $1500
• Customs, Duties, and other Processing Fees are $500
• Domestic shipping costs are $400

If I were to value my widgets only at the cost from my vendor, the cost in inventory would be $2000 for the 100 widgets at $20/each. And then I would expense the additional $2400 in costs.
The situation changes though, if I am using landed costs. Using the same costs above, I would value my inventory at $4400 dollars in total, or 100 widgets at $44/each. Perhaps a more accurate picture of the inventory value? In turn, this increased cost will affect my Cost of Goods Sold on the sales side, impacting my margin appropriately.
So, now that we have an example, how about talking through how Landed Costs work in Dynamics GP? With Landed Costs in Dynamics GP, these costs can be:

• Recorded as an estimate amount on a receipt
• Matched to an invoice and revalued to provide a more accurate amount

There are five key aspects to Landed Costs in Dynamcs GP from my perspective:

• Landed Cost Setup: Includes setup of individual landed costs, landed cost groups, and assignment to item/site combinations
• Purchase Order Landed Cost: Assigning landed cost groups to POs
• Receivings Landed Cost: Recording of landed cost groups on Receipts
• Receivings Landed Cost Apportionment: Apportionment of landed costs across an entire receipt
• Invoicing Landed Cost: Recording invoices for landed cost vendors and matching them back to the original product receipts

Let's take a look at each aspect. In this post, I will cover the setup items. And in my next post, I will cover the actual transaction entry. First, Landed Cost Setup.

Cards>>Inventory>>Landed Cost

This is where you can set up each individual landed cost that you plan to track. If you plan to match invoices for the landed cost, you will want to put in a Vendor ID. Matching invoices for landed cost means that (in this example above), I could record the actual insurance invoice from Associated Insurance and match it back to the shipments from my supplier for the product I purchased.
You can also select a cost calculation method, this will be how the landed cost estimate on the receipt will be calculated. If you have chosen to match invoices, the invoice amount will be compared against the original estimate on the receipt. The resulting difference can be revalued, if Revalue Inventory for Cost Variance is marked. Otherwise, the difference will post to the Purchase Price Variance account.
The GL account specified are used in the following manner:

• Accrued Purchase Account: Used as the offset to the landed cost estimate on receipts, and cleared when the amount is invoiced or matched to invoice.
• Purchase Price Variance Account: Used for the variance between invoice and receipt when matching invoices but not revaluing.

Note that no inventory or expense account is specified because the item's account will be used, since landed cost is updating the inventory cost/value of the item.

Cards>>Inventory>>Landed Cost Groups

Landed Cost Groups are used to organize landed costs in to sets that can be assigned to item/site combinations and/or to document line items like Purchase Orders and Receipts. An individual landed cost can exist in more than one landed cost group. You might choose to have landed cost groups to represent a set of landed costs for:

• A specific location
• Certain items
• A type of shipping process

In my example above, I have chose to set up a landed cost group for my domestic shipments. It includes both the insurance costs and the freight costs. Now, I can assign this landed cost as a default for certain combinations of items and sites.

Cards>>Inventory>>Quantities/Sites

This step is not required. However, if appropriate, you can assign a landed cost group to a specific item/site combination in this window. Doing this will cause the landed cost group to default on the purchase order line item, where it can be changed if necessary.

More soon on the transaction side of all of this fun!

Editing PO Distributions in Summary

So, with service packs for GP 10 (SP2 and later) and GP 9 (SP3 and later), users can no longer edit the summary distributions in Receivings Transaction Entry and Purchasing Invoice Entry. Users are required to access the Item Detail expansion, and modify the account associated with the line item.

I completely understand the change from an inventory perspective, since it has always been a difficult task to reconcile inventory to the general ledger when distributions could be modified in summary, therefore "breaking" any association between the line items and the resulting distributions. But, for the many clients that use the POP module without using inventory, this change has created a more complicated process.

At first, the majority of the complaints we received about it were related to the fact that it is more time consuming to access the Item Detail expansion than to simply edit the distributions in summary. Annoying, yes. But, yesterday, I had a client with an even more exasperating situation. In their case, they have been routinely coding single items (non inventory, of course) to multiple accounts. So, their PO may have one item, a service contract as an example, that needs to post to a variety of accounts. And, of course, it is not a fixed thing that could be addressed with an allocation account.

There is a workaround provided by Microsoft, but of course it is not ideal as it creates extraneous debit/credit transactions in the GL for non inventory items:

For non inventory items:

1. In Purchasing Distribution Entry, create a third distribution line with the same PURCH account but using an OTHER distribution type. Use this line to "wash" the original PURCH distribution.
2. Enter desired distributions using the OTHER distribution type.

For inventory items, I think the complaints are less frequent because most users do have items posting to default accounts, but there are workarounds per Microsoft there as well.

For inventory items (three different options):

1. Use fixed or variable allocation accounts
2. Receive quantities on multiple shipments, changing account on Purchase Order as needed
3. Change account on Item Maintenance or in Posting Account Setup to the appropriate default account

So, what to do? I would love to hear your thoughts, and the reactions you have gotten on this change (as necessary as it may have been from an inventory perspective).

UPDATE: Brenner from Willoware just let me know that they have included a fix for this in their GP PowerPack, www.WilloWare.com. "Not only will it allow you to again edit the PURCH distribution, but it also has a new "default" option which will calculate one distribution line per line-item received. " Thanks Brenner!