A discussion on current ratio for schedule 3 and schedule 6 returns

Problem statement: here we look at the liquidity ratios and how the CFO and CEO needs an accurate guidance regarding the same. We shall also look at the variations that we have had in our projects regarding the calculation of various liquidity ratios. The sample case is for service industry. For other industries inventory needs ot be taken into account.

2.) We shall also look at how to calculate the liquidity ratios automatically using the product.

 Current ratios are normally calculated using the following formulae: current assets/current liabilities. For the same we use the trial balance extract using the product for unlimited companies.

Trial balance extract shall be through the extract 'Ledger Balance'[matching with the Tally object name]. The added methods for the extract are for ledger heirarchy flattening.

Simple current ratio is calculated by using the level2 in our product module(after ledger flattening) for current  assets and current liabilities

1.) Variation 1 by CFOs and CEOs: Many a times the current assets have a large portion of sundry debtors. These sundry debtors could have a large account of receivables.For accurate calculation these CFOs and CEOs desires that based debts be taken out.

For the same one needs to go to level 3 and other entries at level 4 and beyond need to be taken out.

Method of finding the bad debts:(Bill payable and bill receivable and ledger flattening)

2.) Handling wrong group entries for the current assets and current liabilities: many a times the group heirarchy in the source system or the Excel report needs to be changed. The same can be handled through ledger flattening.

3.) Cash ratio: for calculating the cash ratios we look at the cash balances specifically with the ledger group of 'current assets' The cash balances shall include cash-in-hand and all the bank accounts(the bank od accounts are part of the current liability).

Fitting the current assets in the schedule 6 balance sheet:

Inventories is closing stock-opening stock

Trade receivables shall correspond to Sundry debtors(level 3)

Cash and cash equivalents shall correspond to cash-in-hand and bank accounts level 3

Short term loas and advances shall correspond to Loans and Advances(level 2)

In this way  we have a better understanding of current ratio and filling up the sschedule 6 portion of the balance sheet.

Name: Apoorv Chaturvedi

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

Phone:+91-8802466356

Cost centre analysis add on-Tally

 Problem statement: Many companies require cost centre wise analysis(specifically with Tally). Their needs are as follows:

1. Profit analysis per cost centre

                2. Understanding the billing implications for various projects. Material could have been             purchased but billing would not have been done.

Relevant industries: any project related industries for construction.

In order to meet the need for cost center analysis we have a software add on for revenue analysis.

For quick querying we have developed the same over SQL Server. The clients can  also develop workflows over the cost center module.

Why is the SQL Server module required?

a.) The logic as per the required revenue accounts has been developed in the database. We extensively use ledger flattening for the same.\

b.) In order to make the system more secure and customizable we have used python code.

c.) The client can specify the screen to be shown based on ledger and item hierarchy flattening.

Why is this module better than the Tally system:

a.) Customized revenue models have been developed

b.) Better control flows can be specified using item hierarchy and ledger hierarchy flattening. That is a CEO can contact his subordinates with ease using the module.

Explanation of the module:

 In the sample screen below we make a preliminary snapshot of a sample cost center module developed for a company.

a.) Company name is a drop down box: we can work with unlimited companies

b.) Cost center: these are the associated cost centers for the company that has been selected in the combo box above.

Hereafter we explain each of the list boxes below:

1.) Sales: this gives the total sales entries as per the sales accounts for the given cost center.

2.) Purchase: this portion can be further customized base don the ledger flattening by the end client. Various sub groups can be added. this indicates the total purchase(across all voucher types) for the purchase accounts.

(Note: delivery notes, receipt notes, purchase order and sales order have been excluded. The revenue accounts report from Tally has been used to generate the given numbers).

The sales and purchase entries include both ledger and inventory movements(so the journal entries for the sales shall also be counted)

Labour, Transport and other expenses have been extracted from the ledger flattening.

The net profit per project is sales-purchase-indirectexpense-direct expense for a project per cost center.

Other customizations that are possible: 

For example automated emailing and whatsapp feature across various ledger level hierarchies are also possible(the same is been done for a client given below):

Inventory movement analysis: In order to make the module more comprehensive and showcase more details for the project movement we also have added sections to showcase the inventory movement for sales, purchase. In the module the following actions can also be taken:

a.) Conversion of units can be done

b.) Link with bill receivable can be done

c.) Workflows based on project completion can be done.

For deployment of the module please contact:

Name: Apoorv Chaturvedi

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

