City Taxes – Total Sales By Customer with contact info:
Why do we need to be able to do this?
1) To report for business tax purposes – usually we need to know sales for the cities we do business in.
2) The methods shown here can be applied in many other useful contexts.
What you will learn:
You will learn which reports can include customer contact info and which one’s can’t and I will explain why.
You will learn how to export the reports you need to combine into excel
You will learn how to format the report in excel to make it much easier to read.
You will see how a simple macro will speed up this process.
You will learn how to write the vlookup formula in excel to grab information from one report and include it in the other.
Back story:
When reporting to your local city for business tax purposes we need to see Sales by City. Many companies over pay their city taxes because they simply report total sales and pay taxes on that. The forms from the City of Los Angeles (for example) do not make it clear that there are in fact different rates for Sales earned inside the city, inside the state, and outside the state. I run a report for my clients breaking the sales down in this manner. Here’s where I ran into trouble. I can run a Sales By Customer Detail for the year which shows every sales transaction by customer. This makes for a large report and I cannot total it by “City” because it is already grouped by customer. I could export this to excel and with some formatting I can get it sorted and even sub-totaled by City, and state. There’s a better way though. There are 2 reports I can run, export them into excel and with a little formatting and a Vlookup formula I have the perfect solution. As noted above you will also begin to see how the tools I am about to demonstrate for you in this video tutorial will have application elsewhere. You will also see how to use some other tools in Excel that will help you analyze data better than you can do in QuickBooks alone.
Once I figured out this solution I was able to quickly and easily sort and group the data for my clients so that I can offer them other really valuable information. For example I can sort by amount and see who was responsible for the greatest $ sales to my company and I have their contact information right there so I can send an e-mail, call to thank them or even offer them a discount on something!
Watch the video tutorial to see how this all comes together:
Video Tutorial - Click to watch
Download The Excercise files here ($2.00):
File Download - get the exercise files here
