Building a sales-based pro forma income statement

What does a sales-based model look like?

A financial statement model is a spreadsheet that projects a company’s statements from a few assumptions, and it is the starting point for most valuations. In a sales-based model, revenue is the first forecast and almost everything else hangs from it.

The pro forma income statement is built top down in four steps: revenue, operating costs (cost of sales, then selling, general, and administrative expenses, or SG&A), operating profit, and finally the non-operating items (interest, tax and the share count).

Pro forma income statement

• Revenue Memorize segments: volume, price/mix, currency
• Operating costs Use it each follows its own driver
  • Cost of sales % of revenue, or fixed + variable
  • SG&A and other often partly fixed
• Non-operating items Use it below operating profit
  • Interest rate × debt
  • Income tax expected effective rate
  • Shares buybacks, issues, dilution

Each step below uses one invented company, Caldera Beverages, a spirits maker that reports in euros. Last year it had revenue of EUR 2,000m.

How do you forecast revenue?

Split revenue growth into its revenue drivers , forecast each, and compound them. The drivers are volume growth (more or fewer units), price/mix (price changes plus a shift toward pricier or cheaper products) and the foreign currency effect (translating foreign sales into the reporting currency).

$$R_1 = R_0\,(1 + V)(1 + PM)(1 + FX)$$

Acquisitions and disposals are a scope change . They are forecast separately and are not part of organic growth. Organic growth is the part that comes from the existing business: volume and price/mix only.

$$O = (1 + V)(1 + PM) - 1$$

Reported revenue growth

• Organic growth Memorize what the existing business did
  • Volume growth more or fewer units sold
  • Price/mix price changes plus a shift toward pricier or cheaper products
• Not organic Memorize reported separately
  • Foreign currency effect translation of sales made abroad
  • Scope change acquisitions and disposals

The exam also asks for a driver from the other side: the price/mix that reproduces a revenue target, or the volume needed. It is the same formula, turned around.

Exam tip If one option equals the sum of the growth rates applied to last year’s revenue, it is the additive trap. If one leaves out the currency term, it is the “forgot FX” trap (FX is foreign exchange).

Which revenue method does the question use?

Behind the drivers sits a choice between top-down and bottom-up forecasting . A top-down forecast starts at the economy: nominal gross domestic product (GDP) growth (real growth plus inflation), then the industry’s growth relative to GDP, then the company’s share of the industry. A bottom-up forecast starts inside the company: segment volumes, prices and currency, or simply the company’s own average growth. Many analysts use one as a check on the other.

Revenue forecast approach

• Top-down Use it starts at the economy
  • Nominal GDP growth real growth plus inflation
  • Industry sales growth relative to GDP
  • Company sales industry sales × market share
• Bottom-up Use it starts at the company or segment
  • Segment drivers volume, price/mix, currency
  • Company history average growth rate and margins

How do you forecast the costs?

Forecast cost of sales and SG&A as a share of revenue, or as a fixed amount plus a variable share. Use the split whenever volume may move a lot, because only the variable part moves with revenue.

The simplest method is cost of sales as a percentage of revenue , using the recent ratio adjusted for what you expect (a richer mix lifts gross margin, for example). For Caldera, cost of sales is 40% of revenue: 0.40 × 2,139.9 = EUR 856.0m.

When some costs do not scale with volume, split them. This is the fixed and variable costs approach.

$$C = F + c \times R$$

The SG&A forecast works the same way. It is often modeled as a percentage of revenue, or as last year’s amount grown at a chosen rate when the costs are mainly salaries and rent. Caldera’s SG&A is EUR 300m fixed plus 15% of revenue.

Go deeper Why a constant percentage can mislead when volume falls

Suppose total costs are 400 fixed plus 30% of revenue, and revenue is 1,000. Costs are 700 (70% of revenue) and operating profit is 300. Revenue now falls 10% to 900.

• Constant-percentage method: costs = 70% × 900 = 630, so profit is 270.
• Fixed plus variable method: costs = 400 + 0.30 × 900 = 670, so profit is 230.

The constant-percentage forecast overstates profit by 40, because the fixed 400 does not shrink. This is operating leverage, and it works in both directions. When volume rises, the same shortcut understates profit.

What comes below operating profit?

Three non-operating items finish the statement: interest, tax and the share count. These non-operating items each have a different driver.

Interest. Forecast it from the balance sheet: the interest rate times the debt balance (usually the opening or average balance), less any interest income on cash and investments. A variable rate is a market reference rate plus a credit spread. Do not carry last year’s interest amount forward if debt will change.

Tax. Apply the effective tax rate forecast , the rate the company expects to pay on its pretax profit. It can differ from the statutory rate because of credits, foreign profits and non-deductible items. If the company’s effective rate has stayed close to the statutory rate for years, using the statutory rate is a fair shortcut. Otherwise use the effective rate.

Shares. The shares outstanding forecast starts with the weighted average basic count and adds the dilution from options and convertibles. Share-based pay raises the count, buybacks lower it. If the company has no buyback program and issues little stock, hold the count flat.

$$EPS = \frac{(EBIT - I)(1 - t)}{\text{shares}}$$

Try the whole model below. Change one driver at a time and watch which lines move.

Go deeper Checking the total with a segment build

Most companies report revenue and operating profit by segment. Forecast each segment’s growth and margin, add the segments, and subtract the corporate costs that are not allocated. Compare the result with the consolidated forecast. If they disagree, one set of assumptions is off. The check also shows which segment the forecast depends on: a model in which one high-margin segment carries all of the profit growth is only as good as that segment’s assumptions.

Go deeper Treating one-off items in the base year

Growth rates apply to a base year. If last year’s revenue or costs contain one-off items, such as a restructuring charge or a gain on a disposal, remove them from the base before applying the forecast growth. Otherwise the one-off is projected into every future year. The earlier lesson on non-recurring items covers how to spot them.