Phone:+91-8802466356

Estimated cost: (To be decided on case by case basis)

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

Youtube channel: https://www.youtube.com/@mndatasolutions2042

 

Workflows and secured custom reports access(unlimited users)-Turbodata

  Workflows and secured custom reports access(unlimited users)-Turbodata

 

 Who are the stakeholders with whom you need to share data?

Are you a customer who is on Tally server?

Then let us see who could be the possible users of your data?

For most firms the user hierarchy details are in ledger hierarchy.

Say in the internal hierarchy there are 25 people  each across various levels of hierarchy.

say the company has 50 sundry debtors and 20 sundry creditors. SO the total people involved for sharing information securely are atleast 95.

Methods of contact by whatsapp, email are time consuming.

a.) Internal users: The number of internal users could be large. Various reports with row level security could need to be shared with various end users.

b.) External users: primarily under the primary group sundry debtors and sundry creditors. Currently They are informed by whatsapp or email intimations. However

Additional details that can be shared are as possible:

i.) Pending sales orders

ii.) Pending purchase orders

iii.) Pending bills statements: https://mndatasolutionsindia.blogspot.com/2023/11/issue-problem-statement-in-many.html

iv.) Intimation regarding new receipts

v.) Sales order input forms.

vi.) Open stock position for reordering: https://mndatasolutionsindia.blogspot.com/2021/01/sell-more-of-your-inventory-to-existing.html

Additional requirements for internal users:

a.) Workflow approval requirements for sales orders and purchase orders within the internal heirarchy:

Current solution:

a.) Most companies use third party apps for getting one or more tasks done: issues regarding data security and data confidentiality are open in many cases.

b.) Tally server companies also use third party apps

Our proposed solution:

a.) If on server in Tally then use the same to give access to multiple end users without paying additionally for each user by using own server.

b.) Design custom workflows for approval process

c.) Get custom reports in a very quick and customized fashion without paying any licensee fees

Process steps for the same:

a.) Use Turbodata Powerbi module to understand the Tally data using ledger hierarchy and item hierarchy flattening: https://mndatasolutionsindia.blogspot.com/2021/10/turbofast-powerbi-reports-from-tally.html

b.) Load the data into SQL server. For faster incremental data load indicate masters entries for data load. Optimize the reports in SQL server: https://www.youtube.com/watch?v=m8Lg6Og9bcU,https://www.youtube.com/watch?v=PI5DmU5IUSo,https://www.youtube.com/watch?v=B-odSuZkYhU&list=PLZPWImFqOkLsGgXPGf82nHPVaaZtJx4BL&index=1

using views and functions : https://www.youtube.com/watch?v=B-odSuZkYhU&list=PLZPWImFqOkLsGgXPGf82nHPVaaZtJx4BL

c.) Set up row level security at the database level:  

d.) Use Turbodata data capture forms to give custom reports at each level of stakeholders.

Name: Apoorv Chaturvedi

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

Phone:+91-8802466356

Hire sales analyst-Turbodata

 Sales Order Analyst:

Get specialized sales order analysts for your firm to handle multiple issues with regards  to sales orders and sales analysis

Profile: Accounting knowledge, SQL, python, Preferably Tally

Pre requisites; Turbodata(Powerbi module, SQL Server datawarehouse, python data configuration module)

 

Why is a sales analyst required for your firm?

·        Many firms are making sales over a large number of skus(stock keeping units) over a number of customers

·        The orders are coming from multiple sources and across multiple items

The owner requires the following:

