QuickBooks Analyzing Financial Data: Printing, Exporting, and Quickinsight Graphs

Welcome back to Educator.com0000

In this QuickBooks lesson, we are going to discuss analyzing financial data, printing, exporting and quick inside graphs.0003

The first thing that we are going to talk about now is reporting or actually printing the reports that we have generated.0011

It is a simple process, but we need to go over it so you understand.0017

The previous lesson, we discussed how to generate reports, how to generate the standard reports that comes with QuickBooks,0021

how to customize those reports and make them a little more meaningful for our business.0027

In order to print a report, let's pull one up.0033

The first thing we are going to do is we are going to the Report menu, and by selecting Reports from the Menu bar,0035

we are going to choose just a standard balance sheet at this point in time, so we will just say a Balance sheet summary.0042

So, I will choose the balance sheet summary by clicking on Reports, go into Company and Financial and clicking on Balance sheet summary.0050

It pulls up the standard balance sheet in summary, and at this point in time, I am going to change the date to be at the year-end for this current year.0058

So, under the As of and the date, I will pull down the little icon that has a little calendar to it.0066

And I will select the 31st of December, and I will click on Refresh. Now, I have the year-end report here for a summary of balance sheet.0071

Notice that it does give us the assets that will equal the total liabilities and the equity.0083

QuickBooks does a fantastic job in keeping things balanced and making sure that that balance sheet is balanced.0088

If there is an item that is at a balance and your assets and your liabilities and equity do not balance, there would be something wrong with your file.0094

And it might be a corrupt file, and then, a pro advisor or a QuickBooks professional will be able to assist you in getting that corrected.0102

But, typically, QuickBooks does a fantastic job. I have never seen anything where it goes out of balance.0111

But, if that does occur, then, QuickBooks has a solution for it in their technical support.0117

So, here, we have our balance sheet, and in order to print it, the first thing that we will do is we will notice up in the Menu bar in our Options,0123

within the report, there is our customized report where we can filter items and customize our report and to make it a little more meaningful for us.0130

We can memorize a report, or we can print it, and at this point, we are going to choose the Print option.0140

And within Print, we can actually print it from it and go directly to a printer.0146

I am going to choose Preview, so I can see what it looks like, so I will select Preview, and there is my report.0149

Notice that when I am in the display of the body of that report, I have a magnifying glass with a plus sign in it,0157

which indicates that if I click on it, it would zoom it in or my options above, I can print from it, or I can zoom in, ask for help or close the report.0166

At this point, I am going to click Zoom in. It zooms into the reports.0175

So, now, I can see it a little more legibly, so I can see what it looks like and how it is going to print.0181

If I am satisfied with that, I will go ahead and simply select the Print button. It will print in my printer.0186

At this point, notice the magnifying glass has changed, and now, it has a minus sign inside it.0193

So, if I click on the minus sign, it will resume back to the full screen. I am happy with that.0199

Assuming I printed it, I will go ahead and close it, and now, it goes back to the Print report screen, and I will close that, too or cancel it.0205

So, now, I have done my print on the particular report that I wanted to display.0212

The next section we want to discuss now is processing reports in groups.0219

We have discussed how to create certain reports that might be required by our accountant for taxes.0224

Or maybe a month then, we want to have a series of reports that we conclude at the end of each period0230

or at the end of each month that we might want to have to keep track of our business, sales reports.0236

There might be P&L reports. There might be balance sheet reports.0241

There might be inventory reports. We might have expense reports.0245

We might want to have various or a variety of reports that go into an analysis that management would use.0250

So, at this point, assuming that we have maybe at year-end, we have a certain number of reports.0259

Many times, our accountant will require these reports for us in order for them to prepare our taxes.0265

So, keeping that in mind, 12 months goes by, and after you have created all these reports,0271

if you were on top of things, you would have memorized those reports, put them into a category under the Memorized Report group,0278

and then, you would not have to think about it. The next time, you could just run them.0286

So, assuming we want to run these reports at year-end in a group, we do not have to regenerate them by searching through reports.0290

Under the menu list, we will choose Reports.0298

And again, maybe our accountant is looking for a P&L and a balance sheet and a sales reports or inventory reports, your 1099 reports.0302

So, instead of searching for all these different reports and remembering what the accountant needs,0310

