All values use the current Week 7 modelling workbook. Run Data > Data Analysis > Regression in desktop Excel first. The workbook’s Decision model sheet is a student working copy, so coefficients and formulas must be entered. Keep full ToolPak coefficient precision when filling the scenario table; rounding the slope to −5.01 can alter whole-unit results.
Download the Week 7 worked Excel workbook.
Pilot and demand model
Watch me: inspect the pilot / Now you try: data and decision audit
The observational unit is a comparable store-week; there are 24 rows. Price is X (AUD), units sold Y (whole units). Observed prices are AUD 50–105, with two observations at each listed price. The scatterplot is strongly downward and approximately linear, with modest spread around the line. Check all rows for missing or misplaced values. A line is useful for summarising demand over the tested range, but future demand can differ because of season, promotion, competitors or imperfect comparability. The pilot does not supply unit cost, fixed cost or weekly production capacity; those come from business assumptions.
Watch me: run Regression / Now you try: explain the regression
Using Units_sold as Y and Price_AUD as X, ToolPak gives
\[\widehat{Units}=\mathbf{701.033800-5.011189\,Price_{AUD}}.\]
Within the studied range, a one-AUD higher price is associated with about 5.01 fewer predicted units per comparable store-week. At AUD 80, predicted units are 300.139. \(R^2=\mathbf{0.99467}\); the slope p-value is extremely small. A strong pilot fit does not ensure that future store-week demand will follow the same relationship. Avoid predictions beyond AUD 50–105 without new evidence.
Watch me: validate before using coefficients / Now you try: output quality check
For the first two pilot rows (both price AUD 50), fitted units are 450.474. Observed units are 442 and 457, so residuals are −8.474 and +6.526. The full pilot residuals range about −9.14 to +9.64 units. Confirm ToolPak output uses the correct Y/X roles, compare these rows with its residual output, inspect residual-versus-price for structure, and retain coefficient precision. The current listed-price predictions are positive.
Spreadsheet decision model
Watch me: apply the capacity rule / Watch me: calculate profit / Now you try: complete the scenario model
Use =MAX(0,MIN(ROUNDDOWN(B13,0),$B$6)) in C13. Then D13 =A13*C13, E13 =$B$5+$B$4*C13, F13 =D13-E13; fill down. An audit formula in G13 can be =IF(B13<0,"Negative prediction",IF(ROUNDDOWN(B13,0)>$B$6,"Capacity binds","OK")). The first row has predicted demand 450.474, planned units 340, revenue AUD 17,000, cost AUD 14,800, profit AUD 2,200. The independent profit check is \((50-40)\times340-1200=2200\). =ROUND(B13,0) is valid Excel but violates the stated round-down rule; using uncapped predicted units in revenue also violates capacity.
| 50 |
450.474 |
340 |
2,200 |
Yes |
| 55 |
425.418 |
340 |
3,900 |
Yes |
| 60 |
400.362 |
340 |
5,600 |
Yes |
| 65 |
375.307 |
340 |
7,300 |
Yes |
| 70 |
350.251 |
340 |
9,000 |
Yes |
| 75 |
325.195 |
325 |
10,175 |
No |
| 80 |
300.139 |
300 |
10,800 |
No |
| 85 |
275.083 |
275 |
11,175 |
No |
| 90 |
250.027 |
250 |
11,300 |
No |
| 95 |
224.971 |
224 |
11,120 |
No |
| 100 |
199.915 |
199 |
10,740 |
No |
| 105 |
174.859 |
174 |
10,110 |
No |
Watch me: find the best listed scenario / Now you try: defend a recommendation
At baseline assumptions, AUD 90 gives the highest predicted profit among the listed prices: AUD 11,300 from 250 planned whole units. Capacity does not bind there. The nearest listed alternatives are AUD 85 at AUD 11,175 (AUD 125 less) and AUD 95 at AUD 11,120 (AUD 180 less). A change in unit cost or capacity can reverse the ranking. This is a comparison of the supplied listed options under the model, not a claim about every possible price.
Sensitivity and managerial brief
Watch me: stress-test one assumption / Now you try: two stress tests
Restore baseline before each change. Raising unit cost to AUD 50 leaves capacity at 340 and changes the best listed price to AUD 95: 224 units and AUD 8,880 predicted profit. Setting capacity to 200 while restoring unit cost to AUD 40 changes the best listed price to AUD 100: 199 units and AUD 10,740 profit. Relative to baseline, the profit falls by AUD 2,420 in the cost case and AUD 560 in the capacity case; the cost increase has the larger profit impact, while the capacity reduction moves the chosen listed price farther. Do not change both inputs simultaneously when reporting these separate tests.
Group brief: recommend a launch price / Now you try: team working time
A model brief could read: “The 24 store-week pilot yields \(\widehat{Units}=701.034-5.01119\,Price\), with \(R^2=0.9947\) and the AUD 50 residual check of −8.47 units. We use whole units rounded down, cap sales at 340, and calculate profit as \((Price-40)\times Units-1200\). Of the twelve listed prices, AUD 90 gives the largest baseline predicted profit, AUD 11,300; AUD 85 gives AUD 11,175. With unit cost AUD 50, AUD 95 is best at AUD 8,880; with capacity 200 and baseline cost, AUD 100 is best at AUD 10,740. Trial AUD 90 subject to monitoring actual units, costs and competitor response, and revisit if demand or capacity differs materially.” Include the completed formula table or a clearly labelled extract.
Watch me: critique a decision memo / Now you try: improve the memo
Check one row independently using \((Price-UnitCost)\times PlannedUnits-FixedCost\), verify the recommendation is a listed price, and qualify “expected” or “predicted” profit rather than promising realised profit. State the whole-unit and capacity rules and compare each sensitivity with the same baseline. A useful revision adds a monitoring trigger, such as revisiting the price if actual weekly units at AUD 90 are consistently far from the 250 forecast. The pilot does not establish a causal price effect across all future conditions or a global optimum.
Retrieval and closing checks
Units sold is the demand-regression outcome; price is the predictor. The intercept is predicted units at zero price, outside this pilot’s range and not a useful decision forecast. The price slope is predicted units change per AUD 1 within the model. A fitted value is the model’s estimate of mean units; actual sales also include residual variation. Staying within AUD 50–105 avoids extrapolation. The decision’s inputs are costs, capacity and ToolPak coefficients; its objective is predicted profit; its rules are whole units, non-negative sales and capacity. The best listed baseline option is AUD 90, subject to the assumptions and monitoring conditions above.