Archive

Author Archive

We’ve Moved!

June 17, 2010 Leave a comment

Please update your settings. You can find the Excel Help on “Nerd’s Blog” as a category across the top. The URL http://www.msexcelguru.com will take you right into the MS Excel Help Category.

Here is the RSS Feed for Nerd’s Blog:

http://nerdenterprises.com/blog/feed/

Categories: Uncategorized

Reaching Your Goals With MS Excel – The Goal Seek Command

April 20, 2010 Leave a comment

There are many commands in Excel and depending on what you use MS Excel for you may or may not need to learn all of them. The Goal Seek command is one we find is very useful. Normally when we price our products we simply set a base price and then add the sales tax based on the tax rate. What if we want to set the price including sales tax to be a specific number? Goal Seek is a great way to do this. An even more useful application of this command comes into play when we are analyzing our Sales goals. We sell certain products, we know what our costs are and we think we have nailed down the price we want to sell it at. Well now it’s time to look at some scenarios. Specifically based on the current set of assumptions, how many do we have to sell to generate the desired total dollar profit? The “desired” part is where the goal seek command comes in. We can use it to tell excel to change the quantity sold in order to force the profit to be equal to our “goal”. Get it? Hopefully the video tutorial will make it more obvious. We can look at the product information in different forms in terms of how things are calculated so that we can use the Goal Seen Command in different ways. This web cast will demonstrate how this works.

The Key components you have to understand in order to use the goal seek command are as follow. You are going to set the value of a formula based cell to a specified amount by changing a cell who’s value the formula based cell depends on. In other words in the sales tax example. I am going to change the total selling price including tax to equal some number by changing the base amount.

Finally in this web case we will demonstrate our powerful scenario based margin calculator which enables you to get around the Goal seek command (in other words we’ve done the work for you) so that you can calculate your product prices in various scenarios without having to know this command at all.

Description:

Symbol To Look For:

Video Tutorial:

File Download:

The File Download is the excel file we use to demonstrate the Goal Seek Command

If you are interested in the Profit Margin Scenario Calculator it is currently $65 in our learning center

 

 

 

 

 

Categories: Goal Seek

Download The $2 Paper on Social Media – From Action Plan To Strategy

March 19, 2010 Leave a comment

Social Media – From Action Plan To Strategy

What is My strategy / what do I want?

What can I offer people?

What am I looking for from people?

What are my topics of interest?

Then where do I find the people who meet the criteria set forth above (ie the people who want what I am offering, and the people who are offering what I want)?

This is what you will read and even watch some videos about in this $2 Paper on Social Media – From Action Plan To Strategy

Add this to your cart here I can take you on a brief journey with a year’s worth of my learning all for only $2.

Categories: Uncategorized

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:

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:


Setting Goals and Tracking Progress – Using Excel to track progress at the gym

January 6, 2010 Leave a comment

Web Cast Date: 1/14/09

Video


Running Time: 00:19:57

It’s important to be humble enough to recognize that I don’t know everything. Based on that I seek help in areas of my life that are important to me. The Gym is one example. This was my web cast from Jan 14, 2009 – just about a year ago. I am no longer working with the trainer – cut-backs.

Microsoft Excel is the tool that I use to track progress on anything that involves numbers. It is made for this sort of thing so that makes the most sense. In this web cast I show you how to use MS Excel to enter the statistics and then graph them. I also show you how to change the parameters on the graph to be sure you are tracking your progress with meaningful results. This not only allows me to track progress but it also gives me the opportunity to analyze the information and make sure I understand it. As you will see I can look at the graph and see things that won’t be as obvious from only looking at the numbers. Then I can ask questions if things don’t make sense to me.

When setting goals it will be difficult to gauge my progress without doing something at some regular intervals to stop and look at where I was when I started compared with where I am today. It is useless in life to compare myself to others and very meaningful to compare myself today to myself yesterday so that I am always sure I am growing and improving. Even if I see a setback, I am still growing because I am taking the time to measure my progress and this way I can make corrections – changing the actions to change the results!

Microsoft Excel is a great tool for financial analysis. This is an example of something non-financial where excel is also a GREAT tool to use for tracking and analysis.

Quick Update From Me (Seth)

December 30, 2009 Leave a comment

Check our upcoming events page – I’m doing a live webinar on Monday Jan 4 from 10am – 11:30 am and it’s only $10!!

Visit my updated Personal Blog for some other rants and a detailed write-up on the upcoming webinar.