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

 

FIFO Logic-Turbodata

FIFO logic of Turbodata

Attached is the FIFO logic as per Turbodata. This document is for any firm that has one of the following requirements:

·         It is a firm that is using accounting software that calculates the inventory values using on fly calculations.

·         The finance managers need a comprehensive documentation for the closing stock values for their overseas auditors.

·         Firms that are looking to raise money from overseas investors(in the United states) or have holdings with multinational corporations.

·         Firms that are facing high inventory pressures and need to do accurate ABC categorization for inventory. These firms require historical valuations of the closing stock to carry out inventory optimizations.

·         firms that do not have batch level details for the items

·         Firms that have multiple locations, multiple SKUs, multiple cost centers and multiple warehouses.

The FIFO logic has been developed within the following parameters:

·         The valuations have been done on a monthly basis only. No portion of the code goes to the orderline basis. This is because within Tally/other ERPs one can change the data on  a prior transaction date. In such a scenario the perpetual valuations could take a lot of time and would be very cumbersome to operate. However the ETL team has the technical ability to create a code for orderline FIFO calculations

·         For the period of month, the ETL team has taken averages within the required debit entries. The required debit entries used for the stock value calculation are only purchase and item journal debit entries. The payment and receipt transactions have not been considered for the stock value transactions.

·         Running of the FIFO valuations: The FIFO valuations can be run anytime since the calculations do not entail orderline calculations(only monthly periodic calculations).

Explanation of the logic:

·         The closing stock number shall be calculated on a monthly basis.

·         In case the total credit quantity for the fiscal month is lower than the closing stock number for the prior fiscal month then closing stock value shall be calculated as follows:
o   closing stock value is equal to the total debit value for the current fiscal year-fiscal month and the product of the following 2(two) entries:

§  difference between the prior month closing stock and the credit quantity for the current fiscal year and fiscal month

§  The FIFO rate for the prior fiscal month calculated as follows:·         closing stock value for the prior fiscal month divided by the closing stock number for the prior fiscal month

·         In case the total credit quantity for the fiscal month is greater  than the closing stock number for the prior fiscal month then the closing stock can be calculated as follows:

o   closing stock is equal to the product between the following:

§  Difference between the debit quantity for the current fiscal year-fiscal month and the difference between the following two(2) measures.

·         Credit quantity of the current fiscal month

·         closing stock quantity for the prior fiscal month

o   The debit rate for the current fiscal month defined as follows:

§  debit value for the current fiscal month

§  debit quantity for the current fiscal month.

Sample explanation of the entries:

Let us assume the entries are as follows:

In the month of August 2017, the opening stock is 555.37 units. the credit quantity of 150.72 units shall be taken from the opening stock only(since the credit quantity is lower than the opening stock number).

Hence the closing stock value shall be the total debit value for the current fiscal month and sum product of the following:

·         difference between the opening stock quantity and credit quantity

·         Average rate of the opening stock(161k/555.37)

Month of September 2017:  In this particular month there is no credit quantity. Hence the closing stock value is the sum of the closing stock value for the month of august 2017 and debit value for september 2017.

Month of October 2017: In this month the credit quantity(2763) is more than the closing stock quantity for the last fiscal month(2755). so the closing stock value shall be the defined as a product for the following:

·         The difference between the debit quantity for the current fiscal month and the difference between the following;

o   credit quantity for the current fiscal month

o    closing stock quantity for the prior fiscal month

·         The debit rate that is the division of  debit value and the debit quantity.

Valuation for November2017:

In this month there is no debit quantity only credit quantity. Hence the closing stock calculation can be done by calculating the product of the following as follows:

·         difference between the October 2017 closing stock number and November 2017 closing stock number

·         The closing rate for the month of October 2017

For the end clients desiring a complete historical valuation by FIFO, please contact the following:

Apoorv Chaturvedi: apoorv@mnnbi.com

Sharad Maheshwari: tureasesolutions@gmail.com

Other logics possible by Turbodata.

·         FIFO perpetual

·         Tally FIFO business logic: accuracy more than 95%

·         FIFO quarterly

EXPLANATION OF THE TALLY FIFO LOGIC (AS UNDERSTOOD BY ETL TEAM):

The ETL team has sought to reverse engineer the Tally FIFO business logic. The explanation is as follows. The ETL team can develop the FIFO logic based on Tally based on the end client sign off.

·         The debit and credit entries are taken for all the vouchers. Hence in case of a payment voucher having stock item name details the stock summary shall include the value for the same. The quantity of the item is not necessary.

