A budget is your projections and hopes for the coming fiscal year. Creating a budget is a great way to plan ahead and forecast so you can set goals for your business.
At the end of the year, it’s always a good idea to compare your forecasted budget with your actual numbers. This allows you to see how accurate you were and plan ahead for next year.
I’ll show how to take your Quickbooks data and export it into Excel to create a budget. Then compare that budget to your actual numbers.
We’ll start with exporting the Profit and Loss report from Quickbooks.
To get to the Profit and Loss Report click on “Reports” on the right-hand side of your screen. Then select “Profit and Loss.”
If you can’t easily find the Profit and Loss report you can use the search box.
In this example, the default report runs from January through December and only shows us the total for the year. But, we want to have a budget that takes into account the highs and lows of the business throughout the year.
To do this we’re going to change the column display from “Total Only” to “Months” and click “Run Report.”
Take a quick look over your report and make sure everything is the way you’d like and that there are no mistakes. You’re good to export when everything is good enough that you’d feel comfortable using this data to file your taxes.
To export the Profit and Loss scroll up to the top of the report and find the little icons in the right corner. The icon of a box with an arrow is “export” click this icon to export to Excel.
When you open the Excel file you may see a yellow ribbon at the top and you’ll need to click “enable” to view the file.
We’ll start off making a copy of this report. Right-click on the sheet name at the bottom of the screen and select “Move or Copy.”
Then check off “Create a copy” and click “OK.”
You’ll now have two identical sheets in your document. Let’s rename the one we’ll use for our budget. In this example, I renamed it “Budget 2020.”
You can now update all the numbers to reflect your monthly budget/estimates.
To compare your budget with your actual numbers we’ll copy “Budget 2020” just like we did with the “Profit and Loss.” Now rename the new sheet “Budget VS Actual.”
We’ll add three columns between January and February. January will be labeled “Budget” and then we’ll have “Actual,” “Over/Under” and the final column will be a blank spacer.
For this example, I’ll pretend this is a budget for 2019. I’ll use the 2019 Profit and Loss report we exported earlier for our actual numbers.
I’ve opened the Profit and Loss separately so we can see the two spreadsheets side by side. This allows me to easily copy over the numbers.
Cells that contain formulas like “Total Expenses,” “Net Operating Income,” “Other Expenses,” and “Net Income” can be copied and pasted from the Budget columns to the Actual columns. This will carry over the formulas from those cells.
In the Over/Under column we’ll subtract the Budget from the Actual column.
In the example we can see I went over budget by more than $200.
But, in the end my Net Income is over budget by more than $8,000. I projected I’d spend more in January than I brought in but I actually ended up making money.
Now you know how to export your Profit and Loss report in QuickBooks so you can create a budget and then compare that budget to your actual numbers. You can watch the video tutorial below to see me walk through this process step-by-step: