IT budget and PMO

IT budget template in Excel: CapEx, OpEx, committed and forecast at completion

Published 19 October 2026 · 7 min read

An IT budget template in Excel that lets you control spend, rather than record it, has one line per budget item, a CapEx or OpEx tag on each line, a link to a project or to a run service, and seven amounts per line: initial budget, revised budget, committed, actual to date (fed by twelve monthly columns), remaining to commit, forecast at completion and variance. The forecast at completion is the number the sheet exists to produce. It is recomputed every month from what has been invoiced, what has been ordered and what the budget owner still expects to spend, and the alerts fire on the forecast variance, never on the actual alone.

What the budget sheet must answer

Every month, three questions: how much will we have spent by year end, where does that differ from the revised budget and by how much, and which lines need a decision now. An accounting export answers a different question, how much has been invoiced, in the chart of accounts rather than in your projects. The budget sheet sits upstream: it is maintained by the people who sign orders and reconciled with finance once a month.

If your governance framework needs a reference standard, one is ISO 21502:2020, Project, programme and portfolio management — Guidance on project management, which iso.org lists as published in December 2020 and under revision (consulted 20 October 2026). The sheet itself depends on one discipline: updating three columns a month.

One line per item, linked to projects

The granularity rule is simple: one line per thing that can be ordered, invoiced and explained separately. A contract, a purchase order stream, a project workstream, a licence family.

Each line carries a project identifier chosen from a drop-down list, or the value RUN for recurring services. That one column turns a budget sheet into a portfolio tool: the portfolio tab reads each project's budget and forecast with a single formula, and nobody retypes figures between tabs. The article on the IT project portfolio dashboard in Excel shows how.

Columns	Content	Filled by
A–C: ID, label, category	Unique code, short name, category from a list (hardware, licences, cloud, services, telecom, external staff)	Owner, once
D: CapEx / OpEx	Tag from a two-value list	Owner, validated by finance
E–F: department, project ID	Cost centre; project code from the portfolio list, or `RUN`	Owner, once
G–H: initial budget, revised budget	Approved amount; amount after formal changes (transfers, additions)	Owner, on approval only
I: committed	Total of signed orders and contracts for the year, invoiced or not	Owner, monthly
J–U: actual, January to December	Twelve columns, invoiced amounts per month	From the accounting export, monthly
V: actual YTD	Formula	Nobody
W: remaining to commit	What the owner still expects to order this year	Owner, monthly
X–AA: forecast at completion, variance, variance %, alert	Formulas only	Nobody

CapEx and OpEx without the accounting debate

In the budget sheet, CapEx and OpEx are a tag, not an accounting opinion. Whether a given spend is capitalised is decided by your finance team under the accounting standard that applies to your company, and the sheet must agree with that decision, not anticipate it. The practical rule: the owner sets the tag when the line is created, finance validates it at the first monthly reconciliation, and it is not changed during the year without finance's agreement. Subtotals then come from one formula, =SUMIFS(X:X, D:D, "CAPEX") for the forecast, and the CapEx total reconciles with the investment plan that finance presents. A line that mixes both is split in two rather than argued over.

Committed, actual, forecast at completion

Three amounts from three sources build the forecast. With the columns of the table, the row 2 formulas are:

Actual YTD: the sum of the twelve monthly columns, =SUM(J2:U2). Future months are simply empty.
Open commitments: what has been ordered but not yet invoiced, =MAX(I2-V2,0), where I is committed and V is actual YTD. The MAX avoids a negative figure when invoices exceed recorded orders, a signal to check the committed column.
Forecast at completion: actual YTD, plus open commitments, plus what remains to be committed, =V2+MAX(I2-V2,0)+W2.
Variance: forecast minus revised budget, =X2-H2, and in percent =IF(H2=0,"",Y2/H2).

For consumption-based lines with no orders, such as cloud hosting, the owner's estimate in column W can be replaced by a run rate: =V2/Settings!$B$3*(12-Settings!$B$3), where the Settings cell holds the number of months elapsed at the status date. Use it with judgement: a run rate built on an annual invoice is meaningless, and a better-informed owner estimate always wins.

