The problem

In an insurance company providing insurance protection for loans, credit cards and other bank products, sales reports are received from several affiliated banks and retailers on a monthly and/or semi-monthly basis.

Each affiliate sells products with different names, rates, age and duration limits, etc. In addition, each affiliate provides the monthly and/or semi-monthly reports in a different format and sends a .csv file to the insurer.

The insurer needs to then check all incoming data for errors and inconsistencies, analyze the sales results, calculate tax information and report to the management, the state, and potentially the affiliates if errors are discovered.

Therefore, the monthly/ semi-monthly tasks that need to be manually performed for each affiliate using Excel include:
‍
• Preparing the incoming .csv files for analysis – e.g., by formatting columns (e.g., dates), handling inconsistencies and anomalies in text strings, etc.
• Analyzing up to hundreds of thousands of data rows for errors
• Calculating sales, tax, commissions, and other valuable information for each data point
• Calculating sales totals by affiliate, product, sales channel, etc.

The solution

A macro-enabled template file was created for each affiliate, allowing the user to:

• Find and select the raw data file.
• Apply the correct settings for analysis – e.g. relevant time period.
• Run the analysis by clicking a button.

As a result, the macro:
• Loads and prepares the data from the source file.
• Performs all necessary analyses.
• Calculates and prints all relevant information.
• Saves the result as a separate file.

The results

The automation of the process resulted in:

• A significant reduction in time resources, reducing the total length of the process from multiple days to less than 10 minutes per run.
• The elimination of potential human errors, by not requiring user input.

The problem

A Beauty Clinic offers a wide range of products and services, along with discounts based on the number of items purchased. In order to issue an offer to a client, the sales staff has to open an Excel file containing the price and discount information for every product, manually find the items requested by the client and calculate the total price.

The Beauty Clinic wants to expand the process of issuing offers by providing the sales staff with a tool that is capable of:

• Issuing a standard offer, based on a company template.
• Issuing an offer divided by seasons.
• Issuing offers in bulk based on Excel data files, containing client information, and generating automatic E-mails to clients with the offer attached.

All offers need to be saved as PDF, in order to be uploaded into a client management system.

The different types of offers have additional requirements:

• Offers of all types must include mandatory legal information and a GDPR compliance sheet.
• For seasonal offers, discounts are calculated for each season. The user may enter one or more seasons, for each season the duration of the procedures must be shown.
• Offers generated from a file must contain a summary sheet with the client’s data, shown with text fields, checkboxes, and images.
• Offers generated from a file must contain an offer divided into seasons and an offer for the same procedures if bought in bulk.
• Offers generated from a file must contain links to the respective pages of every product or procedure chosen by the client if a link exists.
• Special formatting must be applied to different sections of the document if the price calculations result in discounts.

The solution

A macro-enabled template file was created for each affiliate, allowing the user to:

• Find and select the raw data file.
• Apply the correct settings for analysis – e.g. relevant time period.
• Run the analysis by clicking a button.

As a result, the macro:
• Loads and prepares the data from the source file.
• Performs all necessary analyses.
• Calculates and prints all relevant information.
• Saves the result as a separate file.

The results

Since the introduction of the offer generation tool:

• It has become the only way to issue offers to clients, abolishing previously used leaflet-based offers.
• It has led to the introduction of a standard offer generation process, followed by an employee training for the entire sales personnel.
• All offers generated by the tool are being uploaded to a client management system, allowing sales personnel to access the information quickly and easily upon a client request or potential complaint, and ensuring that all client data is safely and centrally stored.

The problem

An international brand performs a brand evaluation and a competitor benchmark quarterly in multiple product lines across different markets around the globe. A market research agency is hired to process and analyze the data, provided by a third party, and to produce reports, based on a dashboard template.

The data contains comparisons between the client’s brand and its top competitors in over 15 markets. In each market the competitors and their respective ranks are different, as well as the product lines chosen for comparison.

The data are presented in tabular form, in a separate Excel source file for each market, and contain statistics for the current, as well as the previous three time periods.

Because the number of markets, brands and product lines is so high, the manhours required to produce the reports would render the project unviable if performed by employees, even as experienced Excel users.

The solution

The entire process of creating the dashboards was automated using VBA macros:

• One template macro-enabled file was created to produce the reports for all countries.
• From it, a raw data file was loaded, and the macro stripped the data for the appropriate market.
• The data were transformed along the requirements of the client for each specific market.
• Brand rankings and KPIs were calculated, based on formulas provided by the client.
• The data was loaded onto the dashboard.

The results

The automation of the process not only drastically reduced the time that it would take to complete the project but made it financially feasible in the first place.