Modeling Framework

I used to have a superior (in title only; he was a dumbass) that commonly used the phrase, "level-set."  As in, before we go any further, let's do some level-setting with a 30,000 foot overview.  Because, you know, commercial airline pilots always use construction tools.  He really was a dumbass.  Anyway, taking a page out of his dumbass playbook, I'm using this post to do some level-setting of my own with regard to financial modeling and all of the important pieces.  Thereafter, I can use future blog entries to heavily rain down Excel knowledge for your continued benefit.

Most readers have familiarity with the basic layout of a financial model.  For those lacking that familiarity, let me borrow a trick from crappy resume-writing and just tell you the objective: to show a company's income statement, balance sheet and cash flow statement projected into the future, with the ability to change assumptions for various accounts and display their impact on each of the statements.  So let's start with the basic framework of a financial model, which a VP once told me is not much different from a fine-tuned automobile:

1. Steering wheel (controls / assumptions)
2. Axes and tires (formulas / financial statements)
3. Interior furnishing/paint job (formatting)
4. Higher likelihood that Asian females would crash it (ditto)

Does this also mean Asian males would soup up their models with "Toyota Racing" decals and nitrous oxide engines?  Cause that might be 2 fast, 2 furious.

How much do you think your dream car would skimp on any of the above-mentioned items?  If it does at all, then improve your imagination.  You can't have a shitty steering wheel or axes if you want your car to get around and change directions.  You also don't want paint peeling off the side doors.  And if it's Asian-chick-proof, it's not a car, it's a cement block (assuming they don't know martial arts).  By the way, why do car salesmen say "side doors" when talking about sedans? All doors are on the F'ing side.  Front and rear doors?  I don't see anyone climbing into their seats through the hood.

But enough nonsense.  For a model to work mechanically (disregarding how realistic the numbers may be), you need acertain number of inputs (they only need to be numbers to start off), and the rest of the model will all be calculations based on these inputs.  At the most advanced levels, these inputs become models within themselves, driven by many underlying functions and assumptions.  For example, a basic model may show revenue as $100 million for each of the next five years.  However, a slightly more advanced model will show revenue as a function of annual growth percentages.  Further, a complex model may show revenue as a function of several individual customers or accounts, with each based on numerous assumptions for price, volume, market size, etc.

So here is the basic framework, along with which inputs will drive the model.  Again, many future posts will be dedicated to transforming these numbers into more complex, functional formulas.
Income Statement 

1. Revenue - COGS - operating expenses = operating income
2. Operating income + interest income - interest expense = pre-tax income
3. Pre-tax income - taxes = net income

Balance Sheet:

1. Assets = cash + current assets (accounts receivable, inventory, prepaid expenses, etc.) + long-term assets (PP&E, etc.)
• Cash = previous year's balance + net cash flow
• PP&E = previous year's balance + capital expenditures - D&A
2. Liabilities = current liabilities (accounts payable, deferred revenue, etc.) + debt + long-term liabilities
• Debt = previous year's balance + debt issuance - debt repayment
3. Shareholders' equity = 
• Retained earnings = previous year's balance + net income - dividends
• Share capital (is this what you call it? I got fired from an accounting internship) = previous year's balance + stock issuance - stock repurchase
4. Assets = liabilities + shareholder's equity

Cash Flow Statement

1. Cash flow from operations = net income + D&A - increases in working capital
2. Cash flow from investing activities = - capital expenditures
3. Cash flow from financing activities = debt issuance - debt repayment + stock issuance - stock repurchase - dividends
4. Cash flow from operations + cash flow from investing activities + cash flow from financing activities = net cash flow

If you happened to read this far, you're more of a loser than I am for writing this far,

-F-One