Automation brings significant value to various business functions. Tasks such as data reconciliation, validation, and copy-pasting are ideal candidates for automation, particularly in finance. This improves efficiency and reduces manual effort across accounting, procurement, credit checking, and reporting. But, it comes with its own challenges. 

Xiaomi knows this better than anyone else in the field, as it is heavily driven by automation. To date, the company has implemented various automation technologies to streamline its core business processes and improve efficiency.

In one such endeavour, the finance team at Xiaomi India Private Limited took a system-driven approach to automate their core business operations. The team found their solution in automation using SQL, R, and Python tools in collaboration with Convergytics Solutions Private Limited. 

The Problem to Solve

Previously, Xiamoi took 368 hours per month to check data differences at both the card and cash level for 50 stores. With automation, the task is now faster and easier. But, the question is, how did they do it? Xiaomi automated its manual data validation and computation processes using SSMS and R-Studio. 

Further, the team at Xiaomi used Excel files as their primary source of data. The input data was sourced from multiple systems, including WMS (Warehouse Management System), SAP, and DMS (Distributor Management System). The team also performed reconciliation at various business categories.

But, it was easier said than done as users started to face issues while handling the excel files containing massive data as there were multiple constraints. Given the increase in day-to-day business transactions, they could not perform any single validation or computation manually due to the size of the data.

How Xiaomi optimised automation

The automation process offers a faster implementation for specific use cases than reconfiguring core ERP systems and built-in applications. As a result, it gives the potential to deliver value for various use cases across the organisation, including activities that require logging onto multiple applications, repetitive and redundant manual tasks, and data reconciliation. 

Finance is a common use case, with teams taking important steps in reducing manual effort and increasing efficiency in domains spanning accounting, procurement, purchase-to-pay and order-to-cash, credit checking, and reporting, amongst others.

The development process involved the installation of Jupyter, Python, R, and, most importantly, the SQL Server Management Studio by Convergytics Solutions Private Limited. In addition, the IT departments must ensure that the tools integrate with internal systems like SAP, WMS (Warehouse Management System), OMS (Order Management System), and CRM (Customer Relationship Management).

How they did it?

The following datasets act as inputs:

• Bank Statement
• Bajaj Finance Statement
• Collection Report (OMS: Order Management System)
• Ezetap Report

The bank statement report comprises key columns like serial number, transaction date, transaction amount, transaction description, and amount. 

The Bajaj Finance statement report includes key columns like amount, date, UTR number, type of transaction, and store code. 

The collection report consists of key columns like amount, date, transaction details, type of payment, store code, transaction status, and amount. 

The Ezetap report includes key columns like amount, date, type of payment, store code, transaction status, and amount.

Development Flow

The data load is taken care of in SQL, and the output is in the expected format. The output structure consists of reconciliation at the store and date level for every mode of transaction. 

The logic computation involves pre-processing the input data and adding store code details during the pre-processing step.

SCQD ( Situation- Complication – Question – Desired State)

The SCQD Framework follows the below structure

• Situation: What knowledge or context is required to understand the complication? 
• Complication: What has changed, and why do we need to act now?
• Question: Explicitly state what we are trying to solve.
• Desired State: What is the solution to the problem? What are the specific recommendations or next steps?

Hypothesis Testing

Hypothesis testing enables developers to evaluate if the sample data is statistically relevant or significant. This process is crucial for measuring the validity and reliability of outcomes in any systematic investigation.

S. No.	Question	Priority	Hypothesis	Data Required	Data Available	Feasibility
1	FOCO (Franchise Owned Company Operated) to COCO (Company Owned Company Operated) Store Transformation Update.	H	How many stores do we have?	Store Level Data based on Transformation update	Store Level Data based on Transformation update	H
H	How many stores are yet to get transmitted?	Store Level Data based on Activation	Store Level Data based on Activation update	H
2	List of Input Source	H	What is the current Input source list?	OMS (Order Management System) Data, Bank Data, Bajaj Data	OMS (Order Management System) Data, Bank Data, Bajaj Data	H
Do we have a list of Key Column Identification from sources?	OMS (Order Management System) Data, Bank Data, Bajaj Data	OMS (Order Management System) Data, Bank Data, Bajaj Data.	M
L	Do we have a Daily selling Report (DSR) for Cash transactions in proper format?	DSR Report	DSR (for very few sources)	L
3	Format for reconciled Output	H	Is the current output structure format being proper?	Manual Output file	Manual Output file	M

Fishbone Diagram

A fishbone diagram is a visualisation tool for categorising the potential causes of a problem. It is used for root cause analysis; a fishbone diagram combines the practice of brainstorming with a type of mind map template. It should efficiently be a test case technique to determine cause and effect.

Notation	Description
A1	As there is an increase in business regularly Keeping a track was a key challenge.
A2	Due to increase in business the enhancements in business will play a major role.
A3	As validation needs to be performed on a regular basis the time was a deciding factor.
A4	Dependency on the system/team to perform the activity without any issue.
A5	Due to inadequate system If activity is getting performed manually in excel will be difficult.
A6	Improper dataset will result in a wrong validation file.
A7	For a desired result / output file format is the key.

Output

In the Output structure, the system calculates the collection of payment at the date level for an individual store at various payment modes like Bajaj Finance, Amazon Pay etc.

Serial No.	Column Header	Input Table Name	Logic
1	Store Code	Bajaj, Order Management system, Ezetap Report.	Store ID column
2	Date	Bajaj, Order Management system, Bank, Ezetap Report.	Key date columns
3	OMS_CASH	Order Management system	Cash transaction from OMS Source
4	OMS_CARD	Order Management system	Card transaction from OMS Source
5	OMS_CASHIFY	Order Management system	Cashify transaction from OMS Source

The output based on the above column is used to identify the entry flow at various payment levels like Card, UPI, and Cash.

If any discrepancy is observed, the user team can check with the system about entry flow.

Concerns

While automation is easy to implement, it also poses some challenges spanning security and access control. For example, users can build a function and store embedded usernames and encrypted passwords, resulting in issues with exception handling or blocking access by other users.

It also poses a risk of failure wherein common issues include unstructured datasets, changes in the scope of work and undefined processes.

Conclusion

Automating certain parts of your finance process strategically is pretty much like adding another employee to a company’s team. It saves time, protects businesses and helps them scale without fear. 

The post Xiaomi’s Automation Prowess Redefines Convenience and Efficiency appeared first on Analytics India Magazine.