Tutorial: Build a Validation Cost Model in a Spreadsheet
Build a validation cost model in a spreadsheet in five steps, with one row per event type, cost formulas and a total you can use as a baseline.
Key takeaway
A validation cost model is a table with one row per event type. Each row multiplies events per year by hours per event and a loaded hourly rate, and the rows add up to your baseline.
This tutorial builds a small model that gives a more accurate baseline than a single average.
Step 1: Set up the columns
Create five columns: event type, events per year, hours per event, loaded hourly rate and annual cost.
Step 2: Add one row per event type
Use the types your team recognizes, for example new system validation, change control and periodic review.
Step 3: Enter the formula
Annual cost = events per year x hours per event x loaded hourly rate.
Step 4: Fill in the example
| Event type | Events | Hours | Rate | Annual cost |
|---|---|---|---|---|
| New system validation | 2 | 400 | $85 | $68,000 |
| Change control | 20 | 40 | $85 | $68,000 |
| Periodic review | 10 | 16 | $85 | $13,600 |
Step 5: Total it
The total baseline is $149,600 across 32 events and 1,760 hours, an average of 55 hours per event.
Next step
Enter 32 events, 55 hours and $85 in the Validation ROI Calculator to test savings scenarios.
FAQ
Frequently asked questions
What is a validation cost model?
- A validation cost model is a table that calculates what validation costs per year. Each row is an event type, and its cost is events per year multiplied by hours per event and a loaded hourly rate.
Why model validation cost by event type?
- Event types differ widely in effort. Modelling them separately shows which type carries the most cost, which a single average across all events hides.