IT budget and PMO

IT project portfolio dashboard in a spreadsheet: the one-tab model

Published 20 October 2026 · 6 min read

An IT project portfolio dashboard in Excel works best as a single tab: one row per project, about twenty columns, and a few computed cells that nobody types. The budget and actuals come from the budget sheet, the delay is the forecast end date minus the original (baseline) end date, and the RAG status is derived from that delay and from the budget drift, the gap between the share of budget spent and the share of work done. Counters, a timeline and the steering committee page are then formulas on top of the same tab, so the portfolio is updated once and read everywhere.

Why one tab beats a tool nobody updates

A portfolio view is only as good as its last update. The more fields a tool asks for, the more of them go stale; a tab that project managers already know, with the inputs limited to what changes week to week, is easier to keep current. The single-tab model also gives one source for every committee: if a figure is not in the tab, it does not exist for the meeting.

If you need a reference text for your governance, ISO 21504:2022, Project, programme and portfolio management — Guidance on portfolio management, gives guidance on the principles of project and programme portfolio management. It does not prescribe a spreadsheet layout; the model below is one practical way to apply such principles.

The columns

Group	Columns	Entered or computed
Identity	Code (P01, P02…), project name	Entered once
Ownership	Sponsor, project manager, department	Entered once
Classification	Priority (P1 to P4), status, phase, from drop-down lists	Entered, reviewed monthly
Money	Budget, actuals, % spent	Computed from the budget sheet by project code
Progress	% complete	Entered weekly
Dates	Start, next milestone and its date, baseline end, forecast end	Entered; baseline end changed only by committee decision
Status	Delay in days, RAG	Computed
Decision	Next decision required, in one sentence	Entered before each committee

The money columns are the reason to keep a project code on every budget line: with the revised budget of the budget sheet in a named range, the project budget is =SUMIFS(Budget_Revised,Budget_Project,$A7) and nobody retypes it. The structure of that budget sheet is described in the IT budget template with CapEx, OpEx and forecast.

Delay measured against the original commitment

With the baseline end in column P and the forecast end in column Q, the delay in days is =IF(OR(Q7="",P7=""),"",Q7-P7). The baseline is the date committed when the project was approved. Measuring against the latest re-plan instead lets a project turn green again every time its date moves, which empties the indicator of meaning. If the commitment genuinely changes, the committee decides it, the decision is logged, and only then is the baseline replaced.

Computed RAG status

A declared status is an opinion; a computed one is a measurement everybody can check. With % spent in K, % complete in L, status in G and four named thresholds held in a Settings tab:

=IF(OR(G7="Completed",G7="Cancelled"),"",IF(OR(N(R7)>=DelayRed,N(K7)-N(L7)>=DriftRed),"Red",IF(OR(N(R7)>=DelayAmber,N(K7)-N(L7)>=DriftAmber),"Amber","Green")))

N() turns an empty cell into zero, so a project without a forecast date does not break the formula. The thresholds are management choices, not standards: write them down, keep them for at least a few committees, and change them at a quarterly review rather than the day before a meeting.

Dashboard views

Counters above the table: active projects =COUNTIFS(Code,"<>",Status,"<>Completed",Status,"<>Cancelled"), the number of Green, Amber and Red projects with COUNTIF, the average delay of active projects with AVERAGEIFS, and the number of decisions required.
A timeline: one row per project, one column per month, each cell coloured by conditional formatting when the month falls between the start and the forecast end, in the project's RAG colour.
A committee page: the projects awaiting a decision, sorted by priority, with their RAG status and the decision phrased as a question.

Example (fictitious data)

Thresholds: amber from 15 days of delay or 5 points of drift, red from 30 days or 10 points. Amounts in thousands, any currency.

Project	Priority	Phase	Budget	% spent	% complete	Baseline end	Forecast end	Delay (days)	RAG
P01 Network refresh	P2	Build	400	65%	60%	2027-03-31	2027-03-31	0	Amber
P02 Finance module upgrade	P1	Testing	600	90%	78%	2026-12-15	2027-01-29	45	Red
P03 Service desk tool	P3	Deployment	120	80%	82%	2026-11-30	2026-12-14	14	Green
P04 Data platform	P2	Design	300	15%	20%	2027-06-30	2027-07-20	20	Amber
P05 Laptop refresh	P3	Build	250	40%	45%	2027-02-26	2027-02-26	0	Green

The counters read: 5 active projects, 2 Green, 2 Amber, 1 Red, an average delay of 16 days (79 divided by 5, rounded) and a total budget of 1,670. Two rows deserve a second look. P01 has no delay but is amber because spending runs 5 points ahead of progress, a drift that only shows when the two percentages are compared. P03 is green with 14 days of delay, one day below the amber threshold: the committee page should still show it if its milestone is close. P02 goes to the next steering committee with its decision written in one sentence.

Keeping it alive

The model holds if the inputs stay few and the rhythm fixed: % complete, forecast end, next milestone and next decision updated weekly by the project managers, budget actuals monthly after the accounting close, priorities and thresholds quarterly. Contracts that end or renew inside a project's window belong in the same discussion; the notice dates come from the IT contract register.

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 Project portfolio tab has the columns above: budget and actuals read automatically from the IT Budget tab through the project code, delay against the baseline end, RAG status computed from the delay and the budget drift with thresholds set once in Settings (defaults: 15 and 30 days, 5 and 10 points), counters, and a rank for the projects awaiting a decision. The Timeline tab draws an 18-month Gantt, and the SteerCo tab builds an A4 landscape page with seven indicator tiles, the RAG status of the projects, the top five risks, the decisions of the last thirty days and the five projects awaiting a decision. Other tabs cover the IT budget, a RAID risk register, decisions and actions, 12 KPIs and team capacity. It ships with a fictitious data set, a 17-page method guide and an 8-slide steering committee template; the currency is a setting. Unlimited use within the licensed company, 12 months of updates.

IT PMO Cockpit: $490 excl. tax

Launch code LANCEMENT: 30% off, for the first 20 orders or until 30 November 2026.

FAQ

How is % complete measured?

By one rule applied to every project, for example the share of planned deliverables accepted. Whatever the rule, it must be written down: the budget drift compares it with % spent, so a generous % complete hides an overspend.

What if my budget lines are not linked to projects?

Enter the project budget and actuals by hand in the money columns. The delay and RAG formulas still work; you lose the certainty that the portfolio and the budget sheet agree.

How many projects can one tab hold?

As many as a committee can read. The IT PMO Cockpit is designed for portfolios of 5 to 40 projects; beyond that, filter the committee page by department or priority.