At a higher level, we analyze each element of working capital using the same methods. The overall objective is to know the average number of days that it takes for an item in stock to be sold, a customer to pay, or a supplier to be paid.
We can help free up cash for the business if we reduce the number of days that an item is in a warehouse or the number of days that a customer takes to pay an invoice. Inversely, we want to increase the number of days that we can wait to pay our suppliers without any penalty. Let's start our working capital analysis by calculating the average number of days that an item is in a warehouse. We call this key performance indicator Days Sales of Inventory (DSI).
If we store inventory for too long, then it takes up space that could be put to better use or sold. If we store inventory for too few days, then we increase the risk of not being able to satisfy customers' needs. Days Sales of Inventory (DSI) tells us the average number of days that we store items in inventory based on our average inventory balance and our cost of sales. The following formula calculates DSI over a one-year period:
Days Sales of Inventory = (Annual Average Inventory Balance / Annual Cost of Sales) * 365
Let's create a bar chart that displays total DSI by month. We calculate each month's DSI over a rolling one-year period:
Before beginning the following exercise, we import this chapter's exercise files into the QDF as we did in Chapter 2, Sales Perspective.
Let's create a bar chart with the following property options:
|
Dimensions | |
|---|---|
|
Label |
Value |
|
AsOf Year-Month |
|
|
Expressions | |
|
Label |
Value |
|
DSI |
avg({$<_Periodicity={'Monthly'}
,[AsOf Months Ago]={">0<=12"}>}aggr( sum({$<_Periodicity={'Monthly'}
,[AsOf Months Ago]={">0<=12"}>}
[Inventory Balance]),[Year-Month],[AsOf Year-Month]))/sum({$<[AsOf Months Ago]={">0<=12"}>} [Cost])*
365
|
Similar to the financial perspective, we use the [AsOf Months Ago] field in the set analysis to calculate over twelve rolling months. We first use the aggr() function to sum the inventory balance of each Year-Month and then calculate the average monthly balance. We are careful to include [AsOf Year-Month] in the aggr() function because this function only works properly when it contains all fields used as a chart dimension.
We also make sure to use the same set analysis in the avg() function outside the aggr() as we do in the sum() function within the aggr() function. A function's set analysis only applies to the fields that are directly located within the function. It is never adopted by a parent function or inherited by a nested one. We, therefore, have to repeat it for every function. Feel free to experiment and remove the set analysis from one of the functions to see how the values in the graph change.
An acceptable DSI varies per industry but a result between 60 and 240 days is common. The previous chart shows that the company has too much inventory in relation to its sales. At one end, it needs to stop purchasing or producing goods and, at the other end, it needs to increase sales. Let's now take a look at how well we collect customer payments.
Although sales are important, if we don't collect payment for these sales in a reasonable amount of time, then we won't have the cash necessary to keep the business running. Days Sales Outstanding (DSO) is a key performance indicator that measures the average number of days it takes a customer to pay an invoice. Its calculation is quite similar to that of DSI:
Days Sales of Outstanding = (Annual Average A/R Balance / Annual Net Sales) * 365
Let's now add DSO to the bar chart that we created in the previous exercise.
Let's add the following expression to the bar chart from Exercise 5.1. We change the bar chart to a stacked bar chart in the Style tab:
|
Expressions | |
|---|---|
|
Label |
Value |
|
DSO |
avg({$<_Periodicity={'Monthly'}
,[AsOf Months Ago]={">0<=12"}>}aggr( sum({$<_Periodicity={'Monthly'}
,[AsOf Months Ago]={">0<=12"}>}
[A/R Invoice Balance]),[Year-Month],[AsOf Year-Month]))/sum({$<[AsOf Months Ago]={">0<=12"}>} [Net Sales])*
365
|
A healthy DSO depends on the business, but we should expect anything between 15 and 90 days. In the previous chart, we started the year with a DSO that wasn't too far from this range, but, as the year progressed, the DSO grew. As DSO is a ratio that is based on sales and A/R balance, this increase could be caused by an increase in the A/R balance, a decrease in sales, or a mixture of the two. Alongside any DSO analysis, we recommend creating auxiliary charts that can show what is causing the DSO to change. This recommendation also applies to DSI and the final working capital element—Days Payable Outstanding.
In order to determine whether we have a healthy DSO and DSI, we compare them with the key performance indicator Days Payable Outstanding (DPO). DPO measures the average number of days before we pay our suppliers and has the same structure as the previous two indicators:
Days Payable of Outstanding = (Annual Average A/P Balance / Annual Cost of Sales) * 365
Let's now add DPO to the bar chart that we created in the previous exercise.
Let's add the following expression to the bar chart from Exercise 5.2:
|
Expressions | |
|---|---|
|
Label |
Value |
|
DPO |
-avg({$<_Periodicity={'Monthly'}
,[AsOf Months Ago]={">0<=12"}>}aggr( sum({$<_Periodicity={'Monthly'}
,[AsOf Months Ago]={">0<=12"}>}
[A/P Invoice Balance]),[Year-Month],[AsOf Year-Month]))/sum({$<[AsOf Months Ago]={">0<=12"}>} [Cost])*
365
|
An ideal DPO is greater than the sum of DSO and DSI. Such a situation means that the business's suppliers finance its operations. Regardless of whether this is really possible, we aim to reduce the time it takes to convert cash spent into cash received, which is called the Cash Conversion Cycle (CCC):
Cash Conversion Cycle = DSO + DSI – DPO
We make a slight change to the previous chart so that we can explicitly analyze CCC.
Let's go through the following steps to adjust the bar chart from Exercise 5.3:
- Change the bar chart to a combo chart in the General tab.
- In the Expressions tab, add the following expression:
Expressions
Label
Value
CCC
DSO + DSI – DPO
Enable only the Bar option for all the expressions except CCC, which should only have the Symbol option enabled.
- In the Axes tab, select CCC in the Expressions list and enable Right (Top) in the Position section. Enable Split Axis.
We can now analyze all the working capital elements in a single chart. In the previous chart, we can see how an increase in DPO has been offset by an even greater increase in DSI. In the next section, let's look at how we can break down and analyze each of the working capital elements. We'll do this using DSI as an example.