Get accounting analysts to develop custom data entry forms over Turbodata using python
Need : Sundry debtors mapping at the time of entry into Tally and at the time of sales order processing.
Problem statement: many a times in various ERP systems various parties, that do not have a good payment history, are entered.
Sales to these parties can result in cash losses.
The owners desire protection in terms of the quality of the parties that are entered into the system.
Why is an accounting analyst required?
a.) Custom ledger and item hierarchies for the end clients
b.) Complex Tally implementations
c.) Complex legacy collection issues
d.) Design of a custom data analysis and data capture system because there is no workflow system for most other ERPs
e.) Management to work closely with the accounting analyst for handling difficult accounts.
The process flow is explained below:
Solution(proposed to CEOs/CFOs)
a.) A thorough analysis of the client Tally ERP data across all the companies using the Turbodata tool(Tally to Powerbi). This tool does not require the installation of any database. The extract shall involve ledger hierarchy flattening, item hierarchy flattening for trial balance, receivable and payable and ledger movement analysis. A sample of the ledger hierarchy flattening is attached herewith:
https://drive.google.com/file/d/15Y1KjYLDJ3CFEeXS6B0pQSFG2HBg9qWj/view?usp=sharing
A similar exercise shall be done for item hierarchy flattening.
Reason for doing the analysis is the following:
- Tally allows dynamic hierarchies. Understanding each level of hierarchy is critical for understanding the organizational dynamics.
- The ledger and item hierarchies in each company can be different
- We need to understand the balance sheets and bill payable and receivable history of the companies very well. We need to identify if the pain points of various customers.
b.) Once the proof of concept and analysis has been done then the data is to be input into the Turbodata SQL Server datawarehouse. The SQL Server database shall help with the following:
2. Maintaining historical data
3. Customizing the inventory valuations
4. Speeding up the data access using forms
5. setting up secure access across various levels of hierarchy
6. Generating customized python forms for data capture and workflow approval.
Data capture process
a.) A workflow for parties authentication(various hierarchies). In addition to the software systems we propose an online verification system using Turbodata SQL Server along with the python data entry forms. The verification will take place through the Turbodata entry forms at various levels of hierarchy. A sample data entry form is given herewith.
b.) A thorough credit check for new parties: attached bank statements, financial statements, details of credit bureaus
We shall look at quick ratio, Total turnover of the party for the prior fiscal year, profitability, cash profitability ratios.
c.) Possibility for the firm hierarchy to block the party in case the credit history is not up to the mark. In such a case no entry into the Tally ledger master to take place.
Additionally the management can specify the following:
i.) Decide the hierarchy level from the group flattening at which the given party shall be handled.
For example Tally allows flexible hierarchies for a given party. The management can decide that a party with a large order values
will be handled by ledger level4, a lower order value will be handled by ledger level_5. In such a case the key stakeholders for the collection process
shall be defined.
c.) Solution shall be deployed on client server. The custom hierarchy of the client to be taken into analysis process.
d.) Every party to have a set of stakeholders for ease of collections: this shall be ensured by having the mapping of each party at a particular ledger level
during the data entry process. Additionally the following steps shall be done to ensure that collections take place in time:
Deploy checklists for the sundry debtors based on balance sheet analysis from google
Decide the payment terms for the party.
Link the payment terms with the sales order entry process.
Block the sales invoice entry without the sales order entry. The credit check for the party to take place at the sales order entry level.
The above is the ledger entry form
a.) It matches with the ledger entry form with Tally.
b.) Additional fields and control systems shall be added based on the client requirements
Data validation at the sales order entry process entails 2(two) levels of credit checks:
- Ledger: at the ledger level
- Item: this shall entail checking the inventory valuations at the batch, godown level for the required client. This has been done because many end clients require only specific movement types to be incorporated in the inventory valuation, remove the errors due to company splitting and get a more accurate closing stock valuation at the batch godown level
For example in the above form, for a party for which sales order has come the following 6 KPIs have been checked base don the Turbodata datawarehouse.
a.) Closing ledger balance of the party
b.) Total receivable as of date for the party. This entry comes from Billdetails and should match with the closing balance of the party.
c.) Receivable more than 90 days: This indicates the total receivable with due date more than 90 days
d.) Cashinflow: this metric indicates the total cash inflow from the party
e.) Sales order pending value : this is the total sales order pending value for the party as of this date. This shall include the pre closed orders also,
Based on the same above inputs the hierarchy of the company can approve or disapprove of the order.
A similar form shall be created for the items(batch, godown wise analysis) to ensure that the required profitability level is maintained.
Name: Apoorv Chaturvedi
Phone:+91-8802466356
email: support@turbodatatool.com;support@mndatasolutions.com
