Re: Accural Revenue - How to divide revenue into Months

From: Shane Clarke (sfclarke_at_eircom.net)
Date: 09/27/04 

Date: Mon, 27 Sep 2004 16:23:47 -0700

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" <anonymous@discussions.microsoft.com> wrote in message
news:25eb01c4a4e4$f377ccf0$a501280a@phx.gbl...
> 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
>
>
>>-----Original Message-----
>>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
>>
>>
>>
>>
>>.
>>