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.

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,

My reply on December 1, 2015 shows the DSO in decimal places. Please download that workbook.

Ashish,

This string is very helpful and interesting. Where can I find the link to your December 1, 2015 reply?

Never mind. I see it now.

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.

Please retry.

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

You are welcome.

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

Here is the link of the file - https://skydrive.live.com/view.aspx?Bsrc=Share&Bpub=SDX.SkyDrive&resid=CBE6893192E84062!157&cid=cbe6893192e84062&app=Excel&authkey=!AqM_g223f5PIIGk

Thanks in advance!!:)

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?

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.

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.

You are welcome.

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.

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.

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,

Please post your exact question in detail. Upload your file to SkyDrive and share the link of the uploaded file here.

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.

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.

You made my day!

Thomas

You are welcome.

Formula is not working in following DSO File, Kindly help me:

https://docs.google.com/a/pearson.com/spreadsheets/d/1kkGkOqJ0bo8Exo9JLRF_oUFmve7l7i3YiCDXET5NtJM/edit?usp=sharing

Hi,

You may refer to my solution in this workbook.

Hope this helps.

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,

Upload your workbook to OneDrive and share the download link here.

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?

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,

The formula will depend upon your data layout. Please upload your workbook to OneDrive and share the link of the workbook here.

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.

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

Hi,

It is working fine. Please retry downloading the workbook from your home computer.

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

Please, discard my comment, thank you

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?

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.

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.

Thanks in Advance.

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.