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 totaling 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 items you have in stock this should add up to the balance of your inventory account in 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 the cost that results 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 makes 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 referred 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 receiving’s 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 a product. You also probably know that when you receive a 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 product 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 a 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 the cost or quantity of a PO line, editing the 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 affect 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 are some key points:
    • Do not change the cost on an invoice line
    • Do not change the cost or quantity on a PO line after PO is completed
    • Do not change the Inventory Value posting on a bill
    • Do not oversell an item on an invoice
    • Do not make a manual adjustment without a corresponding GL entry that hits the inventory value for the same amount


Process Suggestions

Entering bills in a way that allow them to match up to the PO

The problem can easily be described this way: My bill does not match the PO that I received the product on, how do I correctly proceed? If our supplier doesn't respect the prices they have quoted us, then it might be better to address getting them to provide the correct information on pricing, or respect the prices they have given you. If your employees can't enter the information correctly then maybe you need to address loading price lists in the system to save on manual entry errors.

If the scenario is not that simple and you just have to live with it then there are options. First of all, it is important to understand how things work. Basically, the price we put on the Purchase order will determine the price an item goes out of stock with when it is sold on an invoice. Also, it is important to realize this method works whether the problem is an error, a discount, or another reason the change needs to be made.

Example: On a purchase order a widget is received for $100 I receive a bill and am charged $80 because I got a discount on the widget I then sell the item on an invoice and it is removed from stock at $100 and expensed to its appropriate cost of goods sold account.

In this example, we didn't pay the $100 for the widget and were only billed $80. What should our PO look like? What does the bill look like?

It is important to note that it is a valid and correct accounting practice to record the value of the widget at $100 on your books. Here is how we properly deal with this scenario. First, we do not change the PO as $100 is accurate. Secondly, the Bill has to total $80, and the inventory account (1200) must equal $100 from the total landed value of the PO.

To correctly enter a bill at this point we only have to make a minor adjustment. Here is what the new bill should look like: Please note the 5000 accounts could be for discounts, or for errors in purchase order entry, the COGS account for the product being changed, or whichever other task may be relevant. Choose the appropriate account.

1200 ............ $100 5000 ............ -$20
  • Notice that the 1200 account for $100 matches the total landed value of the PO. Your books will go out if this does not happen
  • Notice that the total of the bill is accurate.
  • Potential drawback: When you look at the profitability of selling this item on an invoice the cost will be $100, not $80 so profitability will be less. Remember though that we already recognized this difference in the 5000 accounts making our GL look better for profitability.
  • What if my invoice profitability has to be $80?
    1. You are in for a world of complications, exceptions, and possibilities to make mistakes. You must manually change the cost on the invoice, change the bill back to only one line to 1200 for $80, and edit the PO.
    2. You may notice that when setting the cost on the invoice that neither $80 nor $100 is used. You may not have actually sold this item on this invoice according to FIFO rules, or there may be more than one stock line involved requiring you to calculate a new average weighted cost. There are reasons we suggest other methods of properly accomplishing this task, please request assistance from our support team to learn how to perform these complicated tasks. Some scenarios are much easier than others
  • Why is a 5000 level account used instead of a 6000 level account? This is because when you view GL profitability, only 4000 and 5000 level accounts are used. 6000 level accounts don't come in until you do financial comparisons for the entire set of expenses including rent etc.

Multi-currency discussion on entering bills to match the PO

Multi-currency transactions add another element to your transactions for the purpose of inventory reconciliation. There are a few best practice requirements that I should mention before letting you know the standard way to make sure your PO and Bill entry procedures don't cause outages.

  • One supplier account for each currency you work with a supplier, and all activity for that supplier be in that one currency. It is important for reconciliation to only use one supplier account for that one currency.
  • Your inventory account must have a currency on it and that must be your local currency
  • You must set your exchange rate at the start of the day before any entries are made, and may not change the rate after the fact.
  • May add more to this list later as I remember more of them. This is a good start though.
  • Receive your PO and take note of the date and total landed value of the PO
  • Enter a bill and make sure the inventory value account matches the total landed value of the PO, and the date matches the receiving date of the PO.
    • GAAP ALERT: If you have multiple receiving's, each receiving must have its own bill that exactly matches the date and total landed value.

Explanation:

GAAP stands for generally accepted accounting principles. One of those principles is that the inventory is recorded to your accounting system on the date it is received. This principle simplifies all the scenarios and details into one idea. If you stray from this, you must make sure you have made all of the appropriate adjustments, as things may not add up in System Five after you sell the product.

Excerpt from Federal Accounting Standards Advisory Board -------------------------------------------------------- 38. Recognition. The consumption method of accounting for the recognition of expenses shall be applied for operating materials and supplies. Operating materials and supplies shall be recognized and reported as assets when produced or purchased. “Purchased” is defined as when title passes to the purchasing entity. If the contract between the buyer and the seller is silent regarding passage of title, title is assumed to pass upon delivery of the goods. Delivery or constructive delivery shall be based on the terms of the contract regarding shipping and/or delivery


