Friday, 20 December 2024

Increase the report efficiency multifold -cost center analysis

 Problem statement: many customers use Tally for cost center analysis. The customers have a need to get profit and loss, cash flows statements for cost centers across multiple companies. In order to get the details from Tally itself the end client needs to work with the cost center voucher report such as follows:



The xml request is as follows:


<ENVELOPE>

  <HEADER>

    <VERSION>1</VERSION>

    <TALLYREQUEST>Export</TALLYREQUEST>

    <TYPE>Data</TYPE>

    <ID>ccvouchers</ID>

  </HEADER>

<BODY>

<DESC>

<STATICVARIABLES>

        <EXPLODEFLAG>Yes</EXPLODEFLAG>

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

<Costcentrename>Aerocitymohali</Costcentrename>


                  </STATICVARIABLES>

      <TDL>

        <TDLMESSAGE>

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

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

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

                                       

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

                </REPORT>

        </TDLMESSAGE>

      </TDL>

</DESC>

</BODY>

</ENVELOPE>



Here the given report needs to be accessed cost center by cost center to get the complete details.

Tally also has a number of variables whose value can be set.





Thereafter comes the issue of handling inventory valuations by cost center. This report export will also give the inventory movement details. These issues need to be handled.

The extract takes place only for one company.

Bottlenecks:
a.) Works  for only one company
b.) A number of variables can be adjusted.
c.) If there are a large number of cost center then the report needs to be accessed multiple times. One has to do the same repeatedly. say one has 50 cost centers then the loop has to be run 50 times.



Getting the cost of sales value: Tally in its profit and loss report gives the cost of sales details as follows:


The cost of sales needs to be derived too at the cost centre level. Hence the inventory valuations need to be also done at the cost center level or the inventory valuations need to be done perpetually using the weighted average rate.

In order to get the inventory valuation at the item level, we need to access the stock vouchers report.

Here again we need to get the data repeatedly from Tally. If there are 50 stock items then we need to hit the stock vouchers report 50 times.


Our resolution:
a.) Extract raw data from Tally multiple companies.
b.) Get raw cost center data for ledger and inventory movements.
c.) The complete group hierarchy should be enabled. We do not need just the ledgerprimary but also the other child attributes for our analysis.
d.) Tally extract through json should happen only once. The data processing should happen in Powerbi or SQL Server.


Say a client has 2 companies with  40 ledgers, 10 stock items then our productivity should increase by atleast 800 times(40*10*2).
The given cost center ledger movement has the complete group and ledger hierarchy across unlimited companies.



The debit and credit movements have also been specified in the same. From here we shall get the complete ledger movements.


Inventory movements we have  the second view as follows:


Thereafter we have consolidated the ledger and inventory movements onto a single table. In that single table we have done cash inflow, cash outflow, profit and loss and other analysis.


Limitations: The cost center cost of sales would included only those inventory movements where godown is involved. One needs to calculate the perpetual weighted average rate.





Alternatively we can get the weighted average rate on a perpetual basis from the inventory movements as shown below:


For getting the comprehensive data for profit and loss and cash flow statements please contact the following:


Name: Apoorv Chaturvedi

email: support@turbodatatool.com;support@mndatasolutions.com

Phone:+91-8802466356



at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Reduce costs:Paying for each mobile number for Mobile access for ERP/Tally ERP data

 Problem statement: for many companies the number of internal mobile users for ERP/Tally ERP data on mobile are significant. Say the number ...