0

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.

  • I can't see your formula or what you've tried. As it is, the question is too broad and reads like a "do my work for me" type question :( I'm sure it's not, so if you can edit to show your efforts then we can see where it's going wrong :) – Dave Jul 12 '16 at 10:57
  • I'm confused. The examples say "16/06/2016 means revenue will start in June 2016" but the rules say "If a contract starts on or after the 16th of the month, the revenue will start from the following month." Shouldn't revenue start July 2016? – creidhne Jul 12 '16 at 21:20
0

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.

Number of months in the Revenue Phase

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.

Find revenue in 2016

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.

Using PivotTable

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.

0
  • 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

  • I believe there are problems with this solution. Example 1 has 9 months and Example 2 has 8 months in the Revenue Period. For Example 1 this solution finds 8 months (D1-C1) and for Example 2 it finds 7 months. The 2016 revenue is wrong. More seriously, if a contract begins 16/06/2016 and ends 01/12/2016, this solution says the revenue is 1200. (NB: The question says 10 and 9 months, but we're waiting for clarification. The examples say the period for 16/06/16 starts June 2016, but "on or after the 16th of the month, the revenue will start from the following month," so it should be July.) – creidhne Jul 14 '16 at 1:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.