Forecasting the Income Statement
Thankfully, forecasting the Income Statement is one of the more straightforward Financial Modeling concepts, because we're effectively just mapping out (a) what we sell, and (b) what our associated expenses are, to (c) find our net profit.
We'll save the trickier Balance Sheet and Statement of Cash Flows for subsequent issues.
Income Statement Layout
Here's a typical layout I use in most models:
- Revenue
- COGS
- Gross Profit
- Operating Expenses (OpEx)
- Operating Income
- Tax Accrual
- Net Income
- EBITDA (as Defined, as Adjusted, etc. — my PE background can't help itself)
Bones First, Details Second
Once you've got the basic layout done, it can be easy to immediately jump into a complicated "Revenue Build," which is typically a separate schedule that models out Revenue by customer or product.
However, I'd propose something simpler first: just keep everything flat. In other words:
- Revenue equal to prior year
- COGS is a trailing % of Revenue
- OpEx equal to prior year
- Tax Accrual is a trailing % of Operating Income
Once the bones are good (in other words, the model is structurally sound), then you can confidently add details.
Modeling the Details
Instead of making 40 million untraceable tabs, I prefer to take the 80/20 approach, which is to say, "pick the handful of items that materially drive the business, and keep the rest simple."
Every company is different, but these items are typically:
- Revenue
- Headcount
- Primary Vendors
- Two to Four OpEx items (based on company)
- Capital Expenditures (for the Balance Sheet)
Build detailed schedules around these core items, link them to your "good bones model," talk with your team, and iterate from there.
For "simpler stuff" like Office Supplies, Travel, and the like, usually a modest year-over-year growth assumption works just fine (at least as a placeholder).
Not only does this make your model cleaner and easier to manage, it also makes it "scenario-friendly," meaning you won't have to move Heaven and Earth to start building scenarios, which always gets complicated.
The Forecasting Process ("EPN")
I'm going to steal an acronym from my friend Josh Aharonoff that I realized I'd been doing for several years, but that he eloquently put into words. That is: "Existing, Pipeline, New."
It's easiest to understand in the context of Revenue, but applies to all line items, so let's start there.
Part 1: Who are your Existing Customers?
Will they stay with us? Go up? Go down?
A customer-by-customer buildout is best here.
Part 2: Who is sitting in the Pipeline?
Who will we close? What are the odds?
You'd model out (a) the projected Revenue multiplied by (b) the likelihood of closing the deal.
Part 3: Who's completely New?
Modeling here is directional and scenario-prone, and should be meaningfully discounted since it's mostly theoretical.
Part 4: Subtotal
The first three parts above will subtotal to "Total Revenue" (or whatever the schedule may be), and you would link that back to your "good bones model," with the confidence that it will flow correctly down to Net Income.
Btw, here's the link to Josh's full post. It's excellent and comes with a great infographic. Check it out.
Part 5: Iterate and Modify
As modelers, sometimes we can feel like we're sitting alone on an island, and "must know everything about the business" to create the perfect forecast.
In reality, the opposite is true. You should constantly be talking with your team about the latest assumptions and projections, and then going back and modifying things. It is a living, breathing, iteration until all are confident.
Other Schedules
The "EPN Framework" works great for nearly every schedule in your model. Using Headcount as another quick example:
- Existing: what salary and bonus will current employees make this year?
- Pipeline: who do we need to hire in the near term and what is their likelihood of joining us? (one note, I will always assume 100% likelihood to make my model conservative)
- New: who do we have to hire as time goes on to meet our strategic objectives? (this might be a build-up by position instead of by name)
A Process, Not Just a Spreadsheet
Leading Management through the "EPN Process" alongside multiple iterations is what I learned to do with every Portfolio Company we acquired in Private Equity and every client I've helped in the FP&A world.
It's as much about managing time, schedules, and work styles as it is punching numbers into your Financial Model, so keep that in mind as you discuss line items with different people in the company, and give yourself extra time to complete a budget or forecast.
In Summary
- Build the bones
- Model the 80/20
- Use the "EPN Framework" for your sub-schedules
- Iterate, modify, and manage time
That's it for today. See you next week.
—Chris