Days Sales Outstanding (DSO), Days Payable Outstanding (DPO), and Inventory Turns are some key metrics for company analysis. While they are just some simple calculations, they tell are story about how a company is doing.
In the balance sheet assumptions section of the model, see below, we calculate each metric and then make assumptions about the forecast values.
Days Sales Outstanding (DSO)
DSO is a measure of how long it takes a company to collect on it’s accounts receivable. The higher the DSO, the slower the collecting – that’s a bad thing. The faster the company can collect, the more options for the company such as investing in more inventory to turn into sales.
The formula for DSO is (Accounts Receivable / Credit Sales ) * 365
In our model, DSO was too high, so we’ve brought it down to more reasonable levels.
Days Payable Outstanding (DPO)
DPO is a measure of how long it takes the company to pay it’s accounts payable. It’s the opposite of DSO – the longer it takes the company to pay, the more opportunity the company can use the money to generate sales.
The formula for DPO is (Accounts Payable / COGS ) * 365
In our model, the DPO historical average was 92 days. However, most creditors only like to give 30 days of credit, so we’ll adjust DPO downwards.
Days Sales Inventory (DSI)
DSI is a measure of how long it takes for a company’s inventory to turn into sales. The shorter the better because the company carries less inventory and hence less cash is tied up.
The formula for DSI is (Inventory / COGS ) * 365
A related metric to DSI is inventory turns. Inventory turns is a measure of how many times you sell your inventory per period. In our model, the inventory turns is 7.7 times per year. In general, the higher the number the better.
The formula for inventory turns is COGS / average inventory
Cash Conversion Cycle
DSO, DPO, and DSI taken together is the cash conversion cycle for a company. DSI measures how long it takes for money invested in inventory to turn into sales, DSO measures how long it takes to bring cash in from the sales, and DPO measures how long it takes to pay for the inventory.
Analysis and Forecasting the Balance Sheet
Analysis of these metrics is straight forward. Calculate and compare to industry comps to make sure they are within reason.
To forecast, calculate historicals and use the average as a starting point. From that starting point, you can adjust based on what you think the company will do. For instance, if a young company has very high DSO historically, you might want to forecast the DSO will come down over time as the company gets a grip on its financials and processes.
We do not forecast inventory turns, because DSI does that for us. It is just a metric to keep an eye on.
The formula for the forecast periods is the reverse of the formula you used to calculate the metric. See the model for examples.
It is important to understand what each metric means and does. Play with the model by changing some of the assumptions and see what each does to the ending cash balance. If you increase DSI, meaning it takes longer to turn inventory in to sales, then the cash balance will decrease.
Prepaid Expenses and Accrued Expenses are two other balance sheet items we need to forecast in our model. They are fairly straight forward in that we just use a percent of relevant line items. In the model, the prepaid and accrued expenses were too high historically, so we just lowered what they would in the future.
** Modeling Tip: Check your balance sheet to make sure it still balances. If you built your cash flow statement properly, then adding in these new assumptions and forecast should not require any work on the cash flow statement and it should still balance.
** Interview Tip: Using historicals to forecast is the easy answer. Assumptions with good rationale behind them are the skills, knowledege, and thoroughness interviewers want.
** All of these metrics can be done on a monthly, quarterly or annual basis depending your what your model periods are.