Every Month Revenue

[Guest]

I have a health club. I need a Excel sheet that give me monthly base
membership Revenue and divide it automatically on monthly base and show the
divided Amount result in each month column.
Example: 2 member for 3 month, they came in March and their membership will
end in May. So membership fee divide in automatically in three month and
result show in colum of March, April and May. According to total month of
membership.

 

[Guest]

One possible set-up is illustrated in this sample construct:
http://www.savefile.com/files/600477
Club Membership Fee Apportionments.xls

Inputs: Startdates, Enddates, Fee paid in cols B to D, from row2 down
Startdates & Enddates are real dates

In F1 across are listed the "1st of month" real dates (formatted to show as
mmm-yy) eg; Jan-07, Feb-07, etc

Placed in F2:
=IF(AND(F$1>=$B2,F$1<=$C2),$D2/(DATEDIF($B2,$C2,"m")+1),"")
Copy F2 across and fill down to populate the table.

The fees paid will be apportioned equally for each member across the months
concerned. Membership periods which straddle across to the next year pose no
issue, these are handled correctly (see Mem4 and Mem5 lines as examples).

 

[Guest]

Put instead in F2:
=IF(AND(DATE(YEAR(F$1),MONTH(F$1),1)>=DATE(YEAR($B2),MONTH($B2),1),DATE(YEAR(F$1),MONTH(F$1),1)<=DATE(YEAR($C2),MONTH($C2),1)),$D2/(DATEDIF($B2,$C2,"m")+1),"")
Copy F2 across and fill down to populate the table

Pl keep discussions within the newgroup for the benefit of all.

 

[Guest]

Dear Max
Thank you, great help and highly appreciated.
It really solve my problem. I need one more help in this excel sheet. I want
to insert some empy rows and colum in begnning. But when I insert rows colum,
the formula be come wrong “#Value!â€. Please tell me how I insert rows and
colum

 

[Guest]

Don't know what happened over there, but here's a revised set-up for your
easy ref with some additional row/cols inserted to provide immediate
"flexibility" for use as a template:

http://www.savefile.com/files/612401
Club_Membership_Fee_Apportionments_v2.xls

You should be able to insert rows/cols as per normal here w/o impacting the
formulas' functionalities, as it does not contain any row/col sensitive
functions within, such as ROW(), COLUMN(). The cell refs will adapt auto. Be
careful, though with row/col deletions. Deletions might mess up formulas.
Avoid deletions.

 

[Guest]

Dear Max,

Thank you very very much it solve my all problem,

Ahmad

 

[Max]

Good to hear that. You're welcome ..