Model structure: Inputs, calculations, and outputs
Separate assumptions, calculations, and decision outputs so a small business model can be checked and updated.
Lay out a small business model with separate assumptions, formulas, and decision outputs, using clear units and time periods.
Overview
A manager at established equipment-service company Vale Services wants to offer extra maintenance visits next month. Her spreadsheet contains a profit figure, but nobody can see which sales estimate it uses.
Both businesses and all figures in this lesson are fictional teaching assumptions.
Recall Lesson 028: Why did Cedar Trail's positive campaign profit fail to justify approval under Rina's stated requirements?
Separate the three parts
A business model, in today's quantitative sense, represents relationships between business quantities. A spreadsheet connects assumptions about work and prices to results such as profit and required hours.
Inputs are values supplied to the model. Some come from records or quotes; others are estimates or management requirements. Record their units, period, source, and status. Sales quantity may be an estimate; a profit target is a requirement.
Calculations apply formulas to those inputs. A formula describes a relationship, such as revenue equals visits sold multiplied by price per visit.
Outputs are the results selected to help someone decide. They usually come from calculations. The calculation area shows the reasoning; the output area highlights decision consequences.
ICAEW's spreadsheet guidance recommends a clear flow from inputs through calculations to outputs, with inputs entered once and formulas easy to inspect. Its principles provide a useful foundation for this layout.
Define the decision and period
Begin with a purpose statement: “Assess whether extra maintenance visits next month can add at least $800 profit within 30 available productive hours.” Name the model owner and when inputs were checked.
Use the same period throughout. Dollars per visit multiplied by visits next month gives dollars next month. Available hours must cover that same month and exclude time already committed elsewhere.
A small model can use three labeled sections on one sheet. Add columns for description, value or formula, unit, and source or explanation. Label inputs and results without relying only on color.
Worked example
Vale's inputs cover next month. Sales are untested; price is proposed. Cost and time estimates come from comparable work; the scheduler confirms available hours.
| Input | Value | Unit |
|---|---|---|
| Visits sold and completed | 20 | visits next month |
| Price | $180 | dollars per visit |
| Variable cost | $80 | dollars per visit |
| Other additional expense | $1,000 | dollars next month |
| Time per visit | 1 | productive hour per visit |
| Available time | 30 | productive hours next month |
| Required profit improvement | $800 | dollars next month |
Variable cost changes with visits completed. The $1,000 expense stays fixed within this plan. Assume one visit type, no displaced work, no other additional expenses or income taxes, and sufficient funding. Time per visit includes travel and preparation. Existing business results stay unchanged.
Build the calculation section in steps:
| Calculation | Arithmetic | Result |
|---|---|---|
| Revenue | 20 × $180 | $3,600 |
| Total variable cost | 20 × $80 | $1,600 |
| Additional profit | $3,600 − $1,600 − $1,000 | $1,000 |
| Required time | 20 × 1 hour | 20 hours |
The output section can show $1,000 additional profit, $1,000 − $800 = $200 above the target, and 30 − 20 = 10 unused hours. These outputs meet the stated requirements if assumptions hold. They do not establish demand.
Keep the connections visible
A spreadsheet cell is one box, identified by its column letter and row number. If visits are entered in B4 and price in B5, the revenue formula =B4*B5 uses those inputs. The asterisk means multiplication; the equals sign starts the formula.
A cell reference points to another cell's value. Referencing the price input lets you change it in one place. Typing =20*180 inside the revenue calculation buries both assumptions. Someone could update the visible price and leave that hidden 180 unchanged.
Likewise, display profit in the output section by linking to its calculated result. Do not type $1,000 there as a separate answer. Keep the $800 target separate from calculated profit.
Separation makes the reasoning traceable, but it cannot repair an unsupported assumption. This model also omits collection and payment timing because funding is assumed sufficient. Remove that assumption and a cash calculation becomes necessary before approval.
Exercise
Allow 7–9 minutes. You own Brook Bicycle Repair and are considering an extra tune-up service next month. Use paper or a spreadsheet to outline three labeled sections.
Assume 40 completed tune-ups at $90 each, variable cost of $35 per tune-up, and $800 of other additional monthly expense. Each tune-up requires 0.75 productive hours, including setup. You have 40 productive hours available after existing commitments and require at least $1,200 additional profit.
Assume full payment, adequate funding, no displaced work, no other additional costs or income taxes, and unchanged existing business results. The sales quantity is an estimate. A supplier then confirms that variable cost will be $45 rather than $35.
- List the seven inputs with their units and period. Identify an estimate and a management requirement.
- Write the formulas for revenue, total variable cost, additional profit, and required time. Calculate the original results.
- Select three outputs for the decision: profit, difference from target, and unused hours. Recalculate them after the cost change.
- Explain which input you would edit, which results should update, and whether the revised proposal meets both requirements.
Try the exercise before reading on.
Answer
The inputs are 40 tune-ups next month, $90 per tune-up, $35 per tune-up initially, $800 next month, 0.75 hours per tune-up, 40 available hours next month, and a $1,200 monthly profit requirement. Sales quantity is an estimate; the profit target is a management requirement.
Revenue = quantity × price = 40 × $90 = $3,600. Total variable cost = quantity × cost per tune-up = 40 × $35 = $1,400.
Additional profit = revenue − total variable cost − other additional expense = $3,600 − $1,400 − $800 = $1,400. Required time = quantity × hours per tune-up = 40 × 0.75 = 30 hours.
| Decision output | Original cost | Revised cost |
|---|---|---|
| Additional profit | $1,400 | $1,000 |
| Profit minus $1,200 target | $200 | −$200 |
| Unused productive hours | 10 | 10 |
Edit the variable-cost input to $45. Total variable cost becomes 40 × $45 = $1,800, and additional profit becomes $3,600 − $1,800 − $800 = $1,000. The extra $10 on each of 40 tune-ups reduces profit by $400.
Revenue and hours stay unchanged because this scenario changes only cost. Formulas should carry the change through to profit and the difference from target. The revised proposal fits capacity but misses the profit requirement by $200. Revise the proposal before approving it.
Recall answer: Cedar Trail's corrected profit missed Rina's minimum target, and producing enough kits to reach it exceeded capacity. A positive profit figure alone left important decision constraints unanswered.
Applying this lesson
Keep this layout as the start of this week's decision spreadsheet. Trace each output through its formula to labeled inputs. Tomorrow adds reusable formulas and cell references.
Sources
Comments
Loading comments…