Weighted average calculation using Tableau

 

The team has developed a module integrated with Turbodata for perpetual weighted average valuation.

The aggregations to be used for inventory valuations have been developed over turbodata.

Assumptions: the team has used inwards and outwards supply. Inwards supply shall include all purchase and journal debit entries.

Outward supplies shall include all sales and journal credit entries.

If the client has got item details within payment and receipt vouchers then the same are not covered as part of this module.

Benefits of using the Tableau weighted average module:

•      Complete flexibility of calculation closing stock by perpetual method across any level of location, time granularity.
•           Flexibility in calculating inventory ageing across any level of granularity
•    Turbodata integration module ensures that any number of locations can be consolidated in the system across any combination of ERPs.
•           Stable and mature system in terms of integration across multiple ERPs.
•       Data validation system built into the system.

Attached is the brief methodology of calculation of weighted average valuation:

For cumulative purchase value and cumulative purchase quantity  the ETL team used the following method

Step1 : sorting the data by item and date

Step 2: calculation of the running sum of purchase value

Step3: adding the opening purchase value to the value derived in step 2 for the given item across each fiscal date.

Step 4: calculation of the running sum of purchase quantity by item and date.

Step 5: adding the opening stock quantity for each item by fiscal date to the value derived in step 4.

Step 6: calculation of the weighted average rate by dividing the values in step 3 and step 5.

Step 7: Calculation of the gross profit based on the weighted average rate in item sales.

Pane down & quick table_calculation(running_total):

·        Cumulative_purchase_value

Table Calculation (Running_total)

Pane Down:

Pane Down for the purchase quantity.

·        Cumulative_purchase_qty2

·        Cumulative_purchase_value2: addition of the opening stock value and cumulative purchase value.

For the weighted average calculation the following calculation was used:

·        Weighted_average: cumulative purchase value divided by cumulative purchase quantity.

·        Closing_stock_value

    Calculations:

·        Cumulative purchase_no

{EXCLUDE  [Item],DATEPART('day', [ordeR date]):SUM([quantity_debit])}

·        Closing stock_value

[net_closing_quantity]*[weighted_average]

·        Cumulative_purchase_qty

RUNNING_SUM(ATTR([cumulative_purchase_no]))+SUM([openinG_stock (updated WA & FIFO)].[Dspclqty])

·        Cumulative_purchase_value

{EXCLUDE  [Item],DATEPART('day', [ordeR date]):SUM([purchase_value2])}

·        Cumulative_purchase_value2

[opening_stock_value]+RUNNING_SUM(ATTR([cumulative_purchase_value]))

·        Net_closing_qty

SUM([quantity_debit_credit])+SUM([openinG_stock (updated WA & FIFO)].[Dspclqty])

·        Opening_stock_value

SUM([openinG_stock (updated WA & FIFO)].[Dspclqty])*SUM([openinG_stock (updated WA & FIFO)].[Dspclrate])

·        Purchase_value2

if [Is Deemed Positive]='yes' then [Quantity Appended]*[Rate Appended]

else 0 end

 ·        Purchase_value2

if [Is Deemed Positive]='yes' then [Quantity Appended]*[Rate Appended]

else 0 end

·        Weighted_average

[cumulative_purchase_value2]/[cumulative_purchase_qty2]

v Blending concept:

The opening stock number and the transaction data come from 2(two) different data sources. For each item the opening stock could be 0 or a non zero value. Accordingly the ETL team has used blending for getting the cumulative sum of purchase quantity and the opening stock quantity. The process of blending shall ensure the following:

a.)  Duplicates in the source or target tables for item names shall be removed

Cumulative inventory total.[item]=opening_stock_value.[dspdispname]

Final Dashboard:

·        Weighted_average:

·        Opening_closing_stock_valuation:

Prepared by

Deepanshu Garg: deepanshu@mnnbi.com

For further retail, sales, inventory, ledger, taxation and loyalty analytics please contact the following:

Apoorv chaturvedi: apoorv@mnnbi.com

Phone: +91-8802466356