Building a Healthcare PMO Scoring Model in Excel

Lead and Deliver

In my previous article, I discussed why large healthcare organizations need a structured approach to ranking projects. Once leadership agrees on the importance of objective prioritization, the next question is usually:

"How do we actually build one?"

The good news is that you don't need expensive portfolio management software to get started. Microsoft Excel is powerful enough to build a scoring model that provides transparency, consistency, and data-driven decision making.

Whether your organization manages 20 projects or 500, an Excel-based scoring model is an excellent first step toward mature portfolio management.

Step 1: Define Your Scoring Criteria

Before building your spreadsheet, determine what factors are most important to your organization.

Every healthcare organization is different, but most evaluate projects based on areas such as:

Criteria	Description
Strategic Alignment	Supports organizational strategic goals
Patient Safety	Improves patient outcomes or reduces risk
Regulatory Compliance	Meets CMS, Joint Commission, HIPAA, or other requirements
Financial Impact	Revenue generation or cost savings
Operational Efficiency	Improves workflows or productivity
Cybersecurity	Reduces security or privacy risk
Patient Experience	Enhances the patient journey
Resource Availability	Organization has capacity to execute
Technical Feasibility	Technology is ready and supported
Executive Sponsorship	Strong leadership commitment

The goal is to measure what matters—not simply what is easy to measure.

Step 2: Assign Weights

Not every criterion should carry equal importance.

For example:

Criteria	Weight
Strategic Alignment	20%
Patient Safety	20%
Regulatory Compliance	15%
Financial Impact	15%
Operational Efficiency	10%
Cybersecurity	10%
Patient Experience	5%
Resource Availability	3%
Technical Feasibility	2%

The weights should total 100%.

Healthcare organizations often assign greater weight to patient safety and regulatory requirements because these initiatives directly affect patient care and organizational compliance.

Step 3: Create a Scoring Scale

Keep scoring simple.

A five-point scale works well.

Score	Meaning
1	Very Low
2	Low
3	Moderate
4	High
5	Very High

Using consistent definitions helps different reviewers score projects more objectively.

Step 4: Build the Spreadsheet

A simple worksheet might look like this:

Project	Strategic	Safety	Compliance	Financial	Operations	Security	Total
New EMR Module	5	5	4	4	4	3	4.45
Wi-Fi Upgrade	4	3	2	3	5	5	3.75
Visitor Mobile App	3	2	1	4	3	2	2.75

Each criterion is multiplied by its assigned weight.

Example formula:

Weighted Score = Score × Weight

The total project score is simply the sum of all weighted scores.

Step 5: Add Automatic Rankings

Excel makes ranking easy.

If your total score is in column L, use:

=RANK(L2,$L$2:$L$25,0)

The highest scoring project automatically receives Rank #1.

No manual sorting required.

Step 6: Add Conditional Formatting

One of Excel's most powerful features is Conditional Formatting.

Use colors to quickly identify priorities.

Example:

🟢 Green = Score above 4.25

🟡 Yellow = Score between 3.50–4.24

🔴 Red = Below 3.50

Executives can immediately see which projects deserve attention.

Step 7: Include Risk

Many organizations make the mistake of ranking projects only on value.

Risk should also influence prioritization.

Create separate scores for:

• Implementation Risk

• Schedule Risk

• Vendor Risk

• Change Management Risk

• Technology Risk

Some PMOs calculate:

Priority Score = Business Value – Risk

Others display Business Value and Risk separately using a two-dimensional chart.

Either approach encourages better conversations.

Step 8: Add an Impact vs. Effort Matrix

One of the most useful executive visuals is a simple four-quadrant matrix.

Projects typically fall into four categories:

Quick Wins

High Value / Low Effort

Complete these first.

Strategic Investments

High Value / High Effort

Require executive sponsorship and careful planning.

Fill-In Projects

Low Value / Low Effort

Complete only if resources permit.

Avoid or Delay

Low Value / High Effort

These rarely provide sufficient return.

This visual helps executives understand not only project importance but also delivery complexity.

Step 9: Build a Dashboard

With a few Pivot Tables and charts, Excel becomes a lightweight portfolio management dashboard.

Consider displaying:

• Number of Active Projects

• Projects by Department

• Projects by Strategic Goal

• Budget by Portfolio

• High-Risk Projects

• Regulatory Projects

• Average Priority Score

• Resource Allocation

• Upcoming Major Milestones

Executives appreciate seeing the portfolio summarized on a single page.

Step 10: Review and Recalibrate

A scoring model should never become static.

Healthcare priorities evolve quickly.

New regulations, cybersecurity threats, mergers, clinical initiatives, and financial pressures all influence which projects should receive priority.

Many PMOs review scoring quarterly to ensure the portfolio still reflects organizational strategy.

Tips for Success

✔ Keep scoring criteria simple.

✔ Limit the number of scoring categories.

✔ Use consistent definitions.

✔ Include cross-functional stakeholders.

✔ Document how scores were assigned.

✔ Review rankings regularly.

✔ Use the model to support discussions—not replace leadership judgment.

Remember, the spreadsheet doesn't make the decision.

It supports better decisions.

Final Thoughts

An Excel scoring model won't replace a full Project Portfolio Management (PPM) platform, but it provides an affordable, transparent, and highly effective way to prioritize projects—especially for organizations beginning to mature their PMO practices.

The true value isn't the spreadsheet itself; it's the conversations it enables. When executives can compare initiatives using consistent criteria, portfolio decisions become less subjective and more strategic.

The best PMOs don't just manage projects—they help organizations invest in the work that delivers the greatest value to patients, caregivers, and the business.

Sometimes the most powerful portfolio management tool isn't the most expensive software—it's a well-designed Excel workbook backed by thoughtful governance.