We join Bob’s General Store at the start of a new financial period with no history.  Bob has already bought $10,000 of grain and a $500 cash register with $5,500 cash that he invested into the store and $5,000 he borrowed from the bank.  We will ignore his rent, utilities, insurance, and other miscellaneous expenses to keep this simple.

  1. Things already look complicated and Bob has just turned on the lights!  What kind of structure do we have so far?  Let’s ignore the income statement until we sell something.  Starting with the balance sheet and moving down… (these descriptions are long, but just follow along with the formulas in the cells and read through it quickly.  As we add transactions, we will revisit the formulas and the logic as you think back to what you read here first).
  2. Cash is set equal to the ending cash on the cash flow statement in cell C27.  Always take your cash on the balance sheet as the answer of the cash activities during the time period.  Here, we have assumed that the capital raising transactions (Bob’s money and the bank’s money) took place during this period.  We will get to the cash calculations later.
  3. Current Assets.  Currently, A/R and Inventory (C28 & C29) are hard codes - we will make them formulas soon.  Bob’s $10,000 of grain is in inventory.
  4. PP&E.  PP&E (gross) is set to the prior period value minus the capital expenditure amount (because it is expressed as a negative on the cash flow statement) in cell C32.  Here, the prior period is a blank opening amount, but setting up the formula now allows you to fill across later.  In order for your model to work properly and your balance sheet to balance (as will be further explained later) PP&E (gross) should increase only with capital expenditures (which is how you buy more PP&E).  Depreciation should come from a single source in the model and then be carried to the balance sheet and the cash flow statement.  There is no depreciation yet, because Bob just bought the cash register.  The accumulated depreciation formula in C33 adds this period’s depreciation to last period’s depreciation because the depreciation on the balance sheet accumulates (I have used a positive sign convention).  
    For example, if the cash register has a 5 year life, it will depreciate $100 per year.  At the end of 5 years, it will have no value on the balance sheet ($500 gross PP&E less $500 accumulated depreciation).  The formula allows this to happen by adding up all of the depreciation.  The Net PP&E is just the difference of the accumulated depreciation and the gross PP&E (if using a negative sign convention for accumulated depreciation, then add).
  5. Total Assets (C36) is the sum of the sub totals from long-term assets (here only PP&E) and current assets.  It is good practice to use subtotals so that all of the numbers on the sheet are “good” and references in complicated sheets are easier to follow and error check.
  6. Current Liabilities.  They are empty - we will address them later.
  7. Debt (C42) takes last period’s amount and adds/subtracts the borrowings or additions on the cash flow statement.  It works best to have the “action” take place where the action really happens.  As with PP&E, the balance sheet is the end result of actions that Bob took during the period.  The cash flow statement “happenings” create balance sheet results.  Therefore, make your borrowings and repayments happen on the cash flow statement and your balance sheet reflect these.
  8. Like total assets, total liabilities (C44) takes its values from the subtotals.
  9. Shareholder’s equity (C46) is simplified here as the value of stock sold.  Like PP&E and Debt, it is the result of an action that took place on the cash flow statement, and would change with any future action.

10.  Retained earnings (C47).  This is very important.  Retained earnings are just that, the accumulation of the earnings (net income) of the company.  Do not use retained earnings to balance your balance sheet.  With a correct model it will automatically balance because, again, the balance sheet is the result of what has happened during the period.  If your balance sheet does not balance then something is wrong with your model.

11.  On to the cash flow statement…  A note on sign convention:  the cash flow statement works best from a debugging and presentation standpoint if all items are reflected according to + for increases and – for decreases in cash.  If you stray from this and have errors, they are much more difficult to find.

12.  Net Income (C56).  Cash starts with earnings.  Your net income should be the main source of your operating cash flows.  This net income is simply equal to the income on the income statement.  This simple model ignores many complexities that are addressed in another section of the site.

13.  Depreciation (C57).  Net income reflects GAAP (again discussed elsewhere) and contains non-cash items.  Depreciation in this case is not the physical phenomenon but a financial fiction.  Your capital assets are worth less on your balance sheet because of the depreciation expense in income, but no one comes and takes the money from you.  So, you add depreciation back to net income.  Here, it is linked to the depreciation in the income statement.  Take note of how this formula works with PP&E and net income on the balance sheet.  Liabilities & Equity are “going up” by Net Income (retained earnings), but we have just “added” (really corrected) Depreciation to cash, causing the cash asset on the balance sheet to “go up” by Net Income + Depreciation.  Help, the asset ledger is up by NI+Dep, and the liability ledger up by NI!  But remember, net PP&E on the asset side is shrinking by Dep, so it will balance.  The same Net Income and Depreciation in all cases because we have linked them all back to the same, single source.

14.  Changes in Working Capital (row 59-62).  What is going on in changes in working capital?  All along we have kept things “where the action is” and here things are backward – the cash flow statement refers to the balance sheet!  Why?  Modeling convention.  The actual “transactions” here are happening at the cash level (as we will see later), but people have found that businesses are best reflected (and much easier to model) by modeling the amounts / quantities of working capital assets and liabilities than trying to reflect all of the transactions that take place relative to the booked revenue and expense. 

15.  The easiest way to think of the formulas here is to remember that you have to buy assets and that liabilities let you defer cash expenses.  For example, Bob bought $10,000 of grain that he paid for with cash.  Looking at the balance sheet we went from no inventory assets to $10,000 of inventory assets.  These assets appeared because someone bought them, so the modeled formula is prior period assets (none) minus current period assets.  Liabilities are the opposite.  Think if bob had handed over the company’s visa card instead of cash for the grain.  Then, current liabilities (the outstanding balance that Visa will want paid soon) would reflect accounts payable (or short term debt) of $10,000.  Down here in the cash flow statement, our handy formula would have produced +10,000 (=current period liability – prior period liability).  That is exactly what happened.  Your accountant / model booked $10,000 of inventory and $10,000 of liability.  Your cash flow formulas would show -10,000 for inventory increase, and +10,000 for liability increase, net zero.  Net zero because you posses the grain and an IOU to Visa, but have moved no money.  However, in our example spreadsheet he paid cash, so operating cash flows total out at -10,000.

16.  Investing Cash Flow (C66).  Here we currently have a hard – code for the cash register purchase.  It is negative, because the cash went out the door.

17.  Financing Cash Flow (C70 & C71).  Again, currently hard codes representing money raised.

18.  Net Cash Flow (C74).  In some respects, this is the answer from your model.  Note again the use of subtotals to make debugging and use easier.

19.  Cash BOP / EOP (C76 & C77).  This can be done net, but debugging is easier and understanding higher if prior period cash is shown and then cash flow is added for end of period cash.  Cash beginning of period is the cash on your prior period balance sheet.  Cash at the end of the period is what you post up to this period’s balance sheet.  Note again how all of these statements tie together.  Some primary inputs & drivers make the model work.  The statements “talk” to each other automatically resulting in a correct, “footing” model.  When an error occurs, it almost always unbalances the balance sheet, giving you a quick way to determine when something has gone wrong.