E
ExcelMonkey
Sorry, I posted this earlier but was not clear and the text showed up funny.
I have a an annual forecast with year end dates. I have a revenue stream
each year let call it $100. I want to be able to apply a % scaler to this
annual revenue to reflect the partial periods on the front end and back end
of the forecast. That is, the term is 3 years, but the start date of the
revenue stream may occur later in the year such that it will not really be
$100. Assume my year end dates are Oct 31st and start in 2007(i.e.
31-Oct-2007 31-Oct-2008, 31-Oct-2009, 31-Oct-2010, 31-Oct-2011) and are in
cells B1:F1 . My start date attached to my revenue stream is 1-Jan-2008. I
need logic which tells me how much to scale the revenue in each annual
period. Thus the first year will have 0 months and $0 as it is before the
start date, the second year will only have 10 months out of 12 and $83, the
third will be 12 months out of 12 and the full $100, the fourth year will
have 2 months out of 12 and $17, and the last year will have 0 months and $0.
The logic has to be smart enough to know when to start, how much to scale in
each annual period, and when to end. See Below
1 2 3
4 5
% in Period 0/12 10/12 12/12 2/12
0/12
revenue $0 $83 $100 $17
$0
Thanks in Advance.
EM
I have a an annual forecast with year end dates. I have a revenue stream
each year let call it $100. I want to be able to apply a % scaler to this
annual revenue to reflect the partial periods on the front end and back end
of the forecast. That is, the term is 3 years, but the start date of the
revenue stream may occur later in the year such that it will not really be
$100. Assume my year end dates are Oct 31st and start in 2007(i.e.
31-Oct-2007 31-Oct-2008, 31-Oct-2009, 31-Oct-2010, 31-Oct-2011) and are in
cells B1:F1 . My start date attached to my revenue stream is 1-Jan-2008. I
need logic which tells me how much to scale the revenue in each annual
period. Thus the first year will have 0 months and $0 as it is before the
start date, the second year will only have 10 months out of 12 and $83, the
third will be 12 months out of 12 and the full $100, the fourth year will
have 2 months out of 12 and $17, and the last year will have 0 months and $0.
The logic has to be smart enough to know when to start, how much to scale in
each annual period, and when to end. See Below
1 2 3
4 5
% in Period 0/12 10/12 12/12 2/12
0/12
revenue $0 $83 $100 $17
$0
Thanks in Advance.
EM