Every Month Revenue
 

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.

Every Month Revenue
 

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).
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Qazi Ahmad" wrote:
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.

Every Month Revenue
 

Put instead in F2:
=IF(AND(DATE(YEAR(F$1),MONTH(F$1),1)=DATE(YEAR($B 2),MONTH($B2),1),DATE(YEAR(F$1),MONTH(F$1),1)<=DAT E(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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
--- Ahamd N wrote:
Dear Max,
Thank you to help me it highly appreciated,
I have one problem with this file.
If one person came in May 3rd (for one month his membership will expire in
Jun 3rd.) when I entered revenue it goes to month of Jun (nest month) not
current month it should go to current month, in month of May not next month..
Current month must get it revenue. Even member come any date of the month
first to last day of the month. Please correct the formula which show revenue in
current month, and if member ship more then one month the revenue should
go to coming months.

Every Month Revenue
 

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


"Max" wrote:

Put instead in F2:
=IF(AND(DATE(YEAR(F$1),MONTH(F$1),1)=DATE(YEAR($B 2),MONTH($B2),1),DATE(YEAR(F$1),MONTH(F$1),1)<=DAT E(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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
--- Ahamd N wrote:
Dear Max,
Thank you to help me it highly appreciated,
I have one problem with this file.
If one person came in May 3rd (for one month his membership will expire in
Jun 3rd.) when I entered revenue it goes to month of Jun (nest month) not
current month it should go to current month, in month of May not next month..
Current month must get it revenue. Even member come any date of the month
first to last day of the month. Please correct the formula which show revenue in
current month, and if member ship more then one month the revenue should
go to coming months.

Every Month Revenue
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Qazi Ahmad" wrote:
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

Every Month Revenue
 

Dear Max,

Thank you very very much it solve my all problem,

Ahmad

"Max" wrote:

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Qazi Ahmad" wrote:
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

Every Month Revenue
 

Good to hear that. You're welcome ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Qazi Ahmad" wrote in message
...
Dear Max,

Thank you very very much it solve my all problem,

Ahmad