View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Tim Green Tim Green is offline
external usenet poster
 
Posts: 17
Default 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