Weighted Average Costing

Oh i forgot to put this out there We need to block changing costing methods in a Fiscal calendar year. IRS don't like it when costing is changed. You can but you have to get the governments permission when changing how inventory is valued plus you have go back through that fiscal calendar revaluing all inventory with the new method. Not a fun thing to do

So once i get this done and its get into OpenMFG and PB users need some kind of warning stating what they are doing may be a against the law by changing costing systems.

I've worked with this stuff on several systems -- a common way to do thing is to always maintain the weighted average cost in the background, along with your standard costs.

Depending on individual situations / industries, it's useful to also be able to run inventory valuation reports based on all the available methods.

For purchasing agents -- using "last received cost" helps, for accounting you do have to stick to your methods, but it'd be useful to look at it all possible ways.

I'm debating which would be the best way to implement these new features. So i asked the question on PostgreSql hacker mail list to get some suggestions

the one responds i got pointed pl/r plugin module so we either create new aggregate functions or use an already created pl/r language .

The problem with using this new plugin is getting all the users who wish to use these new features, need to deploy this plugin. on the plus side these tools have already gone through debugging and has allot more math functions.

This would add another tool set that would allow for complicated BI and QC analysis

We have a prospective user interested in this so I also started reading up on average costing. The implentation is actually quite simple: What you need to do is just keep track of the on hand value of items by itemsite. For each receipt you simply increment the inventory value of the itemsite by the value of the reciept, which in the case of work orders means the entire wip value accumulated. When you issue the items, you divide that total value by the quantity on hand to come up with the average, issue based on that average, and deduct the transaction value from the itemsite total.

I don't see any reason to employ complex weighting formulas. It's just arithmatic. I could, however, see the necessity of providing the ability to manually adjust the inventory value of an itemsite and charging the adjustment to variance. That way, if something had horribly distorted the itemsite value for some reason, there would be a way to correct it.

In any case there would be no need to do cost roll ups other than for informational purposes.

Here are examples of different averaging formulas and the net result of what the actual total cost is compared to averaging, weighted averaging and standard-costing methods can be over a period of time.

Now of course the question we have to ask is why do we care about costing methods beyond keeping track what a part cost to manufacture which standard costing takes care of. Another reason we care about different costing methods and the net result is in the United States we have the IRS and if you are really unlucky live in state that collects inventory tax. So the method used to calculated inventory values not only has an impact on what we think we should sale a product for, it also has a direct impact on how much income tax a company pays.

This document will detail out different costing methods and the net impact what the value of inventory is for purchased items.

Lets first look at OpenMfg Standard/Actual Costing method. Openmfg Actual Cost method is the last cost an item was bought at for purchased items.

Lets see the impact of how last cost entered method can miss lead us to incorrect values.

Lets assume we purchase Brass alloy 360, where the prices have proven to be very unstable from week to week.

Week 1 Purchase 1,000 lbs @ $3.67 a pound we consume 800 lbs
Week 2 Purchase 1,000 lbs @ $3.73 a pound we consume 900 lbs
Week 3 Purchase 2,000 lbs @ $3.40 a pound we consume nothing
We have on hand 2,300 lbs where the Actual Cost states the value at $6,800
Lets assume the 200 lbs of the 3.67 is still around and 100 lbs 3.73 we know is on the shelf for a total value of $7,907.

The value of inventory in this case is off 14% using last cost entered method. This is a great value for tax time, but absolutely worthless for figuring out what to sale items for.

Lets look at the straight basic average method net impact assume the same thing above

($3.67 + $3.73)/ 2 = 3.70
(3.70 + $3.40)/2 = $3.55 * 2,300lb for a value of $8165.00

Now raw inventory is over valued by 3%.
Now lets look at weighted average with same assumptions from above

Week 1 Purchase 1,000 lbs @ $3.67 a pound we consume 800 lbs, 200 lbs left current value of inventory is $667.00

Week 2 Purchase 1,000 lbs @ $3.73 new cost will be
((200*3.67) + (1000 * 3.73))/ (1000+200) = $3.72 a pound we consume 900 lbs
For a value of 300 = $1,116 it actual value would be $1,098 assuming 200 lbs of 3.67 is still around and 100 lbs of 3.73 is around. We are over valued by 1.6%

Week 3 Purchase 2,000 lbs @ $3.40 a pound we consume nothing
((300*3.72) + (2000 * 3.40))/ (2000+300) = $3.44 for a value of $7,916 compared to the actual true value of $7,907 is off 0.1%

Now lets discuss the impact of on Finished Good inventory that are ready to sale. To simplify the math lets assume we have no labor or overhead cost for the finished parts and the material is the only cost content.

Quantity on Hand for Item ABC cost $3.00 we have 100 parts for $300
Manufacture produces 500 ABC at $3.33 $1,665
We redo a roll up and roll over on the last cost entered the value of Inventory is $1,998 actual inventory cost is $1,965 inventory is now over valued
Manufacture produces another 500 at 3.15 again we do a roll up and roll over on last cost entered inventory is valued at $3465 actual inventory value is $3,540. Inventory is now undervalued by 2.2%

