The Stock Brokerage KPI Dashboard in Excel is a month-by-month performance scorecard for a brokerage business. You type your own numbers onto three input sheets, choose a month from a single dropdown, and the whole workbook re-reads itself: fifteen KPIs, five KPI groups, month-to-date and year-to-date columns, achievement percentages, traffic-light status, prior-year comparatives and a top-and-bottom-five ranking.
Everything is plain worksheet formulas – VLOOKUP, MATCH, INDEX and COUNTIF. There is no macro to enable, no query to refresh, no data model to load and no add-in to install. The file opens and works in Excel 2013 and later, and in Excel for the web.
Every number you see in the screenshots is sample demo data shipped with the file so the scorecard is not blank on first open. None of it is real market, client or firm data, and none of it is a benchmark. You replace it with figures from your own systems.
Scorecard, not an analytical dashboard
NextGenTemplates publishes two different families of dashboard, and they are easy to confuse by name. This product is the KPI scorecard: a governance-style monthly review sheet built around targets, thresholds and traffic lights. The analytical dashboards – the ones with slicers, pivot-driven charts and record-level drill-down – are a separate line of products.
| This template (KPI scorecard) | An analytical dashboard | |
|---|---|---|
| Question it answers | Did we hit target this month and year to date? | What is driving the number, by segment? |
| Data you supply | One MTD and one YTD figure per KPI per month | A transaction-level table of many rows |
| Main control | A month dropdown and a KPI dropdown | Slicers and filters |
| Core visual | A traffic-light KPI table with achievement % | Charts broken down by category |
| Engine | Worksheet formulas only | Pivot tables or a data model |
| Best for | The monthly management pack | Ad-hoc analysis |
If you want the analytical view instead, see the related templates at the end of this page. Many buyers keep both: the scorecard for the monthly review, the analytical dashboard for the follow-up questions.
Key Features
- One month dropdown drives everything. Cell D6 on KPI Dashboard lists the twelve months of the reporting year. The scorecard and the KPI Analysis page both follow it.
- MTD and YTD side by side for every KPI – actual, target, achievement %, status, prior year and a vs-PY movement, in two mirrored blocks.
- Direction-aware scoring. Each KPI is flagged UTB (upper the better) or LTB (lower the better) in column G of KPI Definition. Achievement is Actual / Target for UTB and Target / Actual for LTB, so beating a cost or a response-time target scores above 100% instead of looking like a miss.
- Editable traffic-light thresholds. As shipped: On Target from 100%, At Risk 95% to 99%, Missed below 95%. The thresholds sit in the formulas in columns L and U on KPI Dashboard – change them to match your own governance.
- Seven summary cards across the top: Total KPIs Tracked, On Target (YTD), At Risk (YTD), Missed (YTD), Improving vs PY (MTD), Avg Achievement (MTD) and Avg Achievement (YTD). They recount themselves when you add or remove a KPI.
- Add or rename a KPI without touching a formula. Type it on KPI Definition and it flows through the three input sheets, the scorecard, the trend page and the analysis page. The sheets are wired for 22 KPIs; 15 are filled in and the rest are live and empty.
- Charts that follow the KPI you choose. Pick a KPI in cell B4 on KPI Trend and the attribute strip, the twelve-month table and both charts redraw.
- Re-base the whole year from one cell. Cell E3 on KPI Input – Actual is the first month of the reporting year. Change it and the Target sheet, the Prior Year sheet, the month dropdown and every sheet title follow.
- Nothing is locked or hidden. Every formula is readable and traceable, and you own the file for good.
What Is Inside – Page by Page
Home
A navigation page with a tile for each sheet, grouped into Dashboard Pages, Input Sheets and Reference & Help, plus a short summary of what the workbook does. Every tile is a hyperlink, and every other sheet carries a HOME link back.
KPI Dashboard – the scorecard
The main page. The seven summary cards sit across the top, the month picker on the left, then one row per KPI with a Month To Date block and a Year To Date block. Each block shows Actual, Target, Achievement %, Status, Prior Yr and a vs PY percentage with a direction arrow. The arrow shows the raw direction and its colour shows whether that direction is good for that particular KPI, which is why a falling cost gets a green down-arrow.
KPI Trend – one KPI, twelve months
Choose any KPI from the dropdown and this page shows its attribute strip (group, unit, type, owner, priority, frequency), its formula and definition, a twelve-month table of MTD and YTD actual, target, prior year, achievement and status, and two combo charts: MTD Trend for [the selected KPI] and YTD Trend for [the selected KPI]. Each chart plots actual and prior-year columns against a target line.
KPI Analysis – groups and rankings
A roll-up by KPI group showing how many KPIs sit in each group and how many are On Target, At Risk or Missed, with average MTD and YTD achievement. Beside it sit the Top 5 Performing KPIs (YTD) and Bottom 5 Performing KPIs (YTD) tables, and below it the Average YTD Achievement by KPI Group bar chart. The whole page follows the month picked on the scorecard.
KPI Input – Actual
Your current-year results. One row per KPI, one MTD column and one YTD column for each of the twelve months. The KPI rows are driven by KPI Definition, so they always line up with the other two input sheets. Cell E3 is the first month of the reporting year.
KPI Input – Target
The same grid for your targets. The month headers follow the Actual sheet, so you never re-type them.
KPI Input – PY
Last year’s results, in the same shape. The month headers are the Actual sheet shifted back twelve months, which is what feeds the Prior Yr and vs PY columns on the scorecard.
KPI Definition – the master list
The sheet that drives everything else: KPI number, group, name, unit, formula, definition, type (UTB or LTB), owner, priority and frequency. Add, rename or delete a KPI here and every other sheet follows automatically – no formula editing.
Read Me
A built-in manual page: the five-minute setup, the rules the numbers follow (MTD vs YTD, cumulative vs average YTD, UTB and LTB, achievement, status, arrows), how to add, rename, remove or go beyond 22 KPIs, and a map of every sheet in the file.
Get More Templates
A links page pointing at the rest of the NextGenTemplates catalogue and at custom-build enquiries.
There is also a hidden-in-plain-sight Support sheet holding the helper calculations – the selected month, the arrow glyphs, the month dropdown list, the trend series behind the charts, the distinct group list and the ranking helpers. Nothing on it needs editing.
The 15 KPIs Included
These are the KPIs the file ships with, and they are a starting point, not a prescription. Rename, delete or replace any of them on KPI Definition.
| # | KPI | Group | Unit | Type |
|---|---|---|---|---|
| 1 | Active Trading Accounts | Growth & Accounts | Count | UTB |
| 2 | New Account Openings | Growth & Accounts | Count | UTB |
| 3 | Funded-Account Conversion | Growth & Accounts | % | UTB |
| 4 | Daily Average Revenue Trades (DARTs) | Trading Activity | Count | UTB |
| 5 | Average Revenue Per User (ARPU) | Revenue & Economics | USD | UTB |
| 6 | Net New Assets | Revenue & Economics | USD (M) | UTB |
| 7 | Assets Under Custody | Revenue & Economics | USD (M) | UTB |
| 8 | Margin Balances Outstanding | Revenue & Economics | USD (M) | UTB |
| 9 | Cost Per Trade | Revenue & Economics | USD | LTB |
| 10 | Order Fill Rate | Execution Quality | % | UTB |
| 11 | Average Trade Execution Speed | Execution Quality | Minutes | LTB |
| 12 | Platform Uptime | Execution Quality | % | UTB |
| 13 | Client Asset Retention | Client Experience | % | UTB |
| 14 | Support First-Response Time | Client Experience | Minutes | LTB |
| 15 | Net Promoter Score | Client Experience | Index | UTB |
Each KPI also carries a plain-English formula, a definition, an owner role, a priority and a reporting frequency on KPI Definition – all of it editable text.
Who It Is For
- A retail or discount brokerage running a monthly management review
- A COO, CFO or head of operations who wants one page that says what hit target and what did not
- A head of trading, client experience or technology reporting a handful of owned KPIs upward
- A finance or BI analyst who needs a presentable scorecard today, without building one from scratch
- Anyone who wants a KPI-scorecard skeleton and intends to swap the brokerage KPIs for their own
How To Use It
- Open the file and look around. The sample data is already in place, so click through Home, KPI Dashboard, KPI Trend and KPI Analysis before you change anything.
- Set the reporting year. Put the first month of your year in cell E3 on KPI Input – Actual. The Target and PY sheets, the month dropdown and every sheet title re-base automatically.
- Edit the KPI list. On KPI Definition, rename the KPIs you want to keep, clear the rows you do not, and add your own in the empty rows. Set UTB or LTB per KPI in column G.
- Type your numbers. Fill KPI Input – Actual, KPI Input – Target and KPI Input – PY. Each sheet takes an MTD and a YTD value per month, so you keep full control of how your YTD is defined – a running total for volumes, a running average for rates and ratios.
- Pick a month on KPI Dashboard and read the scorecard. Then pick a KPI on KPI Trend to see its twelve months.
- Tune the thresholds. If your governance says At Risk starts at 97%, edit the threshold in the formulas in columns L and U on KPI Dashboard.
What This Template Is Not
Please read this before buying. This is a spreadsheet you type numbers into. It is deliberately not any of the following:
- Not a compliance or regulatory tool. It makes no claim about SEC, FINRA, SIPC, MiFID II, FCA or SEBI requirements, net-capital rules, books-and-records obligations, trade surveillance, best execution, AML or KYC, suitability or Reg BI. Nothing in it is designed or certified for regulatory reporting, and no regulator has reviewed or accepted it.
- Not a system of record. It is not a broker-dealer books-and-records system and it does not satisfy any record-retention obligation.
- Not connected to anything. There is no market data feed, no real-time quotes, no order routing and no link to any trading, execution, custody or clearing system. Every figure in it is a number you typed.
- Not investment advice. It says nothing about returns, client portfolio performance, or whether any security or strategy is suitable for anyone.
- Not a benchmark. The sample values, the targets and the 100 / 95 thresholds are placeholders chosen to make the file readable. They are not industry norms.
- Not a data-analysis tool. It summarises KPIs you supply; it does not analyse trade-level records. For that, see the analytical dashboards below.
Known Cosmetic Issues In This Build
Stated up front so there are no surprises. None of these affect the calculations:
- The Home page and the Read Me page both say 14 KPIs. The workbook actually ships 15. It is a stale label in two text boxes.
- The Read Me page’s “Cumulative or average YTD” note illustrates its point with examples from a sibling template (aircraft deliveries, non-conformance reports). The rule it describes is correct; the examples are not brokerage ones.
- Numbers are formatted with Indian digit grouping (for example 5,15,021.00 rather than 515,021.00). Select the cells and apply your own number format if you prefer Western grouping.
- Average Trade Execution Speed is stored in minutes at two decimal places, so it reads 0.01 in every month and its trend line is flat. The KPI’s own definition says roughly 0.4 seconds. Change the unit to seconds on KPI Definition and re-enter the values to make that KPI legible.
What You Get
- Stock Brokerage KPI Dashboard.xlsx – the full workbook, unlocked, with sample data
- Excel-KPI-Dashboard-User-Manual.pdf – the user manual for this KPI dashboard family
- Instant download after purchase, and lifetime access to the file you buy
- Single-user commercial use inside your own organisation
Frequently Asked Questions
Does this make my brokerage compliant, or connect to market data?
No – to both. It is not a compliance, surveillance or books-and-records tool and it satisfies no regulatory obligation of any kind. It also has no market data feed, no quotes, no order routing and no connection to any trading, custody or clearing system. Every number in it is a number you or a colleague typed in by hand.
Are the numbers in the screenshots real?
No. They are sample demo data generated so the file is not blank when you open it. They are not real brokerage figures and not industry benchmarks. Delete them and enter your own.
Do I need macros, Power Query or a data model?
No. Every number is an ordinary worksheet formula – VLOOKUP, MATCH, INDEX and COUNTIF. There is nothing to enable, refresh or install.
Which Excel versions does it work in?
Excel 2013 and later on Windows and Mac, and Excel for the web. It is a .xlsx file, not a macro-enabled workbook.
Can I change the KPIs?
Yes, and that is the intended use. Edit KPI Definition and every other sheet follows. The sheets are wired for 22 KPIs out of the box; the Read Me explains how to go beyond that.
Can I use it for something other than a brokerage?
Yes. The engine is generic – it is a monthly MTD/YTD scorecard. Replace the 15 brokerage KPIs with your own and the file works for any business.
How is achievement calculated?
Actual divided by Target for a UTB (upper the better) KPI, and Target divided by Actual for an LTB (lower the better) KPI. That way beating a cost or a response-time target scores above 100%, exactly as beating a revenue target does.
Can I change the traffic-light thresholds?
Yes. They ship as On Target from 100%, At Risk 95-99%, Missed under 95%, and they live in the formulas in columns L and U on KPI Dashboard.
Is this the same as your Stock Exchanges or Investment Banking dashboards?
No – those are separate templates for separate businesses, though they use the same scorecard engine. And do not confuse this scorecard with the analytical dashboards in the catalogue: those are slicer-and-chart tools built on a transaction table, whereas this one is a target-versus-actual review sheet.
Do I get updates or support?
You get lifetime access to the file you purchase. For a custom build – your KPIs, your branding, in Excel, Power BI or Google Sheets – contact us at info@NextGenTemplates.Com.
Related Templates
Other KPI scorecards built on the same engine, in the same format:
- Stock Exchanges KPI Dashboard in Excel – the exchange side of the same market
- Investment Banking KPI Dashboard in Excel
- Mutual Funds KPI Dashboard in Excel
- Family Offices KPI Dashboard in Excel
- Insurance Brokers KPI Dashboard in Excel
- Stock Exchanges KPI Dashboard in Power BI – the same scorecard idea in Power BI
Prefer the analytical view – slicers, category charts and record-level detail? Try the Hedge Fund Administration Dashboard in Excel or the Mutual Funds Dashboard in Excel. They answer a different question and pair well with this scorecard.
Browse the full range at KPI Dashboard templates.
Reviews
There are no reviews yet.