if we rememorize those reports and put it in a list, for instance, Year-end, then, we can print those reports all at once, so let's do that.0316

Under the Reports menu, we are going to choose Memorize reports, and choose the Memorize Report list.0326

Under the Memorize Report list, we will see all the various reports that we have generated and put into groups.0331

In this option, we have all these different year-end reports.0338

We have the balance sheet year-end report, an income tax summary and a 1099 summary that let's say our accountant requires for taxes.0344

At this point in time, we have looked at it, and if we click on Display, we will have all of those reports...0352

After we have highlighted Year-end, if we click on Display, I will do that one more time.0362

If we highlight the group Year-end, and we click on Display, it shows us all of those available reports in that group that we can choose to display or print.0366

At this point in time, we can all leave all of those three reports with a check mark on them, and we can either print them, or we can display them.0377

At this point in time, I would go ahead and display and boom. It will generate all three reports at once, so that we can get all those reports.0387

And from here, we can review them, make sure they are correct and print them.0396

Change the date if we need it to be the end of the year etc., and we have all of those reports generated all at once.0401

And we do not have to have that accountant bothering us and pestering us to get the data that they require, so they can file those tax returns.0409

At this point in time, I will go ahead and close all my windows.0420

So, now, that we have learned how to process through those reports in groups and printing those reports - excuse me -0425

the next thing we are going to talk about is exporting those reports to Excel.0433

Excel is a wonderful tool. If you are familiar with it, wonderful.0440

If not, I suggest getting to know Excel a little bit better because you can manipulate and customize your data in many other ways better.0445

This could be very meaningful and helpful to your business, so we are going to discuss how to export the data or reports to Excel.0455

Now, a lot of times, we might want to be able to sort our data in a different way.0464

We might want to sort it by name. We might want to sort them in more complex ways than what QuickBooks might allow.0472

So, using Excel will help us to manipulate that data into a little more meaningful operation for us, so we can get better management decisions.0480

How to send a report to Excel: the first thing we want to do is we are going to go ahead and look at that.0493

We have various features within Excel that we can utilize.0499

For instance, there are some defaults that we can do in QuickBooks that when exporting to Microsoft Excel, we can utilize this default.0504

There is an Autofit. It sets the column widths in Excel wide enough to display your data without it cutting off words.0514

There is Freeze panes. It allows us to scroll through the data while keeping the row and headers viewable.0520

And there is grid lines. We can turn off the grid lines with an Excel or not.0527

And so, we can customize it that way in viewing, and it can customize our data.0531

We can also have a report already memorized in Excel, and when we run new data, we can actually update that report using Excel.0541

So, first thing that we want to do is we are going to send a report to Excel, so let's get started.0550

From the Reports menu, we want to make sure that we are going to choose Company and Financial.0554

And we are going to choose the Balance sheet standard report. That gives us more detail than the summary.0559

At this point in time, I am going to keep the data, I guess, at 12-15. That is fine.0567

It is the current time, and we are going to modify it just a little bit, so we will sat Customize that report.0571

We are going to add a couple of things. The thing that we are going to add is we are going to look at the dollar change under the Add subcolumns for.0580

We are going to click on the previous period, so we want to see a dollar change and a percentage change for the previous period.0590

So, we are going to look at the current period. Then, we are also going to compare it with the previous year of that period.0598

So, we want to see that, and we will click on OK.0604

At this point now, we have customized this balance sheet to include the current year and then, the previous year's numbers.0608

And then, we can see the dollar change and percentage change, so we can see that our cash on hand has actually increased by 57.9%.0618

Our accounts receivable has increased to 154.9% and etc.0628

So, we can, kind of, look at that. Luckily, our liabilities have only increased by 9.5%, or actually that is our total assets.0633

But, we can see where we are at with our business. When we look at accounts payable, that has increased significantly etc.0643

So, we can, kind of, get an idea where we are at and how we are changing in the balance sheet.0649

But, with this data, we can go ahead and export it to Excel and get some more options for us.0654

Now, we have our report, and you will notice up within the Report are Report headers or Report options:0664

Customizing the report, Share template, Memorize, Print, E-mail and Excel.0671

So, when we drop down that menu for Excel, it gives us a couple options.0678