Lets keep in mind the actual true Inventory value is $1,998 and $3,540

Lets see what average does to the value
(3.00 + 3.33)/2 = $3.17 * 600 parts inventory value of $1,899 inventory off by 5%
(3.17 + 3.15)/2 = $3.16 * 1100 parts inventory value of $3476 inventory off 2%

Lets do weighted average.

((100 * 3) + (500 *3.33))/(100 +500) = $3.28 * 600 = $1,965 inventory is off 1.7%
((600 * 3.28) + (500 *3.15))/(600 +500) = 3.22 *1100 = $3,543 inventory is off 0.08%

In the above example I gave the most favorable conditions for basic average method where the price increased and dropped back down to the mean value between $3.00 and $3.33. The basic average formula compared to weighted average formula results in being off a factor of 2.5 to 250.

As you can see using any method of averaging will result values being off, the question is how much? Weighted Average net result is always closer to what the actual value is, while arithmetic averaging can be off several percentage points. For a further understanding on how average formulas can misrepresent actual figures please see Simpson Paradox.

I think you are missing the simplicity of what I'm saying. The math is much more straight forward than that. You just add the total value of each receipt (based on the actual purchase price or work order cost) to your total inventory value for the item. Then when you issue items the average is calculated on the fly. You simply take the total on hand value and divide it by the total quantity on hand to come up with the average, then issue using that average. There will never be any over or under valuing that way. The method is completely self adjusting.

Unlike standard costing, no cost roll ups, updates or postings are required.

jrogelstad that's the weighted average formula.

One method to reduce the error in averaging is increase the sampling size but this creates other problems as including values of items no longer around will either push the average up higher and lower to what is occurring today. The number agrees perfectly to what items cost but broken out by period of time it starts have errors introduced.

The only method to keep cost perfectly straight is do Lot control costing.

I have written a aggregate weighted function in pl/sql which is attached.

Play with that and compare the results to how you want to do it.

it should have the same result.

to use the function
select item_number, wcost_average(coitem_qtyord, coitem_price) from coitem, item, itemsite
where coitem_itemsite_id = itemsite_id and itemsite_item_id = item_id group by coitem_itemsite_id, item_number

this will return the average selling price for items

Agreed what I am proposing is a weighted average; what I'm getting at is you don't need a special weighted average function to find the result. I think our disagreement is how values are stored and calculated. It looks like you are assuming costs are stored in layers like a FIFO system and the average is calculated based on some kind of distribution mechanism of those layers. I am saying you don't track layers at all. All you track is the total inventory value for the item and use that as the basis for the average calculation when materials are issued.

Perhaps the attached spreadsheet using your scenario provides a clearer example.

We are taking pass each other here. I'm not to putting cost into layers i'm going to use what Openmfg has now to get where i need to be.

At receipt time is when to expire the old average cost and re-do a new average cost. I was going to use the item cost table to keep track of all the old cost so we have an audit trail of how/why inventory was revalued. Need that nasty audit trail which in this case it will work. I was going to add a comment field to the cost table to note po/wo that caused the inventory values to drop of go higher. Need the audit trail..

jrogelstad wrote:

Perhaps the attached spreadsheet using your scenario provides a clearer example.

Post generated using Mail2Forum at xTuple forums

Post generated using Mail2Forum at xTuple forums

Yes we are. I don't think the existing update/post tools are of any use for average cost. However, I think most of the necessary structure exists to keep the audit trail. The invhist table has a invhist_unitcost field. You could use this field in conjunction with qty and date to reconstruct the history at any time.

Of course that would always require you start from the first transaction and if history were purged would cause problems. Alternatively, we could add invhist_invval_before and invhist_invval_after (similar to quantities) so an audit can be started from any point in time.

Guys:

Before finalizing the processes for average costing, be sure to test what happens if inventory quantities are allowed to go negative. Particularly, if there is a negative qty on hand and a new receipt quantity is not large enough to make the new on hand quantity positive. My personal opinion is to not allow negative quantities. This forces the users to process things in the proper sequence. At the very least a setup option to NOT allow negative quantities should be considered.

If negative quantities are allowed, I have seen products that actually will create a journal entry to correct previous transactions and then start a new cost basis with the new receipt cost.

For simplicity think of purchased items that are sold. If inventory quantities have a negative value and costs are rising, the sale transactions that caused the negative quantity were under costed. To make up for that, the new receipts have to be over valued so that when they are sold, they are over costed to make up for the previous transactions that were under costed. That can create some distorted financial statements, depending on the periods in which this type of thing occurred and how dramatic the cost changes were.

To sum up, I believe it is important for us to add average costing cabability to OpenMfg. I am confidant you guys can work out the details. Hopefully, I have given you something else to test before you finalize your specifications.

If negative quantities are allowed, I have seen products that actually will create a journal entry to correct previous transactions and then start a new cost basis with the new receipt cost.

To sum up, I believe it is important for us to add average costing cabability to OpenMfg. I am confidant you guys can work out the details. Hopefully, I have given you something else to test before you finalize your specifications.

Negative QOH values have to be knocked to zero for the average function only.

