Ultimate Project Management Template v2.0 – User Guide | Analysistabs

Requirements

• Excel 2016, 2019, 2021, 2024 or Microsoft 365 on Windows or Mac. Excel 2013 and older, Google Sheets and Apple Numbers are not supported.
• A standard .xlsx workbook – no macros, no add-ins. It passes most corporate security policies.
• Calculation mode must be Automatic (Formulas › Calculation Options). If numbers look stale, press F9.
• You receive two files: a Sample Data edition to explore every feature and a Blank edition to start your own portfolio.

Quick start – 8 steps

1. 1Set up the basicsSetup sheet: company weekends, holidays, default values, RAID risk levels and heatmap colours. Lists you can freely edit (green headers): Departments, Phases, Response Strategy, Expense Categories, Client Status and Progress steps. Amber-header lists (Status, Complexity, Priority, Yes/No, Emp Type, RAID Type, RAID Status) feed the dashboards – don't rename or reorder their values. Labels sheet: rename or translate titles.
2. 2Add clientsClient Management: one row per client – contact, company, status and payment terms (used for invoice due dates).
3. 3Add your teamResource Management: name, type, department, bill rate, cost rate, max hours per day, start and end date.
4. 4Add projectsProject Management: pick the responsible person and client from the dropdowns; enter budget, start date, duration (working days) and baseline end.
5. 5Plan milestones & tasksTask Management: pick the project and phase, enter the task name, set IS MILESTONE = YES for milestones, plan start (a working day), duration, owner, hours and an optional predecessor.
6. 6Track progressAs work happens, fill ACTUAL START, ACTUAL DURATION and ACTUAL PROGRESS (0–100%) and set STATUS. Update ACTUAL HOURS for effort tracking.
7. 7Log RAID, invoices & expensesRAID Log for risks, assumptions, issues and dependencies; Invoice Manager for billing and payments; Expenses (Billable) for costs recharged to clients.
8. 8ReviewDashboards, the Gantt and the Kanban board update automatically. Check Data Quality for anything that needs fixing.

Sheet guide

The Home sheet links to every sheet. Use the sidebar or the cards; each sheet has a ⌂ Back to Home link top-left.

Home – one-click navigation to every view, data sheet and setting.

Entering data

Data-entry sheets

Seven linked tables. Enter a row once – every dashboard, the Gantt and the Kanban board pick it up.

Task Management – input columns on the left with dropdowns; grey calculated columns on the right.

Dashboards

Labels with ☰ or ▾ are dropdown filters – choose ALL or a single item. Navigation buttons at the top of each dashboard jump to its sections. Each section is set to print on its own page (File › Print).

Executive Dashboard

Four sections: Summary, Delivery, Financials, RAID.

• Summary – KPI cards (projects, clients, budget, plan hours, overdue, due in 7 days, budget burn, hours variance), Project Health table with progress, schedule and budget status, portfolio health donut, milestone list.
• Delivery – tasks by status, priority and complexity, completed %, due by month, project timelines, workload by resource.
• Financials – budget vs invoiced vs remaining by project, billable expenses, plan vs actual hours.
• RAID – open items donut, likelihood × impact matrix, items by type, level and project.

Project Dashboard

Pick a project in PROJECT ☰ – five sections update.

• Summary – progress and schedule gauges, days remaining, budget, milestones.
• Delivery – task status, phases, overdue and due-in-30-days lists.
• Financials – budget, invoices (with detail) and expenses for the project.
• RAID – the project's risks, issues and actions.
• Status Report – a one-page report; set the period to this week, month, year and more.

Resource Dashboard

Choose one person or ALL RESOURCES; filter by department and type.

• Summary – tasks assigned / completed / due this month, hours this week, overdue; Resource Attention top-5; workload chart; tasks by week.
• Capacity – assigned vs capacity hours, utilisation vs target, availability next 4 weeks, capacity vs demand 12 weeks, over-allocated list, hours by department and type.
• Heatmap – planned tasks per person per day for the whole year, monthly totals and overload days.

Gantt chart

Everything is driven by the dropdowns at the top – no formulas to touch.

The header strip shows progress (tasks and milestones done), schedule health, next milestone, overdue tasks, due and starting in the next 7 days, high-priority open items, hours used vs plan and the latest plan end. Weekends and company holidays from Setup are respected in every bar.

Kanban board

Five lanes – Open, In Progress, On Hold, Completed, Cancelled.

• Filter by Project, Responsible, Priority and Phase; SHOW = tasks, milestones or both.
• Each card shows project, phase, owner, due date, a progress bar, a priority colour stripe and a ⚠ overdue flag.
• Cards are sorted overdue-first, then by due date; up to 20 cards per lane, with a “+N more” count if there are more.
• There is no dragging: change a task's STATUS in Task Management and its card moves to the right lane.

Setup & customising

• Weekends & holidays – tick your weekend days and list company holidays; every date calculation, the Gantt and the heatmap skip them.
• Default values – defaults used when a cell is left blank.
• Dropdown lists – 13 lists. Green-header lists are safe to edit: Departments, Phases, Response Strategy, Expense Categories, Client Status, Progress steps. Amber-header lists feed dashboard formulas: Status, Complexity, Priority, Yes/No, Emp Type, RAID Type, RAID Status – don't rename values like YES, Completed, Closed, High or Risk, or add / reorder items, without updating formulas.
• RAID thresholds – score bands for Low / Medium / High / Extreme.
• Heatmap thresholds – colour bands for the Resource heatmap.

Setup – weekends, holidays, dropdown lists and RAID / heatmap thresholds.

Labels

Every title, heading and filter label (78 in total) lives on the Labels sheet. Type your own wording or translation in NEW LABEL; FINAL USE feeds the sheets. Rows marked in red are titles on shapes – edit those shapes directly.

Data Quality

100+ live checks across every input sheet, grouped by sheet. Each row shows the number of records failing, the first one, and how to fix it. The summary at the top shows total checks, passing, and how many need review.

• Missing or invalid owners, clients, projects, statuses and dates
• Start dates on weekends or holidays; end before start; durations ≤ 0
• Duplicate IDs, names, emails and milestones
• Overdue RAID items, open high-level risks without a response strategy
• Invoices over the project budget, unpaid past due, expenses without a project
• Chart-capacity warnings (15 projects, 15 resources, 10 clients)

Data Quality – summary strip and checks grouped by sheet, each with the first problem and how to fix it.

Troubleshooting

FAQ

Can I see and change the formulas?

Yes. Nothing is password-locked and there are no macros. Unprotect a sheet to edit it.

How many projects and tasks can it handle?

Tables grow automatically for hundreds of tasks. Portfolio charts show up to 15 projects, 15 resources and 10 clients.

Can I translate it?

Yes – change the wording on the Labels sheet and the dashboards follow.

Can I print or share the dashboards?

Yes. Page layouts are preset so each dashboard section prints on one page, or export to PDF.

Is it a subscription?

No. Pay once and use it forever, with free updates for 12 months from purchase.

Upgrading from v1.x

v2.0 is a new workbook, not a patch. Copy your rows into the matching v2.0 tables (Clients, Resources, Projects, Tasks, RAID, Invoices, Expenses) using Paste → Values; the new columns are optional and can stay blank. Then review Data Quality for anything to fix.

Version history

See every change, fix and upgrade note in the full Ultimate PM Template – Standard changelog ↗.

Questions? Email info@analysistabs.com.