Using analytics for optimizing hospital operations

Contact details of blog writer:

Name: Apoorv Chaturvedi

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

Phone: +91-8802466356

website:https://mn-business-intelligence-india.business.site/

Blogs:  http://mndatasolutionsindia.blogspot.in/

FOR FREE MICROSOFT POWERBI DASHBOARDS: please do one of the following:Email: apoorv@mnnbi.com or go to  landing  page http://user1333631.sites.myregisteredsite.com/id62.html

The given blog has been developed from case studies of 2(two) of three large hospitals based out of India and 2 hospitals based out of United States.

Problem statements:

• ·        Help the end client meet the SLA(Service Level agreement) by completing the nightly process in time.
•                  Help the end client capture the data entry errors for the following:
  o   Ledger
  o   Inventory
  ·        Help the end client optimize its inventory in terms of Target Share of Business and Actual share of business.
  ·        Help the end client manage its payables.
  ·        Help the end client find the profitability of the following:
  o   Packages
  o   Department consumption
  ·        Forecast the following:
  o   Bed usage
  o   Medicine usage
  o   Patient cash inflow usage
  o   Patient insurance premium payment usage
  ·        File the taxes automatically without any errors

Solution:

The ETL team devised the soluton based on the following methodology:

Crashing the query execution times:

Problem: the nightly process of the hospital chain was taking time because of which the SLA(performance parameter) of the CTO of the firm was not been met. The ETL team was advised the bottleneck sql by the end client manager.

Methodology: the end client had developed the code using cursor logic. The ETL team developed the code using set based logic in order to optimize the usage of RAM. The given process has the following benefits for the end client:

·         Optimum usage of RAM

·         Lower implementation times

·         Error logging and error handling

·         Incremental data loads after the initial data loads.

·         Audit of the transformation process for the end client.

Methodology:

The ETL team adopted the Inmon methodology for resolving the same. The cursor logic was reverse engineered using the set based system. The following were the methodologies adopted by the ETL team.

·         Data normalization: error logging, data audit, incremental data load and optimum usage of RAM.

·         Data transformation: converting the cursor logic to set based logic

·         Data cleansing: obtained from the cursor logic

Final result:

·         The output of the set based system was matching with the cursor logic output.

·         The execution time was reduced by more than 80(eighty) percent.

Suggested next steps for the prospect:

·         Send the bottleneck sql to the ETL team.

Data consolidation

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.

      Data Consolidation Example

       Data Auditing

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.

Inventory optimization

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:

• email apoorv@mnnbi.com

• Or fill up the contact form on the website  http://mnnbi.com/.

Ledger Analytics

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:

• Data capture 
• Data cleansing
• Data consolidation
• Data transformation

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.

Predictive analytics for hospital after Data Consolidation, Data Compression and SQL Reduction

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:

https://www.youtube.com/watch?v=k9Oe9EDRfTM&feature=youtu.be

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:

https://www.youtube.com/watch?v=1ySNrgI4Na0&feature=youtu.be

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:

https://www.youtube.com/watch?v=PQfsfWLkaP8&feature=youtu.be

A more detailed video is attached herewith:

https://www.youtube.com/watch?v=7DoZDBay-bQ

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

Inventory optimization for a large hospital

Problem: for the given hospital, the end client desires to optimize its inventory. The end client has very high level of closing stock numbers and wanted to optimize the same using the Turbodata services.

Approach: Turbodata team had worked under a top 4 consulting firm. The end client had been given target Share of Business at the generic, strength and UOM level. The actual share of business versus target share of business was calculated.

The ETL team first looked at category ‘A’ items in terms of closing stock numbers at the generic strength and UOM level. Thereafter the percentage contribution in terms of sales and profit was calculated for these items.

The ETL team also found yearly and monthly trend analysis.

The ETL team then did a comparison between actual and target SOB on yearly basis and looked at the average procurement price by generic, strength and UOM.

Based on the same the ETL team looked at the vendor wise share and payables owed to the vendors in terms of ageing analysis(snapshot attached below):

Decision sheet: The final decision sheet had the following 7(seven) set of dashboards and graphs:

·         Consumption(year to date) by patient category

·         Consumption(year to date) by doctor

·         Consumption(year to date) by package

·         The team looked at inventory ageing and number of days inventory for the targeted stock items.

Based on the above decision criteria, the ETL team looked at actual SOB versus the Target SOB.

Action step:

The top 3(three) doctors were sent an email about the excess consumption.

Result for the end client: immediate intimation of the excess stock by the doctor and package.

Process pre requisites:

Data consolidation: HIS and the SAP systems

Data cleansing: specifically from the HIS system

Data auditing and data profiling

Business Intelligence: Microsoft platform was used for the same.

Data extraction/data capture: from multiple source systems.

Further details on inventory optimization can be obtained from the given link.

Automated GST filings in India

Why Turbodata GST?

As per the Times of India article, the government is dealing with under recovery for GST collections in India.

