Menu Path: Report >> > Stock Report >> > SBR-05 Historical Stock Balance
Filters
...
- Select Company and click Submit
- Select ALL Location under 1 Company and click Submit → controlled under permission
- Select Date → key in end of financial year
- Key in Item Code Description → leave it blank to view all
...
- Key in Item Code Range → leave it blank to view all
...
Result Set
...
- Select Item Category → leave it untick to view all
- Select Item Status →select ALL
- Select Item Type → deselect will generate All Type (If you wish to Compare Balance Sheet to Inventory, please select Inventory type only)
- Tick Group By → leave it untick
- Select Order by → item name or item code
- Tick Options → tick ALL
- Select Transaction Type → deselect will generate All Type
Sample of Report
NOTES:
UOM → Unit of Measurement
- QTY (A) → Stock Quantity on the selected date
- Min Purchase Price
...
- → Minimum GRN Price
- Max Purchase Price
...
- → Maximum GRN Price
- Curr MA (B)
...
- → Item Moving Average on current date which can be compared with Stock Movement
- Inv Value base on Curr MA (A*B)
...
Miscellaneous Information:
- Acc Inv Value/unit(C/A) is NOT same as Curr MA (B)
- Reset MA does not have any Stock Movement. Hence Acc Inv Value(C) which is based on movement - does not include the Reset MA amount.
- Balance Sheet Inventory Value(C+D) is comparable to the Balance Sheet Inventory GL Value.
- SBR-05 Historical Stock Balance does not take note of stock movement of Internal Stock Transfer, since the Stock Transfer does not affect the Company Stock Value in any manner.
- Calculation on Acc Inv Value/unit(C/A) → Curr MA * Qty on the selected locations under 1 company
- Historical MA (C) → Balance Sheet "Inventory" Value (for all locations under 1 company) / Stock Quantity (for all locations under 1 company)
- Inv Value based on Historical MA (A*C) → Historical MA * Qty on the selected locations under 1 company
- Last Purchase → latest GRN Date
- Total of Column Inv Value based on Historical MA (A*C) need to be the same with Balance Sheet "Inventory" Value (the breakdown of the Item Code inventory value can be seen under Stock Movement)
- Refer to /wiki/spaces/WU/pages/132626579 to remove the VALUE on ZERO item quantity
Example:
Example item Test_Item has the following transactions:
| Transaction | Transaction Date | Quantity | Unit Price (Selling / buying price) | Total stock value change | Balance Quantity | MA Cost | Remark |
|---|
| GRN 1 | 05/01/13 | 10 | 100 | 1000 | 10 | 100 | MA Cost Calculated |
| GRN 2 | 05/03/13 | 5 | 70 | 350 | 15 | 90 | MA Cost Recalculated: (100*10+70*5)/15 |
| Invoice 1 | 05/05/13 | -5 | 150 | -450 | 10 | 90 | MA Cost Remain unchanged |
| Reset MA | 05/09/13 |
| 10 | +10 |
| 200 |
| 10 | 110 | MA Cost is set manually from 90.00 to 110.00 . Total amount on the Reset MA is : change of unit price * total balance quantity . So for this case (110.00-90.00)*10 = 200 | ||
| Invoice 2 | 05/11/13 | -1 | 150 | -110 |
| 9 | 110 |
Generating the SBR-05 on 2nd May 2013
With filter setting date: 1st May 2013
Only one GRN is involved.
| Transaction | Transaction Date | Quantity | Unit Price Purchase / Unit Cost | Unit Price Sales | Balance Quantity | Balance Amount | MA Cost | Change in Inventory Value |
|---|---|---|---|---|---|---|---|---|
| Opening | 0 | 0 | ||||||
| GRN 1 | 05/01/13 | 10 | 100 |
| 10 |
| 1000 |
| 100 |
| 1000 |
| GRN 2 | 05/ |
| 03/13 |
Generating the SBR-05 on 8th May 2013
With filter setting date: 1st May 2013
Only GRN is involved. However different GRN unit price caused current MA to affect Inv Value base on Curr MA (A*B)
| No | Item Code | Item Name | UOM | Qty(A) | Min Purchase price | Max Purchase Price | Curr MA (B) | Inv Value base on Curr MA (A*B) | Acc Inv Value (C) | Acc Inv Value/unit (C/A) | Reset MA (D) | Balance Sheet Inventory Value (C+D) | Last Purchase |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Test_Item | Test_Item | unit | 10 | 100 | 100 | 90 | 900 | 1000 | 100 | 0 | 1000 | 05/01/13 |
Generating the SBR-05 on 10th May 2013
With filter setting date: 8th May 2013
Different Transactions (with movements) involved. No Reset MA Value.
| No | Item Code | Item Name | UOM | Qty(A) | Min Purchase price | Max Purchase Price | Curr MA (B) | Inv Value base on Curr MA (A*B) | Acc Inv Value (C) | Acc Inv Value/unit (C/A) | Reset MA (D) | Balance Sheet Inventory Value (C+D) | Last Purchase |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Test_Item | Test_Item | unit | 5 | 70 | 100 | 110 | 550 | 900 | 90 | 0 | 900 | 05/03/13 |
Generating the SBR-05 on 10th May 2013
With filter setting date: 9th May 2013
Transactions with Movement (Invoice & GRN) along with Reset MA (No movement - not effecting Acc Inv Value)
| 5 | 70 | 15 | 1350 | 90 | 350 | |||
| Invoice 1 | 05/05/13 | -5 | 90 | 150 | 10 | 900 | 90 | -450 |
| Reset MA | 05/09/13 | 10 | 110 | 10 | 1100 | 110 | 200 | |
| Invoice 2 | 05/11/13 | -1 | 110 | 150 | 9 | 990 | 110 | -110 |
NOTES:
- Please exclude GL CODE item type when user want to compare the "inventory" with Stock Flow Report
Related WIKI Pages:
| Panel | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
|