IBJOpinion

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.

ADVERTISEMENT

Post a comment to this story

COMMENTS POLICY
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
ADVERTISEMENT

facebook - twitter on Facebook & Twitter

Follow on TwitterFollow IBJ on Facebook:
Follow on TwitterFollow IBJ's Tweets on these topics:
 
Subscribe to IBJ
  1. Uh, sorry Johnnie, but you are incorrect. Despite the assertions by yourself and various defenders and captains, sports attendance is NOT off significantly at most sporting events in the US. Variances in attendance has been in the range of single digits, both + & - for years now. MLB has had most of its best overall attendance nubers in the last decade, and that trend has been consistent for most major sporting events. The number one issue cited by most fans when asked about attendance is the overall cost of attending. The presence of HD and big screen televisions in home doesn't even register, as a factor for not attending an event. VALUE in the product is the key, and apparently is something lacking in the current ICS. What other explanation is there when with what is routinely touted as the "best" racing on the planet, fans are staying away in DROVES. A "close" title battle into the last event at Fontana, with the "cars and stars" of the ICS, and who showed up? MAYBE 8K. Sorry, but HD TV isn't to blame for that kind of fan apathy.

  2. Do you need finance to establish your business ? Are you interested in getting a loan at 3% from our private company? If so please Email: suntrust_oil@blumail.org

  3. If she was worth the $ the public outcry over direct tv dropping them would have kept them on their dishes as we have seen with other companies. I too quit watching channel 13 after she showed up since I left channel 8 because of her all show rather than production results. When Randy on 8 corrected her she had a big head and incorrectly challenged his correction for pronunciation of a city. Other antics while she matures was too much for me with her very inaccurate forecasts. All the forecasters were predicting rain until Thursday except Chris. They predicted sunny on Thursday but instead of rain until Thursday upon which the sun would finally make it out in full glory Chris was right on the money just as I too predicted looking at the radar on weather.gov. One thing I love about Angela is the fear you can see in her every time it thunders in the winter. It far exceeds the entertainment value of her body language (high heel noise drags, depression, etc) when her forecasts are so incorrect. Her hair stands on end, you have to see it!!!

  4. Good Day, Apply For A Loan I am Mr Fernadez Antonio, a private Loan lender and a cooperate financial for real estate and any kinds of business financing. I also offer Loans to individuals, Firms and cooperate bodies at 3% interest rate We offer any kind of loans. email us via fernadezloaninvest@outlook.com LOAN APPLICATION FORM First name:......................... Middle name:......................... Last name:........................................ Date of birth (yyyy-mm-dd):....................... Gender:........................................... Marital status:................................... Total Amount Needed............................... Loan Duration.................................... Address:.......................................... City:............................................. State/province:................................... Zip/postal code:.................................. Country:.......................................... Phone:............................................ Fax:.............................................. Mobile/cellular:.................................. Monthly Income.................................... Occupation:....................................... Best Regard, Mr Fernadez Antonio.

  5. i will love to share my testimony to you all the people in world i got married to my husband about 2 year ago we start having problems at home like we stop sleeping on the same bed,fighting about little things he always comes home late at night,drinking too much and sleeping with other women out side i have never love any man in my life except him. he is the father of my child and i don't want to loose him because we have worked so hard together to become what we are and have today .few month ago he now decided to live me and the kid,being a single mother can be hard sometimes and so i have nobody to turn to and i was heart broken.i called my mom and explain every thing to her,my mother told me about DR.okoro how he helped her solve the problem between her and my dad i was surprise about it because they have been without each other for three and a half years and it was like a miracle how they came back to each other. i was directed to DR. okoro on his email:okorospell@gmail.com and explain everything to him,so he promise me not to worry that he will cast a spell and make things come back to how we where so much in love again and that it was another female spirit that was controlling my husband he told me that my problem will be solved within two days if i believe i said OK So he cast a spell for me and after two days my love came back asking me to forgive him i Am so happy now. so that why i decided to share my experience with every body that have such problem contact Dr okoro the great spell caster on his email addresses spellcasterforlove@outlook.com

ADVERTISEMENT