Gfrktyl

My company phases revenue based on slightly obscure start/end dates and I need calculate how much revenue falls into the current year, based on these dates.

If a contract starts on or after the 16th of the month, the revenue will start from the following month. If a contract start on or before the 15th of the month, the revenue will start from the current month:

For example:

Dec-15  16/11/2015  15/12/2015

Jan-16  16/12/2015  15/01/2016

Feb-16  16/01/2016  15/02/2016

If the value of this contract is £1000, my company phases the revenue equally by months.

Example 1:

Contract start= 16/06/2016 means revenue will start in June 2016

Contract end =  23/02/2017 means revenue will end in March 2017

Example 2:

Contract start= 16/06/2016 means revenue will start in June 2016

Contract end = 04/02/2017 means revenue will end in Feb 2017

In example 1, this would result in the total revenue being split into 10 months.

In example 2, this would result in the total revenue being split into 9 months.

If need a calculation that splits the total revenue based on this equal revenue phasing (months), and then calculates how much falls into 2016.

Dave simplified Mikey's question so it's not so open-ended. Let me restate it. We know when a contract begins and ends. There are rules for deciding when the Revenue Phase starts and ends. We want to find two things:

1. How many months are there in the revenue phase, and


2. How much revenue occurs in 2016.

I'm going to find the revenue that occurs in the same year as the start, whatever year that may be.

I've defined names for the following cells. Contract.value is defined as 1000.

Start  $C$3

Stop   $D$3

Months $E$3

Start and Stop are computed from the contract begin and end dates. We need the number of months from Start to Stop so we can find the "equal revenue phasing." It's really easy. Use DATEDIF or check How to calculate the number of months between two dates in Excel for an alternative method. Here's the formula for cell E3.

=DATEDIF(Start,Stop,"M")+1

The +1 makes the number of months inclusive of the first and last months. In the example, there are 9 revenue months from July 2016 to March 2017.

Here's how to find the revenue in 2016, the year that the Revenue Phase starts.

First, find the number of months in 2016. Cell C2 computes it:

=IF(YEAR(Stop)>YEAR(Start),12,MONTH(Stop))-MONTH(Start)+1

Compute the number of months times the revenue for one month in the Revenue Phase. Cell D2 does it:

=C2*Contract.value/Months

Combining C2 and D2 into a single formula shows the same result in cell E2.

=(IF(YEAR(Stop)>YEAR(Start),12,MONTH(Stop))-MONTH(Start)+1)*Contract.value/Months

There are some disadvantages to this approach. What if you want to find the revenue in 2017? The formula for 2017 follows the same pattern as C2, but it's not the same. Things get messy if the Revenue Phase spans more than two years.

The problem can be generalized by making a simplified amortization table and using PivotTable to summarize it. Start, Stop and Months are all that's needed to make a list of months and revenue. The PivotTable finds the revenue for every year in the Revenue Phase.

Row 2 is special.

Here are the formulas for A2:C2.

=Start

=YEAR(A2)

=Contract.value/Months

Use the formulas for A3:C3 to Fill Down to fill the list. Fill the number of rows to allow for the largest possible months in the Revenue Phase. It's easy to add more.

=IF(A2<Stop,DATE(YEAR(A2),MONTH(A2)+1,1),"")

=IF(A3<>"",YEAR(A3),"")

=IF(A3<>"",Contract.value/Months,"")

The "trick" is in A3. When the date in column A for the previous row is after Stop, the cell in column A is blank and every following row is blank, so there will be one row for each month in the Revenue Phase -- no more, no less. The trick is useful when you want to make a list, but you don't know the number of rows in advance.

To make the pivot table, select the entire list, including blank rows. By selecting the entire list, the pivot table will still work if even when you change the contract dates. Use the Year field for the pivot table row, and Sum of Revenue and Count of Year for the summary fields.

• Cell A1: Contract Start Date


• Cell B1: Contract End Date


• 
  

  Cell C1: Month index when contract began:

  
  
  

  =YEAR(A1-15) * 12 + MONTH(A1-15) + 1

  
  


• 
  

  Cell D1: Month index when contract ended:

  
  
  

  =YEAR(B1-15) * 12 + MONTH(B1-15) + 1

  
  


• 
  

  Cell E1: Pay per period assuming $1000 total contract

  
  
  

  =1000/(D1-C1)

  
  


• 
  

  Cell F1: Number of months in 2016

  
  
  

  =24205 - C1

  
  


• 
  

  Cell G1: Total 2016 pay

  
  
  

  =F1*E1