To score companies relative to peers in Excel, calculate three-year and five-year financial metrics like revenue and EBITDA, then normalize them using a rank-based formula where the score equals the company’s rank divided by the total number of peers. This approach ensures accurate comparative analysis without mathematical distortion.
Structuring Your Financial Data
Before applying any scoring logic, you must establish a clean dataset that allows for easy comparison across entities. The most effective structure places each company in a separate row, with columns dedicated to specific financial metrics over defined time periods. You should create distinct sections for 3-year and 5-year historical data to capture both short-term momentum and long-term stability. Start by listing your peer group in Column A. In subsequent columns, input the raw financial figures for Revenue, Gross Profit, and EBITDA. It is critical to maintain consistent formatting; ensure all currency values are treated as numbers rather than text. If your data source includes non-financial identifiers, such as ticker symbols or industry sub-sectors, include these in hidden columns or separate sheets to aid in filtering later. For the time periods, consider using dynamic date ranges if possible, but static year-over-year columns (e.g.,Rev_2021, Rev_2022) are often more stable for manual modeling. Ensure that every peer has complete data for the selected metrics. Missing values can skew rankings significantly if not handled explicitly through IFERROR functions or placeholder zeros, depending on your strategic view of missing data.
Calculating Aggregate Metrics
Raw annual figures provide snapshots, but scoring requires aggregated performance indicators. For each metric, you will likely need to calculate averages or compound growth rates over the specified windows. To find the average revenue over the last three years, use theAVERAGE function across the relevant cells for that specific row.
For example, if Revenue data for years 2020, 2021, and 2022 sits in cells C2, D2, and E2, the formula would be =AVERAGE(C2:E2). Repeat this for Gross Profit and EBITDA. Some analysts prefer Total Revenue Growth Rate instead of simple averages. In that case, calculate the percentage change from the earliest year to the latest year within the window.
It is also wise to create a summary table that consolidates these calculated aggregates. This separation keeps your raw data intact while allowing your scoring engine to reference clean, processed numbers. This modularity makes it easier to adjust assumptions later, such as changing the lookback period from three to five years, without rewriting core formulas.
Implementing the Normalization Formula
The core of the scoring system lies in normalization. Raw financial sizes are incomparable; a large-cap tech firm naturally has higher revenue than a small-cap industrial firm. Therefore, you must rank each company against its peers for each metric individually. A common mistake is usingRANK.EQ directly as the score. This is mathematically incorrect because a rank of 1 out of 10 does not equal a normalized value of 1. Instead, divide the rank by the total count of peers. This yields a decimal between 0 and 1, representing the percentile position.
The correct formula for a given metric is:
=RANK.EQ(AverageValue, EntireColumnRange) / COUNT(EntireColumnRange)
Here, AverageValue is the calculated aggregate for the specific company. EntireColumnRange must be an absolute reference (e.g., $C$2:$C$50) to ensure the range stays fixed when copied down. COUNT ensures that only numeric entries are included in the denominator, preventing division errors if empty cells exist.
This method produces a score where 1.0 indicates the top performer and lower values indicate poorer relative performance. Note that for negative metrics like Debt-to-Equity ratios, you may want to invert the ranking so that lower debt results in a higher score. You can achieve this by subtracting the result from 1: =1 - (Rank/Total).
Weighting and Final Scoring
Once you have normalized scores for Revenue, Gross Profit, and EBITDA, combine them into a final composite score. Assign weights based on what matters most to your investment thesis. For instance, you might weight EBITDA at 50% due to its focus on operational efficiency, while weighting Revenue at 30% and Gross Profit at 20%. Use theSUMPRODUCT function or simple weighted addition to generate the final score. If your normalized scores are in columns F, G, and H, and weights are in cells J1, J2, and J3, the formula would be:
=F2*J1 + G2*J2 + H2*J3
Sort the final list by this composite score in descending order to instantly identify the strongest performers relative to the peer group. This structured approach eliminates bias and provides a transparent, auditable methodology for financial analysis.
Frequently Asked Questions
How do I handle companies with negative EBITDA?
Negative EBITDA indicates losses, which complicates standard ranking. You can rank them normally, where less negative is better, or exclude them entirely if you only seek profitable peers. Another option is to rank absolute values, but this misrepresents financial health. Always document how negatives are treated in your model notes.What if my peer group size changes annually?
If new competitors enter or exit the market, the denominator in your normalization formula changes. Use dynamic ranges likeCOUNTA combined with offset functions, or simply update the static range manually. Consistency is key; try to keep the peer group definition stable over time to allow for longitudinal comparison.
Can I use this method for non-financial metrics?
Yes, provided the metrics are quantifiable and comparable. Employee count, R&D spend, or customer acquisition costs can be normalized similarly. Just ensure that the directionality of improvement is clear; for cost metrics, lower is usually better, requiring the inversion formula mentioned earlier.Why is RANK.EQ preferred over PERCENTRANK?
RANK.EQ gives you the integer position, allowing you to control the normalization logic precisely. PERCENTRANK returns a pre-calculated percentile which can sometimes round differently or behave unexpectedly with duplicate values. Using RANK.EQ / COUNT gives you explicit control over edge cases and ties.