Free Financial Model Tutorial

• What is a Model?
• Excel Tips
• Accounting Overview
• Conventions and Layout
• Basic Modeling
  • Column C
  • Column D
  • Column E
  • Column F
  • Column G
  • Column H
  • Column I
  • Column J
  • Operating

Column F.  The accountants weigh in on Bob’s cash register.  Depreciation and its effects are important to understand in a model since nearly all businesses have depreciation and it is often a source of errors.  It shows up on all three statements in the model.  Depreciation is an expense on the income statement (but is not “real” in that it is non-cash).  Depreciation also lowers the book value of assets.  Because depreciation is non-cash, the cash flow statement (which begins with net income containing depreciation) must adjust for this fact by adding depreciation back.

1. Income Statement – Depreciation (F12).  Here the formula uses one input for asset life, and assumes that the periods of the model are months.  Note:  the life chosen and periods are only for simplicity.  Ask your accountant the appropriate life of your assets.  Also, depreciation is more typical inside of COGS as an allocated cost of a manufactured item.  It often is in both SG&A and COGS.  The location here is only illustrative purposes.
2. Income Statement -  Tax (F21).  Note that depreciation is tax deductible.  The government will generally pay you cash for your depreciation (subject to many things including but not limited to current period income, the ability to carry back, when you file your return, etc.).  When depreciation is referred to as non-cash, remember that itis cash with respect to taxes.  Note:  the Advanced Modeling tutorial addresses differences between tax depreciation and accounting depreciation.
3. Balance Sheet – PP&E (F33).  The accumulated depreciation formula has picked up the depreciation and subtracted it from gross property plant and equipment.
4. Balance Sheet – Total Assets / Liabilities & Equity (F36 & F50).  Note that the balance sheet has balanced.  Assets (PP&E) dropped by 8 while cash went up by 3 (the cash tax shelter from the depreciation).  On the other side of the ledger, a net loss reduced retained earnings by 5.
5. Cash Flow Statement – Net Income and Depreciation (F56 & F57).  The Net loss of 5 contains a non-cash depreciation expense of 8 and a cash tax benefit of 3.  The cash flow statement simply adjusts net income by adding back the non-cash portion of net income on the depreciation line, thus correctly resulting in the actual cash flow of $3.