User Tools

Site Tools


faq:inventory_reconciliation

This is an old revision of the document!


Inventory Reconciliation

Check your processes

The idea behind checking your processes is to track the inventory sub ledger and general ledger entries from ordering right through to selling, and capitalization. The General Ledger balance can be seen via the trial balance report, and detail can be seen with a ledger transaction report. The Sub Ledger is a totalling of all FIFO lines. FIFO lines are the first in first out recordings of the value of your inventory. If You add up Quantity times cost for all itmes you have in stock this should add up to the balance of you inventory account in the General Ledger. When they match, you are in balance.

From the PO to the Bill

The Purchase Order (PO) has a cost, then additional columns that are added to cost that result in a total landed cost. The landed cost which includes all of the cost you place on the PO is what is used for the FIFO line. The amount still left in stock on these lines make up the inventory sub ledger (Inventory Value report in System Five). Now we can look at the bill for this product. There are some common items that show in the Bill you get from your supplier (Sometimes refered to as an invoice from your supplier.) A line to the Inventory GL account (Usually 1200), a line to one or more tax accounts (Usually 1060, or 1080), and if you did not include any other freight extra or duty costs on your PO lines for any of the freight, extra, or duty amounts. The landed total of the PO must match the total amount posted to the Inventory value account(s) on the Bill. It is possible to have multiple bills or receivings show on a single bill, but the landed total of the POs must match the total posted to the Inventory value account(s) on the bill.

From The PO to the Invoice

I bet this seems confusing. If not it should be, because in System Five an invoice is what you give to your customer when they purchase product. You also probably know that when you recieve product the next place they go is to a bill to record the Payable and expense or record the value of an asset to the GL for this product. The reason it makes sense to go from the PO to the Invoice is that when we receive the product that PO line becomes the FIFO line. When we sell the procuct on an invoice the total removed from stock is actually removed from the In Stock quantity of the PO line. If more than one receiving is needed to fulfill the Invoice the system will take them all out of stock and assign a weighted average to that line on the invoice.

What not to change

So what happens if you change a PO line, an inventory value line on a bill, or a line on an invoice? The answer is you break things, and can no longer reconcile. If you follow proper processes things should reconcile unless you change things after the fact. The one exception to note is overselling. If you oversell on an invoice you oversell at standard cost. When you receive the product at cost other than standard, there is now an outage in the system. In order to fix this issue you have to go back to all invoices that were oversold and change the cost on them to the amounts from the PO line(s) that would have filled them.

So what kind of things can cause problems if you change them after the fact? If you change either side of the PO to Bill, or PO to Invoice process you will be out of reconciliation. So editing cost or quantity of a PO line, editing cost or quantity of an invoice line, or changing the amount on the inventory line of a bill after the fact will cause problems. Manual adjustments effect the sub-ledger totals so we need to place manual adjustments in the list of things that break reconciliation. So why do manual adjustments break the system? Ans: because every manual adjustment has to have a corresponding GL adjustment of equivalent dollar value. Here is a list of some things not to do:

  • Change the cost on an invoice line
  • Change the cost or quantity on a PO line
  • Change the Inventory Value posting on a bill
  • Oversell an item on an invoice
  • Make a manual adjustment without a corresponding GL entry to that hits the inventory value for the same amount

Process Suggestions

Changing cost on an invoice line

There really is no reason to change the cost on an invoice, unless you are making up for a previous entry mistake like overselling. This only applies to completed A, or C type invoices. The costs on invoices like estimates or Work orders often do not match the value that will be applied to the invoice once it has a completed status. Note: profitability on non-completed invoice types (W, E, Y etc.) is based on standard cost and will not necessarily be accurate. Profitability will be re-calculated based on the cost used for the completed invoice type. You have probably already figured it out but the reason the cost on the completed invoice can be different on a completed invoice is because it is re-valued based on FIFO. FIFO doesn't apply to an invoice that only reserves the stock (Ex: Work orders only reserve stock, and do not remove it).

Change the cost or quantity on a PO line

If you change the cost or quantity of a PO line after the fact (Bill already entered, or product already sold), you must go back through all corresponding inventory value lines on Bill(s) and all corresponding invoice lines. On bills you change the bill total to match the new PO total. For invoice lines you need to change the weighted total of that invoice line to reflect the change.

Overselling

Overselling should never happen, but since we live in the real world where things are not always done right. How can you get product into your customers hands without receiving into your business? The answer is you can't, you just didn't record that the product came in. The first reccomendation I have is to clean up your processes if this is happening. Create an area for product to be recieved, and make sure if anyone wants to rush the process they have the training and means to receive it properly within System Five. Leaving a note that the product was taken is not good enough.

