Accural Revenue - How to divide revenue into Months

[Shane Clarke]

Hi all.



Hope someone can help.



I have a project start date, a project end date and an amount. The start to
end may span 5 days or 5 months.



I need a formula that will take the length of the project, divide the total
revenue amount by the number of days in the project and automatically insert
the revenue amount in a particular month in the right month column.



For example if the project started in Jan 31st, lasted for 10 days, and was
$1,000, then $100 would be booked in January and $900 in February.



It would look like this:



Date Registered - Date Due - Amount - Days - Per Day - Jan - Feb - Mar
1/2/2004 - 3/4/2004 - $1,458 - 62 - $23.52 - $705.48 - $658.45 - $94.06
2/20/2004 - 2/23/2004 - $3,873 - 3 - $1,291.00 - $0 - $3,783.00 - $0



I need a repeating column formula that I can use for every month. Should I
be pivoting? Is there a simple answer to this?



Thanks a million in advance.

Shane

 

[Michael]

Hi Shane
Depends on whether you want to include every day or just
work days !!
For every day try:
Start date in A1
Finish date in B1
Revenue in C1
In D1 use formula
=C1/(B1-A1)

If you only want work days using the above, try:

=C1/(NETWORKDAYS(A1,B1))


HTH
Michael

 

[Shane Clarke]

Hi Michael.

Thanks for the reply but what I need is little more complicated.

The formula needs to look up when the project starts, when it finishes, and
work out how much of the revenue is earned in a particular month. It needs
to work on calander days and assumes that a equal amount of revenue is
earned for each day the project is in progress.

There would be 12 columns January - December, and one row per project with
the total revenue for each project divided out into each month depending on
when it started, when it ended.

The answer below just gives the amount earned per day. It needs to be taken
one step further and work out the number of days the project has been (for
example) in process for January, the number of days in Feburary, and
multiply this number of days by the amount per day. It then needs to put the
amount in the correct column.

So for the example below the result is (transposed)

Date Registered - 1/2/2004
Date Due - 3/4/2004
Amount - $1,458
Total days in progress- 62
Per Day - $23.52
Jan - $705.48
Feb - $658.45
Mar - $94.06

Thanks,
Shane

 

[Michael]

Shane
Sorry, didn't see the rest of the post.
At this stage, between work tasks, I am having a look at
the problem.
Other posters will no doubt come up with an answer before
me !!
Regards
Michael

 

[Michael]

Hi Shane
Sorry about the delay, but I have finally solved your
problem.
As the answer needs a bit of explaining Send me an E-Mail
at the above with NOSP removed and I will send you a
sample of the worksheet

Regards
Michael

 

[duane]

start date in a5
end date in b5
amount in c5
=b5-a5 in d5 #days
=c5/d5 in e5 $/days
months listed in f4 thru say i4 (as dates 1/1, 2/1, 3/1, 4/1) for as
far as you need
- you need 1 month past max end date

in cell f5 and copied over and down for more rows

=IF(OR($A5>G$4,$B5<F$4),0,MIN(G$4-$A5,$B5-$A5,$B5-F$4,G$4-F$4))*$E5

 

[Michael]

Hi Duane
Nice to see who has the most expertise....
My formula was a nested IF and takes about three lines of
formula.
I guess the more I learn the less I know !!
I shall file your example for my future learning
Regards
Michael.

 

[Jerry Pacemaker]

Thank you Michael and Duane for your replies - I greatly appreciate it!

 

[Myrna Larson]

Note that Duane's formula is shorter in part because he has set up two
additional columns (D and E) to calculate intermediate results, and he
requires an extra column for "1 month past max date" in the first row.