Contract run-off analysis

When acquiring a business that operates the majority of its business under long-term contracts, one of the key considerations when assessing their forecast model should be ‘to what extent are forecast revenues contractually secured?’.

One of the best ways to understand and visualize the impact of contractual revenue is to create a bar chart which illustrates run-off. For the purposes of this piece of analysis, we assume that all contracts will all come to an end at their contract of their contractual term (i.e. we are taking a worst case view).

How to perform contract run-off analysis

First, we set up a table in which we input customer name, annual revenue for the last twelve months, and contract-end date. Alongside, add a list of months included within the forecast period. In this example, I assume that revenue is the annual revenue for the year ending 31 December 2017, whilst Jan-18 is the start of the forecast period.

Once these dates are input, you now need to create your formula to calculate whether or not each customer remains under contract at each month-end date (i.e. Jan-18, Feb-18, Mar-18 and Apr-18).

The formula used is a nested if formula.  Whilst this looks complicated at first glance, see the logic employed below.

=IF(Contract end date<Month date,0,(IF(Contract end date>Month date,(Feb-18 – Jan-18),((Contract end date – Jan-18))))))/((Feb-18 – Jan-18))*Annual revenue

The formula is saying:

  • If the contract has expired; present zero as a revenue total in the month. e. where the contract end date is prior to the start of the month.  E.g. in April-18, the contract with Customer A had expired prior to 1st April, therefore revenue is shown as 0.
  • If a contract expires after the start of the next month, present full annual revenue in the month. E.g. the contract with customer B runs until 2020, and thus full annual revenue is shown in each month.
  • If a contract expires in the middle of the month, calculate the number of days which revenue is contracted in that month, and only present that proportion of annual revenue. g. in Mar-18, the contract with customer A is only active for 23 days out of 31. Therefore 23/31 * 100 = 74.

Note that this analysis is illustrative only. The annual revenue total is presented in each month as I chose to present the run-off of annual contracted revenue. If you would like to show monthly revenue, simply add ‘/12’ to the end of the formula listed.

Ultimate output:

Download Excel File

 Example of contract run-off analysis

Comments (No)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.