·        Complete visibility of the profit of the transactions as per his logic across item hierarchy and ledger hierarchy(https://mndatasolutionsindia.blogspot.com/2020/08/weighted-average-valuation-from-tally.html)

 

·        Control over the process flow by which the orders are approved before the data entry into the Tally system(https://mndatasolutionsindia.blogspot.com/2020/01/turbo-analytics-batch-wise-bill-wise.html)

 

·        Look into the possibility of automation of data entry into Tally after the approval process.

·        Desire to sell the existing stock over the customers: https://mndatasolutionsindia.blogspot.com/2021/01/sell-more-of-your-inventory-to-existing.html

 

·        There is a need to check the credit worthiness of the parties. The parties credit worthiness can be checked based on the following parameters:

o   Prior cash inflow from the prospect

o   Receivable status of the client

o   Pending open sales order status

Based on the above parameters the sales analyst shall help make workflows to decide on the following parameters:

·        Whether to accept the order or not. Based on the analysis the sales analyst could recommend to accept or reject the order

·        Decide on the discount and the pricing of the product. An appropriate workflow based on the hierarchy could need to be decided for the same.

·        Payment terms: the payment terms can also be adjusted

·        Decision on factoring agencies: the sales analyst will help with the factoring agencies to discount the bills in case the parties with the credit terms are not of the best quality

·        Collections process: the analyst can help with designing custom email and whatsapp solutions or implementation of external solutions like Biz analyst for speedier collections process

Analysis for the parties shall be done based on each sales order.

 

Analysis of the Item profitability: many customers desire custom logic for their item profitability for the following scenarios(https://mndatasolutionsindia.blogspot.com/2018/09/fifo-logic-turbodata.html):

a.)   Batch wise items(https://mndatasolutionsindia.blogspot.com/2018/03/developing-optimization-module-for.html)

b.)  Non batch wise items(https://mndatasolutionsindia.blogspot.com/2018/01/reduce-retail-and-manufacturing.html)

c.)   Items both in batch and non-batch

d.)  Incorporation of the landing costs for the end client

e.)   Look into designing sales combos for the items for the end client(https://mndatasolutionsindia.blogspot.com/2019/08/market-basket-analysis-turbodata.html)

 

Issues involving splitting of companies, incorporation of the movement types need to be involved for the same.

 

The analyst shall help the owner arrive and report on the items costing based on Tally, costing based on the custom logic of the end client. Thereafter the anticipated profitability of the orders can be gauged.

 

Name: Apoorv Chaturvedi

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

Phone:+91-8802466356

Youtube channel:

https://www.youtube.com/watch?v=f7GnueEDhVo&list=PLZPWImFqOkLtJk2MoUPjb6ap_wFbJN53E

Industry analytics module: https://www.youtube.com/watch?v=xGyQBZoEAaI&list=PLZPWImFqOkLtequtQueJLPdo4ngnZFUbx

https://www.youtube.com/watch?v=O8_3PMVBUv8

 Collections issue?

Problem statement: In many companies the data for the receipts has not been entered bill wise. There is a need to adjust the ledger entries against the bill entries.

The system has been designed to work across multiple companies. 

with various outstanding bills in the Tally system. There is direct integration with Tally. The data capture can also be done using Turbodata. The following method can be used for resolving the same:

a.) Check for a given  party whether the bill entries are pending or not. This can be done by doing the comparison between 2 reports from Turbodata

Trial balance analysis with ledger flattening(Ledger Balance)

Payable and receivable snapshot with ledger flattening(Payableandreceivable_snapshot). 

The ledger  hierarchies shall help verify of the bills belong to the correct group parent.

If there is a difference between the closing ledger balance and total from the bill details then bill adjustment needs  to be done.

b.) Look at the complete cash flow statement(debit entries)  from the Ledgerview(here 'isvoid' and 'iscancelled' vouchers are to be excluded). 

The filter conditions include the following:

At the ledger_3 filter use 'Cash' Bank statement'. 

This indicates that there has been cash outflow movement for the given party(partyledgername would need to be chosen). We would consider the debit entries of cash and bank  for the same.

c.) Select those entries in part b.) that are not part of the voucherledgerbill movements. For the same the entire voucher ledger bill history for the set of firms shall need to be extracted.

The extract has been given in the object voucherledgerbill. The comparison shall be done using masterid and companyname.

After the above analysis we shall get the following 2 set of entries:

i.) Debit cash and bank ledger entries not adjusted against any bill(Ledgerview)

ii.) Debit  cash and bank ledger entries not associated with any bill(Voucherledgerbill)

Manual interface: Based on the unadjusted entries for the parties, the client to indicate which ledger movements are for the bill and which ledger movements are not associated with any bill. The Turbodata interface shall help with the same. The client team will help with the same.

The ledger payments not adjusted against the bills shall then be extracted from the ledger movement.

Usage of Turbodata(SQL Server):Hereafter we insert the data into Turbodata SQL Server module. The bill entries in the database are adjusted based on the ledger movement and the bill due dates. We look at partyledgername, bill amount, bill due dates for automated adjustment

Usage of the python data capture script to confirm the adjustment for each bill: Turbodata input forms shall then be used to adjust the amount for each bill. On approval by the required authorities the data shall be entered into Turbodata and Tally friendly Excel module

Note: the logic for post dated checks and on accounts balances shall need to be indicated by the end client.

