04-12: Capstone — Business Statistics Project¶

Home | All Projects | Notes | Exercises | Quiz Hub

The whole course in one deliverable. Take a single customer data set and run it end to end: organize and describe, estimate with confidence, test three hypotheses, model the relationship, and write a business report that a manager could act on.

Chapters applied: all thirteen.

Difficulty: ⭐⭐⭐ Advanced

The Data¶

data/customers.csv — 300 customers of a retail business.

Column	Level	Notes
`customer_id`	Nominal	Key
`region`	Nominal	North / South / East / West
`channel`	Nominal	Online / In-store / Phone
`visits_per_year`	Ratio	Count of visits
`annual_spend`	Ratio	The main response variable
`satisfied`	Nominal	Yes / No
`returned_item`	Nominal	Yes / No

The Brief¶

You are the analyst. Management wants answers to five questions:

Who are our customers? Describe spending, visits, and the categorical mix.
How much does the average customer spend? Give an interval, not a point.
Does spending differ by region? Regional managers are arguing about budget allocation.
Is channel related to satisfaction? Marketing wants to know whether to push online.
Can we predict annual spend? Build a model and say how accurate it is.

Deliver a report with a recommendation for each.

Part 1 — Organize & Describe (chapters 01–04)¶

Excel

' quality report
=COUNTA(A2:A301)                                        ' rows
=COUNTA(A2:A301)-COUNTA(UNIQUE(A2:A301))                ' duplicate ids -> 0
=COUNTBLANK(E2:E301)                                    ' missing spend
=UNIQUE(B2:B301)                                        ' region levels
=MIN(E2:E301) & " to " & MAX(E2:E301)                   ' range check

' descriptive summary
' Data ▸ Data Analysis ▸ Descriptive Statistics on annual_spend
'   tick Summary statistics AND Confidence Level for Mean: 95%
=QUARTILE.INC(E2:E301,1) & " / " & MEDIAN(E2:E301) & " / " & QUARTILE.INC(E2:E301,3)
=STDEV.S(E2:E301)/AVERAGE(E2:E301)*100                  ' CV
=3*(AVERAGE(E2:E301)-MEDIAN(E2:E301))/STDEV.S(E2:E301)  ' Pearson skewness

' outliers, the resistant way
=QUARTILE.INC(E2:E301,3)+1.5*(QUARTILE.INC(E2:E301,3)-QUARTILE.INC(E2:E301,1))
=IF(OR(E2<$K$1, E2>$K$2), "OUTLIER", "")

' PivotTable: Rows = region, Columns = channel, Values = Count and Average of spend
' Charts: histogram of spend, boxplot of spend by region, bar chart of channel mix

Deliverable: a one-page profile — how much a typical customer spends, how variable that is, whether the distribution is skewed, and which measures you are therefore reporting.

Part 2 — Estimate (chapters 08–09)¶

Excel

' 95% CI for mean annual spend
=CONFIDENCE.T(0.05, STDEV.S(E2:E301), COUNT(E2:E301))   ' margin of error
=AVERAGE(E2:E301)-$K$5 & " to " & AVERAGE(E2:E301)+$K$5

' 95% CI for the proportion satisfied
=COUNTIF(F2:F301,"Yes")/COUNTA(F2:F301)                 ' p-hat
=NORM.S.INV(0.975)*SQRT($K$7*(1-$K$7)/300)              ' margin of error
=AND(300*$K$7>=5, 300*(1-$K$7)>=5)                      ' condition check

' how many customers would we need to survey for a +/-2% margin?
=ROUNDUP($K$7*(1-$K$7)*(NORM.S.INV(0.975)/0.02)^2, 0)

Deliverable: two intervals, each written as a sentence a manager can quote, plus the sample size needed to halve the margin of error.

Part 3 — Test (chapters 10–12)¶

Three tests, each stated before the data is examined.

#	Question	Test	`H₀`
1	Does mean spend exceed the $250 target?	One-sample t, right-tailed	`μ ≤ 250`
2	Does mean spend differ across the four regions?	One-way ANOVA + Tukey	`μ₁ = μ₂ = μ₃ = μ₄`
3	Are channel and satisfaction associated?	Chi-square independence	independent

