QuickBooks gives us some great reporting options, but in some cases when we really want to take the analysis a step further it makes more sense to export a report into excel and then format it so we can write some formulas to give us meaningful analysis.
So the first step is to run a Sales by item detail report in QuickBooks. One of the first things you may notice is that QuickBooks does not total the quantities of the units sold. When I am analyzing sales data for a client that becomes a really important piece of information. How many did I sell and what was the average price? So I can compare with what I am selling it for now. I might very quickly find that I want to lower the price. So knowing how to do this stuff in excel can really help you make intelligent decisions about your business.
Once you export the report in QuickBooks you want to trim it down to just the raw data. Then it becomes fairly easy to write the formulas and create a drop box allowing you to choose a product and instantly get the information you want about that product and it’s sales.
In this video we show you how to write a concatenate formula, a sumif, and vlookup. We also show you how to define a named range and use that range to populate a drop-down inside of a cell.
Watch The Screen Cast:
For the original raw footage:
http://nerdenterprises.adobeconnect.com/p16666050/
Download the file used here:
File Download
Dear Seth
I suggest you watch this video and see the same thing I see. I find that you are talking about steps that you are doing but the video is not up to what you are showing. I saw this also with the course i purchased that you are ahead of what is being showed on the screen.
I watched this video and nothing you are doing or saying can I see that you are doing it and where you went to do it.
I tried this from two computers to ensure is was not an issue with my computer. I find it upsetting because I see how much you know and you have a great voice to deliver but I find I i am not able to follow at all and I walk away learning just that a concept exist. Watching it again does not help. I hate to sound like a critic or a complainer I just want to give you back honest and real feedback.
Please let me know your thoughts.
Yosef Smetana
Thanks Yosef! I am sorry you’re having trouble.
This video, like the Cash Flow webinar is recorded using Adobe Connect. All of my newer “Free” screen casts are recorded in Camtasia and this issue does not come up (just watch anything that you see on the front page of the blog here).
You are correct, it is not a computer issue. It is actually a bandwidth issue. If you are on a wireless connection (and I am guessing you are) then you can get this issue, especially if it is not a good strong connection. If you can try connecting an ethernet cable (Cat 5) to your computer when watching these, they playback should be much better. I use the same format for all of my 1 on 1 training and I only very rarely get a complaint. Eventually I will re-do the Cash Flow using Camtasia (not live) so that the quality is perfect high definition. I am also starting to re-purpose all of my recorded training using Camtasia, streaming off the web (book in a blog) and there again this will no longer be an issue. Just to give you an idea of what I am talking about, my basic bookkeeping course should be ready in a few weeks and can be seen here (School Of Bookkeeping With QuickBooks).
Thanks again for the honest feedback and let me know if there is anything I can do to help you.