Countback method for DSO – DIO – DPO Best Practice (Includes countforward method!)

All Industries, Financial Model, General Excel Financial Models

Accounts Payable, Accounts Receivable, Budgeting, Cash Flow Analysis, CFO, Controlling, Excel, Financial Planning, Financial Reporting, Forecasting, Inventory

This Excel model will help you with your needs to better understand your inventory management and your trade receivables and trade receivables management. While there are several ways to calculate DSO DPO and DIO, none of them tops the countback method. This is the most accurate widespread calculation method out there, used by the most advanced financial analysts within the Big 4. This model is now available for you to download and is super easy to use.

You will simply need to input your monthly inventory, trade receivables and trade payables value along with Revenue, COGS and purchases. Once this is done, that’s it! You can now look at your different trends and start getting insights from your data.

For those of you who would like to analyse DIO on a product by product basis, the template already includes the possibility to analyse DIO on 10 products at once.

What I offer in this tool is one of the cleanest and clearest way to compute DSO DIO and DPO using the countback method. Here is how I proceeded in 3 simple steps:
– First the model calculates the number of full months of sales (for DSO) included in Trade receivables.
– Then it calculates the pro-rata it should apply for the last partial month
– Finally it multiply each month (partial or complete) by the number of days in this given month

For Inventory, I offer two ways to calculate DIO.
1. The first is on past purchases which will give you an indication of how many days a purchase remains in inventory.
2. The second is on future COGS, which should give you a good indication on how good your inventory management / forecast process is

This Excel Model includes a summary tab with 4 nice charts which showcase the monthly movement of the balance sheet elements (TP, TR, Inventory) along with DSO DIO DPO.

You must log in to submit a review.