CashflowMatrix - RefDate ### 100% 1 10% 2 40% 3 20% 4 15% 5...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: RefDate ### 100% 1 10% 2 40% 3 20% 4 15% 5 10% 6 5% 7 0% n: Year: Month: n Year Month Amount 1 2 3 4 5 1999 1999 1999 1999 1999 Jan Feb Mar Apr May 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 F1 G1 H1 Payment proportions 0.00 0.10 C1 0.40 D1 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 [email protected] Somewhere I feel it should be rather elementary, but nevertheless I can't figure out the right formulas. Hans Knudsen [email protected] ...
View Full Document

This note was uploaded on 06/28/2010 for the course MKTG 00HP01 taught by Professor Himanshu during the Spring '10 term at Indiana Institute of Technology.

Ask a homework question - tutors are online