Buy Revenue Modeling Exercise Assignment

Posted: September 16th, 2022

Buy Revenue Modeling Exercise Assignment
Matrix’s distribution model is built upon developing relationships with Agencies / Brokers located across the US and Canada regions (B2B). Our Sales organization – segmented by revenue bands into small, medium, and large – onboard Brokers within Agencies onto our Sales platform in order to generate quote submissions. They then work with internal stakeholders across Security, Risk Engineering and Customer Success teams to convert these quote submissions into Policyholders or Binds. The rate of conversion of quotes to Policyholders (Binds) is known as a Quote-to-Bind (QtB) ratio. The quantity of Policyholders (binds) and an average price per policy (average premium) generates total Gross Written Premium.
Exercise
Within Excel or Google Sheet please model out the following, documenting any assumptions in the process:
1. Build out a 12-month New Business Gross Written Premium forecast utilizing the drivers detailed in the overview section (Brokers, Submission volume, Quote to Bind ratio, and Avg Premium), split into the following Revenue Bands:
· Small Revenue
· Medium Revenue
· Large Revenue
Buy Revenue Modeling Exercise Assignment
Assume that the Broker base across all three Revenue Bands is the same. As a starting point, assume that we begin M1 with 1,000 Brokers and add 100 brokers per month who quote. Additionally, account for 0.5% attrition of our broker base on a monthly basis.
2. Most policies have a life cycle of 1 year after which they become part of our Renewal base. For part 2 of the exercise please model out the Renewal Gross Written Premium by Revenue Band we can expect to generate from the policies bound in part 1 utilizing the following assumptions
a. We renew 85% of the policies on a monthly basis
b. We increase premium (rate) on the renewals such the Gross Written Premium remains unchanged year-over-year; put differently we renew policies at an 85% rate and GWP at a 100% rate
Note: Matrix is an MGA which means its revenue is a percentage of GWP. To avoid unnecessary complexity this exercise doesn’t include the GWP to revenue recognition aspect of the Company’s model.
3. Create a 24-month P