Accounting Average Cost Algorithm

[Daniel Kunkel]

Hi

It's so wonderful to see an integrated accounting system for OFBiz.

I've done some thinking about the algorithm used to calculate the
average cost of an inventory item, and thought I'd share it again.

Rather than trying to store an "average cost" for an item, I believe we
would be much better off storing the "total investment" for each item.

Most of the time, it won't make any difference, however I have seen that
directly storing the average cost leads to all sorts of floating point
resolution and residual adjustment issues, and complications when
purchasing inventory at a varying price. Most notably that the total
investments purchasing an item sometimes won't exactly equal the total
cost of goods expensed after it is all expended.

>From http://lists.ofbiz.org/pipermail/dev/2006-March/010207.html:

For example, if you bought a million pieces for $3,889,107,143, and sold
them out of inventory one at a time for $3.89 without tracking/updating
the residual average cost..  eventually you'd have to account for
difference of more than $800,000!

The solution:

Keep a running "total investment" for each item. At
the point that you utilize an item, calculate and subtract
the cost of those items rounded to a penny for example. Other
currencies will round to the degree necessary for their currency.

The effect of doing this will be that the cost of goods will
oscillate up and down, in the above example between 3.88, and 3.89
in such a way that when you've sold your millionth item, the
residual investment remaining is exactly 0.

Thanks

Daniel

[Adrian Crum]

I've found it's best to ask an accountant these types of questions. In this example, an accountant
might expect average cost to be calculated a certain way, and not worry so much about "penny accuracy."

-Adrian

Daniel Kunkel wrote:

> Hi
>
> It's so wonderful to see an integrated accounting system for OFBiz.
>
> I've done some thinking about the algorithm used to calculate the
> average cost of an inventory item, and thought I'd share it again.
>
> Rather than trying to store an "average cost" for an item, I believe we
> would be much better off storing the "total investment" for each item.
>
> Most of the time, it won't make any difference, however I have seen that
> directly storing the average cost leads to all sorts of floating point
> resolution and residual adjustment issues, and complications when
> purchasing inventory at a varying price. Most notably that the total
> investments purchasing an item sometimes won't exactly equal the total
> cost of goods expensed after it is all expended.
>
>>From http://lists.ofbiz.org/pipermail/dev/2006-March/010207.html:
>
> For example, if you bought a million pieces for $3,889,107,143, and sold
> them out of inventory one at a time for $3.89 without tracking/updating
> the residual average cost..  eventually you'd have to account for
> difference of more than $800,000!
>
> The solution:
>
> Keep a running "total investment" for each item. At
> the point that you utilize an item, calculate and subtract
> the cost of those items rounded to a penny for example. Other
> currencies will round to the degree necessary for their currency.
>
> The effect of doing this will be that the cost of goods will
> oscillate up and down, in the above example between 3.88, and 3.89
> in such a way that when you've sold your millionth item, the
> residual investment remaining is exactly 0.
>
> Thanks
>
> Daniel
>
>
>
>
>

[Adrian Crum]

I've attached some pages from an accounting textbook, let's see if they get through...


Adrian Crum wrote:

> I've found it's best to ask an accountant these types of questions. In
> this example, an accountant might expect average cost to be calculated a
> certain way, and not worry so much about "penny accuracy."
>
> -Adrian
>
> Daniel Kunkel wrote:
>
>> Hi
>>
>> It's so wonderful to see an integrated accounting system for OFBiz.
>> I've done some thinking about the algorithm used to calculate the
>> average cost of an inventory item, and thought I'd share it again.
>>
>> Rather than trying to store an "average cost" for an item, I believe we
>> would be much better off storing the "total investment" for each item.
>>
>> Most of the time, it won't make any difference, however I have seen that
>> directly storing the average cost leads to all sorts of floating point
>> resolution and residual adjustment issues, and complications when
>> purchasing inventory at a varying price. Most notably that the total
>> investments purchasing an item sometimes won't exactly equal the total
>> cost of goods expensed after it is all expended.
>>
>>> From http://lists.ofbiz.org/pipermail/dev/2006-March/010207.html:
>>
>>
>> For example, if you bought a million pieces for $3,889,107,143, and sold
>> them out of inventory one at a time for $3.89 without tracking/updating
>> the residual average cost..  eventually you'd have to account for
>> difference of more than $800,000!
>>
>> The solution:
>>
>> Keep a running "total investment" for each item. At the point that you
>> utilize an item, calculate and subtract the cost of those items
>> rounded to a penny for example. Other currencies will round to the
>> degree necessary for their currency.
>>
>> The effect of doing this will be that the cost of goods will oscillate
>> up and down, in the above example between 3.88, and 3.89
>> in such a way that when you've sold your millionth item, the residual
>> investment remaining is exactly 0.
>>
>> Thanks
>>
>> Daniel
>>
>>
>>
>>
>>
>
>

[Adrian Crum]

They didn't make it. Oh well. I put them on the Wiki:

http://docs.ofbiz.org/display/OFBIZ/Complete+the+implementation+of+the+Accounting+component+%28GL%29

Adrian Crum wrote:

