Video Transcript

ask the questions we had some live right here on Facebook I had a pre-submitted question here once again from the
Facebook uh group here for service autopilot today uh Kyle asked sap
reports service autopilot custom reports can anyone point me in the direction how to add your own custom columns that
perform basic functions of math such as actual hours divided by budgeted hours
uh I just know I could get so much more functionality out of the reporting
um a lot of people answering or actually putting some notes on here regarding this and
um really the report Center is a very very powerful tool but uh we need to actually proceed with some caution and
figure out what we want before we get in there and actually create a game plan so what
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
about the report Center anything around sa please drop these uh the notes in the
comments and we’re going to be coming on live pretty much every day here uh throughout the winter season answering
your questions pre-submitted either directly to simple growth um or in the Facebook groups that we
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
of reports uh and share that out as I normally do once again composite questions drop them below here and I
just gotta flip this around here to make that view had it like that yesterday with a interview we did with Mike Andes
announcing his 2023 landscape Summit in Louisville Kentucky right before equipment expo which is now uh used to
be gie uh so the question once again if you’re joining is how to build custom reports and service autopilot submitted
on the Facebook User Group I’m going to dive in first thing on a high level I’m
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
in the report Center and service autopilot we go into reports report Center it pulls up this screen
um essay has built out some really great canned reports if you’re not into building your own right here under the
little toaster oven icon it looks like here under reports what we’re going to
be building here is actually analyzes and analyzes you can see we’ve got quite a few here where we built out uh
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
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
custom reports I’m going to show you the top three actually Four as a bonus uh reports that you should have inside your
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
and the first thing that we’re looking at here once this loads is an automated report based around
automation so a little known fact most people think that you cannot build
reports around automations um even at one point in the early days sa did not understand that you could
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
um these are a couple slides that I used in one of our previous talks where we get business owners together once a
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
coaches so most of those folks are growing by several hundred thousand dollars a year on their way to break a
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
and what this report does is it actually communicates on how many people are in
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
sales pipeline under communicating it’s our status how many days have they been in our sales pipeline
here on the left and then we’ve got the actual estimate date the projected Revenue a really important thing that we
want to be looking at is the type is it a leader client because we’re gonna have different closing ratios versus new sale
versus an upsell expansion play estimate number for easy reference the status has it been sent or is in draft also
important uh we filter this out just so it is the sent estimates only uh the email of the client
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
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
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
into their business of how many people are in their sales pipeline how long has it been the estimate value and the the
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
those sales to hit our monthly quarterly and yearly goals so report number one a automated report based on your sales
pipeline including estimate date value lead Source estimate status
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
Care uh next thing we want to look at is doing a little homework so under the
payroll job costing tab on the right under the employee level we should be putting their hourly rate and their
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
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
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
second must-have report is we need to add in our labor burden now these are some fictitious numbers but the idea is
we got workman’s comp unemployment FICA some other things in there maybe health
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
plug in each employee’s number here in the simple growth blueprint it kicks out your labor with labor burden we put the
hourly rate here and the regular rate with labor burden any overtime with labor now you may ask Mike why would we
ever fill this data in uh well Kyle the question is why wouldn’t we because this
data is now flow going into a essay report of report number two that I think is essential so what we’ve got is
employee job costing data in report so whether you’re pulling this from a pre-canned report in a little toaster
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
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
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
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
decisions out so once you fill this out with the labor and labor burden on the previous screen and we go into uh the
product called Low G it’s a third-party bolt-on Reporting System uh Enterprise Reporting System actually inside service
autopilot it’s going to give you your labor cost labor and labor burden and direct uh drive time direct costs at
non-billable time here um and honestly we probably could start backing in some inefficiency percentages
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
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
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
route density you’ve routed incorrectly for Route density or B or actually C your employees are making uh not
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
by simply forcing the methodology of them clocking and clocking out so that’s report number two job costing data and
reports for labor costs and direct costs as we get that in there and that’s going
to be our clock time times our labor and labor burden that all happens automatically and I say that’s that’s a
report number two so once again good data in good data out not only am I going to show you how to manipulate
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
that so if you’re interested we do offer free audit 45 minutes of essay to make
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
autopilot this is probably the most important screen in the entire system because it sets you up for success to
make sure we have all the data we need for invoicing and our reporting
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
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
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
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
dispatch board we want to do is actually go to Resource filter to the crew and create a views we’re clicking through
moku one two three four five six and just making sure Cadence in each day and
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
the beginning of the day and ensuring that data is entered in that Cadence of probably once a week solid will get your
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
this is the essential business data that we need to run our businesses and if we’re doing paper performance or piece
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
cleaning at this point so close out day screen once we get those columns and the data we want to
make sure we have a a good start and stop time that’s over a minute
hopefully we have our variance is the over and under we want to make sure we have our so the
three things we’re checking really quickly starting stop times a rate and budgeted hours if those three variables
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
prices big big thing going into this season uh how do we know these customers are profitable so there is a total of uh
two reports I’m actually going to call it three that are essential under this umbrella so we’ve given you actually the
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
is how to know when we’re raising our prices so this is an export from uh the
little toaster oven pre-built reports and the idea here is this is a manual
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
we did export it what this report does manually is shows you how much revenue we generated per mowing what our Revenue
our actual our price was per cut the revenue per Mano based on the guys on average 57.30 if our desired gold per
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
service autopilot’s report Center and if you have the automations you can actually automate that report up to five
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
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
once this is built out that same exact report that was on manual extract a whole bunch of Excel or Google sheet
formulas uh we have now automated that and built that into service autopilot uh so what you’re going to see here as we
dive through is uh clock time is the data okay so you can build some logic in
these reports to make sure there’s actually data in here um is there billable hours is there
budgeted hours and is there a job amounts if you remember a few seconds ago I talked about the key element on
the service autopilot report or dispatch board or closeout day screen we need we need a start and stop time a budget time
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
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
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
of reports no matter the software you’re using so the assigned resource whether it’s a solo individual is Chad or our
cleanup crew number two what we’ve got here is an efficiency percentage of
budget uh so if they were at 125 they’re 25 percent under budget
um what they would do is we’d aggregate or average these up um to get a total I’m going to show you
actually how to do some aggregation and Counting inside the software uh but the idea is we know now not emotionally if
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
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
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
the um is the over the timer over budget so if you were let’s just say it’s 75 for easy
math the difference between 75 and 100 is 25. you were 25 over budget obvious
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
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
accountability with quality for these Crews so we want daily and weekly reports that we can report out on each
Cruise efficiency and the entire company and we want a percent of budget uh in a
fashion whether it’s our version or your version that we can actually show to the cruise that doesn’t show the actual
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
be happy percentage is the way to go especially um with our crew that came in from Mexico and Guatemala English is a secret
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
100 giving 100 that was pretty common across all the cultures that we employed uh whether it was h2b or our normal
Workforce so that’s that would be my Pro tip so basically your third report there
uh that we’re looking at before we get to the bonus report is be able to daily and weekly accountability with a
percentage over or under budget um on a daily weekly report automated out and the final one here is automated
job costing so this is a report we should be running right now if we have not done this with Automotion but once
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
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
um but what we’ve got now is every client that we have serviced here and we
know the assigned resource the date and we have budgeted hours we have actual hours we have Revenue per man
hour and our total revenue so once again the sheet is checking do we have a start and stop time a budgeted time and
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
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
you update it on the closeout day screen and refresh this automated report we’re going to dive into here in a second that
data will flow and you got good data so what we’ve got here is uh in this fictitious company
um actual Revenue confirmed how much we were making and what is our desired dollar per man hour so if you plug in
and told to say hey Mike I want to make 55 per man hour um the sheet is saying hey on this
account here you need to raise that average trip five dollars and four cents this says you need to raise it by 1200
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
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
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
if we’re going out and raising and doing contract renewals right now uh my suggestion is we raise the price to our
hourly goal on just the people not hitting the hourly threshold for making 100 bucks an hour and our
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
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
the losers and they are definitely losers for most the time because these are the people that complain they don’t
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
not emotionally raise those prices so that is the third and final report uh
that we want to actually do and as an added bonus if we’re talking about reporting we want to have that crew
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
is a great template to use we’ve got our 11 mowing Crews here percent of budget
across the board and the quality score and that’s going to be out of a 10 and
then we have our weekly results so now we have our non-emotional alignment of Revenue per man hour our production with
a quality score uh once again we want to use it as a percentage not the budgeted hours because we will create animosity
around the cruise so Kyle great question what I’m going to do now is dive into service autopilot report Center and give
you some Basics how to build the three plus the bonus report I showed you
um in there but there is an endless amount of opportunity here even going into figuring out how much production
rate it it is per thousand square feet based on the piece of equipment um so as we’re adding this data in we
can start to not use industry averages but we can actually Implement some time and motion studies based on the guys and
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
order of an analysis in a report is the data is held inside of analysis
and it is can be captured inside a report to um send out where people don’t actually
have access to this database so was on a call last night with uh very important customer for service autopilot we were
actually called into uh lend a hand um to help in these reports so the simple girl team uh really is taking the
reports to a Next Level we’ve spent thousands of hours over the last two or three years in here we were actually at
Service autopilot’s uh office in Dallas when they were actually building these when John and Jonathan the co-founders
the service autopilot flew out my whole development team to actually help them uh put some structure and some workflow
to the automations that you’re currently using so uh we’ve been seeing this as long as we’ve been doing automation so
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
in and we’re selecting some data sets here so uh there is a lot a lot of options here
um but let’s go in and we’re just going to go in and take a job cost details so this is the
foundation of a lot of the reports we’re doing start and stops dollar per hour now it’s going to select everything in
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
and maybe budgeted hours per man hour and we would probably want the drive
time cost effect and then our labor cost and our labor cost of Revenue so I’m not
getting the exact I won’t get into the exact percentages but in our scale and masterminds group we talk about what a
healthy percentage labor percentage of Revenue is some of this can be actually built out automatically inside here and
then we’re going to go in and grab our service and this will probably be a good uh
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
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
service address uh depending what data is in here I won’t do a client name
and we could go into custom Fields so let’s go in and
grab the date of the job and the budgeted hours as well so this is a good
start actual start actual stop time is probably decent and signed resources
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
as well um so as this loads in we’ve got all our data in here so these are drag and drop
Builders so as we’re building these here I would suggest starting to go in and dragging some things around so date
we would go in and put the service address we are going to go in and put our
service name our service and here a number of men I would probably say
actual start actual stop and
grab my assigned resources so I know what are the resources were how many men start stop time actual hours our
budgeted hours budget dollars per hour our drive time cost effect our labor cost effect so
those are some of the things that we would look at and man maybe labor as a percentage okay
so as we’re going in here now we can go in and add some uh filters so let’s go
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
to a specific date here
and we’ll grab on here so let’s do the month of June
we’re going to add that in
so what we’ve done is started a filter between the first and 30th of June and
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
filter down now there’s some additional things here we may want to do so and we’ll get into
some basic equations so we can go in now and actually go into service address or client name right now we did it
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
did each location um per per visit
and we’ve got actual start times and we’ve got uh actual hours so actual verse budgeted so one of the questions
is Kyle said how do we start to actually run um data on some of these things here
so in this test account obviously we’re missing some labor and drive time cost effects but we could do now is go into
these formula and name this uh
over slash under the hours and the formula now would go
in and we’re going to take the column of budgeted hours
and subtract that from our actual hours
actually we would probably want to do that in the opposite order
and that date data tape is going to be a number and it is this display format
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
start building those additional Kyle those numbers that Kyle actually had now we can go in and you can see here
we’ve got the display format it’s kind of funky so if we click into that here
we can actually put our display format and
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
selection we made now we pulled them out to two digits so now that we have that the other
interesting thing here is we may want to know how many jobs we did for each person so based on the date
um or the service we can go in and say based on that service how many services did we actually do in
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
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
a total of 1600 jobs done in the month and this is a specific count on each one
of those now what we’ve done here on our over budgeted hours is we’ve got our total
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
the formulas up here but I’m trying not to overwhelm everybody in this video but this is some of the foundational stuff
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
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
full-time now um that actually work on some of this um and we spend probably 20 to 30 hours a
month at least in the report Center building the reports and testing it so on average you can see now we’re over
under budgeted hours here now we got some over some under uh the
idea here is then we could probably go in and format those cells
and we’d want to say if the numeric value
um is
up to zero
and let’s see how this goes and hit OK
so if it is less than zero we are good now we can go in and add
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
format as visual Clues as well now if we’re looking at uh Kyle’s question of
dollars per man hour um Revenue we could also go in and
Aggregate and take an average and that’s going to be the easiest way of doing this we can also write custom
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
value here um of my Revenue per man hour
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
an hour what do I need to charge so these are some of the basics in the service autopilot report Center that I
would recommend um you start with on the basics and if anybody has their comments or questions
we want a more advanced video how to actually go out and build more custom formulas just like we did here I’m happy
to dive in that but that is um basically here is your your list of columns and there’s a list of formulas
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
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
pretty pretty detailed but high level Not to cause confusion so comments questions dropping below uh top three
reports plus the bonus one report that we should be looking at um we are going to go in and I’ll give
you a quick overview and how to actually go go in and build also reports and logis the first report that we should be
looking at is a sales pipeline report based on your estimate automation follow-up
we want job costing data in here for Success we have good data in good data
out for our job costing data for Drive Time and um on the site with labor and labor
burden gotta clock in and out of those Drive times close out day screen we need all the appropriate information in there
start stop time budgeted timing and values and we need some save views to save the checking of this data for your
admin or virtual assistant as we’re going in this is another view of those three areas start and stop budget hours
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
export out of the Little Toaster icon that we can use into Excel but I recommend automating this report
through our V2 kpi and accountability so we’re going to have our daily and weekly reports telling is a percentage if
they’re over and under budget per crew for the day and summarize for the week and our job cost and report to raise
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
loads how do we actually automate these reports to get them in your hands or
your managers or your team’s hand in there so the key thing remembers you have up to five reports we are working
with sa as of right now to get that number up up so you can actually automate these ports because who really
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
it’s going to and hit done and once we do that if we come back there will be another
button here that we can hit run on or run now we can actually send that automatically to report that so that’s
how we send our analysis and if we want to build the analysis I can make another video how to take your analyzes and
actually bucket them up inside a report so Kyle great question how to build some formulas how to get some reports an
essay hopefully that helped Callahan’s corner you ask the questions we had some live right here