Excel

' TEST 1 — one-sample t against the $250 target
=(AVERAGE(E2:E301)-250)/(STDEV.S(E2:E301)/SQRT(300))    ' t
=T.DIST.RT($K$10, 299)                                  ' right-tailed p
=(AVERAGE(E2:E301)-250)/STDEV.S(E2:E301)                ' Cohen's d

' TEST 2 — ANOVA
' Split spend into four columns by region (FILTER or sort), then
' Data ▸ Data Analysis ▸ Anova: Single Factor
' Then Tukey HSD by hand:  q(0.05, 4, 296) ~ 3.66
=3.66*SQRT(MSW/harmonic_mean_group_size)

' TEST 3 — chi-square independence
' PivotTable: Rows = channel, Columns = satisfied, Values = Count
=$H2*I$5/$H$5                                           ' expected counts
=MIN(expected_range)>=5                                 ' condition check
=CHISQ.TEST(observed_range, expected_range)             ' p-value
=SQRT(chi2/(300*MIN(rows-1, cols-1)))                   ' Cramer's V

Deliverable: for each test — hypotheses, conditions checked, statistic, p-value, effect size, interval, and a plain-English conclusion.

Part 4 — Model (chapter 13)¶

Excel

' correlation matrix first
' Data ▸ Data Analysis ▸ Correlation on visits_per_year and annual_spend

' simple regression
=SLOPE(E2:E301, D2:D301)                                ' y FIRST, then x
=INTERCEPT(E2:E301, D2:D301)
=RSQ(E2:E301, D2:D301)
=STEYX(E2:E301, D2:D301)

' multiple regression with dummies -- build them first, in CONTIGUOUS columns
=IF($C2="In-store",1,0)                                 ' channel dummies
=IF($C2="Phone",1,0)                                    '   (Online = reference)
=IF($B2="South",1,0)                                    ' region dummies
=IF($B2="East",1,0)                                     '   (North = reference)
=IF($B2="West",1,0)
' Data ▸ Data Analysis ▸ Regression
'   Input Y = annual_spend, Input X = the contiguous predictor block
'   tick Residuals, Residual Plots, Normal Probability Plots

Deliverable: the fitted model, R² and adjusted R², the significant predictors with their intervals, the residual plot with a verdict on the LINE assumptions, and a prediction interval for one new customer.

Part 5 — Report¶

Write it in this order, because it is the order a manager reads in:

1. EXECUTIVE SUMMARY      three bullets, no statistics jargon
2. DATA & METHOD          what the data is, what was cleaned, which tests and why
3. FINDINGS               one subsection per question, each with the number,
                          the interval, and the plain-English meaning
4. RECOMMENDATIONS        what to DO, tied to a specific finding
5. LIMITATIONS            observational data, no causal claims, sample coverage,
                          any assumption that was borderline
6. APPENDIX               full output, formulas, and the codebook

Important

Every finding needs an effect size and an interval, not just a p-value. "Region matters (p < .001)" is not actionable. "East customers spend $46 more per year on average (95% CI: $21 to $71), which is 18% above the North baseline" is.

R Route¶

Rscript r/analysis.R

Runs all five parts and writes capstone_plots.png. See r/analysis.R.

Python Route¶

python python/analysis.py

Same via pandas + scipy + statsmodels. See python/analysis.py.

Checkpoints¶

The data-quality report comes first, before any statistic
Skewness is assessed, and the centre/spread reported follow from it
Both intervals state their conditions and are written as sentences
All three tests state H₀/H₁ and the tail before the computation
ANOVA assumptions are checked; Tukey runs only because F was significant
The chi-square uses counts, checks expected ≥ 5, and reports Cramér's V
The regression reports adjusted R², coefficient intervals, and a residual plot
Every conclusion is in business language, with a caveat about causation

Extend It¶

Add a logistic regression predicting satisfied from spend, visits, and channel
Add a two-proportion test: is the return rate different online vs. in-store?
Segment customers by spend quartile and profile each segment
Build a customer-value model and identify the top decile by predicted spend
Repeat the whole analysis on a random 50% subsample and see which conclusions replicate