If we do not knock qty to zero for negative values it will lie: example
((-2000lbs*$1)+(1000lbs*$2))/(-2000+1000) = $0.0 cost per lb

knocking negative qtys to zero results in this
((0lbs*$1)+(2000lbs*$2))/(0+2000) = $2

Another option is use absolute value but again this lies by the simple means we are including items in average function we don't have.
((|-2000lbs|*$1)+(|1000lbs|*$2))/(|-2000|+|1000|) = $1.33 cost per lb

----------
Now stopping inventory from going negative is already there in OpenMfg. Keeping inventory from going negative is double edge sword it cuts both ways.

If paper work becomes absolute then manufacturing shipping and other process come to a stand still while people in the office figure out what is going on.

Example of this is the paper work lags behind items received for RAW material. manufacturing can't use it until all the paper work is done because inventory would be set to negative if they tried to issue the material to WIP.

That is an awfully costly event to happen if manufacturing stops because a none critical paper work SNAFU.

The orders in our shop is if computer tells them they can't start the work order they can't period. The rules are pretty simple Customer went on credit hold, Customer Canceled the order, Or there's is SNAFU on the prints. All other paper work is viewed as we will deal with it later make the parts.

zzzzz

You have just illustrated precisely why negative quantities on hand require some thought and proper handling. You can't just ignore the negative quantity because remember the inventory valuation must match the general ledger and if the quantity is negative the GL inventory value has already been been reduced by the amount of the current average cost when the transaction took place. If you just decide to declare the item count now zero you are certainly creating a difference in the GL balance and a detailed inventory valuation report.

Yes, you are right if negative is not allowed it can cause operational hangups and problems, but a company must decide if it wants to have procedures and processes and keep accurate inventories or not.

By the way, I am unable to find the configuration in OpenMfg that does not allow inventory to go negative. Can you tell me how that is done?

turning Sales reservation on has the side affect of keeping inventory from going negative from a sales side. Now that i think about it this would not affect raw to wip. So i need to amend that comment a little.
-------

I can only deal with this problem in so many ways. I either use the negative qty on hand in the calculation which will cause a way undervalued inventory, or use the absolute value of said qty which again sends inventory values higher or lower where they should be, or ignore the negative values.

The most common way inventory goes negative for raw material is
A: bad count from previous physical inventory
B: BOM is wrong and using up to much inventory
C: Material has shown up but not entered into the computer yet.

Now lets talk about the other side of the equation where Inventory is to high which has the opposite affect on inventory values
A: bad count from previous physical inventory
B: BOM is wrong and not using enough inventory
C: Material was received more than one time.

This is the reason physical inventory are done normally every quarter. If the computer thinks you have more inventory than you actual do you recorded a loss. Now if inventory was negative you just made money because those values get set zero or something greater. I have found Negative values are normally fictitious numbers due to clerical mistakes in BOM or timing issues with RAW material Receipts. The only time i have found them not to be fictitious is when someone was stealing from us. (the guy carried out 3 to 5 lbs of copper out in his lunch box every day)

The only way to fix the problem you are describing is to take a physical inventory every time receipt is done.

What number am i to trust in calculating average?

The one thing we know for sure is negative qty on hands really don't exist and need to be ignored for this calculation. Now if we have show to much qty on hand this also distort the values but nothing like negative values.

The Most common problem i have seen where inventory goes negative by large dollar values is when receipts are missed but issued to WIP have happen. If i don't ignore negative values the new material is way under valued.

At worst case negative values with respect Inventory valuation is either show a bigger or smaller loss. But at next inventory count those are going to be set zero and the loss goes to zero.

The number i'm more concerned about is when we show To Much Inventory. This can really screw with values and having you believe you are making money when you are in fact your bleeding cash

Of course that would always require you start from the first transaction and if history were purged would cause problems. Alternatively, we could add invhist_invval_before and invhist_invval_after (similar to quantities) so an audit can be started from any point in time.

invhist won't work for this purpose sense we have multiple cost elements it makes it very difficult to figure out what part of the cost structure was updated.

This was the problem with our last system it reported on the summed total cost, plus it only kept history of the past 11 cost changes. It was really annoying to figure out what was going on. I'm creating a new table itemcosthist, which is almost identical to itemcost. The idea is the visibility to this table is via item workbench and maintain item cost so can see what has been changing.

zzzzz

Thanks for the information about Sales Reservations.

You are certainly right about the cause of most negative inventory situations. These activities (or lack of) may have caused bad transactions in the financial sytem. Before I continue let discuss what I'm sure you and other program developers understand, but maybe others reading this may not. If you think of the OpenMfg application as a manufacturing process, the output or product produced is verifable, accurate financial statements. Sure, we provide management tools like what to buy, when to buy, what to make, when to make, on hand inventory information, worker productivity, scheduling, product availability, etc, etc, etc. But in the end our product is financial statements. If these are not accurate and verifiable no one is going to use our other tools. If our Accounts Receivable total says we are owed
325,657.00 then we have to able to produce the detailed invoice information that totals that amount. The same is true for Accounts Payable. It is also true for inventory. Inventory detail must not only be an accurate statement of how it is valued, but it must be verified physically. Whenever we add features or make changes to the application we must always question what is, if any, the impact on the financial statements. That is the final output of OpenMfg and any other ERP system. As I said, clearly we offer more tools and benefits, but the financials must absolutely be accurate and verifable. Certainly there are tolerances, because the input will never be perfect.

