Excel-Based Project Profitability Tracker — Enhancement & Optimization

I am the estimating manager at a 22-person commercial electrical contracting firm. Two years ago I built an Excel workbook to track project profitability across our active job portfolio. It has worked, but we are now running 40–60 active projects at any given time and the file is slow, fragile, and requires too much manual maintenance to keep accurate. I am attaching the current workbook. Please open it and review it before responding. I want to hear from people who have actually looked at the file — not a generic Excel pitch. The Change Log tab will give you the history. The Crew Productivity tab will show you where I gave up. We track estimated vs. actual labor hours across three project phases — Rough-In, Trim, and Gear/Equipment — for every active job. Project managers update their rows weekly. The file feeds a dashboard and a project lookup tool I built using VLOOKUPs. It works. But it has problems I have not been able to solve on my own. What needs to be fixed 1. The VLOOKUP chain is fragile Every lookup formula on the Project Lookup tab references a hardcoded range — Projects!$A$5:$T$26. If anyone inserts a row above row 5, or if we add projects past row 26, every formula breaks. I have a warning note in the file but that is not a real solution. I want this replaced with something that does not require babysitting. 2. The file slows down significantly at 40+ projects On some machines it crawls. I do not know if this is the VLOOKUP chain, the conditional formatting, or something else. It needs to handle 60 projects without dragging. 3. The over-budget list on the Dashboard does not auto-update I built a table showing the top projects over budget. I manually update it every week because I could not figure out how to make it sort and filter automatically. This needs to be dynamic — it should always show the worst-performing projects without anyone touching it. 4. The labor rate is hardcoded The dollar variance calculation in column T of the Projects tab uses *85 in every formula — a blended labor rate I typed in two years ago. If our rates change I have to update 60+ formulas. This needs to be a named cell or a settings area somewhere. 5. The Crew Productivity tab is broken I tried to build a labor productivity summary by crew member — total hours across all jobs, average hours per job, estimated vs. actual ratio. I got three rows in and stopped. The problem is two things: crew names are inconsistent between the Crew and Crew Assignments tabs (I have "Torres, Kevin" in one place and "Torres, K" in another), and vacation/zero-hour rows in the Crew Assignments tab throw off the averages. This whole tab needs to be rebuilt properly once the data issues are addressed. 6. No early warning system Right now I find out a project is over budget after it is already over budget. I want some kind of flag that tells me a project is trending toward a problem — based on current burn rate vs. remaining hours — before it blows up. I do not know exactly what this looks like. Open to suggestions. 7. Crew-to-project lookup does not exist There is a note on the Project Lookup tab from March 2024. When I pull up a project I cannot see which crew members are assigned to it without manually filtering the Crew Assignments tab. This should be automatic. What good looks like Someone who opens the file, tells me specifically what they see, and proposes a clear plan for each of the seven problems above. Bonus points if you catch something I did not list. Deliverables 1. Upgraded workbook with all fixes implemented and tested 2. One-page documentation explaining what changed and why, in plain language 3. 30-minute walkthrough call with me at delivery Budget: $600–$1,000 fixed Timeline: 2–3 weeks To apply: Tell me what you noticed in the file. If your proposal does not reference something specific from the workbook I will not read it.