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:
- How many months are there in the revenue phase, and
- 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.
Stop are computed from the contract begin and end dates. We need the number of months from
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
+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:
Compute the number of months times the revenue for one month in the Revenue Phase. Cell
D2 does it:
D2 into a single formula shows the same result in cell
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.
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.
2 is special.
Here are the formulas for
Use the formulas for
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.
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.