Handy Hydraulics Industries in the USA - Research Proposal Example

Unit Business Data Analysis Assignment “Handy Hydraulics (HH) Industries” due Week 11 (Friday 20/05/’11) Unit coordinator’s name: C F Shoostarian Author’s Name: Department: Submission due date: Week 11 (Friday 20/05/’11) ABSTRACT Handy Hydraulics Industries is a growing company. The company has been doing a fairly good business since its inception. The turmoil period in between its journey ended up in change of management. The new management took some beneficial decisions to take the company out of the loss situation. The steady performance of the company compelled the management to consider new options. To achieve an inclined trend amid a competitive environment the management finds it appropriate to acquire the services of a Business Data Analyst/Statistician. Since there was enormous data recorded already it was hooped that the analysis of this data would more likely produce some beneficial results and recommendations. This report is aimed to conduct the data analysis for the company. This would be done in pursuance of the newly hired business data analyst. This analysis would contain the basic and advanced statistical practices including evaluation and analysis of different statistics. These include Mean, Median, Mode, Standard Deviation, Variance, Coefficient of Variance and Inter-quartile ranges. Since graphical representations play key role in trend analysis, the histograms, PIE charts and Bar charts would also be used. The sampling/grouping of data according to the recommended intervals would also be incorporated. This analysis would be conducted for different types of problems in the company to produce conclusions and recommendations for an overall betterment of the company. The main goal is to achieve optimization either through minimization of cost incurred or maximization of profit and to highlight the possible risks the company may encounter in future. INTRODUCTION The scenario involves the business strategies of an Industry named Handy Hydraulics Limited. This company was founded twenty years ago by the Douglas family. Ever since then the company has steadily grown. The first five years being stable, enabling the company to hold its grounds and establish its mark. The merchandise that Handy Hydraulics dealt with were related to Hydraulics as its name suggests. They were not the manufacturers of hydraulics but mainly were distributors and vendors that repaired spare parts. The most the company did was to pack the goods under their name in accordance with the customer’s orders. Their business prospered where large population of humans inhabited land. This was because more the inhabitants the more was the use of garbage trucks and other construction related machinery in the area. Thus, resulting in requirements of hydraulic parts whenever their need arose. The parts that the company dealt with included hydraulic seals, gauges, pump cylinders and all other related spare parts etc. Through the first few years absence or very little presence of marketing did not inhibit a teady growth of the company sales. The late 1970s and early 1980s saw the application of the Brute Force strategy for marketing. This resulting in an increase in demand of the company’s products but unfortunately the inexperienced family run business could not stand the increase in number of orders and their system collapsed. The headquarters in Florida was not the company’s only outlet. By the early decades two new outlets at Arizona and Ohio had also become operational. However, the lack of proper maintenance and handling of inventory in all the outlets resulted in a havoc. It was decided that the company be sold to BMP Enterprises. METHODS Q1. (a) Construct histograms and relative frequency distributions of the company’s daily average order size (total sales divided by total orders) for quarters 1 and 2. For each chart, use interval widths of 20 and let the first interval run from 0 to 20. After the initial observation of data it is noted that there exists one entry of extraordinary sales value. This entry therefore declared as an outlier hence excluded from the analysis. Q1a. The histogram and the related frequency distributions of the company’s daily average order size (total daily sales divided by total daily orders) for quarter 1. The interval width selected is 20 with first interval run from 0 – 20. average order size Frequency 10 0 30 0 50 0 70 0 90 4 110 16 130 16 150 17 170 5 190 3 210 1 The histogram and the related frequency distributions of the company’s daily average order size (total daily sales divided by total daily orders) for quarter 2. The interval width selected is 20 with first interval run from 0 – 20. average order size Frequency 10 0 30 0 50 0 70 0 90 6 110 17 130 26 150 7 170 5 190 2 210 1 Analysis: The analysis of the above histograms of quarter1 and 2 reveals that the strength of bigger orders seems to be lesser in the second quarter as compared to the first quarter. This shows decline in the sales/order value. (b) Construct similar quarterly charts for the company’s total number of orders per day. Use interval widths of ten, with the first interval running from 100 to 110. Q1b. Histograms and related frequency distributions on quarterly basis. Total number of orders/day is considered. The interval widths are of ten, with the first interval running from 100 – 110. 3rd Quarter. Number of Orders /day Frequency 105 0 115 1 125 3 135 4 145 5 155 17 165 15 175 11 185 5 195 1 205 0 4th Quarter Number of Orders /day Frequency 105 0 115 0 125 0 135 2 145 3 155 11 165 13 175 12 185 4 195 10 205 9 1st Quarter Number of Orders /day Frequency 105 0 115 1 125 2 135 2 145 1 155 8 165 9 175 13 185 9 195 7 205 10 2nd Quarter Number of Orders /day Frequency 105 0 115 1 125 0 135 0 145 0 155 4 165 9 175 16 185 13 195 12 205 9 The histograms of all four quarters show an increasing trend in the number of orders as the quarters progress. (c) What changing patterns are evident in the data from quarter to quarter? What are some possible explanations? Q1c. The patterns in histograms shown in 1a and 1b indicate that the number of orders per day increase with the passing quarters but the average sales/order value declines. This means that the company is getting more orders in the last quarters but with lesser volume of sales in dollars. This may happen due to numerous reasons. New customers with lesser business volume may be one of the reasons. Hypothesis: The hypothesis that the total sales/order value is steady throughout is considered as Null hypothesis in this case. This means that the mean sales/order value is same throughout all the four quarters. Ho: Where are the average sales/order values for quarters 1, 2, 3 and 4 respectively. Ha: At least one mean is significant The test conducted here is ANOVA for the determination of p-value. α = 0.05 One-Way Analysis of Variance Analysis of Variance for TSPD/TOP Source DF SS MS F P QRTR 3 7883 2628 3.95 0.009 Error 248 165157 666 Total 251 173040 Since p-value < α, the H0 is rejected. Therefore significant differences between averages observed. 2. (a) Calculate the mean, median and mode for the quarterly data on number of orders and average order size. Do these numbers support Laura’s intuitive findings from the histograms? Which measure of central tendency seems most appropriate in this situation? Now calculate the company’s total sales dollars for the last four quarters. Is Stan correct in his assumption that the total sales are doing well? Number of Orders Quarters Mean Median Mode 3 155.5161 156 150 Mode < Median < Mean +vely skewed 4 171.6719 168.5 163 Mode < Median < Mean +vely skewed 1 171.2581 171.5 177 Mode >Median > Mean -vely skewed 2 177.8125 177 191 Mode > Median > Mean -vely skewed The Quarters 3 and 4 are +ve skewed whereas the next 2 quarters are –ve skewed subsequently. This shows an increase in the number of orders in the last two quarters. Average order size Quarters Mean Median Mode 3 133.9626 132.3523 130 Mode < Median < Mean -vely skewed 4 121 120.09 119 Mode < Median < Mean -vely skewed 1 126.01 125.74 124 Mode < Median < Mean -vely skewed 2 119.93 116.71 113 Mode < Median < Mean -vely skewed The consistent –ve skewed trend in all the 4 quarters when average sales/order value is considered shows a decline in sales subsequently. Mode seems more appropriate in this situation. The analysis here shows that although there was an increase in the number of orders but the average sales/order value declined. This supports Laurel’s intuitive findings from the histogram. The reasons she stated are, relatively small purchases by new customers, customers willing to maintain small inventory, small volumes of businesses, and adverse weather. Quarters Total Quarterly Sales Average sales/order value quarterly 3 1287906 133.96 4 1327508 120.68 1 1346084 126.01 2 1368181 119.93 Stan’s assumption that the total sales are doing well was due to a slightly increasing trend in the total quarterly sales. But the fact that the average sales/order value declines with each passing quarter deems this assumption as incorrect as the sales dollars cannot be considered as expected reflection of the number of orders. Hence the assumption seems incorrect. 2. (b) Calculate the mean number of daily orders and order size for Profit Centre 3 (Pennsylvania) over the last four quarters. Does this warehouse exhibit trends similar to those of the entire company? Is Laurel’s planned investigation of the performance of each profit centre a good idea? Profit center 3 (Pennsylvania) Quarters Mean number of daily orders Mean of daily sales Average order size 3 28.17143 2731.629 96.9645 4 30.6875 3144.109 102.4557 1 31.17742 3121.194 100.1107 2 35.0625 3328.938 94.94296 Analysis: According to these values this profit center shows a slightly different trend. The daily sales values and the average order size values are fluctuating in contrast to the overall trend. Hence Laurel’s idea of individual investigation of each profit centre seems good. 3. (a) Determine the interquartile ranges of the average order size in each quarter. Compare these to the total range in each case. Inter-Quartile Ranges of average order size in each quarter IQR Quarter3 37.77 IQR Quarter4 26.83 IQR Quarter1 33.08 IQR Quarter2 20.20 IQR Total Range 32.83 Analysis: The maximum IQR is of Quarter 3 and the minimum is of Quarter 2. This means that the variation of Quarter 2 is lesser than other quarters. Average order size seems to be converged towards lower values. (b) Using the raw data, calculate the quarterly sample variance and standard deviation values for both the number of orders and the average order size. Compare the coefficient of variation for each quarter. Number of orders Average Order Size Quarters Standard Dev Variance CV Standard Dev Variance CV Q3 16.72805 279.8276 10.75647 28.04715913 786.6431 20.93655 Q4 21.62674 467.716 12.59772 24.13314063 582.4085 19.99819 Q2 23.41776 548.3913 13.67396 26.5081271 702.6808 21.03629 Q1 18.35615 336.9484 10.3303 24.43524632 597.0813 20.37445 The comparison shows that the dispersion of data points in the data series around the mean is highest in Quarter 1 for number of orders and average order size. The dispersion lessened comparatively in the last quarters for both as well. This shows the consistency in the number of orders and average order size towards lesser values. (c) Looking at each warehouse separately, calculate the coefficient of variation for both the number of orders and average order size for the entire 12-month period. Are there significant differences between the relative dispersions experienced at each profit centre? Coefficient of Variation for 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 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 Analysis: There are differences observed between the relative dispersions experienced at each profit center. (d) How would you present your findings to the staff? What recommendations could you make about promotions, future data collection, etc.? The staff should be told that the increase in the number of orders is not the correct indicator of increase in sales in this case. The Sale/order value decreases and if correct measures are not taken, the trend may cause losses to the company. There must be discount promotions to encourage the customers for bigger volumes of purchases. Moreover the data regarding the business volume of customers must also be collected so that further analysis may be conducted to screen out the privileged customers. 4. (a) Using the data given in Table 2.0, what is the probability that a machine will be down on any given day? The probability matrix 0 1 All 0 0.4 10.4 10.8 1 10.4 78.8 89.2 All 10.8 89.2 100 As per the probability matrix given above the probability that each machine will be down on any given day is 10.4%. The combined probability is approximately 21%. (b) With 250 working days per year, how many days per year would you expect (i) One machine to be down? (ii) Two machines to be down? The days matrix 0 1 All 0 1 26 27 1 26 197 223 All 27 223 250 As per the days matrix given above the expectation for both the machines being down is 1 day. Each machine is expected to be down for 26 days out of 250 days. 5. Calculate the expected yearly cost for the current situation. Average service call cost for a machine = $68 Average service call cost for 2 machines = $100 Cost of copies lost = $150 per copier per day. As per the days matrix given above the expected yearly cost for the current situation = Total cost of copies lost + Total cost of service calls = (2×150+52×150)+ (100+52×68) = 8100+ 3636 = $11,736/- 6. Using a 3-year period for comparison (and ignoring the time-value of money), which is the best alternative for HH industries? Following are the transition matrices for current and subsequent years. Current Year 2nd Year 3rd Year 0 1 0 1 0 1 0 0.004 0.104 0 0.010832 0.082368 0 0.00861 0.066033 1 0.104 0.788 1 0.082368 0.63176 1 0.066033 0.506393 The option of continuing with the current setup doesn’t seem feasible as the working probability for both the machines reduced to 50%. Comparison of proposals: Proposal 1: Rent of 2 copiers per month = $350. Yearly rent (assuming 10 months/year) = $350x10 = $3500/- The probability of a machine being down on any given day is 0.05 which is equal to approximately 13 days out of 250 days. Service call cost = $0 and Cost of Copies lost = 2(13x150) = $3900/- (assuming that the cost of copies lost per copier per day remains steady i.e. $150/-) The yearly cost = $3500 + $3900 = $7400/- per year. For 3 years this would be = 3 x $7400 = $22200/- (assuming that the rent and cost of copies lost per copier per day remains steady for next 3 years). Proposal 2: With a new machine purchased the cost of first year = purchase cost + service cost + cost of copies lost = $8750 + $0 + 5 x $300 = $ 10,250/- The service calls are covered for the first year. The cost of copies lost per copier per day is $150 for current setup. This cost would be double with the new machine replacing both. The down probability amounted to 5 out of 250 days approx. The cost of 2nd year = service call costs + cost of copies lost = $175 x 5 + $300 x 5 = $2375/year. Same would be the cost next year (assuming that the cost of copies lost and service call costs remains steady for the next year). The total cost for 3 years = $10250 + $2375 + $2375 = $15,000/-. Being of the lesser cost of all three options the proposal of buying a new machine seems feasible. 7. (a) Using the data given in Table 3.0, calculate the average number of calls received per hour. Analysis: The mean and variance are very much different. Thus this is evident that the data does not follow Poisson arrivals. The calculated average number of calls per hour form the given data is 28 calls approx. (b) If Laurel wants to be 98% sure that a sales rep only has to deal with eight calls an hour, how many reps should she and Stan recommend? Plainly, to entertain 28 calls / hour the recommendation should be of at least 4 operators. (c) After a little more discussion, Laurel found out from Stan that he handles an average of two calls per hour (new customers, requests for new product lines, complaints, etc.). Does this change the recommendations from part (b)? Excluding Stan, the company needs at least 4 operators to entertain 26 calls/hour for 8 calls/hour/operator. 8. (a) Using the data given in Table 4.0, what distribution appears to describe the customers’ purchases? 8a) According to the statement/requirement of this question, we observed the distribution of purchases in terms of histogram, although it seems a bit skewed but we can assume that the distribution is normal with mean equals to $13706 and standard deviation equals $4397. The said histogram is given below (note: grey and white areas are indicating part(c) computations): (b) What are the mean, median, and standard deviation? Part (b): The required statistic(s) is found as follows: Mean= 13705.89 Median= 13291.5 St. Dev.= 4396.791 The slight difference b/w mean and median indicates that the distribution is a bit +vely skewed but we can assume that the distribution can be approximated into a Normal distribution with above mentioned parameters. (c) Suppose that the active customer accounts are normally distributed with the mean and standard deviation calculated in part (b). (i) What proportion of customers would be expected to have accounts greater than $20,000? (ii) Less than $10,000? (iii) What proportions actually do fall in these ranges? Part(c) (i) Only 7.6% purchases can be observed on the right of purchase value equals $20000. It means that more than $20000 purchases are less than 10% of the total. Part(c) (ii) On the other hand, less than $10000 purchases are approx. 20% b/c this value is comparatively closer to the mean found in part (b). Part(c) (iii) Major percentage of purchases are observed within the range of $10000$ to $20000 and this amount is indicating more than two-third part of the total purchases. This percentage is 72.4%. CONCLUSION: The disguised declining trend in sales/order value was identified through the statistical techniques. The increase in the number of orders was not synchronized proportionally with the average order size. This trend was hidden under the growing sales figures and the responsible person at company was pretty satisfied with that measure. This trend was first identified through histograms and then further verified through the observation of other statistics like Mean, Median, Mode, Standard Deviation, Variance, Coefficient of Variance, Inter-Quartile ranges. Moreover it wasn’t deemed necessary to study each profit center individually. The staff was updated about this factual position and the recommendations regarding future promotions and data collection were issued in the light of this recently know fact. Another problem was related to the utilization of photo copiers (resources of company) and to the daily expenses in this regard. The cost analysis considering the down time probabilities of machine revealed the high expenses which were most likely expected to grow to a larger extent in the next 3 years. To recommend the solution to this problem, two proposals were evaluated. One solution was to acquire photo copiers on rent and other was to purchase a new machine to replace the current setup. After the futuristic cost analysis for 3 years the second proposal was recommended as feasible. The main gateway of business was the department where phone reps handle the incoming business through calls. This area had a lacking for which a comprehensive analysis of data was conducted. The recommended number of phone reps after taking all the aspects into account was determined to be 5. The purchase volumes of the customers were identified to have possessed a Normal distribution. Through this the percentage of large, high and medium volumes of purchases was determined. This would help in the future focus of the promotions. RECOMMENDATIONS: It is recommended that the company should change its promotion policies and correctly identify the business volumes of the customers. The promotions or discounts should be targeted with the objective of acquiring bigger volume of sales from the existing customers. The trust building measures like betterment of after sales services and minimization of response time should be practiced to gain the confidence of new customers and bringing them to the company’s trustworthy clientele. The phone reps should be hired as per the recommendations to ascertain the stability of incoming businesses. The resources that are photo copiers should be replaced with a new photo copier to cut down the unnecessary daily expenses of the company and to save the precious potential of staff for enhanced productivity. The current histogram analysis shows that the number of customers with big business volume is marginal. A new business strategy should be formulated to push these figures to a better threshold. REFERENCES: Winston, Wayne L.. Microsoft Excel 2010: Data Analysis and Business Modeling. Microsoft Press. © 2011. Books24x7. (accessed May 18, 2011) Buglear, John. Quantitative Methods for Business: The A to Z of QM. Butterworth-Heinemann. © 2005. Books24x7. (accessed May 18, 2011) Read More