Portfolio of Projects Created
By Chris Shockley
Table of contents
Purpose
The PURPOSE of this report is to illustrate how my Data Analytics skills assisted department heads in decision making.
Introduction
Excel was traditionally the tool of choice for data analytics at my current company. Today, however, Programming languages, such as, Python and R help in ways that weren’t available prior. I learned R with the hopes of helping the decision makers within the company.
By using ‘R’ at ACB we were able to obtain fresh and insightful views of our data like no other time in the companies history. We are able to better quantify our intutions. We could make decisions based on emperical evidence. And the information was timely.
Below you will find a few of of the larger projects I have created.
Customer Trends Using Pounds Analysis and Geo Coding
The Question:
Which ACB Customers are trending negatively or positively as it relates to Pounds Shipped?
The Problem:
Because ACB has approximately 5,000 Active Customers at any given time, the Sales Team had difficulty identifing which Customers were reducing/increasing their buying or were trending negatively/positively.
The Solution:
After exploring several different methods, the linear model, which uses the slope of a line was chosen. The more positive the slope the greater the velocity of purchasing and the opposite. We then built a Web Application around the data, adding some additonal features, such as filters, and geo-coding (for potential rollout to the Outside Sales Team to help them to monitor their own accounts).
Impact:
Though it is hard to measure the impact of this Application, it can be confidently claimed that the ACB Sales team has more precise and meaningful information on the buying habits of our Active Customers than previously. The Sales Team now can reach out to those customers that are trending negatively and positively much quicker than before.
Future Development:
The Web Application is updated Monthly. The Web Application was built with Outside Sales in mind, therefore, at some point the Web Application could be rolled out to Salespeople so they can better monitor their own Portfolios.
Days Beyond Terms with Portfolio Weights
The Question:
Which Customers are driving Average Days to Pay in the Accounts Receivable Portfolio?
The Problem:
Because the A/R Portfolio is fluid it is difficult to see what impacts a given customer(s) have on the key the Credit Departments key metric Average Days to Pay.
The Solution:
The solution consisted of calculating a Weighted Average for customers based on their average days to pay and their outstanding balances. That information was then used to create a Web Application which could be updated monthly. This allowed the Credit Department to not only see the biggest drivers but to see how individual companies fit within the portfolio. The Credit Department also uses the Application to see customer trends, which are becoming more positive or negative and making a decision on credit respectively.
Credit Memo Analysis
The Question(s):
How many Credit Memos is ACB producing Annually? What is the % of Credit Memos to Total Orders? Are the number of Credit Memo constant year over year?
The Problem:
Credit Memos are a valuable source of information for ACB since they can show areas within the company that are having issues. Moreover, Credit Memos are costly. They create more work for every department within the organization. It can also be argued that Credit Memos effect our customer satisfaction, though no consideration was given to that in the analysis.
The first run at the analysis was to get a global view of the problem. How many Credit Memos are there? Are they constant through time or are they increasing? Are they tied to the amount of Orders? Among other questions.
The Solution:
The solution for the initial analysis was to look at the Credit Memos as it related to Total Orders. After that analysis was completed we broke down the Credit Memos by Business Group (i.e., Warehouse, Sales, etc.). A web Application was built for communication to Management and for individual analysis.
Impact:
The impact was immediate as the respective Business Units digested the information. The data pointed to a consistent trend between Credit Memos and Total Sales. But no definitive conclusion was made as ACB Management requested that further detailed analysis of the Credit Memos be completed to see if there were any trends within the Credit Memos themselves. That analysis was completed and can be seen in the Credit Memo Tailored Application, which is discussed in next section.
Future Development:
The Application was a stepping stone to a more detailed analysis, which can be seen in the next section. That detailed analysis is now updateded on a monthly basis.
Credit Memo Root Cause Analysis
The Question(s):
Are there any trends as to the cause of the Credit Memos?
The Problem:
In the Credit Memo Application I noticed that Credit Memos were relatively constant year over year. But what about the groups within the Memos? Were they constant? Were there specific companies that were causing more Credit Memos? Departments? Were there any systemic issues that could be seen in the data?
The Solution:
The solution was to complete Exploratory Analysis on the Credit Memos. To complete this a Web Application as built that allowed each individual department to filter the data based on their own needs.
Impact:
The impact was noticeable as there were indee trends within the data. Those trends were reported to the department heads and will continue to be monitored going forward.
Future Development:
The Root Cause Application will be updated every month for department heads. The goal, though not explicit, is to see a reduction in Credit Memos - from 2% of Total Orders to 1%.
Forklift Analysis
The Question(s):
What are the expenditures associated with specific forklifts? Are there any trends over time within the costs?
The Problem:
ACB had a contract for Forklift repair, which was quite expensive and the data group was tasked with analyzing the data to see whether or not the contract should be renewed. The data, however, was unstructured (one of the columns was text) so a search by keyword was needed to extract the relevent data.
The Solution:
Build a Web Application for the department head to use in a presentation for a recommendation to mangament.
Impact:
Being able to show visually the costs in real time on several data points quickly made the decision making process easy. The contract wasn’t renewed.
Future Development:
The web application will be updated annualy or upon request.
Conclusion
Learning R and Shiny has helped add value to my organization:
Learning R and Shiny has helped our company quantify our data, help derive action points, and create confidence in decisions.
Possible Analysis Areas of Interest with R:
- Employee statistics
- Employee Retention Costs
- Pricing Issues
- Material Issues
- Machine Issues
- Scheduling
- Process Improvement
- Forecasting