Operations Dashboards: Build Inventory, Project & Hospital Capacity Dashboards in Excel

Operations dashboards watch resources and flow — stock on shelves, work in progress, and beds in use. Inventory, Project, and Hospital are grouped because all three answer the same operational question: what do we hold, what is moving, and where are we about to run out? We use the foundation method for all three.

Table of Contents

• Part A — Inventory Dashboard
  • A1. Who Uses It and Why
  • A2. Data You Need
  • A3. Key KPIs
  • A4. Build KPIs with Copilot
  • A5. Charts to Include
  • A6. Reorder Alert
  • A7. ChatGPT Prompt to Plan It
  • A8. Suggested Layout
• Part B — Project Dashboard
  • B1. Who Uses It and Why
  • B2. Data You Need
  • B3. Key KPIs
  • B4. Build KPIs with Copilot
  • B5. Charts to Include
  • B6. Overdue Alert
  • B7. ChatGPT Prompt to Plan It
  • B8. Suggested Layout
• Part C — Hospital Dashboard
  • C1. Who Uses It and Why
  • C2. Data You Need
  • C3. Key KPIs
  • C4. Build KPIs with Copilot
  • C5. Charts to Include
  • C6. Capacity Alert
  • C7. ChatGPT Prompt to Plan It
  • C8. Suggested Layout

Part A — Inventory Dashboard

A1. Who Uses It and Why

Supply-chain leads and store managers use this to answer: How much cash is sitting on shelves? Which SKUs will stock out? It turns a stock export into a replenishment tool.

A2. Data You Need

Load a table named InvData with these columns:

A3. Key KPIs

• Total Stock Value — on-hand × unit cost.
• Stockout Count — SKUs below reorder level.
• Inventory Turnover — COGS ÷ average inventory.
• Days of Supply — on-hand ÷ average daily sales.

A4. Build KPIs with Copilot

Ask Copilot to add a value column:

Add a column StockValue as OnHand times UnitCost.

KPI cards:

=SUM(InvData[StockValue])
=COUNTIFS(InvData[OnHand],"<"&InvData[ReorderLevel])
=SUM(InvData[Sold])/AVERAGE(InvData[OnHand])

The turnover card divides total sold by average on-hand as a simple ratio.

A5. Charts to Include

• Column — Stock value by Warehouse.
• Bar — Top 10 moving SKUs (by Sold).
• Line — On-hand trend by week.

Copilot prompt:

Insert a column chart of stock value by warehouse from InvData.

A6. Reorder Alert

Flag items that need replenishment:

=IF([@OnHand]<[@ReorderLevel],"Reorder","OK")

Anything below its reorder level is marked "Reorder" so buyers act fast.

A7. ChatGPT Prompt to Plan It

Design an inventory dashboard in Excel from a table named InvData
with columns SKU, Warehouse, OnHand, ReorderLevel, UnitCost, Sold.
Give KPIs, charts, and a Copilot prompt to build it.

A8. Suggested Layout

Top: KPI cards (Stock Value, Stockouts, Turnover). Middle: Stock-by-warehouse column + Top movers bar. Bottom: Reorder alert table. Slice by Warehouse and Product.

Part B — Project Dashboard

B1. Who Uses It and Why

Project managers and steering committees use this to answer: Are we on time and on budget? Which tasks are slipping? It turns a task list into a status board.

B2. Data You Need

Load a table named ProjData with these columns:

B3. Key KPIs

• Overall % Complete — average across tasks.
• On-Time % — share of tasks not overdue.
• Budget Used % — spent ÷ budget.
• Overdue Tasks — count past due and not done.
• Resource Load — tasks per owner.

B4. Build KPIs with Copilot

Ask Copilot to add a budget-used column:

Add a column BudgetUsedPct as Spent divided by Budget.

KPI cards:

=AVERAGE(ProjData[PctComplete])
=AVERAGE(ProjData[BudgetUsedPct])
=COUNTIFS(ProjData[Status],"Overdue")

The budget card averages the percentage Copilot computed per task.

B5. Charts to Include

• Pie — Status breakdown (On Track / At Risk / Overdue).
• Line — Planned vs actual progress (burndown).
• Column — Budget vs Spent by Project.

Copilot prompt:

Insert a pie chart of task status from ProjData.

B6. Overdue Alert

Flag late, incomplete work:

=IF(AND([@Due]<TODAY(),[@PctComplete]<100),"Overdue","OK")

A task past its due date and not at 100% is marked "Overdue."

B7. ChatGPT Prompt to Plan It

Design a project dashboard in Excel from a table named ProjData
with columns Task, Owner, Due, PctComplete, Status, Budget, Spent.
Give KPIs, charts, and a Copilot prompt to build it.

B8. Suggested Layout

Top: KPI cards (% Complete, Budget Used, Overdue). Middle: Status pie + Burndown line. Bottom: Budget-vs-Spent by project + Overdue table. Slice by Owner and Priority.

Part C — Hospital Dashboard

C1. Who Uses It and Why

Hospital operations and clinical directors use this to answer: Are we over capacity? Are patients staying too long? It turns an admissions export into a capacity planner.

C2. Data You Need

Load a table named HospData with these columns:

C3. Key KPIs

• Bed Occupancy % — occupied beds ÷ total beds.
• Average Length of Stay (ALOS) — mean days admitted.
• Patient Volume — admissions in period.
• Readmission Rate — share readmitted within 30 days.
• Revenue per Patient.

C4. Build KPIs with Copilot

Ask Copilot to add a length-of-stay column:

Add a column LOS as DischargeDate minus AdmissionDate in days.

KPI cards:

=AVERAGE(HospData[LOS])
=COUNTA(HospData[PatientID])
=AVERAGE(HospData[Cost])

The ALOS card averages the length-of-stay column Copilot created.

C5. Charts to Include

• Line — Admissions trend by week.
• Column — Occupancy by Department.
• Bar — Average LOS by Department.

Copilot prompt:

Insert a column chart of bed occupancy by department from HospData.

C6. Capacity Alert

Flag departments near full:

=IF([@OccupancyPct]>0.9,"Full","OK")

Occupancy above 90% is marked "Full" so admins can redirect inflow.

C7. ChatGPT Prompt to Plan It

Design a hospital operations dashboard in Excel from a table named
HospData with columns Department, AdmissionDate, DischargeDate,
BedID, Cost, Outcome. Give KPIs, charts, and a Copilot prompt.

C8. Suggested Layout

Top: KPI cards (Occupancy, ALOS, Volume). Middle: Admissions trend + Occupancy-by-department column. Bottom: LOS-by-department bar + Capacity alert. Slice by Department and Month.