Column J.  Filling Across the model.  Once a model and its formulas are set up, one can generally select all of the information in the column and drag or fill across.  Review the results of the model to see if the answers make sense and if mechanics / formulas appear to work correctly.

  1. Revenue.  Going up with growth.
  2. SG&A.  Increasing with revenues.
  3. Depreciation.  Flat, as it is associated with one asset that is straight-line over a fixed time period.
  4. Interest Expense.  Going down, as it should when debt is being paid off.
  5. Balance Sheet.  Cash.  Going down – which is not typical in a business, but a review of the cash flow statement and the fact that debt repayments are in excess of net income means that this is logical.
  6. A/R & Inventory.  Both increasing with revenues, as they are set to a revenue ratio.
  7. PP&E.  Gross PP&E is not increasing, because there are no further capital expenditures in this model.  Net PP&E is declining because the underlying asset is “wasting”.
  8. Accounts Payable.  Increasing along with revenue.
  9. Debt.  Decreasing as it is paid off.
  10. Retained Earnings.  Growing with income.
  11. Total Liabilities and Equity.  Equal to Total Assets, the balance sheet is balancing.  Look in row 51.  It is helpful to include a formula that will trip off when the balance sheet is out of balance.  This one returns the word “ERR” along with the difference between the total assets and total liabilities and SHE (The “&” separated by spaces joins multiple expressions in Excel).  When the balance sheet is in balance, it returns an empty text character.  Often, in circular models, there can be minute errors, so a quantity of error is used rather than testing for exact equality.
  12. Cash Flow Statement.  Changes in Working Capital.  This looks generally how a steadily growing, simply modeled business will look.  As the company grows, the assets grow in proportion.  It takes money to buy assets, and the negative cash flow associated with these “purchases” is visible in A/R and Inventory.  Liabilities also grow proportionately, and they generate cash as they grow.