In multi-currency systems, you will start by entering a PO for your supplier just like in a non-inventory system. The only real difference is that there are different currencies you can view the PO in. It will default to viewing the PO in the currency of the supplier you chose. When you receive the purchase order it is like taking a snapshot in time as all the items received are then permanently set to be the currency of your inventory value account (your local currency). The PO has created the sub-ledger entries and valued them in your local currency at the date of receiving. They will now stay stable at that value in your local currency. This prevents your inventory from changing the value in your local currency every time the exchange rate changes. This is important on the ledger side of things because that snapshot in time captures the value that will be used as the value when you sell the product on an invoice to a customer. Why is the date so important?

Assume each date has a different exchange rate. Then if you have a different date on the PO receiving and Bill associated with that receiving you run into a problem. The invoice to the customer will have a different value than the bill that was put onto the ledger. What went into inventory end up not matching what comes out of inventory. It should make sense that if we put $100 (local currency) of product into inventory that we must take out $100 (local currency) when selling the inventory. So don't calculate the local currency two different ways by choosing two different rates: keep the same rate by using the same date.

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 a 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 that 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 products into your customer’s hands without receiving it into your business? The answer is you can't, you just didn't record that the product came in. The first recommendation I have is to clean up your processes if this is happening. Create an area for products to be received, 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 received.

Method 1: It is a work in progress

If you have not received 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 affected, 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 make 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 subtype in the Inventory Tree and call it Completed Oversold. Employees that “steal” products 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 a 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 backorder on a completed invoice. Yes, this will affect your backorder 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 example 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 Miscellaneous item

Miscellaneous 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 guestimate. The idea is to go back to the invoice after the product is received, remove the miscellaneous 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 feasible to change the ordered qty and shipped qty to 0 for the miscellaneous 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 received 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 the 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 bookkeeper 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 its ordered and shipped quantity to 0. You can check this account at the end of each day, week, or month to make sure your books are in order.

Information

The main benefit to removing the Substituted lines is if you end up with a lot of sales history for the miscellaneous 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 physically 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 of 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 Include Freight? The greatest benefit 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 realistic 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. Let’s say you price the product out at $300 in both situations 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 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. Let’s 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 affect 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 affect 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,000 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.

The 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. Make sure that you also set the field to the sum so that the total is calculated at the bottom of the report.

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 writing 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. The first I am interested in is that it is difficult to reconcile 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 Account of 1200 it would make sense to Use 1210 or a similar 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 benefit 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 correct aging to the purchases clearing account. The Amount will then hit 1200 on the date you receive the bill, and in essence, 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 it will show in the Inventory for aging purposes.

IMPORTANT

If you have not previously noticed our backup recommendations here is a very important re-cap that specifically relates to inventory reconciliation. Besides the daily backups, we highly recommend 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 affect 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 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 the 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 proceed 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 necessary 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 rounds 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 necessary 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.

Backdated inventory value report

This report takes into account some, but not all variables that allow us to backdate inventory value. So this report can not be used for accounting purposes. In order to get accurate information on what the inventory value was at a specific point in time, you must actually save a backup from that date and time. Since this is normally needed for review at month-end, I would suggest that you archive your month-end backups. This does not work for everyone, because they are not up to date with data entry. If that is the case any transactions that were not in the backup that needs to be in the backup would have to be entered into the backed-up data set (double entered)

Stock/Ledger Reconciliation report

This report is designed to compare a back-dated inventory value to the ledger and give you how far they are out on a month-by-month basis. It uses the same code as the back-dated inventory value report and thus can not be used for accounting purposes either.

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 running. 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 whose 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 rationale for this is that negative lines are really fictitious, so the cost of 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 toolbox routines by going to the Menu or Navigator > Setup Tools > Utilities > Toolbox. 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. They can also be described as the listing of First In First Out (FIFO) lines.

Best Practice Methods

For inventory reconciliation, there is are best-practice methods that allow you to stay reconciled. Here are some key practices to get this list started.

  • Create the bill for a purchase order on the same day that the PO is entered. Even if you have not received it, you can then update the details when you receive the paperwork.
  • Don't change prices on Purchase orders after you have received it
  • Choose to use the “freight included” or “freight not included” method for purchase orders and billing


Freight Included Method

  • Purchase orders do include freight, extra, and/or duty on the purchase order increasing the total landed value of the product on the PO
  • Bills put all charges associated with cost, freight, extra, and duty to the inventory value account (normally 1200).


Freight Not Included Method

  • Purchase orders do not include freight, extra, or duty on purchase orders.
  • Bills post any freight, extra, or duty charges to the appropriate cost of goods sold account (COGS, a 5000 series account) or to an expense account (6000 series and above).


Footer

The sale must proceed at all costs. The idea, consider the TCO of each of those decisions.


Author: CMackay
Editor: CMackay
Last Edited: 17-Nov-2009