I would like to go through an example of average costing that makes the financial system work, but as you have accurately pointed out presents information that will appear to users to be incorrect. Let's say we have just set up a new item we are going to buy and sell (distribution only for simplicity) and the cost is 1.50 each. we have a few in an out transactions at this price but right now we have 0 in stock and therfore our GL inventory value is 0. We purchase 8 of these items for 2.00 each as the cost has gone up. these 8 are physically received, but the receipt is not recorded in our computer system. Six of these items are sold, shipped, and invoiced before the receipt is entered. At this point our OH qty is -6 and the GL inventory value is a 9.00 credit and 9.00 has been debited to cost of sales. This is because the transaction was recorded at 1.50 each instead of the 2.00 cost. At this point our statements are as accurate as they can be. An inventory valuation report would yield -9.00 which matches exactly our credit balance of 9.00 in the GL inventory. Now let's receive the 8 items into inventory at the new cost of 2.00 each. What are we to do? Well, after this receipt is recorded we want our inventory valuation report to still balance to our GL inventory account. When the receipt is first recorded we generate a debit to GL inventory for 16.00 and a credit to our accrued liablility account of 16.00. Our GL inventory balance is now a debit of 7.00. Our inventory valution report must report 7.00 as the value of inventory to maintain the accurate verifable financial statements. What are we to do? Some would say we assign a value of 7.00 to the two items we physically have. That would make them show an average cost of 3.50 each. Certainly that could be confusing to anyone looking at that item record. However, when those two items are sold the credit to GL inventory will be for 7.00 which leaves it 0. The debit to cost of goods sold is 7.00 making a total of 16.00 that has been charged to cost of goods sold. This is classic weighted average. ((-6 * 1.50) + (8 * 2.00))/2=3.50. The exact same thing we arrived out by following through the debits and credits. All is right in the end. None of the transactions are exactly right but the total end result is exactly right.

Some would argue that this presents an inaccurate picture of the value of those last two items until they are actually sold and they would be right. One solution to this is create the correctiong journal entry at the time of receiving the 8 items into stock. It would go like this. We know we are going to be left with 2 OH with a real cost of 2.00 each or 4.00. Therfore we should make our GL inventory 4.00 right now. Well, if it was a credit balance of 9.00, the debit from the recipt transaction is 16.00 making a GL value of 7.00 debit, we can just create journal entry that credits the GL inventory for 3.00 and debits the cost of sales for 3.00. This corrects the original cost of sales entry when the six items were sold that caused the negative qty in the first place and the inventory value for the two items on hand shows at 2.00 each. I believe that this is the better way. If these types of transactions occur with raw materials consumed in the manufacturing process you still come out with under valued finished goods and then over valued finished goods to make up for it.

My whole point of this exercise is that if we allow inventory to go negative (and we probably have to for reasons well stated by zzzzz) we must deal with correcting the inaccurate transactions that are produced. I think we in the field can strongly discourage allowing negative inventories if we clearly understand all the ramifications. There many other issues in this under costing and over costing scenario such as Gross Profit Analysis by Invoice, Commission calculations based on gross profit, and I could go on and on.

It has been a few years since I have traced all the possibilities through, but if costs are decreasing, if new receipts still leave a negative OH, or other specifice situations can present addtional challenges to handle properly to produce accurate verifable financial statements. I'm just suggesting that all these possibilites need to be analyzed before deciding how it is all to be handled.

One last thing for jrogelstad

You are certainly right that you can just maintain and inventory total for each item/site record and always calculate the unit average cost when needed. There will be lots of times that this calcualtion must be done. Every time issues are made to sales orders or work orders and lots of screen views and reports for the end user. Hoever, if you calculate the unit average cost and calculate totals when needed, the unit average cost would only need to be calculated on new receipts. Probably not a real big difference either way. Actually, now that I think of it, keeping the total might reduce rounding errors which we haven't really discussed.

I apologize for being so lengthy, but if we do this we have got to get it right.

This is the reason why we have an Inventory Variance Account when inventory is revalued with what ever method we need to throw variances which is the difference between new value and old values.

This is how the books stay in balance.

The obvious thing here is negative values will throw bigger variances but the accounts stay in balance and so does the P&L.

I can think of more reason way negative values are timing issues and need to be ignored for calculating the new cost.

At this point we have only discussed timing issues you want to have real fun starting play with wip values and the impact it has on Finished Goods when discussing Costing Systems.

There is no specific way to disallow negative inventory, but there are a couple indirect ways to do it in the commercial editions of xTuple. One is enabling sales reservations as ZZZZ says, but that only works for shipping. Also enabling lot/serial control disallows negative for lot/serial controlled items. Come to think of it, even in PostBooks you can create a similar behavior by enabling location control.

