Vertical Analysis And Horizontal Analysis

How We Actually Do Both Kinds Of Analysis In A Real Finance Team

I have been doing this since before Excel had a native sparkline function. Most people confuse vertical and horizontal analysis because they are taught as separate chapters in a textbook. In practice they are the same worksheet with two different reference cells. The method is simple, the edge cases are where people lose money. Vertical analysis means every line item becomes a percentage of a single base figure within the same period. Revenue is the denominator for an income statement, total assets for a balance sheet. You pick one number, divide every other number by it, and you get a structure that lets you compare a bakery to a tech company without worrying about scale. The formula is literally cell divided by base cell, locked with absolute references. I use $B$2 as my base almost every time because it never moves when I drag the formula down. Horizontal analysis means you compare the same line item across periods. Year over year growth, quarter over quarter, whatever cadence your reporting requires. The formula is current period minus prior period, divided by prior period. Again, absolute references lock the prior period column so you can drag horizontally across five years without breaking the math. The output is a growth rate, not a dollar change, because growth rate is what actually moves decisions.

When you run both on the same sheet, vertical analysis sits in columns E through J and horizontal analysis sits in columns M through R. Column E is revenue percent of total, column M is revenue growth from prior year. The spreadsheet is bigger, but the logic is clean. You stop switching tabs and stop losing context.

What Happens When A Single Item Breaks The Percentages

I ran into this last fall with a midmarket manufacturing client. Their COGS flipped negative for one quarter because of a massive inventory writeback that the ERP dumped straight into cost of goods sold instead of a separate line. The vertical analysis percent exploded to minus 140 percent. The horizontal growth rate was plus three thousand percent. Both numbers looked real. Both were wrong for decision making. The workaround is straightforward if you know to do it. I added a helper column that flags any variance greater than three standard deviations from the trailing twelve quarters. When that flag fires, the analysis switches from growth rate to absolute change and the dashboard displays a footnote. The footnote says something like abnormal item excluded from trend calculation. It is not elegant, but it stops the report from looking confident while lying. I keep the original numbers visible underneath so auditors can see them if they care. You should also flag any base figure that approaches zero. A revenue base of forty thousand dollars makes every expense line look like it grew eight hundred percent when the absolute movement was two thousand dollars. The percentage is mathematically correct and operationally useless. I suppress the growth rate display when the base is under one percent of the prior year total and show the raw delta instead. The reader gets the same information without the illusion of significance.

The Common Mistakes That Waste Time

The first mistake is using the wrong base for vertical analysis. People often pick total expenses when they should be picking revenue on a profit and loss statement. The resulting percentages reverse the natural hierarchy and make the structure unreadable. Revenue to revenue gives you contribution margin. Expenses to revenue gives you operating leverage. They answer different questions. Pick the question before you pick the base. The second mistake is comparing like periods without adjusting for calendar drift. Quarter four always has more retail revenue because of holidays. Year over year growth for Q4 will show a spike that looks like performance improvement when it is just December. I adjust by showing both nominal year over year and same store comparable growth, and I highlight the difference. The gap between those two numbers is where the real story lives. A third mistake is ignoring negative denominators in horizontal analysis. If prior year revenue was negative, the growth rate formula returns an inverted sign that looks like a massive recovery when it is just a accounting normalization. I check the sign of the prior period before calculating growth. If prior is negative and current is positive, I flag it as a structural change rather than a growth event. The label matters more than the number because management will repeat whatever label you give it in meetings.

When Neither Method Works Well Enough

Vertical and horizontal analysis assume linearity and stable business structure. They break down when a company goes through acquisition, divestiture, or a major accounting policy change. The periods before and after are not comparable, so both methods produce noise. I had a client who acquired a competitor mid-year and tried to run growth analysis on the consolidated results. The year over year comparison looked like thirty percent growth when the actual organic growth was six percent. The acquisition added twenty-four percentage points of noise. The fix is to create a pro forma column that restates prior periods on a consistent basis. You tag each line item with its source period and apply a conversion factor so everything sits on the same footing before you calculate percentages or growth rates. It takes about twenty minutes per quarter per client once you have the tagging convention set up. The alternative is presenting unadjusted numbers and watching management make decisions based on artifacts. There is also the seasonal business problem. A snowblower manufacturer in Minnesota will always show a negative horizontal growth in March and a positive spike in October. Vertical analysis will make inventory look normal in March and absurd in October. The solution is seasonal adjustment using a moving average or a year-over-year seasonal index. I usually build a separate tab that applies a twelve-month centered moving average to smooth the seasonality, then run the analysis on the deseasonalized series. The numbers look less dramatic but they are closer to what is actually happening.

How I Build The Sheet So It Does Not Break

I start with a raw data import, never manually typed numbers. The source is the general ledger extract or the ERP PDF export. I map every account to a standard chart of accounts code in a separate mapping tab. That mapping tab is where I catch duplicate accounts, missing subtotals, and reclass entries. If I skip the mapping step, the analysis will include accounts that belong in other categories and the percentages will be wrong even though the formulas are right. The main analysis sheet uses structured references whenever possible. Excel tables auto-expand when new rows are added, which means I do not need to touch the formulas when the client adds a new cost center. The vertical analysis formulas sit in a calculated column that references the table header. The horizontal analysis formulas reference the prior column by position, not by letter, so column shifts do not break the layout. Both calculations are in separate sections of the same sheet with clear visual dividers. I put a thin gray border around the vertical section and a thin blue border around the horizontal section so anyone can tell which is which at a glance. I also add a validation section at the bottom. Total vertical percentages must sum to one hundred percent. Any deviation above point one percent flags a data issue. Total horizontal growth rates for revenue and net income should move in the same direction unless there is a documented margin event. If they diverge without a flagged reason, I add a red note next to the cell. The validation catches transcription errors before the report leaves my desk. It usually catches one error per quarter that would otherwise sit in the board pack for two weeks.

What I Wish Beginners Understood Sooner

The numbers are only as good as the classification. Vertical analysis of operating expenses will look very different depending on whether you put depreciation inside operating expenses or under a separate line. Horizontal analysis of gross margin will behave differently depending on whether you include freight-in in COGS or in inventory. There is no universal right answer. There is only a consistent answer. Document your choices in a footnote and stick to them across periods. Switching classification mid-year is the fastest way to make both analyses look like problems when the only problem is inconsistency. Also, percentages hide magnitude. A ten percent growth on a two hundred thousand dollar line is a two thousand dollar change. A ten percent growth on a two million dollar line is a two hundred thousand dollar change. Both get labeled ten percent growth in the horizontal analysis. I always include the absolute change column immediately to the right of the growth rate column. It takes two extra keystrokes per formula and prevents the common mistake of promoting a trivial dollar movement because the percentage looks flashy. The board does not care about percentages. They care about what the percentage moved in actual cash. Vertical analysis and horizontal analysis are not separate disciplines. They are two lenses on the same data. Use both, watch for the edge cases, and do not let a clean percentage fool you into ignoring the underlying structure. That is how you keep the report honest and keep your reputation intact.