Tips and Tricks for Creating Advanced QuickBooks Reports
Get the latest QuickBooks help from Hector Garcia and Michelle Long.
Michelle Long: Thank you guys for joining us. My name is Michelle Long and I'm actually up here in Toronto today. I was at the Slater conference earlier this week and yesterday I flew up here to Toronto and I'm presenting the QBO Road Show up in here and Canada, so I'm glad you guys could join me. I'm in a hotel room coming to you from Toronto today. You guys know a lot about me. I just want to remind you to join the LinkedIn group, tinyurl/qbgroup. It's a great resource when you have some questions. Hector, would you like to introduce yourself?
Hector Garcia: Yes, my name is Hector Garcia. I work and live in Miami, FL. I'm CPA advanced ProAdvisor and I'm glad to be here.
Michelle Long: Glad to have you. Now, Hector I saw you just a couple days ago at the Slater conference. You were gonna be presenting on Wednesday afternoon. How'd that go?
Hector Garcia: I presented yesterday on importing data from Quickbooks into, from Excel into Quickbooks. I was yelling at the Blackjack dealer the entire week so I have no voice.
Michelle Long: I wasn't yelling at a Blackjack dealer but I lost my voice too cause I love all the networking that we get to do with other pro advisors and accounting professionals from all over the place. It's really great to connect and network with people. I sure had a good time. If you guys could try to come to a conference sometime and meet us, we met lots of people that are attending these QB Power Hours. It's always fun to meet [inaudible 00:01:31]
Let's go ahead and move on. Let's talk about [inaudible 00:01:34] CPE process. I'm gonna talk about reporting and advisory services. I'm gonna talk briefly about Quickbooks Online. Then Hector's gonna talk more in depth on some reporting topics in the Quickbooks Desktop. Our special guest ties right in with our topic today is James Walter from Finagraph. You're gonna learn about some dashboard and some really quick and easy ways that you're gonna be able to see finance ratios and things like that.
First of all the QB Power Hour, we're glad to have you all joining us today for this. We're leading these webinars about every two weeks. It's gonna be off a little bit because we have Thanksgiving in the US that's coming up in a little bit. Generally we do them abut every two weeks. We try to talk about different things with both the Quickbooks Desktop and Quickbooks Online, as well as have a special guest to help you learn more about some third party apps, practice management or things like that. As always we welcome your feedback and try to listen to what it is you all want. It's about meeting your needs and helping you learn more about Quickbooks and grow your practice.
Hector, you want to tell them about the CPE process?
Hector Garcia: Sure, we apologize that we've had to push it back one more week. There was the Slater conference in Las Vegas, so we have not done the CPE for the last seminar, for the last Power Hour. However, in a nutshell, you must attend the entire hour and you must answer the CPE question correctly when the CPE key question comes up. In a nutshell as long as you do that you should be able to receive your certificate.
Michelle Long: Very good. A couple of upcoming events and announcements that i wanted to make you aware of. First of all, at IntuitAcademy.com there are free webinars that go on all the time to help you learn more about Quickbooks Desktop or Quickbooks Online. I wanted to also let you know about the QBO certification training. There will be a road show where we're going to be going around to various cities and you can come and attend a cert prep training in live. Hector, I know is doing some of the ones in the south, down in Miami, in the FL area. I'm doing some on the west coast. If you go to that link there you'll find where those cities are and you can register for one live. If you can't attend a live city there are webinars and there's' recordings to do self study online as well.
Intuit is also hosting another academy ... Accounting v con. That's coming up in December. Again, you can access that through in IntuitAcademy.com. Then, also, I wanted to let you know that the new certifications will be in the course locker December 3rd. When you log into the pro advisor website, go to the certifications center on December 3rd. You should be able to access the desktop certification, online certification and the brand new, Quickbooks Online Advanced certification. I wanted to make sure you knew about that because I'm gonna mention that with some of the ratio and financial analysis stuff that we're talking about today. Again, most all of this you can access through IntuitAcademy.com or through your ProAdvisor website. You log into the ProAdvisor website.
First of all, I'm gonna talk a little bit with you about Quickbooks Online. We're gonna talk about customizing of the reports and also then deal with some of these advisory services and such.
Let me get into Quickbooks here. You can see I am QBO. I opened up my reports over here. A few things that I wanted to make you aware of that you can do in QBO, because a lot of us are familiar with the Desktop, but we may not be familiar with Quickbooks Online. When we're working with the reports and we're wanting to do some analysis of the data. Remember, now as we get away from doing the manual data entry, with those automatic data feeds and everything that we've been talking about. We want to start helping our clients do more financial analysis. Looking at the numbers. Looking for trends. Identifying ratios and what's good and what's bad and helping the clients do better by providing some of these advisory services.
One of the things that we may want to do is be able to create a profit/loss report on a monthly basis. You can do this in the desktop version as well following the same process. What you do is ... I'm gonna come in here and under my rows and columns. I'm going to change it to month. Now, first of all, I had this year to date. So, you choose whatever period of time you want. I'm gonna change that to month and also before I do that I want you to notice how Hector's going to talk to you in a little while, how in the Desktop version, you can add some of these additional percentages in there on your financial statements. You can do that in QBO as well, to add these in there.
Let's go ahead though, and show you what this monthly P&L looks like. I'm going to go ahead and click on it to create the report. Now, this is a sample company so there's not too much data out there. I'm kind of concerned that this might be in your way over here. This is the dashboard. Let me close a bunch of this off so you can see better, hopefully.
Hector Garcia: Yeah, we can see clearly.
Michelle Long: That's good, okay, thank you. In here you'll see the monthly profit/loss. You'll see we don't have anything January through June. What we can do with this, not just create this report but you can save the customization. Better yet though, we can export this to Excel. If I click on Excel, send it over into Excel. Then I can use real quick and easy the chart wizard to create a chart that will show you the monthly trend, the monthly chart for sales and how they're increasing or for the net income and stuff. You can, at a glance, create graphs and charts to see how things are doing on a monthly basis. That's very helpful. You can particularly for income and you want to see how the income is growing.
The other thing that you may want to do is look at a report on the balance sheet for multiple years. You could do that just as easy as the monthly P&L, by coming in and create your balance sheet. You're gonna change your years. Let's say if I wanted to do 2010 through 2014 and then I want to customize ... Let's change this from 2010 to 2014. Remember last time we chose months. This time we're gonna want to choose our years. This will allow you to see more than one year for comparative purposes to see how you're doing. Now again, this is the sample company, so we don't have a whole lot of data out here. What you can do with this is export it to Excel just like we mentioned a while ago. Then, in Excel, you're gonna be able to calculate some of these key ratios for your clients, like the current ratio. Can we pay our bills on time? If you take current assets divided by current liabilities. How's your current ratio doing? How's your debt to equity ratio doing? We want to start doing more financial analysis and looking at these things more to help our clients analyze how they're doing.
Just like I was talking about a while ago with the profit and loss, putting those percentage numbers on there would quickly calculate the gross profit margin for you. You can calculate and help them to see how they're doing.
If I do percent of ... Let's do percent of income, not percent of expense. I want to see things as a percent of income. I now can quickly see what is their gross profit margin. Right here, the gross profit margin in this company is awesome. It's 96%, so it's huge. You could do this on a monthly basis too. Export it to Excel, then create a graph that shows how that gross profit margin is changing on a month by month basis, so that you can track that for your clients.
A good example of this, I had a client that they had a restaurant. It's actually like a sandwich shop. Their gross profit margin was declining. Well, gross profit is made up of your income less your cost of goods sold. Hector, you're not muted, just so you know. Income or costs of goods sold. One of those two things are gonna impact that gross profit margin. If my gross profit margin's deteriorating, could it be their income? Is their income too low? Is income going down? Or is my costs of goods sold going up? You need to look at these things and try to determine what's going on. In this particular case, we started investigating and what we found out, the cost of goods sold was going up. We got to looking into it. The supplier didn't raise their prices, so what else could cause your costs of goods sold to be going up?
Maybe in a restaurant you have employees that need additional training. Maybe you need to train them better cause they're wasting the food and things like that. Maybe they're giving away food free to their friends. Maybe they're ringing up small for a sale, but they're actually giving them an extra large portion. So, looking to see what's going on with this particular client, we identified that some boys were taking cases of meat out the back door of the restaurant. They were stealing. By being able to monitor the gross profit margin and see it's declining and deteriorating what's going on. You can help your client get away from just the data entry person. I'm not just the bookkeeper or accountant that does the data entry, but I'm your trusted advisor that can help you to analyze your numbers and how you're doing. Providing more of these advisory services to help us differentiate ourselves and provide additional value to the clients.
One of the things that I want to make sure you know is, in the new Quickbooks Online Advanced certifications, so in the new QBO Advanced certification, module five covers Advisory Services. Hector's gonna talk to you a lot more about reporting and some things that you can do in the desktop version. You're also gonna learn about Finagraph which is gonna give you the opportunity to see these ratios quick and easy and have a dashboard without going through and creating the reports and exporting them to Excel. We need to start moving away from data entry and into some of these advisory services. That module covers that for you and helps explain to you these different ratios, what they mean, how you can do some of this. You don't have to be certified for three years. You do need to be a ProAdvisor. You do need to have the current ProAdvisor QBO certification. You need to get certified in QBO. Then you will have access to that advanced certification. Module five covers this stuff in detail. There's a recording about it. There's also a PDF guide. Even if you don't want to do the full advanced certification, at least access it to get these ratios and this information there.
The other thing I wanted to share with you in QBO is on the reports tab, this company snapshot, if you click on it, what it will do for you, is it's comparing your type of business with others that are using QBO of the same. We talk about coming ... I'm sorry. I'm in the company snapshot instead of the scorecard.
The snapshot shows you at a glance some charts and graphs. I'm having a bad day here. Let's go down to the scorecard cause that's actually what I wanted to show you was the company's scorecard. It's under the business's overview. Right down here, see where it says "Scorecard." This is comparing you to other small businesses like yours. Industry averages are good to compare yourself to other companies within your own industry. There's a website that you can go to called bizstats.com. B-I-Z-S-T-A-T-S.com Bizstats.com. You can get some free industry averages for other small businesses that allow you to compare yourself. This quick scorecard here will show how do you compare to others that are using QBO based on your type of business. Based on the industry that you put in there. At a glance you can see how your doing.
This isn't very meaningful because this is a sample company. [inaudible 00:13:37] don't have a whole lot of data in here and things like this. Just keep that in mind. Normally you would see how your company or the client's company is doing here and where you rank compared to your peers. It's hard to know, how am I doing? How am I doing relative to other companies like mine? Again, this was the company's scorecard that's available under the business overview reports. This is available in Plus. I don't think it's available in Essentials, but I can't remember it for sure. I know it's not in Simple Start or Easy Start.
Check this out. This is something that gives you quick and easy access but you're also going to learn about Finagraph later. That's going to give you lots of good ratios and things like that real quick and easy. The other alternative is to export that into Excel and create your charts and graphs over there. So, create those monthly reports, those multiple year comparison reports, export to Excel and use the chart wizard over there. You don't have to be an Excel guru to use that chart wizard and build some cool charts and graphs, pretty quick and easy.
I hope that has been helpful. Hector, did we have any quick questions that I need to cover? I didn't show too awful much here.
I'm looking to see is there a better way to get industry comparison reports and compare more specific industries and stuff.
Christine, what you would want to do is go to bizstats.com to get those ratios, but to do the comparison you need to use ... I'm not sure if Finagrpah does industry averages. Hector, do you know?
Hector Garcia: Yeah. Mike's gonna address how Finagraphs works with industry averages, but yes, they do. That's actually one of their specialties. Mike will specifically tell us how they do it.
Michelle Long: Perfect that's awesome. Okay cool. Somebody else says that they have encounters a problem with custom reports. Custom reports created by my client don't appear on my dashboard.
When you create a custom report, that's a great question. Let me minimize this box. When you create a custom report ... When I come over, let's just do a P&L quick. When I show you how we save the customizations, what I would have them do, when I click "Save Customizations," there's an option here that you want to share this report with other users.
If your client or you, if you've created a custom report and you want other people to have access to it, you need to click this box right here to share the report with company users. I would suggest they do is, open the report, edit it, check this box off and save it again. Then you should have access to that.
I hope that was helpful. [inaudible 00:16:18] take you through and see Finagrpah. I love Finagraph. I saw them this week earlier at the Slater conference. I'm glad that they're gonna be here sharing some of that with you today. It's a great tool for you to help implement some of this with your clients.
Hector, I'm gonna turn the presenter over to you.
Hector Garcia: Anyway, thank you Michelle very much. It was great seeing you in Las Vegas. I wanted to also mention, congratulations on your award, Most Powerful Women in Accounting. I wanted to take a picture with you holding that award but everybody was doing it so I didn't get a chance to. Congratulations on that and I'll take over for now [crosstalk 00:17:01].
Michelle, showed you a couple of examples on the Quickbooks Online version. I'm gonna do some examples in Quickbooks Desktop. I saw a lot of the comments and questions here were around doing stuff in Excel. What I'll do is, I'll try to spend as much time as possible in doing examples of reports in Excel. I'm going to close the polling question. Thank you for answering that, by the way.
Let's see. We are on the CPE Keyword. My topics ... I'm gonna cover basically, three concepts. I'm gonna cover, cash versus accrual reports but not in a traditional sense. I'm gonna break down specifically how Quickbooks behaves with these things. It's a basic concept but if you don't have it right it's difficult to then, consult your clients on how to interpret specific reports. There's a tricky piece between how Quickbooks sees cash basis versus seeing paid invoices.
Then, we'll talk about this period comparison and percentages and additional columns. Then we'll talk about working with some of this Quickbooks data and Excel.
For the first example, if you want to follow along, this is going to be an interesting experiment. If you follow along you're gonna find some interesting things about this. We're gonna have a Quickbooks file that I'm gonna come up and there's gonna be three invoices. One for 900. One for a 1,000 and one for 1,100. Each invoice is dated on each of those separate months. If you can write it out on a piece of paper it will help you follow the example. Then I'm gonna experiment with different payments and different time frames and partial payments on applied payments. I will show you how that affects the different reports.
This topic is called "Last month's invoices that are paid. Versus Invoices that were paid the last month." These are two different reports. The titles are very similar. It's the same amount of words just flipped around. It's actually not the same report.
Let me jump into my Quickbooks Desktop now. Hope you guys can see my Quickbooks Desktop. You should be able to see it.
For this particular example, I have a Quickbooks file and I have three invoices. This is an accrual based report. I have three invoices. One and I'll just show you here. I have one invoice, just double click here. So I have three invoices, one ... Each one is dated a separate month, right? Each one has a different amount as well. In accrual basis reports something you guys already know, that amount is tied to the actual date of the invoice.
Now, let's experiment with some payments. Let's switch this to a cash basis report. I'm going to go back to cash basis here. I haven't received any payments so there's no income. When I go to receive a payment ... Let's go to company, let's go to customers. Sorry, received payment. I'm going to receive a payment for the September invoice in November. Let's go here to the one customer that I have. Then I'm gonna receive a payment for the September invoice in November. I'm gonna hit "Save," and "Close."
This is just real basics here. We're setting up a premise. That's how cash basis works. It grabs the entire amount of the invoice and it pushes it to the date it was paid. One tricky piece about this is what happens if it's paid before? Let's go into this payment here, I'm going to go into this payment here. Here's the invoice. I'm going to go into the payment and where's my control H. I'm gonna go to this payment and I'm going to take this very same payment and I'm going to apply it not to the November invoice, I'm going to apply it to ... Not to the September invoice, sorry. I'm going to apply it to the November invoice. I'm going to date this one in November. What is the behavior that is happening now? I have a payment in September for a November invoice. If you were to follow cash basis accrual basis principles, when, just think about this for a second. Once I hit "Save," and "Close." When is this $900 of income supposed to show up. Is it supposed to show up in September or supposed to show up in November? So, think about that for a second. Let's take a look at the reports.
I'm going to go back to the very same report. Just close it here where it's my report. There's my report. We took a November invoice and we ... Sorry, we took a November invoice and we received a payment, in this case, let's go back, in September. Control H. In September. The cash basis principle says, if the invoice is dated one day but is paid a different day you're gonna change the financial statements to the paid date. There's one exception to the rule. If the payment is received before the invoice date Quickbooks will not show the income earlier it will still default it to the invoice date. That's a little bit about a tricky piece behind these reports.
For future payments, the income shows up in the future payment date. When the payment is received before the invoice date the default date is still the original invoice date. That in itself is a tricky thing but you have to know how that works. The other piece that's interesting about this is how payments are applied. Let's go back to the very same report. Let's stay on this very same report. I'm gonna make it here to the right that way we can see things in real time. On this very same report, we have a $900 payment showing up in November, right? If I grab that payment, so I'm gonna grab that payment for a second. I'm gonna go to customers, received payment. It's the only payment that I have in the books, here. If I grab that payment and I unapply it ... Let me show you I'm just gonna unapply it here. There's just payment in September. I hit "Save," and "Close." Then our report is gonna show zero income. Because in Quickbooks cash basis, is not just payment date, it's applied payment date. Kind of an interesting thing, also.
Experimenting with that same payment. I'm gonna grab that ... Let me just go back to customers and received payments. I'm gonna grab that same payment, so let me go back. I'm gonna date this payment October. So, I'm gonna date it in October. I'm gonna apply it to the October invoice. Then I'll hit, "Save," and "Close." Then natural behavior in a cash basis report, I'm seeing that in October. Let me show you something that's more interesting, sort of in depth about this. If I unapply to the October invoice, there's an October payment but then I apply part of it to September. I'm gonna apply, let's say $500 to September. I hope you can follow that. Then I'm gonna apply the other $400 to November. Now what's happening is I'm receiving a payment in October but part of it is being applied to September. The other part of it is being applied to November. Think about for a minute, how you're expecting those numbers to come in the report.
I'm gonna hit, "Save," and "Close." There it is. What happened, right? What happened is we received all $900 in October. But because the $500 were applied to September it future data'd that income to the payment date. However, because the $400, where even though they were paid in October they're still dated on the original invoice date because of it's a payment previous to that. That's an important concept because cash basis is not, in a traditional sense or maybe on a straight forward way. That's an important thing to cover on that.
The other thing I want to show you is, I'm gonna pull a report here of invoices that were paid last month. I'm just gonna go to a regular transaction ... I will go to transaction detail report here and then I'm gonna tell it I want dates. I want to see last month's. I'm gonna go to last month. Then I'm gonna go to filters. Then I'm gonna go to transaction type. Then I'm gonna hit "Invoices." Right now, this is strictly just an invoice report. Let me make the font a little bit smaller cause the resolution here doesn't help. There we go.
This is a report of last month's invoices. That's all it is. It's last month's invoices. If I do a cash basis report and I hit "OK." In this case, what are we looking at? Just make this smaller so we can all see here. If I do a cash basis report then what happens? This is a November invoice that was paid in October. Right? If I look at cash basis report for October I am not looking at October invoices, I'm looking at invoices that were paid in October. That is different. If I switch back to accrual, then I'm gonna see the one invoice that is from October. This is my October invoice. Go back, I'm gonna change that again to cash just so you can see. Now, this is my September invoice partially paid, that's why you see $500. This is my September invoice. Here's the memo, September. If I go to accrual, I see my October invoice. Now, my October invoice is not paid, right? If I filter this invoice, this report, I'm sorry. We're using the paid status to close, which basically means pay. I'm gonna see nothing. It's just a big blank.
What ends up happening is, there's a very important concept here. This report here, an accrual basis report, filtered with a paid status. We'll go here to filters. Here, filter with its paid status, it's gonna show me invoices that are dated in October but they are paid as of today. Whenever today is. Versus, if I go to a cash basis report in this case and then have to turn off that paid status because cash basis and paid status don't play well. That's another very important thing. You cannot do those two filters at the same time. I'm gonna get rid of that one. It's not the same thing.
Going back to my PowerPoint presentation. We have two titles. Last month's invoices that are paid. That's an accrual basis report with a paid filter. Or Invoices that were paid last month, that would be a cash basis report. Those are the two important concepts and I hope that I made the point then.
I'm gonna close this. I'm gonna switch over to a sample file. While I switch over I'm gonna take a look at some of the questions to see if maybe I can answer some of those questions here real quick. Let me see if we have any quick questions up here. Just bear with me for a second and I don't have Michelle here.
There was a question about a CPE Keyword, [inaudible 00:28:25] cash basis. They were asking one question, very important question. Does the cash basis behavior in QBO and Quickbooks Desktop work the same way? The answer is yes. It is the same behavior. On the inventory part it's a little bit different but for the most part I would say, that's exactly what that would be.
Somebody said, "Thank you Hector, for the mystery about the unapplied invoices." There's some other question that I probably don't have to answer but I'll try to see if I can look at it later.
Somebody else is commenting about what would the IRS think about all this? This is an important piece. Probably the most important delicate piece about this. Receiving a payment is not cash basis income unless it's applied. Shawn, that was the answer to your question. When you're gonna percent a profit and loss and cash basis, you have to make sure that you don't have any negative accounts receivable causing this problem.
Then somebody else had a question that ... How do I view webinars that I missed? I'm not sure. I'll try to answer that later.
I don't think there's any questions that I can actually answer at the moment. I'll try to look at them a little bit later.
Let's look at another example then. Let me switch back to the desktop version here. Let's take a look at some of the customization of the reports here real quick.
There's a couple things I want to cover cause Michelle did cover them a little bit but on the desktop, they're a little bit more extensive, so I want to show that.
In the profit and loss ... Let me just see. In the profit and loss when we click on customized report, top left. There's a couple of interesting rows and columns here that people usually have questions about.
First thing I want to do, is for dates, I want to choose here. This fiscal year. Then let's talk about what all these little things here, mean. Previous period, it actually means grab this exact same period that is here and roll it back. This is kind of a mystery for some people. Let's say for example, let me just take a look at what kind of dates we're looking ... This is 2018. Let's say for example I'm gonna look at 06/01/2018 through 07/31/2018. That's a period of two months. This is a transaction detail report. I'm sorry about that.
We're gonna go to 06/01/2018 through 07/31/2018. I'm looking at a period of two months. If I choose previous period, what the system is gonna do, it's gonna take those two months of June and July and compare them. That is not the same thing. The two months before June and July will be April and May. This is a good comparison for something called the rolling performance. We can actually compare the last 45 days with the previous 45 days. This is important for trends. Where as the previous year, we'd actually grab the same month and day but just flip it back one year. Most of you probably already knew that. This is the difference between these two. Some people get them a little bit confused.
Now, the year to date, what I like about the year to date in particular is, let's say for example I'm looking at one particular month. I'm gonna look at October. I'm gonna look at October, just October by itself. I'm gonna do a year to date with a year to date percentage. What I like about this is we can actually start comparing how we're doing based on the whole year. We'll do a particular example. If I have in one month of October, if I have 61,000 and in the entire year I have 153,000. Right? If I take 153,000 and divide it by 10, that gives me the amount of months, right? 153,000 divided by 10 gives me about 15. If you look at how you compare 61 to 15,000. This is already immeasurable. This month is way above average. This is actually a really, really good month compared to the average.
One of the easy ways to look at these percentages is that particular representative of the average or way above average? These are interesting, progressive concepts of comparison.
The other thing I want to show you is comparisons. Comparisons are very important. I'm gonna look at, for example, an entire year. One of the real neat things I like to do when comparing a year's worth of performance is, I take a look at ... I look at these by quarter. Then, what I like to look at when I look at these by quarter and I collapse them ... What I like to look at is gross profit. To look at gross profit, I do percentage of income. That's very important concept. I do percentage of income and what I end up doing is ... These reports may be a little bit off cause of the sample file but what I look at is, tell the client, gross profit percentage quarter over quarter, are you achieving your goal? Typically, when you do some sort of report or performance consulting with your client, gross profit analysis, it's one of the very important pieces.
If you sat down with your client, let's say for example in July, and you said, "Hey, our goal is to increase our gross profits cause we're gonna buy lower or increase our prices." Then we want to be monitoring this gross profits quarterly. Maybe you want to monitor them monthly. We'll use a much shorter period, like this. A simple report like this, where you're looking at gross profits ... Hopefully you guys can see it. When I'm looking at gross profits month over month, could be a good indicator or are we on target, are we off target, that sort of thing.
Let me show you something else with comparisons. So I'm gonna take a look at here on the last four months. Instead of doing percentage of income, I'm going to do something kind of interesting here, which is percentage overall. I'll hit "OK," here. What percentage overall does is it actually compares that particular period compared to the entire period we're comparing. In this particular case we're looking at four months worth of information. Let me do ... I think better, three months will be a little bit better. Let's do that. Let's do just three months. Let's do ... There we go. Let's do September through November. This report is pretty interesting because what allows us to quickly see is that on this three month period, our biggest month was November because it represented 40% of the sales. These cross, percentage of row and percentage of income, options become pretty useful.
The next example I want to show you is how to pull financial reports from Quickbooks, profit and loss balance sheet, export them into Excel then perform some sort of, analysis like taking one [inaudible 00:35:44] dividing it by the other. I'll take my marketing expense and divide it by income for example. I want to take this information and pull it into Excel, make some sort of analysis that's meaningful for my client, and then after I save it I want to be able to grab information from Quickbooks and update it just in case the information changes.
Another very important piece, is I want to make sure that before I export it, that I go to advance and display rows and I click on "All." I need to make sure, I absolutely need to make sure that if the report is refreshed and an account is not used, that when I go ahead and refresh the report and the accounts are not used in a different time period, that they don't mess up my Excel formulas. So I'm gonna click on Excel, create new worksheet. I'm just doing a regular export I'm gonna go to create new worksheet, click export. The system is gonna create the spreadsheet for me. Here it is. All I have to do is rename it. I'm going to put here, balance sheet. I'm going to go ahead and just save it. I'm gonna save it in the desktop. I want to make sure that I know what I saved there, that I know the name because I have to reference this later on when I'm going to save another worksheet on top of it for the profit and loss.
I'm gonna go ahead and switch back to Quickbooks. Let me close Excel first. I'm gonna switch back to Quickbooks, do the exact same thing. Go to customize report, go to advance, and make sure that I'm doing all here. The rule applies to both reports. Then, I'm gonna collapse it, just because for the purpose of analysis I'm gonna go ahead and collapse it. Again, even the zero accounts are there because I did that option that says, "All."
I'm gonna go to create new worksheet. Not update new worksheet. It has to be create new worksheet. Make sure that we're not doing update yet. We're just doing the create, okay? We'll hit create and then we're gonna use existing workbook and we're gonna reference that workbook. What this will do is it'll create a new tab inside of the workbook. People get those two terms confused. Workbook is the Excel file and the worksheet is the tab inside of the Excel file. I want to make sure that's clear. There's my new tab sheet one. I'll rename this to profit and loss. Then I have to save this and close it before going into Quickbooks. You gotta make sure you always do that. That way when you go ahead and do the update, it will work.
Now, we'll ... I actually didn't have to close it. Let me open it back up and let's do the analysis now. I'm gonna create a new sheet. From that sheet I'm gonna take two pieces of information across the two reports or even within the same report and create some sort of meaningful analysis. I'm gonna do return on fixed assets. Let's say that a number that I look at. I'm gonna take net income for the period, year to date, period. Then I'm gonna divide that by the fixed assets. Whatever that means to your client, we're assuming that this is meaningful to your client. The other piece we're gonna do is marketing expense divided by sales. We're just gonna do the example with two metrics.
I'm gonna grab in this particular case the total net income and then I'm gonna divide that by, and this is just standard Excel formula, I'm gonna divide that by total fixed assets. I'll just press enter here and it'll save it. There it is. Let me just change the formatting here to percentage. 600%. Six times [inaudible 00:39:23] percent, the amount of fixed assets. Let's take a look at now ... I'm gonna make this bold and red so you can see it.
Let's look at marketing expense divided by sales. We'll grab marketing expense and we'll divide that by total income and we're just trying to create two metrics that, again, are meaningful to your client for whatever reason. Actually that should be a percent. I'll change it later. These are the two numbers we're looking at and we're looking at them for that period that we exported, which is that December 15th period. I'm gonna save this, get out of it and just remember the numbers. Get out of it and I'm gonna change the periods inside of Quickbooks. I'm gonna go from December to November. I'm gonna go November 1st to November 30th, go ahead and refresh that. I'm not changing the order. Keeping it collapsed. Keeping it with that option that says, "All accounts." Then I'm gonna update the existing worksheet, just exactly as your seeing it. I'm gonna actually gonna tell it which is the sheet that we're updating. We just told it, "Hey, this is the new profit and loss that's gonna take over that existing worksheet." That way the formulas that we created on the third sheet, pretty much stay in tact. You should be able to see it. I'm just gonna save it because I still have to do the balance sheet.
I'm gonna follow the exact same exercises here. I'm gonna change the period instead of December/November. Then I'm gonna do the exact same thing. I want to go to update existing worksheet and then I'll select the worksheet within that workbook which is the balance sheet. Then we'll hit export. We'll just wait until that loads. We should be able to see it real quick here. There it is. Now you see we have new numbers there, right? Before it was 622 and zero. Now it's 619 ... See, I was supposed to make that a percent. Now it's 619 and 3%. That's for the November period because that's the last one we updated. That's for the November period.
We're gonna go ahead and just get out of here and save it. You can see the two metrics that we created. This could be a nice little tool you could create for your client and obviously these metrics could all be customized. They could be different for every client, every industry. These two that I did were just two random ones. I'm not saying that every client you should analyze these two points in here.
I'm gonna show you another example of something we can do in Excel. Something fun with Quickbooks and Excel. We're gonna ... Double click here on total income. I'm basically gonna create a report that contains all of the detailed income data that's in this Quickbooks file. I want to export it to Excel but I want to do something interesting with it. I want to do a pivot table. If you don't know what pivot tables are, this is gonna be nice eye opener here.
I have to make sure that I don't have any of those sub-classifications there. The total buy should be "All." Like this, it should be a total only, exactly as you're seeing it there. I can't have any total by. Let me export this to Excel in the exact same way I would export anything else. This time I'm gonna do a CSV. Now, I'm gonna do a CSV for a specific reason. I need this to avoid having headers and footers and all that stuff. I just want to just bring the data that matters. I'm gonna save this CSV file in my desktop. Then I can just open the CSV in Excel, just like a regular Excel file. Wait until that loads, here we go. See, it's go no headers. All I have to do is clean out the stuff that I don't need. One of the things I don't need is that very last total. It doesn't have any meaning anywhere. It will actually mess up my numbers so I'm just gonna delete that, completely delete that.
Then, I'm also gonna delete those empty headers that are at the very beginning like that column that's empty. I'll get rid of that. I don't need that and that empty one, I'll get rid of that too. That second row there. Now I have just square data. Right, just have straight, data-based style data. Then from here I can create a pivot table. We're gonna see how quickly we can do deep analysis. We'll go here to insert pivot table, we'll hit "Okay." Then if you already know what a pivot table is, this is obviously just a review and something you already know. If you don't, this is a quick, nice, brief introduction into the world of pivot tables.
We're gonna grab the customers and put them in the rows. That way it just grabs all my Quickbooks data and puts all the customers that have activity there. We're gonna go to transaction type and we can put it here as a filter, so I can actually choose within which transaction type I want to work with.
Let's say I want to limit it to sales receipt, right, or something like that. Yeah, I could limit it to sales receipt. Then it's just gonna narrow down the amount of customers that have sales receipts. Then I want to grab the credit amount, which is like sales amount. I want to put it here under value. The default treatment in pivot tables is count. It's basically counting the amount of transactions. I just want to change that to sum here, so it adds it up. What you're seeing here is a simple report is total amount of sales receipts per customer all added up in there. I could do more things. I can grab the date and put it into the filter. I could do many things but the important part is this is where the data's coming from. It's coming from the raw Quickbooks data. I could put the date in the filter for example, then I can actually choose, narrow down which dates I want to see or what date range I want to see. It's just a simple example of how you can do deep detail analysis in Excel with a pivot table by exporting it from Quickbooks.
Let me go ahead and just get out of here. There's one more example. I'll show you graphs. We'll export the raw data from Excel and we'll create a graph from it. That in itself will finish my detail Excel example.
I'm gonna do a graph of all the sales and I'm gonna pick a specific time period. I'm gonna grab all the sales and also I'll grab the payroll expense. I'm gonna grab two line items for the whole year, which is sales and payroll expense. I'm making it by column so I get each month. I'm gonna grab this specific report, export this to Excel. Then, I'm gonna clean it up to only see the information I want to see. In this case, I'm not doing CSV, I'll just do a regular Excel.
Once it opens up in Excel i'm gonna get rid of what I don't need. Right? I'm gonna leave the headers there. Just real quick, I'm pasting it as values that way the formulas don't mess me up. I'm gonna get rid of all this stuff and keep the headers. There's my total income. Then I'm gonna get rid of everything but payroll expenses. I got rid of everything but payroll expenses. Then I'm basically left with two line items. Total income and total payroll. From here, it's just ... Pretty much simple. I can create a graph from here. I have to make sure that these titles make sense because one was bigger hierarchy than the other one. I'm just moving it around. I gotta make sure that the very last one, the total one, is out too cause I don't need that. I just need these here. From here, I just select the data and show the graph. This is a regular Excel graph. We'll do a line graph. The two bar line graph. What it'll do is to show me a quick comparison.
That's in a nutshell how you get Quickbooks data into Excel and different things you can do with it. I'm not saying your limited to doing one thing or the other but there's obviously different things you can do with it.
I'm gonna switch over to Mike from Finagraph. I'm gonna switch over and give control to Mike cause Mike gave me a really nice demonstration in Las Vegas. I think he will behoove a lot of people to know that. Mike can you hear me? Are you still muted? Let me see if I can un-mute you. Mike you should be un-muted now.
Mike: Absolutely, Hector I appreciate the opportunity to present to your group and really an outstanding opportunity to meet both you and Michelle. I felt really honored to be part of this. Been looking forward to it all week. I think this topic that you went over and the theme of all the shows that I've went to this year, that industry is changing towards more that of the advisory services. What do I do with this information?
Basically what we're gonna do, Hector, is when I talk to people at these trade shows, or I talk to people everyday, they say sometimes when you're using a third party application it's difficult to set up. We're gonna just tackle that right off the bat. Showing you how difficult our product really is.
If I'm just gonna add a business, if they're gonna paint the scenario where you're in the client's office or you're in your own office, but it's three pieces of information. We're just gonna create a company for you again, Hector. We're gonna call it ... I think yours was Hector's Avocados, yesterday. Would you say Hector's Excel class-
Hector Garcia: Training.
Mike: There you go. I'll use my zip code and we use that for comparative purposes. Now our keyword search, some people know the [nix 00:49:15] code cause you have access to it on the tax returns. In case you just want a quick search of it you can start typing in a keyword. We're going to go look for things with those key words in it and point you in the right direction for the [nix 00:49:28] code search. Or we also have a complete search tool where we can walk you down through from the highest level of an industry, down through the granular part with the details and definitions tab.
That Hector, that's the most difficult thing we ask people to do. Just three pieces of information an then we're ready to get our import.
When I say that it will go back to our dashboard. The first thing I showed you was the dashboard. It's really just a place where you can go back and find your clients. In this case you can see that I've got Hector's Excel classes. We can do one of two things. We can add Hector, users on your side or we can add them to our firm. It's as simple as just inviting them by sending them an email. I just shoot them an email and then the email, there's a click link inside of it that walks that user through setting up his account and being able to log in and see the analysis that you've done as the accounting professional.
Hector Garcia: Hey Mike, hold on for a second. I wanted to ... Some people are telling me they can't see the screen. Can you guys retell me through the chat or to the questions, can you see his screen? I can see his screen but some people are saying their not. Can you guys let us know? I guess it just came up. Some people are saying that they can, okay, so keep going.
Mike: It looks like the view goes from 78% to 100%. I think there's some of them that are going in and out. Looks like it, so ...
Well I'm just gonna keep going. Always available for more in depth or personal one on one type of demonstrations. We do those all day long.
In order to do the analysis, the hallmark of this company is simple. One click access. Cover. Not a lot of drilling into information. To do your analysis actually you just select your accounting system that you're using. For the Finagraph portion we do all of the desktop versions from 2008 forward, online and of course zero is new one that we just added. I'm using Quickbooks Desktop, today. We're just gonna do a quick little upload of that information.
Now if you're using Quickbooks Desktop you just have to have it open to the company you want to analyze. If you use Quickbooks Online, it's gonna ask you which company you want to do the analysis for. We're gonna launch from the Cloud and connect right to the Quickbooks file. If you notice I haven't even touched the Quickbooks. I've just told it which one I'm using. It automatically detects where we're at. It will go through and gather five years of financial statements, income statements, balance sheets, accounts payable, accounts receivable, inventory evaluations. What it'll do is upload it into the Cloud and process it.
Now, usually I [inaudible 00:51:58] just click the button, go get you a cup of coffee, come back. By the time we've already grind the numbers for you, while you probably ground the coffee beans. What I'm gonna do is jump to ... Because of interest in time and questions. You can see how quickly it goes through.
Hector Garcia: Mike, I have two people commented, can you please explain to me exactly why I need Finagraph? I guess you jumped in a little bit past and people didn't get in context exact ... They know all your doing it syncing the file now, but they didn't get the elevator approach exactly on what that is.
Mike: Got it.
Well basically what we are doing is we're providing you with a platform and the educational piece of why the numbers matter. We're gonna quickly gather and organize your client's financial information and put it into a meaningful presentation with bright red flags, bright green check marks of how they can improve their business and get more profitability. Or see how they stack up to their peers or their competitors. All of the work that you guys are working towards and all of the themes of these trade shows that we've been working on ... Hector, the last six months, we're fitting real nicely and we're uniquely positioned to be able to assist accounting professionals when working with their clients. Instead of doing and hours worth of math or manipulating Excel spreadsheet, we're basically pressing a button and giving them the same basic information.
Now, from the finance one on one, you can give access to your client so h e has this view as well. If we hover over anything that you don't know you can simply just get more information by moving you mouse and hovering over the top of it. One of the things that are confusing to a lot of small business owners is the current ratio. Any of the financial ratios that we're dealing with, we're gonna tell you what it means, why you should care about it. In the case of this quick ratio, where there's a big red flag by it, we're gonna say, "Here are some possible causes of a low quick ratio." And, "Hey, here's how you may be able to solve it on your side."
Now, [inaudible 00:53:58] what we're comparing it to. These can be compared to the industry average when you upload the [nix 00:54:01] code, or you can also use our customized flags and set your own what/if scenarios. What if I'm at 50% or what if I have a bank covenant that I want to maintain? I can simply just type in any of my own goals that I want to compare myself to. We'll just say 50% gross margin and maybe I want 70 days worth of inventory, as one of my goals.
I want it to say that we're gonna recalculate and go back to it. Then, I'm gonna show you the impact of the numbers. What does it mean in dollars to that individual?
With that ... Hector, I think you remember back in high school and college, you always had to show your work. We're no different. What we're trying to do is give that education piece back to the client, back to the customer saying, "Here's where the numbers came from and here's how you stacked up against the industry." If there's a dollar mark that we can put to it we're gonna show you that as well. The gross margin is [Flex 00:55:16]. He's running ... He's usually running about 50%. He's a little off on this year. What does that mean in dollars? It means that if he could run his company at that 50% mark as opposed to the 41%, he'd have a 116,000 more dollars in cash flow. Up here, we determined that he also has too much inventory. Well, how much is too much? Well in this case if he could just reduce it by that 5.7 days from 75 to 70 days at our goal, there's 13 more thousand dollars he could put back into cash.
What we're doing is trying to give them a unique perspective about their business in real time on where they can find hidden cash opportunities in their own financial statements.
Anybody that wants the detail behind the number, of course we have that as well. If I click on one of the dates, it's gonna take you to the five years worth of balance sheets or income statements. Already analyzed for you as opposed to using other types of forms or Excel spreadsheets. In this case you can-
Hector Garcia: Mike, you got time for some questions? I wanted to just before you get a little bit deeper. There's a lot of questions on this screen. I don't know if you can see them. One of the question is, they were asking, instead of pulling the data from Quickbooks to Excel, which is the example that I was trying to do where the computer just failed. Make these calculations by yourself, this tool comes with all these things preset, right? You are showing us how to create flags and ratios but you are pre-doing all these things. Nobody has to program any of these calculations, correct?
Mike: You know what Hector, James Walter, our CEO says it best, "We're gathering all of this information and presenting it in this format and no one's ever typed a thing." There's no importing or exporting to Excel. It's already pre loaded for you.
Hector Garcia: Perfect. Another thing is, somebody was asking about security. This data that's being uploaded to you is just high level financial information. You're not getting customer data or individual sales data, correct?
Mike: That's right, that's exactly right. A matter fact, in Quickbooks, there's a security certificate that pops up. We are one of the Intuit partners. You can also say, "Hey, make sure that no social security numbers or account numbers come through." That's not what we're taking anyway. What we're doing is we're looking at financial performance information to give you back a snapshot of what's happening to the financial health. As far as data goes, we were originally a banking platform. Which means we're using Microsoft Azure data center. Same one the big banks use. That's primarily who our clients have been in the past, are the banks. Microsoft Azure data center is audited by the [inaudible 00:57:40] several times a year. On top of that, James Walter has several patents in data security that's he's overlaid on top of this when he was a program manager over at Microsoft.
Hector Garcia: Just a follow up question to the security. If somebody asked a specific question about data security that has a client with a non disclosure, can they contact you and sort these things through with you guys?
Mike: Absolutely. We do that almost daily.
Hector Garcia: The other thing is relevant to our audience. We have over 500, almost 600 accountants, ProAdvisors here, go back to that financial ratio cheat sheet. That little bubble that tells you how you can improve it. Can you show me that example again?
I want to know exactly ... I want to tell you what I liked about this specifically. This is Hector Garcia talking now. What I liked about this tool is, this potential solutions and possible causes. These are great conversation starters. At the end of the period where you doing bookkeeping or you're doing accounting or you're producing financial statements. Sometimes, accountants, we get stuck trying to figure out how do we turn our job to consulting work? Basically all we have to do is print these things out and just have conversations about possible causes. Things like, "Hey, current liabilities." Let's define them with the client and say these are too high compared to the average. What kind of things can we do and have discussions about what would happen if you reduced inventory, right? What would happen if you sell some of your fixed assets? You guys are not telling people that you should be doing these things, correct? You're telling people these are the conversations to have. Is that correct?
Mike: You know Hector, you're spot on. Basically what we've done is tried to set the agenda for your conversation. Our goal is to position you the accounting professional as that trusted advisor. The one who's gonna help them set a specific action plan to make this better in their own business.
Hector Garcia: There was a question about Quickbooks Desktop QBO both, correct?
Mike: Yeah, it work on both from 2008 of Desktop forward to 2015 and the QB Online as well.
Hector Garcia: Another, question. I'm bombarding you with questions here cause these are the audience questions. Somebody's asking can you export this to Excel?
Mike: Yes absolutely.
Hector Garcia: Can you show us real quick how you would do that?
Mike: The most simple way is just to highlight, copy and paste. [crosstalk 01:00:16]
Hector Garcia: It's formatted in way that Excel will take the copy and paste. Okay, good. There's some questions about pricing or discounts to ProAdvisors, we'll talk about that at the very end. It's Quickbooks Desktop and QBO and Zero as well, is that what it is?
Mike: Yes, yeah. We're working with all three of those at this point in time and then adding other ones as we develop more data around what's the next priority.
Hector Garcia: I want to ... There's more questions but I'll send you the question log and maybe you can answer some of these specifically. I'll let you finish your presentation. I'm gonna put the CPE question up in about two minutes, okay Mike? I just wanted to make sure that those questions were being covered.
Mike: Just a couple more quick things. Hector, one of the things that you pointed out to me that you also liked, was this ability to create a share of a link. Where as maybe you don't want to give someone their own user name and password where they can log in. You have the opportunity to create a read only version of this interactive file to share with anyone else.
Let's take for example someone is looking to buy Hector's company. I want to share with them my five year's worth of analysis without having to go through and print and scan and fax and email a bunch of files. I can actually just set an expire-able link and say, "Listen, I'll give you till the end of the year to look at this and then the read only version will die," meaning this link will no longer work. It's one way to share information but also has some control over it at the same time.
Then lastly of course, all of our charts are interactive. If you want to click things, turn them on and off you can do that. One of my favorite charts, this is the last thing, is this open invoice [inaudible 01:01:59] or your receivables asset. Because we have a banking background, a lot of times this is used to leverage or to secure a loan or a credit. What I can do is actually do some what/if scenarios. I don't want to look at the top five people that owe me money but maybe the top 10. I can use a slider to recreate another view of how my portfolio looks. Let's just pretend that Potter's Planting is a company that you're probably gonna lose as a customer if I'm firing them or they'll go away. They owe me a lot of money and they're not paying. I can go to the legend and turn them off and see instantly what that does to my portfolio.
We're gonna do those recalcs for you in real time. This puts our portfolio at risk at 55% of [Jul's 01:02:44] Dollhouse. [crosstalk 01:02:50]
Hector Garcia: That's it Mike. We have a couple questions about pricing and is the pricing per client or per consultant ... I'm gonna pull up the CPE question cause some people do need to answer this CPE question cause I need to log off cause it's been exactly and hour. The polling question is up and we're allowing people to put those polling questions in there.
Talk to us about how pricing works specifically for accountants and if there's ... Can you give us a special price because we all want the tool but we want to pay, we want a deal for this.
Tell us about this.
Mike: Right. Super simple pricing. Basically it's an unlimited amount of businesses that you can put into your subscription. We're charging $99 per month for unlimited use by the consultant, right-
Hector Garcia: This is meant for the consultant to pay for not for the client, correct?
Mike: This is meant for the consultant to pay for and then hopefully charge over the top is the most successful use cases. Where as they're building it in the monthly or quarterly engagements.
one really interesting story that came out this week was that someone was doing as their tax returns, they was placing a copy of a Finagraph over the top of a tax return and said, "Hey, I noticed some red flags I think we should talk about. Let's put this on the calendar for next week." He was able to double his build-able advisory hours just by incorporating this into practice.
If you're working with a hundred companies, that's basically a dollar per month per company, it's how that runs down. [crosstalk 01:04:21]
Hector Garcia: If you have one company, one consultant with a hundred clients, you're gonna pay one fee, correct?
Mike: That's right. If you have one company, one consultant and he has a hundred clients, it's $99 per month and then each charge over the top. Basically, you could probably get your money back on one client.
Hector Garcia: I'm gonna switch it over to me because we have a special link, so I'm gonna put it on the screen and I asked you personally get the most, best offer possible that we can do for our attendees. Can you walk us through it? I'm gonna just show it on the screen here. Can you show us through what the offer is or what discount and can we get much longer free trial for attending this webinar?
Mike: Absolutely, I'm glad you asked that. What I want to do for your group was rather than our two week trial, that is normal, we're gonna extend the trial out through the end of the year. You're basically gonna get two months of this free, unlimited use, no restrictions on the use of it. To put it in your hands, let you get to see and feel it, maybe even get prepared for that tax return season coming up, using the story I just told you.
Then also, we're giving you 25% off for that first year. 25% off every month for the first year, so what's that come down to, about ... $75 [inaudible 01:05:45]. $75. All you have to do to get that is when you use the buy now button on our pricing page, in the notes section just put the code POWER25. I know that you were here with us today and I can give you that 25%.
Hector Garcia: Could you give us your email address or contact person if we need to contact you specifically?
Mike: Very simple, just contact email@example.com. So it's mike, M-I-K-E @ finagraph.com.
Hector Garcia: So I invite everybody that has multiple clients to add your clients in your free trial. That's not gonna cost you anything. Test out doing some consulting work and working with your clients. Hopefully it's a good tool that can work. It's not for everybody but I'm sure that a lot of you will find a lot of value here.
I really thank everybody for attending. Sorry about the technical difficulties. I literally got to Miami at 9 am this morning. Michelle was in Toronto in the hotel, so I really appreciate this particular one. There will be another Power Hour next week because we're gonna take a break for the holidays, for Thanksgiving, so there will be one next week. You will get an invitation for that. Join Michelle's LinkedIn group. Thank you Mike so much for attending. I'm gonna send you some of the questions that were left not answered and please provide some feedback.
Thank you everybody. I will be ending the webinar.
Mike: Thanks Hector. Bye bye.