> I've attached some pages from an accounting textbook, let's see if they
> get through...
>
>
> Adrian Crum wrote:
>
>> I've found it's best to ask an accountant these types of questions. In
>> this example, an accountant might expect average cost to be calculated
>> a certain way, and not worry so much about "penny accuracy."
>>
>> -Adrian
>>
>> Daniel Kunkel wrote:
>>
>>> Hi
>>>
>>> It's so wonderful to see an integrated accounting system for OFBiz.
>>> I've done some thinking about the algorithm used to calculate the
>>> average cost of an inventory item, and thought I'd share it again.
>>>
>>> Rather than trying to store an "average cost" for an item, I believe we
>>> would be much better off storing the "total investment" for each item.
>>>
>>> Most of the time, it won't make any difference, however I have seen that
>>> directly storing the average cost leads to all sorts of floating point
>>> resolution and residual adjustment issues, and complications when
>>> purchasing inventory at a varying price. Most notably that the total
>>> investments purchasing an item sometimes won't exactly equal the total
>>> cost of goods expensed after it is all expended.
>>>
>>>> From http://lists.ofbiz.org/pipermail/dev/2006-March/010207.html:
>>>
>>>
>>>
>>> For example, if you bought a million pieces for $3,889,107,143, and sold
>>> them out of inventory one at a time for $3.89 without tracking/updating
>>> the residual average cost..  eventually you'd have to account for
>>> difference of more than $800,000!
>>>
>>> The solution:
>>>
>>> Keep a running "total investment" for each item. At the point that
>>> you utilize an item, calculate and subtract the cost of those items
>>> rounded to a penny for example. Other currencies will round to the
>>> degree necessary for their currency.
>>>
>>> The effect of doing this will be that the cost of goods will
>>> oscillate up and down, in the above example between 3.88, and 3.89
>>> in such a way that when you've sold your millionth item, the residual
>>> investment remaining is exactly 0.
>>>
>>> Thanks
>>>
>>> Daniel
>>>
>>>
>>>
>>>
>>>
>>
>>

[Daniel Kunkel]

Hi Adrian

I really appreciate your due consideration of the idea, and taking the
time do share your information. I too believe talking with an accountant
would be a good idea, and yet am fairly certain that penny accuracy is a
very worthwhile endeavor. I think those $800,000 oversights (from the
example), and floating point madness lead to difficult issues to resolve
later.

You can perhaps get a better feel for some of the real issues by
following more of the thread at:

http://lists.ofbiz.org/pipermail/dev/2006-March/010129.html 

One important excerpt:

> The real problem is that different db treat approximations in  
> different
> ways; it seems that the SQL specification says that whether  
> rounding or
> truncation is used is implementation defined, see the comments to the
> following Jira issue:
>
> http://jira.undersunconsulting.com/browse/OFBIZ-565
>
> So for example DerbyDB truncates while MySQL approximates numbers.
> For this reason, I really think we should not delegate the
> approximations to the underlying db.
>
> Another issue (but maybe a bit out of topic here) is that if we have a
> (double) variable that (after some calculations) has some decimals,  
> and
> the variable will be stored in a db field with less decimals  
> digits, we
> should approximate the variable to the db fields decimal positions
> BEFORE using it in any calculation; a good example of this is  
> reported here:
>
> http://jira.undersunconsulting.com/browse/OFBIZ-567

Thanks

Daniel


On Thu, 2008-01-10 at 09:59 -0800, Adrian Crum wrote:

> They didn't make it. Oh well. I put them on the Wiki:
>
> http://docs.ofbiz.org/display/OFBIZ/Complete+the+implementation+of+the+Accounting+component+%28GL%29
>
> Adrian Crum wrote:
>
> > I've attached some pages from an accounting textbook, let's see if they
> > get through...
> >
> >
> > Adrian Crum wrote:
> >
> >> I've found it's best to ask an accountant these types of questions. In
> >> this example, an accountant might expect average cost to be calculated
> >> a certain way, and not worry so much about "penny accuracy."
> >>
> >> -Adrian
> >>
> >> Daniel Kunkel wrote:
> >>
> >>> Hi
> >>>
> >>> It's so wonderful to see an integrated accounting system for OFBiz.
> >>> I've done some thinking about the algorithm used to calculate the
> >>> average cost of an inventory item, and thought I'd share it again.
> >>>
> >>> Rather than trying to store an "average cost" for an item, I believe we
> >>> would be much better off storing the "total investment" for each item.
> >>>
> >>> Most of the time, it won't make any difference, however I have seen that
> >>> directly storing the average cost leads to all sorts of floating point
> >>> resolution and residual adjustment issues, and complications when
> >>> purchasing inventory at a varying price. Most notably that the total
> >>> investments purchasing an item sometimes won't exactly equal the total
> >>> cost of goods expensed after it is all expended.
> >>>
> >>>> From http://lists.ofbiz.org/pipermail/dev/2006-March/010207.html:
> >>>
> >>>
> >>>
> >>> For example, if you bought a million pieces for $3,889,107,143, and sold
> >>> them out of inventory one at a time for $3.89 without tracking/updating
> >>> the residual average cost..  eventually you'd have to account for
> >>> difference of more than $800,000!
> >>>
> >>> The solution:
> >>>
> >>> Keep a running "total investment" for each item. At the point that
> >>> you utilize an item, calculate and subtract the cost of those items
> >>> rounded to a penny for example. Other currencies will round to the
> >>> degree necessary for their currency.
> >>>
> >>> The effect of doing this will be that the cost of goods will
> >>> oscillate up and down, in the above example between 3.88, and 3.89
> >>> in such a way that when you've sold your millionth item, the residual
> >>> investment remaining is exactly 0.
> >>>
> >>> Thanks
> >>>
> >>> Daniel
> >>>
> >>>
> >>>
> >>>
> >>>
> >>
> >>
>

