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:
- 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. - 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 - 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 - 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 - 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
