Wednesday, 11 December 2024

Standardized inventory valuation-Turbodata

 Turbodata seeks to offer a standardized inventory valuation methodology for the end clients. For example within Tally the inventory valuation has multiple options.


For example in the report stockvouchers the extract logic for the same is as follows[the stockvouchers report is aggregated to the stockitem monthly report ].The closing stock valuations are then used for profit and loss and balance sheet for the end client:

<ENVELOPE>

  <HEADER>

    <VERSION>1</VERSION>

    <TALLYREQUEST>Export</TALLYREQUEST>

    <TYPE>Data</TYPE>

    <ID>stockvouchers</ID>

  </HEADER>

<BODY>

<DESC>

<STATICVARIABLES>

        <EXPLODEFLAG>Yes</EXPLODEFLAG>

            <SVEXPORTFORMAT>$$SysName:XML</SVEXPORTFORMAT>

<stockitemname>angularstopcock</stockitemname>

                  </STATICVARIABLES>

      <TDL>

        <TDLMESSAGE>

<REPORT NAME="stockvouchers" ISMODIFY="Yes">

  <ADD>Set : SV From Date:"20240401"</ADD> 

  <ADD>Set : SV To Date :"20250430" </ADD> 

                                       

  <ADD>Set : ExplodeFlag : Yes</ADD>

                                                <ADD>Set : Onlyaccvoucher : Yes</ADD>

                </REPORT>

        </TDLMESSAGE>

      </TDL>

</DESC>

</BODY>

</ENVELOPE>


The report stockvouchers has multiple options as part of the variables. These variables values can be set to get different valuations for the end clients 


[Report: Stock Vouchers]

Use : Range Template

Use : Browser Common SysFormulae

    Title       : If (##IsItemBatchReport) Then $$SPrintf:@@GdwnVoucherTitleFormat:@@BatchTitle Else If ##IsItemBatchGdwnReport Then $$LocaleString:"Batch Godown Vouchers" Else $$LocaleString:"Stock Item Vouchers"

    Family      : $$LocaleString:"Stock Voucher Details"

    List Name   : $$LocaleString:"Voucher Details"

Help : @@HlpStock_Vouchers

Form        : Stock Vouchers


    Variables   : SV CurrentCompany, SV FromDate, SV ToDate, SV SortMethod

    Variable    : SVValuationMethod, SVCurrency, SVExchange, SVBudget, SVVariance

    Variables   : StockItemName, ExplodeFlag, ExplodeNarrFlag, IsDayBook, IsLedgerReport, IsItemReport, IsCCReport, IsGrpReport

    Variable    : DSPShowMonthly, DSPHasMultiLevel

    Variable    : OnlyInVouchers, OnlyOutVouchers

    Variable    : OnlyAccVouchers, OnlyInvVouchers

    Variable    : IsStockReport, IsGodownReport, IsMatConSum

    Variable    : DSPNotScaleable, DSPHasColumnTotal

    Variable    : IsMultiPage, InNewPages

    Variable    : VoucherTypeName, SVStockItem

    Variable    : DSPGodownName, DSPShowOpBal


    Variable    : DSPShowClosing, DSPShowInwards, DSPShowOutwards, DSPShowAdditionalDescription, DSPQtyAlternate

    Variable    : DSPShowQty, DSPShowRate, DSPShowValue, StkvchCostExplodeFlag

Variable : OnFocusInactiveDelete, IsItemBatchReport, DSPBatchName, IsItemBatchGdwnReport

; ;;Start Fresh page for each balancing method

Variable : BMInNewPage

Variables : LedStarting, LedNameStartingFrom, LedNameStartingTo

Variable : SVStockBalType

Variable : IsStockFromParty, IsPrintStockItem

  Variable : ShowGrossValue, ShowGPOnGV, ShowConsumption, DSPInvNameStyle

Variable : VchTotalTrans, ShowBasisOfValues, ExtractMode

  

Set : ShowBasisOfValues : Yes

    Set         : BMInNewPage : No

    Set         : SV SortMethod     : @@Default

    Set         : Is Item Report    : Yes

    Set         : Is StockReport    : Yes

    Set         : DSPShowMonthly    : No

    Set         : DSPHasMultiLevel  : No

    Set         : DSPNotScaleable   : Yes

    Set         : DSPHasColumnTotal : No

    Set         : IsMultiPage       : No

    Set         : InNewPages        : Yes


    Set         : OnlyInVouchers    : No

    Set         : OnlyOutVouchers   : No

    Set         : VoucherTypeName   : ""

    Set         : SVValuationMethod : ""

    Set         : SVCurrency        : ""

    Set         : SVExchange        : 1

    Set         : DSPShowInwards    : ##STKVCHInwards

    Set         : DSPShowOutwards   : ##STKVCHOutwards

    Set         : DSPShowClosing    : ##STKVCHClosing

    Set         : DSPShowQty        : ##STKVCHQuantity

    Set         : DSPShowRate       : ##STKVCHRate

    Set         : DSPShowValue      : ##STKVCHValue

Set : OnFocusInactiveDelete : No

Set : DSPBatchName : ##DSPBatchName

Set : IsItemBatchReport : ##IsItemBatchReport

Set         : IsItemBatchGdwnReport : ##IsItemBatchGdwnReport

Set        : DSPShowOpBal      : ##DSPShowOpBal

Set : StockItemName : #StockItemName

Set : IsPrintStockItem : No

    

These are very large number oif variables that one can set for the given report. The variations in the variables can result in differing numbers across companies, stcok items and even godowns. 

Problem statement we seek to solve:

a.) Give consistant numbers across all the stockitems for a company. In the current scenario one needs to choose the stock item to get the number. It is very difficult for the end client to go and check the valuations item by item. 

