Skip to main content

Frac CFO

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.

Fraccfo
See Your Cash Flow More Clearly
Frac CFO helps businesses use spreadsheets for financial modeling, forecasting, and clearer insight into cash flow and financial health.

Visit

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 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.

Timeline of inventory, customer collection, and supplier payment

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 faster cycle is worth

What a reusable template should include

A one-off calculation helps once. A template helps every month. Build these pieces into your file.

  1. An inputs block with clearly marked cells, as above.
  2. A calculation block that never needs editing.
  3. 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.
  4. A line chart of the CCC trend. Select the month and CCC columns, then Insert > Line Chart.
  5. A target cell. Enter the CCC you want, and add a variance cell: =B20-target.
  6. Conditional formatting that turns the CCC cell red when it exceeds your target.
  7. 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.

Cash  Financing Optimizer

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

Leave a Reply