How American Veterinary Group Streamlined Workforce Planning with Solver

0:09 [Music]

0:17 I'm Fred Whiting, the CFO of American Veterinary Group. I've been with Solver for about a year, a little bit over a year and a half now.

0:25 We did do our first full annual budget with workforce planning at the end of last year, and since then have been operating off of it for all of 2021, and the results have been great.

0:35 We're basically an operator of veterinarian hospitals in the southeastern United States.

0:41 What I'm going to show you is basically trying to get through as much of it as I can. We have four templates that we set up.

0:48 I'm just going to tell you a little bit about our business first. Our key driver is doctor capacity, and what doctors we will have on hand is the basis for driving what revenue we can forecast.

0:58 As Jonathan said earlier, payroll is often the largest expense, and that's no different for us. It's our largest expense, but it's also a key driver of where our revenue comes from.

1:08 Some key assumptions or KPIs that we look at are days, for example.

1:13 We look at it two different ways: how many business days will a location be open, and then how many doctor days can we plan on?

1:19 Business days are obviously open days, and doctor days are how many doctors will be working during those open days.

1:25 Then we need to plan for a few questions: Will we have the same doctors? Will we have new ones?

1:31 And then can the existing doctors grow, or will they stay at capacity? What would be the capacity for the new doctors?

1:38 So what I have here is our revenue template.

1:41 I'm going to go through how we have this structured, which is basically the four different sections on this first tab.

1:49 We have columns for each doctor, and then the end goal is to have an annual plan for each doctor and a completed revenue plan for that location.

1:58 By the time we finish filling out this first template.

2:02 Section one is basically bringing in, like Jonathan said, each doctor. We upload into the payroll section and the personnel section of Solver a ton of information on our personnel.

2:16 We actually do it on every pay cycle, so that's always updated and we can run constant month-end reports off of a lot of personnel files.

2:24 What we bring in for each doctor is basically a variable rate, as well as the number of days they're supposed to work and that kind of stuff. That's the first section.

2:34 The second section really just walks through what is that doctor's personal performance with the company.

2:39 How much revenue have they driven? What has been their visit count? And what has been the transaction rate?

2:45 Those are two other key drivers of what drives our revenue, so we have that here in section two.

2:51 We can basically be looking at what has been their historical performance, as well as using any relief vets or sections for whether we're going to hire a new grad or an experienced doctor in the coming year.

3:05 Finally, the meat of the template really comes into the third section where those key drivers that I had mentioned come into play.

3:16 We're going to talk about pet visits, how many visits each doctor is going to be able to do.

3:19 For the existing doctors, we'll increase it based on a percentage, or we'll do our number of visits per day either up or down that we expect.

3:30 We also have the inputs for the transaction rate. We expect them to increase six percent usually.

3:35 We could put in six percent right here, and this would drive six percent for every doctor, so we don't have to enter each doctor individually.

3:42 Because usually we do a five, six, or seven percent price increase each year, so that's kind of what that serves.

3:47 Or do we expect new doctors to individually change in the type of revenue they do in the next year? We decided to also have the feature to change rate by doctor.

3:57 Finally, what number of doctor days are we planning on for those doctors to work in the coming year?

4:02 As you may have seen up here in section two, doctor number four only worked 18 days because they were a new hire toward the end of the year.

4:08 But now we're going to put in we expect them to work 219 days in the coming year.

4:13 So those are the key inputs. It's basically volume through visits, transaction rate through the price, and how many days we expect the doctor to be working.

4:26 The final section on this template that we did was really just to compare those results against history and see what the overall impact was in that location.

4:35 Here you can see for that hospital there's going to be no change in the number of days they're going to be open. They're going to be open 308 days.

4:43 Now with the new doctors coming on board, you can see we've added 108 doctor days.

4:49 That's going to be the real driver of what's going to be driving the increase in revenue for this location.

4:52 What's the impact going to be on visits? That's a 23% increase in visits compared to last year.

4:59 On each doctor day, we monitor how many visits per day each doctor is going to be doing, and overall you can see it's a 16% impact on this location's revenue for the next planned year.

5:15 We also have each doctor's revenue per day and what the compensation is going to be based off of that.

5:25 This is a high-level first template that we have. After getting this done, we'll know what our revenue is for that location.

5:33 It's being derived by the doctors, and more importantly, we'll have a lot of the compensation figured out.

5:38 Because like Jonathan said earlier, compensation for us is our number one expense. It's usually around 40% of total revenue.

5:52 We do have two other tabs on this template real quick.

5:55 We do have a new DVM tab. This is where we add in the same simpler drivers that we would have for an existing employee.

6:05 Whether or not they're going to be a new grad or whether or not they're going to be an experienced hire.

6:10 We also have the third tab, which is once we take that revenue derived by the doctors, we prescribe percentages of what will be the other revenues.

6:18 That includes grooming, boarding revenue, product revenue, and prescription refills.

6:29 Then we have a section for how we allocate out the revenue over every month for the following year.

6:38 That is our revenue template.

6:43 The other template I was going to walk everybody through would be our team staffing template.

6:49 Similar to what Jonathan mentioned, we have all of our payroll information loaded in the system.

6:57 With that, we can basically have in here the hourly rate, how many hours a month everybody works.

7:05 That information is brought in from this employee detail tab.

7:09 We list out all the employees here, and then our input is going to be: do we expect them to work the same amount of hours as they've been running on a monthly basis, or do we expect them to work more or less?

7:20 We take our existing staff plan for budget for them. We have a rate increase, what month that rate increase is going to happen.

7:29 Then we have a per-month cost as well as the total hours because we monitor hours worked for our existing employee base.

7:40 We have a section here where we can add new staff. That's going to be the hire date that we expect, hourly rates, and the number of hours that they work.

8:01 This template is important for us because it allows us to look at the total revenues.

8:08 We bring in the total percentage of each of these categories, so we can compare that for what our expectations are.

8:17 We have the doctor salaries in here, total compensation down here, and we use this to budget certain percentages for bonuses on certain people.

8:26 We also have a section in here to do PTO accruals and holiday pay.

8:34 This is template number two.

8:41 We have a separate template for payroll taxes, payroll fees, and insurance.

8:49 We bring in the employees and any new hires starting to be on here.

8:55 We bring in what the monthly payroll is now that we've already budgeted for it.

9:00 As we scroll to the right, you'll basically see that we're calculating what the accumulating payroll is for the year.

9:06 That will drive your federal and state unemployment taxes.

9:16 We have payroll fees, health insurance, workers' comp insurance, federal and state unemployment, and of course your FICA, Social Security, and Medicare sections.

9:44 We kept this as a separate template because all you have to do now is run it, save it back for each one of the locations, and this step takes just a minute or two to run and save back.

9:55 So it's a really easy process.

10:00 The final template that I have is an operating expense template.

10:05 We bring in the prior year history.

10:11 We will actually bring in a rolling 12-month calculation of the percent revenue, a six-month, and a three-month calculation so that we can look at different trends.

10:20 Our operating expenses are just merely: do we want to do a percent revenue based on one of these three percents?

10:35 This is how that budget gets allocated out.

10:41 We go straight down the P&L and we can either hard-code in dollars to know what it should be on a monthly basis, or we can do percentage of revenue.

10:59 Once all that's done, here's our budget.

11:03 We have a total column. We can compare that total column against what the prior actual was and get down to our increase in EBITDA.

11:13 [Music]

11:18 That is it in a quick summary. I hope that was not too quick, but four templates and our budget for every location is done.

11:28 We do have a lot of input with the field on the doctor side and the staffing side that helps.

11:39 [Music]