How to Use This Resource: This resource covers the three analytical tools you will use in the Reading the Trend assignment: year-over-year analysis, CAGR, and anomaly identification. It also explains how to create charts in Excel. Refer back to it while working through the workbook.

Trend Analysis Toolkit

This resource covers the three analytical tools you will use in the Berkshire Hathaway exercises: year-over-year analysis, CAGR, and anomaly identification. It also explains chart types and how to create them in Excel.

Financial trend analysis means identifying patterns in a company's data over time. Rather than looking at a single year's numbers in isolation, you compare multiple years to answer questions like: Is this company growing? Is it becoming more or less efficient? Are there warning signs? This resource covers the core techniques you will use in your workbook.

1. Year-over-Year (YoY) Growth

Year-over-year analysis compares the same metric across consecutive years. It answers the simplest trend question: did this number go up or down, and by how much?

Two ways to express YoY change:

Dollar change: Current Year Value - Prior Year Value
Percentage change: (Current Year - Prior Year) / |Prior Year|

Both are useful. Dollar change tells you the magnitude: "Revenue increased by $30 billion." Percentage change tells you the rate: "Revenue grew 12.5%." A $30 billion increase means something different for a $250 billion company than for a $50 billion company.

In Excel: If 2020 revenue is in cell C6 and 2019 revenue is in B6:

Dollar change: =C6-B6
Percentage change: =(C6-B6)/ABS(B6)

Why ABS() in the denominator? If the prior year value is negative (like a net loss), dividing by a negative number would flip the sign of your percentage. Using the absolute value keeps the direction correct.

2. Compound Annual Growth Rate (CAGR)

CAGR smooths out year-to-year fluctuations to show the average annual growth rate over a period. It answers: "If this metric had grown at a perfectly steady rate from the start to the end, what would that rate have been?"

CAGR = (End Value / Start Value) ^ (1/n) - 1

where n = the number of years between start and end

Example: A company's revenue grew from $100M in 2019 to $150M in 2023. That is 4 growth intervals (2019 to 2020, 2020 to 2021, 2021 to 2022, 2022 to 2023), so n = 4.

CAGR = (150 / 100) ^ (1/4) - 1 = 1.5 ^ 0.25 - 1 = 10.7%

In Excel: If the start value is in cell B6 and end value is in F6:

=(F6/B6)^(1/4)-1

When CAGR is useful:

Comparing growth rates across companies of different sizes
Summarizing long-term trends in a single number
Metrics that grow relatively steadily (like revenue, assets)

When CAGR is misleading:

When the path between start and end was highly volatile
When the start or end year was unusual (an outlier distorts the whole calculation)
For metrics that can go negative (CAGR is undefined if the start value is negative)

Key insight: CAGR only looks at two data points: the first and the last. It tells you nothing about what happened in between. A company could have lost money for three straight years and still show a positive CAGR if the final year was strong enough.

3. Identifying Anomalies

An anomaly is a data point that breaks the pattern. Spotting anomalies is one of the most important skills in financial analysis because they often reveal the most interesting stories.

How to spot anomalies:

Sudden spikes or drops: A metric that grew 5-10% per year and then drops 40% demands investigation.
Divergence between related metrics: If revenue grows but net income falls, something changed in costs, taxes, or one-time items.
Reversals: A metric that was trending upward suddenly reverses direction.

What to do when you find one:

Name it: "Net income dropped 48% in 2020."
Ask why: Was it a one-time event? A structural change? An accounting adjustment?
Assess impact: Does this anomaly change the long-term trend, or is it noise?

Common causes of anomalies in public company data include: economic recessions, acquisitions or divestitures, one-time charges (impairments, legal settlements), accounting rule changes, and industry-specific events.

4. Chart Types and When to Use Them

Chart types, their best uses, and examples
Chart Type	Best For	Example
Line chart	Showing trends over time for one metric	Revenue from 2019 to 2023
Clustered bar chart	Comparing two or more metrics side by side	Operating earnings vs. net income by year
Single bar chart	Showing growth rates or comparing values across categories	YoY revenue growth percentage by year
Stacked bar chart	Showing composition (parts of a whole)	Revenue breakdown by business segment

5. Creating Charts in Excel

Select your data: Highlight the cells containing the data you want to chart, including the labels (year headers and metric names).
Insert the chart: Go to the Insert tab and choose your chart type (Line, Bar, etc.).
Add a title: Click the chart title placeholder and type a descriptive title (e.g., "Berkshire Hathaway Total Revenue, 2019-2023").
Label your axes: Click the chart, then use the Chart Elements button (+) to add axis titles. The Y-axis should state the unit (e.g., "$ Millions").
Format for readability: Remove unnecessary gridlines, increase font sizes if small, and use colors that are distinguishable.

Formatting matters. A chart that is hard to read is worse than no chart. At minimum, every chart needs: a descriptive title, labeled axes with units, and text large enough to read without squinting.