AnsweredAssumed Answered

How do I calculate the moving average in inventory

Question asked by AW on May 8, 2012
Latest reply on May 23, 2012 by philmodjunk

Title

How do I calculate the moving average in inventory

Post

I incorrectly posted this on the "report an issue" section of the forum.  I am reposting it here.  Thank you.

 

I am trying to figure out how to add a moving average calculation in the Inventory Starter Solution template.  

Facts.  I am using FIFO.  Inventory purchased is the same item though the price per unit can increase or decrease depending on
the quantity purchased.

Here is my example:
Issue #1

Date:      Units In      Units Out      Cost per Unit        Total Cost
1/1/12      5                                       $5                  $25
2/5/12      10                                     $6                  $60
3/9/12      6                                       $7                  $42

My total units in inventory is 21 and my total cost for the three purchases is $127.  My average cost for each unit is $6.05
( $127/21 = $6.05/unit)


Question #1 - What is the formula to set up that will calculate the moving average to achieve a result of $6?

Second issue:  I want to be able for the moving average to be adjusted when the amount in Units In equals the amount in Units Out. 

Here is my example:
Issue #2

Date:      Units In      Units Out      Cost per Unit    Total Cost
1/1/12      5                  5                   $5               $25
2/5/12      10                                     $6               $60
3/9/12      6                                       $7               $42

My total units in inventory is now 16, as I sold the five units purchased on 1/1/12.  My moving average is now based on the units
remaining from 2/5/12 and 3/9/12.  This gives me a moving average of $6.38 ( $102/16 = 6.38/unit).

Question #2 - What is the formula to set up that will calculate a new moving average based on the inventory remaining if the
amount of Units Out equals the amount of Units In for a particular Date?

Thanks!!

Outcomes