Practice and Answer Key: Describe, Correlate, Predict and Test a Kolhapur Store

Suppose Rani manages a Kolhapur dark store. She has 10 days of discount and orders in A1:C11 (Day, Discount %, Orders) and delivery minutes for 8 morning and 8 evening orders in E1:F9 (Morning, Evening). All data is fictional practice data.

Exercise (one under the other):

1. Calculate the mean, median and sample standard deviation of Orders.
2. Calculate the correlation between Discount and Orders and describe it in words.
3. Fit a line with SLOPE and INTERCEPT and predict orders at a 12% discount.
4. What share of the variation in orders does the line explain (R²)?
5. Are evening deliveries slower than morning ones? Use T.TEST (two-tailed, type 3) at 5%.
6. Write a two-sentence summary for Rani's manager.

Expected result (check your numbers):

Answer key (open after you try)

1. =AVERAGE(C2:C11) = 257.2, =MEDIAN(C2:C11) = 256.5, =STDEV.S(C2:C11) = 28.5455.
2. =CORREL(B2:B11,C2:C11) = 0.9921: a very strong positive correlation. Higher discounts went with more orders.
3. =SLOPE(C2:C11,B2:B11) = 3.5822 orders per discount point, =INTERCEPT(C2:C11,B2:B11) = 212.4222. Prediction: =FORECAST.LINEAR(12,C2:C11,B2:B11) = 255.41, about 255 orders.
4. =RSQ(C2:C11,B2:B11) = 0.9843, so about 98% of the day-to-day variation.
5. =T.TEST(E2:E9,F2:F9,2,3) = 0.0017, below 0.05. Evening deliveries took 3.125 minutes longer on average, and the difference is statistically significant.
6. For example: "Each extra discount point went with about 3.6 more orders over these 10 days (R² 0.98); this shows association, not proof that discounts caused all of it. Evening deliveries are about 3.125 minutes slower than morning ones (p = 0.002)."

Common mistakes:

Ravindra Bagale's rule: a number without its spread, its range or its p-value is only half an answer.