0:00
ask the questions we had some live right here on Facebook I had a pre-submitted question here once again from the
0:05
Facebook uh group here for service autopilot today uh Kyle asked sap
0:10
reports service autopilot custom reports can anyone point me in the direction how to add your own custom columns that
0:16
perform basic functions of math such as actual hours divided by budgeted hours
0:21
uh I just know I could get so much more functionality out of the reporting
0:28
um a lot of people answering or actually putting some notes on here regarding this and
0:34
um really the report Center is a very very powerful tool but uh we need to actually proceed with some caution and
0:39
figure out what we want before we get in there and actually create a game plan so what
0:44
I’m going to do is pop open the screen in natural uh form here as we normally do if you have any comments or questions
0:50
about the report Center anything around sa please drop these uh the notes in the
0:56
comments and we’re going to be coming on live pretty much every day here uh throughout the winter season answering
1:01
your questions pre-submitted either directly to simple growth um or in the Facebook groups that we
1:06
hang out in to help business owners scale and grow their business in the off season so without any further delay here I’m going to go in and grab the screen
1:13
of reports uh and share that out as I normally do once again composite questions drop them below here and I
1:20
just gotta flip this around here to make that view had it like that yesterday with a interview we did with Mike Andes
1:26
announcing his 2023 landscape Summit in Louisville Kentucky right before equipment expo which is now uh used to
1:33
be gie uh so the question once again if you’re joining is how to build custom reports and service autopilot submitted
1:39
on the Facebook User Group I’m going to dive in first thing on a high level I’m
1:44
going to show you some slides real quick to go over and overview and I’m going to dive into the reports Center Ansel Kyle’s question if you have never been
1:51
in the report Center and service autopilot we go into reports report Center it pulls up this screen
1:57
um essay has built out some really great canned reports if you’re not into building your own right here under the
2:02
little toaster oven icon it looks like here under reports what we’re going to
2:07
be building here is actually analyzes and analyzes you can see we’ve got quite a few here where we built out uh
2:14
production rates here for a bunch of our clients so they know how much time it takes to cut say a thousand square feet with a 60 inch mower or to mob blowing
2:21
Edge but we’ve got a lot of stuff in this test account here but the idea is Kyle wanted to Basics how to build
2:26
custom reports I’m going to show you the top three actually Four as a bonus uh reports that you should have inside your
2:32
service autopilot account then I’m going to show you some Basics how to build them out so hold tight here we go so I’m going to pop this open as we normally do
2:38
and the first thing that we’re looking at here once this loads is an automated report based around
2:45
automation so a little known fact most people think that you cannot build
2:50
reports around automations um even at one point in the early days sa did not understand that you could
2:56
actually go out and build these in here if you see the logo in the upper left hand corner of my screen this is our siblical scale group and scale plus
3:04
um these are a couple slides that I used in one of our previous talks where we get business owners together once a
3:09
month um and teach these Concepts and we also have two and a half hours a month one-on-one with one of our seven figure
3:15
coaches so most of those folks are growing by several hundred thousand dollars a year on their way to break a
3:20
million to our masterminds group but the first report that we’re looking at here is 20 days of close it’s an automated estimate follow-up report
3:28
and what this report does is it actually communicates on how many people are in
3:33
your sales pipeline so if we’re going towards the end of the month here we need to know uh what is active in our
3:39
sales pipeline under communicating it’s our status how many days have they been in our sales pipeline
3:46
here on the left and then we’ve got the actual estimate date the projected Revenue a really important thing that we
3:52
want to be looking at is the type is it a leader client because we’re gonna have different closing ratios versus new sale
3:57
versus an upsell expansion play estimate number for easy reference the status has it been sent or is in draft also
4:04
important uh we filter this out just so it is the sent estimates only uh the email of the client
4:10
the postal code and the source how do they hear about us because once again we could go in and iterate off this uh if
4:17
you’re doing lead Source tracking inside service autopilot or any other software you’re going to have very distinct closing ratios based on your client lead
4:25
source so these are things that we want to be able to do to empower the business owner to have a really good look diving
4:30
into their business of how many people are in their sales pipeline how long has it been the estimate value and the the
4:37
lead Source you know they are lead or client and this is going to give us some clarity here uh where to focus to drive
4:44
those sales to hit our monthly quarterly and yearly goals so report number one a automated report based on your sales
4:50
pipeline including estimate date value lead Source estimate status
4:57
um and whether they’re a leader client that’s going to be report number one that I’m looking at in my service business as we looked at Callahan’s Lawn
5:03
Care uh next thing we want to look at is doing a little homework so under the
5:09
payroll job costing tab on the right under the employee level we should be putting their hourly rate and their
5:14
labor with labor burden so not only Kyle I’m not going to talk about their supports but I’m going to give you some of the data you need because good data
5:21
and good data out what we’re finding here is no data in no data out so we need to have some good data in here to be able to drive those important
5:27
business decisions that Kyle wants to make in his report uh so we need to go in Kyle before we do uh one of the
5:34
second must-have report is we need to add in our labor burden now these are some fictitious numbers but the idea is
5:39
we got workman’s comp unemployment FICA some other things in there maybe health
5:44
insurance things like that don’t play in your business yet so you’d zero it out but what you’re going to have is uh down here on the left is a 19 labor burden we
5:52
plug in each employee’s number here in the simple growth blueprint it kicks out your labor with labor burden we put the
5:58
hourly rate here and the regular rate with labor burden any overtime with labor now you may ask Mike why would we
6:03
ever fill this data in uh well Kyle the question is why wouldn’t we because this
6:09
data is now flow going into a essay report of report number two that I think is essential so what we’ve got is
6:17
employee job costing data in report so whether you’re pulling this from a pre-canned report in a little toaster
6:23
oven and I’ll show you what report that is uh this data is going to uh flow in automatically this is one of our
6:30
customized reports and I’m going to show you how to build out some of this but we need to be clocking in and out of the drive mobile and we need to be clocking
6:37
into the drive time in and out and we need to hit the start and stop for the labor those are the data points that
6:42
when Kyle goes to build these or anybody else reports that we want in the system data in data out no data in no business
6:49
decisions out so once you fill this out with the labor and labor burden on the previous screen and we go into uh the
6:56
product called Low G it’s a third-party bolt-on Reporting System uh Enterprise Reporting System actually inside service
7:02
autopilot it’s going to give you your labor cost labor and labor burden and direct uh drive time direct costs at
7:10
non-billable time here um and honestly we probably could start backing in some inefficiency percentages
7:16
of downtime once we get this data in the report but the idea is whoever’s clocked into that job or drive time we get their
7:22
labor and labor burden per job so this is going to give us some ability to see uh if we’re winning on the site or if
7:27
we’re losing on drive time uh now three things that could happen with that drive time when you’re inferring from this report is a you have not created good
7:35
route density you’ve routed incorrectly for Route density or B or actually C your employees are making uh not
7:42
informed decisions and driving halfway across town for a gas station or cigarette break but these things can start to be inferred based on this data
7:48
by simply forcing the methodology of them clocking and clocking out so that’s report number two job costing data and
7:55
reports for labor costs and direct costs as we get that in there and that’s going
8:01
to be our clock time times our labor and labor burden that all happens automatically and I say that’s that’s a
8:07
report number two so once again good data in good data out not only am I going to show you how to manipulate
8:13
manipulate some of these reports but uh we want to be able to make sure we’ve got the system set up correctly to do
8:20
that so if you’re interested we do offer free audit 45 minutes of essay to make
8:25
sure or any software to make sure you’re using it correctly and getting the data you need to run your service business this is a closeout day screen in service
8:31
autopilot this is probably the most important screen in the entire system because it sets you up for success to
8:38
make sure we have all the data we need for invoicing and our reporting
8:44
so first thing we want to do is go on the far right hand side here under columns and we want to click close out
8:50
day screen we want to click variance and we want to put actual hours and what that’s going to do is get us all the
8:56
data we need on a daily basis for a quick two to three minute sweep per crew to make sure all the data is in there so
9:02
when we run those reports we’re going to show you how to build a minute we’ve got good data now that’s going to be under our
9:07
dispatch board we want to do is actually go to Resource filter to the crew and create a views we’re clicking through
9:13
moku one two three four five six and just making sure Cadence in each day and
9:19
we’re if there is not the drive time or there is not the clock and clock out time we are going to that crew leader at
9:24
the beginning of the day and ensuring that data is entered in that Cadence of probably once a week solid will get your
9:29
crew leader into shape if your crew leader is not still clocking in and out of the data after week week and a half to either coach up or coach out because
9:36
this is the essential business data that we need to run our businesses and if we’re doing paper performance or piece
9:42
rate pay uh this is the data that we can automatically pull in a piece rate pay system we do that for lawn care and home
9:48
cleaning at this point so close out day screen once we get those columns and the data we want to
9:54
make sure we have a a good start and stop time that’s over a minute
10:00
hopefully we have our variance is the over and under we want to make sure we have our so the
10:07
three things we’re checking really quickly starting stop times a rate and budgeted hours if those three variables
10:13
are there we are set up for some of these reports we’re gonna be diving into shortly in a second all right so raising
10:19
prices big big thing going into this season uh how do we know these customers are profitable so there is a total of uh
10:27
two reports I’m actually going to call it three that are essential under this umbrella so we’ve given you actually the
10:33
top three reports I’ve given you probably two bonuses in this talker I’m not going to show you how to build some of the stuff out uh so the first thing
10:39
is how to know when we’re raising our prices so this is an export from uh the
10:44
little toaster oven pre-built reports and the idea here is this is a manual
10:50
version which I’m not recommending uh we use the report Center which I’m going to show you how to use in a second but if
10:55
we did export it what this report does manually is shows you how much revenue we generated per mowing what our Revenue
11:02
our actual our price was per cut the revenue per Mano based on the guys on average 57.30 if our desired gold per
11:09
man hour and that crew is 60 we need the rate is the price by 2.56 the good news is this can all be automated in the
11:16
service autopilot’s report Center and if you have the automations you can actually automate that report up to five
11:22
of them to be emailed to you and your team members on a daily weekly or monthly basis so this is what we’re getting into here in a second
11:28
so new V2 kpi and accountability uh this is what we’re gonna be doing in the new report Center and how we build it out so
11:34
once this is built out that same exact report that was on manual extract a whole bunch of Excel or Google sheet
11:40
formulas uh we have now automated that and built that into service autopilot uh so what you’re going to see here as we
11:47
dive through is uh clock time is the data okay so you can build some logic in
11:52
these reports to make sure there’s actually data in here um is there billable hours is there
11:57
budgeted hours and is there a job amounts if you remember a few seconds ago I talked about the key element on
12:03
the service autopilot report or dispatch board or closeout day screen we need we need a start and stop time a budget time
12:09
and a job amount an automated report now if we build it customized can check to make sure that data is there and if it
12:14
isn’t uh you’ll see on one of the next screens it can actually tell you to check your data so what we’ve got here is a
12:21
a daily and weekly report that we’d want to run so I’m going to consider this one report but this should be in your Suite
12:28
of reports no matter the software you’re using so the assigned resource whether it’s a solo individual is Chad or our
12:34
cleanup crew number two what we’ve got here is an efficiency percentage of
12:39
budget uh so if they were at 125 they’re 25 percent under budget
12:45
um what they would do is we’d aggregate or average these up um to get a total I’m going to show you
12:50
actually how to do some aggregation and Counting inside the software uh but the idea is we know now not emotionally if
12:57
they’re on budget this is data we can share with the crew uh live time within a day the idea though is we built this
13:03
as percentages 100 we can go to you and say hey you guys gave 100 with quality now that’s important if you gave 125
13:10
today you kick butt you did your job uh but it it conversely if you only gave 85 and the difference between 85 and 100 is
13:18
the um is the over the timer over budget so if you were let’s just say it’s 75 for easy
13:24
math the difference between 75 and 100 is 25. you were 25 over budget obvious
13:31
that’s a non-emotional number we could stick on the shop wall on a TV or dry erase board it doesn’t show you how many
13:36
people were budgeted for each hour um but these reports will get you that data so we’re not creating animosity amongst the crews but we’ve got clear
13:42
accountability with quality for these Crews so we want daily and weekly reports that we can report out on each
13:48
Cruise efficiency and the entire company and we want a percent of budget uh in a
13:53
fashion whether it’s our version or your version that we can actually show to the cruise that doesn’t show the actual
13:58
budgeted hours so uh if you do show the budgeted hours someone’s going to complain that they got too many hours not enough hours they’re never going to
14:05
be happy percentage is the way to go especially um with our crew that came in from Mexico and Guatemala English is a secret
14:11
language sometimes they had a heart stand understanding um some of the things we were showing them on the wall but they did understand the percentages
14:17
100 giving 100 that was pretty common across all the cultures that we employed uh whether it was h2b or our normal
14:25
Workforce so that’s that would be my Pro tip so basically your third report there
14:30
uh that we’re looking at before we get to the bonus report is be able to daily and weekly accountability with a
14:36
percentage over or under budget um on a daily weekly report automated out and the final one here is automated
14:43
job costing so this is a report we should be running right now if we have not done this with Automotion but once
14:48
again we need that labor and labor burden connected on the employees which is good news if you don’t have it you can update it no update in in addition
14:56
we also need good data from the closeout day screen as we’ve already viewed on this video so if you’re joining me late make sure you watch the review of it
15:03
um but what we’ve got now is every client that we have serviced here and we
15:09
know the assigned resource the date and we have budgeted hours we have actual hours we have Revenue per man
15:16
hour and our total revenue so once again the sheet is checking do we have a start and stop time a budgeted time and
15:22
revenue we’re charging uh you can see here in this example we do not have some data so check the actual hours and check
15:28
the revenue per man hour this is going to force you go back to the closeout day screen and update good news that is once
15:34
you update it on the closeout day screen and refresh this automated report we’re going to dive into here in a second that
15:40
data will flow and you got good data so what we’ve got here is uh in this fictitious company
15:46
um actual Revenue confirmed how much we were making and what is our desired dollar per man hour so if you plug in
15:52
and told to say hey Mike I want to make 55 per man hour um the sheet is saying hey on this
15:58
account here you need to raise that average trip five dollars and four cents this says you need to raise it by 1200
16:03
but lo and behold we’ve got some red data in here that is inaccurate and missing data so we need to go back and
16:09
fix that assumption before we do such uh next job down here we’ve got all green data need to raise the price 12.18 or
16:16
12.80 and on the bottom one here we’re hitting our hourly goal of 55 bucks an hour so we don’t need to raise the price
16:21
if we’re going out and raising and doing contract renewals right now uh my suggestion is we raise the price to our
16:27
hourly goal on just the people not hitting the hourly threshold for making 100 bucks an hour and our
16:32
goal is 55 don’t go out and raise the prices by a percent to some of your most profitable clients because what they’re
16:38
going to do is go out and shop you and you’re going to lose your most profitable and best clients and what you’ve done is only incrementally raise
16:44
the losers and they are definitely losers for most the time because these are the people that complain they don’t
16:49
pay on time and are just not fun to work with um they already know they’re getting a deal so we want to go out and
16:54
not emotionally raise those prices so that is the third and final report uh
17:00
that we want to actually do and as an added bonus if we’re talking about reporting we want to have that crew
17:07
buy-in from those daily and weekly reports um for public accountabilities if you’re putting it on the shop wall or a TV this
17:14
is a great template to use we’ve got our 11 mowing Crews here percent of budget
17:20
across the board and the quality score and that’s going to be out of a 10 and
17:26
then we have our weekly results so now we have our non-emotional alignment of Revenue per man hour our production with
17:33
a quality score uh once again we want to use it as a percentage not the budgeted hours because we will create animosity
17:39
around the cruise so Kyle great question what I’m going to do now is dive into service autopilot report Center and give
17:45
you some Basics how to build the three plus the bonus report I showed you
17:51
um in there but there is an endless amount of opportunity here even going into figuring out how much production
17:57
rate it it is per thousand square feet based on the piece of equipment um so as we’re adding this data in we
18:04
can start to not use industry averages but we can actually Implement some time and motion studies based on the guys and
18:10
girls in the field in your actual equipment so once again this is the canned reports here we’re going to be living in uh an analysis and the packing
18:17
order of an analysis in a report is the data is held inside of analysis
18:23
and it is can be captured inside a report to um send out where people don’t actually
18:29
have access to this database so was on a call last night with uh very important customer for service autopilot we were
18:35
actually called into uh lend a hand um to help in these reports so the simple girl team uh really is taking the
18:42
reports to a Next Level we’ve spent thousands of hours over the last two or three years in here we were actually at
18:48
Service autopilot’s uh office in Dallas when they were actually building these when John and Jonathan the co-founders
18:55
the service autopilot flew out my whole development team to actually help them uh put some structure and some workflow
19:01
to the automations that you’re currently using so uh we’ve been seeing this as long as we’ve been doing automation so
19:06
there’s a lot here I suggest taking some baby steps start out with a toaster oven when you’re ready to dial in we’re going
19:12
in and we’re selecting some data sets here so uh there is a lot a lot of options here
19:18
um but let’s go in and we’re just going to go in and take a job cost details so this is the
19:23
foundation of a lot of the reports we’re doing start and stops dollar per hour now it’s going to select everything in
19:28
here um we probably want to go in let’s say we’ve got the number of men the actual hours that they’re on the job
19:35
and maybe budgeted hours per man hour and we would probably want the drive
19:41
time cost effect and then our labor cost and our labor cost of Revenue so I’m not
19:47
getting the exact I won’t get into the exact percentages but in our scale and masterminds group we talk about what a
19:52
healthy percentage labor percentage of Revenue is some of this can be actually built out automatically inside here and
19:58
then we’re going to go in and grab our service and this will probably be a good uh
20:06
starting point here so I’m going to go in now I can go in and select the actual clients so it’s a cast dating thing as it loads the data tables on stop so Kyle
20:13
had a great question here and we’re we’re going to get into this here um so let’s grab we’re going to do a
20:18
service address uh depending what data is in here I won’t do a client name
20:24
and we could go into custom Fields so let’s go in and
20:30
grab the date of the job and the budgeted hours as well so this is a good
20:37
start actual start actual stop time is probably decent and signed resources
20:43
so you can play with this once you hit OK uh you can drive this in once again you can pull those custom fields in to start maybe building production reports
20:49
as well um so as this loads in we’ve got all our data in here so these are drag and drop
20:56
Builders so as we’re building these here I would suggest starting to go in and dragging some things around so date
21:02
we would go in and put the service address we are going to go in and put our
21:09
service name our service and here a number of men I would probably say
21:16
actual start actual stop and
21:24
grab my assigned resources so I know what are the resources were how many men start stop time actual hours our
21:30
budgeted hours budget dollars per hour our drive time cost effect our labor cost effect so
21:36
those are some of the things that we would look at and man maybe labor as a percentage okay
21:43
so as we’re going in here now we can go in and add some uh filters so let’s go
21:48
in and do a date filter so we’re going to go in and filter and I’m going to do a date range here and I’m going to go in
21:55
to a specific date here
22:03
and we’ll grab on here so let’s do the month of June
22:19
we’re going to add that in
22:28
so what we’ve done is started a filter between the first and 30th of June and
22:33
now what we can do is go into the date and actually sort it from A to Z so growing from top to bottom and we can
22:39
filter down now there’s some additional things here we may want to do so and we’ll get into
22:46
some basic equations so we can go in now and actually go into service address or client name right now we did it
22:53
address so what I’m going to do is actually group all of our clients together so we’ve got all of the jobs we
22:58
did each location um per per visit
23:06
and we’ve got actual start times and we’ve got uh actual hours so actual verse budgeted so one of the questions
23:13
is Kyle said how do we start to actually run um data on some of these things here
23:20
so in this test account obviously we’re missing some labor and drive time cost effects but we could do now is go into
23:26
these formula and name this uh
23:33
over slash under the hours and the formula now would go
23:40
in and we’re going to take the column of budgeted hours
23:47
and subtract that from our actual hours
23:53
actually we would probably want to do that in the opposite order
24:04
and that date data tape is going to be a number and it is this display format
24:10
would be probably just a number so we would go in and actually add that out so this is how we start to Aggregate and
24:16
start building those additional Kyle those numbers that Kyle actually had now we can go in and you can see here
24:22
we’ve got the display format it’s kind of funky so if we click into that here
24:28
we can actually put our display format and
24:34
put it in there we’re not going to hit add we’re actually going to replace that and by replacing it that will update those values on there based on the
24:41
selection we made now we pulled them out to two digits so now that we have that the other
24:48
interesting thing here is we may want to know how many jobs we did for each person so based on the date
24:55
um or the service we can go in and say based on that service how many services did we actually do in
25:02
that month so we can go in and Aggregate and actually get a count and that’s going to go in and subtotal each one of
25:07
those jobs as we go in I probably could give you a 10 hour crash course here so I don’t want to go too deep but we have
25:14
a total of 1600 jobs done in the month and this is a specific count on each one
25:19
of those now what we’ve done here on our over budgeted hours is we’ve got our total
25:27
for each one so what we probably want to do now is go in and Aggregate and take an average now this can all be done in
25:33
the formulas up here but I’m trying not to overwhelm everybody in this video but this is some of the foundational stuff
25:38
like I said we’ve been doing this for a long time it’s not often it’s not uh uncommon that the software company will
25:44
actually call us to to get some insight how we build some of these reports we’ve got a team about 26 to 28 people
25:49
full-time now um that actually work on some of this um and we spend probably 20 to 30 hours a
25:56
month at least in the report Center building the reports and testing it so on average you can see now we’re over
26:01
under budgeted hours here now we got some over some under uh the
26:07
idea here is then we could probably go in and format those cells
26:12
and we’d want to say if the numeric value
26:17
um is
26:26
up to zero
26:35
and let’s see how this goes and hit OK
26:48
so if it is less than zero we are good now we can go in and add
26:54
additional data if it’s over zero over budget we could put it in Red so these are some of the things we could start to
26:59
format as visual Clues as well now if we’re looking at uh Kyle’s question of
27:05
dollars per man hour um Revenue we could also go in and
27:10
Aggregate and take an average and that’s going to be the easiest way of doing this we can also write custom
27:16
formulas up top right up here just like I did here and that is also a great way of doing it but now I know uh my average
27:24
value here um of my Revenue per man hour
27:29
so that’s how we start to then eventually take that data and say if the revenue per man hour is less than say 55
27:36
an hour what do I need to charge so these are some of the basics in the service autopilot report Center that I
27:43
would recommend um you start with on the basics and if anybody has their comments or questions
27:48
we want a more advanced video how to actually go out and build more custom formulas just like we did here I’m happy
27:56
to dive in that but that is um basically here is your your list of columns and there’s a list of formulas
28:03
that we can get in here and as well as the operators here we can also go in and write uh not if else statements but
28:10
actually case statements um so for most people watching this diving into a case statement would probably be extremely confusing so I’ve kept this actually
28:16
pretty pretty detailed but high level Not to cause confusion so comments questions dropping below uh top three
28:22
reports plus the bonus one report that we should be looking at um we are going to go in and I’ll give
28:27
you a quick overview and how to actually go go in and build also reports and logis the first report that we should be
28:34
looking at is a sales pipeline report based on your estimate automation follow-up
28:39
we want job costing data in here for Success we have good data in good data
28:45
out for our job costing data for Drive Time and um on the site with labor and labor
28:51
burden gotta clock in and out of those Drive times close out day screen we need all the appropriate information in there
28:56
start stop time budgeted timing and values and we need some save views to save the checking of this data for your
29:01
admin or virtual assistant as we’re going in this is another view of those three areas start and stop budget hours
29:07
and rate raising prices how do we do it we raise it on the prices just below our hourly threshold and this is our manual
29:14
export out of the Little Toaster icon that we can use into Excel but I recommend automating this report
29:20
through our V2 kpi and accountability so we’re going to have our daily and weekly reports telling is a percentage if
29:26
they’re over and under budget per crew for the day and summarize for the week and our job cost and report to raise
29:31
those prices not emotionally final piece that I do want to leave you with is when we go back to the home icon and this
29:38
loads how do we actually automate these reports to get them in your hands or
29:43
your managers or your team’s hand in there so the key thing remembers you have up to five reports we are working
29:49
with sa as of right now to get that number up up so you can actually automate these ports because who really
29:56
wants to go in here and find these uh we hit the green icon here of the schedule we put in all of the information who
30:04
it’s going to and hit done and once we do that if we come back there will be another
30:10
button here that we can hit run on or run now we can actually send that automatically to report that so that’s
30:16
how we send our analysis and if we want to build the analysis I can make another video how to take your analyzes and
30:22
actually bucket them up inside a report so Kyle great question how to build some formulas how to get some reports an
30:28
essay hopefully that helped Callahan’s corner you ask the questions we had some live right here