Streamline Your Finances with the Auto Repair Invoice Template Excel for Finance

Auto repair invoice template excel for Finance

[Music] [Applause] [Music] if you're looking to launch or grow an auto repair shop and you've gone maybe to the bank or to a potential investor and they have asked you for a set of financial projections and you're kind of caught like a deer in the headlights wondering how in the world do i create a set of financial projections but that's the thing that's blocking you standing in your way between you and that business loan or a potential investment we've come to the right place because we have built a financial projection template built specifically for auto repair shops my name is adam hooksman i'm the co-founder of projection hub and over the last decade we've helped thousands of entrepreneurs create financial projections for their business and so today i'm going to walk you through our auto repair shop financial projection template and the first thing i'm going to show you here is really looking at this with the end in mind so what you can see here is our at a glance tab and what you're going to see is a number of tables and charts and graphs that are going to be useful for you once you've created your projection so this might be what you want to throw into your business plan or what your banker might want to see you've got your profit and loss at a glance some different summary data nice graphs charts use of startup funds a breakeven graph a lot of details that a banker or investor might want to see and then we have the projected income statement cash flow statement and balance sheet for five years annualized but then we also have those same financial statements income statement cash flow and balance sheet in a monthly format here as well so that's really what we're going to get to but we have a little work to do to get there in the first place and so that's what i'm going to do is walk you through how this template works so the first thing that you need to know is that every cell that's highlighted in blue is this is an assumption that you can change without breaking the template so um you know you can put your company name in here you can put your start date now here we're looking for you to enter your projected investment so that's could be your personal investment that you're putting in if you're going to the bank to get a loan they're probably going to ask you you know what are you putting in as a down payment so you may have to put some money in yourself you may also have other investors and you could add those in here as well you can add in your starting inventory i'm going to jump down to this table this is our fixed asset table where you can enter in things like your building so let's say you're leasing your space but when you lease the new space you're going to have to do some renovations we call that you know real estate improvements or building improvements so let's say that's a 60 000 cost then you want to enter in the different equipment that you're going to need to purchase so maybe a lift some tools diagnostic equipment and then probably some furniture for people to to to sit in while they're waiting and so you can see these these numbers here and and you know really these numbers can vary widely incredibly uh widely depending on what kind of size and scope of auto repair shop you're looking to start and so you need to do your own research here figure out what's going to cost for your specific concept and then enter those numbers in here but we've tried to put in some example data and then let's say you're going to try to get an sba loan and so we've entered in an sba loan for 200 000 at an eight percent interest rate over ten years and um next thing we're going to do is jump over to our input revenue tab make this a little bigger here for you so the way we think about revenue is we we start with customers we want to understand how are you going to get a customer um are those customers going to keep coming back to you and that sort of thing and and then how much do those customers spend on a regular basis and so what we're saying here is that we've got 250 current active regular customers so let's say that we're kind of starting out of the gate with 250 active customers and we're saying that we think the shop can only handle a thousand customers regular customers um maybe it's a it's a small shop and so on average you know maybe those thousand regular customers are gonna come maybe four times a year three times a year that sort of thing and so you kind of we can do the math and we figure out that's the capacity that we think we can handle in our shop so you want to put in what what capacity of regular customers you think you can handle now we'll have an advertising budget so probably you may do some mailers you know coupons in the mail for breaks or oil change that sort of thing to get customers in and so we estimate what is the cost to acquire a new customer and then ultimately down here it's going to say okay we're going to get if we spend 500 and it cost us 50 to acquire a new customer we're going to get 10 new customers from paid ads in in the first month here and then we also have the ability to add in word-of-mouth customers so you probably get you know if you do a good work people will talk who's your mechanic you know and and [Music] and send business your way and so that's what we have here and now here we say what percentage of new customers will become regular so say you send a coupon in the mail for a new set of tires new customer comes in to take advantage of those now what percentage of those customers are going to then come back for their break work or when they need an oil change or you know what percentage of them are going to become your regulars that's what we're looking for here so we're saying we kind of hit this steady state of new customers 20 of new customers become regulars and these are just assumptions that we've we've put in here by default but of course a lot of this depends on your your particular model and so you'll need to think through this on your own but ultimately you're going to get your total active regular customers down here now here we're saying how many times per year does the average active customer visit we're saying two and a half times on average and then we're able to calculate your total customer visits per month based on that we know we can put in how many days you're open and then you can put in some different categories of services that you provide and then really here you put in your specific service items pick a category you can see from the drop down menu so let's say if you wanted to change the categories you could change the categories up here and then that would change what categories show up here in the drop down menu then you have to put in the average price for those different those different services your cost for parts and supplies and then the average length in service hours how long do you expect it to take to complete that and then what percentage of customer visits so up here remember we're calculating you've got 85 customer visits so what percentage of those 85 are going to be for each of these different types of services now one thing to keep in mind if you look at this right here if i add this you can see down here right here it says sum is 200 so what that means is that that on average a customer is going to get two services done so this adds up to more than a hundred percent now if you said each customer visit only gets one service you only do one thing for them then what you'd want to do is make sure that these percentages only add up to no more than 100 right right now we have them adding up to 200 percent because we're kind of assuming that you'll you might upsell so you might provide break service but while you're at it you um rotate the tires that sort of thing and so keep that in mind all right so based on those assumptions we're going to come up with our average spend per customer visit now this isn't something that you need to this is calculated for you so you don't need to change this this is all calculated based on what we entered up up above and then we have the ability to add an increase in annual prices as well as increase for supplies and materials once you do that you've got your projected income and you've got that broken down by category as well so the next thing we need to do is jump into operating expenses so you'll see here a table with different typical operating expense line items and the thing that i want to point out here is that you can enter these in as a fixed dollar amount or a percentage of total revenue or even a percentage of your different revenue categories but i'm going to give you this example so let's say this transaction fee this is like your credit card processing fees so we're saying on average three percent we've got three percent uh fee that the credit card company processing company charges um and so you may have a different rate negotiated with your credit card company but that's that's what you're able to put in here and so if you put in three percent put that in as .03 that will make that show up as a as a percentage now if you pick fixed these are fixed dollar amounts so that that's where you want to put in the full dollar amount so you know for accounting let's say you spend 100 a month for rent you're spending 3 000 a month and so on so you can see how you can enter those things you'll want to do research again we're not saying that rent should typically be three thousand dollars a month um that may be for a very small garage you're able to get rent for for three thousand dollars a month you know obviously some garages and pendants on where you're located could be you know significantly more than that per month so do your own research and make sure you you fill in these assumptions ingly and then here on the input salary and owner draw tab you can see the ability to add in job positions you can add specific positions what kind of category of labor they are the direct labor are they general and admin then how many hours per week they work an average annual or an annual salary for the position you've got your employer taxes and benefits and then here you can see that you can pick which month they're gonna start on so if you say they're gonna start at month one they're starting right at the beginning of the projections but here for the second mechanic mechanic number two we're saying well we're going to grow into that so over time as the business grows we're going to add a second mechanic in year two to be able to handle you know the customer base the growing customer base um and actually i'm sorry that should be actually is year i guess month 25 would be the first month of year three actually um that we're starting here so so yeah you can fill all that out you put month 60 that's just uh the end of the five year projection so if you don't plan to uh fire these folks then just put 60 in there and that will add their salaries throughout the rest of the projection and then you can put in the number of that particular employee that you have and so if you had if you hired you know uh two service texts at once you could put two in here and that would add two of those at once all right and so that is really it that brings us all the way back to where we started back at the profit and loss at a glance you can see just based on the numbers that we put in here kind of zoom in here for you you can see that you have a small 54 000 net loss in the first year but then you start to reach profitability and um and you can see the top line growing i'm getting close up to that 700 000 level by year five with uh with a nice uh 18 percent ebitda 13 net income which seems uh reasonable for for a auto repair shop in terms of a net income percentage so obviously again depending on the size of your shop you could have much more revenue than that or less revenue if it's a if it's a small shop so um if you have any questions please uh ask us in the comments and we will we'll keep an eye on those comments in the youtube video comment section and be happy to reply to you that way also you can reach out to us at support projectionhub.com and if you want to get your hands on this particular template that we've been using today we'll link to the template down in the description of the video below alright thank you very much