[Christopher L]

+1

As someone who worked at a company (which shall remain nameless) that sold a software product that was off by 2c or so each month because of rounding, I can attest to the fact that accountants really do care about a cent here and there.  It also took a surprisingly painful effort to correct.

Chris

> Subject: Re: Accounting Average Cost Algorithm
> From: [hidden email]
> To: [hidden email]
> Date: Thu, 10 Jan 2008 11:04:01 -0800
>
> Hi Adrian
>
> I really appreciate your due consideration of the idea, and taking the
> time do share your information. I too believe talking with an accountant
> would be a good idea, and yet am fairly certain that penny accuracy is a
> very worthwhile endeavor. I think those $800,000 oversights (from the
> example), and floating point madness lead to difficult issues to resolve
> later.
>
> You can perhaps get a better feel for some of the real issues by
> following more of the thread at:
>
> http://lists.ofbiz.org/pipermail/dev/2006-March/010129.html 
>
> One important excerpt:
>
> > The real problem is that different db treat approximations in  
> > different
> > ways; it seems that the SQL specification says that whether  
> > rounding or
> > truncation is used is implementation defined, see the comments to the
> > following Jira issue:
> >
> > http://jira.undersunconsulting.com/browse/OFBIZ-565
> >
> > So for example DerbyDB truncates while MySQL approximates numbers.
> > For this reason, I really think we should not delegate the
> > approximations to the underlying db.
> >
> > Another issue (but maybe a bit out of topic here) is that if we have a
> > (double) variable that (after some calculations) has some decimals,  
> > and
> > the variable will be stored in a db field with less decimals  
> > digits, we
> > should approximate the variable to the db fields decimal positions
> > BEFORE using it in any calculation; a good example of this is  
> > reported here:
> >
> > http://jira.undersunconsulting.com/browse/OFBIZ-567
>
>
> Thanks
>
> Daniel
>
>
> On Thu, 2008-01-10 at 09:59 -0800, Adrian Crum wrote:
> > They didn't make it. Oh well. I put them on the Wiki:
> >
> > http://docs.ofbiz.org/display/OFBIZ/Complete+the+implementation+of+the+Accounting+component+%28GL%29
> >
> > Adrian Crum wrote:
> >
> > > I've attached some pages from an accounting textbook, let's see if they
> > > get through...
> > >
> > >
> > > Adrian Crum wrote:
> > >
> > >> I've found it's best to ask an accountant these types of questions. In
> > >> this example, an accountant might expect average cost to be calculated
> > >> a certain way, and not worry so much about "penny accuracy."
> > >>
> > >> -Adrian
> > >>
> > >> Daniel Kunkel wrote:
> > >>
> > >>> Hi
> > >>>
> > >>> It's so wonderful to see an integrated accounting system for OFBiz.
> > >>> I've done some thinking about the algorithm used to calculate the
> > >>> average cost of an inventory item, and thought I'd share it again.
> > >>>
> > >>> Rather than trying to store an "average cost" for an item, I believe we
> > >>> would be much better off storing the "total investment" for each item.
> > >>>
> > >>> Most of the time, it won't make any difference, however I have seen that
> > >>> directly storing the average cost leads to all sorts of floating point
> > >>> resolution and residual adjustment issues, and complications when
> > >>> purchasing inventory at a varying price. Most notably that the total
> > >>> investments purchasing an item sometimes won't exactly equal the total
> > >>> cost of goods expensed after it is all expended.
> > >>>
> > >>>> From http://lists.ofbiz.org/pipermail/dev/2006-March/010207.html:
> > >>>
> > >>>
> > >>>
> > >>> For example, if you bought a million pieces for $3,889,107,143, and sold
> > >>> them out of inventory one at a time for $3.89 without tracking/updating
> > >>> the residual average cost..  eventually you'd have to account for
> > >>> difference of more than $800,000!
> > >>>
> > >>> The solution:
> > >>>
> > >>> Keep a running "total investment" for each item. At the point that
> > >>> you utilize an item, calculate and subtract the cost of those items
> > >>> rounded to a penny for example. Other currencies will round to the
> > >>> degree necessary for their currency.
> > >>>
> > >>> The effect of doing this will be that the cost of goods will
> > >>> oscillate up and down, in the above example between 3.88, and 3.89
> > >>> in such a way that when you've sold your millionth item, the residual
> > >>> investment remaining is exactly 0.
> > >>>
> > >>> Thanks
> > >>>
> > >>> Daniel
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>
> > >>
> >
>

[BJ Freeman]

in a double ledger system, both sides when summed must =0
not 0.000000000001 or greater

so the transactions must be calculated the same way. letting the math
package when to average up or done will not accomplish this.

Same with any calculation that will eventually end up in the accounting
system.

Daniel Kunkel sent the following on 1/10/2008 11:04 AM:

> Hi Adrian
>
> I really appreciate your due consideration of the idea, and taking the
> time do share your information. I too believe talking with an accountant
> would be a good idea, and yet am fairly certain that penny accuracy is a
> very worthwhile endeavor. I think those $800,000 oversights (from the
> example), and floating point madness lead to difficult issues to resolve
> later.
>
> You can perhaps get a better feel for some of the real issues by
> following more of the thread at:
>
> http://lists.ofbiz.org/pipermail/dev/2006-March/010129.html 
>
> One important excerpt:
>
>> The real problem is that different db treat approximations in  
>> different
>> ways; it seems that the SQL specification says that whether  
>> rounding or
>> truncation is used is implementation defined, see the comments to the
>> following Jira issue:
>>
>> http://jira.undersunconsulting.com/browse/OFBIZ-565
>>
>> So for example DerbyDB truncates while MySQL approximates numbers.
>> For this reason, I really think we should not delegate the
>> approximations to the underlying db.
>>
>> Another issue (but maybe a bit out of topic here) is that if we have a
>> (double) variable that (after some calculations) has some decimals,  
>> and
>> the variable will be stored in a db field with less decimals  
>> digits, we
>> should approximate the variable to the db fields decimal positions
>> BEFORE using it in any calculation; a good example of this is  
>> reported here:
>>
>> http://jira.undersunconsulting.com/browse/OFBIZ-567
>
>
> Thanks
>
> Daniel
>
>
> On Thu, 2008-01-10 at 09:59 -0800, Adrian Crum wrote:
>> They didn't make it. Oh well. I put them on the Wiki:
>>
>> http://docs.ofbiz.org/display/OFBIZ/Complete+the+implementation+of+the+Accounting+component+%28GL%29
>>
>> Adrian Crum wrote:
>>
>>> I've attached some pages from an accounting textbook, let's see if they
>>> get through...
>>>
>>>
>>> Adrian Crum wrote:
>>>
>>>> I've found it's best to ask an accountant these types of questions. In
>>>> this example, an accountant might expect average cost to be calculated
>>>> a certain way, and not worry so much about "penny accuracy."
>>>>
>>>> -Adrian
>>>>
>>>> Daniel Kunkel wrote:
>>>>
>>>>> Hi
>>>>>
>>>>> It's so wonderful to see an integrated accounting system for OFBiz.
>>>>> I've done some thinking about the algorithm used to calculate the
>>>>> average cost of an inventory item, and thought I'd share it again.
>>>>>
>>>>> Rather than trying to store an "average cost" for an item, I believe we
>>>>> would be much better off storing the "total investment" for each item.
>>>>>
>>>>> Most of the time, it won't make any difference, however I have seen that
>>>>> directly storing the average cost leads to all sorts of floating point
>>>>> resolution and residual adjustment issues, and complications when
>>>>> purchasing inventory at a varying price. Most notably that the total
>>>>> investments purchasing an item sometimes won't exactly equal the total
>>>>> cost of goods expensed after it is all expended.
>>>>>
>>>>>> From http://lists.ofbiz.org/pipermail/dev/2006-March/010207.html:
>>>>>
>>>>>
>>>>> For example, if you bought a million pieces for $3,889,107,143, and sold
>>>>> them out of inventory one at a time for $3.89 without tracking/updating
>>>>> the residual average cost..  eventually you'd have to account for
>>>>> difference of more than $800,000!
>>>>>
>>>>> The solution:
>>>>>
>>>>> Keep a running "total investment" for each item. At the point that
>>>>> you utilize an item, calculate and subtract the cost of those items
>>>>> rounded to a penny for example. Other currencies will round to the
>>>>> degree necessary for their currency.
>>>>>
>>>>> The effect of doing this will be that the cost of goods will
>>>>> oscillate up and down, in the above example between 3.88, and 3.89
>>>>> in such a way that when you've sold your millionth item, the residual
>>>>> investment remaining is exactly 0.
>>>>>
>>>>> Thanks
>>>>>
>>>>> Daniel
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>
>
>
>

[Adrian Crum]

Both of you are missing my point. If an accountant chooses to use the average cost method for
inventory, he/she is aware it is an AVERAGE, not a "penny accurate" figure. If an accountant chooses
to use lifo or fifo, then they will expect a more accurate result.

-Adrian

BJ Freeman wrote:

> in a double ledger system, both sides when summed must =0
> not 0.000000000001 or greater
>
> so the transactions must be calculated the same way. letting the math
> package when to average up or done will not accomplish this.
>
> Same with any calculation that will eventually end up in the accounting
> system.
>
> Daniel Kunkel sent the following on 1/10/2008 11:04 AM:
>
>>Hi Adrian
>>
>>I really appreciate your due consideration of the idea, and taking the
>>time do share your information. I too believe talking with an accountant
>>would be a good idea, and yet am fairly certain that penny accuracy is a
>>very worthwhile endeavor. I think those $800,000 oversights (from the
>>example), and floating point madness lead to difficult issues to resolve
>>later.
>>
>>You can perhaps get a better feel for some of the real issues by
>>following more of the thread at:
>>
>>http://lists.ofbiz.org/pipermail/dev/2006-March/010129.html 
>>
>>One important excerpt:
>>
>>
>>>The real problem is that different db treat approximations in  
>>>different
>>>ways; it seems that the SQL specification says that whether  
>>>rounding or
>>>truncation is used is implementation defined, see the comments to the
>>>following Jira issue:
>>>
>>>http://jira.undersunconsulting.com/browse/OFBIZ-565
>>>
>>>So for example DerbyDB truncates while MySQL approximates numbers.
>>>For this reason, I really think we should not delegate the
>>>approximations to the underlying db.
>>>
>>>Another issue (but maybe a bit out of topic here) is that if we have a
>>>(double) variable that (after some calculations) has some decimals,  
>>>and
>>>the variable will be stored in a db field with less decimals  
>>>digits, we
>>>should approximate the variable to the db fields decimal positions
>>>BEFORE using it in any calculation; a good example of this is  
>>>reported here:
>>>
>>>http://jira.undersunconsulting.com/browse/OFBIZ-567
>>
>>
>>Thanks
>>
>>Daniel
>>
>>
>>On Thu, 2008-01-10 at 09:59 -0800, Adrian Crum wrote:
>>
>>>They didn't make it. Oh well. I put them on the Wiki:
>>>
>>>http://docs.ofbiz.org/display/OFBIZ/Complete+the+implementation+of+the+Accounting+component+%28GL%29
>>>
>>>Adrian Crum wrote:
>>>
>>>
>>>>I've attached some pages from an accounting textbook, let's see if they
>>>>get through...
>>>>
>>>>
>>>>Adrian Crum wrote:
>>>>
>>>>
>>>>>I've found it's best to ask an accountant these types of questions. In
>>>>>this example, an accountant might expect average cost to be calculated
>>>>>a certain way, and not worry so much about "penny accuracy."
>>>>>
>>>>>-Adrian
>>>>>
>>>>>Daniel Kunkel wrote:
>>>>>
>>>>>
>>>>>>Hi
>>>>>>
>>>>>>It's so wonderful to see an integrated accounting system for OFBiz.
>>>>>>I've done some thinking about the algorithm used to calculate the
>>>>>>average cost of an inventory item, and thought I'd share it again.
>>>>>>
>>>>>>Rather than trying to store an "average cost" for an item, I believe we
>>>>>>would be much better off storing the "total investment" for each item.
>>>>>>
>>>>>>Most of the time, it won't make any difference, however I have seen that
>>>>>>directly storing the average cost leads to all sorts of floating point
>>>>>>resolution and residual adjustment issues, and complications when
>>>>>>purchasing inventory at a varying price. Most notably that the total
>>>>>>investments purchasing an item sometimes won't exactly equal the total
>>>>>>cost of goods expensed after it is all expended.
>>>>>>
>>>>>>
>>>>>>>From http://lists.ofbiz.org/pipermail/dev/2006-March/010207.html:
>>>>>>
>>>>>>
>>>>>>For example, if you bought a million pieces for $3,889,107,143, and sold
>>>>>>them out of inventory one at a time for $3.89 without tracking/updating
>>>>>>the residual average cost..  eventually you'd have to account for
>>>>>>difference of more than $800,000!
>>>>>>
>>>>>>The solution:
>>>>>>
>>>>>>Keep a running "total investment" for each item. At the point that
>>>>>>you utilize an item, calculate and subtract the cost of those items
>>>>>>rounded to a penny for example. Other currencies will round to the
>>>>>>degree necessary for their currency.
>>>>>>
>>>>>>The effect of doing this will be that the cost of goods will
>>>>>>oscillate up and down, in the above example between 3.88, and 3.89
>>>>>>in such a way that when you've sold your millionth item, the residual
>>>>>>investment remaining is exactly 0.
>>>>>>
>>>>>>Thanks
>>>>>>
>>>>>>Daniel
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>
>>
>>
>
>

[BJ Freeman]

Sorry I hijacked you thread Adrian.
I agree with you about averaging cost.
and there is process that allows for this to come out in the Accounting.

Adrian Crum sent the following on 1/10/2008 12:18 PM:

