Here is a "deeper dive" into some of the amazing projects that we have worked on!
Case Study #1
Manufacturing company was spending 4 hours per week generating customized daily open order reports from a CSV file based off ERP system data. This was leading to inconsistent reporting, errors and lost productivity.
After meeting with company management and their team, we were provided the raw CSV data file along with guidelines for how this data needed to be transformed into a formatted report. Part of the requirements included automatically highlighting rows different colors based on order priority and expected shipment dates along with performing sales and shipping calculations for the team to track sales team data. We created a stand-alone Excel tool which included a macro that would take the most recent CSV file from a folder, convert it to a new Excel file and then manipulate the rows and columns to meet requirements. This also added conditional formatting to highlight appropriate rows, formulas to perform calculations and added a timestamp to the tool so that the team would know who last ran the report and at what time it was generated. This project saved the company several hours per week which over the course of a couple years will add up to over $10,000 in internal cost savings.
Case Study #2
Cement Block Manufacturing Company
Manufacturing company in cement products needed to create an interactive sales quote tool that could house product information and pricing while being able to auto calculate discounts, quantities and generate a customer quote without showing all the internal details.
We were provided a current quote document that was being used in Word along with a list of products and pricing that needed to be entered into the quote tool. The formatting on the main quote page of the Excel workbook was standardized so quotes would have a consistent appearance. A second page was used as the input form where sales would enter customer information along with product quantities. They could also add miscellaneous costs in such as mileage and freight charges to get a better picture of job-related revenues and costs. Macros were added so that by clicking a button the appropriate products would be copied to the customer quote without having to do any additional work. Another button would clear the quote input page and allow a new quote to be started. This project saved the customer time while standardizing quote appearance.
Case Study #3
Furniture company needed to automate manual inventory data recording tasks.
We spoke with the customer to understand how the Excel workbook worked and was currently being used. This led to a discussion of tasks that needed to be automated to save time and reduce the chances of errors. Since the workbook was already created, we built a macro that copied and pasted data into correct cells when the file was turned over for a new production month. This macro also cleared data entry cells so that new production data could be entered for inventory tracking. After discussing further, we divided the single macro into two separate ones to allow additional functionality between copying/pasting and clearing data for the customer. The end result was tedious, manual tasks being replaced by clicking a button to accomplish the same goal.
Case Study #4
Parts Component Manufacturer
Needed to create a searchable product database in Access for customer service team as current software was no longer able to perform this task.
We worked with the customer to see their vision for how the database needed to operate. The customer provided current data sets as a starting point for building the required search queries. The Access database was built and included required navigation, data entry forms, queries and reports so that the customer would be able to perform searches quickly. Incorporating macros into the form operations was important to allow for correct updating of combo boxes and operation of navigation controls. The result was a database that accomplished the product search requirements for the customer, saving them time and allowing for future database additions.
Case Study #5
Travel agency needed commission tracking and event planning Excel spreadsheets to be streamlined with better efficiency.
After speaking with the customer and reviewing the current spreadsheets, we worked on a plan that would incorporate linking cells together to minimize data entry and allow for automatic calculations. Tables were used to organize event information and summarize financials accordingly. Conditional formatting was incorporated to show where certain data inputs were outside of a specified range, highlighting the need for additional review. The result was a well formatted set of spreadsheets that saved the customer time and took their data entry to another level.