How to Create a Budget Spreadsheet in Excel

Creating a Business Budget in Excel

Does creating a budget, often called a forecast, in Excel (with or without templates) sound too difficult or too intimidating? It can, but this article should help! I put together this Excel budget template and budget building guide to help business owners understand that they DO know their numbers, even if they have been hiding from some of them for months. Click below to download the spreadsheet template so you can follow along! Let’s start by talking about your business.

Download the Excel Template

Step 1: Revenue

Answer the set of the questions below (as many as are relevant to your business and easy to answer):
  • How many units of product or service do you sell in a month? How much do customers pay for your product? Now, open a spreadsheet or a calculator and find the answer to this formula: Units/month X price/unit = revenue/month
  • How much revenue do you have in an average month? If you know this number, write it down.
  • How many customers do you have? How much revenue do you get per customer in a month? Now, open a spreadsheet or a calculator and find the answer to this formula: Customers X revenue/customer/month = revenue/month
Step 1A: Answer this question: Is your business seasonal? Do you get more revenue in certain months of the year than other months? Great, now you have your revenue number. Plug that into the revenue section of the Excel spreadsheet. If you have multiple streams of revenue, use the questions above to determine the revenue for each line of business.

Step 2: Cost of Goods Sold

These are variable costs. If you sell more units of your product or service, these expenses go up with revenue. If you are selling french fries, the cost of the potatoes would be part of this number. If you are selling jewelry, the cost of the direct labor to produce a piece of jewelry and the cost of materials for the jewelry are some of your costs of goods sold. I recommend writing out each major cost line item and tying it to the dollar value of revenue by month. For many businesses, this will be expressed as a percent of a particular line of revenue.

Step 3: Operating Expenses

What does it take to run your business? If you are an established business, go into your accounting system and pull a financial report. Look for the expenses section and see what your business is spending money on to make sure you don’t forget anything. Budget time is not the time for guessing what you spent last year on rent, take the time to look it up. No mental math! Look up the actual numbers.

Once you have an idea of what you spent in the last 6-12 months, think about the changes that have happened in your business. Now, write down for each expense line item what you expect to spend this year during each month. Then, fill in the rows in the Excel budget template with the correct expenses for your business for the next year.

Step 4: Other Operating Income/Expenses

Fill in the amount your business will spend on things like interest expense and taxes for the next year. It is good to project taxes as a percentage of operating income. Ask your tax accountant the right rate to use for your business if you’re not sure. Only project income taxes for months where operating income is positive, you are less likely to pay typical income taxes when you have losses.

A note on taxes: It isn’t a bad thing to pay taxes, taxes mean your business is profitable. Profit is a good thing.

Step 5: Net Income

Take a look at the Net Income row in the Excel budget spreadsheet. Is the number positive? If not, take a look at Steps 1, 2 and 3. Is there anything you can change in those numbers (you know what you can realistically change in your business, trust your gut), to make those numbers positive? If you want, make a copy of the spreadsheet to play around with the numbers to see what’s possible.

Once you are done adjusting things, save your budget. Talk to your bookkeeper and ask when the books will be closed every month. Then, set a calendar reminder every month for the day of the week after the books are going to be closed to review how you did against your budget.

A note on cash: What about cash?? I’d give you tips on how to forecast a balance sheet in Excel with formulas, but half of the professional CFOs I know struggle with projecting balance sheets in Excel, so we are going to skip it. It’s not that the math is hard, it’s that the formulas are confusing and easy to mislink. The key to projecting your cash flow is knowing how quickly your customers pay you and how quickly you pay your bills. It’s best to calculate this historically. Then, use the number of days aging (days it takes someone to pay you or you to pay them) historically to project the speed revenue is converted into cash in the future.

Step 6: Reviewing Your Budget

Each month, pull the financial reports from your accounting software and match the categories up to the categories you budgeted for. Did you hit your revenue targets? How did you do on total expenses? Way over budget? Right on target? Was the net income number positive like you expected? If not, where was your budget off?

Reviewing your budget is the real key to successful budgeting. Building a budget isn’t enough, you need to know where things went “not according to plan” to better understand your business and make realistic assumptions going forward. Once you know what can and can’t be done (cutting your employees off from coffee to save a few pennies maybe didn’t work out?), you can work toward a realistic plan that hits the profit goals you want to hit.

What Should My Profit Goals Be?

That’s really up to you, but I recommend dreaming big and targeting 10%+ of revenue if possible. Your business needs to be a good investment for you and any investors you have. First, make sure you and your key employees have sustainable wages (meaning you all can keep working there), that you are able to reduce your debt load (if applicable), save up a rainy day fund (start with 6 weeks of payroll as a target) and get a return on your investment of blood sweat and tears in your business.

Yes, it makes sense to forgo profit for growth or traction for a while, but no business that is unprofitable or isn’t able to pay its founders and operators (or investors) will last in the long run.

I’ll be honest with you, the most difficult part of budget building is checking in every month to see how things are going. The reason is that it’s a painful process to pull your financial reports and match them up to your budget. Also, cash projections (which are in this template but took me, now a CFO for over 5 years, 2 years of investment banking to start to understand) are not easy in Excel.

Budgeting Made Easy

The good news? There IS an easier way. We built AutoCFO just for you to address this exact problem. Whether you wish viewing your financial data only took 30 seconds or you find projecting balance sheets to be painful (or maybe both!), we have a solution for you. Get set up with your free trial of AutoCFO today to get started. Once you’re logged in, fill in the budget (just like above), save, then when you want to check how you are doing, simply hit refresh to update from the data in your accounting system and instantly see how you did.

Happy budgeting.