There is a clear truism in George Box's 1979 statement that “all models are wrong, some models are useful.” We attempt to model reality to see how changes can affect it – hopefully, for the better. But models of reality are, by their very nature, incomplete depictions, which tend to be misleading. Still worse are models and associated solutions that faithfully attempt to do justice to reality by incorporating many facets of reality into their structures. Unfortunately, a common result is an overemphasis on certain issues in decision making that, although interesting, are far less relevant to effective decisions than other issues that have been overlooked.

Ultimately, any approach to decision making is a balancing act between an appropriate accounting of relevant reality (that is, the objectives, decision variables, and constraints discussed in Chapter 4) and avoiding details that only obscure or mislead. When we attempt to rationalize all of the factors that might go into a decision-making process, as well as possible solutions that might be practically viable, we often “satisfice,” a term used to describe making a decision about a set of alternatives that respects the limitations of human time and knowledge.

As an extension to the discussion in Chapter 6, it's relevant at this point to reconsider how a feature such as Solver can come up with a solution. Although it's not necessarily critical for developers to understand the detailed technicalities of these packaged programs, any developer worth his or her salt should understand at least the limitations of these algorithms.

How Solver “Solves”

Many people use Solver with the expectation that it can find the optimal solution for any kind of problem (of reasonable size). But even small problems can have their nuances that make the job extremely difficult for the standard Solver add-in, and the resulting solutions are prone to poor performance (substantially less-than-optimal managerial recommendations). Engines like Solver commonly use hill-climbing algorithms to search for optimal solutions. In reality, this is just another heuristic (see Chapter 5). It starts with a guess of what the solution might be and then it sees if small changes to any of the decision variables of that solution can result in better value for the objective function that is subject to constraints.

Hill-climbing algorithms typically look into only one solution at a time. For example, consider the following hypothetical performance surface (where performance along the z-axis is some function of the two decision variables x and y). In Figure 7.1, a shaded dot represents a possible solution, one that, at this point, appears to be less than ideal. From a local perspective, it certainly doesn’t represent the apparent peak value of z attainable (shown by an ellipse).

The world changes all the time. Technological resources advance at such a clip it seems hard to keep up. But not all technological developments really need shadowing. What we really need to keep up with are developments that help us do the great things we've been doing all along…only more effectively.

Such is the case with spreadsheet applications. New interfaces and bells and whistles appear in each new version. Some are wonderful; however, not all are particularly useful. And sadly, some of the best features often get left behind (I always give as an example the losses in macro writing capabilities and graphical direct-editing we lost after MS Excel 2007 replaced Excel 2003). But that's history. We need to keep looking forward, and where new capabilities replace older ones, we are not necessarily left in the lurch. After all, MS Excel and VBA provide the remarkable ability to be expanded and customized to our particular needs, even in ways that can replicate some of what we loved about previous versions, and even in ways we might expect future versions to automate (but which are not yet in place).

With that in mind, the second edition of Excel Basics to Blackbelt capitalizes on many of the recent developments made available by more contemporary versions of Excel (while retaining a nod to the features of the past). Furthermore, this edition looks forward to capabilities that are not currently standard through the introduction of a series of newly developed Excel addins. The application and description of these are peppered throughout the second-edition text with an aim to augment the text and fill in the capability gaps that were present in the original edition.

Aside from typing information into Excel, there are a number of other ways to get new data into spreadsheets. These methods include opening structured, plain-text files in ways that are meaningful to Excel (for example, rawdata.txt); using other desktop applications as data sources (such as tables in MS Word and tabular results from SPSS); drawing information from structured or nonstructured online sources (such as content from COMPUSTAT or even the whitepages.com); and developing systems that create or simulate large volumes of data with desired characteristics (mainly for use in illustrating or testing the robustness of proposed management policies). In this chapter, we'll touch on each of these methods at some level.

Text File Imports and Basic Table Transfers

If you have a text file that contains information, such as a survey or database data in text-file format, it can be opened into Excel as a new file. You simply need to specify how data in that file are organized, such as separated by spaces, tabs, commas, and so on. As an example, imagine a text file titled Chp3_MultRespsFinal.txt. Each record in this file occupies a new row, and the information relating to each record is organized sequentially with each field separated by a comma. This kind of data organization is referred to as “comma delimited.” Select Home>Open in Excel and then find and select this text document. The Step 1 of the Text Import Wizard opens, as shown in Figure 3.1.

The visualization of analytical dynamics comes naturally to the tools developed in Excel. This is largely due to the dynamic nature of graphs constructed in Excel. For example, if we wanted to depict the range of possible outcomes associated with specific decisions for which outcomes had a describable level of uncertainty or variation, it would be simple enough to introduce a random term into tabular forms and then graph the result. As always, pressing the F9 key would simply draw another random number from the built-in generator and augment the associated data tables and plots to represent the volatility of those outcomes.

For example, based on the Data Table generated in the Lobo's Reservations case, we could graphically depict the variable nature of our results using the high-low-close plot (although tricked out a bit) provided in that workbook. Every time F9 is pressed, we would see how much the variability in outcomes across policy types was subject to change (simply based on different separate and independent sets of random data draws). The result, shown in Figure 10.1, would depict an alternative array of outcomes that could be associated with a set of decisions. Another example is shown with the second Data Table example, shown in Figure 10.2.

Many applications such as Microsoft MapPoint and RISKOptimizer can be leveraged through the primary interfaces with which they were designed, but they can also be called from behind the scenes through the same Visual Basic (VB) developer environment discussed in Chapter 11. From a decision support development perspective, there are several advantages to making such calls from behind the scenes. First and foremost, behind the scenes control can eliminate the need for users to become acquainted with alternative interfaces in the course of using a DSS that leverages their capabilities. Another advantage is the potential avoidance of outputs that automatically accompany the use of these applications, but are nevertheless visual and information distractions from the main point of the DSS design. The appearance of seamlessness in a designed DSS is also facilitated by VB-driven automated calls to applications. This has the potential for engendering greater confidence in the developed DSS, as well as in the developers. This chapter covers several approaches to working with such applications in roughly the order in which they have been introduced throughout this book.

Calls to MapPoint

The Chp12_MapPointCall workbook provides a template through which we can demonstrate how Excel, through VB, can leverage some of the functionality of MapPoint. As with all other demonstrations in this chapter, we'll present only a smattering of what can actually be done. To start, let's consider a hypothetical need to get information regarding a route that starts in Seattle and passes through four additional cities before returning to Seattle. The workbook outlines these stops (shown in Figure 12.1).

People make decisions every several minutes. Some of these decisions may appear trivial, such as what shirt to wear or what to have for lunch. Some decisions may appear routine: Should I provide my PIN number? Should I respond to a question from a colleague? Others are more complex: Should I recommend that my client invest in a particular firm? Should I offer to take on additional work? Should I purchase a new technology? Should I recommend a settlement in a lawsuit? These aren't simple questions and they don't have obvious answers and outcomes. Sometimes, we need help with decisions. The sources of help can vary, but increasingly these sources tend to have two things in common: analytical strength and ease of use. These sources of assistance often take the form of prepackaged off-the-shelf software tools. However, they can also be uniquely customized, and more and more frequently, this customization is being developed by individual users.

We can describe these tools by listing their potential benefits. Some of them appear in Figure 1.1. For many developers and analysts, only a few of those attributes are listed important. For others, the full complement of potential benefits must be considered. Toward this goal, visualization is very important. Indeed, one could argue that the application of visualization in data analysis is critical to the development of Decision Support Systems (DSS).

Before attempting to construct a home, architects and builders need to know what resources they have at their disposal and how they can access these resources. The same holds in decision support development, but as you'll learn in subsequent chapters, the tools available to Excel developers are more numerous than a typical user would imagine. In this chapter, we'll start with the low-hanging fruit by becoming acquainted with the general nature of Excel's front-end development environment. Figure 2.1 provides an annotated view of what people typically see when they open a new file in Excel. Only a few key elements of this interface are central to our initial discussion.

