The task and original case
Model how fixed cost per unit changes when volume falls. This finance practice project is an allocation worksheet with declared assumptions. It should explain why the same fixed total spreads differently, without claiming an actual company's costs or a pricing recommendation.
| Case element | Value or requirement |
|---|---|
| Inputs | Fictional fixed cost 120 and variable cost 3/unit; compare volumes 40 and 20. |
| Build | An allocation workbook and a volume-sensitivity explanation. |
This is an original invented exercise. It is independent practice, with no employer assessment or open-vacancy claim. Download the inputs, attempt your own result, then open the separate answer key.
Data and worked inputs
Build a result another person can follow
Set fixed cost to 120 and variable cost to 3 per unit. Create scenarios for 40 units and 20 units. Calculate total variable cost as volume ×3, total modeled cost as 120 plus that variable amount, and cost per unit as total modeled cost divided by volume.
Keep fixed total and variable rate in separate labelled input cells. A PivotTable may summarize scenario rows, but inspect Sum versus Count according to Microsoft's guidance. Do not average the scenario per-unit costs to answer a pooled-volume question. This exercise assumes the fixed amount and variable rate stay unchanged across the two volumes; that is a model choice you must state.
Sources[1]
Check the worked result
At40 units, variable cost is 120 and total cost is 240, giving 6 per unit. At20 units, variable cost is 60 and total cost is 180, giving 9 per unit. The variable cost per unit remains 3, while allocated fixed cost rises from 3 to 6 per unit.
The lower-volume scenario's total cost is smaller, but its per-unit cost is larger. Show both measures together so the reader cannot mistake a cheaper total for a cheaper unit. Neither result establishes revenue, profit or the price a customer would accept.
Make the failure visible
Set volume to zero. The fixed total remains 120 under this model, but cost per unit is undefined because there are no units to allocate it across. Do not fill the result with zero. Also test a volume that triggers additional fixed capacity; if that assumption changes, use a separate scenario rather than keeping the original formula unnoticed.
Revise it and ask someone to reproduce it
Ask a peer to calculate the 30-unit case before opening your answer: variable 90, total 210 and cost per unit 7. Then change fixed cost to 150 and compare all three volumes. Save the assumptions with each revision so differences remain attributable to volume or cost inputs rather than accidental cell edits.
- Total cost and per-unit cost are both displayed.
- Fixed cost and variable rate remain separate inputs.
- Zero-volume and changed-capacity scenarios are explicitly handled.
Describe the work accurately
After completing your own version, you could adapt this project bullet: “Built an independent fixed-and-variable cost allocation model with volume sensitivity, visible assumptions and an undefined zero-volume state.”
Use Projects or Independent practice as the heading. Keep the input files, result and revision together. Describe only your own completed work; the invented numbers are model outputs, not employer results or measured business impact.