Case Studies
Over the last 20+ years, we have built several hundreds of customized automated tools in Excel. Below we will showcase a few of the used cases.
Case Studies 1
Inventory Management System
Problem Statement
Our client ABC Inc. is in Restaurant / Food chain business.
They procure ingredients/raw materials, and use them to prepare finished food products (e.g. Pizza, Meals etc). They have been doing Inventory Management in Excel in a manual way. If a finished product involves a certain amount of different ingredients, they used to manually calculate & deduct the ingredients & calculate the remaining inventory. Moreover, since this was all manual, the ordering system was also inefficient & unreliable, often leading to shortage of ingredients at the time when it was needed.
Solution Provided
We discussed the client’s requirement and built an interactive and automated Inventory Management solution in Excel (using VBA macros).
The solution allowed the users to perform tasks at ease in an automated and user-friendly way. Users could enter Finished Products in a designated tab, and enter Raw Materials/Inventory in another tab. We created an easy mapping table (BOM) defining the ingredients/raw materials needed to prepare each finished product. In a user form, the user could simply select a finished product, and the system immediately shows the ingredients needed to prepare the items. Also, the system showed the stock quantities, making the user aware based on the inventory quantity, how many of any finished product the customer could prepare.
The solution built made use of interactive user form, buttons, date-pickers, dropdowns, etc. We built the Reporting module to track Purchase and Sales History by Year/Month/Date.
The automated solution highly increased the efficiency of the team, resulting in a saving of hundreds of effort hours per year.
Case Study 2
Automated Salary Slip generation
Problem Statement
Our client is a marketing company.
The client frequently needs to send bulk emails. Each time, send different content.
It was being done manually by creating individual New Email in Outlook, and then do copy-paste in mail body and in the fields like TO, CC, BCC, Subject, etc. This was taking a lot of effort and errors were high.
Solution Provided
Created a fully automated solution in Excel to create/send bulk emails using Outlook in Excel (using VBA macros).
The solution allowed the users to manage/maintain a database of the recipients (their ‘Firstname’, ‘Lastname’, ‘Email Address’, etc). The tool also easily created a template for the email to be sent. Then, upon a button click within Excel, customized individual emails got generated and sent to the complete list (or a selection from the list) with no manual intervention.
The automated solution highly increased the efficiency of the team, resulting in a saving of hundreds of hours of effort each year.
Case Study 3
Automated Emails from Excel using Outlook
Problem Statement
Our client ABC Inc. is in Restaurant / Food chain business.
They procure ingredients/raw materials, and use them to prepare finished food products (e.g. Pizza, Meals etc). They have been doing Inventory Management in Excel in a manual way. If a finished product involves a certain amount of different ingredients, they used to manually calculate & deduct the ingredients & calculate the remaining inventory. Moreover, since this was all manual, the ordering system was also inefficient & unreliable, often leading to shortage of ingredients at the time when it was needed.
Solution Provided
We discussed the client’s requirement and built an interactive and automated Inventory Management solution in Excel (using VBA macros).
The solution allowed the users to perform tasks at ease in an automated and user-friendly way. Users could enter Finished Products in a designated tab, and enter Raw Materials/Inventory in another tab. We created an easy mapping table (BOM) defining the ingredients/raw materials needed to prepare each finished product. In a user form, the user could simply select a finished product, and the system immediately shows the ingredients needed to prepare the items. Also, the system showed the stock quantities, making the user aware based on the inventory quantity, how many of any finished product the customer could prepare.
The solution built made use of interactive user form, buttons, date-pickers, dropdowns, etc. We built the Reporting module to track Purchase and Sales History by Year/Month/Date.
The automated solution highly increased the efficiency of the team, resulting in a saving of hundreds of effort hours per year.
Case Study 4
Employee Mgmt tool in Excel
Problem Statement
Our client has over 500 employees.
They needed a method to do the following:
* Add new employees,
* View and edit existing employee info,
* Incorporate the hours of each employee,
* Determine holiday entitlement,
* Calculate payroll info (including deductions, allowances, overtime etc),
* Generate and send pay slips
* Sending those salary slips over email to each employee through Outlook.
This process was taking lot of manual effort and chances of making human-error was high.
Solution Provided
Created an interactive and automated Employee Management Tool in Excel (using VBA macros).
The solution allowed the users to record and manage employee data, like introducing new members of staff, and to view and edit employee info when required. It also allowed to manage their attendance records. The tool also had features to create schedules for employees working in different shifts.
During month-end, the salary for each employees gets calculated based on fed information (holiday entitlement, salary details and more) and then the individual salary slips were generated and sent to each employee.
The solution made use of interactive user forms, buttons, date-pickers, dropdowns etc.
The automated solution increased the efficiency of the team, saving hundreds of hours each year.
Case Study 5
Quote/Invoice generation tool in Excel
Problem Statement
Our client is a rapidly growing UK service company who sends a growing number of quotes each month
Listing, creating, and sending the quotes were each laborious independent tasks that meant the estimating department was struggling to scale up as the business was growing.
Solution Provided
We created an automated solution in Excel for the client, using which the quote and invoice generation happened with a few button-clicks (single or bulk generation).
The solution automatically generated sequential Quote and Invoice numbers the last used number. The quote generation incorporated their costing database and meant they could be sent 80% quicker.
The tool also had the functionality to generate and send the Quotes and Invoices in either Excel or PDF or Word format in bulk upon a button click.
The automated solution increased the efficiency of the team, saving hundreds of effort hours per year. It will be used as a design template for a bespoke system reducing the development cost by thousands of pounds.