A spreadsheet exercise is easier to review when a second person can trace the output back to an assumption. Start with a small model that answers the question, then add the necessary detail. Elaborate formatting cannot compensate for a broken revenue formula or an unexplained starting balance.
Build in a deliberate order
- Write the decision, period and units at the top of the file.
- Preserve the supplied source data and reconcile its totals.
- Put assumptions in a visible section with definitions and sources.
- Calculate operating drivers before financial outputs.
- Add checks and a short recommendation.
For an employer's assessment, follow its instructions on tools, assistance and permitted resources. These practice materials are for preparation, not for misrepresenting independent work during a live assessment.
A service-business model
Assume 20 consultants, 160 available hours each, 70% billable utilization and a $150 realized hourly rate. Monthly compensation including benefits is $200,000 and other operating costs are $80,000. All figures are fictional.
Billable hours are 20 × 160 × 70% = 2,240. Revenue is $336,000, and operating profit is $56,000. At 65% utilization, revenue falls to $312,000 and profit to $32,000, assuming costs remain unchanged.
| Input or output | Formula logic |
|---|---|
| Capacity | Staff × available hours |
| Billable hours | Capacity × utilization |
| Revenue | Billable hours × realized rate |
| Operating profit | Revenue − loaded payroll − other costs |
Checks that catch material errors
Confirm that utilization is entered as 70%, not 70. Confirm that an annual salary is converted into the modeled monthly period. Sum monthly outputs to the quarter and reconcile headcount from opening staff, starts and departures.
Keep scenario inputs separate. If hiring lowers utilization while new staff ramp, do not apply the old team's utilization to the larger workforce unless the case supports it. That mistake can make an uneconomic hiring plan appear profitable.
Explain the model's limits
This simple case omits leave, overtime, ramp time, contractor costs and collection timing. Name the omissions that could change your recommendation. A real forecast may need a more granular capacity schedule and a separate cash model.
Before submitting, explain one output without looking at the formula bar. If you cannot describe how it is produced, simplify or correct the model.
Continue with the driver-based forecasting guide and FP&A workbook, which includes two capstones and full solutions. The workbook is a PDF; readers build the models in their own spreadsheet.