Now that I have harped on how important basic business processes are, I will explain better ways to manage when you don't have a choice but to sell the order before it is recieved.

Method 1: It is a work in progress

If you have not recieved the product, then it would make sense to say that the order the customer has is still in progress. The invoice type that reserves stock and stays in progress is a Work Order (W type invoice). Leaving the invoice as a W type makes sure it doesn't hit the GL, the quantities are not effected, and the available quantity is still correct. This situation makes sure if you are going to be behind on data entry, you at least stay consistent and do things in order. The trouble with this solution is that you need to go and receive the product onto the invoice at a later date, and you have to take any payments on Account instead of as Cash invoices. So how do you know to flip the invoice? There are paper ways to make sure you process old invoices, but there is a way to do this in System Five as well. The idea is to use an invoice sub-type. Invoice sub-types allow you to produce a report for all open work orders of a specific sub-type. The setup portion of this solution is to set up an invoice sub type in the Inventory Tree and call it Completed Oversold. Employees that “steal” product from receiving can then select that sub-type on their Work Order invoice for this customer and take payment on the payment screen of the work order, or take payment on an ROA invoice. This method will allow you to rap the knuckles of any employees that forget to do the paperwork, and catch everything not turned to a completed invoice at the end of the day, week, or month when you run this report. When the product is taken from receiving the shipper receiver needs that information in order to do the receiving later, so make sure a copy of the paperwork (The customer invoice) with a note on which product you took is given to the shipper/receiver.

Method 2: Substitute for the real thing

The idea is to leave the item as a back order on a completed invoice. Yes this will effect your back order numbers, but the method can be useful. The idea is to flip the invoice to a completed type, but leave the qty to 0 and the shipped quantity to 1 (For examples sake, change as necessary). Since this will mess with the invoice total we have to fill the remaining balance. There are 2 valid ways to do this. Most people will likely go with the first option, but if this is done in the back office option 2 is acceptable.

Option 1: Substitute a Miscellanious item

Miscellanious items are non-stock items, so they are a great way to put the product with its appropriate description in the invoice. You can even put the correct cost in for a profitability questimate. The idea is to go back to the invoice after the product is received, remove the Miscellanious item, and then change the quantity of the original item back to 1. The correct costs will then be added behind the scenes and you can complete the invoice. Note: It would be feasable to change the ordered qty and shipped qty to 0 for the Miscellanious item so that it would show a history of items that were done this way (All orders of a qty of 0 would be history). Cleanup and missed paperwork would still need to be examined, especially in the case that you don't fill in the cost of the product for the same amount as when you recieved it (This takes you out of balance on inventory value.) The idea is that if you look at the sales history of this item, anything with a qty greater than 0 was not fixed. You can check sales history on this item at the end of each day, week, or month to make sure your books are in order.

Option 2: Substitute a GL code

This is fine for the back office, because a book-keeper or office clerk is often aware of the ramifications and makes sure they choose the correct account. The idea is to post to 1 account so that you can check and see if there is a balance or any activity in this account. We then edit the associated invoice and remove the GL line or change it's ordered and shipped quantity to 0. You can check this account at the end of each day, week, or month to make sure you books are in order.

Inforamtion

The main benifit to removing the Substituted lines is if you end up with a lot of sales history for the miscellanious part or a lot of entries to the GL account for Oversell Clearing you can easily identify the out of place transactions. Just remember it is phisically impossible to give someone the product before you get it, so this scenario shouldn't happen.

Freight, Duty, and More

When considering reconciliation of inventory and entering Freight, Duty, and Extra costs there are a couple choices on how to record these expenses. In the From the PO to the Bill section above it talks about matching up the inventory value account (Normally account 1200) postings of the associated bill against the receivings on a purchase order. Here I will discuss the 2 primary methods of recording the extra changes and why you might want to do things a particular way. If we use freight as an example the two ways to do things are: 1. to include the Freight on the PO, and post the PO Total including freight to the account 1200 line(s) on the bill; 2. to not include the Freight on the PO, and post only the total of the items before freight to the bill. Please note that in both methods the 1200 account total matches the total of the PO.

Include Freight on the Purchase order, where are the differences?

WHY to Include Freight? The greatest benifit to using this method is that you include as many of the possible costs of the product as possible into its cost. It is then easier to have a more real cost of the product to mark up from. This is especially important where there are higher freight costs. Here is an example:

The product Costs $100
The freight charge is $75 to bring that item into stock
If I apply assign a %50 margin (%100 markup) I would get 2 different results
From method 1 including freight: $100 x 2 = $200
From method 2 not including freight: $175 x 2 = $300
Since the true cost to get the product to my door (Not including rent and other overhead) was $175 method one would only really give me $25 to work with to give my wholesale customers a better deal and not lose money. 
On the other hand, using method 2 allows me to realize my markup, and know how low I can really go before I start losing my shirt.
 