I'm sure somebody will request or contribute a full fledged disallow negative qty feature at some point.

This issue about negative qoh is an excellent point. I have spent more time thinking about this situation than you know!

Generally, my thought is similar to ZZZZ which is that that cost for negative transactions will generally be zero. For the most part any issue of material will be the total inventory value divided by the total qoh multiplied by the quantity issued. If all quantities are issued, the value will go to zero. Any quantity issued beyond that has no value, so none can be assigned. There are some tricky nuances when say you have -100 items, then you receive 10 with value of $100 so now you have -90 items with a value of $100. I think in that case the average calculated at time of issue is the greater of the total qty on hand divided by the value, or the qty being issued divided by the value.

Also, I'm expecting that adjustment of inventory quantities will have zero inventory value change impact for average costed inventory. However, I am certain in the inventory adjustment window, we'd need to add the ability to include an inventory value adjustment option for average cost items. With this addition you would be able to adjust the inventory qty, the inventory value or both.

Great food for thought. Thank you!

Oops. Meant to say: I think in that case the average calculated at time of issue is the value divided by the greater of the total qty on hand, or the qty being issued.

Good morning guys:

I am certainly glad we are having this discussion. I think we all agree we have to get it right and there can be different approaches that will work if we just think through all the possibilities.

zzzzz
You are certainly right about variance accounts, but generally in a pure weighted cost averaging system what is your variance from? Normally there is no variance except at time of physical inventory and these variances are increase or decrease earnings for the period or when adjustments have to made to correct for errors in posting transactions. In the weighted average cost system the normal debits and credits to inventory and cost of sales (or cost of work orders) handle the changes in costs, what we usually refer to as variances in a standard cost system. In a weighted average system, what we know as the standard cost doesn't exist. Our automatically created journal entries at receipt and issue have to be calculated correctly.

jroglestad

I don't think we can justify having 0 costs passed to a work order and on to finsihed goods and eventually cost of sales just because the transaction took place out of sequence. It seems to me that if you allow the quantity to go negative you have to allow the value to go negative also. Just because the electronic record of inventory went negative, we certainly know that on the shop floor we actually consumed or shipped items and they certainly had a value even if we had not gotten around to recording it in the electronic world.

When negative qtys are allowed and actual real supplier costs are changing, it boils down to under or over costing some transactions to make up for the under or over costing of the transaction that took place that caused the qty to go negative.

And of course, let me bring up again that rounding can be an issue, particlarly when items can be sold or consumed in fractional quantities.

Actually, if you trace all the scenarios through to the end, even when allowing negative qtys, when receipts are properly recorded and items work themselves out, the final values balance (except for rounding), but you certainly can have some wildly over and under costed transactions.

I am still of the opionion that recaculating the average cost at time of receipt and stroing that number in the data base along with the qty's on hand is the better method that storing the total value and calculating the average for each issue. Either way works but I believe the number of times the average has to be calculated is significantly less that way. Kind of like we do with purchase orders, invoices, etc. We stor the unit values, the quantity and we calculate the totals as needed.

I think OpenMfg needs to get some advice from more than one cost accounting expert before finalizing specifications.

Icartee,
Well, the quest for alternate opinions is certainly why we have this forum. 

Theoretically if you issue an item with zero cost to a work order because of negative quantity position, it would often have little impact since only the average of the item you are issuing to is affected, which may be a small amount in the big picture.  However, I concede that if you have an item of a high dollar amount that went negative and was issued to a work order for another item that was rarely made, it could throw the average off dramatically for the manufactured item.

A straight forward alternative to zero is to cost negative quantities using the standard cost.  I've seen this done on at least one FIFO based system as the way to value negative quantities.

As far as the basic calculation method goes, the math required to calculate the average at issue is fairly trivial and actually requires computing power less than or equal to the current function to return standard cost (which is the sum of multiple cost elements).  There are three things I particularly like about the rolling valuation average method I have been championing:

1) It is simple
2) It is simple
3) It is simple

My experience in ERP implementation is that the more complicated the methodology is, the more likely it is to be broken and misunderstood by users or the code that surrounds it.  I believe if you can explain in a single sentence to the accountant how the average is calculated, that will go a long way toward increasing their comfort with and comprehension of the system.   My thinking on how average should be calculated is further based on at least two Accounting textbooks which both describe average costing methodology almost identically to each other and the way I have described it.

But I certainly welcome to additional points of view, exceptions and thoughts as you have brought up.  Your point about the undesirable impact of using zero value for negative quantities (a scenario not in the textbooks ) has certainly swayed me toward the idea of using standard or some other cost for that situation.

Regards,

John

On Mar 24, 2008, at 11:11 AM, lcartee wrote:

I am certainly glad we are having this discussion. I think we all agree we have to get it right and there can be different approaches that will work if we just think through all the possibilities.

zzzzz
You are certainly right about variance accounts, but generally in a pure weighted cost averaging system what is your variance from? Normally there is no variance except at time of physical inventory and these variances are increase or decrease earnings for the period or when adjustments have to made to correct for errors in posting transactions. In the weighted average cost system the normal debits and credits to inventory and cost of sales (or cost of work orders) handle the changes in costs, what we usually refer to as variances in a standard cost system. In a weighted average system, what we know as the standard cost doesn't exist. Our automatically created journal entries at receipt and issue have to be calculated correctly.