A fourth figure is worth a column: the commitment rate, =IF(H2=0,"",I2/H2). Above 100 percent of the revised budget, the line will overrun whatever the actuals say.

Alerts that work

An alert is useful when it is rare, computed, and leads to a named decision. Three rules keep it that way. First, alert on the forecast variance, not on the actual: a line at a quarter of its budget in October looks healthy until you see that orders already exceed the budget. Second, keep the thresholds in a Settings tab, one cell each. Third, limit the states to four:

=IF(H2=0,"",IF(Z2>Settings!$B$5,"Over budget",IF(Z2>Settings!$B$4,"Watch",IF(Z2<-Settings!$B$6,"Under-used","OK"))))

in column AA, where Z is the variance in percent and the three Settings cells hold the watch, over-budget and under-use thresholds. "Under-used" matters as much as "Over budget": a line that will not be spent is money another project is waiting for, and the monthly review is where it gets transferred. The same logic, applied to delay and budget drift per project, gives the RAG status criteria for the portfolio, and the roll-up of these alerts feeds the CIO dashboard indicators.

Example (fictitious data)

Status date 30 September, nine months elapsed, thresholds at 5 percent (watch), 10 percent (over budget) and 15 percent (under-use). Amounts in thousands, any currency.

Line	Tag	Project	Revised	Committed	Actual YTD	To commit	Forecast	Variance	Alert
ERP upgrade, integrator	CapEx	P-07	420	395	310	60	455	+35 (+8.3%)	Watch
Office suite licences	OpEx	RUN	180	180	135	0	180	0	OK
Cloud hosting	OpEx	RUN	240	0	201	67 (run rate)	268	+28 (+11.7%)	Over budget
Laptop refresh, wave 2	CapEx	P-03	150	90	90	30	120	−30 (−20%)	Under-used
Managed security monitoring	OpEx	RUN	96	96	72	0	96	0	OK
Total			1,086	761	808	157	1,119	+33 (+3.0%)

The total variance is three percent. The lines say otherwise: the ERP line will overrun unless the remaining scope is cut, the hosting line needs a reserved-capacity decision, and the laptop line is holding thirty that could cover most of the ERP overrun if the steering committee agrees to the transfer. That transfer is the decision the sheet exists to put on the table.

What the tool does for you

The IT PMO Cockpit is an Excel workbook of ten tabs with native formulas and no macros, for Excel 2016 and later or Google Sheets. Its IT Budget tab follows the structure above, with a few differences:

one row per budget line with code, description, department, CAPEX or OPEX tag and linked project (or RUN);
original budget, revised budget, committed, twelve monthly actual columns, left to commit (revised budget minus committed) and a year-end forecast entered by the line owner, which the formulas above can feed;
forecast variance in amount and percent, share spent, and an alert (Green, Amber, Red or Underspend) driven by two thresholds set once in the Settings tab;
summaries by CAPEX/OPEX and by department, and a cumulative curve of the straight-line revised budget against actuals.

Other tabs cover the portfolio, timeline, risks, decisions and actions, KPIs, SteerCo page and resources; the currency is a setting. It ships with a fictitious data set, a 17-page method guide (five routines, twelve KPIs with thresholds, a 30-minute start-up checklist) and an 8-slide steering committee template. The Company or personal use licence allows unlimited use within the licensed company, with 12 months of updates.

Get the IT PMO Cockpit – $490 excl. tax

FAQ

Should the budget sheet follow the chart of accounts?

No. It follows your projects and contracts, because that is where decisions are taken. A category column maps each line to the accounting view, and the monthly reconciliation with finance checks that the two totals agree.

Monthly or quarterly actuals?

Monthly. A quarterly figure arrives too late to change an order, and a forecast refreshed four times a year loses most of its value.

How are multi-year projects handled?

The sheet covers one fiscal year, so the lines of a multi-year project carry this year's amounts only. The full-life forecast (earlier years, this year's forecast, the estimate for later years) lives in the portfolio tab.