SAIBT
Quantitative Methods for Business T3 2018
Assignment 2
Due Monday 14 January 2019
• The assignments are to be submitted by 1:00pm on the due date on line. A hard copy is to be handed in during class.
• An assignment submitted late, without an extension being granted, will attract a penalty of 5% (5 marks) per each working day or part thereof beyond the due day and time. Please refer to the Course Information Booklet for the course policy regarding extensions.
• Please remember to fill out and attach a cover sheet. A copy of the cover sheet can be downloaded from the Course web page.
• Please make sure that the pages of your submission are in the correct order and that they are stapled securely in the top left-hand corner only. Please make sure that each page shows your name and the page number.
• Your assignment submission does not have to be typed; neat and legible hand-written solutions are sufficient. Poor presentation will attract a penalty of 10%.
• Please – no plastic sleeves or any type of folder!
• Show all your work!
1. (25 marks) The French Club is a restaurant specialising in ratatouille and beef bourguignon. Prevailing prices in the local market are $18 for a ratatouille and $24 for a beef bourguignon.
The executive chef is reviewing the operation of the kitchen. The restaurant has 2 kitchen assistants that spend 12 hours per service preparing all ingredients for these dishes. The kitchen staffs will need 3 minutes to prepare each ratatouille and 6 minutes for each beef bourguignon. These ingredients will then go to any of the chefs hired by the restaurant. There are a total of 15 chef hours available per service. From experience, each ratatouille will take 5 minutes to cook and 4 minutes for each beef bourguignon. Before the plates are served to diners, it will also need be plated and served. The executive chef has allocated 10 hours for this purpose. Each ratatouille will take 4 minutes to plate and 2 minutes for each beef bourguignon.
Use linear programming to find the maximum possible revenue per service.
Task 1 (5 marks)
Construct a mathematical model for this problem. In doing so, consider the following:
(a) What are the decision variables for this problem?
(b) Using decision variables identified in part (a), formulate the objective function for this problem. Is the quantity of interest to be maximised or minimised?
(c) What constraints are relevant to this problem? Using the decision variables from part (a), formulate those constraint
Task 2 (4 marks)
Use Excel Solver to obtain a solution to the mathematical problem from Task 1. Your submission should include:
• your Excel spreadsheet
• the Sensitivity Report
• the Answer Report
Task 3 (16 marks)
Use your Excel output to answer the following questions:
(a) Describe the linear programming solution to the executive chef of The French Club in terms of:
• The optimum numbers of ratatouille and beef bourguignon to prepare each service.
• The maximum revenue per service.
• Whether all the hours allocated for kitchen assistants will be fully utilised.
• Whether all the time allowed for chefs will be fully utilised.
• Whether all the hours budgeted for plating staff will be fully used.
Which of the Solver reports helps you answer these questions?
(b) What is the maximum profit per service if each chef draws a salary of $30 per hour, kitchen assistant earns $25 per hour, the plating chefs are paid a fixed combined salary of $250 per service and fixed overhead (including salary of executive chef and other expenses) averages $1,000 per service. Which Solver report allows you to answer this question?
(c) The executive chef is hoping to improve the popularity of the beef bourguignon among diners by reducing the price to $22 per serve. Would the solution obtained in Task 2 still be optimal? Which of the EXCEL reports helps you answer this question? Justify your answer carefully. How would the solution and The French Clubs’ profit change, if at all?
(d) In preparation of the upcoming festival, it is decided that one group of employees, either kitchen assistants, chefs, or plating staffs is going to have an additional 3 hours of workload. The group that will do so will be the one that would increase the total revenue per service the most. Use the appropriate Solver report to make that choice. Assume no change to in the original estimates of prices per unit. Make the proposed change to the work hours available and use Solver to generate a new Answer Report. How did the solution change?
Here are a few points to consider while working through this assignment question:
1. The first step is always to work out the mathematical set up for the problem. This means identifying decision variables, formulating the objective function and then formulating constraints. At this stage, we are not trying to solve the problem or work out interactions among constraints. We simply list all conditions that must be satisfied.
When you complete Task 1, you should have two decision variables, the objective function written in terms of those decision variables, and five constraints, also written in terms of decision variables (some using both decision variables, others just one of them).
2. The second step is to find a solution. Task 2 tells you specifically to use Excel Solver to find this solution. The key here is to translate all mathematical expressions from Task 1 into Excel format. Instructions for doing so can be found under Topic 5 in the Excel booklet, as well as in the Linear Programming supplement. In addition, the Lecture notes page in this website gives you access to Excel spreadsheets used to generate Excel output shown in lecture slides for Week 5. It may be worthwhile examining them before attempting Task 2.
3. The final step is interpreting the solution that has been found, which is Task 3.
2. (13 marks) In preparation for an international swimming event in 100 metre backstroke, Michael Phelps tested two different types of swimsuits from his major sponsor.
Samples of 20 swimming times were recorded below using the X-Glide and the X-Fly:
X-Glide 100m backstroke (seconds)
52.52 50.78 50.93 51.02 51.48 51.60 51.75 52.97 53.93 55.45
50.53 50.80 50.97 51.05 51.48 51.60 52.85 53.10 54.15 56.32
X-Fly 100m backstroke (seconds)
50.08 50.52 50.60 50.65 51.10 51.53 51.92 53.30 53.75 52.23
50.10 50.58 50.60 50.72 51.48 51.65 52.10 53.75 54.02 57.55
(a) Use EXCEL to obtain a histogram for each swimsuit (X-Glide and X-Fly).
EXCEL Instructions: Go to the Assignments page on the course web site and download the data file for this question, called Time.xls. Refer to Topic 6 in the Excel Booklet for instructions on how to obtain and format a histogram; use the bins given in Time.xls.
(b) Use EXCEL to obtain Descriptive Statistics, Quartile 1 (Q1) and Quartile 3 (Q3) for each swimsuit.
EXCEL Instructions: Refer to Topic 7 in the Excel Booklet for instructions on how to obtain Descriptive Statistics and the two quartiles.
(c) Based on the histograms in (a), briefly describe the shape (symmetry, modality and outliers) of the data for each swimsuit.
Instructions for identifying outliers:
Whether an observation is an outlier is a matter of judgement. One rule commonly used for identifying outliers is the so-called 1.5 × IQR rule. An observation is suspected to be an outlier if it lies more than 1.5 × IQR below the first quartile Q1 or above the third quartile Q3.
Apply this rule to the data from each swimsuit. Identify suspected outliers (if any) by their exact value(s).
(d) Nominate appropriate measures of centrality and dispersion for the distribution of swimming times for each swimsuit. Give reason(s) for your choice. For each distribution, give and interpret the values of the summary measures you have chosen.
3. (12 marks) To help determine how many beers to stock for the Ashes Cricket Test against England, the bar manager at the MCG wanted to know how the weather affects beer sales. Accordingly, he took a sample of 10 cricket matches and recorded the number of beer sold and the temperature in the middle of the day.
Temperature °C Beer Sales
25.0 6789
23.2 6210
28.5 8455
33.4 13432
26.2 8432
37.5 16453
28.0 9234
31.3 11237
27.8 9154
29.1 9265
a. Identify what should be the dependent and the independent variable for this scenario.
Then use Excel to produce an appropriate scatter diagram that contains the trendline, least squares model and the coefficient of determination R2.
In addition:
• Comment on the relationship between Temperature (0C) and Beer Sales, based on the scatter diagram.
• Comment on the value for R2. Include an interpretation of the meaning of R2 in the context of the data.
b. Interpret the meaning of the slope in this problem.
c. Interpret the intercept and explain if it is meaningful in the context of this problem?
d. Use the regression line equation obtained in part (a) to predict the number of beer sales if the temperature is forecasted to be 350C. In addition, explain briefly whether this prediction is likely to be accurate.
Note! Take particular care in interpreting the value of R-squared and in assessing accuracy of prediction. For full marks, follow the process outlined in the lecture notes.
4. (13 marks) Does the type of meal influence the type of beverage ordered? At a restaurant, the orders of 340 dining customers were analysed. The resulting data are summarised in the contingency table below:
Meal Preferred Beverage
Coffee/Tea Soft Drink Alcohol Total
Breakfast 95 25 18 138
Lunch 19 41 20 80
Dinner 40 27 55 122
Total 154 93 93 340
(a) Use Excel to obtain a 100% stacked column chart for the data from the table. What does your chart suggest between the relationship between type of meal and preferred beverage?
EXCEL Instructions: Refer to Topic 6 in the Excel Booklet for instructions on how to obtain 100% stacked column chart.
(b) Use the contingency table to calculate the following probabilities; include an appropriate probability statement for each case:
(i). The probability that the preferred beverage is Coffee/Tea.
(ii). The probability that the meal type is Dinner and preferred beverage is Alcohol.
(iii). The probability that the meal type is Breakfast or preferred beverage is Coffee/Tea.
(iv). Given the meal type is Lunch, what is the probability that the preferred beverage is Soft Drink?
(v). Given that the preferred beverage is Coffee/Tea, what is the probability that the chosen meal is Dinner?
(c) Are ‘Breakfast’ and ‘Preferred Beverage’ independent or dependent? How do you know? Show all calculations that support your answer.
5. (13 marks) An orange juice producer buys all his oranges from a large orange grove. The amount of juice squeezed from each orange is approximately Normally distributed with a mean of 135mL and a standard deviation of 12mL.
(a) Find the probability that a randomly selected orange will contain less than 120mL?
(b) Find the probability that a randomly selected orange will contain more than 160mL?
(c) Find the probability that a randomly selected orange will contain between 130mL and 160mL?
(d) 80% of oranges will contain less than how many millilitres of juice?
(e) 95% of oranges will contain at least how many millilitres of juice?
Note! For full marks, include an appropriate probability statement for each question.
6. (14 marks) Students investigating the packaging of potato chips purchased bags of Smith’s Original flavoured potato chips from their local convenience store. These bags are marked with a net weight of 28.3 grams. They carefully weighted the content of each bag, recording the following weights (in grams):
(a) Use EXCEL to find the sample mean. Suppose that the population standard deviation for the content of potato chips is known to be 0.40 grams.
(b) Construct a 95% confidence interval for the population mean weight in such bags of chips.
(c) Does the population weight of potato chips have to be Normally distributed here? Explain.
(d) What sampling method was used? Was it a good choice? Explain.
(e) If the true average weight of potato chips is 28.3 grams as stated by the manufacturer, what is the probability of a mean weight exceeding 28.5 grams for a sample of 30 bags? Assume that the population standard deviation is 0.40 grams.
7. (10 marks) In the week before Christmas it was hoped that the mean takings of a shop’s branches would be $40,000. However, 40 randomly sampled branches has mean takings of only $37,000 with a standard deviation of $6,000. Does this suggest that the mean takings of all branches was significantly different from target figure? Test at ? = 0.05.