> Both of you are missing my point. If an accountant chooses to use the
> average cost method for inventory, he/she is aware it is an AVERAGE, not
> a "penny accurate" figure. If an accountant chooses to use lifo or fifo,
> then they will expect a more accurate result.
>
> -Adrian
>
> BJ Freeman wrote:
>
>> in a double ledger system, both sides when summed must =0
>> not 0.000000000001 or greater
>>
>> so the transactions must be calculated the same way. letting the math
>> package when to average up or done will not accomplish this.
>>
>> Same with any calculation that will eventually end up in the accounting
>> system.
>>
>> Daniel Kunkel sent the following on 1/10/2008 11:04 AM:
>>
>>> Hi Adrian
>>>
>>> I really appreciate your due consideration of the idea, and taking the
>>> time do share your information. I too believe talking with an accountant
>>> would be a good idea, and yet am fairly certain that penny accuracy is a
>>> very worthwhile endeavor. I think those $800,000 oversights (from the
>>> example), and floating point madness lead to difficult issues to resolve
>>> later.
>>>
>>> You can perhaps get a better feel for some of the real issues by
>>> following more of the thread at:
>>>
>>> http://lists.ofbiz.org/pipermail/dev/2006-March/010129.html
>>> One important excerpt:
>>>
>>>> The real problem is that different db treat approximations in
>>>> different
>>>> ways; it seems that the SQL specification says that whether
>>>> rounding or
>>>> truncation is used is implementation defined, see the comments to the
>>>> following Jira issue:
>>>>
>>>> http://jira.undersunconsulting.com/browse/OFBIZ-565
>>>>
>>>> So for example DerbyDB truncates while MySQL approximates numbers.
>>>> For this reason, I really think we should not delegate the
>>>> approximations to the underlying db.
>>>>
>>>> Another issue (but maybe a bit out of topic here) is that if we have a
>>>> (double) variable that (after some calculations) has some decimals,
>>>> and
>>>> the variable will be stored in a db field with less decimals
>>>> digits, we
>>>> should approximate the variable to the db fields decimal positions
>>>> BEFORE using it in any calculation; a good example of this is
>>>> reported here:
>>>>
>>>> http://jira.undersunconsulting.com/browse/OFBIZ-567
>>>
>>>
>>> Thanks
>>>
>>> Daniel
>>>
>>>
>>> On Thu, 2008-01-10 at 09:59 -0800, Adrian Crum wrote:
>>>
>>>> They didn't make it. Oh well. I put them on the Wiki:
>>>>
>>>> http://docs.ofbiz.org/display/OFBIZ/Complete+the+implementation+of+the+Accounting+component+%28GL%29
>>>>
>>>>
>>>> Adrian Crum wrote:
>>>>
>>>>
>>>>> I've attached some pages from an accounting textbook, let's see if
>>>>> they get through...
>>>>>
>>>>>
>>>>> Adrian Crum wrote:
>>>>>
>>>>>
>>>>>> I've found it's best to ask an accountant these types of
>>>>>> questions. In this example, an accountant might expect average
>>>>>> cost to be calculated a certain way, and not worry so much about
>>>>>> "penny accuracy."
>>>>>>
>>>>>> -Adrian
>>>>>>
>>>>>> Daniel Kunkel wrote:
>>>>>>
>>>>>>
>>>>>>> Hi
>>>>>>>
>>>>>>> It's so wonderful to see an integrated accounting system for OFBiz.
>>>>>>> I've done some thinking about the algorithm used to calculate the
>>>>>>> average cost of an inventory item, and thought I'd share it again.
>>>>>>>
>>>>>>> Rather than trying to store an "average cost" for an item, I
>>>>>>> believe we
>>>>>>> would be much better off storing the "total investment" for each
>>>>>>> item.
>>>>>>>
>>>>>>> Most of the time, it won't make any difference, however I have
>>>>>>> seen that
>>>>>>> directly storing the average cost leads to all sorts of floating
>>>>>>> point
>>>>>>> resolution and residual adjustment issues, and complications when
>>>>>>> purchasing inventory at a varying price. Most notably that the total
>>>>>>> investments purchasing an item sometimes won't exactly equal the
>>>>>>> total
>>>>>>> cost of goods expensed after it is all expended.
>>>>>>>
>>>>>>>
>>>>>>>> From http://lists.ofbiz.org/pipermail/dev/2006-March/010207.html:
>>>>>>>
>>>>>>>
>>>>>>> For example, if you bought a million pieces for $3,889,107,143,
>>>>>>> and sold
>>>>>>> them out of inventory one at a time for $3.89 without
>>>>>>> tracking/updating
>>>>>>> the residual average cost..  eventually you'd have to account for
>>>>>>> difference of more than $800,000!
>>>>>>>
>>>>>>> The solution:
>>>>>>>
>>>>>>> Keep a running "total investment" for each item. At the point
>>>>>>> that you utilize an item, calculate and subtract the cost of
>>>>>>> those items rounded to a penny for example. Other currencies will
>>>>>>> round to the degree necessary for their currency.
>>>>>>>
>>>>>>> The effect of doing this will be that the cost of goods will
>>>>>>> oscillate up and down, in the above example between 3.88, and 3.89
>>>>>>> in such a way that when you've sold your millionth item, the
>>>>>>> residual investment remaining is exactly 0.
>>>>>>>
>>>>>>> Thanks
>>>>>>>
>>>>>>> Daniel
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>
>>>
>>>
>>
>>
>
>
>
>

[David E Jones]

I've found this baffling over the years, but Adrian has a strong point  
here.

With modern systems, especially highly integrated ones like OFBiz, we  
can do crazy things to get actual inventory costs and so on, but for  
COGS calculations companies often don't want that. Because goods  
purchased for resale vary in price and such, and there is often a  
desire to even out such costs over a longer period, they actually  
prefer to use averages and such because they are allowed in GAAP and  
they can make the company's books look better.

It's a scary thing, especially when you come from a technical  
background where you're always trying to get the "right" answer, but  
people often don't want the "right" answer, they want the "best"  
answer according to some other metric, and that metric for most  
companies is profitability and consistently increasing long-term  
performance.

-David


On Jan 10, 2008, at 1:18 PM, Adrian Crum wrote:

