Financial Forecasting: Making Sense of Your P&Ls
Hello and welcome to Financial Forecasting: Making Sense of Your P&L’s, or profit and loss statements, in other words. I’m Bess Ogden and I am looking forward to walking you through this spreadsheet that we’ve created for you that will help you translate the profit-and-loss perspective, which your accountant is providing for you, to a cash flow perspective that you need in order to manage the practice operationally. So, please download and open the spreadsheet and save a copy of it to your folders so that you’ll be able to customize it on your end. But, just take a few minutes here to walk through the tool with me, so that you know where to put your attention and where to spend your time.
We’re starting on the Original Analysis tab as you can see and we’re going to start with historical data. In this case we’re preparing, since I’m recording this for the year 2022 so what you’ll need is your historical data for 2020 as well as (once the year is completed) your historical data for the year 2021. I’m recording this in November of 2021, so we’re not quite there yet but you’ll be ready to go once you get your final profit and loss statement for this year.
You start by just putting in your existing profit-and-loss information just as it is created by your accountant. It may or may not need some reorganization and attention once we get it all in here, but don’t worry about that yet. That will come down the road as you work with your business advisor here at Williams Group. You’ll notice in all of our spreadsheets this yellow-colored cell and that’s your cue that this is where you’re going to need to enter data. If the cell is not yellow, then it is locked and in general you do not have access to change the data because it’s already including formulas, and you just need to let the spreadsheet do its work here for you.
So, here’s your first step, which is entering all of your income from the prior two years, your total cost of goods, your total fixed operating expenses, your total staff operating expenses, variable operating expenses, and then below the line here, your eye care provider compensation which includes both Owner Eye Care Providers and Associate Eye Care Providers. That’s how we view the expense to pay for Eye Care providers, that you are the producers, so we’re going to put you below the line in terms of operating costs in our perspective. Now, the perspective for your accountant is for tax purposes and although it will pay attention to that to some extent, our job is not to help you with your tax return preparation. That’s between you, your certified professional accountant, and your financial planner. We are going to assist you with the operational and cash flow management side of the equation. Two very different perspectives, so keep that in mind.
So, you’ll enter your data for 2020, then you’ll enter your data for 2021. Once you have that, below the row 39 here, you’ll start to see some practice metrics that we find very important. One is calculating your chair cost, you simply put in the total hours that Eye Care providers worked in the clinic, and that’ll give you chair cost. Receipts per patient is one of those critical practice stats that we’re always going to keep available here on the main page for you. So, you just enter your total comprehensive exams in the United States. Those fall under these CPT codes, and will be different in different countries such as Canada, the Bahamas, the UK, etc. You’ll have different codes you’ll use there to count that up. Staff productivity per hour is another practice management stat and this is where you’re going to enter all the staff hours actually worked, not including Eye Care provider hours. Below that, we have contribution margin, which is a practice metric that we use to plan for New Capital expenses and I’ll talk about that one later as we move through the spreadsheet. Below this are just some industry benchmarks that we use at Williams Group and this is here just for easy reference for you.
Once we’ve set up the Original Analysis tab, you’ll move to the Initial Budget Calculation Tab and we’re going to be entering a little more detailed information from the year 2021 (the last completed year). So here is where you’ll break down month by month. What were your cost of goods? What were your operating expenses? What were your collected receipts each month? What were your total sales per month? How many ECP days were worked? And that means an 8-hour clinic day, so sometimes you need to count up your hours and then divide by 8 in this case. So, we’re going to put in ECP days from the prior year, meaning in this case 2021, and then projected ECP days for the year 2022 at the time of this recording. You’ll also have some places where you can put in a desired growth rate. You can even plug in some new capital expenditures that you’re anticipating in order to create reasonable goals for your team or for yourself as you move into the new year. As in many spreadsheets, as you enter data it will pre-populate formulated information. So, we’ll have projections on if you met your goals, what would your receipts be, both per ECP day, as well as for the total month. So, lots of great data will begin to populate as you enter your historical data here.
And then it’s just a matter of each month, going into your month tabs, and again you’re only concerned with the yellow cells in this case. In most cases, you’re just entering information in column D of each month tab. What were your actual gross sales? What will your actual receipts be? We have a place here to manage your total accounts receivables. Then you’re going to just plug in your actual expenses for cost of goods, fixed expenses, etc., for each month. And you’ll continue on as each month goes by. You’ll work with your business advisor here to look at what was our goal and then what is our actual as the year progresses. And we’ve got a column here where you could compare to last year and see if you’re on the right track, maintaining areas where you had good control, or perhaps identifying areas where you need to pay attention and get better control.
So, looking at a blank template is not very interesting, so I did create a filled-in template for you as an example so you can see some data. So, in this case, I’m creating a fictional business and I’m pretending that the year 2021 has been completed. So, this office has entered all their information for those two prior years. You can see as we go down, there are places in each section of expenses where we’ve identified industry benchmarks for you to pay attention to and then you can see if you’re falling within the benchmarks and move on from there. It’s good to realize that once we move off the Original Analysis tab, all of the calculations will be pulling from column E, the most recent complete year of financial data. The only reason we have the prior-year here, is so you can see a little summary of one year compared to the next.
So, we entered the data here in our fictional business, and you can see that in this particular practice they gained some net profits before any ECP compensation. And are also within our industry Benchmark here of 30% to 40% profits before ECP compensation. And then you can see down here in this case it’s in the US, so they listed the doctor compensation and then the final profit at the bottom here. We note the number of months on the budget here, which is 12. Sometimes you’re actually creating this tool in the middle of a year and that’s okay because we can actually adjust the information and column E to reflect 8 months of data, or six months of data, or 2 months of data. In this example, I’m going with 12 months just so it’s easier to see how this works out. We’ve entered some clinic hours here and come up with a chair cost. We’ve entered the number of exams from the full year and come up with receipts per patient. We’ve got a staff productivity per hour that is falling within our range of normal. And then we’ve got a calculation for a contribution margin that we’ll be able to use to plan for new expenditures moving forward into 2022. On the Initial Budget Calculation tab, we’ve entered the prior years, full-detailed, information such as cost of goods, operating expenses, collected receipts, sales, the prior-year’s ECP days, and then what we’re anticipating moving into the new year. We’ve set it up with a growth rate desired in 2022 of 8% per ECP day collected receipts, not over prior month. It’s broken down by ECP days. Obviously, the number of days that ECP’s are working will influence the outcome in terms of income, so we want to make the growth rate apply to the doctor days or ECP days that are worked.
In this example, I’ve entered a new capital expenditure and I made a note over here that we’re adding, in this office, a new medical technician in 2022 and that were anticipating the total additional expenses for that new person to be about $3,000 a month. So, we’re building that in to create goals for the office in terms of receipts, down below. This is applying the contribution margin to that new capital expenditure, so that the goal allows you to maintain your net profits. We don’t want to add a new expense and reduce your final profit. So, this protects and buffers your profit into the new year. We can see that if we hit our 8% growth goal, plus cover this new expense, we can anticipate projected receipts by the end of the year of 1.2 + million dollars in this particular example.
So, in this spreadsheet, we are now set up to begin entering our data in the month of January 2022. We already now have pre-populated goals in column B and C, and we are ready to go as we begin to enter actual data in January. I want to point out a couple other month tabs, or I should say tabs that are available on the spreadsheet just so you know what they are. The monthly tab is a data review tab. It’s not meant for actual management of cash flow within your practice. However, it’s a really great snapshot that allows you to see, for instance, if you are missing data for a month. I’ve had this happen where for whatever reason there are no cost of goods entered for a particular month. It just got deleted somehow as we moved forward in the year. So, this is a great way to just overall see if we have data in the right places for every month in the year. I’d say that’s the best use of this particular tab, not really for practice analysis.
It also is going to help you set up your new spreadsheet as you move into a new year. It just makes it really easy to copy and paste data, and move it on over. The final tab that may be of use to you on this particular template is our Williams Group Traditional Bonus System. This may or may not be the system that you want to use, but we do have it here for your references if this is something that you like to use. And basically, we are setting a monthly target for the team based on receipts. Then this will track if the goal is met or not and how much bonus you will be paying out to the team members if they achieve the goal either by the month, or as a total quarter. It’s just a convenient way for you to see the results if you’re going to use this to create your Team Bonus System. We have other ways that we can set up bonus systems and you can certainly talk with your business advisor, or your executive management coach to determine what’s the best team bonus system for your practice.
So, you are ready to go! You’re going to download, if you haven’t already, your template. You’re going to go to your Original Analysis tab, gather the appropriate profit-and-loss statements and statistics, and get it set up! Let us know if you have questions. Good luck!