We can either update an existing worksheet if we have already run this report, or we can create a new worksheet.0682

At this point, we are going to go ahead and create a new Excel worksheet.0688

When we get to the next screen, it is going to give us some options.0694

Again, it gives us the other option. It is either Update an existing workbook or Create a new worksheet within a new workbook.0701

We also have other options where we can update an existing worksheet,0710

replace an existing worksheet, create just a CSV file or a Comma-Separated Value file.0714

And then, we have some advanced options. At this point, we are going to choose Advanced options and look at what we can do.0720

Here, we want to make sure that we have our formatting options done properly.0727

We want to see that there are colors within our formatting. We get a little fun with colors.0733

Under the Excel options, we want to make sure that Autofit is selected. We want to make sure the Freeze pains is selected.0739

We want to show the gridlines within Excel. We want to make sure that, that is selected, so we have those items there.0749

Autofiltering is something that I prefer to have on as well as include the QuickBooks export0756

guide worksheet with helpful advice, so you could read through that, as well.0763

It is a helpful solution there. With this, we can go ahead and click on OK, and here, we can click on Export.0767

QuickBooks will now launch Microsoft Excel so that we can utilize those functions.0781

Now, it has our Excel, our report here in Excel. It is the same report if we go back to our QuickBooks, there is the report.0788

And now, we go to our Excel, and it is the same data just in a different format, so it should look similar to this.0801

Your screen should look like this if you have Excel. If you do not have Excel, just follow along with us.0810

And when you do have Excel, you can utilize these features.0818

So, we have that there. If we wanted to save the report, we could choose Save from Excel, in File, Save and save it there at this point.0822

So, that is how we could go ahead and choose our...oops. That is how we export the data within Excel.0834

Next, we are going to go ahead and continue on with that, and we are going to talk about filtering a report and using Excel.0848

And, here, we have all the data here, and in Excel, we can look at the different0854

dropdown options because we went ahead and said we want to do the filtering.0859

Under the Advance tab within QuickBooks to export to Excel, we wanted that dropdown filters.0863

So, here, we have a dropdown arrow, and we can go to the Dollar change column and drop that arrow down.0870

And we can notice that we have some options. We have all these different dollar figures here.0879

In the Dollar change field, we can go ahead and get to the greater than feature, or we want to select maybe a ten of an option here.0885

We can, sort of, from largest to smallest, or we can choose different dollar values.0896

If we can look at it, we do not want to see blanks maybe. We want to remove the blanks.0903

We do not want to see blanks. We can look at things at an inch.0908

We can also sort it and say we want to see only...maybe we just want to see things that are greater than zero.0914

So, if we want to sort this and just see values, a dollar change value that is less than zero, we can say Less than zero.0924

Let's go here, and we can say Less than, so we can choose Microsoft Excel version 2010.0934

When we say Number filters, we can choose the Less than option, and under the dollar change is Less than.0943

We can just type in zero, click OK, and now, it just gives us a dollar change of anything less than zero.0952

So, it gives us that. It sorts our report to say dollar change is less than zero.0961

It gives us an idea of how that works, and we can make it our data that way, as well.0968

If we want to look at a different, maybe, percentage change, we can clear that filter out by choosing the dropdown menu, go into our Number filters,0973

unchecking that box that says Less than and clear that out and click OK, and it brings it back to our...let me get back here select All here.0981

If we select All, it will give us everything again, so we are back to normal by selecting All.0998

So, by dropping out menu down, selecting All, it brings us back to normal, and we do not have any further filters.1004

If we want to filter just a percentage change, we do not want to see things that are less than zero.1013

And on that, we can do the same thing by going to the Percentage change, go to the number filters and say Less than and say Less than zero.1020

It will give us similar information, OK?1032

In order to clear that, in the Percentage change file, we will choose the dropdown menu, choose Select all, click OK, and it will give us everything again.1035

Everything you will notice if you drop it down again that Number filters is not selected again.1045

So, we can manipulate our data, look at things and analyze it in different ways.1050

At this point in time, we are going to close down Excel, and we will go ahead and not save the report. I will close this down.1056

It will say Do you want to save? And I will say Don't save, so it is going to close it down.1064

The next thing that we want to talk about now is creating quick insight graphs.1072

