Cumulative review: Audit the model
Audit a campaign model for unit, margin, reference, and decision errors before recommending approval.
Find planted errors involving units, percentage denominators, formula references, and decision assumptions, drawing on weeks 1–4.
Overview
You manage a branch of established distributor Larch Supply. A supervisor proposes a one-month tool-kit campaign. Check the spreadsheet and its recommendation before approving.
Allow two minutes for recall, thirteen for the audit, and ten for answers. Use paper and a calculator.
Larch Supply, its policies, and all figures are fictional teaching assumptions.
Recall: why should a recommendation address customer needs and operating constraints as well as profit? Why should an unrecoverable past payment not favor proceeding?
The campaign inputs
The kit combines tools customers otherwise buy separately. Demand at the proposed price is untested. Twenty orders are confirmed and expected to proceed; further sales are estimates.
All three scenarios share these inputs:
| Cell | Input | Value |
|---|---|---|
| H2 | Selling price, dollars per kit | 100 |
| H3 | Complete product cost, dollars per kit | 60 |
| H4 | Other campaign expense, dollars this month | 1800 |
| H5 | Packing and checking, minutes per kit | 45 |
| H6 | Available productive hours this month | 60 |
| H7 | Cash available for the campaign, dollars | 6200 |
Product cost is also the full variable cost: it changes with kits sold. The $1,800 expense is fixed for the campaign. Available time and cash exclude existing commitments. No other work is displaced, and no additional packing time or funding is available.
Assume all produced kits sell and are paid for in full, with no returns, other additional expenses, or income taxes. Product costs and the campaign expense must be paid before customer receipts. Existing business results stay unchanged.
Only the branch manager can approve. The manager requires at least $900 additional profit in the base scenario, within cash and capacity limits. Approval also requires credible demand evidence.
The company previously paid $400 for nonrefundable artwork with no alternative use or resale value. This is separate from future campaign costs and the available cash above.
The draft model
Columns B, C, and D contain Downside, Base, and Upside. Row 2 contains quantities of 50, 70, and 90 kits. Cells I2 and J2 are empty.
The draft contains these formulas and claims:
| Item | Draft entry |
|---|---|
| Revenue in B3, copied across | =B2*H2 |
| Product gross-margin claim | (100-60)/60, reported as 66.7% |
| Required hours in B4, copied across | =B2*$H$5/100 |
| Recommendation | “Approve: the base earns $1,000, all scenarios fit our resources, and the downside guarantees at least $200 profit. We should recover the $400 already spent.” |
The supervisor calculated the quoted profits by hand. They do not establish that the spreadsheet works.
Review questions
- Repair the revenue formula so it can be copied across. Write its base-column version and calculate all three revenues. Explain what happens to the uncorrected H2 reference.
- Calculate gross profit per kit and gross margin. Identify what the draft's 66.7% actually measures. Calculate each scenario's additional profit after the $1,800 expense.
- Correct the hours formula. Calculate hours and upfront cash required in each scenario. Which scenarios fit both limits? Calculate maximum capacity in whole kits.
- Specify two checks with numerical expected results: one independent hand calculation and one operating-limit check. Explain why adding the three scenario profits is unsuitable as a monthly total.
- Write a three-sentence recommendation. Include the corrected base result, the strongest limitation, and a named next action. Address the claimed guarantee and the $400 payment.
Useful relationships: revenue = quantity × price; additional profit = quantity × (price − variable cost) − fixed expense; upfront cash required = quantity × variable cost + fixed expense.
Try the audit before reading the worked answers.
Answer
Use =B2*$H$2 in B3. Copied to C3, it becomes =C2*$H$2: the quantity follows the scenario while the price stays in H2. Dollar signs fix the column and row when copying. The original formula would become =C2*I2, then =D2*J2, pointing to empty cells rather than the common price.
Revenues are 50 × $100 = $5,000; 70 × $100 = $7,000; and 90 × $100 = $9,000.
Gross profit per kit is $100 − $60 = $40. Gross margin divides this by revenue: $40 ÷ $100 × 100 = 40%. The draft divides by product cost, calculating markup: $40 ÷ $60 × 100 ≈ 66.7%. Neither measure deducts the separate $1,800 campaign expense.
Additional profits are 50 × $40 − $1,800 = $200; 70 × $40 − $1,800 = $1,000; and 90 × $40 − $1,800 = $1,800. The base exceeds its target by $100.
There are 60 minutes in an hour. Correct B4 to =B2*$H$5/60 and copy across. Forty-five minutes is 0.75 hours, not 0.45 hours. At 70 kits, the draft shows 31.5 hours; the corrected requirement is 52.5 hours.
| Corrected result | Downside: 50 | Base: 70 | Upside: 90 |
|---|---|---|---|
| Additional profit | $200 | $1,000 | $1,800 |
| Required hours | 37.5 | 52.5 | 67.5 |
| Upfront cash required | $4,800 | $6,000 | $7,200 |
| Fits both resource limits? | Yes | Yes | No |
For the base, cash required is 70 × $60 + $1,800 = $6,000. That leaves $6,200 − $6,000 = $200 before collection. Its $1,000 profit is a different measure from this cash balance.
Capacity is 60 hours × 60 minutes per hour ÷ 45 minutes per kit = 80 kits. The upside needs ten more kits than capacity and $1,000 more than available cash. Its calculated profit describes an infeasible plan.
For the independent check, calculate base profit as revenue minus separate costs: $7,000 − $4,200 − $1,800 = $1,000. Compare the model's base profit with this expectation; the difference should be zero.
For a capacity check, test required hours against 60. At 80 kits, 80 × 45 ÷ 60 = 60 hours should pass. At 81 kits, 60.75 hours should fail. A separate cash check would compare each scenario's required funding with $6,200. Passing capacity alone is insufficient.
ICAEW recommends visible checks and systematic testing of spreadsheet outputs, including changes to inputs. Source: ICAEW
Adding $200 + $1,000 + $1,800 gives $3,000, but these are mutually exclusive scenarios for one campaign. The sum is not monthly profit. Nor does the downside establish a guaranteed minimum: actual demand could be lower or costs higher.
A suitable recommendation is: “Hold approval pending demand evidence: the corrected base earns $1,000, needs 52.5 hours and $6,000 upfront, and meets the stated numerical limits. The upside exceeds capacity and cash, while the downside is a scenario rather than a guarantee; the unrecoverable $400 does not favor proceeding. The supervisor will verify demand beyond the 20 confirmed orders and return a corrected model for the branch manager's decision.”
Recall answer: Profit depends on delivering something customers value using available resources. A calculation cannot establish willingness to buy or remove an operating constraint. The artwork payment is a sunk cost, meaning a past amount that today's choice cannot recover; keep it in historical records while comparing future consequences.
Applying this review
Keep the corrected table, two checks, and recommendation as this week's decision model. Record one error to watch for. Next week examines probabilities and how evidence changes decisions under uncertainty.
Sources
Comments
Loading comments…