Archive

Archive for the ‘vlookup’ 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

Daily Budget

February 15, 2010 Leave a comment

When creating a budget in excel whether it is for your personal finances, your business, or both the basic layout and structure is the same. Your columns describe your time line and your rows describe your categories or sources of income and expenses. If you are using something to track your income and expenses like QuickBooks or Excel then you will want to set up your categories in a manner consistent with what you have in that program.

The key to setting up a budget that works is really not just setting up a budget, but setting up something that is fun and easy to use as well as something that makes you check back with it regularly so that your attention is repeatedly placed on it. Otherwise you can overspend and then go back and say oops! Oh well, better luck next month! That won’t really help your situation though.

MS Excel makes it very easy to set up and customize your budget in exactly the manner that best fits your situation. It’s the flexibility of excel that makes it so great, and you can easily expand upon things to increase the level of detail.

Your budget will start to get real exciting when you begin to see the results. It’s sort of like working out at the gym. At first it is a chore, until people start noticing. Then it gets exciting. Well the budget gets exciting when you tie it up at the end of the month and see that you can transfer $1,000 to your savings account where you were never able to do that before.

In addition to learning how to set up a budget, this video tutorial teaches you a number of skills in excel – some simple, and some pretty advanced:

Simple formulas – addition, subtraction, and division

Weekday formula

Vlookup

Getting the date to light up when it matches today’s date

Conditional Formatting with a conditional formula embedded.

How to calculate and analyze your variances

Formatting to make it easy to read.

Please enjoy the video tutorial – MS Excel – Setting up a daily budget by clicking on the image:


How To Calculate Quantity Price Breaks

December 10, 2009 Leave a comment

WATCH THE WEBCAST (VIDEO)


If you are in the business of selling products then you will find this tutorial very useful. The excel template we go over here will enable you to lay out your price break ranges and set up a table so that you can populate an estimate that will look up the price when a quantity is entered and calculate the total taking into account that over certain quantities, the unit price goes down. In the web cast we teach you how to write the formula that accomplishes this. You can use this concept in a more sophisticated design of your own – or you can download the enhanced template from our learning center and use it however you like. The version in the learning center is a much more enhanced version that includes an estimate form you can populate with your own items in a drop-down. It also of course has a link to this tutorial so you can watch it as often as you need to.

If you sell products then you know it is a violation of UCC Code to charge different prices to different customers for products, unless you justify it with a quantity discount. QuickBooks will not calculate this for you – you would have to set up different items for each price level. At the same time it is very common to offer price break points as an inducement to get clients to purchase a larger quantity thereby increasing your total sales volume. You can adjust the prices as well as the break points. When you do purchase the template (Only $25.00 in our learning center) we recommend you save a master copy in case you mess up the formatting or just want to start over.

Please enjoy the web cast.

WATCH THE WEBCAST (VIDEO)

How to track your sales in MS Excel

October 28, 2009 Leave a comment

Click here to watch the web cast:

How to track your sales in MS Excel

Some of us have small businesses and cannot yet afford to go to the expense of purchasing and learning a program like QuickBooks. Especially if our background is in something non-financial (like the products or services we offer). In these cases we can use a spreadsheet to track our business’. As with everything in life we have to consider what information we need and in general we want to keep these things as simple as possi/ble. So in this week’s web cast we are going to look at tracking sales. In the next few weeks we will be building on this theme in short 10 minute web casts (so they can fit on youtube J. www.youtube.com/nerdenterprises for all of our tutorials on both QuickBooks and Excel.

The simple answer on sales is setting up a template that provides a foundation that fosters consistency and simplicity. All too often we get caught up in the details and wind up creating something too complicated to maintain. The phrase keep it simple REALLY applies here. Then you can expand on it as you go – that’s the beauty of doing things in excel – you are the developer so you create the things you need as you need them and you don’t need to wait for the next release!

Figure 1 – Simple column layout

Notice in the example above I kept it simple. Sales tax is calculated separately. You will see in the web cast I was about to make it more complicated by combining the Sales Tax calc with the total. Then I realized I had actually laid it out better than that. It might seem more efficient to do it the other way, but how will I be able to see my total sales tax for a given period? I will have to write complicated formulas to back it out of my total. Then if I have multiple sales tax rates that computation will start to get really complicated. The way it is laid out here, later on I will be able to create a simple pivot table that shows me Sales Taxes by rate for a specified time period. Simple – then I can write my check to the State Board of Equalization based on a pivot table which can be created once and updated when I need to. For non-taxable “Labor” I simply set the sales tax rate as ‘0.00%”.

Sometimes we like to make things more complicated to impress people and then it costs us. That’s right our vanity based on the desire to impress people with our excel skills will cost us dearly in the end. Much more impressive is to show people something that was so well thought out that it was kept simple enough for anyone to use!

In this web cast I go over how to set up a simple template to track sales for your business. Next week we will look at how to track the expenses.

This question came in from my new friend Julio Rios. He has a great product and plans to put more out there. Visit his website at www.qintexsolutions.com (make sure pop-ups are enabled).

Visit the Ask us Page here and post your questions and we will try and get your answer up quickly and/or record a web cast for you. Got a QuickBooks question? Visit our QuickBooks blog at http://www.quickbooksnerd.com

Finally!… Click here to watch the web cast:

How to track your sales in MS Excel

Timesheets – Conditional Formatting, Vlookup, & Weekday formula

August 26, 2009 Leave a comment

MS excel’s conditional formatting feature allows you to highlight data based on certain criteria and especially in MS Excel 2007 there are many options in terms of what you can bring to light and how.
Since time allowed we also gave you a bonus tip on this one – how to write a simple Vlookup formula on a named range so you can being in data from a set list based on other criteria.

This is useful in creating a timesheet as you will see because timesheets are very much dependent on having a date in there, and it is handy to have today’s date light up to bring attention to it. When designing forms it is always good to make it as easy as possible to use. Adding little bells and whistles like this can help your end user. Formatting goes a long way to, but we did not get into this. E-mail us at classes@nerdenterprises.com for a copy of the timesheet we use at Nerd Enterprises, Inc. as well as the sheet created in this video tutorial.

Please enjoy the video tutorial: http://nerdenterprises.acrobat.com/p31944487/