part month calculation

[slamanager]

Hi,

Seems im really behind the eight ball this month, i thought i had my maths wizard hat on but it got knocked off, I voluntered to try and fix some sales spreadsheet now it belongs to me, go figure.

They need to calc total contract values per month including if a contract starts part way through a month.

example
A1 startday 1/1/2011
B1 term of contract 12 months
C1 Calculated end date 1/1/2012
D1 Contract value 12,000

so the answer would be 1,000 per month

but......

A1 startday 15/1/2011
B1 term of contract 3 months
C1 Calculated end date 15/4/2011
D1 Contract value 12,000

So thats 4000 per month but its not there are 16 days in JAN, full month of FEB, Full Month of MAR, and the 15 days of APR how can i show in a monthly matrix these part month values. Would a SUMPRODUCT type formula be the way. Or should i start counting days and working it out based on the days ........

I did go through the fincial formulas but there didnt seem to one that dealt with this type of combination.

 

[Roli001]

What should the answer look like for the second scenario?

 

[slamanager]

so the matrix should show

Jan-11 = 2000
Feb-11 = 4000
Mar-11 = 4000
Apr-11 = 2000

its the calculations about the first and last month if the dates full part way through the period. Because its revenue i have to show the values per the month.

 

[Roli001]

A2: 15/05/2011
B2: 3
C2: =IF(MONTH(A2)+B2<=12,DATE(YEAR(A2),MONTH(A2)+B2,DAY(A2)),DATE(YEAR(A2)+1,MONTH(A2)+B2-12,DAY(A2)))
D2: 12000
E2: =((DAY(DATE(YEAR(A2),MONTH(A2)+1,1)-1)-DAY(A2))/(C2-A2))*F2
F2: =D2/B2
G2: =(DAY(C2)/(C2-A2))*F2

C2 - calculates the end date based on start date and duration
E2 - calculates contract value for first month based on start date (Jan)
F2 - calculates average contract value for "in between months" (Feb,Mar)
G2 - calculates contract value for last month based on end date.


Hope this helps.

 

[Roli001]

I made an error in the above post.

This is the correct formula:

E2: =((DAY(DATE(YEAR(A2),MONTH(A2)+1,1)-1)-DAY(A2))/(C2-A2))*D2
G2: =((DAY(C2)-1)/(C2-A2))*D2

 

[slamanager]

Hi,

Got your amendemnet but its still missing the mark !

even though this is a 3 month term it covers 93 actual days

A2: 15/5/2011
May (remaining)is 17 days $2193.548
June has 30 days $3870.968
July has 31 $4000
August has 15 $1935.484

93* 129.0323 = 12000

i think its only one day out somewhere.

 

[sanrv1f]

Try this,

Excel Workbook
	A	B	C	D	E	F	G
1	Start	End	Value
2	15-Jan	15-Apr	12000			Jan-11	2242
3						Feb-11	3692
4						Mar-11	4088
5						Apr-11	1978
Sheet1

Excel 2003

Cell Formulas
Range	Formula
G2	=$C$2*(MIN(EOMONTH(F2,0),$B$2)-MAX(F2,$A$2)+1)/($B$2-$A$2+1)

You need to include the Analysis tool pak addin for EOMONTH function to work

 

[slamanager]

But where is the term part ?

 

[Roli001]

Hi,

Got your amendemnet but its still missing the mark !

even though this is a 3 month term it covers 93 actual days

A2: 15/5/2011
May (remaining)is 17 days $2193.548
June has 30 days $3870.968
July has 31 $4000
August has 15 $1935.484

93* 129.0323 = 12000

i think its only one day out somewhere.

So in other words are you looking for a formula that would look at monthly value based on the number of days in that month?

i.e.

((12000)/93) *17 for May
((12000)/93)*30) for June
((12000)/93)*31) for July
((12000)/93)*15) for August

Is that correct?

What about stat holidays and weekends?

One more point. You are saying that May has 17 days left when in reality it has 16 days left (31-15). Is this correct?

 

[slamanager]

ok,

i have 50,000 rows of data spanning 2000 to 2025 each row has a start date possibly at the start of a month but can be anywhere in the month im trying to pivot the data or summarise it with formulas but need to make sure those part months of the contract value are allocated to the corrcet month both at the start and end of each contract.

if you use your fingers there are only 17 days from the 15th to the 31st