Views
Notes
Folders
Reading List
Read
Financial Modeling 101: Part I - Revenue & Income Statement Build-Up old
An article by • Published Feb 12, 2021

Follow Video Tutorial Along With Note



Excel File Shown In Video Is Available In Part III

After nearly 500 likes and tens of positive responses to this Tweet, I cornered myself into building a 3-statement model from scratch, something I hadn't done in many years. But if you are an investor, if you are in corporate finance, if you are in business development, banking, or any tangentially-related position - or want to be - building a model from scratch is a good learning exercise. Take all the accounting and finance classes you want, but sometimes you just need to see what it all means mathematically and practically for the light to go off.
By no means does one need to build a model for every company or every investment. But once you have taught yourself the how and why of the income statement, cash flow statement and balance sheet harmony, it gives you a foundation and mental model that you can rely on when discussing businesses and the drivers of their performance.

What This Isn't

NVIDIA Corporation (ticker: $NVDA) is the company being modeled in this example. By no means is this an attempt to value the stock for investing purposes or give investment advice. Please do not rely on the numbers for that purpose as they are simply illustrative. NVIDIA is indeed exposed to many of the secular growth trends of the next few decades, including artificial intelligence, machine learning, cloud, inference, autonomous driving, gaming, among other vectors, making it a popular investment and well-known name. Its financial statements are also quite clean, making it relatively easy to model. NVIDIA also highlights that it is difficult to forecast beyond the next few years. In 2015, NVIDIA management was discussing its datacenter business growing into a $1 billion business. Datacenter was a $3 billion line item in 2020.
Colette Kress You are correct that our high performance computing and cloud with our overall Tesla platform is extremely strong. We finished last year growing more than 50%, approaching nearly a $300 million business. Associated with not just high performance computing in parallel but really that expansion into new workloads and ISPs, internet service providers using this for deep learning, training on anything from image detection, voice recognition, or voice translation. So, we've seen just a continued growth of that TAM. I don't think we're anywhere near the end of that.FPGAs are another form of acceleration that's possible. I could see the interest in the market of approaching the desire, the TAM that's out there for acceleration. But there's different ways to get it done and a GPU in terms of its overall performance, its ease of programming. You're talking about a programming language based on C++ which is generally very familiar and very taught in many different forms across the world.So, it's a great market. We do have a very strong leadership position and continuing you will see big names come forth and talk about how much they're using these GPUs in those type of workloads. Vivek Arya Got it. I believe Jen said perhaps at Computex then that over many number of years he sees this growing to somewhere close to $1 billion. At least that's where he sees the market opportunity.


What This Is

The goal of this exercise is simple. Share knowledge and attempt to make complex items simple. Think of this as Accounting 101 / Corporate Finance 101 rolled into a 45 minute three-part series around a modeling exercise. The model isn't absurdly complex primarily because the goal is a high level overview of a company's financials and explaining what they mean in practice. The notes tie to the video and vice-versa. We've split the notes and videos into three pieces to allow users to digest and understand. Move at your own pace. Try to understand the first section before the second and so on, as there is reasoning behind the sequencing. The excel file shown in the videos is provided in Part III to allow you to download and sensitize yourself while seeing all calculations along with line item descriptions. The series is broken down into the following parts:
1.
Revenue & Income Statement Build-Up
2.
Balance Sheet, Working Capital & Cash Flow Items
3.
Financing Activities, Capital Structure & Valuation

Model Notes

Standard modeling convention (in my experience) typically uses BLUE text for manual or hardcoded inputs, BLACK text for calculations and GREEN text for data pulled from other sheets/tabs.
We started the model using historical data and financial statements that were downloaded from Unhedged. NVIDA's financial statements can be downloaded from the financial tab at the company pages, as is the case with all companies. Our roadmap has excel plug-ins to make auto-creating models easier, but it should be noted that once a single model is tied into a financial statement download, it is easy to copy and paste another set of financial statements into the same worksheet and have a model up and running for a different company in a short period of time.
Focus on the most important 3-5 drivers of the business. There are many people that can model down to the penny seven years forward who also can't call a trade because they get lost in the weeds or suffer from paralysis by analysis.
Make your assumptions reasonable and defendable. If that is done, you've done your job.
Building a model from scratch is iterative. The income statement can't be completed until parts of the balance sheet are finished and the balance sheet can't be finished until parts of the cash flow statement are calculated. It can be frustrating even for experienced finance professionals. While the model shown and provided is completed , we try to explain the process of building and how to sequence and tie together.

Revenue & Income Statement Build Up

NVIDIA reports five different revenue line items
1.
Gaming
2.
Professional Virtualization
3.
Datacenter
4.
Automotive
5.
OEM & IP
To build a complex model one would 'build-up' each one of those revenue line items. For example:
Desktop and notebook units multiples by average sales price ("ASP") for gaming revenue
Percentage of aggregate cloud capital expenditures ("Capex") that NVIDIA receives for its datacenter business
Platform units times ASPs for automotive chip revenue
We have simplified the modeling by simply growing revenue at a % of prior year sales for each segment individually to drive total revenue
Next year revenue = this year revenue x (1+ growth rate)
Cost of revenue = revenue x (1-gross margin %)
Gross margin = revenue less cost of revenue
Research and development ("R&D") is calculated as a % of revenue
Sales, general & administrative costs are calculated as a % of revenue
Gross margin less operating costs (or any other operating costs) = operating income
Interest income = cash and marketable securities multiplied by an assumed interest rate received (cannot be calculated until cash changes from cash flow statement are tied together in model)
Interest expense = principal amount of debt and contractual or assumed interest rates (debt balances can be manual inputs or derived based on assumed cash flow sweep for debt repayment and/or debt issuance for cash flow shortfalls)
Operating income plus interest income, less interest expense = income before taxes
Income taxes = income before taxes x assumed tax rate
Net income = income before taxes less income taxes
Diluted earnings per share ("eps") = net income divided by diluted shares
Non-GAAP diluted EPS = GAAP eps + tax-adjusted stock compensation per share
We show Earnings Before Interest, Taxes, Depreciation, and Amortization ("EBITDA") under the income statement but in reality that cannot be calculated without line items from the cash flow statement
EBITDA can be viewed as a loose proxy for cash flow
Our revenue build-up section includes some math on comparing change in gross margin versus the change in operating margin - effectively trying to measure whether the business is scaling in so much as revenue and/or gross margins are improving or growing at faster rate than operating costs
Historical calculations are done to provide a roadmap for future expectations and reasonableness check
Financial models that tie all three statements can cause a #REF! error in Excel at the interest income or interest expense line items
Circuit breakers were built in to solve for this problem, which *zero those calculations out then re-calculate
Iterations must be turned on in Excel > Preferences > Calculation
We have pulled Wall Street Revenue and EPS estimates to show the variance between the model's and Wall Street's estimates - both for a reasonableness check and to allow the user to see upside or downside versus market expectations


Reference Materials













Sign Up to Become a Better Investor