This intern has walked into the core problem that most organizations refuse to acknowledge: the data they need already exists, but the process for capturing it is broken. The request is straightforward, build a live dashboard tracking buyer efficiency and capacity. But the real challenge isn't the math or the tooling. It's that the buyers, the very people whose work you need to measure, don't update the shared Excel file unless management chases them. That's not a data problem. That's a trust problem.
The intern's initial approach is sound. Calculate average time per case type from historical data, supplement with senior buyer interviews, then build a model. But the senior buyers tell you what every experienced practitioner knows: cases vary wildly. Some take three months, others a year, depending on stakeholder complexity. When the variation is that wide, averages become misleading. And when the primary data source, the Excel tracker, is treated as an afterthought, your dashboard will always lag behind reality. The intern is right to feel stuck.
Here is what we think. The real opportunity here isn't a better pivot table or a more sophisticated Tableau visualization. It's fixing the input problem before you fix the output problem. If buyers don't update their progress because it isn't their priority, then the system is asking them to do extra work for no visible benefit. The dashboard needs to give them something back, maybe a personal view of their own capacity, or a clear signal when they are approaching overload. When the tool becomes useful to the person entering the data, the data quality improves. That is the human-centered approach this situation demands.
For the intern, the practical path forward is to prototype two views: a completed cases dashboard and an active cases dashboard, as planned. But layer in a third view that each buyer can see privately, their own efficiency trend and remaining capacity for the week. Let them see the value of the data they provide. Then, instead of chasing updates, you let the tool create its own incentive. The technical stack, Power Query, PivotTables, Tableau, is fine. But the solution isn't technical. It's behavioral. Build for the people who own the data, not just the people who want to watch it.