A financial concept, Day Sales Outstanding (DSO), is a measure of the average number of days that a company takes to collect revenue  after a sale has been made.   For further clarification, read here.

Herein, i have presented the solution for two different data layouts.  Further, Layout 2 has two further divisions - one for computing Day Sales Outstanding (DSO) based on historical sales and the other for computing Days of Supply (DOS) based on future Cost of Good Sold (COGS).

You may refer to my solution in this workbook.

1. I am trying to calculate the # of months of forward-looking inventory I have on hand for each month. Below, i have laid out the key assumptions.

Row 1: Assume Jan, Feb, Mar, April, May

Row 2: Forecasted Demand: 1, 3, 4, 6, 8

Row 3: Current inventory: 9, 12, 13, 20, 25

Row 4: expected output: for Jan, there is 9 units of inventory so that would cover the demand for Jan (1), Feb (3) and March (4)......so I would want the output to be 3.

Hope that better explains.

• Hi,

Try this

1. In range B2:F2, type 1,3,4,6,8
2. In range B3:F3, type 9,12,13,20,25
3. In cell B4, enter this array formula (Ctrl+Shift+Enter) and copy till cell F4

=IFERROR(MATCH(TRUE,SUBTOTAL(9,OFFSET(B2,,,1,COLUMN(B2:\$F2)-COLUMN()+1))>=B3,0)-1,COUNT(B2:\$F2))

Hope this helps.

• Hello Ashish!
This formula work well for me but I would like to go to another decimal. For instance 3.3 rather than 3. I tried to use the formula at the end of this thread but it did not work for me. Assume the same layout of data as in this example.
Thanks!

• Hi,

• Ashish,

• Never mind. I see it now.

2. Ashish
Its not working. Based on the inputs, I should get an answer of "3" in cell B4.....I am getting an answer of "5". It looks like it is defaulting to the error syntax and returning the value for "count(b2:\$f2).
Chris

• Hi,

The formula has to be Ctrl+Shift+Entered not simple Entered. If you simply Enter, your answer will be 5.

3. Ashish
That worked perfectly. Thank you for your help. I have 2 other things I am trying to do. Please let me know if you are interested in helping or I can post back on the MSN boards. I will upload file to skydrive and send you the link.
Chris

4. Joyce lim

Question: In excel, I am calculating the DSO manually. The example as follows:

DSO Method (New) 28 28 35 28 28 35
DSO Method (Old) 30 30 30 30 30 30
Example for the month of Sep 13
Total Outstanding Sep 13 Aug 13 Jul 13 Jun 13 May 13 Apr 13
New Acc Per BAL Per SLS 30 DSO (30) Per-1 SLS 60 DSO (60) Per-2 SLS 90 DSO (90) Per-3 SLS 120 DSO (120) Per-4 SLS 150 DSO (150) Per-5 SLS 180 DSO (180) DSO (Days)
1000080 384,456.95 785,287.94 553,107.89 799,952.80 836,369.76 591,942.81 649,927.50 14
1000081 1,428,717.92 784,137.80 645,396.42 723,397.19 960,783.81 694,559.44 582,277.60 60
1000082 5,415,568.17 2,334,735.44 2,813,092.14 3,244,705.83 3,080,806.00 2,526,003.50 2,173,871.56 62
1000084 80,921.79 40,381.29 33,062.72 39,944.38 52,446.02 34,956.61 38,442.75 66
1000085 1,069,799.83 269,431.64 304,287.68 370,336.00 288,006.13 396,204.22 63,507.84 103
180

Calendar Day (4-4-5) Days
Oct 13 28
Nov 13 28
Dec 13 35
Jan 14 28
Feb 14 28
Mar 14 35
Apr 14 28
May 14 28
Jun 14 35
Jul 14 28
Aug 14 28
Sep 14 35

