Executive briefing / synthesis
New business prospecting reporting across eight business lines
A VP spent 10 to 12 hours a month reconciling eight teams' numbers by hand. They now spend five minutes.
- problem
- Eight business lines reported prospecting performance in eight shapes. An executive consolidated them by hand each month.
- approach
- One normalization table that absorbs each team's existing output, validation at the point of entry, scripted assembly of the deck.
- result
- 10 to 12 hours of monthly VP effort down to a 5 to 10 minute review.
- scale
- Eight business lines. Fortune 500 automotive division. Reporting that reaches the enterprise CEO and the shareholder board.
The problem
Eight business lines report new business prospecting performance to Global Automotive leadership every month. Finance publishes the refreshed numbers. Business leaders add commentary and pipeline detail. Someone then makes all of it agree.
That someone was a VP, and he did it for a sound reason. Consolidated prospecting performance stays inside a small leadership group. The same reporting goes to senior executives across Global Automotive and, at the enterprise level, to the CEO and the shareholder board. He could not hand the reconciliation to an analyst. So he resolved it himself: eight definitions, eight spreadsheet shapes, eight levels of completeness, 10 to 12 hours a month. Bad months ran longer.
The confidentiality requirement is what made the work cost so much. It kept the job with the person whose hours were worth the most.
You might fix this by mandating a common reporting format. That would have failed. The teams had no spare capacity, and their formats existed for local reasons that held up. Some lines ran on Salesforce. Others had never migrated and worked in standalone Excel. Ask eight teams to change how they work and they will ignore you, with cause.
What I built
Normalization. I built a structured table in Microsoft Lists that everything resolves into. For the Salesforce lines, I worked with a business solutions architect to automate a report exposing the columns my table needed, so those teams keep working in Salesforce and touch nothing new. For the Excel lines, I rebuilt their sheets as Lists with forms. Those teams got a faster input path than the spreadsheet they had been maintaining, so I never had to sell them on it.
Validation at entry. The failure I cared about was a wrong number reaching the board. Rules and conditional formatting catch bad data as someone types it. The assembly scripts run their own standards checks before they generate anything. Lists keeps version history, so I can trace every change. I also built automated stale-data notifications. A gap now shows up as a prompt to the team that owns it instead of a hole in an executive deck.
Assembly. Claude Code scripts generate the monthly deck from the normalized table. Power Automate and Power Apps run the orchestration. Power BI drives the reporting output. A Copilot Studio agent helps business leaders draft their commentary and pipeline updates, which is the part of the process that still needs a person.
Where it stands
Finance signals that the numbers are ready. Business leaders update in whatever system they already use. The automations do the rest. Leaders review their own slides and any stale-data flags. The VP clicks through in five to ten minutes and posts it.
Nobody reconciles anything. Adding a business line means I write one mapping instead of negotiating a standard.
I am now working with the solutions architect team to replace the scheduled Power Automate runs with direct API feeds. That removes the monthly trigger and lets the reporting layer read from source as the data changes.
I designed and built this.
Questions about how this works, or want to talk about building something similar? abraytondesign@gmail.com