# Cash Flow Matrix

Document Sample

```					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

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 189 posted: 1/8/2008 language: English pages: 5