·         The closing stock numbers are stored on a monthly basis. That is Tally does not keep closing stock history beyond a fiscal year-fiscal month.

         Within a fiscal month, Tally finds the FIFO valuations on a day to day basis as per the following logicTally finds the difference between the debit and credit entries for a given fiscal day.In case the difference between the debit and credit entries is 0 then Tally picks up the prior closing stock number and the closing stock value for the FIFO closing stock number and fifo closing stock value for the current fiscal date.

o   If there is a difference between the credit quantity and the debit quantity then the difference between the credit and the debit quantity is allocated in the following manner(The ETL team is assuring an accuracy of more than 95% for the same):

§  The credit quantity is allocated to the opening stock at the beginning of the fiscal month in the following ratio:

·         (1-opening stock quantity at the beginning of the fiscal month)/(cumulative stock quantity for the fiscal date before the FIFO valuation has been done)* [difference between the credit quantity for the given fiscal date and the debit quantity for the given fiscal date]. This value is extracted for the ratio to 2 decimal places.

·         The remaining quantity from the above is allocated to the purchase quantity and the purchase values in the current fiscal month before the sales date.

·         The value of the opening stock quantity is adjusted dynamically after each iteration. Say for example the opening stock at the beginning of the fiscal month was 150. After the first sales instance the quantity that has been credited is 36. Then in the next iteration the value of the opening stock to be considered shall be 150-36=114.

o   Tally will not stop the iteration for  negative stocks.

 Perpetual FIFO calculations from Turbodata:

For this case the debit and credit entries are calculated for the given location for each fiscal date. Thereafter the FIFO calculations are done based on the logic given for the periodic FIFO calculations as stated above in (FIFO logic of Turbodata).

Are you a customer having the following issues:

Having issues with large value of  slow moving inventory
Have issues with cash flow cycles
Do not have clarity regarding product profitability

Our product Turbodata can help your firm with resolving the above issues. The product is inspired by philosophy of The Goal by Eliyahu Goldratt and Profit Beyond Measure by Thomas Johnson and Ander Brohms(please see the appendix 1 for a summary of the philosophies)

Both the philosophies imply that the end client should use the order line profitability instead of using the periodic calculations. Only then would the end client get complete visibility into its operations and profitability by customer, region etc.

What is required for determining the orderline profitability?

For determining the same the end client needs to have valuations of inventory using perpetual method instead of the periodic method.

As a case to the point, consider the following:

In the attached scenario of an item, the valuation using weighted average/FIFO has been done on periodic basis. Hence the end client looses the orderline profitability details by using the same.

However in the snapshot below using Turbodata, the weighted average calculations are done on a daily basis(as in the attached snapshot)

 

This enables the end client to calculate orderline profitability.

Issues with calculating the orderline profitability:

v  In some of the software,  negative stock is allowed.  Because of the same orderline profitability calculations might be impacted. The sample below gives the first instance of negative stock for an item.

Sample attached below:

v  The physical stock entries valued at 0(zero) value can create discrepancies in the stock valuations.

v  Data consolidation from multiple systems could be required for calculating the same.

v  Data transformation in terms of business logic of the end client needs to be done so that the required calculations come into force.

By using Turbodata, the end clients shall be able to achieve the following:

v  Go towards orderline profitability by getting an estimate of cost of goods sold based on perpetual FIFO and weighted average calculations.

v  Achieve the following activities

o   Data consolidation

o   Data cleansing: clean the master data before reporting is done

o   Data profiling: find the first instance when the closing stock of an item turned negative at godown or consolidated level.
       Data analytics: have consolidated dashboards along with predictive analytics facilities at economical costs.

v  Better management of inventories: by finding the profitability of the sale of items at the orderline level for a given set of customers.

v  Prepare the data for predictive analytics and forecasting through data compression and sql reduction. The predictive analytics and forecasting is required to capture the variations from the standard values for sales. A significant variation is to be captured early so that the end client could take the corrective actions quickly.

Interested in moving towards orderline profitability:

v  Deployment of Turbodata solution(for testing sample data): USD 3000/-(USD Three thousand only)+taxes as applicable. Contact us for a sample demo

v  Buy our standard book based on Turbodata project experiences: USD 5/-(five) dollars

Please contact the following for the above for a demo

Name: Apoorv Chaturvedi

Email: apoorv@mnnbi.com

Website: www.mnnbi.com

Appendix 1

What do the above management philosophies say?

The Goal:

The Goal is inspired by the theory of constraints. This implies that there are 3 parameters that are critical for any firm:

v  Throughput: the rate at which the system generates the sales(our definition of cash sales)

v  Inventory: the input material required to convert the inputs material to final product for generating throughput.

v  Labor: The manpower required for converting inventory to throughput.