Now, QuickBooks has a feature where we can actually graph some of our data and display it in a graph mode, which is rather helpful.1078

We can look at income and expense graphs. We have sales graphs.1088

We have accounts receivable graphs. We have accounts payable graphs, net worth graphs.1092

We have budget versus actual graphs, so we can look at our data and a graph will go stand point.1098

And that, kind of, gives us a better picture sometimes versus just looking at plain numbers black and white.1105

If we see it in a pie chart or a bar graph, it can, kind of, see where the trends are, and we can draw a line on that.1110

The first thing we want to do is we are going to create an income and expense graph.1120

At this point, I am going to go ahead and close all my windows down by clicking on Window and selecting Close all. Then, it will close all my windows down.1123

Now, let's go ahead, and let's look at suppose our business, we want it to be very profitable.1131

We need to keep track and keep an eye on our expenses.1138

So, we should be able to be especially concerned with the proportion that we are spending relative to the income that we receive.1141

A lot of times, if you are at a business, that you are doing jobs, and each job has cost related to that job,1148

that you might want to look at that job and see how much you are actually making.1155

Are you making a profit on that particular job or not?1161

A lot of times, people are not paying attention to that, and when they get into the numbers,1163

they realize that they are actually spending more money trying to make that money.1167

And so, it is not very profitable, and they are not bidding things properly.1171

If you are earning $20,000 in income, and we do not want to spend $30,000 in expenses for that, so we want to keep it within budget.1176

So, let's go ahead and create an income and expense graph, so that we can see things visually.1185

From the Reports menu, we are going to go down to Company and Financials.1190

And we will notice towards the bottom, we will see an income and expense graph.1195

So, we click on that, wa-la, it is going to give us a report with the pie chart and a bar graph.1200

And it is going to give us our income in green and our expenses in red.1207

And we can see where our expenses per period are really lining up and how we are doing.1210

In this case, this business is increasing in their revenues, and their expenses are increasing, as well, and we have a little trend analysis for that.1217

If you look at that, also, you are looking at just those periods within the year.1226

But, your pie graph is only showing a certain number of your expenses because it is not large enough to create all the categories.1230

So, you can actually go to the next group.1241

If you will notice, within this report, this bar graph, we have various options up here, and you will see it says Next group.1244

Over here in the specific expenses are related out, and they are pied out here.1252

And the key is on your right, so it says Job expenses, Payroll expenses, Cost to get sold, Insurance, Automobile, etc.1259

So, you can see the Automobile expenses right here, and we have our tools and machinery in red,1266

which is this little sliver over here, our utilities, this tiny little blue sliver over here.1273

We have this little pink one. This is printing and reproduction with the tiny little sliver over there.1278

So, it, kind of, gives you an idea in a pie chart where everything is, but the other expenses here is 81%.1283

So, let's go to the next group, and it will show us additional.1292

Here is depreciation expense in relationship to these items, as well, and this is the next group of expenses within that percentage there.1295

So, we have our expenses, kind of, broken out.1306

So, I always find that the bar graph gives us a nice trend and see where we are1311

at in growth and income and in our expenses in relationship to that net income.1317

And it looks like we are getting a little more profitable here as we have higher volume, so that is our quick insight in income and expense graph.1321

The features in QuickBooks, quick insight graphs or various reports here.1333

Every graph window except for the net worth and the budget versus the actual graphs1340

shows a bar graph on the top, a part of the window and a pie chart in the bottom.1345

The bar graph will usually show the totals for both income and expenses, and, of course, the pie chart breaks it out by expense category, and that is nice.1350

And that is expenses. We can always choose down at the bottom, it has an income tab.1366

So, if we click on that, it will just show us income, what our income is broken out as in more detail.1371

Still, it gives us the same bar graph, but the pie graph gives us a little more detail in that we have construction income, which is 99.06% of all income.1378

There is reimbursement income, other income and interest income, and of course those are the tiny little slivers that we see over here in this pie graph.1389

So, that pie chart, kind of, breaks down those income and expenses.1396

That is helpful if we have various revenue streams. This could be a nice visual for us to break that down because these are smaller categories that we have.1403

We do not have as many as the expense categories are shown here.1413

Every graph has a button bar up above that we can click to customize a graph, and we can choose it by class, by customer.1419

We can look by account etc. by date. We can change the dates.1428

