Calculating revenue based on accounting months

[Gazzr]

Hi,

Hope you can help. We have sales reps that sell online advertising
space that can start and finish when the customer wants. EG booking
from 06/01/06 to 05/02/06 for a total of $1,000.

The accounts department needs to show the revenue in their accounting
month. In this case Jan month is from 01/01/06 to 27/01/06, Feb month
is 28/01/06 to 24/02/06.

So in this case some of the $1000 would be Jan revenue and some Feb
revenue.

What sort of formula could I use to show what accounting month the
revenue should appear in based on the start and finish date of the
advertising?

i.e The sales rep can enter the contract amount and the start and
finish date of the contract and the formula will work out what
accounting month the revenue should appear in and show this in the
appropriate month columns.

Regards
Garry

 

[Fred Smith]

You will need a definition of how the revenue is to be prorated, but let's
assume it's by the number of days in the contract. Therefore, let:

a1 = start date = date(6,1,6)
a2 = end date = date(6,2,5)
a3 = revenue = 1000
a4 = revenue /day = a3 / (a2-a1)
a5 = revenue in first month = a4 * (date(year(a1),month(a1)+1,0) - a1)
a6 = revenue in last month = a4 * day(a2)

It gets more complicated if the contract spans more than two months, but you can
extrapolate the above to accommodate.

 

[Gazzr]

Hi Fred,

Thanks for the quick response. I have tried out the example you
provided but it doesn’t seem to account for the fact that the Jan
financial month ends on 27th Jan and not the 31st. So the number of
Days revenue in month one should be 1000 / 33 * 22 Days, then month two
should be 1000 / 33 * 9 Days.

It all sounds too hard doesn’t it!

Kind Regards
Garry

 

[flummi]

I think what you need is something like this:

Jan Feb Mrz Apr
1 2 3 4
offset 1
start 01.01.2006 28.01.2006 25.02.2006 25.03.2006
end 27.01.2006 24.02.2006 24.03.2006 21.04.2006
days in m 27 28 28 28

Booking
start end Total jan feb mrz apr
06.01.2006 05.04.2006 90 22 28 28 12
17.01.2006 03.03.2006 46 11 28 7

Formulas:

in D4 enter: 01.01.2006
in D5 enter; =D4+28-1-D3
in E4 enter: =D5+1 and autofill right to December
autofill D5 right to December
in D6 enter: =D5-D4+1 and autofill right to December

in C10 enter; =SUM(D10:O10)
in D10 enter:
=IF(MONTH($A10)=D$2;IF(MONTH($B10)=D$2;$B10-$A10+1;D$5-$A10+1);IF(AND(MONTH($A10)<D$2;MONTH($B10)>D$2);D$5-D$4+1;IF(MONTH($B10)=D$2;$B10-D$4+1;"")))

and autofill right to December. Depending on your config replace ";"
with ",".

That will give you the total days between the two dates in the Total
column.
For each new line in your bookings you will need to copy the formulas
from C10:O10 into the new line.

If your bookings were in a separate Excel workbook you could
incorporate them into a new workbook by means of a query. That will
update the formulas automatically for each line in the query.

Hans

Sorry if the text layout of the post is crap. Don't know how to format
properly.

 

[Ron Coderre]

Try this example:

In A1:C5 enter this table:
Period______MthBeg_______MthEnd
200512_____01-Dec-05____31-Dec-05
200601_____01-Jan-06_____27-Jan-06
200602_____28-Jan-06_____24-Feb-06
200603_____25-Feb-06_____24-Mar-06

E1: StartDate
E2: 01-Feb-06

F1: EndDate
F2: 25-Feb-06

G1: Days
G2: =+F2-E2+1

H1: Fee
H2: 1,000

I1: 200601
J1: 200602
K1: 200603

H2:
=MAX(MIN(VLOOKUP(I$1,$A$2:$E$5,3,1),$F2)-MAX(VLOOKUP(I$1,$A$2:$E$5,2,1),$E2)+1,0)*$H2/$G2

Copy H2 across through J2

These are the returned values
I1: 0
J1: 960
K1: 40
Totalling: 1,000

Is that something you can work with?

Regards,
Ron

 

[Guest]

Try this example:

In A1:C5 enter this table:
Period______MthBeg_______MthEnd
200512_____01-Dec-05____31-Dec-05
200601_____01-Jan-06_____27-Jan-06
200602_____28-Jan-06_____24-Feb-06
200603_____25-Feb-06_____24-Mar-06

E1: StartDate
E2: 01-Feb-06

F1: EndDate
F2: 25-Feb-06

G1: Days
G2: =+F2-E2+1

H1: Fee
H2: 1,000

I1: 200601
J1: 200602
K1: 200603

I2:
=MAX(MIN(VLOOKUP(I$1,$A$2:$E$5,3,1),$F2)-MAX(VLOOKUP(I$1,$A$2:$E$5,2,1),$E2)+1,0)*$H2/$G2

Copy I2 across through K2

These are the returned values
I1: 0
J1: 960
K1: 40
Totalling: 1,000

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Gazzr]

Hi all,

Thanks for the responses. I will check them out over the weekend and
let you know how I go.

Your help has been much appreciated.

Regards
Garry

 

[Gazzr]

Just wanted to let you know that the examples you provided worked fin
and the person who I was doing the work for was amazed!

Regards
Gazzr: