Smartsheet Portfolio Dashboard for Client Sponsors

A Smartsheet portfolio dashboard for several client projects works when one master sheet holds one row per project and every project lead fills the same status, risk, and budget columns the same way. Build a summary on that master, filter one view for each sponsor, and check the numbers before anything goes out. The sections below cover the sheet, the status and risk rules, the dashboard, and the checks.

What should the master portfolio sheet contain?

The master portfolio sheet should hold one row per client project, with fixed columns for client, sponsor, project lead, status, next sponsor decision, approved budget, forecast, actual to date, budget variance, open high risks, and last updated. Keep the names and order unchanged so every report reads the same fields.

1. Create one sheet named [portfolio name]. Add a row for each active client project and nothing else.
2. Add the columns in the order above. If your sheet settings allow a dropdown, use one for status so every lead picks from the same options.
3. Set budget variance to approved budget minus forecast, and write that sign convention in the sheet notes.
4. Keep forecast and actual to date in separate columns. Actual shows what has been spent so far. Forecast shows where the project is expected to finish. The two answer different questions, so keep both.

Illustrative budget variance, placeholders only; write it in your sheet's own formula syntax:

Budget variance = Approved budget - Forecast   (same row)

A negative variance then means the forecast is above the approved budget, for everyone who reads the sheet.

Illustrative row, with placeholders only: [Client name] | [Project name] | Sponsor: [sponsor name] | Status: [Green, Amber, or Red] | Next sponsor decision: [decision needed] by [date]

How do I write one status rule that every lead follows?

Write one Green, Amber, and Red definition in the master sheet notes, based on schedule, budget, and open decisions, and have every lead apply it before a sponsor report. A color should follow the written rule, not how the lead feels that week. The ONES guide notes that project health can update from schedule variance or budget burn.

1. In the notes, write Green as [criteria for on plan], Amber as [criteria for one signal outside tolerance], and Red as [criteria for a missed milestone or a forecast above tolerance, with no agreed recovery].
2. Replace the brackets with your own tolerances, such as [schedule tolerance] and [budget tolerance]. The ONES example rule marks a project red once it passes set limits on budget or schedule. Use it as a model for the form of the rule, then set your own limits.
3. Require a status reason for every Amber or Red row. The reason names the next step and any decision or help the sponsor needs.
4. Walk each lead through the rule once before the first sponsor report.

Illustrative status logic, placeholders only; write it in your sheet's own formula syntax:

Status = Red    if Forecast is more than Approved budget plus [budget tolerance], or a milestone is missed with no agreed recovery
Status = Amber  if one signal is outside [schedule tolerance] or [budget tolerance]
Status = Green  otherwise

How do I keep the data current without rebuilding it each week?

Have each project lead update only their own row before every sponsor status report, and keep one portfolio owner who controls the column names. Cross-sheet roll-ups can break when a column is inserted, a status is renamed, or a sheet is archived, and then report a wrong number without warning, according to PPM Express.

1. Name one portfolio owner. The owner keeps the column names and order fixed, and a lead asks before adding or renaming a column.
2. Each lead updates their row and the last updated date before the status report goes out.
3. Keep a detail sheet for each project with its tasks, dates, and cost lines. Copy the summary figures into the master from that detail sheet, and check the copy against the source.
4. Before each sponsor report, the owner compares a few rows with their project sheets. If one differs, the owner finds out why before the report goes out.

How do I track risks so they show in the sponsor view?

Keep one risk log per project with the same fields and one scoring method, then count the open high risks that need a sponsor decision in the master. Use likelihood and impact as the two inputs. Smartsheet's guide to project portfolio dashboards describes project risk as a likelihood and impact matrix with mitigation.

1. Add these columns: Risk ID, Description, Owner, Likelihood, Impact, Score, Mitigation, Next step, and Needs sponsor decision (Yes or No).
2. In the notes, list each likelihood level as [level name] = [what it means], and do the same for impact. Use the same level names in every project's log.
3. Calculate Score as the likelihood value times the impact value, using the level values in the notes. Write that one sentence in the notes too.
4. Write the high-score threshold in the notes as [high-score threshold]. Any open risk at or above it counts as high.
5. In the master, count the open risks that are high and have Needs sponsor decision set to Yes. Put that count in Open high risks.