The protagonist Jonah in ‘Goal’ also insisted on standard deviations and variations to be part of the process. The variations to be detected on a close to real time basis so that any errors are caught beforehand.

Profit Beyond Measure:

Profit Beyond Measure  is inspired by the Toyota Production system. It emphasizes that the manufacturing company should function like a human body. The functional managers should account for self sustainability(standard cycle times), diversity and interdependence( the manufacturing managers need to look at the whole system like a human body and not just a single component).

The book emphasizes that there should be a reduction in inventory by reducing a changeover times at each of the working station. That is the manufacturing process should start once the customer order has come into the system. The book further looks at ‘Design to order’ by designing multiple configurable modules to offer the end clients multiple types of products.

The system emphasizes catching the errors in production cycle quickly so that there is reduced material wastage.

Sample example of inventory optimization:Inventory optimization of large trading company

For Inventory Optimisation, our top end functional consultants and partners

Mr. Sharad Maheshwari:  https://www.linkedin.com/in/sharad-maheshwari-688b733

 Mr. Puneet Khanna: https://www.linkedin.com/in/puneet2009/

For inventory optimization services, consider the following:

• email apoorv@mnnbi.com

• Or fill up the contact form on the website  http://mnnbi.com/.

Automated GST Filing using Turbodata and GSP Partners

Are you facing the following issues with regards to GST filing?

• ·         Delay in filing
• ·         Concern regarding the changing regulations from the government
• ·         Concern regarding reconciliation: specially for customers using MS Excel upload.
• ·         Have a manual process for GSTR filing. The manual process is prone to error
• ·         Have high manpower costs related with GST filing.

Turbodata shall help your firm with faster, easier and more convenient GST filing.

How is Turbodata different?

• ·         All the reports for the end client shall be developed on the cloud installation. Only a minimal extract for all the vouchers and masters shall be done from the end client location. The ETL team shall commit to usage of maximum amount of RAM for the same(say 1 GB for incremental data extract)
• ·         The end client can do the prior change of the data. The system shall automatically take care of the same. This is enabled through incremental data load process using data normalization.
• ·         No reports shall be developed at the client location. All the reporting work shall be done at the server location.
• ·         Initial and incremental transaction data extract shall be done from the end client location.
• ·         The end client need not worry about re filing the GST reports since it shall be done by the GSP partner automatically.
• ·         The package is very easy to deploy, deliver and maintain. No high end software are required. The system can extract data from SAP, Tally and other source systems with ease.
• ·         Dependence on MS Excel for tax filing purposes is taken away since it could result in data errors and discrepancies.

  Current system:

Why is the Turbodata system better?

Turbodata system:

·         Turbodata system is inspired by ‘The Deming Way’, ‘The Goal’ and the Toyota production system and the Inmon methodology.  In a nutshell the following are the features copied from the above systems by Turbodata:

o   No error prone data should be passed for the reporting purposes. The data needs to be cleansed, audited and consolidated before report development.

o   The processing of the transaction should be done as soon as the transaction has been fed in the source system. That is the processing should take place on a real time basis and not specifically at the end of the month. Turbodata enables this feature in the following manner:

§  Each transaction fed into the end client source system is assumed to be an order from the end client.

§  The system offers the facility for real time extract and upload(current system is manual but the data can be loaded on a daily basis by the end client go the server)

o   Once the data has been loaded onto the server, it is transferred to a normalized database(insert, update and deletes). At the data warehouse level the data cleansing, data transformation, data consolidation activities are done

o   Once the data has been cleansed at the datawarehouse level then the reports for GST are developed. In one single lot, GSTR1, GSTR2 and GSTR3 reports can be developed.

o   Turbodata is integrated with at least one GSP partner. The end client could look at other GSP partner solutions if it desires the same.

o   The deployment of the solution is very easy and convenient. For any end client the deployment should take not more than 20(twenty) minutes. Minimum installation pre requisites are required.

o   The data for the end client is stored in a datawarehouse. The end client does not need to worry about changes in the statutory requirements. Other high end services like inventory optimization and predictive analytics are possible on the cloud.

To check why should the end client consider Turbodata GST, please check the following linkage:
http://mndatasolutionsindia.blogspot.in/2018/02/why-turbodata-gst.html

GST Reporting Issues:

Contact

Apoorv Chaturvedi

Phone: 8802466356

Email: apoorv@mnnbi.com

Website: www.mnnbi.com

Indicate the following:

·         ERP system/ERP systems

·         Turnover: frequency of load

·         Number of locations

 ·         Automation of GST filings(a sample with a GSP).

·         Attached is a blog for the same:

