How to Build an Excel Earnings Comparison with Sources, Formulas, and Analysis
Learn to fix periods and units before calculating revenue growth and margins in Excel. Separate inputs from formulas and handle losses, missing data, and amended figures.
An Excel earnings comparison is easier to update and check when source data, calculations, and interpretation are separate. Align each company’s periods, accounting scope, and units before calculating growth rates and margins. A structure that lets you trace inputs back to their sources matters more than attractive charts.
Define rows and columns first
For a single company’s trends, it is convenient to use columns for accounting periods and rows for items such as revenue and operating profit. For multiple companies, you can build a long-form source table with separate company, period, and account fields, then create the comparison view separately. Whatever the structure, do not mix annual figures and quarterly cumulative figures in the same column. Keep the original URL, table location, consolidated/separate basis, units, and actual/estimate label beside the data. A table containing pasted numbers alone becomes difficult to reconstruct over time, so record sources at the input stage.
Separate growth calculations from display formatting
If prior-year revenue is in B2 and current-year revenue in C2, and the prior-year value is positive, calculate growth with =C2/B2-1 and display it as a percentage. Operating margin is operating profit divided by revenue for the same period. If the denominator is zero or the comparison period is missing, mark the calculation as unavailable rather than turning it into 0% growth. For earnings that turn from a loss to a profit, explaining the turnaround and absolute change first is easier to understand than a simple growth rate. Hiding every error as a blank makes missing inputs difficult to distinguish from calculations that cannot be performed, so retain the reason.
A correct formula can still use the wrong comparison basis
When copying formulas, check that references move to the intended cells. If the first part of a table contains single-quarter figures and the later part contains cumulative figures, calculations can run correctly while producing a misleading result. Distinguish input values from calculated values in the display, and start revisions with the source data. When an amended filing appears, trace which calculations and charts depend on the changed inputs. Check a few representative items for totals matching the original, margins remaining unchanged after unit conversion, and correct year-over-year comparisons. This reduces errors without manually recalculating everything every time.
Worked example
Hypothetical example: Suppose prior-year revenue is 800, current-year revenue is 1,000, and current-year operating profit is 120, all in units of KRW 100 million. Growth is 1,000 ÷ 800 − 1 = 25%, and operating margin is 120 ÷ 1,000 = 12%. With 1000 entered in C2 and 800 in B2, the growth formula’s underlying value is 0.25, displayed as 25% with percentage formatting. Multiplying the formula by 100 and also applying percentage formatting converts it twice, so use one method consistently.
What to check
- Have you distinguished source-data cells from formula cells?
- Have you aligned annual, single-quarter, and cumulative periods?
- Have you distinguished division by zero from division with missing data?
- Have you avoided applying percentage conversion twice through calculation and formatting?
Sources and primary materials
This is educational material about research methods. For an actual company decision, verify the filings and primary sources for the relevant period.