Best in class reporting solutions from ERPs: 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: