Weighted average valuation from Tally

 The attached module has  developed the weighted average module from Tally. The required collections are available for analysis in the Turbodata datawarehouse  by the end client.

The required numbers impact the following:

Balance sheet entries including the closing stock valuations.

The weighted average periodic valuations have been calculated on

a daily basis. These numbers shall be used to calculate the perpetual cost of goods sold. This shall further impact the gross profit calculations for the end client.

Methodology for solving the problem:

·         For the opening stock numbers and opening stock valuations the extraction from item masters was done from Tally.

·         For stock movements the extraction from the voucher captured the inward and outwards movements as debit and credit entries (independent of the voucher types).

·         After the extraction the data was taken to the cloud instance where the running sum for inward movements(quantity and value) was calculated for each stock item.

o   Quick points: the calculations can also be done at other levels of granularity such as categories, stock groups and at lower levels of granularities such as batch wise allocations. These are custom requirements that shall be handled on a case by case basis.

o   The development team has currently excluded ‘sales order’, ‘purchase order’, delivery notes’ and ‘receipt notes’ from the valuations

o   The data has been input in the Tally datawarehouse. The masters and the transactions of the Tally ERP have not been updated by the team. The option of doing the same rests with the end client and the Tally partner.

o   In the current release only the numbers for the stock items where the closing stock numbers are matching have been released. At places where the closing stock numbers are not matching a separate report shall be released.

Method for adjusting the closing stock numbers:

·         Run the Tally datawarehouse report:

Get perpetual cost of goods sold in a  plug and play basis

The difference between the Closing Value and Closing Value(Tally) is the adjustment to be done for the closing stock value.

 

Method for calculating the running total:

Attached are the steps for calculating the same:

a.)    Calculate the debit quantity, debit value and credit quantity from tally keeping the voucher type filter details as above into consideration.

b.)    Calculate the running debit quantity, running total debit amount.

c.)     The periodic weighted average rate is calculated accordingly.

d.)    The periodic weighted average rate for the given fiscal date multiplied with the closing stock number for the given fiscal date gives the weighted average valuation.

e.)    The closing stock valuation on the last date gives the closing stock value for the comparison purposes.

 

 

 

Flowchart for implementation:

 

Benefits of doing the analysis on cloud:

a.)    The speed of the calculations goes up significantly without blocking the functioning of Tally ERP .

b.)    The connector can be scheduled to run automatically(no manual intervention is required). The connector can open Tally at a prescheduled time and run the extraction. Thereafter on pressing the required button in Tally the data shall be updated in the Tally datawarehouse.

c.)     For each company a separate database shall be created. This is been done to ensure data security and flexibility in giving offerings

 

 

 

Method of implementation:

 

a.)    Give the company name for which the stock valuation is to be done.

 

a.)    The development team shall configure the details for the end client for stock valuation.

b.)    Load the TCP file.

 Open Tally ERP >Configure (Shortcut key: F12) > Product & Features (shortcut key: T) > Manage Local TDL (shortcut Key:  F4) >Paste the Location of TCP File

c.) Load the Tally connector

Fetch Tally Data to fetch data from Tally into cloud

 

 

a.)    Refresh the report data.

 Gateway of Tally>COGS and Weighted Avg

WA: For Weighted Average Report

WA (Excel Export): Export Weighted Average Report into Excel sheet

Cogs: For Cost of goods sold Report

Cogs (Excel Export): Export Cost of goods sold Report into Excel

Location of Exported Excel Report is: C:\Excel Export\WA and COGS

 

 

Other activities:

·         Automation of extraction: the following inputs shall be required for the end client:

o   The time at which extractor is to run.

o   Multiple locations consolidation: in case then the consolidation logic shall need to handled on a case by case basis.

o   In the current product the stock journal entry values have been taken as is. In case of customization required for stock journal values then the logic coding needs to be handled separately.

Contact:Apoorv Chaturvedi

Phone: +91-8802466356

Email: support@mndatasolutions.com;apoorv@mnnbi.com;support@tureasesolutions.com