From forecasting demand to ordering – An automated ML approach with Amazon Forecast to decrease stockouts, excess inventory, and costs

January 2023 | 9-minute read

Tagged: RetailAnalytics | SupplyChainOptimization | AIinRetail | DemandForecasting | InventoryManagement | AmazonForecast | MLforRetail | RetailTechnology | GanitSolutions | AutomatedOrdering | RetailEfficiency | RetailInsights | DataDrivenRetail | SupplyChainSolutions | RetailSuccess

One of the critical responsibilities of the financial control team in any organization is to ensure the efficient functioning of the company’s cash flow. To accomplish this, they must source multiple data from various functions/divisions within the organization, analyze this and then identify cost-efficiency avenues and past leakages.

Ganit is an AWS Advanced Tier Services Partner that provides intelligent solutions at the intersection of hypothesis-based analytics, discovery-driven artificial intelligence (AI), and new-data insights. Over the years, they have successfully deployed Business Intelligence systems on AWS cloud environment with Data Lake and Data Warehouse being the core of the solution. This system has helped many of their clients reduce significant man-hours in the creation of reports and spend more time on driving action to improve their top-line and bottom-line numbers.

Ganit’s client is the financial control division of an Indian subsidiary of a large apparel manufacturer in India, tasked to optimize expenses across divisions and ensure functions operate most efficiently.

To achieve their goals, the client (financial control team) was facing a few key challenges.

1. Data resided in disparate sources, i.e., functions-maintained data in either a global ERP system (MySQL-based), locally maintained ERP system (MSSQL-based) or MS excel documents in Microsoft SharePoint system


2. Since the organization was large ($300+Mn Annual Revenue), the data size was for MS Excel to handle was huge (>10GB overall) and would require a lot of processing time using excel Macros (pre-built scripts)


3. Most of the time would go into manually collating data across various departments, and hence, they would create these reports monthly. To monitor operational efficiency and drive efficient capital flows, the process of creating and circulating the reports required a shorter time to reach the truth.


4. They would manually create the reports which were prone to
   
   
   1. Human error (while collating data, processing, and circulating)
   
   
   2. Ability to deep dive (used pivot tables to identify deviations) on the concerns raised.


5. The above would often lead to more extended discussions on the sanctity of the data during the debate rather than working towards resolving the issue.

Some key constraints laid out to Ganit team from the client to consider before building the solutions:

1. Since some source systems were maintained by a global team, the Indian subsidiary did not have permission to connect to those source systems directly


2. While the IT team was able to provide access to source systems maintained in India, due to the on-prem system’s capability, data movement was allowed only for a few hours during the day


3. Excel documents maintained by departments were prone to changes in formats (data inputted manually)


4. While the client wanted a robust and scalable solution, they also wanted to ensure the costs to this unified platform is efficiently handled


5. Also, since these files had sensitive information, client (both financial and IT team) wanted a solution which had high security provisions with limited (permission enabled) access to select users


6. Business filters and other adjustments varied based on the divisions and had to be incorporated in the reports to be developed

Solution Overview:

Given the above challenges the client was facing, and the constraints provided by their IT team, Ganit team suggested the client to create a data lake on a cloud environment which would act as a single source of truth (both structured and unstructured data) for all its Business Intelligence reporting needs. Amazon QuickSight was chosen to be the reporting UI layer for business intelligence.

AWS was finalized as the cloud provider as its ecosystem allowed:

1. Ease of integration with other disparate source systems


2. Quick to deploy in production


3. Enables scale within the organization


4. Its suite of AIML products with machine learning capabilities improve predictive capability (enable proactive correction rather than reactive)


5. Cost efficient (pay-per-use model)

Technical Architecture deployed in production system:

We configured the transactional systems to push incremental data to a remote FTP server during this process. The finance team also manually create reports like budgets and revenue targets; our client can place these files directly in an Amazon Simple Storage Service (S3) bucket location using the AWS console. S3 supports both data encryption in transition and at rest.

We used AWS Glue for data orchestration and ETL tool as it is a one-stop solution for the following reasons:

1. Glue supports all types of JDBC connections


2. Has crawlers & catalogs to crawl the files in s3 and generate metadata


3. Glue workflows help efficiently orchestrate the entire workflow


4. Glue job bookmarks help process only newly added data without the need to track old files


5. Glue supports encryption in transit to ensure secured data transfer

Using Amazon Athena, we created tables and views which could directly connect with Amazon QuickSight. We utilized key features of Amazon QuickSight during the report development journey.

1. Row Level & Column level security — We provided role-based access to all users. It enabled only relevant information to be accessed by respective users.


2. Threshold monitoring & user alerts — For critical metrics such as Contribution Margin and Net Sales, we set appropriate thresholds based on business inputs, which upon breach, would send “threshold breach” alerts to all relevant departments along with the finance team to act.


3. Reports subscription — Users can subscribe to paginated reports in their inboxes on a scheduled basis.


4. SPICE Memory — Super-fast, Parallel, In-memory Calculation Engine of Amazon QuickSight enables faster data retrieval and rapid calculation of complex calculations giving a seamless experience to the users.

Setup to ensure maximum security of data:

We used native AWS security provisions to ensure data encryption is easily integrated at rest and in transit. Amazon S3 uses SSE-S3 to encrypt data at rest. AWS Glue enables encrypting data in transit using KMS (AWS Key Management Service). Encryption for Data at rest in AWS QuickSight’s SPICE memory was using AWS-managed keys service.

Production System Governance:

For smooth functioning and governance of the infrastructure built, we enabled the AWS CloudWatch service to capture and check all the processes’ logs. AWS CloudTrail was set up to track all API calls across the AWS account. We used AWS IAM (Identity Access Management) to manage access to different AWS services for various users, groups, or roles across the account. We sent alerts to the user during job failures using the AWS Simple Notification Service (SNS).

Sample Snapshots of Business Intelligence Board built on Amazon QuickSight

Conclusion:

A scalable, cost-efficient, and robust system addressing all challenges and constraints laid out by the client was built using a Data Lake in AWS Cloud environment. This system led to a potential savings of ~1000+ Manhours every month. It enabled our client to proactively track and monitor cost leakages and take appropriate actions immediately, which could have led to a potential benefit of ~1–2% improvement in bottom-line numbers.

Ganit has successfully automated the entire financial control monitoring system for its client by empowering their decision making leveraging the plethora of services offered by AWS.

We recommend you kick-off your data transformation journey on the cloud with AWS to propel your organization’s growth by benefiting from its wide range of services.

To learn more about Ganit and its solutions, reach out at info@ganitinc.com.