Fleet asset finance
Spend and variance analysis. Hieu Van Nguyen
This is the shape of analysis I run for the Allegiant Air fleet program, built on invented numbers. Pick a category, then follow the variance from the headline down to the vendor and the invoice line. The commentary is written from the numbers, so the explanation and the data cannot disagree.
What this shows
A variance bridge from budget to actual by category
Price, volume and timing separated, so the cause is visible
Drill-down from category to vendor to invoice line
Commentary generated from the numbers, ready to edit
Sample build: engine maintenance spend against budget
An invented airline's engine maintenance spend for one year, by category, vendor and invoice line. Pick a month, drill down, and read the commentary the numbers write.
Synthetic inputs for illustration. Not data from any employer.
What to try
- Leave it on year to date through August. Overhaul looks well under budget, but the gap is timing that comes back.
- Switch to Month and pick August. Overhaul shows no spend, because the visits budgeted for August were billed in other months.
- Click Engine overhaul shop visits, then Vendor A, to see which invoice lines were billed above the contract rate.
- Raise the percent limit and watch the smaller rows fade. Move the month to December and the three year-end methods agree.
A row is flagged material when its gap to budget is over both limits. Rows under either limit fade. 3 of 5 rows are flagged in the table.
Showing the whole program, year to date through August. Variance is actual minus budget, so a plus sign means more spend than planned.
Budget
$15.19M
Actual
$13.79M
Variance
-$1.41M
-9.3% of budget
Price
+$177K
Rate billed against budget rate
Volume
+$540K
Units bought against budget
Timing
-$2.12M
Landed in another month
Spend by category, year to date through August. Click a category to see its vendors.
| Category | Budget | Actual | Variance | Price | Volume | Timing |
|---|---|---|---|---|---|---|
| Material | $9,510,000 | $7,931,600 | -$1,578,400 | +$67,600 | +$474,000 | -$2,120,000 |
| $1,889,333 | $1,876,963 | -$12,370 | +$48,710 | -$95,080 | +$34,000 | |
| Material | $1,723,200 | $1,812,410 | +$89,210 | +$42,630 | +$107,030 | -$60,450 |
| $1,164,667 | $1,207,196 | +$42,529 | +$4,000 | +$14,812 | +$23,718 | |
| Material | $906,533 | $959,733 | +$53,200 | +$14,000 | +$39,200 | $0 |
| All categories | $15,193,733 | $13,787,903 | -$1,405,831 | +$176,940 | +$539,962 | -$2,122,732 |
Commentary: whole program, year to date through August
Written from the numbers above. It rewrites itself when you change the selection or the period, and you can edit it before you copy it.
The chart follows whatever you have selected above, from the whole program down to a single invoice line.
Month by month for Engine maintenance program
| Month | Budget | Actual | Variance | Timing |
|---|---|---|---|---|
| January | $663,067 | $679,791 | +$16,724 | +$7,194 |
| February | $2,513,067 | $2,502,478 | -$10,588 | -$21,556 |
| March | $2,292,667 | $2,466,984 | +$174,318 | +$1,947 |
| April | $1,203,067 | $1,214,444 | +$11,377 | -$142,199 |
| May | $2,513,067 | $685,858 | -$1,827,209 | -$1,847,145 |
| June | $2,292,667 | $2,746,575 | +$453,908 | +$412,205 |
| July | $663,067 | $2,800,136 | +$2,137,069 | +$1,848,009 |
| August | $3,053,067 | $691,637 | -$2,361,429 | -$2,381,187 |
| September | $2,292,667 | $1,312,073 | -$980,594 | -$1,136,933 |
| October | $663,067 | $2,697,124 | +$2,034,058 | +$1,983,667 |
| November | $2,513,067 | $2,249,618 | -$263,449 | -$423,529 |
| December | $1,392,667 | $3,275,382 | +$1,882,716 | +$1,699,528 |
| Twelve months | $22,055,200 | $23,322,100 | +$1,266,900 | $0 |
Standing at the end of August, with only the months up to then known, three ways to estimate where the year ends. Change the month above to see them move.
Remaining budget
$20.65M
$1.41M under the full-year budget
Run-rate
$20.68M
$1.37M under the full-year budget
Remaining budget plus timing
$22.77M
$717K over the full-year budget
With the remaining budget, the year ends $1.41M under budget. At the run-rate, it ends $1.37M under budget. With reversing timing added back, it ends $717K over budget. The run-rate differs from the remaining-budget view by $32K. In this data the twelve months end at $23.32M, and the remaining budget plus timing method lands nearest to that.
Where the numbers come from
| Item | Amount |
|---|---|
| Full-year budget | $22,055,200 |
| Budget, January to August | $15,193,733 |
| Actual, January to August | $13,787,903 |
| Budget for the months still to come | $6,861,467 |
| Timing inside the months closed | -$2,122,732 |
| Remaining budget view | $20,649,369 |
| Run-rate view | $20,681,854 |
| Remaining budget plus timing view | $22,772,102 |
| Run-rate minus remaining budget | +$32,485 |
| What the twelve months of this data add up to | $23,322,100 |
Remaining budget. What is booked so far, plus the budget for the months still to come. It assumes the rest of the year lands exactly on budget.
Run-rate. The average month so far, repeated for twelve months. It misses the calendar of big visits and anything seasonal.
Remaining budget plus timing. The first view with the timing that has not yet reversed added back. Timing nets to zero over the year, so a visit that slipped out of the closed months comes back in the open ones. This is the view to read when a large visit has slipped.
Invoice lines billed above the contract rate, for year to date through August. The amount at issue is the gap per unit times the units billed in that period. It is what to take back to the vendor, whether or not the line is over budget.
Billed above contract
$189K
8 invoice lines
Vendor A
$68K
2 lines above contract
Vendor D
$67K
3 lines above contract
Lines billed above the contract rate, year to date through August
| Invoice line | Billed rate | Contract rate | Over, per unit | Units billed | At issue | |
|---|---|---|---|---|---|---|
| Parts kit for the shop visitVendor A, Engine overhaul shop visits | $276,000 | $270,000 | +$6,000 | 8.7 kits | $52,000 | |
| Aircraft-on-ground callout laborVendor D, Line maintenance | $108 | $95 | +$13 | 2,600 hours | $33,800 | |
| Bleed valve repairVendor C, Component repairs | $6,650 | $6,200 | +$450 | 58.1 each | $26,130 | |
| Heat shield and cowl repairVendor D, Component repairs | $10,400 | $9,800 | +$600 | 27.5 each | $16,500 | |
| Consumables and chemicalsVendor D, Parts and consumables | $2,520 | $2,400 | +$120 | 136.7 lots | $16,400 | |
| Teardown and inspection laborVendor A, Engine overhaul shop visits | $126 | $120 | +$6 | 2,600 hours | $15,600 | |
| Line check laborVendor C, Line maintenance | $69.50 | $68 | +$1.50 | 9,940 hours | $14,910 | |
| Engine run and test cell operatorVendor C, Third-party labor | $112 | $105 | +$7 | 2,000 hours | $14,000 | |
| Total at issue | $189,340 |
The three parts of a variance
- Price. The rate billed against the budget rate, times the units actually bought. Did the same thing cost more?
- Volume. Units bought against units budgeted, at the budget rate. Did we buy more or less of it?
- Timing. Spend that landed in a different month than budgeted. It is not a change in cost: over twelve months it nets to zero.
A worked example
- Budget: 100 units at $50 is $5,000.
- Billed: 110 units at $52 is $5,720.
- Price: 110 units x $2 = $220.
- Volume: 10 units x $50 = $500.
- Timing: $0 when the spend lands in the budgeted month.
- Variance: $720 over budget, which is $220 + $500 + $0.
If the same units were budgeted for March and billed in May, then through March the variance is -$5,000 and all of it is timing. In May it is +$5,720 and the timing part is +$5,000.
Integrity checks: 7 of 7 pass
- PassMonthly shares add to 100 percent24 lines checked
- PassUnits and prices are positive numbers24 lines checked
- PassEvery line has a unique id and a listed category24 lines checked
- PassPrice plus volume plus timing equals the variancelargest gap on any line and month is 0.0000 dollars
- PassTiming nets to zero over twelve monthslargest full-year timing on any line is 0.0000 dollars
- PassTwelve months add to the year for budget and actuallargest gap on any line is 0.0000 dollars
- PassCategories add to the program totalgap between the program total and the category sum is 0.0000 dollars
Figures are rounded to the nearest dollar for display, so a column can differ from its parts by a dollar.
Engine maintenance program, year to date through August: $1.41M under budget, mainly timing. Standing at August, the three year-end methods put the program at $20.65M to $22.77M against a budget of $22.06M.
How I use it
Closing the month: find the few lines that explain most of the variance
Challenging a vendor invoice line by line against the contract rate
Forecasting the rest of the year from what has already moved