By Jonas Tyrone Lobaton, MSc Quantitative Finance, FMVA®
Founder, Frac CFO · Updated November 16, 2026
You can calculate your cash conversion cycle (CCC) in Excel with one formula, =DIO+DSO-DPO, fed by three inputs pulled from your balance sheet and income statement. Set it up once as a reusable template, and every month you paste in new figures and see how many days your cash is tied up. Below is the exact layout, a worked example, and what to do when the number is too high.
TL;DR
- CCC = days inventory outstanding (DIO) + days sales outstanding (DSO) minus days payables outstanding (DPO). It tells you how many days cash is tied up before it returns.
- You need five figures: average inventory, average accounts receivable, average accounts payable, revenue, and cost of goods sold (COGS).
- A good template has an inputs block, a calculation block and a trend row, so you can compare month to month.
- The FDIC puts a typical CCC at 30 to 45 days, depending on industry, so judge your number against your own sector and your own history.
- Each day you remove frees roughly one day of sales in cash. Our worked example shows the math.
Table of Contents
- What the cash conversion cycle tells you
- The formula and its three parts
- Where to find the inputs
- Step-by-step: build the calculator in Excel
- Worked example with sample figures
- What a reusable template should include
- How to read your number
- Practical ways to shorten the cycle
- Our perspective
- Take it further with a ready-built model
- FAQ
- Sources
What the cash conversion cycle tells you
The FDIC describes the CCC as a measure of how efficiently a company manages working capital: the number of days it takes to turn investments in inventory and other resources into cash from sales. Working capital simply means the money you have tied up in day-to-day operations: stock on shelves, invoices customers still owe you, and bills you still owe suppliers.
The cycle has a clear story behind it. You pay for inventory. It sits until it sells. The customer may not pay for another few weeks. Meanwhile your supplier may or may not be waiting for their money. The CCC adds those waiting periods together.

