Version 4.40 and later:

The Average Inventory YTD is calculated based on the Year and Period/Month selected. The data used in the calculation is stored in the IM_PeriodPostingHistory.m4t file.

The first step is to calculate the Ending Balance for each period. For example: if period 3 was selected you would have the following calculation:

  • BeginningBalQty (Per1) + PeriodChangeQty (Per1) = EndingBalPer1
  • BeginningBalQty (Per1) + PeriodChangeQty (Per1) + PeriodChangeQty (Per2) = EndingBalPer2
  • BeginningBalQty (Per1) + PeriodChangeQty (Per1) + PeriodChangeQty (Per2) + PeriodChangeQty (Per3) = EndingBalPer3

Then, add these Ending Balances together then divide by the number of periods.

(EndingBalPer1 + EndingBalPer2 + EndingBalPer3)  / 3

Here is what those formulas look like with data:

  • Period 1:  0 + 0 = 0
  • Period 2:  0 + 0 + 0 = 0
  • Period 3:  0 + 0 + 0 + 0 = 0
  • Period 4:  0 + 0 + 0 + 0 + 33 = 33
  • Period 5:  0 + 0 + 0 + 0 + 33 + 2642 = 2675
  • Average Inventory Quantity: YTD (0+0+0+33+2675) / 5 = 541.60

Version 3.71 to 4.30

Note: Inventory is perpetual; therefore the quantity on hand may include future transactions.

The Average Inventory is calculated at period end by dividing the running total of the quantity on hand plus the current quantity on hand by the number of periods.

Formula: (A + B) / C = D

  • A = The running total of the quantity on hand
    The quantity on hand for the prior period is added to the accumulated quantities on hand for all the prior periods for the year. The current period is not included.
  • B = The quantity on hand for the period just closed
  • C = The period last closed (in other words, for period 5, C = 5)
  • D = Average inventory

Example:

  1. Inventory is in period 1, and a new item is created. A receipt for 50 of the items is processed.  Then I/M period end is performed. When period end was performed, the quantity on hand was 50. The calculation for Average Inventory is: (0+50)/1=50
    Note: A=0 (Prior total); B = 50 (0 + 50); C = 1; D = 50.
  2. In period 2, another receipt for 75 of the items is processed. Then I/M period 2 is closed. At the time of period end, the item’s quantity on hand is 125. The calculation forAverage Inventory is: (50 + 125) / 2 = 87.5
    Note: A = 50 (0+50); B = 125 (50+75); C = 2; D = 87.5
  3. During period 3, another receipt for 100 of the items is processed, and then period 3 is closed. The current quantity on hand is 225. The calculation for Average Inventory is:  (175 + 225) / 3 = 133.3
    Note: A = 175 (0+50+125); B = 225 (125+100); C = 3; D = 133.3
  4. In period 4, a sales transaction for 150 of the items is processed, and then period 4 is closed. The current quantity on hand is 75. The calculation for Average Inventory is: (400 + 75) / 4  = 118.7
    Note: A = 400 (0+50+125+225); B = 75 (225-150); C =  4; D = 118.7
  5. In period 5, a receipt for 50 of the items is processed, and then period 5 is closed. The current quantity on hand is 125. The calculation for Average Inventory is: (475 + 125) / 5  = 120
    Note: A = 475 (0+50+125+225+75); B = 125 (75+50); C = 5; D = 120

Notes: The Average Inventory is stored in the IM2 file in field 28 (Avg on hand Qty), which is calculated at period end. Each time period end is processed, this field is updated in the following way:
(Avg on Hand Qty in IM2 * Previous Period) + (Current Qty on Hand ) / Current Period.
For step 5 (before running Period End for this period), the Avg Qty on Hand in IM2 was 118.7 (as determined at the end of step 4). When running Period End for period 5, the calculation is as follows:
(118.7 (Avg On Hand Qty) * 4 (Previous Period)) + (125 (Current Qty on Hand)) / 5 (Current Period) =
(474.8 + 125) / 5 = 119.96 (which is rounded to 120)

From the Sage Knowledgebase.  If you have any questions please contact our support team as DSD Business Systems

Jim Woodhead is Vice President of Professional Services of DSD Business Systems, a national provider of on-demand (cloud) and on-premises ERP and CRM software, specializing in wholesale distribution, manufacturing, warehouse management, inventory, business intelligence and eCommerce software. DSD offers Sage 100 (formerly MAS 90), Sage 300 (formerly Accpac), Sage 500 (formerly MAS 500), NetSuite, Sage FAS, Sage HRMS (formerly Abra), Sage CRM, Sage SalesLogix, Extended Solutions, and Custom Programming.
Jwoodhead
View all posts by Jwoodhead
Jims website
Share
© 2010 DSDtips.com Suffusion WordPress theme by Sayontan Sinha