Week 8 — instructor solutions
Integrated supermarket case and revision
ETX1100 / ETX5900
The case answers use the current Week 8 workbook. Each row is one store; model coefficients describe this observational sample. The profit model below is one defensible specification. If students choose another justified reduction, check its equation, comparisons and conclusions against their actual ToolPak output.
Download the Week 8 worked Excel workbook.
Choose a method and audit the case
Watch me: unpack a business question / Now you try: method selection
For manager-retention incentives, the outcome is Profit (AUD); candidate predictors include manager tenure and skill, crew measures, service quality and store context. One store is the unit. Describe distributions and groups, then use scatterplots and multiple regression to assess conditional relationships. The data alone do not show that introducing incentives will cause profit to rise; a pilot with a comparison group, cost estimate and implementation details would strengthen the decision.
| Question | Purpose and method | Key justification |
|---|---|---|
| Percentage open 24 hours | Describe: count Yes/No and divide by 75 | Binary category; 63/75 = 84% |
| Mean service quality above 80? | Infer: one-sample right-sided \(t\) test | Numerical score, unknown population SD; \(H_1:\mu>80\) |
| Which staffing factors predict profit? | Predict/explain: scatterplots and multiple regression | Numerical outcome with several predictors and context controls |
| Best listed price within capacity? | Decide: ToolPak price–demand regression plus formula scenario table | Compare listed prices after whole-unit and capacity rules |
Watch me: audit before modelling / Now you try: data-quality brief
The file contains 75 stores, 13 columns and no missing cells. store is an ID; Profit is AUD; MTenure and CTenure are months; MgrSkill, CrewSkill and ServQual are scores; Res and Hours24 are 0/1 dummies. Pop is a count, Comp numerical, and Visibility and PedCount recorded on small ordered scales; treating the latter as numeric in regression is an approximation to justify. Profit mean is AUD 276,314, median AUD 265,014, SD AUD 89,404, range AUD 122,180–518,998. Use a histogram or boxplot to inspect skew and outliers, plus Profit versus a candidate predictor scatterplot. Target population, sampling frame and measurement definitions are not documented in the workbook; these limit generalisation. Extreme tenure values and the ServQual ceiling at 100 deserve checking, not automatic deletion.
Relationships and service quality
Watch me: correlation matrix / Now you try: prioritise relationships
Run Data > Data Analysis > Correlation on labelled numerical columns; the diagonal should be 1 and the matrix symmetric. Excluding the store ID, the strongest positive correlations with Profit are PedCount 0.450, MTenure 0.439, Pop 0.431; the strongest negative is Comp −0.335. For ServQual, MgrSkill 0.357 is strongest positive; CrewSkill is weak at −0.034. Plot Profit versus MTenure and Profit versus Comp (or PedCount), looking for unusual points and nonlinearity. Pop and PedCount correlate 0.608, a possible collinearity concern if both enter a model. A weak marginal relationship, such as Hours24 with Profit (\(r\approx-0.026\)), can still have a conditional coefficient when other store factors are held constant. Say “in these stores, higher manager tenure tends to accompany higher profit” and “more measured competition tends to accompany lower profit”; neither is a causal finding.
Now you try: compare service groups
Create =IF(M2>=90,1,0) if ServQual is in M; 37 stores meet the threshold and 38 do not. MgrSkill mean is 3.782 for excellent-service stores versus 3.498 otherwise; medians 3.683 versus 3.472. CrewSkill means are 3.434 versus 3.479; medians 3.500 versus 3.425. Side-by-side boxplots and SDs (about 0.39 for manager skill and 0.41 for crew skill in both groups) show overlap. The manager-skill difference is descriptively more pronounced; this classification discards score information and does not establish a causal effect.
Watch me: service-quality model / Now you try: refine the service model
With all four staffing predictors, ToolPak gives
\[\widehat{ServQual}=53.664+0.02281\,MTenure+0.01876\,CTenure+10.1462\,MgrSkill-1.3645\,CrewSkill.\]
At fixed other predictors, one additional manager-tenure month is associated with 0.0228 more predicted service-quality points; one manager-skill unit with 10.15 more. Adjusted \(R^2=\mathbf{0.0906}\), residual SE 12.03 points, Significance F 0.0303. Only MgrSkill has a clearly small individual p-value (0.0054); MTenure 0.376, CTenure 0.824, CrewSkill 0.703. A defensible focused reduction keeps MgrSkill alone:
\[\widehat{ServQual}=47.081+11.0152\,MgrSkill.\]
Its adjusted \(R^2\) is 0.1153, residual SE 11.86, overall p-value 0.00168, and MgrSkill p-value 0.00168. The coefficient is similar to the full model’s 10.15. The simpler model is preferable for an exploratory service-quality summary, while retaining the full model may be justified by a prior staffing theory. Inspect residuals for structure, unequal spread and ceiling effects near 100; both models explain little of the score variation.
Profit and decision
Watch me: profit model / Now you try: decision model
A full contextual model with MTenure, CTenure, MgrSkill, CrewSkill, ServQual, Pop, Comp, Visibility, PedCount, Res, Hours24 has adjusted \(R^2=\mathbf{0.6637}\), residual SE AUD 51,849, and Significance F about \(2.77\times10^{-13}\). Res=1 denotes residential context and Hours24=1 denotes 24-hour operation; their zero categories are bases. In this model, the manager-tenure slope is AUD 684 per month (\(p<0.0001\)), crew-tenure AUD 870 per month (\(p=0.0335\)), and manager-skill AUD 45,197 per score unit (\(p=0.0090\)), all conditional on other included variables. CrewSkill and Visibility have large p-values; Pop and PedCount correlate, so check coefficient stability. Even a statistically clear effect may not cover the cost of an incentive.
A transparent reduced model removes CrewSkill, ServQual and Visibility while retaining tenure, manager skill and store-context controls:
\[\begin{aligned} \widehat{Profit}={}&-131266.99+724.64\,MTenure+806.36\,CTenure\\ &+57347.80\,MgrSkill+3.2705\,Pop-29779.05\,Comp\\ &+31364.34\,PedCount+91706.36\,Res+65092.73\,Hours24. \end{aligned}\]
For example, at fixed other predictors, another month of manager tenure corresponds to AUD 725 more predicted annual profit, another month of crew tenure to AUD 806 more, and a one-unit manager-skill increase to AUD 57,348 more. Res compares residential-context stores with the zero base, and Hours24 compares operating categories, conditional on controls. All retained slopes have two-sided p-values below 0.05. Adjusted \(R^2=\mathbf{0.6498}\), residual SE AUD 52,904, Significance F about \(5.06\times10^{-14}\). Compared with the full model, fit falls slightly but interpretation is simpler; this is an exploratory reduction, not proof that omitted factors are irrelevant. For Store 1, predicted profit is AUD 261,597 versus actual AUD 265,014, residual AUD 3,417. Inspect residuals and influence before using the model for operational decisions.
Now you try: executive recommendation
A five-sentence example: “Pilot a manager-retention incentive in a limited group of comparable stores before any chain-wide decision. In the reduced observational model, an additional month of manager tenure is associated with about AUD 725 more annual store profit at fixed included factors, with strong coefficient evidence and adjusted \(R^2\) of 0.650. That estimated association should be compared with incentive cost and a pre-set minimum worthwhile profit gain. Existing stores may differ in ways the workbook does not measure, so the regression does not establish an incentive’s causal return. Compare pilot stores with a suitable control group, track tenure and net profit, and revisit the policy after a defined period.”
Listed-price revision
Watch me: carry a ToolPak model into a decision / Now you try: compare feasible prices
The stated \(\widehat{Units}=701-5.01\,Price\) is an illustrative rounded ToolPak equation over AUD 50–105. Unit cost AUD 40, fixed cost AUD 1,200, capacity 340, the listed prices and the rule to round predicted units down are business inputs. Apply MAX(0,MIN(ROUNDDOWN(predicted,0),capacity)) before revenue and cost.
| Listed price (AUD) | Predicted units | Planned units | Revenue (AUD) | Cost (AUD) | Profit (AUD) |
|---|---|---|---|---|---|
| 80 | 300.2 | 300 | 24,000 | 13,200 | 10,800 |
| 90 | 250.1 | 250 | 22,500 | 11,200 | 11,300 |
| 100 | 200.0 | 200 | 20,000 | 9,200 | 10,800 |
Recommend AUD 90 among these three listed prices, given these assumptions. None binds capacity. Actual demand, cost or capacity could change the ranking; the result is not a continuous-price optimum.
Misconception clinic and exam practice
Now you try: correct the conditional claim
“Among app users in the sample, 70% are highly satisfied” is the claim supported by the given figure. To calculate \(P(App\mid Satisfied)\), obtain the number who are both app users and satisfied and the total number of satisfied customers, or the full 2×2 table. The denominator changes from all app users to all satisfied customers.
Now you try: repair the interval statement
A correct contextual form is: “Using this sampling method, we are 95% confident that the population mean [named quantity and units] lies between [calculated bounds].” In repeated sampling, about 95% of such intervals capture the fixed mean. A confidence interval for the mean is about uncertainty in the population average; it is not a range containing 95% of individual customer values.
Now you try: repair the coefficient claim
In the reduced profit model, the Hours24 coefficient AUD 65,093 compares predicted annual profit for a 24-hour store (1) with a non-24-hour store (0), holding the other included predictors fixed. The base category is non-24-hour operation. Store selection, location or other unmeasured factors could explain part of the difference, so this is not a causal estimate of extending hours. A numerical slope such as MgrSkill has no base category; it is per one score unit.
Now you try: repair the evidence claim
Use a template: “The estimated effect is [estimate with units], with a [confidence level] interval of [lower, upper]. The test gives [p-value] under its assumptions. Our pre-set business threshold is [amount]; because the plausible gain is [above/below/straddles] that threshold, [decision].” If a statistically clear effect is too small to cover cost or meet the threshold, do not recommend rollout on significance alone. The p-value is not a probability that \(H_0\) is true.
Watch me: decode an exam question / Now you try: rapid method map
The slides leave the eight mini-scenarios to the instructor, so there is no unique numerical answer key. A complete response to each scenario identifies: target parameter or objective; variable types; one matching method; a critical assumption or constraint; the Excel command or formula; and a conclusion in context. Useful mappings span (1) category percentage → count/PivotTable; (2) Normal individual probability → NORM.DIST; (3) mean interval with unknown SD → T.INV.2T; (4) proportion test → null-based z statistic and NORM.S.DIST; (5) categorical association → contingency table, expected counts and CHISQ.TEST after Week 5, with conditional rates for practical interpretation; (6) simple regression → ToolPak Regression, scatterplot and residuals; (7) multiple regression → ToolPak Regression with coefficient and F tests; (8) listed-price choice → ToolPak demand model plus formula table, whole-unit and capacity rules. For every case, report units and avoid causal or global-optimum claims unsupported by the design.