A shorter cycle means inventory becomes cash faster. The FDIC’s guide says a short cycle puts money back into your cash flow quickly, while a long one leaves money unusable while stock sits unsold. A longer cycle can point to problems in inventory management, collections or payment terms.
This is why owners who track cash in spreadsheets care about it. Profit on paper does not pay rent. Days of cash tied up decide whether you can.
The formula and its three parts
The FDIC states the formula as CCC = DIO + DSO – DPO. Here is what each part means and how to compute it.
| Component | What it measures | Formula |
|---|---|---|
| DIO (days inventory outstanding) | Average days stock sits before it sells | Average inventory ÷ COGS × days in period |
| DSO (days sales outstanding) | Average days to collect from customers | Average accounts receivable ÷ Revenue × days in period |
| DPO (days payables outstanding) | Average days you take to pay suppliers | Average accounts payable ÷ COGS × days in period |
| CCC | Days cash is tied up | DIO + DSO – DPO |
Use 365 days for a year, 90 for a quarter, or 30 for a month, and make sure your revenue and COGS cover the same period. “Average” means the opening balance plus the closing balance, divided by two. For a simpler monthly version, many owners use the month-end balance alone. Pick one method and keep it the same every period, because consistency matters more than precision when you are tracking a trend.
Two notes on who this fits:
- Product businesses (retail, e-commerce, wholesale) use all three parts.
- Service businesses usually have no inventory, so DIO is zero and the cycle becomes DSO minus DPO. That is still a useful number: it shows how many days you fund your clients before they pay.
We cover the inventory piece in more depth in our guide to days inventory outstanding.
Where to find the inputs
You do not need special software. Everything comes from two reports you already have.
- Revenue and COGS: your income statement (profit and loss) for the period.
- Inventory, accounts receivable, accounts payable: your balance sheet at the start and end of the period.
If you only keep a sales log and a bank export, build a simple balance sheet snapshot first: total unpaid customer invoices, the value of stock on hand at cost, and total unpaid supplier bills on the same date. A few figures, kept monthly, are enough.
Avoid a common mistake: using revenue in the DIO and DPO formulas. Inventory and payables are recorded at cost, so they pair with COGS. Receivables are recorded at selling price, so they pair with revenue.
Step-by-step: build the calculator in Excel
Open a blank workbook and create one sheet named CCC. The layout below uses column B for values.
Step 1: Set up the inputs block
| Cell | Label | Example entry |
|---|---|---|
| B2 | Days in period | 365 |
| B3 | Revenue | 900000 |
| B4 | COGS | 600000 |
| B5 | Opening inventory | 70000 |
| B6 | Closing inventory | 80000 |
| B7 | Opening accounts receivable | 85000 |
| B8 | Closing accounts receivable | 95000 |
| B9 | Opening accounts payable | 45000 |
| B10 | Closing accounts payable | 55000 |
Format input cells with a light fill so anyone using the file knows where to type. Leave every calculation cell unfilled.
Step 2: Calculate the averages
- Average inventory in B12:
=AVERAGE(B5,B6) - Average receivables in B13:
=AVERAGE(B7,B8) - Average payables in B14:
=AVERAGE(B9,B10)
Step 3: Calculate the three day counts
- DIO in B16:
=B12/B4*B2 - DSO in B17:
=B13/B3*B2 - DPO in B18:
=B14/B4*B2
Step 4: Calculate the cycle
- CCC in B20:
=B16+B17-B18
Step 5: Protect against divide-by-zero
If a service business has no COGS or inventory, wrap formulas so they return zero instead of an error. For DIO: =IF(B4=0,0,B12/B4*B2). Apply the same pattern to DPO, and to DSO with revenue.
Step 6: Lock the structure
Protect the calculation cells (Review > Protect Sheet) and leave the input cells unlocked. Then a teammate can enter numbers without breaking a formula.
Worked example with sample figures
These figures are illustrative, not from a real company. Imagine an online retailer with $900,000 in annual revenue and $600,000 in COGS.
| Item | Calculation | Result |
|---|---|---|
| Average inventory | (70,000 + 80,000) ÷ 2 | $75,000 |
| Average receivables | (85,000 + 95,000) ÷ 2 | $90,000 |
| Average payables | (45,000 + 55,000) ÷ 2 | $50,000 |
| DIO | 75,000 ÷ 600,000 × 365 | 45.6 days |
| DSO | 90,000 ÷ 900,000 × 365 | 36.5 days |
| DPO | 50,000 ÷ 600,000 × 365 | 30.4 days |
| CCC | 45.6 + 36.5 – 30.4 | 51.7 days |
Read it as a sentence: the business pays cash for stock, then waits about 52 days, net of supplier credit, before that money returns. Compared with the FDIC’s 30 to 45 day typical range, this retailer sits above it, which is worth a look. It does not automatically mean trouble, since the range depends on industry.
What a faster cycle is worth
This business sells about $2,466 a day ($900,000 ÷ 365). Cut DSO by 5 days, by collecting faster, and about $12,300 stays in the bank instead of sitting in unpaid invoices. That is cash you can use for inventory, payroll or growth without borrowing.