The adjustment against each bill shall need to be done at the Turbodata data capture level.

For basic extraction please contact the following

Name: Apoorv Chaturvedi

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

Phone:+91-8802466356

Accounting analysts for improving collections and margins with ERPs

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:

1. Incremental data load

                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

Turbofast Powerbi Reports from Tally

 

Turbofast Reports: Powerbi

Consolidate and minimize your spreadsheets

M&N Business Intelligence India LLP(www.mnnbi.com)  is a leading analytics provider providing faster, easier and more convenient reporting from various ERPs. We have worked with clients like Medanta, PVR Cinemas, Kurlon etc for helping them optimize their operations through management reports. We help clients reduce their working capital requirements by helping design processes.

In most of the firms, accountants and managers are developing a large number of Excel spreadsheets manually for the top management. This is time consuming, manual and expensive. The large number of spreadsheets are due to the following:

1.           Complexity of information required
2.           Heavy load on Tally during processing large amounts of data

At the end of the process the management has to work with large number of complex spreadsheets.

For getting immediate benefit, we have made a product for   end clients

.Please fill out the form to enable us to get in touch with yourself for deployment:

https://forms.gle/dZGZDnqADxZquJrL9

Product download details: https://drive.google.com/drive/folders/13TOzjHxvTU8mEYuELYWmYj8MyLk-5V5f?usp=sharing

The solution is integrated with Tally and the solution is secure, easy, fast and very convenient.  Please contact  us for deploying the same on your machine.

Benefits:

We can help minimize the number of spreadsheets for the top management by the following:

·         Developing complex reports from Tally machine using Microsoft technology(DAX).  This is done with minimal deployment requirements. We have made the base product with a free version of Powerbi. 

·         The complex reports run atleast 50% faster than in Tally using Powerbi.We also provide data audit features with our product.

Our product gives reports with lower refresh and reload times than Tally on your machine or on secure Azure server. This shall help the client with the following:

·         Give managers required information in lower number of pages in an automated manner.

·         Extensive graphical representation can be done with ease and convenience.

        Advanced predictive analysis modules are also possible.

S      Sample for automation of the spreadsheets by our developer: Automation of spreadsheets sample 

            Next steps:

·        

·         The cost of the module is Rs. 7000/-(seven thousand only)+taxes as applicable. We would need the Tally serial number for the permanent license.

We shall be thankful to understand your requirements in depth  for reporting and Excel automation.

 Sample reports:

    1.) ABC Ledger analysis: This gives an immediate view of the important ledgers whose outstandings are due.

Please capture the important ledgers for receivable purposes from this report.

Application: This report is for collections teams

2.) Cost center wise sales report: here we look at the complete visibility starting from sales order, to sales invoice(including delivery notes) to billing outstandings. Use this report for the following:

a.) Understanding the sales order outstandings

b.) Look at the pending receipts

This report has been developed godown wise.

Application: use this report for collections team

3.)  A critical problem for the end users is that they do not have access to batch wise and godown wise inventory. The valuations are required for calculating the gross profit and for calculating the value of work in progress inventory. This report has been developed as a consolidated statement for stock summary, closing stock report.

Application: use this report for production planning team

5.) Payable and receivable snapshot has been developed for giving a one shot look into payments are receivables basis.

6.) Monthly, yearly and quarterly gross profit views for for those managers who want to automate the profit and loss statements.

6.) Stock item batch analysis: this view gives complete visibility to the inventory planning managers for batch wise and godown wise analysis

7.) Receivable and payable turnover: it helps analyze the credit quality of various parties.

8.) Daybook view: it gives views by voucher, voucher inventory and voucher ledger. One can make most reports from these 3 views.

Other views:  

·  

 

Price: Rs. 5000/-(Five thousand only)+GST

(This includes one simple spreadsheet automation)

Payment details: 

Beneficiary Name: M&N Business Intelligence India LLP

Bank Name: Axis Bank

Branch: DLF, Gurgaon[HR], Gurgaon 122002

IFSC Code: UTIB0000131

A/c Type: CHECKING

Account Number: 910020041330740

Inputs required: Tally serial number

 

 How to install Powerbi:

Download Powerbi from the following link:

https://www.microsoft.com/en-us/download/details.aspx?id=58494

 

Contact details:

Apoorv Chaturvedi

Phone: 8802466356

email: apoorv@mnnbi.com;support@mndatasolutions.com