A Comprehensive Quantitative Analysis Based on Statistical Measures for Handy Hydraulics - Essay Example

? “ANALYTICAL REVIEW OF ‘HANDY HYDRAULICS (HH) INDUSTRIES’” Unit BUSINESS DATA ANALYSIS Unit coordinator’s C F SHOOSTARIAN. Submission Date: 20/05/11. ABSTRACT The scenario revolves around a company named Handy Hydraulics Industries. It is a company that has been growing at a stable rate over the past few years. The ownership of the company is however concerned that the company takes its decisions on the basis of past experience and gut feeling only. Therefore they hire a professional business analyst who can formally analyze the business process within the industry. The purpose of this report is to conduct the data analysis for the company’s business data analyst. In order to conduct this data analysis techniques such as sampling of data, finding out of the mean, mode, median , construction of histograms and other graphs, resolution of standard deviation and cumulative frequency upon samples of data would be implemented. The application of analysis techniques upon samples of data would enable the analyst to assess whether the company was prospering or not. The resultant of the analysis would also be an account of the future standings of the company if the present management strategies are maintained. The results of the data analysis would enable the analyst to suggest to the company’s ownership what course should their management strategies take in order to achieve maximum profits in the future. INTRODUCTION Laurel McRae, an experienced data analyst and strategic planner was assigned to conduct a comprehensive quantitative analysis based on statistical measures for Handy Hydraulics. Working for more than 20 years HH Industries has a long history of distribution. It started its operations as a family-owned business of the Douglas family, tracked down part sources, resold directly under the manufacturer’s name, or resold packaged individual parts into repair kits in its own name. During its initial five years business of the industry grew steadily; emphasis given on word of mouth and market niche. However, little marketing provided healthy startup for the organization. Its sales were limited within its headquarters Florida expanded to Alabama and Georgia by 1974 after it produced its first catalogue. Later in late 1970s and early 1980s the prospective customers spread grew up with the outreach of over 25,000 people by use of “Brute-force” marketing by the company. But this expansion couldn’t be controlled by the typical family-owned company, and Handy Hydraulics had to be sold to BMP enterprises (the present parent company). Mr. Douglas remained its president on a contract of 3-years. Emphasis been paid on investing in opening warehouses in Arizona and Ohio in 1985 and 1986 respectively, but neglected the importance of increased competition, management strategies and new technologies, etc. Consequently business couldn’t gain from divestiture. Over the next three years significant changes were brought in when Hal Rogers took office of Handy Hydraulics in 1988 after the retirement of Mr. Douglas. Hal Rogers paid attention on sales, extensively high payrolls and operating expenses by closing the unfruitful walk-in parts counter, and installing toll-free customer order numbers and updating the company’s catalogue into a “throwaway” version with more concise representation of company’s products and finally changing the name to HH industries. It was essential that the effects of the steps by the new president be taken into account so that analysis could be made about those decisions. In order to analyze clearly what effects would the changes produce on the industry’s current processes it was required that the data analyst should be able to produce analytical reports. These analytical reports would be made using all the raw data available with the company. Presentation of these reports to the higher management would enable them to decide future strategies. This is possible because the analytical reports would show both current and projected trends of the company’s sales and expenditure along with the customer input. The process of analyzing the data is executed by taking samples of data from the company’s data repository. The data may be a record of the order placements, order sizes, sales related data, maintenance, employee payroll, maintenance, external outlets etc over a specific amount of time, and this in this case, is spanned over the entire last fiscal year. Certain data analysis methods have been used to assist the data analyst for analyzing data. These involve recording and sampling of data along with construction of histograms and other graphical representations in order to clearly outline the trends that the industry’s current processes are exhibiting. Tabular arrangements of findings like standard deviation, cumulative frequency, mean, mode and median of samples of data show the degree of stability and instability of processes. METHODS According to Laurel’s experience the total sales dollars were the direct result of two factors: the number of orders per day and average dollar value per dollar. For initial investigation histograms are best to evaluate. The situation here demands the observation and analysis of histograms constructed with different perspectives. Histograms are useful to observe data trends and the initial findings usually become the basis of further analysis. The Histograms required in this current situation here are as follows, Histograms of average order size for quarters 1 and 2 (To observe the trend of sales/order value) Quarterly histograms for total numbers of orders per day to analyze the pattern of incoming orders, to acquire basic statistics related to orders and to compare the quarterly trend with the trend of average order size. The patterns in histogram may indicate the inclined/declined/fluctuating trend that helps in drawing basic conclusions regarding the processes under study. The required charts for the given data and respective frequency distribution tables are given at the end of this discussion. As it is mentioned earlier that histograms usually provide the basis of further investigation. Comparison of the two histograms shown in Chart 1.1 clearly reveals that in the last quarter (Quarter 2) the average order size values are intense in class interval 120 – 140. This intensity was more focused at interval 140 – 160 in the previous quarter (Quarter 1). The backward shift says it all. The number of small orders increased in the last quarters as a result of which the average order size reduced. This is in fact an alarming situation but there are certain measures required to be taken up to ascertain these findings. Chart 1.1 Table 1.1 Frequency distribution of Average Order size with required intervals Quarter 1 Avg Order Size 10 30 50 70 90 110 130 150 170 190 210 Frequency 0 0 0 0 4 16 16 17 5 3 1 Quarter 2 Avg Order Size 10 30 50 70 90 110 130 150 170 190 210 Frequency 0 0 0 0 6 17 26 7 5 2 1 The quarterly charts shown in chart 1.2 confirm the inclined trend of number of daily orders towards the last quarter. This satisfies the finding related to the increase in the number of orders in the last quarters. Following are the quarterly charts for Total numbers of orders per day, Table 1.2 Frequency distribution of Number of daily orders with required intervals Quarter 3 Total Orders per day 105 115 125 135 145 155 165 175 185 195 205 Frequency 0 1 3 4 5 17 15 11 5 1 0 Quarter 4 Total Orders per day 105 115 125 135 145 155 165 175 185 195 205 Frequency 0 0 0 2 3 11 13 12 4 10 9 Quarter 1 Total Orders per day 105 115 125 135 145 155 165 175 185 195 205 Frequency 0 1 2 2 1 8 9 13 9 7 10 Quarter 2 Total Orders per day 105 115 125 135 145 155 165 175 185 195 205 Frequency 0 1 0 0 0 4 9 16 13 12 9 Chart 1.2 This now calls for further investigation as the sales figures are continued to rise and the proportional imbalance between the total daily orders and average order size got disguised. Ignoring this fact and persistence of same practices may lead the company to troubled waters. It would be appropriate to take into account the Laurel’s findings in this regard. Laurel assumed that, There may be new customers joining the company and placing small orders to initiate business with the company and to evaluate company after sales support and incentives. The customers don’t want to maintain large inventories due to expenses incurred in doing so. Smaller service shops are the cause of small volume orders. Adverse winter weather may have caused construction slow down, hence hindering large volume of purchases. Stan the VP sales acknowledged these findings but he was satisfied due to the increasing figures of sales dollars. Laurel had to verify this assumption also. Moreover Laurel thinks that an individual assessment of each profit center should be conducted separately. The central tendency based estimation is a good choice for further investigations in this case. It will help to ascertain the current findings. The Table 2.1 contains the required statistics. The observation proves that Mode is the only measure of central tendency which seems to deliver some results. The first two quarters (3rd and 4th) for the number of daily orders are positively skewed as compared to the last two (1st and 2nd) are negatively skewed. This supports the fact that the numbers of orders in the last quarters had increased. In contrast to this the statistics for the average order size shows all 4 quarters as positively skewed indicating a decline in average order size over the time. To verify Stan’s assumption the sales figures are shown in Table 2.2. Apparently the increase in overall sales with the passage of time may have led him to this assumption but the decline in sales/order value deems it as incorrect. There would be a need of some more tests in order to verify this assumption. The unclear divergence of the statistics acquired separately from profit center 3 left the analysis a bit hanging. The clear cut comments can not be issued at this stage regarding the suitability of the idea of separate investigation of all the 3 profit centers. The statistics are presented in Table 2.3. Table 2.1 (Mean, Median and Mode for all quarters) Number of Orders/day Average Order Size Quarters 3 4 1 2 3 4 1 2 Mean 155.52 171.67 171.26 177.81 133.96 121.00 126.01 119.93 Median 156 168.5 171.5 177 132.35 120.09 125.74 116.71 Mode 150 163 177 191 130 119 124 113 Table 2.2 Company’s total Sales dollars for all quarters Quarters 3 4 1 2 Total Quarterly Sales 1287906 1327508 1346084 1368181 Table 2.3 Figures of Profit Center 3 Quarters 3 4 1 2 Mean number of daily orders 28.17 30.69 31.18 35.06 Mean of daily sales 2731.63 3144.11 3121.19 3328.94 Average order size 96.96 102.46 100.11 94.94 To conclude this analysis and to address the pending issues it seems better to observe and analyze the measures of dispersion through the inter quartile ranges of the average order size in each quarter and Standard deviation, Variance and Coefficient of Variances for all the factors. A separate investigation of each profit center using the same approach may prove to be conclusive. Table 3.1 represents the Inter-Quartile ranges for Average order size and the Total IQR of average order size. The least dispersion is observed in the last quarter to make it consistent. The study of Mean confirms this consistency towards low sale/order value. This again asserted the initial findings. The Standard deviation, Variance and CV for the total number of orders and average order size are presented in Table 3.2. The total number of orders is getting consistent in the last quarter with high mean in contrast to the average order size which is consistent with low mean. Table 3.3 shows the Coefficient of Variation of Total orders/quarters and of average order size/quarters for all the 3 profit centers. The profit center 1 seems to follow the same trend as overall but the rest of the two centers have shown non-negligible divergence. This asserts the fact that Laurel’s idea of separate center based investigation should be considered. At this stage the staff should be made aware of the concrete facts and should be asked for improved data collection to make the analysis more fruitful. Table 3.1 Inter-Quartile Ranges of average order size in each quarter Inter-Quartile Range for Quarter3 37.77 Inter-Quartile Range for Quarter4 26.83 Inter-Quartile Range for Quarter1 33.08 Inter-Quartile Range for Quarter2 20.20 Inter-Quartile Range for ALL 4 Quarters 32.83 Table 3.2 Variance and Standard Deviation by QUARTERS Number of orders Average Order Size Quarters Standard Dev Variance CV Standard Dev Variance CV Q3 16.73 279.83 10.76 28.05 786.64 20.94 Q4 21.63 467.72 12.60 24.13 582.41 20.00 Q1 23.42 548.39 13.67 26.51 702.68 21.04 Q2 18.36 336.95 10.33 24.44 597.08 20.37 Table 3.3 Coefficient of Variation of Number of Orders for All Profit Centers Center 1 Center 2 Center 3 Quarters St Dev CV St Dev CV St Dev CV Q3 20.12 19.57389 7.728 20.98688 5.889 20.90448 Q4 14.22 15.54438 8.925 18.0303 5.24 17.07563 Q1 13.73 14.69707 8.735 18.72013 7.741 24.8292 Q2 10.86 11.62119 8.831 17.91387 6.704 19.11987 Coefficient of Variation of Average Order Size for All Profit Centers Center 1 Center 2 Center 3 Quarters St Dev CV St Dev CV St Dev CV Q3 42.99 27.86673 42.58 41.70013 23.11 23.58404 Q4 43.27 28.85436 25.06 31.90731 32.58 32.06693 Q1 46.66 29.59345 27.39 35.24643 40.98 40.67494 Q2 42 29.45508 29.71 31.38933 26.44 27.74106 This was not just the task to be performed by Laurel. She was a statistician and there were diversified endeavors that caught her. The HH Industries was facing a long time trouble regarding the photocopier machines. The copiers were down more often and the company had to bear different costs due to this. To analyze the gravity of the situation Laurel acquired data related to the photo copiers. The data she received was of 10 months of 25days each. This makes the year duration equals to 250 days. So the analysis conducted here is for a 250 day year with 10 months. The two stage analysis here asks for the verification of the problem and the recommended solutions. Table 4.1 contains the probability and Table 4.2 contains the number of days for which one or both the machines are expected to be down or functional. These tables are formulated through cross tabulation of the given data in MS Excel. According to this table the probability that a machine will be down on a given day is 10.4 % for each machine. In terms of days this figure becomes 26 days for which one machine is expected to be down and 1 day for which both the machines are expected to be down. The details of the expenses the company has to bear in case of the malfunction of these machines are as follows. Service call cost for one machine $68 and for two machines is $100. The cost of copies lost = $7.5 = $0.05 per copy for 150 copies per copier. In this case the total estimated loss = (54 ? $7.5) + (52 ? $68) + $100 = $4041. (as per Table 4.1 and Table 4.2). The futuristic trend analysis reveals through the cube of Table 4.1 that the downtime is expected to be 50% of the total days. This calls for some correct and feasible recommendations for the future. Laurel received two different proposals of acquiring two machines on rent OR buying a new advanced machine capable of replacing both the existing machines. According to the figures presented in Table 6.1 it is recommended to go for the second proposal of buying and installing a new machine. The figures in Table 6.1 support the recommendation for the 3 year duration of futuristic time. The length of year for this analysis is 250 days i.e. 10 months. Table 4.1 (Probabilities) Machine 2 Machine 1 0 1 All 0 0.4 10.4 10.8 1 10.4 78.8 89.2 All 10.8 89.2 100 Table 6.1 Figures and Calculation related to Proposal 1 Figures and Calculations related to Proposal 2 Rent of 2 copiers = $350/month. Yearly rent = $350x10 = $3500/- Per year service call cost = $0 The probability of a machine being down on any given day is = (0.05 x 250) = 12.5 = 13 days approximately. Per year cost of copies lost = $7.5 x 13 x 2 = $195. The yearly cost = $3500 + $195 = $3695 per year. Cost for 3 years = 3 ? $3695 = $11805. Cost of new machine = $8750. The given probability of a machine being down on any given day = (0.017 x 250) = 4.5 = 5 days approx. The cost of copies lost would be doubled = 5 x ($7.5 x 2) = $75. The service cost is covered for the first year by the seller. Cost for the first year = $8750 + 5? $15 = $ 8823 Service call cost for nest 2 years = $175. Cost for subsequent 2 years = 2 ? ($175 x 5 + $15 x 5) = $1895 per two years. Cost for 3 years = $8823 + $1895 = $10718 Now the next problem is related to the choice of suitable number for phone representatives to optimize the handling of customer calls. This area was termed as of great importance by the Head of the company. The rationale behind this claim is that the phone calls actually bring business and new clients for the company. So it would be appropriate to say that it is a LIFE LINE of the company. Laurel in consultation with a colleague and Stan acquired the hourly phone call data for a month. On the suggestions of the Head of the Company and after an affirmative response from Stan, Laurel set a measure that the appropriate number of calls/hour/operator should be 8. The next task was to determine the average number of calls per day which is 28 calls. For this the recommended number of operators would be 4 but Stan being overbooked and dedicated for privileged calls the number suggested was 5 phone reps in all. This settles the question raised in part 6 and 7. Last but not the least is to identify the customers with low purchases, high purchases and average purchases. The identification of the distribution of purchase data is extremely helpful in this regard. The Chart 8.1 (Histogram of Purchases) delivers that idea of a bit skewed NORMAL distribution for which the Mean value is 13706, and the Median and Standard Deviation are 13291.5 and 4397 respectively. The difference between the Mean and the Median proved the fact that the distribution is a bit skewed. Taking the cut off regions on both the sides for purchases less than $100000 and greater than $20,000 it is evaluated through the NORMDIST( ) of MS Excel that about 20% customers fall below the $10,000 mark and about 7% customers bear the high volume accounts that are greater than $20,000. RESULT, CONCLUSION AND RECOMMENDATION Following are the results of analyzing the data of HH Industries. DOUBTFUL STATUS OF SALES The first issue that was highlighted was that the company’s sales were not prospering. This was in contrast to a general understanding among the current management that there was no problem with their sales and that they were doing well. It was suggested that steps such as proper publicity should be taken that would increase the amount of net sales. COSTLY SUBTASKS The second point that was highlighted was that the Photocopier of the company was not being productive but was rather a financial burden on the company’s revenue. It was thus suggested that the photocopier be removed from the company completely as computerized systems and online handling of memos and data would resolve the issue of excessive photocopies. DISORGANIZED INCOMING PHONECALL MAINTENANCE The third issue that was highlighted was that the company was unable to attend to all the phone calls that were being made to it. Analysis revealed that if customer services personnel number handling the phone call was increased to a standard of 4 they would be able to take 8 calls per hour and no calls would be missed by them. RANDOM NATURE OF ORDERS BEING PLACED The final issue that was highlighted was that the orders of higher volumes were very random. Data analysis highlighted the percentage of customers that were placing orders with large volumes and suggested what measures should be taken in order to promote large volumed order buying by the customers. REFERENCES Business Solutions Formulas and Functions with Microsoft® Office Excel 2007 Author: Paul McFedries; Publisher: Que; Pub. Date: March 14, 2007; Print ISBN-10: 0-7897-3668-3 Head First Statistics; Author: Dawn Griffiths; Publisher: O'Reilly Media, Inc.; Pub. Date: August 26, 2008; Print ISBN-13: 978-0-596-52758-7; Pages in Print Edition: 720 Read More