Compute Day Sales Outstanding (DSO)

{ 46 Comments }

By Ashish Mathur · · · Leave a comment

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.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

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

    Reply

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

      Reply

      • 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!

        Reply

  • 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

    Reply

  • 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

    Reply

  • 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!!:)

    Reply

    • 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?

      Reply

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

    Reply

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

      Reply

  • Hi Ashish

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

    Reply

  • 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

    Reply

  • 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

    Reply

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

      Reply

  • 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

    Reply

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

    Reply

  • 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

    Reply

  • 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

    Reply

  • 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 “

    Reply

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

        Reply

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