b.) No need to check the valuations and reports item by item. A consolidated standardized set of reports is there for the entire company to know its inventory valuations.

c.) Option of using weighted average or batch rate  whenever the client desires the same. One can maintain both batchwise and non batchwise inventory.


Problem resolution: Turbodata proposes to resolev this problem by building the inventory valuations from the base using DAX. For example the inward movements can be defined by extensive row and filter level contexts as follows:

INWARD_BALANCE_QTY =

/*ROW LEVEL CONTEXTS IN TERMS OF ITEM, COMPANY AND DATES ARE DEFINED HERE*/
VAR COMPANY_NAME='Stockitem_view'[Companyname]
VAR ITEM_NAME='Stockitem_view'[Itemname]
VAR DATE_COMPARE='Stockitem_view'[DocumentDate]



VAR B=
/*FIND THE RUNNING  SUM QTY OF THOSE VOUCHER INVENTORY ENTRIES WHERE VOPUCHER TYPE IS NOT SALES ORDER, PURCHASE ORDER, DELIVERY NOTE AND RECEIPT NOTE*/
CALCULATE(SUM('Stockitem_view'[Debit_quantity]),FILTER(all('Stockitem_view'),'Stockitem_view'[Itemname]=ITEM_NAME && 'Stockitem_view'[Companyname]=COMPANY_NAME  && 'Stockitem_view'[DocumentDate]<=DATE_COMPARE && Stockitem_view[Iscancelled_voucherinventory]="No" && Stockitem_view[Isvoid_voucherinventory]="No" && Stockitem_view[Isinvoice_voucherinventory]="yes" && NOT(Stockitem_view[VOUCHERTYPE_PARENT] IN {"SALES ORDER","PURCHASE ORDER","DELIVERY NOTE", "RECEIPT NOTE"})))+

/* FIND THE OPENING STOCK VALUE OF THE ITEM NON BATCH WISE. Add the same to the running sum */
calculate(max('Item Details'[OpeningBalance]),filter(ALL('Item Details'),'Item Details'[CompanyName]=COMPANY_NAME && 'Item Details'[Name.1]=ITEM_NAME))+


