My budget spreadsheet builds your profit and loss statement for the next year by predicting the sales and expenses for each month of the year. Then, every month, you can compare your actual performance to the budget and see where things are getting off track.
Start with Past Performance
Export your Profit and Loss Sheet (P&L) from Quickbooks (or whatever accounting package you use) for last year; ask for columns by month. Your P&L lists all your income and expenses for the year and helps you to see where your money is coming from and going to. You should have your chart of accounts down the left side with the entries for each month running across the page. Put that in the “Last Year’s P&L” tab.
1. Predicting Sales
The next thing we need to do is to forecast sales.
In most service businesses, the easiest way is to list your ongoing customers down one side of the spreadsheet with their expected monthly payments out to the right. Of course, you will likely land some new clients, so add them in as Client1, Client2, etc. I know you don’t know how much those contracts will bring in — use your average contract size. Or estimate some are small, some medium, and some large.
List these in the Sales Forecast Tab.
2. Predicting Payroll
The next tab is all about your payroll. Payroll is usually easier to forecast! Add in your current employees and their paychecks, then think about any new hires you might need to support the sales you’ve forecasted. Don’t forget to include raises!
These go in the Roster tab.
3. On to Expenses
First, create a formula that copies the sales from the sales forecast tab to the sales line in the P&L. Do the same for Wages from the roster tab to the P&L.
In the “This Year’s Budget” Tab, we want to copy the chart of accounts from your historical P&L and paste it down the side of this tab.
Every expense on the P&L has something that “drives” it. So office supplies go up or down depending on the number of employees. Same with the phone bill, computer expenses, etc. Other expenses like marketing, meals, and travel vary with sales. Lastly, there are some fixed expenses, like rent, utilities, and accounting fees.
For each expense on the P&L, decide what drives it and enter that in the “Drivers” column.
Then, in Last Year’s P&L, calculate the ratio of that expense to its driver. So, if you want to know the factor for office supplies, you’d divide last year’s office supplies expense by last year’s wages to get the factor for this year. Do this for all the expense categories and copy those factors into column G in the spreadsheet.
Now, figure this year’s expenses by multiplying this year’s driver times the factor for that expense, and you’ll have a prediction for this year’s spending in that category, for fixed costs, add in a little inflation.
4. How to use your budget
During the year, as your sales forecast changes, update the sales forecast tab. Do those changes make you want to change your hiring plans? Update your roster accordingly. Then, when you look over at the budget, you’ll see what your profit looks like with those assumptions.
As you get actual numbers at the end of each month, you should copy those into the “This Year’s Actual” tab. That tab always shows you the YTD actual plus budget for the rest of the year. Now, you have a running total of your profit prediction for the year.
Truthfully, making a budget is a lot to explain in one email. There are strategic decisions to make, and it’s tough to do on your own.
If you need help, I’ve got an e-book with a detailed step-by-step (more than I could put in this email).
This is my #1 tool for business transformation!
You likely get it at this point — having a budget is important if you are growing your business! Maybe there was a time when you could keep all this stuff in your head – when your gut instinct was good enough, but that time is likely in the past.
Too much is changing! Your customers are asking you for different things than they were before — how should you price it? Your team is changing — how should you pay them? Your company needs new tools — are they worth it?
But I urge you. Don’t hesitate! Make a budget today (even if it isn’t perfect) to make better decisions all year long.