How to Create a Budget in Excel From Your Quickbooks Profit and Loss Report

Written By

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.”

Navigating to the Profit and Loss report in Quickbooks Online.

If you can’t easily find the Profit and Loss report you can use the search box.

Searching reports for the Profit and Loss in Quickbooks Online.

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.”

Changing column display in Profit and Loss report in Quickbooks Online.

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.

Exporting the Profit and Loss report to Excel in Quickbooks Online.

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.”

Copying an Excel sheet

Then check off “Create a copy” and click “OK.”

Copying an Excel sheet.

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.”

Renamed Excel spreadsheet.

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.”

Budget VS Actual spreadsheet.

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.

Inserting columns to compare our budget vs actual data in Excel.

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.

Adding Profit and Loss data to our Budget comparison spreadsheet in Excel.

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.

Copying cells with formulas in Excel.

In the Over/Under column we’ll subtract the Budget from the Actual column.

Creating formula for the Over/Under column in Excel.

In the example we can see I went over budget by more than $200.

Comparing data in our budget, here we have an overage.

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.

Comparing line items in our budget, in the example we have more net income than projected.

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.

☕ If you found this helpful you can say “thanks” by buying me a coffee…
https://www.buymeacoffee.com/gentlefrog

You can watch the video tutorial below to see me walk through this process step-by-step:

Need some one-on-one QuickBooks assistance?
Share Now

This content is for information purposes only and should not be considered legal, accounting, or tax advice, or a substitute for obtaining such advice specific to your business. Additional information and exceptions may apply. Applicable laws may vary by state or locality. No assurance is given that the information is comprehensive in its coverage or that it is suitable in dealing with a customer’s particular situation. Gentle Frog, LLC does not have any responsibility for updating or revising any information presented herein. Accordingly, the information provided should not be relied upon as a substitute for independent research. Gentle Frog, LLC does not warrant that the material contained herein will continue to be accurate, nor that it is completely free of errors when published. Readers and viewers should verify statements before relying on them.

Leave a Reply

Your email address will not be published. Required fields are marked *

Read similar articles

How to Use the New QuickBooks Online Invoice Layout (2024)

Learn how to Use the new QuickBooks Online invoice layout.

Have a QuickBooks / Bookkeeping Question?

Let me know what you’d like to see in future videos, blog posts, and courses.

Review Your QuickBooks

Are you a small business owner struggling to make sense of your QuickBooks data?

  • Discover how to spot costly mistakes in your bookkeeping and fix them quickly and easily.

  • Save time and money by streamlining your bookkeeping process and reducing errors.

  • Learn how to identify trends in your financial data so you can make smarter business decisions.

  • Get organized and stay on top of your finances by tracking expenses, managing receipts, and closing your books effectively.

Sign up now for this free mini-course and start mastering the essential bookkeeping skills that every small business owner needs to succeed!

FREE Mini Course

Sign up now and enjoy the peace of mind that comes with knowing your bookkeeping is accurate, up-to-date, and free from costly errors.

We won’t send spam. Unsubscribe at any time.