How to forecast professional services revenue based on headcount, utilization, and a billable rate
The utilization of the delivery team is a common methodology that can be used to forecast professional services revenue. In this example, we will follow the steps below to calculate professional services revenue using the # of Billable Headcount, Utilization %, and a Billable rate for both Onshore & Offshore resources.
Step 1: Define Assumptions
Go to Settings ⚙️ > Assumptions > Manage Tables > Select + Add Assumption Table and name it "Delivery Assumptions". Then select + Add Assumption to define Assumptions within the Table for each variable you'd like to change in the model. This typically includes the Billable Rate(s), Utilization %s, and Cost Assumptions. When finished, it should look similar to the one below.
Step 2: Define Custom Table
Go to Setup⚙️ > Custom Tables > Manage Tables > Select the "+" icon to add a new Custom Table and name it "Delivery Bookings and Revenue". Then select + Add Line to create the Sections and corresponding New Lines as shown below.
Step 3: Populate Assumptions
Go to Plan > Assumptions > All Assumptions > populate the Delivery Assumptions and Save.
Step 4: Add Inputs & Drivers to Custom Table
Go to Plan > Custom Tables > Select the Delivery Bookings and Revenue Custom Table and input data and/or drivers as described below by Section.
Revenue & Bookings Section
In this section, we are forecasting Bookings and calculating Revenue Recognized keeping the split into Onshore and Offshore Revenue.
- Bookings: Input manually (or add a driver if desired)
- Onshore Revenue: Add a Custom Driver to multiply Onshore Billable Hours by the Onshore Billable Rate Assumption
- Offshore Revenue: Add a Custom Driver to multiply Offshore Billable Hours by the Offshore Billable Rate Assumption
Cost Section
In this section, we are calculating Onshore and Offshore Costs based on Billable Hours and Costs per Hour.
- Onshore Cost: Add a Custom Driver to multiply Onshore Billable Hours by the Onshore Cost per Hour Custom Line
- Offshore Cost: Add a Custom Driver to multiply Offshore Billable Hours by the Offshore Cost Per Hour Assumption
Profit Margins and Profit Margins % Section
In the Profit Margins section, we are calculating both Onshore and Offshore Margins as follows:
- Onshore Profit Margin: Add a Global Driver as a difference between Onshore Revenue and Onshore Cost
- Offshore Profit Margin: Add a Global Driver as a difference between Offshore Revenue and Offshore Cost
In the Profit Margins % section, we are calculating both Onshore and Offshore Margins in percentage terms based on the below formulas:
- Onshore Profit Margin: Add a Global Driver to divide Onshore Profit Margin by Onshore Revenue
- Offshore Profit Margin: Add a Global Driver to divide Offshore Profit Margin by Offshore Revenue
- Total Profit Margin: Add a Global Driver to divide Profit Margins by Revenue Recognized
Available Hours Section
In this section, we are including Working Days and Working Hours.
- Working Days: Input manually (typically calendar days less weekends & holidays)
- Working Hours: Add a Custom Driver to multiply Working Days by Constant 8
Billable Hours Section
In this section, we calculate Onshore and Offshore Billable Hours.
- Offshore Billable Hours: Input manually (or add a plan driver if desired)
- Onshore Billable Hours: is the sum of 3 below Billable Hours types:
-
- Jr Consultant Billable Hours: Add a Custom Driver to multiply Utilized Jr Consultants by Working Hours Custom Line
-
- Sr Consultant Billable Hours: Add a Custom Driver to multiply Utilized Sr Consultants by Working Hours Custom Line
-
- PM Billable Hours: Add a Custom Driver to multiply Utilized PMs by Working Hours Custom Line
Hours Backlog Section
In Hours Backlog Section we are calculating Hours Backlog at the end of each month.
- Start with the Beginning of Period and add 2 Custom Drivers:
- Last Month: Hours Backlog from Last Month
-
- Month 1 Value:
- If populated in Actuals, there is no need to input a Month 1 Value as the above driver will simply pull forward prior month
- If Actual Backlog is not populated, then the beginning balance should be input in month 1 either via a plan driver or manual input (in this example 1500 was input as the beginning balance)
- Month 1 Value:
- + New Hours Sold: Add Custom Driver to divide Bookings by Average Bill Rate
- - Hours Billed: Add Custom Driver to multiply Billable Hours by Constant -1
Onshore Cost Supporting Calculations Section
This section contains all of the supporting calculations required for the sections described above.
- Utilized Onshore Resources is the sum of 3 below Custom lines:
- Utilized Jr Consultants: Add a Custom Driver to multiply Headcount Cumulative # for Associate Consultant by Associate Utilization % Assumption
-
- Utilized Sr Consultants: Add a Custom Driver to multiply Headcount Cumulative # for Senior Consultant by Associate Utilization % Assumption
-
- Utilized PMs: Add a Custom Driver to multiply Headcount Cumulative # for Project Manager by Associate Utilization % Assumption
- Total Onshore Hours Available: Add Custom Driver to multiply Working Hours by Headcount Cumulative # for the appropriate Department and Roles that are part of the Delivery team (in this example we are assuming all departments & roles are part of the delivery team)
- Total Billable Onshore Cost: All applicable costs for Billable Onshore resources should be added to this calculation. In this example, we are including salaries plus an associated burden rate as shown below:
- Burden: Multiplies Salaries $ for the delivery team by the Onshore Burden Rate Assumption
-
- Salaries: Reference all Salaries for the delivery team and multiply by Constant 1
- Onshore Cost per Hour: Add a Custom Driver to divide the Total Billable Onshore Cost by the Total Onshore Hours Available
- Average Bill Rate: Add a Custom Driver to divide Revenue Recognized by Billable Hour
