Views
Notes
Folders
Reading List
Read
Financial Modeling 101: Part III - Financing Activities Capital Structure & Valuation
An article by • Published Feb 12, 2021

Follow Video Tutorial Along With Note



Excel File Shown In Video Is Available
Here



In Part I - Revenue & Income Statement Build-Up we discussed creating drivers for future revenue of NVIDIA and how to calculate operating and non-operating expenses as a function of revenue or driven by other factors.
In Part II - Balance Sheet, Working Capital & Cash Flow Items we used forecasted revenue and cost of revenue along with balance sheet items to forecast working capital items and used PP&E and Capex to forecast D&A and ending PP&E.

Financing Activities, Capital Structure & Valuation

Let's tie the entire model together and put a bow on it. Debt and equity financing activities can be considered and modeled out. Stock compensation as a percentage of revenue and other forms of equity issuance or buybacks are considered. Dividend payments, interest rates, debt and equity balances are all derived from activities in these sections of the financial statements. Once all three parts of the cash flow statement are complete (operating, investing, financing), ending cash can be calculated and tied into the balance sheet, creating the balancing loop.
We also do a quick capitalization table, show an enterprise value calculation and provide a couple different ways to look at $NVDA valuation. As a reminder the numbers show are illustrative only and not meant to be a recommendation on buying or selling NVIDIA stock.
Principal debt balances are pulled to reconcile actual debt versus what is shown on the balance sheet (if any material difference)
Reported balance sheet debt often differs from principal amount outstanding due to accounting rules that require valuing into debt and equity components when the issuer has converts and/or when the issuer has discounts or premiums and capital markets fees to amortize over the life of the bond or credit facility.
Cash interest can be calculated using contractual interest rates
Income statement interest is shown for illustrative purposes to highlight the difference between actual cash interest and income statement interest; in addition to the potential sources of differences above, interest can also be capitalized when debt is used for growth projects - or in the case of E&P's interest associated with PUDs (proved undeveloped reserves) or unproven leasehold can be capitalized rather than expensed
Stock compensation = % revenue; this is a non-cash add back in the operating cash flow section, costs that are expensed in COGS, R&D and SG&A but actually paid in stock
Reasonable assumptions are made on the drivers of the amount employee stock option that are exercised and the amount of payments related to tax on restricted share units, both of which are line items in the financing section of the cash flow statement
Dividend payments - if any - can be calculated using outstanding shares and dividend per share; the aggregate amount of dividends paid can then be linked to the cash flow statement
The net impact of stock issuance is considered with manual inputs for equity issuance or repurchase
The preferred stock line item is not tied into the cash flow statement given NVIDIA didn't have that line item, but incorporating would simply entail offsetting line items in the cash flow statement and balance sheet
The model is illustrative not exact. The stock issuance table ties only stock issued that results in a cash flow impact. In the event of an M&A transaction that was done 50/50 cash and stock, the impact would result in the cash portion hitting the cash from investing line items, but the stock portion only increasing equity on the balance sheet, with offsetting net asset additions
Debt repayment or issuance is considered. Users should sensitize the manual inputs shown to follow the line items through the financial statements to understand how they impact debt balances.
It would be much more simple to have manual entries for debt issuance or repayment each year
Instead, we have shown a more complex scenario whereby excess cash is allowed to sweep short-term or long-term debt - more of a leveraged balance sheet consideration, which is not the case at NVIDIA
Once the cash flow from financing statement is complete, we then tie the ending cash balance to the balance sheet and the model SHOULD balance
Ending cash = beginning cash + change in cash from the cash flow statement
Conditional formatting is utilized to quickly tell whether the model is balanced
Capital structure & valuation
Capital structure / capitalization table is built using cash, marketable securities, debt, share count and market price per share
Market capitalization = share count x price per share
Enterprise value = market capitalization + net debt
Forward EBITDA multiples are shown
Simple discounted cash flow analysis is done
Leverage metrics are shown (in a leveraged scenario would build in covenants and conditional formatting to highlight headroom or breaches)


Reference Materials


Liquidity, stock buyback and debt discussion






Long-term debt





Equity table (can be used to help reconcile equity balance on balance sheet)












Hopefully this was a worthwhile exercise. The goal was to bang the videos out and provide an Accounting and Finance 101 tutorial through building a model in a half hour or less. The videos came in at 45 minutes instead, but the materials covered are significant and likely overwhelming for many. It is highly suggested to download the excel file linked in this note. Re-create your own model from scratch following similar steps, including building out the necessary tables to see if you can get the three statements to balance. Even if you never build another model again after doing it once, the lessons learned on how parts of the income statement, balance sheet and cash flow statement impact each other will likely prove to be valuable professionally and/or personally going forward.


Sign Up to Become a Better Investor