RefDate ######
100%
1 10%
2 40%
3 20%
4 15%
5 10%
6 5%
7 0%
1 2 3 4 5 1999 1999 1999 1999 1999 Month: Jan Feb Mar Apr May
n: Year: n Year Month Amount
6 1999 Jun
7 1999 Jul
8 1999 Aug
9 1999 Sep
10 1999 Oct
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
1999 1999 1999 1999 1999 1999 1999 1999 1999 1999 1999 1999 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2,000 3,000 4,000 5,000 6,000 7,000 8,000 7,000 6,000 5,000 4,000 3,000 2,000 -
-
-
-
-
200 -
800 300 -
400 1,200 400 -
300 600 1,600 500 -
200 450 800 2,000 600 -
100 300 600 1,000 2,400 700 -
11 1999 Nov
12 1999 Dec
13 2000 Jan
14 2000 Feb
15 2000 Mar
16 2000 Apr
17 2000 May
18 2000 Jun
19 2000 Jul
20 2000 Aug
21 2000 Sep
22 2000 Oct
150 400 750 1,200 2,800 800 -
200 500 900 1,400 3,200 700 -
250 600 1,050 1,600 2,800 600 -
300 700 1,200 1,400 2,400 500 -
350 800 1,050 1,200 2,000 400 -
400 700 900 1,000 1,600 300 -
350 600 750 800 1,200 200 -
300 500 600 600 800 -
250 400 450 400 -
200 300 300 -
150 200 -
100 -
23 2000 Nov
24 2000 Dec
-
-
Hello Will someone give me some help in setting up a model to forecast accounts receivables. I should be something like this: I regret the rather long-winded question but ....
Input:
B1 C1 D1
F1 G1 H1 Payment proportions 0.00
0.10
0.40
0.30
0.10
0.00
meaning that 10% of cash flows are received in the month of sale, 40% in the first month after sale, 30% in the second month and 10% in the third month. The model should allow to work with payment proportions extending over up to month five after sales.
I have Start month as input in cell B2 (dd-mm-yyyy). I have entered here: 01-01-1999
Row 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Mth. Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Credit sales
C10 D10 E10 Jan Feb Mar Apr May .......
Dec
2000 3000 4000 5000 6000 7000 8000 7000 6000 5000 4000 3000
200
800 600 400 300 1200 900 600 400 1600 1200 800 etc.
In C10 I have the formula: =B2 In D10 I have the formula: =EDATE($B$2, COLUMNS($A$1:A1) which I drag to M10. In A15 I have the formula: =EDATE($B$2, -(COUNTA($B$1:$H$1)-1)). In A16 I have the formula: =EDATE($A$15, Rows($A$1:A1) which I drag to A29. What I am after is some help with the formulas to calculate the monthly cash flows (as indicated in rows 18-20).
If I enter for example 0.10; 0.40; 0.20; 015; 0.10; 0.05 in cells B1:H1 then I should have 200; 800; 400; 300; 200; 100 in cells C18: H18 etc.
Somewhere I feel it should be rather elementary, but nevertheless I can't figure out the right formulas.
Hans Knudsen hans.knudsen@mail.tele.dk
hans.knudsen@mail.tele.dk