|
Calculating days sales outstanding
I have a spreadsheet showing sales and outstanding debts month-by-month. I
need a formula to calculate how many days' sales the outstanding debt represents. I need to do this by countback. e.g. for June 08 below, the calculation is 30 days (because debtors is greater than June's sales, so all the days in June must be outstanding) plus (1873-1020)/1065*31. Month Debtor Sales Days Desired result Jan-08 2569 1203 31 -- Feb-08 2614 1228 29 -- Mar-08 2471 1003 31 66 Apr-08 2561 1181 30 70 May-08 2416 1065 31 66 Jun-08 1873 1020 30 55 Any help would be greatly appreciated. Tim |
Answer: Calculating days sales outstanding
Hi Tim,
To calculate the days sales outstanding, you can use the following formula: =Days Outstanding + ((Debtor - Sales)/Sales per Day) Here's how you can apply this formula to your example:
This will give you the desired result for each month. Let's take the example of June 08. The debtor is 1873 and the sales are 1020. Since the debtor is greater than June's sales, all the days in June must be outstanding. So, we add 30 days to the formula. Then, we subtract the sales from the debtor and divide it by the sales per day (which is 1065/31). This gives us 0.52. Multiplying this by 31 gives us 16.12. Adding this to 30 gives us the desired result of 55. |
Calculating days sales outstanding
Tim,
With your table in cells A1:E7, in F7 I get 55 if I use the formula =ROUND(D7+(B7-C7)/C6*D6,0) but I don't get all of your other desired results.... Wanted My Formula 66 66 This one works... 70 73 66 65 55 55 This one works.... HTH, Bernie MS Excel MVP "Tim Green" wrote in message ... I have a spreadsheet showing sales and outstanding debts month-by-month. I need a formula to calculate how many days' sales the outstanding debt represents. I need to do this by countback. e.g. for June 08 below, the calculation is 30 days (because debtors is greater than June's sales, so all the days in June must be outstanding) plus (1873-1020)/1065*31. Month Debtor Sales Days Desired result Jan-08 2569 1203 31 -- Feb-08 2614 1228 29 -- Mar-08 2471 1003 31 66 Apr-08 2561 1181 30 70 May-08 2416 1065 31 66 Jun-08 1873 1020 30 55 Any help would be greatly appreciated. Tim |
Calculating days sales outstanding
Bernie,
Thanks for your help. The problem is that I don't know how many months back I need to go to cover the value of sales that are outstanding for the month in question. Some accounts have long payment terms or are behind, so there may be several months outstanding. I have now got this working as follows: Month Debtor Sales Days Months Debtor days Jan-08 2,501 1203 31 #REF! #REF! Feb-08 2,446 1228 29 #REF! #REF! Mar-08 2,323 1003 31 2 62 Apr-08 2,419 1181 30 2 67 May-08 2,299 1065 31 2 63 Jun-08 1,834 1020 30 1 54 The formula in the June row and Months column is as follows: =IF($C7$B7,0,IF(SUM($C6:$C7)$B7,1,IF(SUM($C5:$C7 )$B7,2,IF(SUM($C4:$C7)$B7,3,IF(SUM($C3:$C7)$B7, 4,IF(SUM($C2:$C7)$B7,5,"Problem")))))) The result of this formula is the number of full months' sales included in the outstanding balance. In this case, only the current month's sales are outstanding in full. The formula in the Debtor days column is as follows: =IF($B7<$C7,$B7/$C7*$D7,SUM(OFFSET($D7,($E7-1)*-1,0):$D7)+($B7-SUM(OFFSET($C7,($E7-1)*-1,0):$C7))/OFFSET($C7,($E7)*-1,0)*OFFSET($C7,($E7)*-1,1)) This formula takes the full number of days for the months in the previous column. It then subtracts the sales from those months from the current outstanding debt to get the total to pro rate for the previous month. i.e. if I have 3 and a bit months' sales outstanding, I'll take the number of days in the previous 3 months, subtract those 3 months' sales from the current debtor, then pro rate the remainder against the 4 month's sales to calculate the number of days from the 4th month. I'm now happy this setup works, though it is extremely clunky, and I don't like using offset, as now if I add a column the formula will fall over. Ho hum. The remaining problem now is if I get a debt older than 5 months. Is there a way to re-write that first formula to give me the number of full months' sales represented by the current debtor, regardless of how long ago it was? I only stopped at 5 months because I hit the nesting limit. I bet you're glad you asked now! Thanks, Tim "Bernie Deitrick" wrote: Tim, With your table in cells A1:E7, in F7 I get 55 if I use the formula =ROUND(D7+(B7-C7)/C6*D6,0) but I don't get all of your other desired results.... Wanted My Formula 66 66 This one works... 70 73 66 65 55 55 This one works.... HTH, Bernie MS Excel MVP "Tim Green" wrote in message ... I have a spreadsheet showing sales and outstanding debts month-by-month. I need a formula to calculate how many days' sales the outstanding debt represents. I need to do this by countback. e.g. for June 08 below, the calculation is 30 days (because debtors is greater than June's sales, so all the days in June must be outstanding) plus (1873-1020)/1065*31. Month Debtor Sales Days Desired result Jan-08 2569 1203 31 -- Feb-08 2614 1228 29 -- Mar-08 2471 1003 31 66 Apr-08 2561 1181 30 70 May-08 2416 1065 31 66 Jun-08 1873 1020 30 55 Any help would be greatly appreciated. Tim |
Calculating days sales outstanding
Tim,
As a matter of fact, I AM glad I asked ;-) If you don't mind using VBA, copy this code into a regular codemodule of the workbook, and use it like this (on row 7, then copied up and down): To get the months: =BackSum(C7,B7,"M") To get the days: =BackSum(C7,B7,"D") 'Code he Function BackSum(mySale As Range, _ myDebt As Range, _ Per As String) As Integer Dim i As Integer Dim SaleSum As Double Dim days As Integer i = -1 SaleSum = 0 While SaleSum < myDebt.Value i = i + 1 SaleSum = SaleSum + mySale.Offset(-i).Value If Per = "D" Then days = days + mySale.Offset(-i, 1).Value * _ IIf(SaleSum < myDebt.Value, 1, _ 1 + (myDebt.Value - SaleSum) / _ mySale.Offset(-i).Value) End If Wend BackSum = IIf(Per = "D", days, i) End Function HTH, Bernie MS Excel MVP "Tim Green" wrote in message ... Bernie, Thanks for your help. The problem is that I don't know how many months back I need to go to cover the value of sales that are outstanding for the month in question. Some accounts have long payment terms or are behind, so there may be several months outstanding. I have now got this working as follows: Month Debtor Sales Days Months Debtor days Jan-08 2,501 1203 31 #REF! #REF! Feb-08 2,446 1228 29 #REF! #REF! Mar-08 2,323 1003 31 2 62 Apr-08 2,419 1181 30 2 67 May-08 2,299 1065 31 2 63 Jun-08 1,834 1020 30 1 54 The formula in the June row and Months column is as follows: =IF($C7$B7,0,IF(SUM($C6:$C7)$B7,1,IF(SUM($C5:$C7 )$B7,2,IF(SUM($C4:$C7)$B7,3,IF(SUM($C3:$C7)$B7, 4,IF(SUM($C2:$C7)$B7,5,"Problem")))))) The result of this formula is the number of full months' sales included in the outstanding balance. In this case, only the current month's sales are outstanding in full. The formula in the Debtor days column is as follows: =IF($B7<$C7,$B7/$C7*$D7,SUM(OFFSET($D7,($E7-1)*-1,0):$D7)+($B7-SUM(OFFSET($C7,($E7-1)*-1,0):$C7))/OFFSET($C7,($E7)*-1,0)*OFFSET($C7,($E7)*-1,1)) This formula takes the full number of days for the months in the previous column. It then subtracts the sales from those months from the current outstanding debt to get the total to pro rate for the previous month. i.e. if I have 3 and a bit months' sales outstanding, I'll take the number of days in the previous 3 months, subtract those 3 months' sales from the current debtor, then pro rate the remainder against the 4 month's sales to calculate the number of days from the 4th month. I'm now happy this setup works, though it is extremely clunky, and I don't like using offset, as now if I add a column the formula will fall over. Ho hum. The remaining problem now is if I get a debt older than 5 months. Is there a way to re-write that first formula to give me the number of full months' sales represented by the current debtor, regardless of how long ago it was? I only stopped at 5 months because I hit the nesting limit. I bet you're glad you asked now! Thanks, Tim "Bernie Deitrick" wrote: Tim, With your table in cells A1:E7, in F7 I get 55 if I use the formula =ROUND(D7+(B7-C7)/C6*D6,0) but I don't get all of your other desired results.... Wanted My Formula 66 66 This one works... 70 73 66 65 55 55 This one works.... HTH, Bernie MS Excel MVP "Tim Green" wrote in message ... I have a spreadsheet showing sales and outstanding debts month-by-month. I need a formula to calculate how many days' sales the outstanding debt represents. I need to do this by countback. e.g. for June 08 below, the calculation is 30 days (because debtors is greater than June's sales, so all the days in June must be outstanding) plus (1873-1020)/1065*31. Month Debtor Sales Days Desired result Jan-08 2569 1203 31 -- Feb-08 2614 1228 29 -- Mar-08 2471 1003 31 66 Apr-08 2561 1181 30 70 May-08 2416 1065 31 66 Jun-08 1873 1020 30 55 Any help would be greatly appreciated. Tim |
Calculating days sales outstanding
Bernie,
Thank you very much for this code. I was trying to avoid VBA to keep things simple for other users of the spreadsheet! Obviously I failed, as my formulae are impenetrable and the custom function is very elegant and more robust, so thanks again. I did add a myMonth parameter to replace mySale.Offset(-i, 1).Value with Day(DateSerial(Year(myMonth.Offset(-i)), thus eliminating a column offset that could have upset the function in future. Tim p.s. the final function looked like this, if anyone ever needs it: Function DebtorDays(myDebt As Range, mySale As Range, _ myMonth As Range, Optional per As String) As Integer Dim i As Integer Dim SaleSum As Double Dim days As Integer If per = "" then per = "D" i = -1 SaleSum = 0 While SaleSum < myDebt.Value i = i + 1 SaleSum = SaleSum + mySale.Offset(-i).Value If Per = "D" Then days = days + Day(DateSerial(Year(myMonth.Offset(-i)), _ Month(myMonth.Offset(-i)) + 1, 0)) * _ IIf(SaleSum < myDebt.Value, 1, _ 1 + (myDebt.Value - SaleSum) / _ mySale.Offset(-i).Value) End If Wend DebtorDays = IIf(Per = "D", days, i) End Function "Bernie Deitrick" wrote: Tim, As a matter of fact, I AM glad I asked ;-) If you don't mind using VBA, copy this code into a regular codemodule of the workbook, and use it like this (on row 7, then copied up and down): To get the months: =BackSum(C7,B7,"M") To get the days: =BackSum(C7,B7,"D") 'Code he Function BackSum(mySale As Range, _ myDebt As Range, _ Per As String) As Integer Dim i As Integer Dim SaleSum As Double Dim days As Integer i = -1 SaleSum = 0 While SaleSum < myDebt.Value i = i + 1 SaleSum = SaleSum + mySale.Offset(-i).Value If Per = "D" Then days = days + mySale.Offset(-i, 1).Value * _ IIf(SaleSum < myDebt.Value, 1, _ 1 + (myDebt.Value - SaleSum) / _ mySale.Offset(-i).Value) End If Wend BackSum = IIf(Per = "D", days, i) End Function HTH, Bernie MS Excel MVP "Tim Green" wrote in message ... Bernie, Thanks for your help. The problem is that I don't know how many months back I need to go to cover the value of sales that are outstanding for the month in question. Some accounts have long payment terms or are behind, so there may be several months outstanding. I have now got this working as follows: Month Debtor Sales Days Months Debtor days Jan-08 2,501 1203 31 #REF! #REF! Feb-08 2,446 1228 29 #REF! #REF! Mar-08 2,323 1003 31 2 62 Apr-08 2,419 1181 30 2 67 May-08 2,299 1065 31 2 63 Jun-08 1,834 1020 30 1 54 The formula in the June row and Months column is as follows: =IF($C7$B7,0,IF(SUM($C6:$C7)$B7,1,IF(SUM($C5:$C7 )$B7,2,IF(SUM($C4:$C7)$B7,3,IF(SUM($C3:$C7)$B7, 4,IF(SUM($C2:$C7)$B7,5,"Problem")))))) The result of this formula is the number of full months' sales included in the outstanding balance. In this case, only the current month's sales are outstanding in full. The formula in the Debtor days column is as follows: =IF($B7<$C7,$B7/$C7*$D7,SUM(OFFSET($D7,($E7-1)*-1,0):$D7)+($B7-SUM(OFFSET($C7,($E7-1)*-1,0):$C7))/OFFSET($C7,($E7)*-1,0)*OFFSET($C7,($E7)*-1,1)) This formula takes the full number of days for the months in the previous column. It then subtracts the sales from those months from the current outstanding debt to get the total to pro rate for the previous month. i.e. if I have 3 and a bit months' sales outstanding, I'll take the number of days in the previous 3 months, subtract those 3 months' sales from the current debtor, then pro rate the remainder against the 4 month's sales to calculate the number of days from the 4th month. I'm now happy this setup works, though it is extremely clunky, and I don't like using offset, as now if I add a column the formula will fall over. Ho hum. The remaining problem now is if I get a debt older than 5 months. Is there a way to re-write that first formula to give me the number of full months' sales represented by the current debtor, regardless of how long ago it was? I only stopped at 5 months because I hit the nesting limit. I bet you're glad you asked now! Thanks, Tim "Bernie Deitrick" wrote: Tim, With your table in cells A1:E7, in F7 I get 55 if I use the formula =ROUND(D7+(B7-C7)/C6*D6,0) but I don't get all of your other desired results.... Wanted My Formula 66 66 This one works... 70 73 66 65 55 55 This one works.... HTH, Bernie MS Excel MVP "Tim Green" wrote in message ... I have a spreadsheet showing sales and outstanding debts month-by-month. I need a formula to calculate how many days' sales the outstanding debt represents. I need to do this by countback. e.g. for June 08 below, the calculation is 30 days (because debtors is greater than June's sales, so all the days in June must be outstanding) plus (1873-1020)/1065*31. Month Debtor Sales Days Desired result Jan-08 2569 1203 31 -- Feb-08 2614 1228 29 -- Mar-08 2471 1003 31 66 Apr-08 2561 1181 30 70 May-08 2416 1065 31 66 Jun-08 1873 1020 30 55 Any help would be greatly appreciated. Tim |
| All times are GMT +1. The time now is 06:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com