jroglestad

I don't think we can justify having 0 costs passed to a work order and on to finsihed goods and eventually cost of sales just because the transaction took place out of sequence. It seems to me that if you allow the quantity to go negative you have to allow the value to go negative also. Just because the electronic record of inventory went negative, we certainly know that on the shop floor we actually consumed or shipped items and they certainly had a value even if we had not gotten around to recording it in the electronic world.

When negative qtys are allowed and actual real supplier costs are changing, it boils down to under or over costing some transactions to make up for the under or over costing of the transaction that took place that caused the qty to go negative. 

And of course, let me bring up again that rounding can be an issue, particlarly when items can be sold or consumed in fractional quantities.

Actually, if you trace all the scenarios through to the end, even when allowing negative qtys, when receipts are properly recorded and items work themselves out, the final values balance (except for rounding), but you certainly can have some wildly over and under costed transactions.

I am still of the opionion that recaculating the average cost at time of receipt and stroing that number in the data base along with the qty's on hand is the better method that storing the total value and calculating the average for each issue. Either way works but I believe the number of times the average has to be calculated is significantly less that way. Kind of like we do with purchase orders, invoices, etc. We stor the unit values, the quantity and we calculate the totals as needed.

I think OpenMfg needs to get some advice from more than one cost accounting expert before finalizing specifications.

Post generated using Mail2Forum at xTuple forums

jroglestad

You and I agree completely about simplicity. I believe we all agree average costing works beautifully until you throw negative quantities into the mix. One of the appeals to users for average costing over standard is not having to maintain standard costs. No roll ups, etc all the things that need to be done in standard costing to keep up with changing costs. In the old days people just adjusted the standards once per year and someone monitored the variance accounts to see how far off actuals had gotten from standards. Today, lots of people on standard cost systems are constantly wanting to adjust, roll up, etc. Average costing does away with those maintenance steps. except for correcting errors and that is what the users want. I see the average cost system totally replacing standard cost, not enhancing it.

As you said simple is good. But, if your data gets to the situation you described with a value of 100 and a qty of minus 90 that is not a simple situation. Your GL inventory account has a value of 100, when you really don't have any of the item on hand. Yes, you can say it has a value of 100 in the computer, but where can I put my hands on and touch something that has that 100 value? When I eventually receive the balance of 90 my value will go way up, but my qty will still be 0. That just doesn't make sense. I am totally convinced that value has to follow quantity. Actually, the case you described is the worst of possible situations, that is qty has gone negative and the next receipt is not for a qty large enough to at least get back to 1 on hand. Now, depending on how many and how varied the transactions, this qty of 1 can have a completely un realistic value in order to make everything balance. I firmly believe that if quantity falls to negative the portion of the GL Inventory account that is made up of that item must also go negative. If you don't do that, you really add complexity, not simplify.

Yes, depending on different circumstances the impact of a cost transaction not being correct can be minimal or it can be major. If one transaction is significantly under costed another over costed transaction must be processed to bring everything into balance. As we have said the totals are right but individual transactions may not be.

The inventory valuation report is a sub ledger to the GL inventory account just as the Accounts Receivable trial blance and/or ageing reports are a sub ledger to the Accounts Receivable GL account and they have to balance. The same for AP, and the same for WIP. We wouldn't dream of adding or deducting to an invoice without adding or deducting from the AR Control account. Nor would we dream of making an entry to the control account without adjusting the detail. When we create an Allowance for Bad Debts, we don't touch the AR control, instead we maintain a contr-asset for that allowance and net the two to value our receivables, but the invoice detail must still match the AR control.

This is like so many application development problems. The normal transaction is easy, but the exception takes all our time in figuring out exactly how to deal with it.

John, I am confidant in your ability to figure out the right way.

jrogelstad Please note the IRS has banned the use of Rolling Average method as does not meet the following.

[Reg. section 1.471-2(c).] The section 471 regulations also define cost to include a reasonable approximation of cost. [Reg. section 1.471-3(d).]

Rolling average method will disagree the current actual cost because it includes to much historical cost

This also means we have to ignore negative qtys on hands when calculating the new average cost, as it will give a unreasonable cost of said parts. It means that average must exclude any purchases from previous year and purchases that are no longer in inventory.

In Rev. Proc. 2002-17 and Rev. Proc. 2006-14, the IRS allows the use of a replacement cost method because replacement cost approximated their actual cost.

This is the Method i'm developing to using QOH and the Current Cost with the new Qty and the new Cost, to create an average with the two. I then replace the cost and note the variance it creates between what inventory was to what its revalued to.

zzzzz