We can customize it to get a better picture of our pie graphs, OK? Oops, here we go, get back there.1432

So, that is our quick insight graphs, and we are going to now, kind of, look at customizing graph data at this point1446

in time now that we have created an income and expense graph, and we can see what those look like.1457

Now, we are going to talk about customizing graph data when we have that data in there, so let's list that down.1462

Being back in QuickBooks, we are going to close this particular report down.1471

And actually, I will just keep this one up since we are going to be using this one. Keeping this report up, we can choose it by customer.1477

For instance, let's look at the graph data, and we can choose the customer. Right now, it is by account.1486

We can say it by customer by clicking on the option up there, the upper left.1492

By customer, it will give us an overview of the income and expense by customer, and it will give us more data with that.1497

So, that customizes it for us, and if we wanted to zoom in on any one of these months,1505

we can choose and say "Well, this was a pretty good month here”, and I can double click on the expense here.1510

And it should open up a little more detail for me as our system is way laid.1516

So, now, it will give me a graph of just that particular period of time.1524

By double clicking on that, it will see what my expense summary is etc., the overhead or the 100% on that.1528

So, that is a helpful little tool, we can zoom in, and anywhere we see that magnifying glass with that Z, it will zoom in on us.1535

And it will give us the further detail. We can do it by class, as well in the quick zooms.1543

So, as we see, that using quick zooms and graphs, it gives us the idea and more information if we can see that.1551

The next thing we want to do is we are going to go ahead and look at the display sales for Anton.1563

So, we are going to pull up more of a zoom on a particular customer here.1569

By customer, we will click on that, and we will look at just a particular customer for a month, and we will try to find that.1576

I am going to go to June and double-click on my income and see where we are at with that.1589

And here, we have in June, I have a variety of customers for income, so if you choose in June,1597

that might give you the same graph that breaks up the income by various customers.1604

I am going to choose Chris Baker here since he is a larger portion, or I can even look at Jennifer Ron at 67%.1611

So, let's look at Jennifer Ron right now. I will double click on Jennifer Ron, and it will pull it up.1620

And I can see the detailed transactions for her and the invoices that we have sent out to them or to Jennifer Ron.1626

And we could see how that income is broken out on a more detailed display.1637

Then, if I am curious about a particular invoice, I can go ahead and find that invoice, double click on in, for instance, on this 12,000.1643

I can see it. It has been paid.1650

And I can see the details of what that invoice is all about that we did some floor plans, installation and labor etc. and removal.1652

So, that gives me a detail of that particular customer.1661

If I want to look at another one, I will find down here Jeanette Mimeo, the red one. I will double click on Jeanette.1664

It will give me the detail of the invoices and income that we generated with this particular individual.1671

Or I can look at the individual invoices from here, too.1677

This is a credit card charge that they were able to pay their bill with.1682

So, that gives me a little further information on that, and I can see how they have been paying their invoices there.1689

The next point, that is how we can quick zoom on graphs just like any other report that quick zoom is available.1700

And we can utilize that to our best ability.1707

The next thing we are going to talk about is customizing how graphs will display, and we can look at them in 3D or 2D.1710

At this point, we are looking at 3D graphs, but we can actually change them into maybe a 2D.1719

Maybe this does not give me all the slivers that I need on this particular graph, so maybe I want to look at it in 2D rather than in 3D.1726

So, in order to do that, I need to go to my Preferences.1733

So, I am going to choose from the Menu bar the Edit function and go all the way to the bottom to Preferences.1735

And under Preferences, I can go down here and see on the very left column where Reports and Graphs is, and I can click on Reports and Graphs.1744

And I will notice that I have drawn graphs in 2D as an option on the upper right section.1754

So, I will choose that, and it will give me a 2D graph, so I am going to click OK. It will change this graph to 2D.1762

Now, I can see those slivers a little easier since there is more information, more data, smaller items.1771

I can see those smaller slivers in here a little more clearly, and it gives me a better view of that.1779

In the 3D mode, it was harder to see these other smaller income clients.1785

So, that is how we do that in Displays.1792

This is QuickBooks reporting, displaying, printing, the quick insight graphs.1797

I hope you enjoyed this, and we will see you again next time at Educator.com.1805

Thank you for visiting.1809