Hire an accounting analyst for gross profit calculations

 Hire accounting analysts for calculating gross profit as per your requirements

Profile: accountants and computer engineers available all over India with Turbodata training to help CFOs with the following problem

Problem statements:

For calculating the cost fo goods sold we use the formula: opening stock value+purchase value-closing stock value.

Thereafter we calculate the gross profit.

Use the accounting analysts to help and calculate the cogs at perpetual  level across unlimited companies, 

unlimited branches and across the complete historical time frame.

Issues with the ERP valuations:

a.) The purchase value is normally calculated with ledger parent 'purchase accounts', excluding order entries. However in the schedule 3 balance 

sheet entries the CFOs and CEOs like to exclude the following entries also:

i.) Delivery notes and receipt notes

ii.) Stock journal entries(this calculation shall also include for dales entries)

iii.) Branch transfer details(a firm could have multiple companies or multiple branches. Stock transfer between them needs to be excluded)

iv.) Purchase of capital goods.

v.) Rate differences(this is included in ledger masters)

vi.) If the company is using cost centers with branches then the solution needs to be configured accordingly.

Similar calculations are to be done for closing stock value with the sales accounts.

Why use our accounting analysts:

a.) The accounting analysts shall come prepared with the software module(with and without database) on site

b.) The reports shall be automated and tamper proof(a critical requirement for many CFOs)

c.) The product will have features row level and filter level contexts and ledger and item heirarchy flattening

d.) The CFO and CEO can dictate how the solution is to be configured.

what else would the analyst do?

a.) Based on the custom calculations the analyst shall help adjust the MRP entries in Tally/other ERP.

b.) Help automate the distribution of MRP details to various internal and external stakeholders in the ERP

Why can't a normal accountant do this job?

a.) Normal accountants are trained only in Excel

b.) Large data limitations

c.) Routine tasks for the accountant

d.) Reliance of the accountants only on the numebrs given in the ERP

e.) Profitability is not a priority for the accountant

Number of companies that have implemented the solution: more than 15

Automation of profit and loss statements: https://www.youtube.com/watch?v=O8_3PMVBUv8

Usage of vlookup and sumproduct in Excel: https://www.youtube.com/watch?v=hpNs1uUYbTg

Sumif(Excel to DAX): https://www.youtube.com/watch?v=_pndahQvpvk

Row level and filter level contexts: https://www.youtube.com/watch?v=_3XlMxB_R0U

Lookup(DAX): https://www.youtube.com/watch?v=eIT4YAITIIg

Time intelligence: https://www.youtube.com/watch?v=6bowkQ4dcxk

Blogspot link: 

Turbodata: https://mndatasolutionsindia.blogspot.com/2021/10/turbofast-powerbi-reports-from-tally.html

Running  sum problem for inventory valuations: https://mndatasolutionsindia.blogspot.com/2021/01/resolving-running-sum-problem-using-dax.html

Weighted average valuation: https://mndatasolutionsindia.blogspot.com/2020/08/weighted-average-valuation-from-tally.html

Batch wise analysis: https://mndatasolutionsindia.blogspot.com/2020/01/turbo-analytics-batch-wise-bill-wise.html

Name: Apoorv chaturvedi

Phone:+91-8802466356

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

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