Archive

Archive for the ‘Data’ Category

Analyzing Sales from QuickBooks To Excel

March 14, 2010 Leave a comment


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.

Description:

Click to Download or watch

File Download:

Video Tutorial:

Advertisements
Categories: Data, Formulas, Sumif, vlookup

How to track your business in excel part 3

November 11, 2009 Leave a comment

For a better quality version of the video web cast:

http://nerdenterprises.na6.acrobat.com/p84834671/

Visit our Excel blog for more: http://www.msexcelguru.com

Here we really complete the picture for you by showing you how to enter and then report on your financial transactions.

We show you how to set up the hyperlinks so that if you expand upon this concept you will be able to incorporate any new items in the ‘Home’ or ‘Menu’ Tab.

We review the setup of a list and data validation to update the way the source account works, because that account really needs to represent sources of payments and deposits. This also makes it possible to rely on the “Destination” accounts for tracking all income, expenses, asset purchases and payments on liabilities.

Now that the template is set up we can show you how to enter your transaction data:

This is what the template looks like with everything entered. If there was a deposit to be entered the Source account would be the bank account where the money was deposited and the destination account would be the appropriate income account. This is not set up to show invoices to and payments on accounts receivable. This can be done but would have to be done on a private consult because it would be very involved.

Once we have data in we can begin to report on it. We show you in the last 3 minutes of the web cast how to create a pivot table on this data reporting on expense by category and then expanding upon that by showing you how to further report the expenses daily based on the date of the transaction.

Figure 1 – Pivot Table – Daily Expenses click for larger image

We will post a bonus web cast going into more detail on these pivot tables and how you can use them to report quickly on the information in the sales and transactions.

How to Track Your Business in MS Excel Part 2

November 11, 2009 1 comment

Click here to watch the webcast:

http://nerdenterprises.na6.acrobat.com/p20159112/

Bonus Web Cast (Too Long For YouTube) setting up your accounts:

http://nerdenterprises.na6.acrobat.com/p44179111/

MS Excel Info: http://www.msexcelguru.com (you’re here already)

QuickBooks Info: http://www.quickbooksnerd.com

In this part we show you how to set up your transaction register and show you how to set up your lists and then validate your Columns in your transaction register so you have drop-downs to choose from in your transaction register. There are two columns for accounts. In accounting terms it’s because every transaction needs a debit and a credit. In lay-person’s terms you need a source account and a destination account as shown here:

The source account is essentially how I paid for something (ie Bank account or Credit Card) or if I received money, which bank account that money went into (which bank account).

The destination account is where the money went or what the money went to pay for (ie office supplies, equipment, etc..)

We are going to show you how to set up lists in this web cast.

After that you will learn how to use those lists to populate drop-downs in the Excel Template.

It is important wherever possible to limit data entry to information that is populated in pre-set lists to ensure consistency. Eventually we want to run filters and pivot tables on the data and if there are variations on spellings this will take extra time in order to clean up for proper and accurate reporting.

In the next web cast we will begin to create some pivot tables and you will be able to see how powerful this is in terms of being able to use a simple template like this one created in Microsoft Excel to track your financial accounting information.

For a bonus web cast showing you how to set up your “Account List” click here:

http://nerdenterprises.na6.acrobat.com/p44179111/

This is too long for YouTube! Sorry

MS Excel – Data Filters and Table Formatting

September 10, 2009 Leave a comment

MS excel is great for tracking and analyzing information such that you can quickly sort and filter your information to get at what you want and get it quickly. There are some really easy to use tools that excel offers in terms of sorting, filtering and formatting that make this all much easier and you don’t need to be an excel wiz to learn this stuff.

When you filter data in excel you take the information in a list in a given column and tell excel that you only want to see the rows containing what you select. You can do this over multiple columns in order to filter the data down based on very specific criteria. In the example you’ll see here we show you a list that we are building of all of the tutorials we’ve recorded. You’ll see how you can filter a list like this down to all QuickBooks classes recorded in a specific year and you could even take it further to get it down to a specific month. When you understand the concept, then you can apply the mechanics to any list you might have in ms excel.

Also we demonstrate how you can format your data as a Table which is a specific designation in excel and once you do this you have formatting options that can be accomplished manually but you will hear why this is much better in the web cast.

Please enjoy the web cast!