ALTOM: Time digging into Excel 'goodies' will pay off

Tim Altom
May 21, 2011
Back to TopCommentsE-mailPrintBookmark and Share
Tim Altom

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.


Post a comment to this story

We reserve the right to remove any post that we feel is obscene, profane, vulgar, racist, sexually explicit, abusive, or hateful.
You are legally responsible for what you post and your anonymity is not guaranteed.
Posts that insult, defame, threaten, harass or abuse other readers or people mentioned in IBJ editorial content are also subject to removal. Please respect the privacy of individuals and refrain from posting personal information.
No solicitations, spamming or advertisements are allowed. Readers may post links to other informational websites that are relevant to the topic at hand, but please do not link to objectionable material.
We may remove messages that are unrelated to the topic, encourage illegal activity, use all capital letters or are unreadable.

Messages that are flagged by readers as objectionable will be reviewed and may or may not be removed. Please do not flag a post simply because you disagree with it.

Sponsored by

facebook - twitter on Facebook & Twitter

Follow on TwitterFollow IBJ on Facebook:
Follow on TwitterFollow IBJ's Tweets on these topics:
Subscribe to IBJ
  1. I never thought I'd see the day when a Republican Mayor would lead the charge in attempting to raise every tax we have to pay. Now it's income taxes and property taxes that Ballard wants to increase. And to pay for a pre-K program? Many studies have shown that pre-K offer no long-term educational benefits whatsoever. And Ballard is pitching it as a way of fighting crime? Who is he kidding? It's about government provided day care. It's a shame that we elected a Republican who has turned out to be a huge big spending, big taxing, big borrowing liberal Democrat.

  2. Why do we blame the unions? They did not create the 11 different school districts that are the root of the problem.

  3. I was just watching an AOW race from cleveland in 1997...in addition to the 65K for the race, there were more people in boats watching that race from the lake than were IndyCar fans watching the 2014 IndyCar season finale in the Fontana grandstands. Just sayin...That's some resurgence modern IndyCar has going. Almost profitable, nobody in the grandstands and TV ratings dropping 61% at some tracks in the series. Business model..."CRAZY" as said by a NASCAR track general manager. Yup, this thing is purring like a cat! Sponsors...send them your cash, pronto!!! LOL, not a chance.

  4. I'm sure Indiana is paradise for the wealthy and affluent, but what about the rest of us? Over the last 40 years, conservatives and the business elite have run this country (and state)into the ground. The pendulum will swing back as more moderate voters get tired of Reaganomics and regressive social policies. Add to that the wave of minority voters coming up in the next 10 to 15 years and things will get better. unfortunately we have to suffer through 10 more years of gerrymandered districts and dispropionate representation.

  5. Funny thing....rich people telling poor people how bad the other rich people are wanting to cut benefits/school etc and that they should vote for those rich people that just did it. Just saying..............