calculating inflows in each month depending on different paymentterms

[johniellll]

Hi there,


I have the following problem. We're sealling 2 products, each has
different payment term and the payment term can change during the
year. It is pretty simple if payment term is an equivalent of 30 days
(if =30 then inflows are in the next month, if =90 then inflows are in
n+3 month and so on) The problem occurs when payment term is different
than equivalent of 30 days. We noticed that if payment term =7 days,
than 7/30=23% of inflows are in n+1 month and (30-7)/30=77% of inflows
are in n month (if payment term =14 then 14/30=47% is in n+1 month and
16/30=53% is in n month). The same is for payment term e.g. =40,
40=30+10 so 10/30=33% of inflows are in n+2 months and (30-10)/30=66%
of inflows are in n+1 month.

I hope that following example will help you understand what I am
talking about:

Jan Feb Mar Apr May Jun Jul Aug and so on
Revenue: 4252 3422 4534 6356 3643 4346 1122 3424

Product A 30% 20% 15% 20% 25% 30% 30% 27%
Payment term (in days) 30 30 180 60 14 14 14 14

Product B 70% 80% 85% 80% 75% 70% 70% 73%
Payment term (in days) 40 40 40 30 30 15 14 14

Inflows: ... ... ... ... ... ... ... ...

where

Feb: 4252*30% + 4252*70%*(20/30)
Mar: 4252*70%(10/30) + 3422*20% + 3422*80%*(20/30)
Apr: 3422*80%*(10/30) + 4534*85%*(20/30)
May: 4534*85%*(10/30) + 6354*80% + 3643*25%*(16/30)
Jun: 6356*20% + 3643*25%*(14/30) + 3643*75% + 4346*30%*(16/30) +
4346*70%*(15/30)

or in other words

Inflows:
Feb: Jan*30% + Jan*70%*(20/30)
Mar: Jan*70%*(10/30) + Feb*20% + Feb*80%*(20/30)
Apr: Feb*80%*(10/30) + Mar*85%*(20/30)
May: Mar*85%*(10/30) + Apr*80% + May*25%*(16/30)
Jun: Apr*20% + May*25%*(14/30) + May*75% + Jun*30%*(16/30) +
Jun*70%*(15/30)

and so on (I got data for 2008-2011)

Is there a way to write some formulas that will calculate the inflows
in each month automatically?

Lukasz

 

[Héctor Miguel]

hi, ?

1) it's not very clear why you consider partial incomes in some cases and not in another (i.e.)
- for Feb your incomes includes "... + 4252*70%*(20/30)" which term in days is 40
- for Apr your incomes EXcludes any proportion from Mar/4534*15% where days term is 180 -???-

2) using {tab} and {space} to "simulate" an excel table is NOT as "visible" as when you comment (real) ranges -?-
it's also unclear if Jan/Feb/... are only the 3 months letter, or we are talking about (real) date-entries -?- so...

3) these are (my) "assumptions" (your data table arrangement):
a) column A is for row titles
b) data in columns B:I (Jan/Feb/... in range [B1:I1])
c) revenues in row 2
d) row 3 is empty (same as rows 6 & 9 which will be used as "helper rows")
e) percentage for products in rows 4 (product A) and 7 (product B)
f) payment term in days in rows 5 (product A) and 8 (product B)

1) find the month in which collect of funds reaches the payment term:
[B6] =ceiling(b5/30,1)+columns($b$1:b$1)
[B9] =ceiling(b8/30,1)+columns($b$1:b$1)
-> fill-right this formulae up to column I (column # 9)

2) this is your incomes formula (assuming you start on row 11 and column C) [C11]
=sumproduct(--($b6:b6=columns($b1:c1)),$b2:b2,$b4:b4)+sumproduct(--($b9:b9=columns($b1:c1)),$b2:b2,$b7:b7)
-> fill-right this formula up to column_n

if any doubts (or further-clear information)... would you please comment ?
hth,
hector.

__ original post __