Contact details of blog writer:
Name: Apoorv Chaturvedi
Email: apoorv@mnnbi.com
Phone: +91-8802466356
website: www.mnnbi.com
What is the problem we are trying to solve?
·
Identify the end clients having delinquent payment
history using perpetual valuations(not simply periodic valuations). Develop a
comprehensive solution for ageing analysis for the ledger for yearly, quarterly,
monthly , weekly and daily analysis. Develop a comprehensive historical trend
analysis for the ledger ageing analysis(accounts receivable ageing and accounts
payable ageing analysis)
·
Consolidate the findings along with inventory
for those cases wherein sales of items is involved.
·
Develop the profile of customers. This profile
shall be used to give selective discounts and pricing to be used for the
secondary sales.
o
Giving discounts based on credit and sales history
profiles
o
Giving access to stock data based on the credit
profiles of the customers.
·
Integrating the given data with the website of
the end client to sell slow moving items
·
Integrating the data with secondary sales
websites such as flipkart, indiamart and tradeindia for selling excess stock.
The exact process flow is as under:
Data cleansing/Data auditing and Data profiling:
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.
The author has always associated himself with Ranganath.
Sample project for data cleansing/data auditing:
Source system: multiple installation of Tally ERP 9.1.
Problem : The end client desired to have a custom installation of Turbodata based on the unique requirements of its business. The product shall be used for designing a custom web interface for customer interaction. The key tenets of the solution that differed from the standard deployment of turbodata were as follows:
· Standard price list instead of weighted average or FIFO pricelist.
· Closing stock value was to be calculated at a godown and item level.
· The solution was to work across multiple locations seamlessly with maximum RAM usage been 1 GB for both initial and incremental data loads.
· Custom masters extraction for item, stock group, category .
· GST classification to be extracted for the end client.
Time duration of the project: 2 weeks.
Approach of the ETL team:
· Choosing the appropriate set of attributes to be loaded based on the modular approach. That is the required fields to be loaded for ledger and inventory were chosen.
· Custom extraction for the tables: The process of normalization helped in the same since the attribute is to be loaded only once.
· Testing of initial and incremental data loads in terms of time and load on the system. The incremental data load process helped at reducing the time of data load.
· Data cleansing: special characters were removed from the item names. Also separation of the numeric values from the character fields
· Data consolidation: multiple types of voucher types were loaded onto the datawarehouse.
Project has been done successfully. Hereafter the end client shall go for a MVC interface over the datawarehouse for reporting and customer interaction purposes.
Ledger analysis:
Problem statement: a number of firms use periodic statement for ledger analysis(monthly, quarterly and yearly). In such a scenario these firms loose the day by day and transaction by transaction history of ledger balances. This information is required for the ageing analysis, in depth accounts receivable analysis per ledger. As an example, consider the following:
The above snap shot indicates the ledger balance on any fiscal date by partyledgername and ledger name(the group name is a roll up). From the daily ledger balance, the end client should be able to extract the trial balance, balance sheet and even profit and loss statements.
As an example consider the following snap shot:
Because of keeping the ledger balance history, the end client is able to find the cash balance as of the given fiscal date. Thereafter it has been able to capture the cash balances on a monthly, yearly, quarterly basis as given below:
Monthly report:
Daily Report:
Pre requisites for achieving the same:
The historical ledger balances need to be calculated and the closing ledger balance on the current fiscal date shall need to be matched with the ledger balance on the last day of the ledger balance history table as given below:
Alternatively the debit and credit balances need to be matched as given below:
The process replicates the ledger balancing related with bitcoins.
For achieving the same the end client needs to do the following:
The ETL team would be also able to offer Business Intelligence and predictive analytics services along with ledger analytics.
Ledger analytics is also related with GST filing.
Further case studies for consolidation of data can be seen from the following link.
Ledger analytics is also related with GST filing.
Further case studies for consolidation of data can be seen from the following link.
Predictive analytics for Ledger Analytics:
Purpose of the development of the product: The Turbodata team intends to reduce the costs of the analytic solutions by creating a single platform for ETL, Reporting, Reporting development and predictive analytics. The team also intends to provide the best in class analytics on the same machine on which the ERP is running or with the addition of minimum hardware requirements for the end client. This has been done to develop scalable systems that can be deployed over a large number of customers(with limited budgets) with ease(deployment, delivery and usage) and convenience(maintenance).
The end goal is to increase derisking and predictability for the end clients at lower costs.
Methodology for achieving the required ends for the end client:
· Turbodata adopted the Inmon methodology for datawarehouse development so that multiple data sources could be added onto the same datawarehouse. That is the change from one data source to another was done with ease. More details on the attached web page link: http://mnnbi.com/mnnbi_tallydataconsolidation.html
o The benefits of the normalization of data were as follows:
§ The incremental data load took minimum time and had minimum impact on the source system. The ETL team was able to commit the incremental data load to a maximum of 2GB RAM from multiple source systems. The source systems did not hang with the incremental data load working.
§ Error handling was done with ease at staging layer.
§ Massive data compression took place due to reduced data redundancies.
§ The business logic was coded between staging and the ODS layers thereby reducing the length of the final sql code.
The attached video shows a more detailed description of the benefits listed above:
The joins were reduced in the data mart layer(over which a reporting layer was built).
The ETL team was able to develop extremely complex reports using the datawarehouse as in the attached sample:
Due to the data compression for most projects the ETL team are able to bring the data within 1 GB. Hence the desktop version of Microsoft Power BI could be used free of cost for the end client.
Reducing the cost of predictive analytics solutions
Most of the end clients use high end predictive tools over the datawarehouse/ over the direct data extract from various source databases. With large datasets predictive analytics using in memory solutions entails high usage of RAM. The ETL team has gone around this issue in the following manner:
o A seamless environment was created for ETL, reporting and thereafter predictive analytics on SQL/C# and .Net. The reasons for the same are attached herewith:
§ Maintenance becomes easier since there is a single platform for all aspects.
§ The cost comes down since the resources to be used for ETL can also be used for predictive analytics
§ Error handling becomes very easy since errors can be captured before in the
Hypothesis testing
Based on the hypothesis testing, the ETL team developed ARIMA analysis and Market Basket analysis in SQL using seamless integrated set of stored procedures. That is the ARIMA analysis flowed from the datawarehouse A,B,C categorization. The ETL team thus reduced the requirement for high end R and Python developers to code over the datawarehouse thereby presenting a seamless solution to the end client on a 8GB RAM machine.
Benefits to the end client:
· The end client gets immediate and confirmed peace of mind and satisfaction through immediate deployment of predictive and forecasting analytics modules.
· No additional hardware/software requirements need to be taken
· The costs are way lower for the end client.
· Large scale deployment is possible with the given set of solutions.
Please check the attached video for the same:
A more detailed video is attached herewith:
Example of predictive analytics with Turbodata: Example of predictive analytics-Turbodata
Epilogue
The ETL team has been inspired by the following management books:
· ‘Profit Beyond Measure’ by Thomas Johnson and Anders Brohms
· McKinsey Mind by Ethan Rasiel and Paul Friga.
· Blue Ocean Strategy by W. Chan Kim and Renee Mauborgne
· Better India, Better World by Narayana Murthy
Integration with Indiamart/flipkart/tradeindia:
The ETL team is preparing the entire solution at the database level. Thereafter integration with various secondary sales web partners such as flipkart and indiamart can be done through the following mechanisms:
- XML exports
- Direct data transfer
