Client Fee Revenue Forecast

wdavis5917

New Member
Joined
Jan 21, 2020
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
New to excel but slowly and surely making progress. One of our divisions provides services to clients at a fixed fee. We projected that we would begin January 2020 with 25 clients, averaging $5k per month, per client, and projected we'd add 3 clients per month, each month whom would also average $5k each per month thereafter. I want to create a forecast that reflects the compounding effects of not adding 3 clients per month, or even losing a client, based on the month. I would then like to create a line(s) that shows what we have to do to correct it. For example, if we didn't add 3 clients in January, the compounding impact by the end of 2020 would be $180,000. That can be correct in three ways (1) add the clients as soon as possible, (2) Earn higher fees per client for the current client base, or (3) a combination of both these things.

I have an image of my sheet attached, and you'll see I have a table that provides the assumptions for the 2020 budget. This includes beginning client count, projected client adds, @$5,000 per client, to arrive at ending projected fee revenue. Beneath it, we've completed January and February, and March I have input the secured clients we have at the beginning of the month. These are all in pink, but the area I have shaded grey is meant to indicate those months (Feb and March) will have additional revenue added to them for month-revenue share. Its likely around $40k at a client base of around 30 but my plan was to use the average fee revenue from January as my basis for the projection forward (April-Dec). Then, as each month officially closes, I'll use the most current months average fee revenue to project forward. You'll see I have a running surplus/deficit, which is just secured revenue (Jan-Mar), and projecting out through Dec the revised numbers that adds 3 clients to the ending March client count.

From there, I'm basically saying a have a $287k deficit that needs to be absorbed over the next 9 months assuming an average monthly client fee of $5,113. My formula is =FLOOR((-$B$17/E14)/$C$17,1) which gives me clients rounded to 6 where I multiply by the $5,206. This works to an extent but there is still a discrepancy no matter how I try to adjust the formula to calculate the clients needed. I wish it were a bit more dynamic in that I could play with the numbers by saying in addition to our regular 3 added each month, 2 were added in April, not in May-Jun, but 5 were added in Jul, and the average fee revenue is actually $5,700 per client per month, etc.

Is this possible?
 

Attachments

  • Dummy Fee Projection.PNG
    Dummy Fee Projection.PNG
    52.2 KB · Views: 9

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Watch MrExcel Video

Forum statistics

Threads
1,132,701
Messages
5,654,818
Members
418,155
Latest member
demasisi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top