SO 510 Milestone Two Guidelines and Rubric The final project for this course is the creation of a statistical analysis report. Operations management professionals are often relied upon to make decisions regarding operational processes. Those who utilize a data-driven, structured approach have a clear advantage over those offering decisions based solely on intuition. You will be provided with a scenario often encountered by an operations manager. Your task is to review the \"A-Cat Corp.: Forecasting\" scenario, the addendum, and the accompanying data in the case scenario and addendum. In Module Seven, you will submit your selection of statistical tools and data analysis, which are critical elements III and IV. You will submit a 3- to 4-page paper and a spreadsheet that provides justification for the appropriate statistical tools needed to analyze the company's data, a hypothesis, the results of your analysis, any inferences from your hypothesis test, and a forecasting model that addresses the company's problem. Specifically, the following critical elements must be addressed: III. Identify statistical tools and methods to collect data: A. Identify the appropriate family of statistical tools that you will use to perform your analysis. What are your statistical assumptions concerning the data that led you to selecting this family of tools? In other words, why did you select this family of tools for statistical analysis? B. Determine the category of the provided data in the given case study. Be sure to justify why the data fits into this category type. What is the relationship between the type of data and the tools? C. From the identified family of statistical tools, select the most appropriate tool(s) for analyzing the data provided in the given case study. D. Justify why you chose this tool to analyze the data. Be sure to include how this tool will help predict the use of the data in driving decisions. E. Describe the quantitative method that will best inform data-driven decisions. Be sure to include how this method will point out the relationships between the data. How will this method allow for the most reliable data? IV. Analyze data to determine the appropriate decision for the identified problem: A. Outline the process needed to utilize your statistical analysis to reach a decision regarding the given problem. B. Explain how following this process leads to valid, data-driven decisions. In other words, why is following your outlined process important? C. After analyzing the data sets in the case study, describe the reliability of the results. Be sure to include how you know whether the results are reliable. D. Illustrate a data-driven decision that addresses the given problem. How does your decision address the given problem? How will it result in operational improvement? Guidelines for Submission: Your paper must be submitted as a 3- to 4-page Microsoft Word document and attached spreadsheet with double spacing, 12-point Times New Roman font, one-inch margins, and at least six sources cited in APA format. Instructor Feedback: This activity uses an integrated rubric in Blackboard. Students can view instructor feedback in the Grade Center. For more information, review these instructions. Rubric Critical Elements Exemplary Statistical Tools and Meets \"Proficient\" criteria and Methods: Family of identification demonstrates Statistical Tools nuanced understanding of statistical tools (100%) Statistical Tools and Methods: Category of Provided Data Statistical Tools and Methods: Most Appropriate Tool Statistical Tools and Methods: Justify Tool Statistical Tools and Methods: Quantitative Method Analyze Data: Process Proficient Identifies the appropriate family of statistical tools used to perform statistical analysis, including statistical assumptions (90%) Needs Improvement Identifies a statistical family of tools used to perform statistical analysis but either the tools are not the most appropriate to use or discussion lacks statistical assumptions (70%) Meets \"Proficient\" criteria and Determines the category of Determines the category of demonstrates insight into the the provided data, including the provided data but relationship of categorical justification to support claims category is either inaccurate data and statistical tools (90%) or discussion lacks justification (100%) to support claims (70%) Selects the most appropriate Selects a statistical tool but statistical tool used to analyze selection is not the most the data (100%) appropriate given the data (70%) Meets \"Proficient\" criteria and Justifies why the tool chosen Justifies why the tool chosen justification demonstrates is the most appropriate for is the most appropriate for the insight into the relationship analysis of this data (90%) analysis but justification is between statistical tools and either illogical or cursory type of data (100%) (70%) Meets \"Proficient\" criteria and Describes the quantitative Describes the quantitative description demonstrates method that will best inform method but either the insight into the relationship the decision, including how method selected will not between the quantitative this method will point out the result in the most reliable data method and data relationships relationships between the or discussion lacks how the (100%) data (90%) method will point out the relationships between the data (70%) Meets \"Proficient\" criteria and Outlines the process needed Outlines the process needed offers great detail for each to utilize the statistical to utilize the statistical identified step (100%) analysis (90%) analysis but steps are either inappropriate or overgeneralized (70%) Not Evident Does not determine a family of statistical tools (0%) Value 7 Does not determine a category for the data (0%) 7 Does not select a tool to be used for analysis (0%) 7 Does not justify why a particular tool was chosen (0%) 7 Does not describe the quantitative method (0%) 7 Does not outline the process needed to utilize the statistical analysis (0%) 15 Analyze Data: Valid, Meets \"Proficient\" criteria and Data-Driven explanation demonstrates a Decisions nuanced understanding of how following a process will lead to a valid decision (100%) Analyze Data: Meets \"Proficient\" criteria and Reliability of Results description demonstrates keen insight into identifying reliable data (100%) Explains how following the outlined process leads to a valid data-driven decision (90%) Explains how following the outlined process leads to a valid decision but explanation is inappropriate or cursory (70%) Describes the reliability of the Describes the reliability of the results based on data sets, results but description is including a justification to either cursory or lacks support claims (90%) justification to support claims (70%) Analyze Data: Data- Meets \"Proficient\" criteria and Illustrates a data-driven Illustrates a data-driven Driven Decision illustration demonstrates a decision that addresses the decision that addresses the deep understanding of the problem and operational problem but illustration is interplay between a problem, improvement (90%) either inappropriate or the operation, and operational overgeneralized (70%) improvement (100%) Articulation of Submission is free of errors Submission has no major Submission has major errors Response related to citations, grammar, errors related to citations, related to citations, grammar, spelling, syntax, and grammar, spelling, syntax, or spelling, syntax, or organization and is presented organization (90%) organization that negatively in a professional and easy to impact readability and read format (100%) articulation of main ideas (70%) Does not offer an explanation why following the outlined process leads to a valid decision (0%) 15 Does not describe the reliability of the results (0%) 15 Does not illustrate a decision that addresses the problem (0%) 15 Submission has critical errors related to citations, grammar, spelling, syntax, or organization that prevent understanding of ideas (0%) 5 Earned Total 100% A-CAT CORP. A STATISTICAL ANALYSIS REPORT Prepared By: Anthony Husain Southern New Hampshire University QSO-510 Professor Alethea Duhon 1 TABLE OF CONTENTS INTRODUCTION 3 ANALYSIS PLAN 4 STATISTICAL TOOLS & METHODS 5 DATA ANALYSIS 7 RECOMMENDED OPERATIONAL IMPROVEMENTS 9 REFERENCES 11 2 INTRODUCTION A-Cat Corporation (A-Cat) was one of the leading producers of electrical appliances in India. They are categorized as a medium scale industry, producing the distributing domestic electrical appliances to rural areas, specifically in or around the Vidarbha region, in the State of Maharashtra in India. Since 1986, A-Cat has been manufacturing electrical appliances, relying on a collaborative partnership with Jupiter Inc. for the production of cabinets and with Global Electricals for the manufacture of TV signal boosters and battery chargers. A-Cat's primary flagship product is a voltage regulator of 500 kilovolt amps (KVA) that was labeled and sold under the name VR-500. This voltage regulator is used in households as a protective device for refrigerators and televisions to guard against load variations and power failures; common occurrences in rural India. A-Cat, after suffering declining sales over the last few months, has decided to reevaluate their policy of purchasing and stocking spares and components especially with regards to schedule and stock-in-hand inventory. They store all their spares and components, including transformers, in its factory store. A-Cat's sales division uses data from sales for the last two to three months and also the sales figures of the last two years in the same month to forecast the demand for voltage regulators and to determine the right amount of transformers to keep in inventory. This method of predicting what the optimum level of inventory to keep on hand has been problematic at best. The key internal stakeholders are the Vice-President Arun Mittra, the Operations Manager Shirish Ratnaparkhi and the employees who are responsible for producing the voltage regulators. The key external stakeholders are the customers who purchase the voltage regulators and A-Cat's primary suppliers Jupiter Inc. and Global Electricals. 3 ANALYSIS PLAN The quantifiable factors that may be affecting the performance of the operational processes are as follows: 1. The data stored and used to estimate the levels or inventory or merchandise to keep on hand is significantly outdated, going back older than 2006. Considering changes in technologies and general economic (market) conditions, new updated data that has factored in these technological and economic changes should be used to make more accurate predictions / estimates. 2. A more skilled and efficient workforce should be employed in the manufacturing of ACat's voltage regulators. The right employees should be recruited for the right job. Needs should be met with the correct skillset. Workforce can be reduced without affecting output, quality and performance. The problem that A-Cat is facing and would like to analyze is whether the mean number of transformers needed has changed over the period 2006 - 2008 and if the mean amount needed is less than 745. They are using data that is non-current and basing estimates on previous period's sales. Not taking into consideration that prior sales performance is not indicative of future needs or sales and that refrigerator purchases are infrequent, assessing data from prior periods without understanding the needs of the consumer and the relationship between voltage regulators and refrigerator sales will always lead to the under-stocking over-stocking phenomenon. A strategy that addresses the problem that A-Cat is facing is to study the relationship between the sales of refrigerators and the transformer requirements. Most importantly is establishing if there is a linear relationship between the sale of refrigerators and transformer requirements and how 4 strong is the movement in transformer needs (response) influenced by the sale of refrigerators (predictor). After a statistical (regression) analysis of this relationship and the establishment of the various coefficients, we will know mathematically the relationship between the two variables. We will then understand how much does the sale of refrigerators influence transformer needs. This way, when we have sales forecasts (projections), we can use our model to predict with some degree of certainty the amount of transformers needed and we can adjust that accurately as the sales number change. STATISTICAL TOOLS & METHODS A. The main statistical tools that will be used to perform my analysis is Analysis of Variance (ANOVA) and a regression analysis for future prediction. The assumptions that led to me selecting these tools to use for my data analysis is that the transformers required are in fact dependent on the sale of refrigerators (test of independence) and that a regression analysis will determine how much the sale of refrigerators affects the number of transformers required. It will also let us know via R-Squared, how much of the variance in transformers required can be explained by the variations in sales of refrigerators. This will aid in the prediction of transformers required in the future based on predicted sales of refrigerators. B. The data belongs to the category of time series because the sales figures are increasing over the years, therefore establishing a time trend. There is also a recognizable seasonal variation in the sales figure from 2006 to 2008. This category of data can be analyzed 5 using time series methodologies. The next year's data or projected data is dependent on the previous year's data, so time series analysis is appropriate. C. The most appropriate tools that will be used to analyze the data is an Analysis of Variance (ANOVA) to test the independence or dependence of transformer requirements on the sales of refrigerators and a regression analysis to aid in the establishing of the relationship between sales of refrigerators (independent variable) and transformers required (dependent variable). D. The problem that exists is that there is either an over-stocking of transformers or an under-stocking of transformers. Over-stocking ties up needed capital in inventory and under-stocking delays the timely delivery of orders. My justification in choosing ANOVA and a regression analysis is so that I can establish definitively if the transformers required is dependent or independent on the sales of refrigerators and by how much does the sales of refrigerators influence the number of transformers required. E. Because we are using sales of previous years and the actual number of transformers used over the same period, we are not conducting an experiment with before and after analysis. We are using descriptive research with the idea of establishing an association between our variables. We are neither testing nor establishing causality. This method will statistically provide us with the values from the regression analysis that establishes the relationship, and the strength of the relationship between the two variables. Since we are not making assumptions but rather using actual data from previous years, we will be sure that our data is reliable and our results and predictions are accurate. 6 DATA ANALYSIS A. The process needed to utilize the statistical analysis is: 1. The collection of the data and cleaning the data of outliers and irregularities (standardizing the data). 2. Develop the relevant model(s) and start analyzing the data 3. Collect the analysis output 4. Interpret the output B. Following the outlined (above) process is important because using a method contrary to the outlined will lead to insignificant or inaccurate results that can lead to the wrong decisions being made. In the manufacturing industry, wrong decisions can adversely affect the bottom line and ultimately lead to employee downsizing and or insolvency (bankruptcy) of the corporation. C. First, we will perform a test of independence then we will perform a regression analysis. Setup: H0: Sales of refrigerators and transformer requirements are independent HA: Sales of refrigerators and transformer requirements are not independent From the given results, we have a p-value of 0.003202 for data from 2006-2008 and a pvalue of 1.73969 x10-6. Since these p-values are less than an alpha of 0.05 and 0.01, it therefore means that we can reject the null hypothesis for the alternative hypothesis. The sales of refrigerators and transformer requirements are not independent. Since we have established independence, we will perform a regression analysis to establish the relationship between these two variables and to be able to make more accurate predictions in the future. 7 Setup: In predicting the number of required transformers over time, we want our regression equation to show how the y variable (transformer requirements) changes as sale of refrigerators (x variable) increases by one. It's the sale of refrigerators that affects the transformer requirements and not the other way around. In this case, we will regress the sales of refrigerators on the x-axis (independent variable) against the transformer requirements (dependent variable) on the y-axis. Below is my summary output. SUMMARY OUTPUT Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations 0.925948991 0.857381533 0.849458285 179.467867 20 ANOVA df Regression Residual Total Intercept Sales of Refrigerators 1 18 19 SS MS F Significance F 3485332.925 3485332.925 108.2108645 4.84979E-09 579756.8751 32208.71528 4065089.8 Coefficients Standard Error t Stat P-value 1233.499456 167.475485 7.365253823 7.79144E-07 0.314901799 0.030271902 10.40244512 4.84979E-09 Lower 95% Upper 95% Lower 95.0% Upper 95.0% 881.6465184 1585.352394 881.6465184 1585.352394 0.251302893 0.378500705 0.251302893 0.378500705 i = 1233.499456 + 0.314901799 (Sale of refrigeratorsi) Transformer requirements = 1233.499456 + 0.314901799 (Sale of refrigerators) Interpretation: As sale of refrigerators goes up by 1, the predicted number of transformers required goes up by 0.314901799, ceteris paribus. Interpretation of R-Squared (R2) R2 = 0.857381533 = 85.74% The value of R2 means that 85.74% of the variation in number of transformers required can be explained by the variations in sale of refrigerators. This is a very high or 8 significant percent and as such, it can be concluded that the number of transformers required is significantly influenced by the number of refrigerators sold. The effects of any other lurking variables are not significant. This confirms the reliability of our results. D. From our testing, the mean number of transformers required has increased from the period 2006-2010. Since we are confident that 85.74% of the variations in the number of transformers required is attributable to the variations in the sales of refrigerators, we can use our regression output to correctly predict the needed transformer requirements and thus, the correct amount of component parts needed in its manufacture. Knowing how to correctly predict the needed transformers will allow for the \"guessing\" as to the levels of inventory needed. This will eliminate the \"under-stocking over-stocking\" phenomenon that has been occurring. The operation will be more efficient in that they will have the needed inventory to meet manufacturing requirements without overages or shortages. Revenue will be maximized and the profits can be allocated to other resources that can grow the company. Monies will be spent effectively and efficiently. RECOMMENDED OPERATIONAL IMPROVEMENTS A. My analysis plan begins with an examination of the available data. I will first establish if there is a relationship between the variables (sales of refrigerators and transformer requirement). If the transformer requirement is in fact dependent on the sales of refrigerators, I will use a regression analysis to establish the relationship (how the sale of one refrigerator affects the amount of transformer required). I will also analyze how 9 much of the variance (the strength of the relationship) in transformer requirements is attributable to the sales of refrigerators. B. The problem that has been plaguing the company is either an \"under-stocking\" or \"overstocking\" of inventory needed in the manufacture of voltage regulators. This is because we have looked at past data in trying to predict future needs. The mean number of transformers has increased year after year from 2006-2010. The decision to use a regression analysis is because it will statistically establish the relationship between the variables and allow for more accurate forecasting. We will be able to model the relationship between the two variables. C. Since we have a problem with forecasting, I think that a regression analysis is the best option. Regression analysis is typically used for one (or more) of three purposes: 1. Prediction of target variable (forecasting) 2. Modelling the relationship between X and Y 3. Testing of hypotheses (Regression - The basics) Since we can now more accurately predict the amount of transformers required, we will be able to purchase and store the most accurate amount of inventory needed in the production of voltage regulators. There are several operational improvements that this will add namely: 1. Efficient and timely delivery of required orders 2. Reduced downtime waiting for inventory to be replenished 3. Reduced wages trying to make up for lost time to meet deadlines 4. Available financial resources to address other company concerns thus driving growth and profitability. 10 REFERENCES Gujarati, Damodar N. (2011). Econometrics by Example. Houndmills, Basingstoke, Hampshire: Palgrave Macmillan https://www.iveycases.com/ Regression - The basics. Retrieved from http://people.stern.nyu.edu/jsimonof/classes/2301/pdf/regmback.pdf Sharpe, Norean Radke, et al. (2015) Business Statistics. Upper Saddle River, NJ: Pearson Education Inc. 11 QSO 510 Final Project Case Addendum Vice-president Arun Mittra speculates: We have always estimated how many transformers will be needed to meet demand. The usual method is to look at the sales figures of the last two to three months and also the sales figures of the last two years in the same month. Next make a guess as to how many transformers will be needed. Either we have too many transformers in stock, or there are times when there are not enough to meet our normal production levels. It is a classic case of both understocking and overstocking. Ratnaparkhi, operations head, has been given two charges by Mittra. First, to develop an analysis of the data and present a report with recommendations. Second, \"to come up with a report that even a lower grade clerk in stores should be able to fathom and follow.\" In an effort to develop a report that is understood by all, Ratnaparkhi decides to provide incremental amounts of information to his operations manager, who is assigned the task of developing the complete analyses. A-Cat Corporation is committed to the pursuit of a robust statistical process control (quality control) program to monitor the quality of its transformers. Ratnaparkhi, aware that the construction of quality control charts depends on means and ranges, provides the following descriptive statistics for 2006 (from Exhibit 1). 2006 Mean Standard Error Median Mode Standard Deviation Sample Variance Kurtosis Skewness Range Minimum Maximum Sum Count 801.1667 24.18766 793 708 83.78851 7020.515 -1.62662 0.122258 221 695 916 9614 12 The operations manager is assigned the task of developing descriptive statistics for the remaining years, 2007-2010, that are to be submitted to the quality control department. A-Cat's president asks Mittra, his vice-president of operations, to provide the sales department with an estimate of the mean number of transformers that are required to produce voltage regulators. Mittra, recalling the product data from 2006, which was the last year he supervised the production line, speculates that the mean number of transformers that are needed is less than 745 transformers. His analysis reveals the following: t = 2.32 p = .9798 This suggests that the mean number of transformers needed is not less than 745 but at least 745 transformers. Given that Mittra uses older (2006) data, his operations manager knows that he substantially underestimates current transformers requirements. She believes that the mean number of transformers required exceeds 1000 transformers and decides to test this using the most recent (2010) data. Initially, the operations manager possessed only data for years 2006 to 2008. However, she strongly believes that the mean number of transformers needed to produce voltage regulators has increased over the three-year period. She performs a one-way analysis of variance (ANOVA) analysis that follows: 2006 779 802 818 888 898 902 916 708 695 708 716 784 2007 845 739 871 927 1133 1124 1056 889 857 772 751 820 2008 857 881 937 1159 1072 1246 1198 922 798 879 945 990 Anova: Single Factor SUMMARY Groups 2006 2007 2008 Count Sum Average Variance 12 9614 801.1667 7020.515 12 10784 898.6667 18750.06 12 11884 990.3333 21117.88 ANOVA Source of Variation Between Groups Within Groups SS 214772.2 515773 Total 730545.2 df MS F P-value F crit 2 107386.1 6.870739 0.003202 3.284918 33 15629.48 35 The results (F = 6.871 and p = 0.003202) suggest that indeed the mean number of transformers has changed over the period 2006-2008. Mittra has now provided her with the remaining two years of data (2009 and 2010) and would like to know if the mean number of transformers required has changed over the period 2006-2010. Finally, the operations manager is tasked with developing a model for forecasting transformer requirements based on sales of refrigerators. The table below summarizes sales of refrigerators and transformer requirements by quarter for the period 2006-2010, which are extracted from Exhibits 2 and 1 respectively. Sales of Refrigerators 3832 5032 3947 3291 4007 5903 4274 3692 4826 6492 4765 4972 5411 7678 5774 6007 6290 8332 6107 6792 Transformer Requirements 2399 2688 2319 2208 2455 3184 2802 2343 2675 3477 2918 2814 2874 3774 3247 3107 2776 3571 3354 3513