FORECAST: Sunny Skies!

Forecasting can be a tricky business (just ask anyone whose job it is to predict the weather). The problem with coming up with an accurate Forecast is the fact that you are dealing with Historical Data which doesn’t necessarily correlate with the future.

Over time, however, much data is essentially Linear and can, therefore, be used to predict reasonably viable future outcomes.

The good news, of course, is that when calculating Forecasts using historical data in Excel, our favorite spreadsheet program has a Built-in Function for this purpose. The FORECAST function has the following syntax:

=FORECAST(X, Known_Y's, Known_X's)

X is the data point for which you want to predict a value.
Known_Y's is the dependent range of data.
Known_X's is the independent range of data.

Stated in Easier-to-Understand English:

=FORECAST(ValueToForecast, RangeY, RangeX)

ValueToForecast is the point in the future which you need to forecast.
RangeY is the list of values which contain the Historical Data to be used as the basis of the forecast, (Sales Figures are classic…).
RangeX are the intervals used when recording the Historical Data. Months, for instance (Just Be Sure to express the Months as a Number!)

Let’s say that you wish to forecast the Sales for October-December using the Example below. Here is what you should do:

1. In B11 put:
=FORECAST(A11,$B$2:B10,$A$2:A10)

2. Copy/Drag the above formula to B12:B13

Notes:
1. Many Excel users find this Slightly Counterintuitive, so you may wish to spend an extra minute examining the syntax example.
2. Remember that the underlying assumption for your Forecast is Linear Data. If your data does not fit this model, you may wish to explore other avenues.

FORECAST – Give it a try. You may find it says, Sunny Skies Ahead!

SUMIFS (Not Just SUMIF)!

As you may know by now, I am a big proponent of using Interactive Reports. Reports of this nature can be made with a variety of innate Excel tools, including DFunctions, Sumproduct, Vlookup, etc.

There is one Typically Overlooked function for making interactive reports, however. The SUMIFS, (Note the plural), is a surprisingly powerful tool for this purpose.

First of all, the Syntax for SUMIFS:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Unlike its close cousin, SUMIF, the SUMIFS function allows you to conditionally Sum values with Multiple Conditions, rather than just one.

For instance, let’s say you have a table/database that has multiple entries of Sales for Months and Weeks. Using a SUMIFS formula, along with dropdown boxes for the Month and Week, you can make an Interactive Report that is contingent on the parameters you choose.

Bottom Line: If you have ever used the Little Cousin, SUMIF, and wanted more flexibility, you owe it to yourself to try out the vastly more powerful SUMIFS.

As I have said before, Interactive Reports – They are so easy, They'll Make You Smile!

Top Ten Keyboard Shortcuts for 2013

As you may know, every year I like to review my Current Favorite Excel Keyboard Shortcuts. This obviously changes over time as I learn new tricks and incorporate them into my daily work with Excel. New keyboard shortcuts can take a little time to get accustomed to, but it is almost always a worthwhile endeavor.

As I have said in the past, learning and using Keyboard Shortcuts in Excel will greatly enhance your speed and efficiency. Using shortcuts also will help relieve stress on your wrist from overuse of the mouse, and will Set You Apart from the Rank-and-File Excel Users.

So, without further ado, here in Reverse Order are my current favorite (and highly recommended) Top Ten Excel Keyboard Shortcuts for 2013:

10. F1 (Pulls up Excel Help – We can all use a little help now and then…)
9. F5 (Show the Go To menu – particularly useful with Named Ranges)
8. Ctrl + P (Pulls up the Print Dialog Box – Nice!)
7. Alt + + (Inserts AutoSum – Saves you from going to the toolbar)
6. Alt F (Pulls up your Files Menu – Very convenient!)
5. CTRL + 9 (Hides the selected rows – Cleans up your worksheet…)
4. F11 (Creates a Chart from your selection – Still an all-time favorite!)
3. Ctrl + N (Inserts a New Workbook – I use this often…)
2. Ctrl + Z (Undo Previous Action – Handy when you’re working fast!)

And My Number One Favorite Shortcut for 2013 is (this is even a little surprising to me…):

1. Ctrl + [ (Reveals Direct Dependents – Incredibly useful for quick audits!)

That’s it as of April 24, 2013! Depending on the details of your own work, you may rank them differently, but you may well find some of them indispensable in the future.

Happy Excelling All!

Looks Matter!

Looking Good Matters. Simple as that. This applies as much to business as it does in any other facet of society. Somewhat curiously, there are a large number of Excel practitioners who completely Dismiss the importance of aesthetics, saying it is “the data that counts”.

Obviously, the viability of the data is of preeminent importance, but if no one looks at it or takes it seriously, what does that matter? Studies have repeatedly shown that Emotions play an important role in the users’ experience. They also tell a good many about Your Brand, product or service. Just look at the products by Apple.

When it comes to making Engaging Charts, the Design Ribbon, ushered in with Excel 2007, clearly gives you several ways to enhance the appearance of visually-displayed data. Among the possibilities that are readily are the following Options for you on this ribbon:

1. Change the Chart Type (piece of cake!)
2. Chart Layouts (Many ways to display titles, labels, legends, etc)
3. Chart Styles (A generous variety of color and 3-D options)

You can also go to the Format tab and find a great many ways to make your work stand out in a Sea of Mediocrity:

1. Shadows
2. Glow
3. Soft Edges
4. 3-D Format
5. 3-D Rotation

So, once again, why is this important? Well, consider this: If your audience is going to Understand the Message your chart is conveying, they are going to need to Be Engaged by it First! The two major ‘C’s of good communication:

1. Captivate Your Audience
2. Convey Your Message

Good Communication is what it is all about!

Database Best Practices

Using Databases is Essential to getting the most out of Excel. They are also quite often abused. It always amazes me how many otherwise gifted Excel users make Fundamental Errors when it comes to the proper use of databases. It is for this reason that today’s blog is precisely focused on the Three Best Practices for working with Databases.

Other Excel authors may go on and on about several ways to make your data more accessible. If, however, you follow the following Indispensable Guidelines, you will be in excellent shape to slice and dice your data into Meaningful Information:

1. Do Not Mix Data with Calculated Fields
     a. Use a reporting page for any calculation

2. Avoid Blank Records
     a. Zero or N/A is better than blank

3. Break Down or Parse Your Information into the Smallest Discreet Portions Reasonable
     a. Use the Text-To-Column tool if needed

By following these Easily-Adapted practices when building your databases, you will be more readily able to use Pivot Tables, Boolean Functions, and other Excel Magic to extract the information you want. It all starts with Database Best Practices!

Cheers!

Analysis ToolPak

If you are an Excel user who is continuing to get deeper into data analysis in Excel, you will probably want to investigate an often-overlooked set of tools that are available. The Analysis ToolPak is an Add-In program that is available when you install Microsoft Office.

First of all, you need to load it onto your ribbon (Excel 2010 will be used for the example):

1. Click on File, and then click Options.

2. Select Add-Ins, and then in the Manage box, choose Excel Add-ins.

3. Click Go.

4. In the Add-Ins Available box, select the Analysis ToolPak check box, and then click OK. (See illustration):

You can then access a special Data Analysis group on your Data ribbon.

So, what sort of Cool Tools are available in the ToolPak? Among others, there are the following:

• Anova
• Correlation
• Covariance
• Descriptive Statistics
• Exponential Smoothing
• Fourier Analysis
• Histogram
• Moving Average
• Rank and Percentile

Each of the tools provides a very User-Friendly Wizard, and the results will likely be quite useful to any Analyst or Excel Guru-Wannabee.

Special Note: If you are using an older version of Excel, you should know that many Improvements were made, and new algorithms were created to improve the accuracy of the statistical, financial and math functions in ToolPak. That being said, the previous versions are quite suitable for a majority of business purposes.

The Analysis ToolPak. A worthy addition to your Excel Tool Belt!

Unique Values

There are times when manipulating Unique Values or Items in your database is essential to your analysis.

Counting Unique Values
For instance, let’s say you have a database of everyone who has accessed your website, and you would simply like to know the Number of Unique Users. A clever formula (assumes your data is in A1:A95) does this very efficiently:

        =SUMPRODUCT(1/COUNTIF(A1:A95, A1:A95))

Deleting Duplicates
If you want to know the specific items which are Unique Values, and you don’t mind deleting the duplicates, you can use the Remove Duplicates command in the Data Tools group on the Data tab.

Creating a Separate List of Unique Values
If you wish to retain the original list, and create a separate list of Unique Values, you can turn to the Advanced command on the Sort & Filter group on the Data tab. Select your range and do the following when the menu appears:

1. Select the Copy to another location option

2. Insert the starting cell in the Copy to dialogue box

3. Put a check mark in the Unique records only box

4. Your unique items will appear in the newly-created range

Unique Values can pose Unique Issues but, happily, they are easily solved.