Developing a Basic Financial Model - Part IV: Working Capital Historical Relationships

In continuing in our series of fundamental concepts of financial modeling, I will now turn to the initial steps of understanding how to forecast financial information. It is important that the reader has some familiarity of the three major financial statements (income statement, balance sheet and cash flow statement) that I covered in the prior three articles. If not, please read those first prior to continuing.

Working Capital Historical Relationships

We often see in disclaimer language that "past performance is not indicative of future results" or other types of language similar to that to let the reader know not to put too much stock in the historical outcomes of a particular situation. This is commonly seen in relation to stock price performance or asset manager historical returns. In the case of forecasting financial information of specific companies, this is not necessarily true. In fact, in investment banking or private equity, historical relationships help to drive the expectations of future performance.

For example, let us assume that there is a company in a relatively mature stage of growth, meaning that the future growth is likely to be more along the lines of a bit above the inflation rate for the next several years. The last two years have showed that the days outstanding for accounts receivable ("DSO") was 32 (the days from the point of booking a sales turns to cash). In the absence of specific information related to the company, whether a macro event or industry overhaul, there is likely no reason to assume that there would be a significant change from that.

DSO is calculated by taking the average accounts receivable over a time period, like annual, and dividing the result by the total daily sales value for the most recent period. In other word, if annual, the denominator would be the sales figure divided by 365 and the numerator would be the average of the accounts receivable calculated for the current year and the prior year. If the total accounts receivables for the past two years was $50 and $65, and the total sales for the most recent period was $1,050, the DSO would be approximately 20, or on average, accounts receivable is outstanding 20 days before coming to the company as cash.

The same concept would apply to days payables outstanding ("DPO") or days inventory outstanding ("DIO" – although, a more common concept is inventory turns). DPO is calculated similarly to DSO, except the accounts payable becomes the numerator and the denominator is cost of sales divided by 365. You can easily see the similarities between the DSO and DPO because one is a method to track when cash comes in (DSO) and the other tracks when you have to pay (DPO). These are two components of the cash cycle (also known as the cash conversion cycle).

The cash cycle is a way companies can track how quickly cash comes into the company, and when an analyst is forecasting results for a company, the historical information is very important to understand. In simple terms, the cash cycle follows the purchase of raw materials (often building up payables) to creating a saleable product to the collection of receivables. The cash cycle is = days inventory + days receivable – days payable or DIO + DSO – DPO. The DIO is calculated similarly to DPO in using the cost of sales as the denominator with the average inventory as the numerator (the inventory turns is calculated by taking the total costs of sales as the numerator and the average inventory as the denominator and the result yields how often a company is "turning" its inventory, or selling its products).

To continue with our prior example of DSO = 20 days, if we assume that average inventories was $28, average accounts payable was $37 and the cost of sales in the most recent period was $750, then DIO = 14 and DPO = 18. This translates to a cash conversion cycle of 16 days (20 + 14 – 18). This means that a company is getting cash every 16 days from the current operations. What this really tells you is that the time period of 16 days is what a company must finance prior to getting cash. As a slight tangent, there are instances, although rare, where companies have been able to "self-finance," or not rely on a credit facility or other source of funding to finance its working capital. If you change the numbers above and have DSO or 11, DIO of 10 and DPO of 22, you can see that the cash cycle is approximately 0 (rounded to -1). This is an instance where an entity is developing inventory to sell faster and receiving cash from accounts receivable faster than that company is paying to its vendors. That is a great position, but it is not a common situation.

Other current assets include prepaid assets, which many financial analysts will see as a percentage of total sales. Other current liabilities include accrued expenses or other accrued liabilities, which financial analysts may view as a percentage of total cost of sales. For example, if prepaid expenses at the end of the year have been on average 3% of total sales, in the absence of specific information, any deviation from that going forward is probably not warranted. The similar logic holds for current liabilities.

I want to make two quick points before ending this section. The first is that for companies who are new and rapidly growing, it is acceptable to look at all working capital relationships as a percentage of total sales. In many instances, if you attempt to forecast receivables, inventory or payables using historical information for a company only in business for one or two years, you will get misleading data. To better understand this, think of a new company who will be building up inventory ahead of a marketing launch and who may be offering better receivables terms to purchasers in order to generate sales. Additionally, a new company is likely to have more stringent terms on payables so you will be double penalized from a cash cycle perspective. As a company goes from upstart to established entities, the various ratios will reach equilibrium and then using the DIO, DPO or DSO analyses is more prudent.

The second point is that some companies will include short-term borrowings or current portion of short-term debt in the current liabilities section. For working capital analyses, these should not be included. The only counter-argument to this is that is there is a specific working capital line that is associated with financing working capital (not just based on the balances but actually used to finance working capital) some would choose to include that in certain working capital calculations. In my experience in financial forecasting, I have separated that component of debt and I do not view it as a current liability but as part of a company's capital structure (i.e., long-term financing and not a current liability like payables or accrued expenses). But, as long as the methodology is clearly defined, the financial modeler can choose either path, as the results should not differ.

The next article will cover long-term assets and long-term liabilities and how a financial modeler can use some historical perspective to forecasting these items.