Fictional case: BrightPath Repair's staffing choice
Compare two staffing plans and find the completed-job volume that changes the preferred choice.
Build a compact spreadsheet comparing two staffing plans and identify the sales assumption that changes the owner's decision.
Overview
Elena owns BrightPath Repair, a small appliance-repair business. She must choose staffing for a one-month campaign by Friday. One plan costs more before any repairs are sold but less for each completed repair. The other reduces the initial commitment but charges more as work arrives.
BrightPath, its staffing agreements, and all figures are fictional teaching assumptions.
Recall Lesson 033: Why should a model include both a hand-worked example and a reconciliation check?
The two plans
The campaign offers one standard repair at $120 per job. Parts and consumables cost $30 per completed job. Existing staff will continue their normal work; neither plan displaces it.
| Staffing term | A: Reserved technician | B: Flexible service team |
|---|---|---|
| Fixed fee for the month | $2,400 | $600 |
| Additional labor cost per completed job | $0 | $30 |
| Maximum campaign jobs during the month | 100 | 100 |
The fixed fee buys access to the stated capacity and is payable before the month starts, regardless of sales. Both arrangements end after one month with no renewal obligation. The fees include all staffing costs, setup, and supervision. These are supplied commercial terms, not general employment-cost assumptions.
For this case, quality, scheduling reliability, and customer experience are equal. Both plans can handle the same jobs at the same price. Assume no refunds, rework, other additional expenses, or income taxes. Existing business results remain unchanged.
Elena has $3,000 available after reserving cash for existing obligations. Customers pay on completion. Parts suppliers and the flexible team accept payment after the corresponding customer cash arrives. Thus only the fixed fee needs advance funding.
Demand is the uncertain input
Forty jobs are confirmed and expected to proceed. Elena estimates another 30 from recent inquiries, giving a base scenario of 70 completed jobs. Those inquiries are not commitments. Her downside is 40 jobs and her upside is 90. Treat all three quantities as planning scenarios, not probabilities or guaranteed bounds.
Elena wants the higher additional profit, but she is concerned about committing money before demand is known.
Set up a compact model
Use a spreadsheet or paper. Put plan A in column B and plan B in column C. Put the selected job quantity in F2, initially 70, and the common capacity of 100 in F3.
| Row | Label | Column B | Column C |
|---|---|---|---|
| 2 | Jobs completed | =$F$2 | =$F$2 |
| 3 | Price per job | 120 | 120 |
| 4 | Parts per job | 30 | 30 |
| 5 | Variable labor per job | 0 | 30 |
| 6 | Fixed monthly fee | 2400 | 600 |
| 7 | Revenue | =B2*B3 | Copy B7 across |
| 8 | Total additional cost | =B2*(B4+B5)+B6 | Copy B8 across |
| 9 | Additional profit | =B7-B8 | Copy B9 across |
The dollar signs keep both plans linked to the same quantity input. Copying the other formulas across changes B references to C references.
For a separate hand check at ten jobs, A earns $1,200 revenue and incurs $300 parts plus $2,400 staffing, leaving −$1,500. B incurs $300 parts, $300 variable labor, and $600 fixed cost, leaving zero profit.
ICAEW recommends separating inputs, calculations, and outputs, with visible checks built into a spreadsheet. ICAEW: Good spreadsheet practice
Exercise
Allow 10–12 minutes. Use the supplied layout; no earlier spreadsheet is needed.
- Calculate each plan's base revenue, total additional cost, and profit at 70 jobs. Calculate cash left immediately after its fixed fee.
- Change F2 to 40 and then 90, recording each plan's profit. Which plan leads in each scenario?
- Find the job quantity at which both plans have equal profit. Start with A's extra fixed cost divided by its saving per job. Test one whole job below and above that quantity.
- Add two checks: compare profit with jobs × contribution per job − fixed fee, and confirm jobs do not exceed capacity. Contribution per job means price minus parts and variable labor.
- Recommend a plan in three sentences. Include the strongest alternative, the sales assumption that could change your choice, and a named next action before Friday.
Try the decision before reading on.
Answer
At 70 jobs, revenue is 70 × $120 = $8,400 under either plan. A's cost is 70 × $30 + $2,400 = $4,500, giving $3,900 profit. B's cost is 70 × ($30 + $30) + $600 = $4,800, giving $3,600 profit. A leads by $300.
After the fixed fee, A leaves $3,000 − $2,400 = $600 cash. B leaves $3,000 − $600 = $2,400. Both fit the supplied payment arrangements. These immediate cash balances are different from the month's final profit.
| Completed jobs | A profit | B profit | Higher profit |
|---|---|---|---|
| 40, downside | $1,200 | $1,800 | B by $600 |
| 70, base | $3,900 | $3,600 | A by $300 |
| 90, upside | $5,700 | $4,800 | A by $900 |
A's contribution is $120 − $30 − $0 = $90 per job; B's is $120 − $30 − $30 = $60. At 40 jobs, for example, A earns 40 × $90 − $2,400 = $1,200, while B earns 40 × $60 − $600 = $1,800.
Find where the choice changes.
A commits $2,400 − $600 = $1,800 more in fixed cost. It saves $30 labor on each completed job. Matching that extra commitment requires $1,800 ÷ $30 = 60 jobs.
At 60 jobs, A earns $5,400 − $2,400 = $3,000. B earns $3,600 − $600 = $3,000. This is the point of equal profit between plans, not the sales level at which either business first becomes profitable.
At 59 jobs, A earns $2,910 and B $2,940, so B leads by $30. At 61, A earns $3,090 and B $3,060, so A leads by $30. A is financially preferable above 60 jobs; B below 60, within the model's 100-job limit.
At base demand, a fall of ten jobs removes A's advantage. Of the 40 confirmed jobs, Elena therefore needs more than 20 additional completed jobs for A to lead.
Check and recommend.
For the reconciliation check, put =B2*(B3-B4-B5)-B6 in B10 and copy across. Then =B9-B10 should return zero. At 70 jobs, both routes give $3,900 for A and $3,600 for B. Also test the supplied ten-job example to reduce the chance that the same mistake appears in both routes.
For capacity, =B2<=$F$3 returns TRUE when the job count fits. Copy across. Test 100, which should pass, and 101, which should fail. Use only nonnegative whole-job quantities. Passing these checks confirms particular relationships, not demand or service quality.
A defensible recommendation is: “Choose A if a review supports more than 60 completed jobs, since the 70-job base produces $300 more profit. B is preferable if sales are likely to stay below 60 and leaves $1,800 more cash before customer collections. Elena will review inquiry conversion and confirm both staffing offers before Friday, then track completed jobs and actual costs during the month.”
Choosing B now is also defensible if Elena finds the uncommitted inquiries too weak to support A's higher upfront commitment. State that judgment rather than claiming the spreadsheet proves a probability. Do not average the three scenario profits without justified probabilities.
Recall answer: A hand calculation can catch faulty business logic; reconciliation can catch inconsistent or incomplete totals. Neither alone establishes that every assumption is correct.
Applying this case
Keep the two-plan model, three scenario results, and checks as this week's decision spreadsheet.
Sources
Comments
Loading comments…