Calculating A/R in a Financial Model

[Guest]

I am trying to calculate accounts receivable in a 12-month financial model in the following manner. Assume that cell A1 holds the assumption for days outstanding and that DOS is currently 45. In month 2, I would like to be able to take the second half of the first month and the complete second month and put them in A/R and in the third month I would want to take the second half of the second month's sales and the entire third month's sales and put them in A/R and then copy across.Obviously, days outstanding could change so formula needs to be flexibe. Can this be done? Thank you for your help.

 

[Dick Kusleika]

Sjt

Assume that DOS is in row 1, A/R is in Row 2 and Sales is in Row 5, for each
month. Assume the data starts in Col B with row headings in Col A. You'll
have to start this formula in Col C, because you won't have prior month's
sales if you start in Col B. Here's the formula for C2

=(MAX((C1/30)-1,0)*B5)+(MIN(C1/30,1)*C5)

Note that this will work for DOS from zero to 60. If you need to be able to
go higher than 60, let me know and we can adjust the formula. Also note
that a standard of 30 day months is used. If you have a row for dates, you
can use the actual number of days in the months.

In this formula
C1 = DOS for February
B5 = Sales for January
C5 = Sales for February

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

I am trying to calculate accounts receivable in a 12-month financial model

in the following manner. Assume that cell A1 holds the assumption for days
outstanding and that DOS is currently 45. In month 2, I would like to be
able to take the second half of the first month and the complete second
month and put them in A/R and in the third month I would want to take the
second half of the second month's sales and the entire third month's sales
and put them in A/R and then copy across.Obviously, days outstanding could
change so formula needs to be flexibe. Can this be done? Thank you for your
help.