Analizing the Quote Frequency after Stramlining The Process
Using Microsoft Excel
No 1 Project Links:
No 2 Gathering the Data
Five and a half years has passed since we put the calculator into production...
The first step in this process, was to gather all of the information that was generated by the tool and begin organizing it for cleaning and exploration. As the tool was created in Microsoft Access, it was easy enough to export the tables of data as excel spreadsheets and save them into a repository. I set up a Github repository in order to track the progress of the project and allow for version control.
Click Here to Visit the Repo: OHMS Data Analysis Capstone
No 3 Cleaning the Data
The Data set was small and manageable. The tool that I selected for the cleaning process was Microsoft Excel.
At this point, I realized that documenting the data cleaning process would have been made easier utilizing Power Query however, there wasn't much that needed to be cleaned. The process began with deleting the lines that were incomplete. I then reformated the date column to be more consistant with data practices. Changing it to [Year, Month, Day] format. This was all that was needed for this data set. I then turned the data into a table and moved to the next phase of the process.
No 4 Analyzing the Data
Here is where the interesting work begins...
As I am using Excel to complete this portion of the analysis, I created a copy of the excel file and checked it into github in order to keep track of any changes that would result from the analysis process. After reviewing the data in order to determine what portion of the story it would lend itself. I had determined that the most relevant information was the year to year data that was largly influenced by the economic ebbs and flows of the contract furniture industry. I needed to exclude the information from 2020 and 2021 as the inconsistancies skewed the more relevant information captured during the previous 4 years.
2016 Operations Calculations Data:
Some background information needs to be noted in regards to the 2016 fiscal year. During this year, there were three sales representatives employed. Two Sales Reps left the company and their positions were never filled moving into the the next 3 years. This becomes apparent when we move into 2017. Looking at the distribution of the quotes. each market segmant (Government, Healthcare, Education and Corporate) had there individual cycles. It also needs to be noted that this was when we had placed the installation calculator into production. Processes and procedures for implementing the OHMS calculator were still being developed.
2017 Operations Calculations Data:
As a certified dealer, we often accepted intermarket projects that were sold by dealers in the lower 48 states and were to be installed by our delearship locally. The Operations Manager was completing the smaller jobs and intermarket quotes in the proof-of-concept excel calulator. The information generated by the spreadsheet was never transfered to the OHMS calculator and therfore missing from the numbers represented here.
2018 Operations Calculations Data:
During this period, the Team is starting to build its stride. As seen in the 1st quarter, business was building and the Operations manager was capable of easily keeping pace with the increased number of quotes that needed to be completed. As there was only one sales representative covering all four verticle markets, the volume of quotes had diminshed. this will be the a trend going into 2020.
2019 Operations Calculations Data:
Moving from 2018 into 2019 the business had a solid process in place and developed all four market segments as indicated by the consistant number of quotes that were being completed thruoghout the year. The business was in a good place to begin the growth process by bring in more employees, allowing for added capacity.
The files regarding the analysis are available at my repository.
No 5 Summary
In attempt to fill in the gaps, late 2019 moving into 2020, the company did hire more employees. The Operations work shifted to hourly work on an as needed basis. This created missing data in the quoting process. This continued through the following year, and moving into 2021. This is the reason for excluding this period of time from the analysis. The question that was originally being asked was regarding the Frequency of quoting. Unfortunately, there wasn't enough information captured that could be back tested against historical data. In order to determine if the quote tool made a difference, would require surveying the operations team for a more qualitative answer to the success of it's implementation. As mentioned in the initial article, The tool was developed in order to provide the Operations Manager a better quality of life and, allow him the ability to complete his other tasks in an more timely manner.
No 6 Tools
- Microsoft Excel
- Microsoft Access
- Github