Driver-based cost forecast
A trend says what cost did. A driver forecast says what it should do given the work coming. This build uses an invented service division. Change volume, labor rate, material price and productivity and compare the two forecasts, then check how each one has performed.
What this shows
- A driver-based number beside the trend number, and the gap explained
- A short-term view in months and a medium-term view in quarters
- Forecast accuracy by month: whether the forecast runs high or low, and the size of the miss
- Sliders for labor rate, material price, volume and productivity
How I use it
- Explaining a cost forecast from its drivers rather than from last month
- Finding out early when the trend and the drivers disagree
- Reporting how reliable a forecast has been
Explore the model below. It uses an invented company and invented numbers.
Explore the model
Illustrative model · Synthetic dataWide tables scroll sideways: swipe inside a table to see every column.
Sample build: driver-based cost forecast
An invented global field-service division, Tallowfield Service. The cost of a job is labor hours times the labor rate, plus materials, plus travel, and jobs are planned by region with a seasonal pattern. Move the four drivers, compare the driver forecast with a trend forecast, then check how each method has done over the last 12 months.
Synthetic inputs for illustration. Not data from any employer.
What to try
- Raise the labor rate to +8% and watch the labor step in the bridge grow while the others hold still.
- Set productivity to 0% to see how much of the labor rise the saved hours were absorbing.
- Move job volume. Both forecasts rise together, so the dollar gap grows and the percent gap stays put.
- Open Forecast accuracy to see why a 3-month average runs low when costs are rising.
The trend forecast does not use the labor rate, material price or productivity sliders. It prices every job at the last 3 months' average, so only job volume moves it.
The trend forecast takes the last 3 months' cost per job and multiplies it by the jobs coming. The driver forecast builds each job from labor hours times the labor rate, plus materials, plus travel, region by region and month by month.
Driver forecast, next 3 months
$3.00M
Trend forecast $2.94M. Gap +$55K (+1.9%).
Driver forecast, next 4 quarters
$13.42M
Trend forecast $13.08M. Gap +$335K (+2.6%).
Cost per job, next 3 months
$423.08
The trend forecast uses $415.33, the average of the last 3 months.
Average miss, last 12 months
0.8%
Driver forecast. The trend forecast missed by 1.1%.
The watch line is a gap of 2% of the trend forecast, the point where the two forecasts disagree enough to ask why. The next 3 months are within it and the next 4 quarters are beyond it.
Show the 12 monthly numbers behind the chart
| Month | Last year, actual | Trend forecast | Driver forecast | Gap |
|---|---|---|---|---|
| Jan | $878,190 | $963,773 | $976,487 | +$12,713 |
| Feb | $900,480 | $970,315 | $986,999 | +$16,684 |
| Mar | $926,900 | $1,009,564 | $1,035,129 | +$25,566 |
| Apr | $977,680 | $1,042,271 | $1,072,747 | +$30,477 |
| May | $1,039,360 | $1,129,490 | $1,166,273 | +$36,783 |
| Jun | $1,159,950 | $1,227,612 | $1,268,943 | +$41,331 |
| Jul | $1,183,680 | $1,247,236 | $1,285,537 | +$38,301 |
| Aug | $1,149,480 | $1,229,792 | $1,265,881 | +$36,088 |
| Sep | $1,094,450 | $1,144,754 | $1,174,050 | +$29,296 |
| Oct | $1,029,200 | $1,074,978 | $1,100,181 | +$25,203 |
| Nov | $958,650 | $1,013,925 | $1,033,762 | +$19,837 |
| Dec | $981,760 | $1,029,188 | $1,051,724 | +$22,536 |
The first three months of the year ahead, month by month. The driver forecast adds up labor, materials and travel for the jobs planned in each month. The trend forecast prices the same jobs at the average cost per job of the last 3 months.
| Month | Jobs | Labor | Materials | Travel | Driver forecast | Trend forecast | Gap |
|---|---|---|---|---|---|---|---|
| Jan | 2,321 | $709,816 | $206,664 | $60,008 | $976,487 | $963,773 | +$12,713 |
| Feb | 2,336 | $718,463 | $208,345 | $60,191 | $986,999 | $970,315 | +$16,684 |
| Mar | 2,431 | $755,019 | $217,661 | $62,449 | $1,035,129 | $1,009,564 | +$25,566 |
| Total | 7,088 | $2,183,298 | $632,670 | $182,648 | $2,998,615 | $2,943,652 | +$54,963 |
Amounts are rounded to whole dollars, so a column can differ from its total by a dollar. Gap is the driver forecast minus the trend forecast.
From the trend number to the driver number
The numbers over the steps are thousands of dollars. The table beside it has the exact amounts.
| Step | Amount |
|---|---|
| Trend forecast | $2,943,652 |
| Region mix at today's rates | -$16,294 |
| Labor rate change | +$85,687 |
| Material price change | +$30,127 |
| Productivity | -$44,557 |
| Driver forecast | $2,998,615 |
Region mix at today's rates is what the coming mix of jobs costs at current rates before any slider moves, set against the single cost per job the trend forecast uses. A busy month in a cheaper region pulls it down, and one in a dearer region pushes it up.
The gap in words
The driver forecast for the next 3 months is $3.00M, $55K (1.9%) above the trend forecast of $2.94M. The trend forecast prices every job at $415.33, the average of the last 3 months. The driver forecast prices the coming jobs at $423.08, built up from their labor hours, labor rate, materials and travel. Here is where the difference comes from: the coming region mix at today's rates takes out $16K, a 4.0% rise in the labor rate adds $86K, a 5.0% rise in material prices adds $30K, and 2.0% fewer labor hours per job takes out $45K. Using the trend forecast alone would understate cost by $55K over the next 3 months.
| Region | Jobs | Driver forecast | Cost per job |
|---|---|---|---|
| Americas | 3,276 | $1,409,532 | $430.26 |
| Europe | 2,184 | $1,022,130 | $468.01 |
| Asia Pacific | 1,628 | $566,953 | $348.36 |
The same two forecasts for the full year ahead, one quarter at a time. The trend forecast uses one cost per job all year. The driver forecast changes with the region mix, because each region has its own cost per job and its own busy season.
| Quarter | Jobs | Driver forecast | Trend forecast | Gap | Gap, share of trend | Driver cost per job |
|---|---|---|---|---|---|---|
| Q1 | 7,088 | $2,998,615 | $2,943,652 | +$54,963 | +1.9% | $423.08 |
| Q2 | 8,185 | $3,507,963 | $3,399,373 | +$108,590 | +3.2% | $428.60 |
| Q3 | 8,720 | $3,725,468 | $3,621,782 | +$103,686 | +2.9% | $427.22 |
| Q4 | 7,508 | $3,185,666 | $3,118,091 | +$67,575 | +2.2% | $424.33 |
| Four quarters | 31,500 | $13,417,711 | $13,082,897 | +$334,814 | +2.6% | $425.96 |
Gap is the driver forecast minus the trend forecast. The watch line is a gap of 2 percent of the trend forecast.
Gap by quarter
| Quarter | Region mix | Labor rate | Materials | Productivity | Gap |
|---|---|---|---|---|---|
| Q1 | -$16,294 | +$85,687 | +$30,127 | -$44,557 | +$54,963 |
| Q2 | +$25,286 | +$100,518 | +$35,056 | -$52,269 | +$108,590 |
| Q3 | +$15,187 | +$106,566 | +$37,347 | -$55,414 | +$103,686 |
| Q4 | -$8,117 | +$91,076 | +$31,975 | -$47,360 | +$67,575 |
| Four quarters | +$16,063 | +$383,846 | +$134,505 | -$199,600 | +$334,814 |
The numbers over the steps are thousands of dollars. The table beside it has the exact amounts.
| Step | Amount |
|---|---|
| Trend forecast | $13,082,897 |
| Region mix at today's rates | +$16,063 |
| Labor rate change | +$383,846 |
| Material price change | +$134,505 |
| Productivity | -$199,600 |
| Driver forecast | $13,417,711 |
Region mix at today's rates is what the coming mix of jobs costs at current rates before any slider moves, set against the single cost per job the trend forecast uses. A busy month in a cheaper region pulls it down, and one in a dearer region pushes it up.
The gap in words
The driver forecast for the next 4 quarters is $13.42M, $335K (2.6%) above the trend forecast of $13.08M. The trend forecast prices every job at $415.33, the average of the last 3 months. The driver forecast prices the coming jobs at $425.96, built up from their labor hours, labor rate, materials and travel. Here is where the difference comes from: the coming region mix at today's rates adds $16K, a 4.0% rise in the labor rate adds $384K, a 5.0% rise in material prices adds $135K, and 2.0% fewer labor hours per job takes out $200K. Using the trend forecast alone would understate cost by $335K over the next 4 quarters.
How each method did over the 12 months that just closed, using invented history. A miss is the forecast minus what was actually spent, so a plus sign means the forecast ran high. Bias is the average miss with its sign, so it shows whether a method runs high or low. The average miss ignores the sign and shows how far off it usually is.
| Measure | Trend forecast | Driver forecast |
|---|---|---|
| Runs | Low | About on target |
| Bias, dollars a month | -$12,315 | -$3,250 |
| Bias, share of actual cost | -1.2% | -0.3% |
| Average miss, dollars a month | $12,558 | $9,005 |
| Average miss, share of actual | 1.1% | 0.8% |
| Months high and months low | 2 high, 10 low | 5 high, 7 low |
Reading the record
Over the last 12 months the trend forecast ran low, 1.2% under actual in total, and it missed by $12,558 (1.1%) in an average month. The driver forecast ran about on target (0.3% under actual in total), and it missed by $9,005 (0.8%) in an average month. The driver forecast was closer to actual in 8 of 12 months. Actual cost per job rose from $401.00 to $416.00 across the period, and an average of the last 3 months always sits behind a rising line. June was the largest miss for the trend forecast (-5.2%). 2,850 jobs arrived against 2,720 planned, and both forecasts start from the planned jobs.
| Month | Actual cost | Trend forecast | Trend miss | Driver forecast | Driver miss |
|---|---|---|---|---|---|
| Jan | $878,190 | $877,763 | -$427 (0.0%) | $880,000 | +$1,810 (+0.2%) |
| Feb | $900,480 | $899,964 | -$516 (-0.1%) | $901,125 | +$645 (+0.1%) |
| Mar | $926,900 | $918,253 | -$8,647 (-0.9%) | $924,015 | -$2,885 (-0.3%) |
| Apr | $977,680 | $968,859 | -$8,821 (-0.9%) | $977,255 | -$425 (0.0%) |
| May | $1,039,360 | $1,023,686 | -$15,674 (-1.5%) | $1,035,050 | -$4,310 (-0.4%) |
| Jun | $1,159,950 | $1,099,934 | -$60,016 (-5.2%) | $1,113,840 | -$46,110 (-4.0%) |
| Jul | $1,183,680 | $1,156,376 | -$27,304 (-2.3%) | $1,172,775 | -$10,905 (-0.9%) |
| Aug | $1,149,480 | $1,150,788 | +$1,308 (+0.1%) | $1,175,940 | +$26,460 (+2.3%) |
| Sep | $1,094,450 | $1,090,572 | -$3,878 (-0.4%) | $1,095,920 | +$1,470 (+0.1%) |
| Oct | $1,029,200 | $1,017,572 | -$11,628 (-1.1%) | $1,026,285 | -$2,915 (-0.3%) |
| Nov | $958,650 | $958,796 | +$146 (0.0%) | $962,800 | +$4,150 (+0.4%) |
| Dec | $981,760 | $969,433 | -$12,327 (-1.3%) | $975,780 | -$5,980 (-0.6%) |
| Year | $12,279,780 | $12,131,994 | -$147,786 (-1.2%) | $12,240,785 | -$38,995 (-0.3%) |
Each miss shows dollars and the share of that month's actual cost.
Miss by month, share of actual cost
Show planned and actual jobs, and the cost per job the trend used
| Month | Planned jobs | Actual jobs | Actual cost per job | Trend cost per job |
|---|---|---|---|---|
| Jan | 2,200 | 2,190 | $401.00 | $398.98 |
| Feb | 2,250 | 2,240 | $402.00 | $399.98 |
| Mar | 2,290 | 2,300 | $403.00 | $400.98 |
| Apr | 2,410 | 2,420 | $404.00 | $402.02 |
| May | 2,540 | 2,560 | $406.00 | $403.03 |
| Jun | 2,720 | 2,850 | $407.00 | $404.39 |
| Jul | 2,850 | 2,880 | $411.00 | $405.75 |
| Aug | 2,820 | 2,790 | $412.00 | $408.08 |
| Sep | 2,660 | 2,650 | $413.00 | $409.99 |
| Oct | 2,470 | 2,480 | $415.00 | $411.97 |
| Nov | 2,320 | 2,310 | $415.00 | $413.27 |
| Dec | 2,340 | 2,360 | $416.00 | $414.29 |
Both forecasts start from the planned jobs, so a job-count miss hits both. The trend cost per job is the average of the three months before.
A job costs labor hours times the labor rate, plus materials, plus travel. Today's rates are the numbers in the first table before any slider moves. Jobs are planned for each region and month as an average month times a seasonal index. The indexes in each region average 1.0, so seasonality moves jobs between months and never adds any.
| Region | Jobs in an average month | Labor hours per job | Labor rate per hour | Materials per job | Travel per job | Cost per job today | Cost per job with the sliders |
|---|---|---|---|---|---|---|---|
| Americas | 1,200 | 5.0 | $60 | $90 | $30 | $420.00 | $430.26 |
| Europe | 800 | 5.5 | $64 | $85 | $20 | $457.00 | $468.01 |
| Asia Pacific | 500 | 6.0 | $40 | $75 | $25 | $340.00 | $348.36 |
The sliders change the labor rate, the material price, the number of jobs and the labor hours saved. Travel does not move.
| Region | Labor | Materials | Travel | Total |
|---|---|---|---|---|
| Americas | $305.76 | $94.50 | $30.00 | $430.26 |
| Europe | $358.76 | $89.25 | $20.00 | $468.01 |
| Asia Pacific | $244.61 | $78.75 | $25.00 | $348.36 |
| Region | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Americas | 0.85 | 0.85 | 0.90 | 0.95 | 1.05 | 1.15 | 1.20 | 1.20 | 1.05 | 0.95 | 0.90 | 0.95 |
| Europe | 0.80 | 0.85 | 0.95 | 1.00 | 1.10 | 1.20 | 1.15 | 1.10 | 1.05 | 1.00 | 0.90 | 0.90 |
| Asia Pacific | 1.10 | 1.05 | 0.95 | 0.90 | 0.90 | 0.95 | 1.00 | 1.00 | 1.05 | 1.05 | 1.05 | 1.00 |
| Region | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Americas | 1,071 | 1,071 | 1,134 | 1,197 | 1,323 | 1,449 | 1,512 | 1,512 | 1,323 | 1,197 | 1,134 | 1,197 |
| Europe | 672 | 714 | 798 | 840 | 924 | 1,008 | 966 | 924 | 882 | 840 | 756 | 756 |
| Asia Pacific | 578 | 551 | 499 | 473 | 473 | 499 | 525 | 525 | 551 | 551 | 551 | 525 |
Checks on the numbers
Each check compares one part of the forecast with another part, or with the data behind it. A check that fails is shown as Fail.
- PassEach job costs labor plus materials plus travel. 54 comparisons, all agree.
- PassRegions add up to the month. 60 comparisons, all agree.
- PassMonths add up to quarters and the year. 19 comparisons, all agree.
- PassThe trend forecast plus the four steps is the driver forecast. 36 comparisons, all agree.
- PassSeasonal indexes average 1.0 in every region. 6 comparisons, all agree.
- PassEach miss is forecast minus actual. 24 comparisons, all agree.
- PassBias and average miss agree with the table. 10 comparisons, all agree.
- PassThe trend history is the last 3 months of cost per job times planned jobs. 12 comparisons, all agree.
- PassThe inputs are complete real numbers. 105 numbers, all real and not negative.
Over the next 3 months the driver forecast is $3.00M against $2.94M on trend, a gap of +$55K (+1.9%). Over the next 4 quarters it is $13.42M against $13.08M, a gap of +$335K (+2.6%). Over the last 12 months the driver forecast missed by 0.8% a month and the trend forecast by 1.1%.