A commenter recently asked for a quick 3 statement model. If you don’t ask you don’t receive. Before we begin, feel free to email us at [email protected] and we can send you the excel sheet. Finally, before we “catch heat”, yes there are many other items that do matter and this one is for educational purposes, your bank, hedge fund, PE shop and or other finance job already has a pre-made model and template you will be working off of.
Instead of drowning on and on about how all the intricate equations you learned in college do not really matter that much in Investment Banking, Research or Sales and Trading or even the vast majority of the buy-side, lets just jump into the basics.
Income Statement: A basic statement doesn’t break out line items within sales, many large companies break out sales by segment such as iPad iPod.. .etc. With that said the basic version attached should be used by a college student or anyone who does not know how to read financial projections (ie: 99% of people you speak to that invest in stocks). The basics to modeling an income statement:
- Look at revenues and seasonality (the jump in Q4 suggests holiday/year end sales)
- Look at gross and operating margins (are they increasing/decreasing? Why)
- Look at your other interest line to make sure it checks out (our numbers are positive, no debt earning interest on cash)
- Tax rates seem to be creeping up, maybe the company had some shelters there should be a reason why or why not
- The share count is decreasing why? The cash flow statement shows a buyback program
Notable missing lines: year over year growth rates, incremental margins, breakout of revenue, breakout of margins by product line.
Balance Sheet: The basic changes are related to working capital – Current Assets/Current Liabilities – and if the Company has debt. This one is quite basic and runs off of either Cost of Goods Sold or Revenue. Not going to boil the ocean and try to get the number to a decimal. The basics to modeling a Balance Sheet:
- Cash balance links up to your ending Cash which is projected
- Calculate changes in current assets and current liabilities to see the working capital changes
- Link up your Equity to reflect buybacks, dividends and retained earnings
- Spot check for spikes in working capital
Notable missing lines: short and long-term debt.
Cash Flow: A basic statement models depreciation in cash flows from operations, CAPEX in investing activities and a dividend/buyback program adjustment in financing. If you want to get fancier you can add expected acquisitions and expected financing activities (raising debt/equity). Notably, you can also model the numbers off of sales or another metric, for simplicity it is flat lined. Basics to modeling a Cash Flow Statement:
- Link Net Income to the income statement
- Project our D&A (as a percent of sales? or Flat line?)
- Changes in working capital (does it swing in specific quarters?)
- CAPEX projection (as a percent of sales? or Flat line?)
- Buyback/Dividends (model out any stated programs over the next 4 quarters)
Notable missing lines: Foreign Exchange rates, acquisitions, stock based compensation and many financing and investing activities.
Formatting Basics: Green cells are linked cells (none in this sheet), blue cells are constants (may be changed and flows through the model) and black cells are formulas (should not be over written).
That’s the jist. If you have questions and feel free to post up in the Q&A for interviews or the Q&A for general Wall Street info. For fun if people want to add onto this model we can go through many “what if scenarios” and start adjusting the out quarters. It would be quite easy to build out an entire fake projected statement over many years, building in a few lines at a time.