Very interesting. I was not aware of the references to the IRS as weighted average costing like we have been discussing as been an approved GAAP method for as long as I can remember. Some further research on the position of the IRS revealed that their position on "rolling averages" goes back many, many years. I found severeal references that the accounting profession has formally requested the IRS to review and change this position as far back as 2006, but I can find no IRS response to that request. It even appears that the IRS was requesting public input to their consideration to make this change. In the meantime, as we all know computer accounting systems from Quick Books to SAP and nearly everwhere in between (but not OpenMfg) have been using this method. That reasonable approximation of cost seems to be the key the if the "rolling average" is reasonable it is acceptable. It seems to me that if a business is clearing it's inventory items to a qty of 0 or near 0 at any time during an accounting year, the rolling average would pass an audit. I am certainly not a lawyer or a CPA, but there sure a lot folks using average cost. I believe the IRS would look at a situation and determine if a taxpayer were trying to intentionally manipulate inventory value to effect taxable income.

I also find the method you are working on interesting. If I understand correctly you are revalueing inventory on any new receipt, setting the OpenMfg standard cost to the new cost and letting normal OpenMfg General Ledger Posting take over after that. Is that correct or have I misunderstood? When you say "note the variance", are you creating an actual GL journal entry to record that variance?

Very interesting. I was not aware of the references to the IRS as weighted average costing like we have been discussing as been an approved GAAP method for as long as I can remember. Some further research on the position of the IRS revealed that their position on "rolling averages" goes back many, many years.

GAAP really does not care what method you use to value anything just as long as your consistent. The IRS are the ones who care, the IRS has only approved a handful of methods.

The method I'm working on has been reviewed by the IRS and they signed off on it. It also been reviewed by some 4 CPA firms over the past 40 years sense we have been using this method. It also has stood up to 2 separate audits by the IRS when they actual used to do audits.

In the meantime, as we all know computer accounting systems from Quick Books to SAP and nearly everwhere in between (but not OpenMfg) have been using this method.

Most of the general Accounting packages out there do averaging wrong. the Higher End ERP/MRP system use average system like mine.

Yes
That is a GAAP rule along with IRS. i changed the cost number means i have to go back to the Unused RAW Inventory and revalue it. Its the replacement cost method but instead of number i come up with based off (standard vs actual) its weighted average of only what i have on the shelf and whats coming in.

Rolling Averages have never been a very good method to figure out your cost are, as it looks back at all the receipts over a period of time. This will give very accurate number of cost for a product over that period of time as the sampling has been increased (Remember the Bell curve). The more samples you take closer to the middle you will be.

But that's the problem because its not necessarily reflective to what the cost are at that moment.

Now the next problem users may run into is the IRS may tell them tuff in trying to change costing methods. Changing costing methods is like changing from Accrued to Cash methods of accounting.

God i wish that was true.

never tangled with a Government bureaucracy?

My family closed another company down 6 years ago but the Indiana Dept. of Revenue still issues Arrest Warrants for tax fraud and claims we owe $63,456 in sales tax for past 6 years. It going to break 100K this year with interest and other fines.

And yes Tax lawyers have tried to get this killed.

Also note lcartee we have an in house accountant which sole job is to make sure the books stay right. We then have CPA firm review our books at year end.

zzzzz

Thanks again to you for helping me understand exactly what you are working on. I would like to go through a scenario and see if I really do understand. Let's say that you have a qty of 2 of in item in inventory that you paid 2.00 each for. There has been only one purchase order received for the qty of 2 and the PO cost was 2.00 and the standard cost was set to 2.00. None of this item has ever been issued to a work order or a sales order. We place and receive another purchase order for 8 of these items and the cost has gone up to 3.00 each. Upon posting of PO receipt normal OpenMfg will debit the inventory account for 16.00 (8 x 2.00), debit a variance account for 8.00 (8 x 1.00 difference in standard and purchase cost), and credit an accrued liability for 24.00. If I understand correctly you would let that journal entry process just as normal. Is that correct? You would then revalue the inventory by setting the standard cost to 2.80 (value of 28.00, previous value of 4.00 plus 24.00 in new receipts), create a journal entry to increase the GL inventory account to 28.00 by debiting inventory for 8.00 and crediting a variance account (perhaps the same variance account used by normal OpenMfg duriing PO receipts) for 8.00.

The end result of this is a standard cost of 2.80 for 10 of this item ( a value of 28.00) and a GL Inventory value of 28.00 for this item. Future issues of the item to work orders or sales orders would be for the newly calculated standard cost 2.80 by normal OpenMfg processes. Everthing is in balance. All is right with the world.

Have I described correctly and completely what your plan would do in this example?

i tried really hard to get it to work that way but during testing over the last couple of days showed to many scenarios where this method breaks down.

So at PO receipt time Standard Cost is reset to the Weighted average then the item are received, then a variance is thrown which normally only a few pennies between how the old inventory was valued to new values. This means the PO values must be correct before receiving.

The problem i ran into trying to reset the cost at voucher entry time was i lost track of Quantity on Hand and never could figure out a way to get the number back to where it should be to do weighted averaging. My next problem was if there was bunch of vouchers for the same part at different cost waiting to be entered how do we get these numbers to balance out and entered the vouchers in the correct order to calculate the average.

this the reason jrogelstad wanted to use rolling average based on the receipts but this not allowed by the IRS and it can distort what true cost are

