Re: Accural Revenue - How to divide revenue into Months

From: Michael (anonymous_at_discussions.microsoft.com)
Date: 09/28/04 

Date: Mon, 27 Sep 2004 19:10:06 -0700

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
>-----Original Message-----
>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
>>>
>>>
>>>
>>>
>>>.
>>>
>
>
>.
>