Tracking Monthly Revenue when a job rolls over into next month

[phimutau]

I have a problem. I am trying to track Monthly Revenue that is month specific. For example, If I have a pump out on a job that started drawing income on 9-5-10 and does not end until 10-20-10, how can I track month specific revenue for that pump. I need 9-5 to 9-30 revenue to stay in September and 10-1 to 10-20 to go to October.

Is there a formula I can use if I have the start date and end date as columns?

Any and all help would be greatly appreciated.

I am using pivot tables with specific headers to track revenue right now. However, my monthly totals are skewed based on my lack of date specific info. I just have the revenue from job mentioned above going back into Septembers revenue and that is not how my company does it.

I know this can be done in a complex way that is really time consuming to break down, I am searching for a simple formula if one exists.

Thanks

[DonkeyOte]

In theory it's a question of apportionment, ie:

Monthly Revenue = Total Contract Revenue * (Days Applied in Month / Total Contract Days)

the Total Contract Days is simply the difference between the Start & End dates of the contract (plus 1 if inclusive)

the Days Applied in Month is equivalent of

MAX(0, 1 + MIN(end of contract, end of month) - MAX(start of contract, beginning of month))

If you furnish us with more info. - or better yet a basic sample file illustrating how you're storing your values presently we might be able to help further.

[phimutau]

Here is an example of what I am working with. I have another file that I am using pivot tables with. This is just a test sheet to try to figure out my dilemma. Thanks for your help.

[DonkeyOte]

I suspect the layout of your file is going to cause you a few headaches (ie you have multiple sheets per file - one per month) but in the most generic of terms using your file:

Z2:
=EOMONTH(MIN(E:E),-1)+1

AA2:
=EDATE(Z2,1)
copied to AB2

Z3:
=$X3*MAX(0,(1+MIN(EOMONTH(Z$2,0),$G3)-MAX(Z$2,$E3))/(1+$G3-$E3))
copied across matrix Z3:AB5

If as suspected you're looking to work across multiple sheets the date headers (Z2 onwards) should really be identical (ie Z2 may be Jan 2010 for ex.).
In addition you should list as many months going forward as you require (eg to Dec 2011)

Identical matrices will I suspect make life a little easier for you in the long term.

[phimutau]

The attached file is the actual file I am using. I am currently tracking revenue based off field ticket date (column C) on my data source tab. However, my ticket program (Wolfepak) divides the revenue based off of the month the income is coming in, as described in my original post. I know this needs a new column in the data source for end date of job. I hope this helps. If you have any more questions that I can answer to help please let me know. I am eager to assist.

This file is the one that I need the help with, the one from above is a coworkers from a different division. We are both working on the same problem with different data. I use the pivot tables and color the months in my data source. The pivot table is based off the FT Date. This is a basic set up because it is all I know. If there is something I am missing please let me know and I will do my best to fix it. Thanks for all your help.

[DonkeyOte]

I know this needs a new column in the data source for end date of job.

Correct - without it I am afraid I/we can't really provide any concrete examples.
On that basis I can only repeat the earlier advice, namely:

-- store month values for which revenue is to be calculated in header row K1 onwards (determine 1st month based on MIN of Col C)

-- calculate apportioned revenue as previously described in cells below the date headers

-- use the resulting matrix as source for analysis etc...

the above concepts are demonstrated via the prior post (based on the initial upload)

On a final note - if the file you attached contains real names etc I would suggest you remove it and replace with a dummy version for sake of confidentiality etc...