Sale to Bubba, Column D.  Now that you are familiar with some of the basic mechanics of a simple financial model, let’s allow Bob to sell some grain.  Bob sells $500 of grain on trade terms to Bubba (that is a fancy way of saying that Bubba tells Bob: “I’ll catch you next month”).  We will reflect a few simple transactions with hard codes to increase familiarity with the model, and then we will move on to “modeling” transactions.  When building a new model, first fill across your first column, then make changes.

  1. Revenue (D7).  When Bubba walked out with the grain, he signed an IOU.  The accountants say book it.  $500 is typed into revenue
  2. Cost of Goods Sold (D8).  Often in a financial model, the gross margin is either known or within a specified range.  It is common to use either a single input (as in this case) or a different margin for each year to drive the cost of goods.  Here, a single input cell is referenced to produce a cost of goods that will derive a gross margin.  It is more appropriate, even when solving for a margin, to manipulate the cost of goods (rather than gross profit), because this is what changes in the real world.
  3. SG&A (D11).  Here, a common convention of linking SG&A to revenue is used.  This method has many pitfalls, but since our purpose is more mechanical, suffice it to say that some method of driving SG&A needs to be used to reflect the business reality of the situation.  Bob’s overheads (utilities, his pay, rent, etc) don’t really vary with revenue – they are more likely fixed, but this convention works well in large companies and here shows how a model can automatically adjust costs based on volume.
  4. Taxes (D20).  Again, a simplified view of tax where GAAP tax equals cash tax (note there are no balance sheet or cash flow items related to tax).  Here a single rate is often most appropriate because jurisdictions are typically fixed.  In multi-jurisdictional models where income mix changes over time, income from each region must be separated to produce accurate tax results.  Similarly, if GAAP tax does not equal cash tax (which it rarely does) complexity must be added.  See the Advance Modeling section of this site for more information.
  5. Cash (D27).  Cash is a result from the cash flow statement.
  6. A/R (D28).  For now, this is a hard code representing the IOU from Bubba.
  7. Inventory (D29).  For now, this is a hard code representing the cost of the grain sold to Bubba.
  8. PP&E (D32-D33) are carryover formulas.
  9. Other accounts (D38-D39).  No activity.
  10. Retained Earnings (D47).  Again, net income in the period increases retained earnings from the prior period.
  11. Cash Flow Statement.  No new additions, but let’s walk through the formula results:
  12. A/R (D59).  Note that the income statement contains income from the sale to Bubba.  This is proper from a GAAP standpoint, as the sale was made.  However, from a cash standpoint there is non-cash revenue because Bubba gave us an IOU rather than cash.  Therefore, the A/R formula (which sees an increase in an asset, which is “purchased”) correctly reduces cash by the amount of the paper IOU.
  13. Inventory (D60).  Bob has sold grain from inventory.  From a GAAP standpoint, there is expense in the inventory (to correctly reflect the business situation) reflected on the income statement.  From a cash standpoint, Bob already paid for the inventory yesterday.  So, when he sells inventory (reduction in an asset) he has “generated” cash – by the same amount net income was reduced via COGS per GAAP. 
  14. Net Cash Flow (D74).  Does this make sense?  Bob lost $93 of cash on the sale when inventory was paid for and no money came in the door.  In a simple model like this, it is easy to look through the income statement to reconcile this result.  The model assumes that the SG&A costs are paid out currently in cash and that the tax expense is also a current, cash expense (the more sophisticated model contains a more complicated treatment for taxes).  The sum of SG&A and Tax (the only actual cash actions this period) does indeed add to $93.