Running the model.  Running, operating, pressure-testing, etc. the model means manipulating the inputs while observing the outputs to draw business conclusions from the results.  Bob is in a bad spot.  His business is bleeding cash during the forecast period.  He (mainly because of modeling convention) liquidated a lot of inventory resulting in a pile of cash that is preventing bankruptcy, but things do not look good for the future.  In fact, if this model were made when he was trying to obtain a bank loan, his loan officer would not like these projections at all.  What actions might Bob take to improve his cash situation?  (note:  Since we will be manipulating the model, you may want to copy the model tab (right click on the tab, click on Move or copy, then check the Create a copy box on the bottom left) prior to changing the inputs. 

The results of these manipulations are on the “Main (2)” tab.  With proper architecture described in the advanced section, inputs in one place or in multiple cases can easily be copied over.)

  1. Get paid more quickly.  Bob can start taking Visa (ignoring their charges) and get paid in 5 days rather than 20.  Change A/R (Cell C92) to 5 days.  Note the cash outflow for A/R is reduced.
  2. Pay his vendors more slowly.  Most vendors do not mind being paid in 35 days on 35 day terms.  Change A/P (Cell C93) to 35 days.  Note the increase in cash flow on the cash flow statement from A/P.
  3. Reduce inventory.  An analysis of the cash flow statement will show that a lot of cash is going out of the door to maintain a huge inventory.  Bob can read some books about inventory control and prediction and reduce inventory to 1 month supply (assuming this is feasible for his business).  Change Inventory to 30 days (Cell C94).  This makes a much bigger difference in the ongoing cash flows than A/R or A/P, but all are worth taking action on.  In all cases, note the cash liberated in the first modeled time period, period 6.  This is real cash.  If Bob bought a bunch of inventory in the beginning, sold most of it, and did not replace it, he will have gotten his money back.
  4. Bob’s model is helping him become a better manager (or at least given him metrics to shoot for), but he is still losing cash.  Focusing on the cash flow statement, you will notice that the bank payments are large compared to operating cash flow in the projection period.  Bob can “fix” this by (i) borrowing less, (ii) paying over a longer term, or (iii) making more money.  Since (iii) is usually the most difficult, and models are often used for capital allocation and design purposes, Bob will use this information to both borrow less and pay back longer.  Change cell C70 (Bob’s initial loan draw) to $3,000 and his equity investment in cell C71 to $7,500, so that Bob is putting in the same $10,500 of capital.  Now, the model has adjusted to show a light at the end of the tunnel.  Further, Bob convinces the bank that his good credit deserves a 7 year term loan rather than 5.  Change cell C96 to 7 years.  Look at Bob now.  He is only losing money in the second modeled time period.

Bob knows now that the biggest bang for the buck is to figure out how to live with less inventory.  The model tells him how valuable this is from liquidating the inventory initially and avoiding asset growth in the future.  Bob can also see that his ~50% debt capital structure might have been too heavy given his expected sales volumes and margins.  Further, he can decide if the gains from pulling in A/R and stretching A/P are outweighed by any potential costs and any damage to his reputation in the valley. 

Should the model operator look at low and high revenue and margin cases, Bob will understand even more about how his business can perform in good times and bad, and when he might get into trouble from a cash flow or bank perspective.

These analyses and insights, available in even a simple financial model, are the primary reason for building and running a model.  As you can see, only Bob can determine the feasibility of reducing inventory or taking other business actions, but the model can help identify these actions and quantify their results before time, money, and effort are spent to undertake them.  Adding complexity to a model can help with planning as well.  Should Bob lease or own his store?  Could he afford to open a branch location?

This free model download for Excel is simple, but it is a good platform to add features and make a fully functional model customized to your situation.