| Subject: | Check on how Inventory Valuation is Calculated |
| Summary: | Bula Team,
We have done the following test to check how Inventory Valuation is Calculated.- Application Hosted on: https://linktechnologies.com.au/DEMO-LinkSOFT-EDGE
- Version: 13.40.0602.GA
- Product Code - TESTVAL
- Costing Method - Average
Table 1 - Manual Average Cost CalculationLine
| TESTVAL | | Qty | Cost | Sub Total (cost) | Total Cost (running) | Total Qty (running) | Avg Cost | 1
| Adjust In | ADAA000658 | 1.00 | 10.00 | 10.00 | 10.00 | 1.00 | 10.00 | 2
| Sale | RC000014 | - 1.00 | 10.00 | - 10.00 | - | - | 10.00 | 3
| Sale | RC000015 | - 2.00 | 10.00 | - 20.00 | - 20.00 | - 2.00 | 10.00 | 4
| Adjust In | ADAA000659 | 10.00 | 12.00 | 120.00 | 100.00 | 8.00 | 12.50 | 5
| Sale | RC000016 | - 1.00 | 12.50 | - 12.50 | 87.50 | 7.00 | 12.50 | 6
| Purchase | PUR00022 | 5.00 | 11.50 | 57.50 | 145.00 | 12.00 | 12.08 |
Table 2 - System Average Cost CalculationLine
| TESTVAL | | Qty | Cost | Sub Total (cost) | Total Cost (running) | Total Qty (running) | Avg Cost | 1
| Adjust In | ADAA000658 | 1.00 | 10.00 | 10.00 | 10.00 | 1.00 | 10.00 | 2
| Sale | RC000014 | - 1.00 | 10.00 | - 10.00 | - | - | 10.00 | 3
| Sale | RC000015 | - 2.00 | 10.00 | - 20.00 | - 20.00 | - 2.00 | 10.00 | 4
| Adjust In | ADAA000659 | 10.00 | 12.00 | 120.00 | 100.00 | 8.00 | 12.00 | 5
| Sale | RC000016 | - 1.00 | 12.00 | - 12.00 | 88.00 | 7.00 | 12.00 | 6
| Purchase | PUR00022 | 5.00 | 11.50 | 57.50 | 145.50 | 12.00 | 11.80 |
System Calculated - Inventory Valuation Cost - 12.13
- Product Master Cost - 11.7917
- There is a mismatch of costs calculated by the system
Regards
Vineshwar Prasad |
| Audit Notes: | Edited by sanjay on 06/09/22 14:26. Edited by sanjay on 15/08/22 10:46. Edited by sanjay on 15/08/22 09:32. |
| 14 Jun 2022 | 10:52AM Comment 1 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 14-06-2022 10:49 AM Time Taken: 2.00 |
| Teams meeting with Vineshwar and sanjay - Reviewed the case header and added line numbers and Title to Table 1 and Table 2
- Average Cost is calculated when stock is brought In, wither through adjustments or Receipts
- When the stock reaches ZERO "In Stock", the Average Cost is Zero
Next Step: - Vineshwar to review the case and check accounting standards
- Log a Development case if we need to change the average cost calculations
|
|
| 08 Aug 2022 | 11:48AM Comment 2 by Navneet Kumar Assigned To: Vineshwar Prasad (Edge Business Solutions) Followup Date: 12-08-2022 11:37 AM Time Taken: 4.00 Notes: ETC extended from: 07/06/2022 to 12/08/2022 |
| We have done the following test to check how Inventory Valuation is Calculated. - Application Hosted on: https://linktechnologies.com.au/DEMO-LinkSOFT-EDGE
- Version: 14.00.0729.GA
- Product Code - P10011
- Costing Method - Average
Table 1 - Average Cost Calculation on Product Master (Correct)
Task | Details | Transaction Number | Quantity | Cost | Sub total QTY | Sub Total Cost | Total Quantity | Total Cost | Average Cost | Comments |
1 | Purchase Order | PR000007 | 10.00 | 10.00 | 10.00 | 100.00 | 10.00 | 100.00 | 10.00 | |
2 | Purchase Order | PR000008 | 5.00 | 10.00 | 5.00 | 50.00 | 15.00 | 150.00 | 10.00 | |
3 | Purchase Order | PR000009 | 1.00 | 10.00 | 1.00 | 10.00 | 16.00 | 160.00 | 10.00 | |
4 | Sales | TX000020 | - 5.00 | 10.00 | - 5.00 | - 50.00 | 11.00 | 110.00 | 10.00 | |
5 | Sales | TX000021 | - 6.00 | 10.00 | - 6.00 | - 60.00 | 5.00 | 50.00 | 10.00 | |
6 | Purchase Order | PR000010 | 5.00 | 15.00 | 5.00 | 75.00 | 10.00 | 125.00 | 12.50 | |
7 | sales | TX000022 | - 5.00 | 12.50 | - 5.00 | - 62.50 | 5.00 | 62.50 | 12.50 | |
8 | Sales | TX000023 | - 10.00 | 12.50 | - 10.00 | - 125.00 | - 5.00 | - 62.50 | 12.50 | |
9 | Purchase Order | PR000011 | 10.00 | 15.00 | 10.00 | 150.00 | 5.00 | 75.00 | 15.00 | Correct - Product Master is showing Average cost as $15, SOH - 5 |
10 | Sales | | - 1.00 | 15.00 | - 1.00 | - 15.00 | 4.00 | 60.00 | 15.00 | Correct - Product Master is showing Average cost as $15, SOH - 4 |
Table 2 - Average Cost Calculation on Inventory Movement and Inventory Valuation Report (Incorrect)
Task | Details | Transaction Number | Quantity | Cost | Sub total QTY | Sub Total Cost | Total Quantity | Total Cost | Average Cost | Comments |
1 | Purchase Order | PR000007 | 10.00 | 10.00 | 10.00 | 100.00 | 10.00 | 100.00 | 10.00 | |
2 | Purchase Order | PR000008 | 5.00 | 10.00 | 5.00 | 50.00 | 15.00 | 150.00 | 10.00 | |
3 | Purchase Order | PR000009 | 1.00 | 10.00 | 1.00 | 10.00 | 16.00 | 160.00 | 10.00 | |
4 | Sales | TX000020 | - 5.00 | 10.00 | - 5.00 | - 50.00 | 11.00 | 110.00 | 10.00 | |
5 | Sales | TX000021 | - 6.00 | 10.00 | - 6.00 | - 60.00 | 5.00 | 50.00 | 10.00 | |
6 | Purchase Order | PR000010 | 5.00 | 15.00 | 5.00 | 75.00 | 10.00 | 125.00 | 12.50 | |
7 | sales | TX000022 | - 5.00 | 12.50 | - 5.00 | - 62.50 | 5.00 | 62.50 | 12.50 | |
8 | Sales | TX000023 | - 10.00 | 12.50 | - 10.00 | - 125.00 | - 5.00 | - 62.50 | 12.50 | |
9 | Purchase Order | PR000011 | 10.00 | 15.00 | 10.00 | 150.00 | 5.00 | 87.50 | 17.50 | 1. Incorrect - Average Cost because system is also adding negative balance from previous line 2. Incorrect - Inventory Movement report is showing total cost as $87.50, SOH - 5 3. Incorrect - Inventory Valuation is showing total cost as $87.50, SOH - 5, Average Cost - $17.50 |
10 | Sales | | - 1.00 | 15.00 | - 1.00 | - 15.00 | 4.00 | 72.50 | 18.13 | 1. Incorrect - Average Cost because system is also adding negative balance from previous line 2. Incorrect - Inventory Movement report is showing total cost as $72.50, SOH - 4 3. Incorrect - Inventory Valuation is showing total cost as $72.50, SOH - 4, Average Cost - $18.13 |
Next Step - Discuss with Development Team Regards Navneet Kumar & Vineshwar Prasad |
|
| 11 Aug 2022 | 02:04PM Comment 3 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 19-08-2022 01:48 PM Time Taken: 1.00 Notes: ETC extended from: 12/08/2022 to 19/08/2022 |
| Discussion Notes - Sanjay and Vineshwar: - "Inventory Valuation Report" should not include "cost" for items with "negative" " In-Stock"
Next step: Transfer case to Development and assign to sanjay |
|
| 12 Aug 2022 | 08:43AM Comment 4 by Vineshwar Prasad (Edge Business Solutions) Assigned To: Sanjay (Link Technologies) Followup Date: 16-08-2022 10:42 AM Notes: ETC extended from: 19/08/2022 to 16/08/2022 |
| Assigning case to development |
|
| 15 Aug 2022 | 09:32AM Comment 5 by Sanjay (Link Technologies) Case L12773 added to project 14.10 |
| 15 Aug 2022 | 09:32AM Comment 6 by Sanjay (Link Technologies) ETC was changed from 16/08/2022 to 19/08/2022 |
| 02 Sep 2022 | 02:04PM Comment 7 by Sanjay (Link Technologies) Assigned To: Development Followup Date: 06-09-2022 03:54 PM Time Taken: 12.00 |
| Hi Vineshwar, The difference you are seeing is to do with the concept of "Valuation" and "Average Cost". Valuation refers to the "value" of stock movement, while Average Cost refers to the "average" cost of Incoming items, from Adjustments, Purchases and Transfers, which is used for the purpose of Sales Analysis and Sales Markups. These two figures don't match when you have "negative stock" in play. Here is an example to explain the concept: - Refer to Table 1 below. We have done an "Adjustment IN" and an "Adjustment OUT".
- Note that the VALUATION based on these two transactions is $140 and there are only "two" items in stock, however, the "Average Cost" of this item is $20, which is different from the Valuation "Average Cost" which is $70
Table 1 - Valuation and Average Cost Concepts
| Date | Transaction | Quantity | Unit Cost | Cost Movement | | 01/08/2022 | Adjustment OUT | -10 | 10 | -100 | | 02/08/2022 | Adjustment IN | 12 | 20 | 240 | | | Total | | | 140 | Average Value (In Stock divided by Cost Movement) | | | | 70 |
|
|