Lastly i’m calculate DSO based on 30 days, if i want to change the method of calculate to 28 28 35 days which follow calendar day how to get the formula? Please help to change the DSO formula for which if the monthly sales day for this row is 28 days instead of 30 days (by follow cut off calendar days).

• Hi,

So you want to compute the DSO in range O6:O10. Am I correct? Also, what is the relevance of the DSO columns you have created in columns D,F,H,J,L and N? Can they be deleted?

5. Joyce lim

yup... correct. U may deleted columns D,F,H,J,L and N?

• Hi,

You may refer to my solution in column K of this workbook.

Hope this helps.

• hozefa

Hi Ashish, just came across your blog today and this was exactly our requirement for DSO calculating with 4-4-5 weeks period.

Thank you for sharing this. really great help.

6. Joyce lim

Hi,

Thank you so much, the formula work for every column except for the last column H, let's say the outstanding more than sales, the DSO should be 182 days but i get results #N/A. Can this be solve?
Another question: Let say this Oct month i want calculate DSO, the formula for Oct month can use same formula since the day of sales change to 28 days? How to do it, since every month day of sales difference.

• Hi,

You may refer to my solution in this workbook. The solution here solves both problems. Also, just enter the date in cell C4 and this will change the following automatically

1. Dates in range D4:H4
2. Numbers in C1:H1
3. DSO in K2:K10

Hope this helps.

7. Joyce lim

Hi Ashish

Awesome!!! It really solve my problems, really save a lot of time.
Thank you so much for your help, really appreciate.

• You are welcome. Thank you for your feedback.

8. Prashant

Hi Ashish,
I am looking for a method to calculate suggested DOH FG inventory for a trading entity based on assumptions. can you suggest something on that

• Hi,

9. Thomas Rogenmoser

Hi Ashish,

Your formula to calculate the DSO is absolutely fantastic . I am deeply impressed by your skills.

Due to the fact that my figures are organised differently compared to your examples I needed to adjust the formula – and failed greatly. So far I have thought that I master Excel but it looks like I found my limits...

I have posted my template here:

http://1drv.ms/1nNlkr9

Your help would be much appreciated.

Best regards,
Thomas

• Hi,

Thank you for your kind words. I am glad you liked my solution to the DSO problem. I have resolved your specific query at in this workbook. Refer to the yellow coloured cell on the Formula worksheet.

Hope this helps.

10. Thomas Rogenmoser

Dear Ashish

Thank you very much for your fast reply and the adjusted formula. With a bit of tweaking it works now perfect for me.

Thomas

11. Could someone please help me on this? I work for one of the tier-1 company and i would like to see a pivot chart with DSO and AR trend graph for my company for each month for each account or batch of accounts. I have the basedata of AR, Rev and DSO for each customer for each month. When i pull DSO columns into Pivot chart, it does average/Sum, rather than showing the actual DSO for each month.

• Hi,

12. Why is it when i update your formulas in your worksheets (Opened from this tread), the results come back as #N/A?

• What change are you making in which cell of which workbook?

13. Need your help with a Formula to calculate month on hand
as follows:
Current total November inventory is 1000
Demand remaining in Nov 500
Demand Dec 400
Demand Jan 300
So with 1000 we cover all Nov remain inventory 1000-500, remaining 500
With 500 we cover December demand 500-400, remaining 100
We don’t cover all January demand , a fraction is then calculated : 100/300= .3333
Therefore total Month on hand is 2.33

• Hi,

• End inventory Nov/2015 Dec/2015 Jan/2016
1000 500 400 300

• Hi,

You may refer to my solution in this workbook.

Hope this helps.

• Rebeca

Can not access the page, could you shared the formula please?- Thank you

• Hi,

14. Rebeca

Hello, Lets review your spreadsheet A should be zero , I'm looking at the on hand , based on months of forecast

1 1 1 1 1 1

Product Inventory Nov-15 Dec-15 Jan-16 Feb-16 Mar-16 Apr-16
A 1500 2500 0 0 1000 0 0 0.6
B 0 2500 2500 2500 2500 2500 2500 0
C 0 0 2500 2500 2500 2500 #DIV/0!

