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

Written By
Budget

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. 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
Share on facebook
Share on twitter
Share on linkedin

Leave a Reply

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

Read similar articles

How To Record Deposits and Fees in QuickBooks

In this training, you'll learn how to record a deposit for a sale made through a processor like Stripe or Paypal. These types of transactions include fees that should be entered into QuickBooks.

How to Track Hours Worked Using QuickBooks Timesheets

In this tutorial I'll show you how to use QuickBooks timesheets to keep track of your time worked and make sure you bill your time back to customers and clients correctly.

How To Reset Passwords in QuickBooks Desktop

In this training you'll learn how to reset the password in QuickBooks Desktop as both an administrator and a user so you'll never be locked out of your account.