8 Replies Latest reply on May 23, 2012 10:19 AM by philmodjunk

    How do I calculate the moving average in inventory

    AW

      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!!

        • 1. Re: How do I calculate the moving average in inventory
          philmodjunk

          Question #1. What kind of moving average do you want? A simple moving average or a cumulative moving average? (Looked up the issue in Wikipedia and noticed that there's more than one formula for a moving average...)

          What is significant about the $6 dollar average? usually a simple moving average will average a specific subset of data points such as the most recent N purchases.

          Question #2. Are you sure that its a MOVING average that you want here? How does that detail affect your average cost? Average all entries where in = out, all entries where in does not equal out or ???

          • 2. Re: How do I calculate the moving average in inventory
            AW

            Thank you for your reply, PhilModjunk.  I am a newbie to FileMaker and I appreciate your assistance.

            After carefully considering your questions, I believe a simple moving average will accomplish what I want.  I just want to be able to calculate the average cost per unit using the last 5 purchases, which if I understand you correctly, is what you are refering to as " a specific subset of data points such as the most recent N purchases."  

            I am using the Inventory Starter Solution from FileMaker 12.  

            How do I accomplish this?  Using the first example I mentioned in my original post, do I need to create an area in the database to include the date, quantity purchased, and the cost per unit of that purchase in order to eventually calculate the average cost per unit of the last 5 puchases?  How is this determined?  By date?  Would such a calculation take into consideration only those items that are indicated as "IN" on the Starter Solution?  Something like, if "IN" has a value, then use the cost per unit associated with that "IN" to determine the moving average?  I ask because the Starter Solution allows for you to indicate items that were removed from inventory by the use of "OUT".  "Out" would have its own date as I would not batch the "OUT" value but treat it as a line item each time I sold an item from my inventory.

            I hope this makes sense.  

            Thank you for your patience.  

            • 3. Re: How do I calculate the moving average in inventory
              philmodjunk

              Time to take a look at the Inventory tarter solution. FM 12 starter solutions are brand new so we are all figuring them out as we go...

              Hmmm, First issue that I see is that this starter solution does not record the current price each time an item is reordered. You'd need to add an item cost field to the Stock Transactions table.

              Then it could be possible to compute an average of the unit cost for the most recent 5 "in" records in the stock transactions table if you set up a relationship to support that:

              In manage | database | Fields, select the Stock transactions table from the table drop down and add a new calculation field called cItemIDIn:

              If ( units In > 0 ; ITEM ID MATCH FIELD )

              In Manage | Database | relationships, make a new table occurrence of Stock Transactions by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box In Stock Transactions.

              We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

              Add it to your relationships like this:

              Inventory::ITEM ID MATCH FIELD = In Stock Transactions::cItemIDIn

              Double click the relationship line and specify that the In Stock Transactions be sorted by Date in descending order. This will make the most recenlty dated stock Transactions the first 5 related records when you use this new table occurrence to refer to records in the stock transactions table.

              ( GetNthRecord ( In Stock Transactions::Units In ; 1) + GetNthRecord ( In Stock Transactions::Units In ; 2 ) + GetNthRecord ( In Stock Transactions::Units In ; 3 ) + GetNthRecord ( In Stock Transactions::Units In ; 4 ) + GetNthRecord ( In Stock Transactions::Units In ; 5 ) ) / ValueCount ( List ( GetNthRecord ( In Stock Transactions::Units In ; 1) ; GetNthRecord ( In Stock Transactions::Units In ; 2 ) ; GetNthRecord ( In Stock Transactions::Units In ; 3 ) ; GetNthRecord ( In Stock Transactions::Units In ; 4 ) ; GetNthRecord ( In Stock Transactions::Units In ; 5 ) ) )

              Should then compute the average of the most recent 5 "in" items in the stock transactions table.

              PS. For those wondering why I didn't use Sum or Average here, try it and see what you get ;-)

              • 4. Re: How do I calculate the moving average in inventory
                AW

                Thanks a million, PhilModJunk.  I have done everything you said however I am coming across one issue.  In the section where you talk about GetNthRecord, I get the following error message:  "In the function Average, Count, Extend, GetRepetition, Max, Min, NPV, StDev, Sum, GetSummary or GetNthRecord, an expression was found where a field alone is needed."  

                Here is what I created for the GetNthRecord:  ( GetNthRecord ( In Stock Transactions::Units In ; 1 ) + GetNthRecord ( In Stock Transactions::Units In ; 2 ) + GetNthRecord ( In Stock Transactions::Units In ; 3 ) + GetNthRecord ( In Stock Transactions::Units In ; 4 ) + GetNthRecord ( In Stock Transactions::Units In ; 5 ) ) / ValueCount ( List ( GetNthRecord ( In Stock Transactions::Units In ; 1) + GetNthRecord ( In Stock Transactions::Units In ; 2 ) + GetNthRecord ( In Stock Transactions::Units In ; 3 ) + GetNthRecord ( In Stock Transactions::Units In ; 4 ) + GetNthRecord ( In Stock Transactions::Units In ; 5 ) ) )

                I did click ValueCount and where it defaulted to the word "text", I clicked on "List" in the functions section of "Specify Calculation" and then proceeded to build the GetNthRecord calculation.  

                 

                I'm sure the answer is simple, however, my attempts to correct the error has not been successful.  Any suggestions?  Did I miss a step?

                Thanks again!  I really appreciate all your help.  

                • 5. Re: How do I calculate the moving average in inventory
                  AW

                  One more thing... I did copy and paste the "GetNthRecord" that you posted and then set up a random set of dates and cost per units, but now my "Avg Cost per Unit" field is returning a "?" where the average should be.  

                  Thanks.

                  • 6. Re: How do I calculate the moving average in inventory
                    philmodjunk

                    You have + operators where there need to be ; delimitters. Changes in red.

                    You may want to reformat the expression by inserting some returns to make it look like this:

                    (

                       GetNthRecord ( In Stock Transactions::Units In ; 1 ) +
                       GetNthRecord ( In Stock Transactions::Units In ; 2 ) +
                       GetNthRecord ( In Stock Transactions::Units In ; 3 ) +
                       GetNthRecord ( In Stock Transactions::Units In ; 4 ) +
                       GetNthRecord ( In Stock Transactions::Units In ; 5 )
                    )
                    / ValueCount ( List ( GetNthRecord ( In Stock Transactions::Units In ; 1) ;
                                                 GetNthRecord ( In Stock Transactions::Units In ; 2 ) ;
                                                 GetNthRecord ( In Stock Transactions::Units In ; 3 ) ;
                                                 GetNthRecord ( In Stock Transactions::Units In ; 4 ) ;
                                                
                    GetNthRecord ( In Stock Transactions::Units In ; 5 )
                                                )  // list
                                         ) // value count

                    • 7. Re: How do I calculate the moving average in inventory
                      AW

                      Thanks again, PhilModJunk.  I appreciate very much your assistance.  

                      I made the correction, however I am still not getting the proper answer when I calculate the average cost per unit manually.  Shouldn't the Unit Cost/Item Cost field somehow be included in the calculation?  Recall that this was the first thing you suggested I create.  Wouldn't the "Unit Cost" be multipled by the "Units In" to get the total cost of the units purchased for a particular date?  Then, wouldn't you add the totals together and divide by the total number of "Units In"?  

                      Example.

                      Date       Unit Cost       Units In

                      1/1/12     10.00               1

                      2/1/12     20.00               1

                      3/1/12     30.00               1

                      Totals      60.00               3

                      Total Unit Cost/Total Units In = Avg Cost per Unit  60/3 = $20/Unit

                       

                      Where in my calculation do I put the Unit Cost?  I apologize for not understanding it.  Am I missing the obvious?

                      • 8. Re: How do I calculate the moving average in inventory
                        philmodjunk

                        You're not missing the obvious, I'm just not paying full attention to all the details of your initial post. Embarassed

                        My calculation computes the average number of items ordered instead of the average cost.

                        Use:

                        (

                           GetNthRecord ( In Stock Transactions::Units In ; 1 ) * GetNthRecord ( In Stock Transactions::UnitCost ; 1 ) +
                           GetNthRecord ( In Stock Transactions::Units In ; 2 ) * GetNthRecord ( In Stock Transactions::UnitCost ; 2 ) +
                           GetNthRecord ( In Stock Transactions::Units In ; 3 ) * GetNthRecord ( In Stock Transactions::UnitCost ; 3 ) +
                           GetNthRecord ( In Stock Transactions::Units In ; 4 ) * GetNthRecord ( In Stock Transactions::UnitCost ; 4 ) +
                           GetNthRecord ( In Stock Transactions::Units In ; 5 ) * GetNthRecord ( In Stock Transactions::UnitCost ; 5 )
                        )
                        / ValueCount ( List ( GetNthRecord ( In Stock Transactions::Units In ; 1) ;
                                                     GetNthRecord ( In Stock Transactions::Units In ; 2 ) ;
                                                     GetNthRecord ( In Stock Transactions::Units In ; 3 ) ;
                                                     GetNthRecord ( In Stock Transactions::Units In ; 4 ) ;
                                                    
                        GetNthRecord ( In Stock Transactions::Units In ; 5 )
                                                    )  // list
                                             ) // value count