> Both of you are missing my point. If an accountant chooses to use  
> the average cost method for inventory, he/she is aware it is an  
> AVERAGE, not a "penny accurate" figure. If an accountant chooses to  
> use lifo or fifo, then they will expect a more accurate result.
>
> -Adrian
>
> BJ Freeman wrote:
>
>> in a double ledger system, both sides when summed must =0
>> not 0.000000000001 or greater
>> so the transactions must be calculated the same way. letting the math
>> package when to average up or done will not accomplish this.
>> Same with any calculation that will eventually end up in the  
>> accounting
>> system.
>> Daniel Kunkel sent the following on 1/10/2008 11:04 AM:
>>> Hi Adrian
>>>
>>> I really appreciate your due consideration of the idea, and taking  
>>> the
>>> time do share your information. I too believe talking with an  
>>> accountant
>>> would be a good idea, and yet am fairly certain that penny  
>>> accuracy is a
>>> very worthwhile endeavor. I think those $800,000 oversights (from  
>>> the
>>> example), and floating point madness lead to difficult issues to  
>>> resolve
>>> later.
>>>
>>> You can perhaps get a better feel for some of the real issues by
>>> following more of the thread at:
>>>
>>> http://lists.ofbiz.org/pipermail/dev/2006-March/010129.html
>>> One important excerpt:
>>>
>>>> The real problem is that different db treat approximations in  
>>>> different
>>>> ways; it seems that the SQL specification says that whether  
>>>> rounding or
>>>> truncation is used is implementation defined, see the comments to  
>>>> the
>>>> following Jira issue:
>>>>
>>>> http://jira.undersunconsulting.com/browse/OFBIZ-565
>>>>
>>>> So for example DerbyDB truncates while MySQL approximates numbers.
>>>> For this reason, I really think we should not delegate the
>>>> approximations to the underlying db.
>>>>
>>>> Another issue (but maybe a bit out of topic here) is that if we  
>>>> have a
>>>> (double) variable that (after some calculations) has some  
>>>> decimals,  and
>>>> the variable will be stored in a db field with less decimals  
>>>> digits, we
>>>> should approximate the variable to the db fields decimal positions
>>>> BEFORE using it in any calculation; a good example of this is  
>>>> reported here:
>>>>
>>>> http://jira.undersunconsulting.com/browse/OFBIZ-567
>>>
>>>
>>> Thanks
>>>
>>> Daniel
>>>
>>>
>>> On Thu, 2008-01-10 at 09:59 -0800, Adrian Crum wrote:
>>>
>>>> They didn't make it. Oh well. I put them on the Wiki:
>>>>
>>>> http://docs.ofbiz.org/display/OFBIZ/Complete+the+implementation+of+the+Accounting+component+%28GL%29
>>>>
>>>> Adrian Crum wrote:
>>>>
>>>>
>>>>> I've attached some pages from an accounting textbook, let's see  
>>>>> if they get through...
>>>>>
>>>>>
>>>>> Adrian Crum wrote:
>>>>>
>>>>>
>>>>>> I've found it's best to ask an accountant these types of  
>>>>>> questions. In this example, an accountant might expect average  
>>>>>> cost to be calculated a certain way, and not worry so much  
>>>>>> about "penny accuracy."
>>>>>>
>>>>>> -Adrian
>>>>>>
>>>>>> Daniel Kunkel wrote:
>>>>>>
>>>>>>
>>>>>>> Hi
>>>>>>>
>>>>>>> It's so wonderful to see an integrated accounting system for  
>>>>>>> OFBiz.
>>>>>>> I've done some thinking about the algorithm used to calculate  
>>>>>>> the
>>>>>>> average cost of an inventory item, and thought I'd share it  
>>>>>>> again.
>>>>>>>
>>>>>>> Rather than trying to store an "average cost" for an item, I  
>>>>>>> believe we
>>>>>>> would be much better off storing the "total investment" for  
>>>>>>> each item.
>>>>>>>
>>>>>>> Most of the time, it won't make any difference, however I have  
>>>>>>> seen that
>>>>>>> directly storing the average cost leads to all sorts of  
>>>>>>> floating point
>>>>>>> resolution and residual adjustment issues, and complications  
>>>>>>> when
>>>>>>> purchasing inventory at a varying price. Most notably that the  
>>>>>>> total
>>>>>>> investments purchasing an item sometimes won't exactly equal  
>>>>>>> the total
>>>>>>> cost of goods expensed after it is all expended.
>>>>>>>
>>>>>>>
>>>>>>>> From http://lists.ofbiz.org/pipermail/dev/2006-March/010207.html 
>>>>>>>> :
>>>>>>>
>>>>>>>
>>>>>>> For example, if you bought a million pieces for  
>>>>>>> $3,889,107,143, and sold
>>>>>>> them out of inventory one at a time for $3.89 without tracking/
>>>>>>> updating
>>>>>>> the residual average cost..  eventually you'd have to account  
>>>>>>> for
>>>>>>> difference of more than $800,000!
>>>>>>>
>>>>>>> The solution:
>>>>>>>
>>>>>>> Keep a running "total investment" for each item. At the point  
>>>>>>> that you utilize an item, calculate and subtract the cost of  
>>>>>>> those items rounded to a penny for example. Other currencies  
>>>>>>> will round to the degree necessary for their currency.
>>>>>>>
>>>>>>> The effect of doing this will be that the cost of goods will  
>>>>>>> oscillate up and down, in the above example between 3.88, and  
>>>>>>> 3.89
>>>>>>> in such a way that when you've sold your millionth item, the  
>>>>>>> residual investment remaining is exactly 0.
>>>>>>>
>>>>>>> Thanks
>>>>>>>
>>>>>>> Daniel
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>
>>>
>>>
>

