1. Construct an estimate of the average number of complaints filed per quarter. 2. As a manager, you want the accuracy of this estimate to be within 2 complaints of the population mean [i.e. µ is no...

1. Construct an estimate of the average number of complaints filed per quarter. 2. As a manager, you want the accuracy of this estimate to be within 2 complaints of the population mean [i.e. µ is no further than 2 units from ???]. Does your solution from part 1 satisfy this criterion? Why or why not. 3. What is the “best” confidence level you can provide with a budget of $50000 in order to satisfy the accuracy requirement from part 2? 4. What should the budget allocation be to satisfy the criterion from part 2 for an acceptable confidence level?


1 Assignment: Due date Tuesday June 11th 5pm [Submission instructions will be posted separately] Question 1 Refer to the spreadsheet entitled complaints.xlsx for this question. The column of data refers to the number of significant consumer complaints reported by callers to an organisation’s call centre offices during the last quarter. Getting this information involves asking each office to catalogue each complaint they receive and then examining each complaint to see if it is serious enough to be logged in the data collection exercise. It is estimated that this process costs $500 per office. Use the sample standard deviation as the best available estimate of the population standard deviation. 1. Construct an estimate of the average number of complaints filed per quarter. 2. As a manager, you want the accuracy of this estimate to be within 2 complaints of the population mean [i.e. µ is no further than 2 units from ?�]. Does your solution from part 1 satisfy this criterion? Why or why not. 3. What is the “best” confidence level you can provide with a budget of $50000 in order to satisfy the accuracy requirement from part 2? 4. What should the budget allocation be to satisfy the criterion from part 2 for an acceptable confidence level? Question 2 [This question requires you to run regression modeling in Excel] Refer to the spreadsheet entitled consulting.xlsx for this question. A global consulting firm wants to determine what employee characteristics influence requests to include (or not include) them in new project teams. Data about the following attributes were compiled from a sample of employees from offices all over the world. • Employee ID • Team Request: this is a numerical score that rates the level of interest expressed about the employee when new teams are formed. • Positive Reviews: Number of positive customer reviews received about the employee • Salary: Annual Salary of the employee (thousands) • Experience: Number of years of project experience the employee has • Billed Hours: Average quarterly billed hours for the employee • Team size: size of the current work team the employee belongs to • Country of Origin: Country of employee’s office 1. If you used regression modeling to examine this, specify your model (what does it look like)? Clearly classify each variable in the model as either “independent” or “dependent”. 2. Using the data provided, run a regression model while excluding the country of origin variable and answer the following (point to the evidence in the output to support your observations): a. Is the model any good? 2 b. Which variables are important predictors of Team Request? What is the data telling you? 3. Using the “Standard Error” of the Y estimate specified under Regression Statistics in the output as an approximation for the standard error for confidence interval construction, produce a 95% confidence interval for Y for the following case: i. Positive Reviews = 375 ii. Salary = 120 iii. Experience = 3 iv. Billed Hours = 225 v. Team size = 6 4. Modify the model to estimate if the country of origin has an impact on Team Request. Question 3 a. A car manufacturer says its cars cost $100 a year less to maintain than those of its competitors. To test this, a consumer group found the cost of maintaining ten cars for a year, and the mean saving was $79 with a standard deviation of $20. i. State the hypotheses for the consumer group’s test. ii. Test the hypotheses using the information given. What should the consumer group’s conclusion be? b. A company operates two similar factories. There is some disagreement, because people working in each factory think those in the other factory are getting higher wages. A sample of wages was taken from each factory with the following results: Sample 1: size = 45 mean = $250 standard deviation = $45 Sample 2: size = 35 mean = $230 standard deviation = $40 i. State the hypotheses for this test about the equality between the two factories. ii. Test the hypotheses using the information given. What can you say about the wages? Question 4 The datafile, Retail Sales Data.xlsx, contains 11 years of quarterly sales for four kinds of retail establishments, along with non-agricultural employment and wage and salary disbursements. The task is to develop a model for predicting sales of each type using values of employment and wage/salary disbursements. The data sheet shows a matrix of correlations among the variables to be used in the model. a) From the correlation matrix, what are your expectations for each of the four models? How would you rank them based on expected explained variance? State your reasons. b) Which variables would you expect to be significant in each of the four models? c) Run the regression models and highlight any differences to your expectations from (a) and (b). 3 Question 5 A seed packager needs to determine how to combine the wheat portion of a batch of wild birdseed. The table below shows the nutritional content of two seed types – buckwheat and sunflower wheat. The table also shows the minimum nutrition requirement of fat and protein and the maximum amount of roughage allowed in a batch of birdseed along with the unit cost for each type of seed. The packager’s goal is to minimise cost while satisfying the nutritional requirements of the batch of birdseed mix. Nutritional Content Nutritional Item Buckwheat Sunflower Wheat Total requirement for the batch Fat (per kg) 0.04 kg 0.06 kg At least 480kg Protein (per kg) 0.12 kg 0.10 kg At least 1200 kg Roughage (per kg) 0.10 kg 0.15 kg Should not exceed 1500 kg Unit Cost (per kg) $0.18 $0.10 a) Formulate this as a cost minimisation problem. b) Use Excel solver to find the optimal decision. c) What is the optimal cost value? d) Identify the binding and slack constraints and explain what the nature of the binding in each case means. e) For each constraint provide a brief explanation for the associated shadow price value. f) Consider the decision of combining 4000kg of buckwheat with 5000 kg of sunflower wheat. Why would this be considered an infeasible solution? Question 6 A dairy products firm sells two types of products: “Hi-fat” and “Solid”. The profit for Hi-fat and Solid are $250 and $150 per unit respectively. The firm has a total production capacity of 500 units per week regardless of product type. Electronic sales records show that each week, sales of Hi-Fat units never exceed half of the sales of Solid units. The firm must produce at least 350 units of Solid per week as per an agreement with its farmer suppliers. The firm wants to find out how many units of each type of product it should make per week in order to maximise profit. The Answer Report and the Sensitivity Report from Excel Solver are shown below. 4 Answer Report Objective Cell (Max) Cell Name Original Value Final Value $F$5 Profit 0 90000 Variable Cells Cell Name Original Value Final Value Integer $C$3 Hi Fat 0 150 Contin $D$3 Solid 0 350 Contin Constraints Cell Name Cell Value Formula Status Slack $F$6 Capacity 500 $F$6<=$h$6 binding="" 0="" $f$7="" min="" solid="" 350="" $f$7="">=$H$7 Binding 0 $F$8 Relative Sales 50 $F$8>=$H$8 Not Binding 50 Sensitivity Report Variable Cells Final Reduced Objective Allowable Allowable Cell Name Value Cost Coefficient Increase Decrease $C$3 Hi Fat 150 0 250 1E+30 100 $D$3 Solid 350 0 150 100 1E+30 Constraints Final Shadow Constraint Allowable Allowable Cell Name Value Price R.H. Side Increase Decrease $F$6 Capacity 500 250 500 25 150 $F$7 Min Solid 350 -100 350 150 16.66666667 $F$8 Relative Sales 50 0 0 50 1E+30 1. From the Answer Report a. What are the decision variables and what are their optimal values? b. Explain how the Final Value of Profit Value of $90000 is obtained. 5 c. Explain how a Slack value for the Relative Sales constraint of 50 is obtained (I am not looking for an interpretation of this value; explain how the value of 50 can be derived from other entries in the Answer Report?) 2. On a graph: a. Plot the constraints on the graph paper. Label the lines on the graph. Identify the feasible region on the graph by drawing light diagonal lines through the feasible region. b. Show the $90000 iso-profit line on the graph and explain in words below why that is the optimal profit value. 3. From the Sensitivity Report a. Explain in words what the Shadow Price of 250 for the Capacity constraint means. b. Explain in words what the “Allowable Increase” of 25 for this constraint means. c. On the graph from part 2, plot the Capacity constraint at a new value of 550. d. From the graph, explain why the shadow price of 250 does not apply with the new Capacity constraint. Sheet1 TIMEWASAEMPLBDLGAUTOFURNGMER 11193.387973939242960920330363 21217.39002113637475161017425812Key: 31254.49012014392438881107826026 41284.79118013301402981233438081TIME: Time period indicator 51319.88983210411400161022821909WASA: National income wage and salary disbursements ($ billions) 61335