zzzzz

Before I study what you just said and try to figure out what journal entries you would create, I would like to point out that as I understand what jrogelstad is proposing would work exactly like that without the extra journal entry. Using the same scenario I just described, upon PO receipt he would debit GL Inventory for 24.00 and he would increase the Item/site value by 24.00 making it a total value of 28.00 which balances exactly to the GL inventory balance. When issues are made to sales orders or work orders he would calculate the average cost to be 2.80 and if 3 items were being issued he would reduce the value of the item/site record by 8.40 and create a journal entry crediting GL Inventory for 8.40 and debiting either WIP or Cost of Goods sold depending on the issue being to a work order or a sales order. Standard cost is removed entirely from the process. Everything is in balance. All is right with the world.

With the exception of negative quantities and rounding, I cannot think of a scenario where this would not work. We have to determine how we deal with those things after the basic process is idedentified. The only difference in my idea and jrogelstad is that he would store total value and calculate average as needed and I would store average and calculate total as needed. Results are the same.

Since this process matches what you were trying to do and you have already had it approved by the IRS it seems like we are in good shape and can turn our attention back to dealing with negatives and rounding.

Well, I think we're talking about two different things here. One is a weighted average cost system, and the other is a standard cost system where standards are recalculated using weighted averages. I think there is usefulness in both methods depending on the level of granularity and control a company wishes to have of cost calculations.

Technically zzzz is correct that the IRS does not explicitly condone average costing. In fact, they don't condone standard costing either. Specifically they recommend unit tracking and if that is not possible, FIFO or LIFO. Finally they throw that wrench in that lets you revalue inventory based on the lower of actual cost and current market value:

http://www.irs.gov/businesses/small/industries/article/0,,id=100355,00.html

The trouble is in practice, as Icartee point out, is all of those options are typically too complicated and difficult to keep straight for a typical business, especially a small business. Even computerized FIFO systems (which I'm sure we'll support one day) can be enormously difficult to follow, especially when values are changed retro actively by backdated transactions several layers away from finished goods. The link above specifically states that there is no one inventory accounting method that works for all environments, and that they generally allow methods that conform to GAAP, follow industry norms, and are followed consistently.

GAAP is often invoked by accountants and auditors as the guideline to which they ascribe that justifies their methods and decisions and the only "correct" way to handle an accounting problem. The trouble is GAAP starts with a captial "G" which stands for Generally. Generally accepted accounting principles are a sort of common sense rules for accounting. It is often very difficult to pin down certain specifics about GAAP because by definition it is not meant to provide specific advice. They are general rules like "You shouldn't over state your earnings by under valuing inventory transactions."

The best resource I know of with published documents that approximate GAAP are those by the Financial Accounting Standards Board (FASB http://asc.fasb.org/home). About inventory accounting they simply say this:

This leaves room to value the "articles" any number of ways. Properly implemented any costing method we've discussed will conform to this. The key words are "properly implemented," because I think the manner and context of implementation is in many respects more important than the algorithm.

At the end of the day, we'll include features in the system based on what users request and contribute. Subsequent users can decide on and implement whichever method or methods best suit their business needs.

FYI: We have found a sponsor for Average Costing and the specification for it has been completed here: http://wiki.xtuple.org/AverageCosting. It will be released in version 3.1. Note the sponsor said specifically they want to disallow negative on hand quantities, so all the problems regarding that issue evaporate.

This doesn't, however, preclude my interest in having a way to update Actual and Standard cost based on averages, rather than last cost as it is today!

I have quickly scanned the reference and I would like to suggest a review of the debiting voucher variances to inventory. Perhaps I missed something later in the document, but if inventory quantity and inventory ledger values are updated at Purchase Order receipt and the items are completely issued or sold before the supplier invoice is vouchered, there will be no quantities avaialble to absorb the voucher variance and therefore a valuation report can not match General Ledger. I suggest voucher variances still be expensed.

Larry

That's a good point, Larry. Could you copy and paste that comment on to the wiki discussion?

Thanks,

John

John Rogelstad
xTuple
119 West York StreetNorfolk, VA 23510

john@xtuple.com ([email:2p4rjmwv]john@xtuple.com[/email:2p4rjmwv])
(757) 461-3022 ext. 106

On Jun 10, 2008, at 2:16 PM, lcartee wrote:

Larry

Post generated using Mail2Forum at xTuple forums

John:

I have attempted to do thid, but the discussion remains greyed out when I display the specification page. I am sure I am doing something wrong, but I don't know what.

Larry

Sorry, Larry. I hadn't initialized the discussion page. Try again...

http://wiki.xtuple.org/AverageCosting

John

John Rogelstad
xTuple
119 West York StreetNorfolk, VA 23510

john@xtuple.com ([email:3ho0keic]john@xtuple.com[/email:3ho0keic])
(757) 461-3022 ext. 106

On Jun 14, 2008, at 9:25 AM, lcartee wrote:

I have attempted to do thid, but the discussion remains greyed out when I display the specification page. I am sure I am doing something wrong, but I don't know what.

Larry

Post generated using Mail2Forum at xTuple forums