Week 5 — instructor solutions

Chi-square testing and simple linear regression

Numbers use the current categorical relationships workbook and regression workbook. Regression output is rounded here for communication; retain full Excel precision for predictions and residual checks.

Download the Week 5 worked Excel workbook.

Categorical and numerical association

Chi-square: Service demonstration and expected-count activity

SolutionSolution 1

Channel	Resolved: Yes	Resolved: No	Total	Resolution rate
Online	54	26	80	67.5%
Phone	60	60	120	50.0%
Total	114	86	200	57.0%

\(H_0\): resolution and channel are independent in the population; \(H_1\): they are associated. Expected counts in row order are 45.6, 34.4, 68.4, 51.6. For example, online/resolved is \(80(114)/200=45.6\). Every expected count exceeds 5. The test statistic is 5.99755, \(df=(2-1)(2-1)=1\), and the right-tail p-value is 0.01433. At 5%, reject \(H_0\): these sampled cases support an association between channel and resolution. The observed resolution gap is 17.5 percentage points. The table does not show that changing channel causes a change in resolution. Including totals in the CHISQ.TEST ranges would duplicate information and invalidate the calculation.

Chi-square: Loyalty workshop transfer

SolutionSolution 2

Tier	Renewed: Yes	Renewed: No	Total	Renewal rate
Bronze	38	42	80	47.5%
Silver	52	28	80	65.0%
Gold	66	14	80	82.5%
Total	156	84	240	65.0%

\(H_0\): tier and renewal are independent in the population; \(H_1\): they are associated. Because each tier has 80 of the 240 customers, the expected counts in each row are 52 yes and 28 no. Minimum expected count is 28. Sum the six contributions to get \(\chi^2=\mathbf{21.53846}\) with \(df=(3-1)(2-1)=2\). CHISQ.TEST returns 0.00002104. At 5%, reject \(H_0\): the data support an association between tier and renewal. Tier membership may reflect prior spending, tenure, or other differences, so the table alone does not identify a causal effect of tier.

Watch me: categorical relationship / Now you try: explain the association

SolutionSolution 3

The outcome is high satisfaction and the groups are app users and non-users. The given rates, 72% and 61%, are conditional because each uses its own group as denominator. The observed gap is 11 percentage points. Plausible confounders include age, prior satisfaction, loyalty or spending. A defensible sentence is: “High satisfaction was 11 percentage points more common among sampled app users; this observational comparison does not establish that using the app increased satisfaction.” A contingency table with group counts would show precision and support further analysis.

Watch me: income and spending / Now you try: age and market price

SolutionSolution 4

For IncomeSpend, put Income on X and Spend on Y. =CORREL(B2:B806,C2:C806) gives 0.7421, a moderately strong positive linear association. Income ranges from AUD 15,006 to 99,960. The scatterplot should be checked for departures from a straight-line trend and unusual residuals before describing prediction quality.

For HouseSize, plot market price (AUD thousands) against house age. =CORREL(C2:C24,A2:A24) gives −0.5766 (since \(R^2=0.3325\) and the fitted slope is negative). The plot supports a negative linear summary, though the 23 properties show substantial spread. Check the scatterplot before assuming linearity.

Now you try: correlation critique

SolutionSolution 5

1. \(r=0\) only rules out a linear sample association; a curved relationship may remain. (2) Correlation alone does not identify causation. (3) Correlation is unit-free, so “0.8 kilograms” is incorrect. (4) An unusual store must be checked for data error or a substantive reason, and any exclusion disclosed; deleting it to improve \(r\) biases the report.

Simple regression

Watch me: regression in Excel / Now you try: interpret IncomeSpend

SolutionSolution 6

In Data > Data Analysis > Regression, set Spend as Y and Income as X, select Labels and residual output. The fitted equation is \(\widehat{Spend}=\mathbf{8254.58+0.295615\,Income}\), both money variables in AUD. At zero income the line predicts AUD 8,254.58, but zero is outside the observed income range and the intercept should not be used as a practical forecast. For an AUD 1 income increase, predicted mean spend increases by about AUD 0.296, conditional on this simple model; for AUD 10,000, by AUD 2,956.15. The positive slope matches the positive correlation. \(R^2=\mathbf{0.5508}\) and the two-sided slope p-value is about \(1.11\times10^{-141}\).

Watch me: predict from the line / Now you try: HouseSize regression

SolutionSolution 7

The demonstration equation \(8254.575+0.296\,Income\) is rounded; for an in-range income of AUD 60,000 it predicts about AUD 26,015. This is a fitted mean, not a promise about an individual’s spend. Using the unrounded fitted slope gives about AUD 25,991.5; the difference illustrates why coefficient precision matters.

For age, the fitted line is \(\widehat{Price}_{000}=\mathbf{1088.03-10.8883\,Age}\), with price in AUD thousands. Each additional year is associated with AUD 10,888 lower predicted price on average; five years correspond to AUD 54,441 lower. \(R^2=\mathbf{0.3325}\), so age explains about 33.25% of sample price variation under this line. The slope p-value is 0.00397, supporting a population linear association subject to assumptions. For the first house (age 35, observed AUD 630,000), fitted price is AUD 706,944, residual −AUD 76,944.

Validation and comparison

Watch me: inspect residuals / Now you try: model audit

SolutionSolution 8

For the age model, calculate fitted prices from the equation and residuals as actual minus fitted. Plot residual against fitted value; mark large residuals, including the observed range of roughly −271 to +399 AUD thousands. Return to the original row before treating any point as erroneous. IncomeSpend residuals range roughly −AUD 18,494 to +AUD 15,685; a strength is its clear positive relationship, while a limitation is considerable individual scatter and omitted factors such as household size. Use the line for approximate in-range mean prediction, with uncertainty; no causal claim follows from these observational data.

Now you try: prediction guardrails

SolutionSolution 9

AUD 500,000 income is far above the observed AUD 15,006–99,960 range. The calculation is extrapolation. Income and spending may not remain linear at that level, so collect comparable high-income observations or use a model validated for that range before making a decision.

Watch me: a defensible regression brief / Now you try: choose the better predictor

SolutionSolution 10

Separate one-predictor model	Correlation	\(R^2\)	Slope	Slope p-value	Residual SE (AUD 000s)
Price on age	−0.5766	0.3325	−10.888 per year	0.00397	186.97
Price on size	0.7913	0.6262	+2.0616 per m²	0.0000069	139.91

Size is the more useful single predictor in these 23 properties: stronger linear correlation, higher \(R^2\), smaller residual error, and a strong slope test. The size line is \(\widehat{Price}_{000}=399.22+2.0616\,Size_{m^2}\). Check plots and unusual points; size alone still omits condition, location and other property features. This comparison fits two separate lines, so neither coefficient is adjusted for the other predictor. A multiple-regression conclusion requires fitting both together.

Retrieval and closing checks

For two categorical variables, compare conditional percentages in a contingency table. Independence means the outcome probability does not change with the grouping event in the population. A small p-value measures incompatibility with a null model under assumptions; it does not establish cause or importance. Scatterplots require two numerical variables, with X as explanatory and Y as response. For regression, report units, equation, \(R^2\), slope evidence, an in-range prediction, a residual and a limitation, as the worked examples above do.