Features
Part of Cash management is a build order, not a single product
Cash management calculator: the conversion cycle and the low point
This cash management calculator page computes nothing. It teaches the conversion cycle and the weekly low point, worked on invented placeholder figures.
What to take away
- A cash management calculator turns six ledger inputs into two numbers: the cash conversion cycle and the weekly low point.
- The cycle is three ratios added togetherdays of stock, plus days waiting to be paid, minus days you take to pay.
- One day of that cycle is worth one day of sales. That is the number that turns a ratio into money.
- A rolling weekly forecast finds the low point. The buffer is sized from the low point, not from an average.
This page gives you a working calculator layout, not a dead form. It is a cell map you can paste into Microsoft Excel, Google Sheets, LibreOffice Calc, Apple Numbers, or Zoho Sheet. Enter your own figures and the formulas return the cycle and the low point.
The two pieces of arithmetic answer what a cash management calculator is supposed to answer: how many days your money is tied up in the business, and where the low point in the coming weeks will be. Both use your ledger, not invented placeholders.
The cash management calculator: cycle inputs and formulas
The cycle sheet uses a period, meaning the number of days the figures cover. Each ratio divides a balance by a flow over that period. The calculator returns DIO, DSO, DPO, and CCC from the six inputs below.
Building the cash conversion cycle
- Average inventory / COGS x 90 = DIO
- Average receivables / revenue x 90 = DSO
- Average payables / purchases x 90 = DPO
- CCC = DIO + DSO minus DPO
Cycle inputs and formulas
| Cell | Label | Enter | Formula |
|---|---|---|---|
| B2 | Cost of goods sold, period | 180,000 | |
| B3 | Average inventory | 45,000 | |
| B4 | Revenue, period | 300,000 | |
| B5 | Average receivables | 60,000 | |
| B6 | Purchases, period | 180,000 | |
| B7 | Average payables | 30,000 | |
| B8 | Days in period | 90 | |
| B9 | Days of stock (DIO) | =B3/B2*B8 | |
| B10 | Days to collect (DSO) | =B5/B4*B8 | |
| B11 | Days to pay (DPO) | =B7/B6*B8 | |
| B12 | Cash conversion cycle (CCC) | =B9+B10-B11 | |
| B13 | Daily revenue | =B4/B8 | |
| B14 | Daily cost of goods | =B2/B8 | |
| B15 | Cash tied up in cycle | =B12*B14 |
This is a working calculator: enter your own figures in the Enter column, and the formulas in B9 to B15 compute.
Days of stock (DIO). Average inventory, divided by cost of goods sold for the period, multiplied by the days in the period.
Days to collect (DSO). Average receivables, divided by revenue for the period, multiplied by the days in the period.
Days to pay (DPO). Average payables, divided by purchases for the period (cost of goods sold is a usable stand-in), multiplied by the days in the period.
Cycle (CCC) = DIO + DSO minus DPO.
Use averages of the opening and closing balances, and the same period for all three. The services that act on the result, and what an institution asks before switching them on, are set out under what treasury services require. Mixing a year of revenue with a quarter of receivables gives a nonsense answer.
Cycle worked example, invented figures
An imaginary wholesaler, over a 90-day period. Every number is a placeholder.
Cycle worked example inputs
- Cost of goods sold, 90 days
- 180,000
- Average inventory
- 45,000
- Revenue, 90 days
- 300,000
- Average receivables
- 60,000
- Purchases, 90 days
- 180,000
- Average payables
- 30,000
Cycle worked example
- DIO = 45,000 / 180,000 x 90 = 22.5 days
- DSO = 60,000 / 300,000 x 90 = 18 days
- DPO = 30,000 / 180,000 x 90 = 15 days
- CCC = 22.5 + 18 minus 15 = 25.5 days
The imaginary wholesaler pays for goods, on average, 25.5 days before it is paid for them.
Turning days into money
Daily revenue is 300,000 / 90 = 3,333.33. Daily cost of goods is 180,000 / 90 = 2,000.
- One day of DSO is worth 3,333.33 of cash. Collecting three days faster frees about 10,000, once, and keeps it freed as long as the improvement holds.
- The cash tied up in the cycle is roughly the cycle times daily cost: 25.5 x 2,000 = about 51,000. That is the working capital the pattern demands.
- To cut DSO from 18 to 15 days, receivables would have to fall to 50,000 (15 / 90 x 300,000).
- To stretch DPO from 15 to 20 days, average payables would have to rise to 40,000 (20 / 90 x 180,000), which means agreeing longer terms with suppliers, not paying late.
Service firm and retailer cycles
An imaginary service firm with no stock, same period: DIO 0, DSO 45 (receivables 100,000 on revenue 200,000), DPO 20. Cycle 25 days, almost the same as the wholesaler, but all of it is in collections. The lever is invoicing and chasing, nothing else.
An imaginary retailer paid at the till and buying on terms: DIO 10, DSO 2, DPO 30. Cycle minus 18 days. It is paid before it pays.
A negative cycle is a source of cash while the business grows and a drain if it shrinks. That is the trap such businesses fall into. Where a surplus produced that way should sit is a separate decision, covered under high-yield accounts.
The weekly low point calculator: rolling forecast
The cycle tells you how much is tied up on average. It does not tell you when the account runs dry. For that you need a rolling forecast by week: opening balance, plus what you expect in, minus what you expect out, giving the closing balance, which becomes next week's opening.
Weekly low point worked example, invented figures
An imaginary business opens with 20,000. Inflows and outflows are placeholders; the outflow pattern repeats every three weeks because payroll falls in one week of three.
Weekly low point worked example
| Week | Opening | In | Out | Closing | Formula |
|---|---|---|---|---|---|
| 1 | 20,000 | 14,000 | 11,000 | 23,000 | =B2+C2-D2 |
| 2 | 23,000 | 9,000 | 16,500 | 15,500 | =E2+C3-D3 |
| 3 | 15,500 | 6,000 | 9,000 | 12,500 | =E3+C4-D4 |
| 4 | 12,500 | 22,000 | 11,000 | 23,500 | =E4+C5-D5 |
| 5 | 23,500 | 8,000 | 16,500 | 15,000 | =E5+C6-D6 |
| 6 | 15,000 | 7,000 | 9,000 | 13,000 | =E6+C7-D7 |
| 7 | 13,000 | 15,000 | 11,000 | 17,000 | =E7+C8-D8 |
| 8 | 17,000 | 12,000 | 16,500 | 12,500 | =E8+C9-D9 |
The low point formula is =MIN(E2:E9). Paste it below the Closing column and it returns 12,500 for this invented example. That is the number to size the buffer from.
The low point is 12,500, reached in week 3 and again in week 8. The average closing balance is higher, and it is the average that misleads people. The buffer is set by the depth and frequency of the low weeks, plus a margin for a late-paying customer.
Using the forecast
- Roll it.Each week, drop the week that has passed, add a new week at the end, and replace forecasts with actuals for the week just gone.
- Record the variance.Forecast minus actual, by line. After a few cycles the pattern of misses tells you which assumption to fix.
- Stress one week.Take the low week and remove the largest single receipt. If the result is below zero, that is the gap a buffer or a facility has to cover. Government guidance on managing business finances covers arranging that facility before you need it rather than during the week you do.
How the forecast fits into the wider build order, and how to size the buffer from it, is set out under cash management. Where the buffer should sit once you have sized it is a separate question, covered under business savings.
Errors that spoil the arithmetic
- Mixing periodsquarterly balances against annual flows.
- Using closing balances instead of averages when the balance swings.
- Treating revenue as cash received. Revenue is what was invoiced; the forecast needs what will arrive, and money deposited is not the same as money available, which has a federal baseline in Regulation CC.
- Forgetting the annual itemstax, insurance, licenses, renewals. They land in one week and wreck the low point.
- Improving DPO by paying late rather than by agreeing terms, which costs the supplier relationship and often a discount.
Do it this week
Pull the six inputs for your last full quarter, compute the three ratios and the cycle, and work out what one day is worth.
Then build the eight-week table with your own figures and find the low point.
Those two numbers, the cycle and the low point, are what a cash management calculator would have given you. Now you know where they came from.
Common questions
Why is this a spreadsheet calculator instead of a web form?
Because the inputs come from your own ledger, and a form that accepts them would only hide the arithmetic you need to act on it. The calculator table above is the tool; paste it into Excel, Google Sheets, or LibreOffice Calc.
Which of the three ratios should I work on first?
The one with the most days in it, unless it is DPO, where "improving" means longer agreed terms rather than slower payment. For most businesses without stock the answer is DSO.
How far ahead should the forecast go?
Far enough to cover your longest payment cycle plus one payroll. A rolling quarter is a common convention and a reasonable default.
Are the placeholder figures realistic?
They are round numbers chosen to make the arithmetic visible. Do not read anything into them; substitute your own.