Lets review your spreadsheet A should be zero , I'm looking at the on hand based on total forecast

15. rebeca

16. Hi Ashish,

Please help me in providing the formula to calculate the days of stock for the current & coming months. Below is my data. Column A1, A2.... is my stock on hand at the beginning of each month. Column B1, B2 .... is my sale forecast by each month. in column C1, C2 .... i need to know how may days my stock will last.
A B C
1 1000 500 How many days of stock?
2 1750 750 "
3 2000 1000 "

• Hi,

What results are you expecting in column C?

• SYED ZIAULLAH

Hi Ashish,

The result in Jan month column C1 should be 50 days. This is how i came up with result. My Jan opening stock in A1 is 1000. The Jan sales in B1 is 500 (30 days). the Feb sales in B2 is 750 (20 days - Jan Opening Stk less Jan Sales multiply by 30 days divide by Feb Sales).

The Result in Feb Month Column C2 should be 60 days. My Feb opening stock in A2 is 1750. The Feb sales in B2 is 750 (30 days). The Mar sales in B3 is 1000 (30 days - Feb Opening stk less Feb sales multiply by 30 days divide by Mar sales)

I hope i was clear in explaining my query.

• Hi,

Enter this formula in cell C1 and copy down

=(30+((A1-B1)*30)/B2)

Hope this helps.

• SYED ZIAULLAH

Hi Ashish,

Sorry for bothering you. Actually the above formula works for the current & following month. However, if doesn't work if i have numbers from Jan to Dec. Please see below my example.

In column A1 to A12 are the months Jan, Feb, Mar, Apr, May, Jun...... Dec

In column B1 to B12 is my opening stocks from Jan to Dec.
1797,2774,3716,4793,6801,6571,5734,4520,3030,1633,1881 & 1985

In column C1 to C12 is my sales forecast from Jan to Dec.
1200,1250,1500,2209,2234,1869,1739,1490,1397,1072,776 & 748

In column D1 to D12 are the results (stock in days) as per your above formula
44,60,60,65,103,111,110,95,76,52,74 & 80

The result from May to Sep are wrong. They should be 110,123,123,113,81. Rest other months are matching.

• Hi,

Try this

1. Type all month names in range A2:A13
2. Type days in each month in range B2:B13
3. Types Sales figures in range C2:C13
4. Type Closing Stock figures in range D2:D13
5. In cell E2, enter this formula and copy down

=IFERROR(SUBTOTAL(9,OFFSET(B2,MATCH(TRUE,(D2-SUBTOTAL(9,OFFSET(C2,,,(ROW(D2:D\$13)-ROW(C2)+1),1)))<0,0)-2,,-MATCH(TRUE,(D2-SUBTOTAL(9,OFFSET(C2,,,(ROW(D2:D\$13)-ROW(C2)+1),1)))<0,0)+1,1))+(OFFSET(B2,MATCH(TRUE,(D2-SUBTOTAL(9,OFFSET(C2,,,(ROW(D2:D\$13)-ROW(C2)+1),1)))<0,0)-1,,,)/OFFSET(C2,MATCH(TRUE,(D2-SUBTOTAL(9,OFFSET(C2,,,(ROW(D2:D\$13)-ROW(C2)+1),1)))<0,0)-1,,,))*(D2-SUBTOTAL(9,OFFSET(C2,MATCH(TRUE,(D2-SUBTOTAL(9,OFFSET(C2,,,(ROW(D2:D\$13)-ROW(C2)+1),1)))<0,0)-2,,-MATCH(TRUE,(D2-SUBTOTAL(9,OFFSET(C2,,,(ROW(D2:D\$13)-ROW(C2)+1),1)))<0,0)+1,1))),(B2+((D2-C2)*30)/C3))

You will get an error against December. That is because there is no sale for the next month.

Hope this helps.