[Adrian Crum]

David E Jones wrote:
> With modern systems, especially highly integrated ones like OFBiz, we  
> can do crazy things to get actual inventory costs and so on, but for  
> COGS calculations companies often don't want that. Because goods  
> purchased for resale vary in price and such, and there is often a  
> desire to even out such costs over a longer period, they actually  
> prefer to use averages and such because they are allowed in GAAP and  
> they can make the company's books look better.

The pages I posted to the Wiki have an example of what you described - using different costing
methods to achive a desired result.

[Daniel Kunkel]

Hi

Adrian, I'm starting to wonder if the point I'm trying to make is being
missed.

Yes, I do understand that there are reasons accountants would want to
use another form of cost tracking, most commonly lifo and fifo.  

The algorithm I'm suggesting only pertains to the "weighted average"
when the business decides they want to use average cost accounting.

And, for many products, where the cost per unit works out to an exact
cent, the algorithm will work exactly as you would expect.

Where this algorithm is different is in how it handles when the average
cost doesn't work out to the exact penny.

For example, you buy 1000 units for $ 5,788.54, at an average of $
5.78854 each.

As you sell the units, you need to move the cost of each from an
inventory account to an cogs expense account. The straight forward way
to handle this moves $5.79 from inventory to expense for every unit
assuming every unit is calculated independently and you apply simple
rounding. By the time you are sold out, you'll notice the total expenses
for this item add up to $ 5.790.

The algorithm I'm suggesting will vary the costs of goods between $ 5.78
and $ 5.79 in an such a way such that total expenses will exactly equal
the total investment when you are completely sold out.

I believe it is possible to achieve the same result via the more common
method of storing 5.78854 in the database, but then you have complicated
issues with floating point resolution, rounding, adjustments, and have a
hard time making accounts exactly match.

Daniel


 Thu, 2008-01-10 at 13:18 -0800, Adrian Crum wrote:

> David E Jones wrote:
> > With modern systems, especially highly integrated ones like OFBiz, we  
> > can do crazy things to get actual inventory costs and so on, but for  
> > COGS calculations companies often don't want that. Because goods  
> > purchased for resale vary in price and such, and there is often a  
> > desire to even out such costs over a longer period, they actually  
> > prefer to use averages and such because they are allowed in GAAP and  
> > they can make the company's books look better.
>
> The pages I posted to the Wiki have an example of what you described - using different costing
> methods to achive a desired result.
>
>

[Adrian Crum]

Daniel,

I understand the point you're trying to make.

An accountant working for a user of OFBiz will want to verify OFBiz's output against manual
calculations. Our accountants do that all the time here where I work. So, if OFBiz is calculating
average cost one way, and the accountant is calculating average cost another way, then there may be
a discrepency. The accountant is going to question the accuracy of the accounting software.

All I'm suggesting is this: if the subject of this thread is "Accounting Average Cost Algorithm"
and we're really discussing an *algorithm* - then let's use a standard accounting algorithm. If the
issue is problems that arise when databases round numbers, then that's a different subject. In that
case we should be discussing "Errors Caused by Rounding In Databases."

-Adrian

Daniel Kunkel wrote:

> Hi
>
> Adrian, I'm starting to wonder if the point I'm trying to make is being
> missed.
>
> Yes, I do understand that there are reasons accountants would want to
> use another form of cost tracking, most commonly lifo and fifo.  
>
> The algorithm I'm suggesting only pertains to the "weighted average"
> when the business decides they want to use average cost accounting.
>
> And, for many products, where the cost per unit works out to an exact
> cent, the algorithm will work exactly as you would expect.
>
> Where this algorithm is different is in how it handles when the average
> cost doesn't work out to the exact penny.
>
> For example, you buy 1000 units for $ 5,788.54, at an average of $
> 5.78854 each.
>
> As you sell the units, you need to move the cost of each from an
> inventory account to an cogs expense account. The straight forward way
> to handle this moves $5.79 from inventory to expense for every unit
> assuming every unit is calculated independently and you apply simple
> rounding. By the time you are sold out, you'll notice the total expenses
> for this item add up to $ 5.790.
>
> The algorithm I'm suggesting will vary the costs of goods between $ 5.78
> and $ 5.79 in an such a way such that total expenses will exactly equal
> the total investment when you are completely sold out.
>
> I believe it is possible to achieve the same result via the more common
> method of storing 5.78854 in the database, but then you have complicated
> issues with floating point resolution, rounding, adjustments, and have a
> hard time making accounts exactly match.
>
> Daniel
>
>
>  Thu, 2008-01-10 at 13:18 -0800, Adrian Crum wrote:
>
>>David E Jones wrote:
>>
>>>With modern systems, especially highly integrated ones like OFBiz, we  
>>>can do crazy things to get actual inventory costs and so on, but for  
>>>COGS calculations companies often don't want that. Because goods  
>>>purchased for resale vary in price and such, and there is often a  
>>>desire to even out such costs over a longer period, they actually  
>>>prefer to use averages and such because they are allowed in GAAP and  
>>>they can make the company's books look better.
>>
>>The pages I posted to the Wiki have an example of what you described - using different costing
>>methods to achive a desired result.
>>
>>
>
>
>