Formula for contract values based on start date pro rata

[natasha_tracy]

Hello,

This forum is great.

I have a complicated question (or one that confuses me anyway) -

I have been set a task to calculate revenue for different quarters for different business won. So have an annual value and I need to calculate how much of that value falls in each quarter (1, 2, 3, 4), but I also need to take into consideration the start date of the contract (dd/mm/yyyy). So if the contract starts on 27th november 2007, then there's no data in Q1, Q2 or Q3 and I need to pro rata the value for Q4 - so only from the 24th Nov. It would be simple if all the contracts started on 1st Jan, because I would just divide them by 4, but they don't.

I don't even know where to start and I have rows and rows and don't want to do individual formulas for each contract. Can anyone help? I hope this makes sense.

Thanks in advance - Natasha

 

[natasha_tracy]

I've been thinking about it and I imagine I will need an IF statment - but can't work out how to write it... in English though, I guess I need (for Q1)

IF Contract Start Date is greater than 31/03/2007 then $0 Else, calculate the number of days from contract start date to 31/12/07 (31st Dec) and divide the Annual Value by the number of days, then multiply that value by the number of days from Start Date to 31/03/2007 (31 March).

Does this look right? And if so, how do I write it in'Excel speak' (forumla) -or do I need to add in an extra column to calculate the days?

 

[eliW]

Hi and welcome to the board!

How is your data arranged ? and what do you want as a result ?

Say for business A annual revenue was 1000$ start day 1 Jan = then what?
do you want Q1=250 Q2=250 Q3=250 Q4=250 ???
Business B also 1000$ but started in 1 Jul
do yo want Q1 =0 Q2=0 Q3=500 Q4=500 ???
and so on?

Pls give your concrete example.

BTW the best way to use quarters is by Pivot Table

 

[natasha_tracy]

Hi and welcome to the board!

How is your data arranged ? and what do you want as a result ?

Say for business A annual revenue was 1000$ start day 1 Jan = then what?
do you want Q1=250 Q2=250 Q3=250 Q4=250 ???
Business B also 1000$ but started in 1 Jul
do yo want Q1 =0 Q2=0 Q3=500 Q4=500 ???
and so on?

Pls give your concrete example.

BTW the best way to use quarters is by Pivot Table

Sorry - I thought I was being obsure - couldn't work out how to explain it - yes that's exactly what I want.

 

[eliW]

Hi and sorry for the delay.

Look here for a start!

http://www.mrexcel.com/board2/viewtopic.php?t=113115&highlight=