Performance Lawn EquipmentAn exercise in addressing business efficiencyDamon RobertsBlockedUnblockFollowFollowingMay 29Photo by Helloquence on UnsplashThe following mini-projects attempt to answer several questions posed by the board of fictional Performance Lawn Equipment, a company specializing in tractor and mower production.
The associated data includes customer satisfaction, market sales reports, production effeciency, and cost-benefit analysis.
The purpose of these exercises were to explore the various methods of reporting basic statistics with Excel.
All work and graphics presented below were created in Excel.
Dealer and End-User SatisfactionFigure 1: Average Dealer Satisfaction with PLE by RegionFigure 1 (above) shows the average dealer satisfaction across a 5-year period in each region where PLE operates.
The ratings were collected via a survey which allowed respondents to rate their satisfaction with PLE on a scale of 1–5, where 5 was the highest satisfaction.
The collected data were weighted and averaged to create the figure above.
We can see South American dealers consistently rate PLE the highest, while dealers in China tend to rate it the lowest.
The South America and Europe markets historically rate the company around 3.
75–4.
00 with little change.
The Pacific Rim has seen the greatest improvement over the last 5 years, bringing the average rating from just under 3.
25 in 2010 to over 4.
00 in 2013.
While there was a slight drop in average rating for 2014, all other markets maintained or improved ratings from 2013.
Table 1: Average Satisfaction from Dealers by Region and YearTable 1 shows the mean value and standard deviation for satisfaction ratings as collected from dealers on a scale of 1–5.
We can see the ratings have improved each year in all regions except Europe.
North America, for example, averaged a rating of 3.
78 in 2010, which increased to 4.
11 by 2014.
Although China entered the market with an initially low mean rating, the ratings still improved from 3.
00 in 2012 to 3.
69 in 2014.
Europe increased its mean rating from 3.
93 to 4.
12, but then saw a slight drop to 4.
07 in 2013.
The standard deviation also increased each year for most regions, indicating a wider range of response values to the survey.
This suggests there was more variability in the responses, but the increased mean indicates values trended towards higher ratings.
Figure 2: Average End-User Satisfaction with PLE by RegionFigure 2 (above) shows data collected from a similar survey sent to end-users of PLE products.
The data from this survey were also collected and weighted to create averages by year and region, again where 5 is considered the highest level of satisfaction.
Overall, end-users rated their satisfaction higher than dealer surveys indicated.
In this data set, the North American market ranked higher almost every year.
South America was typically rated 3rd or 4th in end-user satisfaction, and in 2014 it had the lowest rating of all markets at an average of 4.
11.
Similar to the average dealer satisfaction, the Chinese end-user market tended to rate satisfaction the lowest.
South America and the Pacific Rim trended closely to one another each year, with differences of less than 0.
05 from 2011 to 2013.
Europe had possibly the largest change across years, increasing from a low of 3.
90 in 2012 to a high of 4.
07 in 2013.
Table 2: Average Satisfaction from End-users by Region and YearTable 2 provides similar information, although collected from end-user surveys instead.
Each region saw an increase overall across the 5-year period, although the South American market experienced a slight drop from 2010–2011.
Beginning with a mean rating of 4.
0, South America’s rating dropped to 3.
95 in 2011 before climbing back to 3.
99 in 2012, followed by a steady increase in later years.
After 2010, the North America region was rated highest among the regions PLE services, ending 2014 with a mean of 4.
22.
Figure 3: Total Complaints Filed Quarterly by RegionAbove, figure 3 details the number of complaints fielded quarterly by each region.
The North American market consistently accounted for at least 50% of all complaints filed to PLE, sometimes accounting for 60%.
The North American market is also the largest, which would likely explain why the largest volume of complaints are from that market.
The ratio of complaints coming from the South American market increased gradually beginning in 2011, while the volume coming from the European market has stayed relatively steady.
We can also pinpoint when China entered the market in 2012, as complaints began appearing from the new market during Quarter 1.
Figure 4: Customer Service Call Response Times by QuarterTable 3: Response Times to Customer Service Calls by QuarterFinally, figure 4 and table 3 highlight average call response times by the customer service department.
2 years of data are available for this metric, which are broken down by quarter.
On first glance, we can see that the median response time decreased from Q1 2013 (3.
829) to Q4 2014 (2.
477).
The interquartile range has also improved over two years; in 2013, 75% of response times fell between 2.
988 and 5.
042.
By Q4 2014, 75% of response times fell between 1.
62 and 3.
476.
The maximum time recorded in Q4 2014 was 4.
872, while Q1 2013 had a maximum of almost twice as long at 8.
019.
While times declined across the board, there was a single outlier in Q3 2014 of 7.
419, well outside the 3rd quartile range of 4.
075.
Market PerformanceFigure 5: Total PLE Mower Sales each Quarter by RegionThe quarterly mower sales by region are displayed in figures 5 and 6, respectively.
Figure 5 shows the majority of PLE mowers are sold in the North American (NA) market.
Typically, over 80% of mower sales in any given quarter are accounted for by the NA market.
Europe makes up the second largest consumer market, averaging around 10–15% each quarter.
South America accounts for less than 5%, while the Pacific Rim and China make up even less.
We can also see a pattern of buying between the NA and European markets each year.
NA sales tend to increase during the first and second quarter of the year, while the European market tends to increase during the third and fourth quarters.
Figure 6: Monthly Mower Sales by PLE and IndustryFigure 6 shows a comparison between mower sales by PLE and as a whole by the industry.
Mower sales have been steady for several years without much change.
However, there is a distinct cycle following the time of year: sales typically rise to be their highest in the summer months, then decline as the year progresses.
Both PLE and industry-wide sales are influenced by this trend, as the graph below shows.
By analyzing sales data from a 5-year span, we can calculate the correlation between PLE and industry mower sales to be about 0.
9904.
This indicates a strong relationship between when sales in each rise and fall.
Figure 7: Total PLE Tractor Sales each Quarter by RegionFigure 7 shows similar data regarding tractor sales, although with drastically different outcomes.
Rather than dominate the market as with mower sales, the NA market often accounts for only 30–40% of PLE tractor sales.
Europe commands a larger portion of these sales with similar values of 30–35% quarterly.
South America and the Pacific each make up 10–15% of quarterly sales on average.
Over the last several years, tractor sales in Europe have declined while sales in NA have risen.
The South American market has also increased sales, accounting for a larger portion of PLE tractor sales.
Figure 8: Monthly Mower Sales by PLE and IndustrySimilarly, we can observe the relationship between PLE and industry in tractor sales.
Figure 8 tracks the sales of tractors over the same 5-year span.
Like mowers, tractor sales tend to ebb and flow with the months each year.
However, unlike the mower trends, tractor sales have seen a steady increase each year.
2010 Industry sales topped 15,905 in July, which increased to almost 19,000 in April of 2011, eventually reaching a peak of 27,374 in June 2014.
While PLE sales figures were only a fraction, they followed roughly the same trend beginning in 2011.
Sales grew gradually from a high of 2,340 in April to a record 4,476 in June 2014.
The correlation between PLE and industry tractor sales is slightly lower than mower sales at 0.
9603, but it still indicates a strong relationship between the two.
Expansion CostsThe tables above explore the costs of building new plants and expanding existing ones in Kansas City and Santiago.
We can more easily compare the value of each proposal by breaking the total construction cost down into the new cost per unit after construction.
This takes into account how much is required to recoup costs for each proposal.
Accounting for new costs and average shipping costs from each location, we can see the unit costs range from $39.
85 to $77.
57 for mowers, and $40.
30 to $78.
01 for tractors.
The first quartile for each is $54.
95 and $55.
40, respectively, while the 3rd quartile for each is $62.
49 and 62.
95.
Based on data from the first table, the most valuable projects (or those with the lowest new unit cost) will most likely be new plants in Mumbai and Singapore.
Both Mumbai proposals fall within the first quartile, meaning they’re among the 25% cheapest proposals.
Expanding Kansas City capacity to 20,000 also falls within the first quartile, making it the cheapest upgrade option available.
The remaining upgrade options in Santiago are past the 3rd quartile, placing them among the highest 75% costing projects.
The 4th quartile, also known as the 100th percentile, equals the maximum value in the data set.
In this case, upgrading Santiago to 5,000 capacity will result in the highest cost per unit to recoup costs.
Business Operations EffeciencyFigure 9: Ratio of Deliveries Made On Time by MonthWe can see the rate of on-time deliveries made across a 4-year span in figure 9 (above).
A low was reached during March 2010, when 27 of 1,116 deliveries were not delivered on time, resulting in a drop from 98.
1% to 97.
6%.
A peak is noticeable on September 2010, when only 13 of the 1,223 deliveries were late.
98.
9% of deliveries were on time for that month, which would hold as the highest point until July 2012.
The red dotted line above graphs the trendline as the rate of successful deliveries increases gradually each month.
While there are a few noticeable high and low points, the majority of delivery rates follow a steady pattern.
Figure 10: Average Defects from Suppliers Each Quarter (per million items received)Data on defects received from suppliers per quarter is graphed above.
These figures indicate how many items on average per each million received from suppliers are defective.
At its highest, PLE received an average of 848.
33 defective items per million.
Similar rates were maintained from Q1 2010 until Q3 2012, when the number of defective items began decreasing.
By Q4 2012, the average was 701.
33.
2013 saw a slight decline with an overall average of 669 items throughout the year.
Beginning Q4 2013, the rate of defective items began declining rapidly.
The average dropped by 40–80 for each quarter of 2014, ending with an average rate of 439.
67 defective items in Q4 2014.
Mower Blade ProductionTable 5: Detailed statistics from mower blade testsThe table above describes the data set collected on mower blade weights.
The average weight of the 350 blades tested was approximately 4.
99, with a standard deviation of .
1093.
There was very little variance in the blade weights.
50% of observations fell between 4.
92 and 5.
06, a range of only 0.
14; 95% of observations fell between 4.
77 and 5.
21.
Assuming the data are normal, there’s a probability of 2.
78% that a weight will be measured over 5.
2.
Similarly, 4.
04% of measurements can be expected below 4.
8.
In the collected data set, 2% of weights were above 5.
2, and 2.
29% were below 4.
8, resulting in a difference of 0.
78% and 1.
76%, respectively.
Looking at the graph below, we can further confirm that weights generally fall within a tight specification.
Almost all measurements were within 4.
7 and 5.
3 with two anomalies.
Plotting a line of the average weight gives us a slope of -7e-5, which declines at a very slight rate of -0.
00007.
While there is a negative slope, it’s small enough to be considered insignificant.
We can safely assume blade will continue to be manufactured at a steady average weight of 5.
00.
Figure 11: Mower blade weights over timeOverall, we can assume the blade manufacturing process is stable and predictable.
There are, however, two outliers in the data set.
Blade number 37 was found to be 4.
63, just over 3 standard deviations from the mean.
Sample 171 was recorded as 5.
87, which is well outside the expected range of any values.
These could result from a number of issues, such as individual defects, problems with materials, or errors in either measuring or recording the value.
Because the mean, median, and mode are all similar values and the values are evenly distributed on either side of the mean, a normal distribution is the best-fitting model for this data, as shown in the histogram below.
Figure 12: Histogram of blade weights over timeIn one last experiment, the exponential distribution in figure 13 below presents the results from the mower test.
This method best displays the frequency of how many failures were in each of the 30 tests, which randomly sampled 100 mowers each.
Overall, 54 of the observed 3,000 mowers failed the test, approximately 0.
18%.
We can see just under 15% of the tests reported no failures, while the majority of tests reported either 1 or two failures.
About 35% of samples reported 1 failure, and about 38% reported 2 failures per 100 mowers.
The rate declines sharply after 2, with only 15% of tests reporting 3 failures.
No tests reported more than 5 failures per sample of 100 mowers.
Given the collected data, we can estimate approximately 2.
07 of the next 100 mowers tested will fail.
Figure 13: Failures in mower blade tests.. More details