/* find the RUNNING SUM FOR THOSE DELIVERY NOTE ENTRIES WHERE THE TRACKING NUMBER ASSOCIATED WITH THE DELIVERY NOTE NUMBER IS NOT AVAILABLE IN THE SALES INVOICE*/
/*CALCULATE(SUM(Sale_missingtrackingnumber[Debitqty]),FILTER(Sale_missingtrackingnumber,Sale_missingtrackingnumber[STOCKITEMNAME]=ITEM_NAME && COMPANY_NAME=Sale_missingtrackingnumber[Company_name] && DATE_COMPARE>=Sale_missingtrackingnumber[DOCUMENTDATE]))


+*/
/* find the RUNNING SUM FOR THOSE RECEIPT  NOTE ENTRIES WHERE THE TRACKING NUMBER ASSOCIATED WITH THE RECEIPT NOTE NUMBER IS NOT AVAILABLE IN THE PURCHASE  INVOICE*/
CALCULATE(SUM(Purchase_missingtrackingnumber[Debitqty]),FILTER(ALL(Purchase_missingtrackingnumber),Purchase_missingtrackingnumber[STOCKITEMNAME]=ITEM_NAME && COMPANY_NAME=Purchase_missingtrackingnumber[Company_name] && DATE_COMPARE>=Purchase_missingtrackingnumber[DOCUMENTDATE]))
RETURN B



Adding the direct expenses associated with the inventory movement.
For the same the sample DAX is as follows:
a.) The value of Purchase accounts for the associated vouchers is calculated.
b.) tHE DIFFERENCE IS CALCULATED between the valaue calculated in a.) with the total inventory value at the item movement level.
c.) The amoount in b.) is then allocated to each item in proportion of the item amounts in inventory movement
d.) The value in c.) is then divided by the total quantity of the item in inventory movement to give the added component for direct costing.

WA_RATE =
VAR A='Stockitem_view'[INWARD_BALANCE_VALUE]/'Stockitem_view'[INWARD_BALANCE_QTY]
Var stockitemname=stockitem_view[itemname]
 VAR MASTERID=Stockitem_view[Masterid]
Var Total_debit_amount=Calculate(sum(Stockitem_view[Debit_Amount]),filter(all(stockitem_view),masterid=stockitem_view[masterid]))

 
Var total_debit_amount_ledger=Calculate(sum(Ledger_view[SaleAmounDebit]),filter(all(ledger_view),masterid=ledger_view[masterid]  && ledger_view[ledgerprimary]="Purchase accounts"))
 
Var difference=total_debit_amount_ledger-Total_debit_amount
 

Var total_amount_item=Calculate(sum(Stockitem_view[Debit_amount]),filter(all(stockitem_view),masterid=stockitem_view[masterid] && stockitemname=stockitem_view[itemname]))
Var total_quantity_item=Calculate(sum(Stockitem_view[Debit_quantity]),filter(all(stockitem_view),masterid=stockitem_view[masterid] && stockitemname=stockitem_view[itemname]))
Var directexpense_rate=difference*(total_amount_item/Total_debit_amount)
Var wa_rate_directexpense=directexpense_rate/total_quantity_item

RETURN a+wa_rate_directexpense

Here we are calculating the standard weighted average rate is cumulative inwardamount/cumulative inward quantity.

In order to add the direct expenses associated with the coucher, we have taken the following steps:
a.)

The code in the variable 'B' entailing missing trackling numbers has been incorporated in case
the onlyaccvouchers variable is set to No.

Based on the above Powerbi module the client can get a set documented inventory costing, weighted average methods across all items for all periods in time.

Shift to Turbodata sql server for better analysis

contact: Apoorv Chaturvedi
Phone:+91-8802466356
email:support@turbodatatool.com;support@mndatasolutions.com
youtube channel: https://www.youtube.com/@mndatasolutions2042
Indiamart site:https://www.indiamart.com/mnbusinessintelligenceindia/



at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Data insertion-Tally(approaches)

 Problem statement: Many softwares look to insert data into Tally from their application.This blog looks at issues and approaches for the sa...