Another less thought obvious way to lose money is on commissions. Lets say you price the product out at $300 in both situation because prices are fixed. If you pay your sales force commission based on the profit margin you would get different numbers.
Method 1: $200 profit x %20 commission = $40
Method 2: $125 profit x %20 commission = $25

WHY NOT to include Freight? If we take a look at the example of a new company running System Five for the first year here is what happens.

When you purchase your initial $100,000 worth of product that goes onto the books as an asset. Lets say that $20,000 of this was freight.
Method 1 would value the inventory at $100,000
Method 2 would value the inventory at $80,000 and expense $20,000 to a freight expense
 
How does this effect your bottom line: (Lets say before we made inventory entries our profit would be $200,000
Method 1 the asset goes on your books just like cash which doesn't effect profitability so profit stays at $200,000 and we pay tax on $200,000
Method 2 you expense $20,000 of your expense reduces your profitability by $20,0000 making our profit is recorded at $180,000 and we pay tax on $180,000.
 
20% of $20,000 is $4,000 out of your pocket at year end the first year of operations. If you have $200,000 in inventory at the end of year 2 you will have paid $0 out of your pocket during year 2. This is because as you sell product it is expensed via the Cost of Goods Sold (COGS) Accounts, and then was received back into stock when you bough more stock. This evens each other out. The differences would tend to be very small, because next year $18,000, or $22,000 may be the total in inventory that was Freight. The amount you gain or lose on these transactions would be $400 in your pocket and $400 out of your pocket respectively.

Total value of freight in Inventory

There is a way to get the total value of the freight that is currently held in inventory. If we run a design inventory report and use the calculation field we can calculate the total of all freight for the items on the report. The calculation field should be filled in with the following formula.

COL(10)*COL(13)

Tips and Scenarios

Stock Landed Value Report vs. the Inventory Value Report

For the purposes of reconciling inventory value the report that uses the correct filters most of the time is the Inventory Value Report. There is/was an issue where serial numbers assigned on work orders were removed from inventory value total when they should not have been, so it would be a good idea to double check this. The Current version at the time I am writting this is 5.35.8.67.

Serial Number Holds

Serial number holds are considered out of stock for reconciliation purposes. It makes sense because the serial is not available on the sub ledger or the general ledger. The inventory value report reports in this way. The stock landed value report calculates based on financial quantity. The hold is still considered to be in the financial quantity until the hold has been resolved on the stock landed value report. As a result the stock landed value report is not as suited for inventory reconciliation.

Extended time between Purchase Orders and Billing

Some customers have an issue with having longer times between when they receive the PO, and when they receive the corresponding bill. This can cause all kinds of issues and hurdles to overcome. This first I am interested in is that it is difficult to reconcile the General Ledger and Sub ledger. I have come across a great way to do this that uses a Purchases Clearing Account.

Information on Using a Purchases Clearing Account

The idea is to post the associated amount from the PO to a Purchases Clearing Account on a Bill from that Supplier. Since you don't have the bill number yet, I suggest using the format of POPoNum. this should make it easy to determine that this is a placeholder bill. Also it is easy to reconcile these at the end of the month because you just look through the Purchases Clearing Account at month end to see if there are any bills in it. These bills are outstanding. When you receive the actual bill I suggest moving the current bill number to comments or to the description, and then adding the real bill number to the bill. You then also need to remove the line that posts to Purchases Clearing, and add a line that posts to your Inventory Account. If there were any outstanding bills in the Purchases Clearing Account that are completed you can complete them as described and then check your inventory Value. If you just checked your inventory value and it is still out, you should notice that the difference is accounted for by adding the total of the Purchases Clearing Account to the Inventory Account. If you use the default Inventory GL Aaccount of 1200 it would make sense to Use 1210 or a simular account for Purchases Clearing.

Alternate Usage

The general idea that helps us use the purchases clearing account can also help us organize other parts of the relationship between PO's and Bills. For example, if you want to be able to see which PO's you have received but have not entered bills for the same technique will work. The idea in this case is that the purchases clearing will act as a report to show you the age of all open PO's. This can be very useful in preventing late fees and allowing you more easily pay early for additional savings. There are methods within the billing process to calculate when early payments should be applied, but if you don't have a bill entered it can't be done. Here is a link to a video on how the process might look for this alternate usage including some setup considerations: Alternate usage for purchases clearing

Benefits

The greatest benifit is that as long as you have posted the associated pending bills to the Purchases clearing account you can reconcile your inventory value at any date. Depending on how you remove the Purchases Clearing line from your bill you can actually see the history of the inventory value in your aged reports as well.

Aging history

What I mean by you can actually see the history of the inventory value in your aged reports as well is that if you take the time to reverse the amount posted to the clearing account on a new bill instead of editing the original bill you will be able to see a correct aging to the purchases clearing account. The Amount will then hit the 1200 on the date you receive the bill, and in essense record the history of what happened when. If you edit the original bill then the change will reflect in the 1200 account on the date you leave on the original placeholder bill. This placeholder bill is dated to the same date as the PO by default, so that is when if will show in the Inventory for aging purposes.

IMPORTANT

If you have not previously noticed our backup reccomendations here is a very important re-cap that specifically relates to inventory reconciliation. Besides the daily backups we highly reccomend you archive monthly backups. This way if you ever need to take a detailed look back at your inventory value report (You Inventory Sub-Ledger in Accounting terms) you can load this archived backup to your training directory and look into any issues. Please note that archiving means you Label the backup correctly ex: Data_Sept2007MonthEnd_EODSep30_2007.zip, or Data Sept2007 month end.zip. The reason this backup is so important is that in System Five the inventory is always live, and because of the processes and adjustments the can be done that don't effect inventory tracking, there is no way to properly create a historical inventory value report. Please note that as a company we are taking steps to change this with our Advanced Inventory control, but the reporting will still have to be created in this new version.

My Purchase & Sales History doesn't Match My On Hand Stock

Month End Reporting

There are month end reporting and procedures that can help you keep on top of your inventory value. At month end please check the GL balance for Inventory against the Inventory value report to see if there is a difference. Doing this should produce the same result at any time when other users are not in the system. If they are in the system they might have a transaction open or in progress that would prevent the 2 from matching. It is best to do this comparison at the end of day with no-one in the system to avoid all timing issues.

Once you have identified and fixed the associated problems it is a good idea to attempt to determine how this was caused. Sometimes the best way to procede is to adjust the process you use to do the entry. Other times the problem can be the result of a problem or glitch in the program. In this scenario we must submit this to programming to fix, but it is best to go through support for this so we can make sure the needed information is passed along (This is neccesary to assist in getting the problem fixed in an appropriate amount of time). There is a third type of problem where the program doesn't stop you, the user from making a mistake. We like to try and make the program smarter, and more intuitive but these are not often critical issues so we will give you work arounds or better processes to use until this issue is addressed.

Sometimes it is not so easy to tell what caused the problem. In order to seek out where the trouble is there are some reports and routines we can use. It is often neccesary to qualify whether we should be using this report, so I will try to add some information that might indicate you should use this report of routine.

Remove Negative Stock Lines (Version 5.35 and earlier)

Qualifier

If you have any overselling or receiving problems then it would be a good idea to consider using the remove negative stock lines routine. (5.35 or earlier). Things have changed in inventory 5.40 and on so there would be no remove negative stock lines routine to run.

Information

The idea behind using this routine, which must only be done after a backup, is to check the value of your inventory before and after the routine has been ran. If there is a change then you know that there were Negative Stock lines in your system that were removed, and that cost on these stock lines did not match the cost of the Stock Lines they were applied against. Warning: If you did not run the routine with the Ignore costs on PO's option checked then the comparison above will not be applicable because the routine will not mess with lines whos costs don't match. When running this routine, unless you have a very high level of control and awareness of your Stock Lines, there is no reason not to use the Ignore costs on PO's option. The rational for this is that negative lines are really fictitious, so the cost on the positive line should be used in most cases.

How to Run the Routine

As mentioned earlier, please log everyone out of System Five and perform a backup. Also this routine should not be run while anyone else is in System Five. Please open the tool box routines by going to the Menu or Navigator > Setup Tools > Utilities > Tool box. Then after answering the backup question go to Inventory Routines > Fixing Routines > Remove Negative Stock Lines. Make sure the option Ignore Cost on POs is checked and click the Remove All Negative Stock lines button.

Definitions

Stock Lines

Stock Lines are the Lines from your PO's, and manual adjustments and the quantity still left in stock from these lines. The can also be described as the listing of First In First Out (FIFO) lines.

Inventory Quantity Check Report (Version 5.35 and earlier)

Qualifier

This report is quite dangerous to run if you don't know exactly what you are doing. It should not be done until after a backup, and will update all quantity on hand figures for your inventory with a single click of the mouse. There are 2 main sections of this report, one for Rebuilding Sales History on Items, and the main purpose to check the system for problems it can self detect with your quantities.

Information

How to Run the Report

Definitions

example phrase

The sale must procede at all costs. Idea, consider what the TCO of each of those decisions.





Author: CMackay
Editor: CMackay
Last Edited: 14-Feb-2008 (Work in Progress)

faq/inventory_reconciliation.1258495412.txt.gz · Last modified: 2009/11/17 14:03 (15 years ago) by cliff