ARTICLES

"Excel Basics to Blackbelt": A Reference Text for MapPoint Integration

Dr. Elliot Bendoly shares information about his new textbook which includes information about integrating Excel and MapPoint.

On a daily basis, professionals are expected to make decisions in fundamentally complex environments. "Should I recommend that my client invest in a particular developing region?" "Should I recommend an offshoring/outsourcing scenario given my expectations of how other parties are likely to act in the near term?" "Should I offer to take on additional work now that one of my current projects appears to be nearing completion?"

These aren’t simple questions, and we can’t expect individuals faced with such questions to always have immediate and appropriate answers available to them. What we "can" expect is some level of thought, and some level of a desire for assistance when good solutions are particularly needed. Where this assistance comes from can vary, but, being the eternal tool-builders that we are, we tend to find such assistance in the form of data representation, analysis and interpretation mechanisms built expressly for these purposes.

Increasingly these are high-tech information-intensive mechanisms... ...BUT at the same time these increasingly tend to be tools that are well within our own grasp to develop and master. And by ‘our grasp’, I’m not talking about ‘the grasp of computer programmers’. I’m referring to the typical manager in any business, small or large, in any industry around the world - As long as such managers are willing to embrace and leverage readily available applications (eg. MapPoint).

Over the last several years I’ve taught an extremely successful elective on decision support tool development at Emory’s top-ranked Goizueta Business School (ranked in the top 10th nationally of all business schools in a large number of degree granting programs we offer). My course is designed as a fast-ramp, that takes individuals with potentially zero-skill in the use of Excel and develops their knowledge in application development to the point at which they can develop user friendly decision support tools for their own or corporate use at a level of sophistication that they themselves would not have imagined. It’s been a cornerstone course for placement and career advancement for many of my students.

This year I’m happy to offer some of the knowledge from that course to a broader audience in the form of a desk reference text titled "Excel Basics to Blackbelt", published by Cambridge press. With endorsements from people like internationally renowned author Tom Davenport, the book focuses on Excel 2007/03 as a foundation for tool development. It starts with basic navigation and data access (eg. web-queries and MapPoint) and evolves through basic function use, data cleaning and statistics (XLStat), problem structuring and optimization (Solver and RISK Optimizer).

It then progresses into more advanced skills such as systems simulation, simulation optimization and the use of Visual Basic programming (Macros and User-defined functions). The use of controls and development of dialogue boxes, as well as the automation of external applications directly through Excel 2007/03 (eg. Solver, MapPoint, XLStat, RISK Optimizer) is left to the final chapters for those particularly intrepid developers. Along with providing sample code and spreadsheet designs, the reference provides a glossary of technical definitions and a consolidated short-cut guide for key-board controlled use.

For MapPoint users in particular, the text provides some novel insights regarding data integration with Excel. Demonstrations of the import, export (mass and selective, as per area selection tools) and direct continuous linkage of data between MapPoint and Excel is discussed in the early chapters. Also discussed are capabilities for embedding and saving functional MapPoint objects within Excel workbooks. The use of MapPoint route distance and built-in route-optimization mechanisms is discussed (along with alternative route optimization / heuristic approximation approaches that leverage MapPoint maps). Many of my past students have found the Visual Basic application calls to MapPoint, for which sample code is provided, particularly useful in automating MapPoint-integrated applications with Excel.

If you would like to learn anything else about the text or applications of the techniques discussed, please feel free to contact me directly (elliot_bendoly@bus.emory.edu). Cambridge is currently offering a pre-publication discount of 20% for any individuals interested in purchasing the text prior to June 30th (info at www.excel-blackbelt.com). Bulk discounts may also be available through direct contact with the publisher.

As always, best of wishes in your work.

Before starting his doctoral program, Elliot worked as a research and design engineer and group leader for Intel's Polymer Core Competency in Chandler, Arizona. In this role he was predominantly involved with failure analysis for micro-chip processor package assembly and in the design of facilities and procedures for such tests. While in Cleveland, he served as a survey analyst for the International Motor Vehicle Program at the Weatherhead School of Management's Center for Regional Economic Issues.

His research has been published in a number of academic journals including the Journal of Operations Management, Production and Operations Management, MIS Quarterly, the Journal of Applied Psychology, EJOR, IJOPM, Decision Support Systems and Information & Management. He has co-edited a text titled “Strategic ERP Extension and Use” and is an Associate Editor for both the Journal of Operations Management and the Decision Sciences Journal. Professional affiliations include officer roles in both the Academy of Management’s OM division as well as the Behavioral Process Management section of INFORMS (an arm of the Behavior Dynamics in Operations Management network, which he co-founded).

	MP2Kmag Internet