https://timesofindia.indiatimes.com/business/india-business/govt-grapples-with-rs-34000cr-gst-puzzle-over-tax-mop-up/articleshow/63262422.cms

Possible solutions from Turbodata(using the Deming Way and 'The Goal' for correct tax filings):

•         Data consolidation, data profiling for GST purposes
•         Data cleansing activities

To illustrate what could be happening with the taxation system in India, attached is a small story from ‘Raag Darbari’ by Srilal Shukl.

In a village named Shivpalganj in India, the protagonist Rangnath goes to meet his aunt’s father, Vaidji, a consummate politician. Vaidji has a protégé called Shanichari, who prepares the local intoxicant drink for the cohort(of Vaidji) on  a daily basis.  Shanichari is chosen by the Vaidji's cohort to become the village head.

Shanichari and his cohort are on a visit along with Ranganath to the village fair. Village fair implies lots of fun and for Shanichari- women. With little oversight in place, shanichari and his team have a bull run molesting women, the act of which disgusts Ranganath. Ranganath protests about the actions to Shanichari to which Sanichari replies: ‘Chamatkar ho gaya, babu’(Miracle has happened).

Are you facing the following issues with regards to GST filing?

• ·         Delay in filing
• ·         Concern regarding the changing regulations from the government
• ·         Concern regarding reconciliation: specially for customers using MS Excel upload.
• ·         Have a manual process for GSTR filing. The manual process is prone to error
• ·         Have high manpower costs related with GST filing.

Turbodata shall help your firm with faster, easier and more convenient GST filing.

How is Turbodata different?

• ·         All the reports for the end client shall be developed on the cloud installation. Only a minimal extract for all the vouchers and masters shall be done from the end client location. The ETL team shall commit to usage of maximum amount of RAM for the same(say 1 GB for incremental data extract)
• ·         The end client can do the prior change of the data. The system shall automatically take care of the same. This is enabled through incremental data load process using data normalization.
• ·         No reports shall be developed at the client location. All the reporting work shall be done at the server location.
• ·         Initial and incremental transaction data extract shall be done from the end client location.
• ·         The end client need not worry about re filing the GST reports since it shall be done by the GSP partner automatically.
• ·         The package is very easy to deploy, deliver and maintain. No high end software are required. The system can extract data from SAP, Tally and other source systems with ease.
• ·         Dependence on MS Excel for tax filing purposes is taken away since it could result in data errors and discrepancies.

  Current system:

Why is the Turbodata system better?

Turbodata system:

·         Turbodata system is inspired by ‘The Deming Way’, ‘The Goal’ and the Toyota production system and the Inmon methodology.  In a nutshell the following are the features copied from the above systems by Turbodata:

o   No error prone data should be passed for the reporting purposes. The data needs to be cleansed, audited and consolidated before report development.

o   The processing of the transaction should be done as soon as the transaction has been fed in the source system. That is the processing should take place on a real time basis and not specifically at the end of the month. Turbodata enables this feature in the following manner:

§  Each transaction fed into the end client source system is assumed to be an order from the end client.

§  The system offers the facility for real time extract and upload(current system is manual but the data can be loaded on a daily basis by the end client go the server)

o   Once the data has been loaded onto the server, it is transferred to a normalized database(insert, update and deletes). At the data warehouse level the data cleansing, data transformation, data consolidation activities are done

o   Once the data has been cleansed at the datawarehouse level then the reports for GST are developed. In one single lot, GSTR1, GSTR2 and GSTR3 reports can be developed.

o   Turbodata is integrated with at least one GSP partner. The end client could look at other GSP partner solutions if it desires the same.

o   The deployment of the solution is very easy and convenient. For any end client the deployment should take not more than 20(twenty) minutes. Minimum installation pre requisites are required.

o   The data for the end client is stored in a datawarehouse. The end client does not need to worry about changes in the statutory requirements. Other high end services like inventory optimization and predictive analytics are possible on the cloud.

To check why should the end client consider Turbodata GST, please check the following linkage:
http://mndatasolutionsindia.blogspot.in/2018/02/why-turbodata-gst.html

Indicate the following:

·         ERP system/ERP systems

·         Turnover: frequency of load

·         Number of locations

  Sample video link: https://www.youtube.com/watch?v=sYbeBfc3ozo&feature=youtu.be

       The product uses optimum RAM so that the source system does not hang during extraction as given in the following video:
       https://youtu.be/7CULkzc5h2g

Reconciliation between buyers and sellers for GST

As per the changed government norms, the buyers and suppliers could have  to reconcile the entries before filing the GST Returns.

http://www.business-standard.com/article/economy-policy/infosys-chief-nandan-nilekani-plan-on-gst-invoice-matching-may-be-tweaked-118022300073_1.html


In this regards Turbodata can help the end client in the following ways:

• Consolidated platform for data consolidation, data cleansing and data transformation.
• Capturing data entry errors automatically
• Matching of the GSTR reports with Tally GSTR output

Take a look at the other blogs:

• Why Turbodata GST?