ALTOM: Time digging into Excel ‘goodies’ will pay off

I make fun of Microsoft tools; but then, who doesn’t?

In a previous column (Jan. 31), I pointed out that many of the features of Microsoft Word were displayed in a way that provided more marketing pizazz than business value. Word suffers, in my view, from trying to do too much.

But I don’t feel the same way about Microsoft Excel. To me, when it comes to business power, it’s hard to find more of it in one relatively cheap package than Excel, except perhaps for something like OpenOffice, which is free for the taking but doesn’t do everything Excel will do. Excel has its limits; I’m not impressed with its statistical analysis capabilities, for example. But it will do some impressive things that most users never even attempt. Here are two of them that I particularly like.

Most companies compile more data than they know they have, because it’s generally scattered around the office in various forms. There may be sales data by region and salesperson, production data, shipping data, even data about leases and utilities. Much of it could be corralled and stored in Excel as a kind of poor person’s database, although I don’t necessarily recommend doing that. Use a database to do a database’s work whenever possible, but Excel isn’t a bad alternative for a cheap solution.

Now, data’s not of much use until you start associating it with other data so you can see what conditions are driving certain outcomes, or at least which ones occur at the same time, so putting all that disparate data into one or more worksheets can have considerable potential.

Let’s say you’re a financial adviser doing some marketing. Which marketing efforts produced more new clients? More referrals? More profit? You can look at the data “swung around” in this way using Excel’s pivot tables.

A pivot table has both rows and columns, which is one of the keys to its versatility. This arrangement lets you do what more sophisticated database applications can do, which is to associate one variable with another and see what happens at the intersection.

You can generally tell when an analysis is a good pivot table candidate when you say the words “per” or “by.” For example, our hypothetical financial adviser might be analyzing referrals by marketing effort. If he’s pursued a half-dozen different marketing angles in one year and recorded where his referrals have come from, he can now see where they come together in a table. He also can filter by marketing campaign and referral source.

The pivot table is often the analyst’s secret weapon and isn’t used nearly often enough by businessfolk. It may take a little time to get familiar with pivot tables, but they are well worth the effort.

On another business front, there is a movement today away from simple seat-of-the-pants decision-making, and toward more rigorous, quantitative planning that involves optimization calculations. The classic example is figuring out how much of each product to make, given material costs, labor costs, number of manufacturing lines and other variables to maximize profits or minimize costs as much as possible.

In real life, such decisions can involve dozens of variables. Our hypothetical investment planner may have several variables of his own in his quest to maximize his profits, including referrals, marketing costs and even his clothing budget. You can, of course, simply trust intuition to guide you, or you can turn the whole business over to the computer, which can figure out an optimal combination. That’s what Excel’s Solver add-in can do. It comes with Excel, but you have to specifically turn it on to use it. Consult Excel’s help file for instructions or one of the many Web sites devoted to Excel.

Solver has been a part of Excel’s arsenal since the early 1990s, but almost nobody ever uses it, in part because it’s fairly complicated to set up. Solver essentially calculates the optimal solution to two or more simultaneous equations. These equations are mathematical business models, so you’ll need to construct those first Most of us would sooner be beaten with a rock than attempt this, but it’s worth doing if you need to squeeze pennies until Lincoln gasps for air. Then you tell Excel what the constraints are for optimizing—the limit of the marketing budget, for example. Then Solver figures out the optimal mix of expenditure and profit, based on the model and the constraints. You either maximize profit or you minimize cost, but you can’t make Solver do both at once.

Excel has more goodies that are rarely used. One is Goal Seek, a kind of simplified, downscale Solver. Excel has a “camera” tool, which lets you watch one part of a worksheet while working elsewhere, a kind of wormhole from one place in Excel to another. Take some time and go exploring the Excel landscape. There are some buried treasures out there.•

__________

Altom is a consultant specializing in pairing businesses with appropriate technology. His column appears every other week. He can be reached at taltom@ibj.com.