Simon G. · Excel template · free
Cash conversion cycle and working capital days: a free Excel template
Five figures per year give the days your money stays in stock and in receivables, the days your suppliers finance you, and what one day is worth.
Excel workbook (.xlsx), no macros, no sign-up.
What you fill in
- Revenue
- Cost of goods sold
- Stock at year end
- Receivables from customers at year end
- Payables to suppliers at year end
All five are in the annual balance sheet and the profit and loss account. Fill in two years to see the change.
The formulas
- Days of stock
stock ÷ cost of goods sold × 365How long goods stay in the warehouse before they are sold.
- Days to collect
receivables ÷ revenue × 365How long customers take to pay.
- Days to pay
payables ÷ cost of goods sold × 365How long you take to pay suppliers.
- Cash cycle
days of stock + days to collect − days to payThe days between paying your supplier and being paid by your customer.
- One day is worth
cost of goods sold ÷ 365, and revenue ÷ 365What you would release by taking one day out of stock, or one day out of collection.
A worked example
A distributor with a revenue of 48.2 million and a cost of goods sold of 37.1 million holds 9.4 million of stock, is owed 7.9 million and owes suppliers 6.1 million.
| Days of stock | 9.4 ÷ 37.1 × 365 = 92 |
|---|---|
| Days to collect | 7.9 ÷ 48.2 × 365 = 60 |
| Days to pay | 6.1 ÷ 37.1 × 365 = 60 |
| Cash cycle | 92 + 60 − 60 = 92 days |
| One day of stock | 37.1 million ÷ 365 = 101,644 |
| Ten days of stock and five of collection | 10 × 101,644 + 5 × 132,055 = 1,676,712 released |
What a spreadsheet cannot see
- Year-end balances are one day out of 365. A company that empties its warehouse in December looks better than it is.
- The figures do not say which products hold the stock.
- They do not say which customers pay late.
The free Working Capital Check does this calculation in your browser and explains each figure. The Stock Check then shows which products hold the excess, and the Credit Check which customers pay late. Nothing you enter leaves your computer.
Questions
Should I use cost of goods sold or revenue for stock?
Cost of goods sold. Stock is carried at cost, so dividing it by revenue makes the days look shorter than they are.
What is a good cash cycle?
It depends on the trade. Compare with your own previous years first. A cycle that grows while revenue stays flat means more money is standing still.
Can the cycle be negative?
Yes. A company paid by customers before it pays suppliers has a negative cycle. It is common in retail.
May I change it and share it?
Yes, freely, with or without credit.
The other free template: Safety stock and reorder point