Illustrative count, placeholders only: Open high risks = number of rows where Status is open, Score is at or above [high-score threshold], and Needs sponsor decision is Yes.

Illustrative risk row, with placeholders only: R-[number] | [risk description] | Owner: [name] | Likelihood: [level] | Impact: [level] | Mitigation: [action] | Needs sponsor decision: Yes, by [date]

The ONES guide says its Smartsheet template has no native risk module and suggests a custom risk-score column instead. Build the log on its own sheet and roll it up, rather than assuming the template covers risk.

How do I give each sponsor a view of only their projects?

Build one filtered report for each sponsor on the master, so each sponsor sees only their own projects with status, decisions, and budget variance. Smartsheet's guide recommends separate dashboards for different stakeholders. Keep the sponsor view short and leave the detail with the project lead.

1. Create a filtered report on the master, using whichever report or view your plan provides, with these fields: Project, Status, Status reason, Next sponsor decision, Budget variance, Open high risks, and Last updated.
2. Filter it to Sponsor equals [sponsor name]. Make one report for each sponsor and name it with that sponsor's name.
3. Build two widgets on the master, both fed from the master sheet: a status summary chart that counts Green, Amber, and Red rows from the Status column, and a bar chart of Budget variance by project from the Budget variance column. Before each sponsor report, the owner checks that both widgets show only the rows verified in the pre-send checks. To compare your columns, the Summary Dashboard template tracks health, status, planned vs. actual budget, progress, and risk.
4. Confirm in your own Smartsheet plan that the sponsor can open a shared report, and note any seat or role they need. Keep edit rights with the portfolio owner, so the column names and status rules stay the same.

What should I check before sending the dashboard to a sponsor?

Send the dashboard only when every row has a current update date, each Amber or Red row has a reason and a next step, and the sponsor's filtered view shows only their projects. The project lead reviews the summary and approves the wording before anything goes out.

1. Check that the last updated date falls inside the current reporting period for every row.
2. Check that each Amber or Red row has a status reason, a next step, and any decision needed.
3. Pick one row and recalculate its budget variance by hand from its approved budget and forecast.
4. Check that the summary columns show no error values and no blank cells.
5. Check that the open high risks count matches the project risk logs.
6. Check that the sponsor's filtered view shows only that sponsor's projects.
7. Confirm the lead has read the summary and approved the wording for the sponsor.

How do I record the sponsor's answer?

Record each sponsor decision in the Next sponsor decision column with its date, the decision itself, and the team's next step, so the master shows what happened after the sponsor responded. A decision left only in an email thread is easy to miss at the next status report.

1. When the sponsor answers, the lead records the decision, the date, and who will act on it.
2. Give the owner of each follow-up action a date, and write it in the status reason for that row.
3. Clear the Next sponsor decision field only after the team has done the follow-up, not when the answer arrives.

Record line, with placeholders only: Decision recorded [date]: [decision]. Next step: [action], owner [name], due [date].

What limits should I plan for?

Plan for manual updates, for a capacity view that a sheet may not handle, and for a project count that your own plan must confirm. ONES lists manual data entry as a limit of its template, and ONES gives different project-count figures in different sections of the same guide.

• Manual entry: build an update point into the week, so the owner knows when each lead's row must be current.
• Capacity: PPM Express says a sheet cannot calculate whether the right people are available across every project at once. That is the vendor's claim, so test it against your own plan before you rely on a capacity view.
• Project count: test the master with a copy of your full project list before you commit to it, and check the tool's own documentation for current limits rather than any single guide's figure.

Write each limit you find into the master sheet notes, so every lead reads the same warning before updating a row.

What should I do first this week?

Start by building the master sheet with the fixed columns and the status and risk notes this week. Then run the pre-send checks on a few real projects before the first sponsor report, and fix any row that fails before a sponsor sees it.

1. Create the master sheet with the fixed columns, and add the status, budget, and risk notes.
2. Enter a few real projects, and update each row as its project lead would.
3. Run the pre-send checks above on those rows.
4. Send the first sponsor report only after the lead has approved its wording.