What a reusable template should include
A one-off calculation helps once. A template helps every month. Build these pieces into your file.
- An inputs block with clearly marked cells, as above.
- A calculation block that never needs editing.
- A monthly trend table. Add one row per month with columns for DIO, DSO, DPO and CCC, so you see direction, not just a snapshot.
- A line chart of the CCC trend. Select the month and CCC columns, then Insert > Line Chart.
- A target cell. Enter the CCC you want, and add a variance cell:
=B20-target. - Conditional formatting that turns the CCC cell red when it exceeds your target.
- A notes column for explanations, such as “big holiday stock purchase” or “large customer paid late”.
If you sell across several product lines or channels, copy the sheet once per line. Different lines often have very different cycles, and a blended number can hide the one that is draining cash.
For Google Sheets, every formula above works unchanged. Only the protection menu differs (Data > Protect sheets and ranges).
How to read your number
Treat the CCC as a trend and a comparison, not a grade.
- Compare to yourself first. A cycle that grows from 40 to 55 days over six months is a signal, whatever the industry norm is.
- Compare to your sector. The FDIC notes the typical range of 30 to 45 days varies by industry, so a grocery seller and a furniture seller will look very different.
- Look at the parts. If CCC rises, check which of DIO, DSO or DPO moved. Each points to a different fix.
- Watch seasonality. Stocking up before a peak season will lengthen DIO temporarily. Compare the same month year over year.
Practical ways to shorten the cycle
The FDIC recommends three levers, each matching one component:
| Lever | What the FDIC suggests |
|---|---|
| Lower DIO | Sell inventory faster by optimizing stock levels, avoiding overstock and promoting slow-moving items |
| Lower DSO | Collect payments quickly, encourage immediate cash or card payments, and use digital payment systems |
| Raise DPO | Negotiate longer supplier payment terms without harming relationships, including bulk-purchase discounts with favorable terms |
To make this concrete, add a “what-if” block to your template. Create three cells for change in DIO, DSO and DPO (for example -3, -5, +4), and a result cell showing the new CCC and the cash released: =days_change*Revenue/365 for DSO, and the same with COGS for DIO and DPO. You will quickly see which lever frees the most cash for the least effort.
Retailers can attack DIO with disciplined purchasing. Our retail open-to-buy template guide shows how a six-month buying plan keeps stock in line with sales.
Our perspective
Most owners we talk to do not need new software to see their cash conversion cycle. They need the right three numbers, laid out the same way every month, in a file they already understand.
That is the idea behind how we build things. Your spreadsheet stays yours: you can see every formula, change every assumption and explain every figure to a lender or investor. A clean CCC sheet, linked to a cash flow forecast and a simple dashboard, turns a vague worry about cash into a number you can act on.
The sheet itself is the easy part. The value comes from reading it each month, deciding which lever to pull, and checking the result next month. We build that routine into the models we deliver, in Excel and Google Sheets.
frac
Take it further with a ready-built model
If you would rather not build the sheet from scratch, our Working Capital Optimizer is a spreadsheet tool for working capital, and our Excel templates cover the wider set of finance models small businesses use. To see how the cycle feeds your weekly cash position, the 13-Week Cash Flow Forecast is the natural companion.
Want it built around your own books? The Launch Kit is a $650 one-off, and our ongoing fractional CFO plans start at $600 per month (Starter) or $7,200 per year, with hands-on onboarding and email support. Custom one-time projects, such as a KPI dashboard or a profitability review, are described on our one-time projects page.
FAQ
What is a good cash conversion cycle?
The FDIC says a typical cycle is 30 to 45 days, though it depends on the industry. A shorter cycle is generally better because cash returns sooner. The most useful benchmark is your own history: a falling trend means you are getting more efficient.
Can a service business use the cash conversion cycle?
Yes. Without inventory, DIO is zero, so the cycle is DSO minus DPO. It shows how many days you pay for work, staff and tools before clients pay you. Track it monthly alongside your invoice aging.
Can the cash conversion cycle be negative?
Yes. It happens when you collect from customers and sell stock faster than you pay suppliers, so suppliers effectively finance your operations. It means your cash comes back before the bills are due. Check that supplier terms are stable, since a negative cycle depends on them.
Should I use average or ending balances?
Averages smooth out spikes and give a fairer picture over a year. Month-end balances are quicker and fine for monthly tracking. Whichever you choose, use it consistently so your trend is comparable.
How often should I update the template?
Monthly works for most small businesses, right after you close the books. If cash is tight or sales are seasonal, update it every two weeks, and add a 13-week forecast so you see the effect before it hits the bank.
About the author
Jonas Tyrone Lobaton is the founder of Frac CFO. He holds a Master’s degree in Quantitative Finance and the FMVA® certification, with work spanning financial modeling, forecasting, optimization, dashboards, business finance, and spreadsheet automation.
Sources
- FDIC, Module 10 Managing Cash Flow, Instructor Guide
- FDIC, Module 10 Managing Cash Flows, Participant Guide
- FDIC, Managing Cash Flow Participant Guide (Indiana state site)

