Column H.  Now that you are fully conversant with basic architecture, we will add some simple modeling techniques to make a “real” financial model.  The changes:  (i) revenue will be driven off of a growth rate, (ii) A/R will depend on sales, (iii) Inventory will depend on sales, (iv) A/P will depend on cost of goods sold, (v) we will pay the bank some money back. 

The key drivers of most simple models are revenue, margin on revenue, overhead costs, and working capital items.  These are also the key drivers of most businesses and projects.  Sell something for more than it costs, cover overhead, and make sure you are getting paid in cash for what you do at some point in the future.

Some advice:  Please avoid modeling profits and solving for revenue.  Sales create profits, not the other way around.  Also, do not ignore working capital.  It may seem a lot of additional work in modeling, but many have gone bankrupt when they did not understand working capital effects when growing (or shrinking).  You will be surprised how critical it is to returns and cash flow management in many situations.  In the Advanced Modeling section we will look at more sophisticated revenue and costing models along with some pitfalls in working capital modeling. 

  1. Revenue (H7).  The revenue formula is simple, feeding off of last period’s value.  Keep track of your time periods.  If these were months, 10% per month would be quite high.  Here, 10% provides a large enough example to see.
  2. GOGS (H8).  Filled over as a reference to fixed margin.
  3. SG&A (H11).  Filled over as a portion of revenue
  4. Depreciation (H12).  Filled over as a fixed time period of gross PP&E
  5. Tax (H21).  Filled over as a percentage of income.
  6. Balance Sheet.  A/R (H28).  It is a common convention to think of A/R as days of sales outstanding (DSO).  You can think of this as how long it takes customers to pay.  This is highly dependent on industry, custom, and the customers themselves, but it typically runs between 30 and 90 days.  Think about a fresh company that sells on 30 day terms.  They would ship goods for a whole month before they collected any cash.  On the 31st day, they would collect only from the first customer.  Assuming level sales, they would constantly have 30 days of sales unpaid in the form of A/R.  This input convention is also easy to discuss with operating management and analysts.  This formula converts revenue into a day of sales (assuming that this time period is a month) and then multiplies by the input days sales outstanding.  If you manipulate the input while watching your cash flow statement, you can see the effect that slow paying customers can have on cash.
  7. Inventory (H29).  Similar to A/R, inventory is related to sales in many organizations.  Since companies sell out of inventory, they tend to manage the level of inventory based on sales levels (notwithstanding purposeful action to increase efficiency (such as JIT and six sigma) or to free up cash in one-time events).  Here, the formula is the same formula as A/R with a different input.  It is linked to cost, which is linked to sales.  Inventory in Bob’s business is his cost.  If cost were not linked to sales, or had fixed components, it may make sense to link inventory levels directly to sales.
  8. PP&E (H34).  No changes here as accumulated depreciation continues to accrue.
  9. A/P (H38).  Just as A/R moves with sales, A/P tends to move with purchases.  The level and relationship will be different with each type of business.  Obviously, a consulting firm whose expenses are payroll will have much different A/P than a wholesaler whose expenses are mostly goods purchased from other companies.  Here, the formula links COGS to A/P through days of cost outstanding.  This is similar to days sales outstanding, but represents how fast you pay your costs / vendors.
  10. Debt (H42).  The debt formula looks to the cash flow statement to increase or decrease debt based on payments to the bank.
  11. Cash Flow.  Inventory (H60).  Look at the dramatic change in cash that occurred when inventory went from hard coded to modeled.  This pitfall is common when “rolling” off of historical / actual numbers.  To develop metrics like DSO historical averages are often used.  Beware plugging in a historical average in the next period of operations to avoid discontinuities like this.  There is not a top of mind example to explain how this might happen in reality without corresponding sales or other entries on the income statement.  The discrepancy is left in the model to illustrate the problem.  Going forward to the future time periods there will not be an issue since each period will have a similar calculation.  It will be as if that is the way Bob runs the store, with 180 days of inventory. 
    1. A further note:  Linking inventory to sales also works better when the inventory ratio is a little more rational (Bob is very conservative), say 60 days of cost.  In these cases, when the model time period is significantly larger than the inventory time period, the assumption works well.  If a company keeps 60 days of inventory on hand and sales decline or increase during a year, it is easy for them to draw down or stock up as they go so as to end the time period with an appropriate amount of inventory.  In this model, a month of zero sales (resulting in zero COGS, making the inventory balance sheet formula spit out zero inventory, thus resulting in positive cash in the cash flow statement formula) would not really allow Bob to somehow liquidate his inventory for cash.  Be aware when using short time periods in models.
  12. (Payments To) / Borrowings From Bank (H70).  This is a simple formula that divides the original debt into several payments based on an amortization input.  Obviously, if the model runs longer than the amortization period the debt will be over-paid.  This will show up on the balance sheet as negative debt given the current formulas.  In the advanced examples, we will work on formula structures to trap repayments at the maximum debt level.