Excel: Forecasting and Prorating Revenue By Month (Based on Start and End Dates)

[scubawildwillie]

Hello. Hoping you can help with this one. I'd like a more automated way to populate my revenue forecast based on the following fields:

1. Amount
2. Fee Type
3. Start Date
4. End Date

Re' Fee Type: One Time Fees are billed on the start date and include the full amount, regardless of the day it starts. Monthly Fees start on the start date and continue monthly until the end date. If the start date lands in the middle of a month the amount is prorated based on the number of remaining days in the month.

Below is sample of what it looks like in Excel without any formulas.



[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD]Service[/TD]
[TD]Amount[/TD]
[TD]Fee Type[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]March[/TD]
[TD]April[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ServiceA[/TD]
[TD]$10,000[/TD]
[TD]One Time[/TD]
[TD]1/15/15[/TD]
[TD]1/31/15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Service B[/TD]
[TD]$4,900[/TD]
[TD]Monthly[/TD]
[TD]3/15/15[/TD]
[TD]12/31/25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]










Thank you in advance for any suggestions you may have.

Will

 

[sergioMabres]

Hi
I think you need the formula:

=IF($D2="One time",IF(AND($E2>=G$1,$E2<$H1),$C2,""),
IF($D2="Monthly",IF(AND($E2<h$1,$f2>H$1),
IF(OR($E2=G$1,$E2<g$1),$c2,
ROUND($C2*((H$1-$E2)/(H$1-G$1)),2)),""),""))<h$1,$f2><g$1),$c2,round($c2*((h$1-$e2) (h$1-g$1)),2)),""),""))
<h$1,$f2><g$1),$c2, round($c2*((h$1-$e2)="" (h$1-g$1)),2)),""),""))
<h$1,$f2><g$1),$c2,round($c2*((h$1-$e2) (h$1-g$1)),2)),""),""))


in G2
You also need first day of every month in G1 to XX1
Then copy the formula down and to the right all you need

There is a copy of a test file at https://onedrive.live.com/redir?res...700&authkey=!AAMFX1Fu88y9xNs&ithint=file,xlsx

Cheers
Sergio</g$1),$c2,round($c2*((h$1-$e2)></h$1,$f2></g$1),$c2,></h$1,$f2></g$1),$c2,round($c2*((h$1-$e2)></h$1,$f2></g$1),$c2,
</h$1,$f2>

 

[scubawildwillie]

Awesome Sergio! Thank you sir!

 

[sergioMabres]

Always glad to help

 

[scubawildwillie]

Sergio,

Hello. I absolutely love your formula and almost have it working in my own spreadsheet. Do you know what might be wrong with the formula I am using in cell H1 on the following link?

https://www.dropbox.com/s/whf59bylk9jdaif/TECas Revenue Forecast 2016.xlsx?dl=0

Thank you again for all your help Sergio!

Will Turner

 

[sergioMabres]

My fault

G5 shoul be

=IF($D5="One time",IF(AND($E5>=G$4,$E5<H$4),$C5,""),
IF($D5="Monthly",IF(AND($E5<H$4,$F5>H$4),
IF(OR($E5=G$4,$E5<G$4),$C5,
ROUND($C5*((H$4-$E5)/(H$4-G$4)),2)),""),""))<h$4),$c5,""),
<h$4,$f5><g$4),$c5,

<h$4),$c5,""),
<h$4,$f5><g$4),$c5,round($c5*((h$4-$e5) (h$4-g$4)),2)),""),""))
<h$4);$c5;"");
<h$4;$f5><g$4);$c5;redondear($c5*((h$4-$e5) (h$4-g$4));2));"");""))
<h$4);$c5;"");
<h$4;$f5><g$4);$c5;redondear($c5*((h$4-$e5) (h$4-g$4));2));"");""))[="" code]
<h$4);$c5;"");si($d5="monthly";si(y($e5<h$4;$f5><g$4);$c5;redondear($c5*((h$4-$e5) (h$4-g$4));2));"");""))[="" code]<h$4);$c5;"");si($d5="monthly" ;si(y($e5<h$4;$f5=""><g$4);$c5;redondear($c5*((h$4-$e5) (h$4-g$4));2));"");""))
I anchor $H4 and it was H$4 sorry

Here is a copy of your file with the corrected formula
https://onedrive.live.com/redir?res...762&authkey=!AEEukdhESGzdoVc&ithint=file,xlsx

Cheers
Sergio
</g$4);$c5;redondear($c5*((h$4-$e5)></g$4);$c5;redondear($c5*((h$4-$e5)></h$4);$c5;"");si($d5="monthly";si(y($e5<h$4;$f5></g$4);$c5;redondear($c5*((h$4-$e5)></h$4;$f5></h$4);$c5;"");
</g$4);$c5;redondear($c5*((h$4-$e5)></h$4;$f5></h$4);$c5;"");
</g$4),$c5,round($c5*((h$4-$e5)></h$4,$f5></h$4),$c5,""),
</g$4),$c5,
</h$4,$f5></h$4),$c5,""),

 

[sergioMabres]

My fault

G5 should be
=IF($D5="One time",IF(AND($E5 >= G$4,$E5 < H$4),$C5,""),
IF($D5="Monthly",IF(AND($E5 < H$4,$F5 > H$4),
IF(OR($E5=G$4,$E5 < G$4),$C5,
ROUND($C5*((H$4-$E5)/(H$4-G$4)),2)),""),""))
<h$4),$c5,""),
<h$4,$f5><g$4),$c5,
<h$4),$c5,""),
<h$4,$f5><g$4),$c5,
<h$4),$c5,""),
<h$4,$f5><g$4),$c5,

I anchor $H4 and it was H$4 sorry

Here is a copy of your file with the corrected formula
https://onedrive.live.com/redir?resid=52B7AEC35713C28B!1762&authkey=!AEEukdhESGzdoVc&ithint=file%2cxlsx

Cheers
Sergio</g$4),$c5,
</h$4,$f5></h$4),$c5,""),
</g$4),$c5,
</h$4,$f5></h$4),$c5,""),
</g$4),$c5,
</h$4,$f5></h$4),$c5,""),

 

[scubawildwillie]

Thank you Sergio. Just when I thought I was getting pretty good at Excel you come up with a formula like this one, I would never have been able to come up with on my own. Thank you again for your help Sergio! You are a lifesaver!

Will

 

[sergioMabres]

glad to help will