Column G.  This step illustrates a typical set of transactions.  The origins of the transactions are still hard codes so that you can spend some more time with the bones / architecture of the model.  If you feel you understand how elements of the model relate to each other, please skip to step 6 where the first real “modeling” begins.

  1. The transactions are (i) sale of $500 of grain (½ cash, ½ trade terms), (ii) further depreciation of the cash register, (iii) purchase of $300 of inventory on trade terms.
  2. Revenue (G7).  A hard code sale of $500.
  3. COGS (G8).  The formula references a fixed margin to result in 25% profit on revenue.
  4. SG&A (G11).  The formula references a fixed portion of revenue to calculate SG&A costs.
  5. Depreciation (G12).  The formula references a fixed life (and assumes that this period is one month long).  Many of you have already noted that if you ran this formula out beyond 5 years you would “over-depreciate” the cash register.  This issue is dealt with in the Advanced Modeling section.
  6. Taxes (G21).  A fixed percentage of income.
  7. A/R (G28).  A hard code to reflect the fact that half of the $500 of grain revenue was paid for with paper rather than money (remember asset up, cash down on the cash flow statement).
  8. Inventory (G29).  A hard code of a decrease by COGS for the grain sold and an increase of $300 for the grain purchased.  In the next step, we will use a simple formula to reflect these net movements.
  9. PP&E (G34).  The accumulated depreciation has increased by last period plus this period.  The net asset is going down.  At the end of 5 years, the net asset would be worth zero.

10.  A/P (G38).  We gave the grain supplier a paper promise to pay rather than cash, so A/P has increased.  (remember liability up, cash up on the cash flow statement.  This will be an offset to the inventory purchase (asset up, cash down) effect in this cashless transaction.

11.  Retained Earnings (G47).  This continues to accumulate the change in net income.

12.  Cash Flow Statement – Net Income and Depreciation (G56 & G57).  These are pulled from their sources on the income statement.

13.  A/R (G59).  Last period assets minus this period’s assets.  When the asset goes up, cash goes down (think purchase of assets).  What happened here is that net income contained accounting revenue of $250 for a sale where Bob got an IOU rather than cash.

14.  Inventory (G60).  Again, last minus current for the inventory asset.  Underlying is a sale of inventory of $375 and a purchase of $300.  Note that the model uses A/R and A/P to track how things are paid for, not the assets themselves.  As far as this line is concerned, you paid $300 cash.  The A/P line will adjust for the fact that you issued an IOU.

15.  A/P (G61).  Current period minus prior period.  The increase in liability increases the cash here.  You gave the grain supplier an IOU rather than cash, so the cash use buried in inventory is offset here by a cash “gain”.

Column G.  This step illustrates a typical set of transactions.  The origins of the transactions are still hard codes so that you can spend some more time with the bones / architecture of the model.  If you feel you understand how elements of the model relate to each other, please skip to step 6 where the first real “modeling” begins.

  1. The transactions are (i) sale of $500 of grain (½ cash, ½ trade terms), (ii) further depreciation of the cash register, (iii) purchase of $300 of inventory on trade terms.
  2. Revenue (G7).  A hard code sale of $500.
  3. COGS (G8).  The formula references a fixed margin to result in 25% profit on revenue.
  4. SG&A (G11).  The formula references a fixed portion of revenue to calculate SG&A costs.
  5. Depreciation (G12).  The formula references a fixed life (and assumes that this period is one month long).  Many of you have already noted that if you ran this formula out beyond 5 years you would “over-depreciate” the cash register.  This issue is dealt with in the Advanced Modeling section.
  6. Taxes (G21).  A fixed percentage of income.
  7. A/R (G28).  A hard code to reflect the fact that half of the $500 of grain revenue was paid for with paper rather than money (remember asset up, cash down on the cash flow statement).
  8. Inventory (G29).  A hard code of a decrease by COGS for the grain sold and an increase of $300 for the grain purchased.  In the next step, we will use a simple formula to reflect these net movements.
  9. PP&E (G34).  The accumulated depreciation has increased by last period plus this period.  The net asset is going down.  At the end of 5 years, the net asset would be worth zero.

10.  A/P (G38).  We gave the grain supplier a paper promise to pay rather than cash, so A/P has increased.  (remember liability up, cash up on the cash flow statement.  This will be an offset to the inventory purchase (asset up, cash down) effect in this cashless transaction.

11.  Retained Earnings (G47).  This continues to accumulate the change in net income.

12.  Cash Flow Statement – Net Income and Depreciation (G56 & G57).  These are pulled from their sources on the income statement.

13.  A/R (G59).  Last period assets minus this period’s assets.  When the asset goes up, cash goes down (think purchase of assets).  What happened here is that net income contained accounting revenue of $250 for a sale where Bob got an IOU rather than cash.

14.  Inventory (G60).  Again, last minus current for the inventory asset.  Underlying is a sale of inventory of $375 and a purchase of $300.  Note that the model uses A/R and A/P to track how things are paid for, not the assets themselves.  As far as this line is concerned, you paid $300 cash.  The A/P line will adjust for the fact that you issued an IOU.

15.  A/P (G61).  Current period minus prior period.  The increase in liability increases the cash here.  You gave the grain supplier an IOU rather than cash, so the cash use buried in inventory is offset here by a cash “gain”.