·         http://mndatasolutionsindia.blogspot.com/2018/07/spreadsheetconsolidationsample.html

·         Blog link: http://mnnbi.net/gst/gst.aspx

·         Preliminary video: https://www.youtube.com/watch?v=-Vp7JzHCYp0&feature=youtu.be

·         Detailed demo link: https://youtu.be/d-KD3mp1jic

Detailed demo link: https://youtu.be/d-KD3mp1jic

The demo was attended by a number of CFOs from industry.

Sample problems that a datawarehouse can solve:

·         Data audit and data profiling before GST is filed-samples given in the demo.

·         Single source of truth for all online GST reports. For example if GSTR1B2B is updated then HSN summary and document summary should also be updated.

Automation of spreadsheets: client reference(a major exporter based in Gurgaon). We are now able to automate the spreadsheets due to our technology of data compression and sql reduction at lower costs. The end client could send the sample profit and loss and balance sheets to be automated along with the source system details.

Attached is the blog link for the same:

http://mnnbi.net/spreadsheet/spreadsheet.aspx

Blog: http://mndatasolutionsindia.blogspot.com/2018/07/spreadsheetconsolidationsample.html

Capturing Data Entry Errors for Audit Purposes

A number of times the end client types in wrong data into the source ERP system thereby resulting in wrong outputs and results. Junk inputs imply junk outputs.  The ETL team would recommend an auditable output from Turbodata to be used as part of the reporting purposes.  Wrong data inputs can impact the end client in one or more of the following ways:

•        Wrong tax filing specifically in online scenario.
•         Wrong business picture
•         Wrong predictive analytics.

As per the Toyota ProductionSystem, bad inputs should not be processed further as it adds to the final costs.

The ETL team(my firm) has found the following errors with regards to the data entry inputs specifically with Tally ERP 9.0.  

·         Stock input has been in one godown but stock outward movement has been from other godowns:

·         Missing purchase or sales order entries resulting in negative stocks at given points in time. One cannot have negative stock balances at any point in time.

Other data input errors that we have commonly seen are as follows:

•       Duplicate payment entries
•      Duplicate sales entries
•        Receipt note entries but no purchase invoice entries
•         Payments not having the required bill reference numbers.

How to resolve the errors:

·         In an object oriented program it is difficult to catch the errors on a real time basis. The ETL team recommends using the relational databases for catching the errors. The real time extraction module for Turbodata should be used for the same.

·         Transferring the data onto the third normal database is recommended. This helps catch data duplicity based on the composite keys.

For example if an end client has made the same amount payment for a given voucher on a given fiscal date, then the same should come as part of the discrepancy report. It is possible that the end client could be correct. There is also a possibility that the payment entries have been made by 2 different resources. Further handling of the given situation is as follows:

·         If the end client desires to catch the following error then the username by which the data entries have been done shall not be added to the composite key. In such a scenario there is a discrepancy between the Turbodata ledger balance output and the Tally report. The end client to approve the discrepant entry before the data is input into the system for auditing purposes.

Using perpetual valuations for ledger and inventory instead of periodic valuations. For example if an end client relies on periodic valuations for ledger balances then a duplicate payment entry then the periodic balances at the end of the fiscal month are difficult to catch. For example if an end client has a duplicate entry of Rs. 100k(One hundred thousand  only) over a balance of say Rs. 15000k(One fifty million only).

However using the perpetual system it is easy to catch the data entry errors.

Matching the consolidated trial balances and closing stock balances at the database level with the on fly calculations at the software level.

A small story for the end user: as Yuval Harari is Sapians says that mankind is primarily driven by myths. Hence many a managers are driven by myths regarding software or the consulting companies having the right audit numbers(with the managers inputting junk numbers).

A small story from one of my favourite books(Raag Darbari by Srilal Shukla) could best illustrate the point.

The protagonist Ranganath had gone from the city to visit his relative, an aunt’s husband , in the village. During the course of the village fair, it was suggested that the group goes and sees the village temple for the local goddess. At the temple Ranganath found that the statue instead of been of a goddess was of a soldier( for a goddess he was looking for two lumps  in front and two lumps in the back). The priest asked for donations for the goddess. To this request Ranganath refused saying that the statue was not of a goddess but of a man. There was an ensuing scuffle between the villagers and Ranganath. Ranganath was eventually rescued by his cousin. On going out and meeting other people, the cousin mentioned the following:

"My cousin has come from the city and is very well read. That is why he talks like a fool."
The author has always associated himself with Ranganath.

Apoorv Chaturvedi

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

Website: https://mn-business-intelligence-india.business.site/

Blogspot: http://mndatasolutionsindia.blogspot.in/

Phone:+91-8802466356