Excel files are called “workbooks.” They contain any number of worksheets (spreadsheets) that can be used in tandem. For the vast majority of its use in practice Excel is relegated to storing information. This is largely because most users don't know what else Excel can do. Not that storing information in Excel is bad, but there are often better alternatives available for storage, such as databases, in the case of very large sets of data. Functionally, the storage capability of Excel represents only the bare tip of this technological iceberg.

Many effective decision support systems rely not only on the ability of a manager to present information, analysis, and meaningful dynamics (for example, through graphics), but also on enabling users to realize the intended use of those elements by themselves (without the developer holding their hand). This is often going to mean providing sufficient documentation that might go beyond cell labeling and embedded comments. It may mean coming up with some kind of a customized user-driven help or wizard component as part of the DSS that makes use of not only automated numerical and graphical demos, but also other objects, such as images and .wav files, which could be incorporated into the workbook. This is often going to mean a level of automation that stretches the limits of the kind of work that can happen at the spreadsheet interface alone. In fact, it may be impossible to achieve by using only the top layer of an Excel workbook. Let's see how macros and the Visual Basic (VB) Editor might provide us with some new options in this regard.

The Visual Basic Editor

Let's take a deeper look into one of the first macros I introduced. Opening the Chp8_LobosInventory workbook provides us with an opportunity. To see the code associated with this macro, select the Developer tab on the main menu bar and then select Visual Basic (which will open the general VB Editor screen) or click Macros (see Figure 11.1), and from the associated dialog box select the specific name of the program code you are interested in viewing (in this case, generically called Macro1) and then Edit.

Decision modeling describes the use of data and logic to clarify the specific nature of a situation for which assistance in the decision-making process may be needed. The hope is that in clarifying such details, the development of meaningful suggestions and solutions may be easier to create. Most management problems for which decisions are sought can be represented by three standard elements: objectives, decision variables, and cons-traints.

1. Objectives

2. Maximize profit

3. Provide earliest entry into market

4. Minimize employee discomfort and turnover

5. Decision variables

6. Determine what price to use

7. Determine the length of time tests should be run on a new product or service

8. Determine the responsibilities to assign to each worker

9. Constraints

10. Can't charge below cost

11. Must test enough to meet minimum safety regulations

12. Ensure responsibilities are shared by two workers at most

All of these elements can be visualized graphically, often to the benefit of analysis and general insights. Our initial discussion will be limited to objectives and decision variables; we'll discuss constraints further on in this chapter. In most business scenarios, managers are faced with making a set of decisions that impact a final outcome (objective). This tends to make the decision process more complex, and sometimes the rationale for making specific decisions is difficult to describe.

As you've probably guessed by now, decisions can become increasingly complex as we increase the number of variables and constraints to maintain reality and practicality in our decision-making process. Similarly, the ability to concisely provide visualizations of what is possible and what is ideal (and, conversely, what isn't) becomes increasingly challenging. Given this complexity and the perceived need in industry to nevertheless pursue means of assisting people in decision making, the concept of the dashboard has come into being and continues to gain popularity.

A dashboard, from a general decision-making perspective, is basically a computer interface that allows individual users to simultaneously view various depictions (that is, presented structures) of data and information, as well as various subsets of data (that is, content) relevant to a particular task and user context. For example, Figure 13.1 shows four dashboards that I’ve personally put into use for research and consulting purposes in the recent past.

Two of these are highly oriented toward geographic (specifically, logistics) tasks; the other two are designed with project management tasks in mind. You’ll notice that each of these consists of multiple frames and multiple control- and form-based interfaces. Some make use of parameterization forms more so than others. Some make use of graphs and charts predominantly, whereas others make rich use of tables with key indices summarized. All of them were designed as applications that could function through the use of Excel alone, and are highly mobile from a distributional perspective.