Decision Support Capabilities in Excel

Applications of Management Science

ISBN: 978-1-78756-652-1, eISBN: 978-1-78756-651-4

ISSN: 0276-8976

Publication date: 20 August 2018

Abstract

During the past several decades, the decision-making process and the decision-makers’ role in it have changed dramatically. Because of this, the use of analytical tools, such as Excel, have become an essential component of most organizations. The analytical tools in Excel can provide today’s decision-maker with a competitive advantage. We will illustrate several powerful Excel tools that facilitate the decision support process.

Keywords

Citation

Klimberg, R. and Ratick, S. (2018), "Decision Support Capabilities in Excel", Applications of Management Science (Applications of Management Science, Vol. 19), Emerald Publishing Limited, pp. 155-182. https://doi.org/10.1108/S0276-897620180000019008

Download as .RIS

Publisher

:

Emerald Publishing Limited

Copyright © 2018 Emerald Publishing Limited


Introduction

Over the past several decades, organizations and decision-makers have been embracing the business intelligence/business analytics (BI/BA) revolution. The degree to which analytics are used, and the complexity of uses, varies among organizations from just beginning to very mature analytics competitors. Regardless of where in this scope an organization may be, a remarkable number of opportunities now exist to use analytical tools, and to create and use analytical models. The development of these new analytics tools has enabled the tools and models to be utilized by those who can best benefit from their use. The application of these tools will likely lead to better decisions and thus provide today’s decision-maker with a competitive advantage.

Nearly all decision-makers and organizations have Microsoft Excel. Many of the analytical decision support capabilities within Excel are either rather unknown or underutilized. One of the objectives of this chapter is to demonstrate a few of these powerful Excel analytical capabilities. First, in the next section, we will provide a brief historical perspective of the evolution of analytics. Subsequently, we will illustrate the application of a few of these Excel analytical decision support tools to analytics problems faced by organizations in the public and private sector.

Historical Perspective

The development and application of analytical tools and models using computers has followed the growth of the computer. Starting with the first computers in the late 1940s and 1950s, analytical applications centered mainly on military problems. Since the 1950s through the first half of the 1970s, analytical modeling expanded to other fields of study, such as manufacturing, health care, transportation, finance, forest management, energy analysis, and water resources. Numerous companies and public sector entities established internal analytics staffs. At that time, decision-makers were, in general, disengaged from the decision process, basically just requesting to know what the solution was and how it would improve or solve the situation. They did not care how the solution was obtained. As a result, many analytics projects developed prior to 1980 were not fully implemented (Watson & Marett, 1979). A major reason for this lack of implementation was generally the inability of analysts to communicate analytical model results. Computers were only used by highly technically trained people, who understood the software and analytical methodologies, yet, did not understand as well the nuances of the business or public section problems to which these techniques were being applied. The analytical techniques produce a solution based on mathematics, but it was unlikely that the decision-maker understood the way the solution was obtained, exactly what the solution was, or how it might best be utilized to address the problems for which it was obtained. As early as 1961, Wynne suggested that the challenge analytics specialists face “consists of stripping the research argument of its mathematical complexities and irrelevancies while retaining the rigorous logic for the executive’s understanding” (Wynne, 1961). Further as F. Bradshaw noted: “Most managers would rather live with a problem they can’t solve than use a solution they don’t understand” (Klimberg & McCullough, 2013).

Today’s decision-making process has radically changed since the 1970s due to the explosion of Big Data. Big Data is the confluence of the 3Vs: data volume, data velocity, and data variety (from structured database data to unstructured social media data). The computer technology improvements of Big Data have not only increased data storage and made more data easily accessible, but it has also enabled BI/BA tools to be readily available in common platforms like Excel. Analytical tools and model developments that used to take months or years to accomplish can now be done in days or weeks, if not almost instantaneously. Individuals who now “understand and talk the business” are developing more and more of these analytics tools and models. The confluence of complex models being implemented on common platforms, the increasing common understanding of analysts and decision-makers, and the fact that today nearly everyone has a computer on their desk have made it possible for increased decision-maker participation in formulating the way in which analytical tools and models are being employed in the decision-making process.

The successful implementation of any analytics model is more likely to occur if the techniques used to create it, and the results it provides, are concomitant with the degree of knowledge and understanding of the potential decision-maker(s). Unless the analytics model can be understood qualitatively, it will not be used. Integral to achieving understanding is the development of effective decision support mechanisms. The development and use of decision support systems (DSSs) has been one of the chief events of this BI/BA revolution that has dramatically changed the information systems/technology field. DSSs have facilitated decision-maker participation in the decision-making process. Decision-makers are now addressing more complex problems, that is, problems that are more ill-structured, more fuzzy, and that include qualitative factors. No longer is the decision-maker willing to accept only the traditional analytics philosophy of finding one optimal solution. Decision-makers want to examine the problem under a variety of conditions and assumptions, and to consider several evaluation criteria.

Nevertheless, understanding is not just some “nice,” colorful GUI table, chart, or graph. Understanding is the evolution from the comprehension of the problem, the inputs, and the outputs, to putting this comprehension into the context of the decision-making situation. Comprehension is achieved by effective decision support mechanisms, such as tables, graphs, charts, and narratives. An effective decision support mechanism presents these factors in a meaningful way to the decision-maker. The progression of the model and its inputs and outputs into the context of the decision-making situation requires the decision-maker to “interact” with the model and its inputs and outputs. Thearling (1997) proclaimed interaction as “the Holy Grail of visualization in data mining.” Interaction consists of asking “what-if” questions, performing sensitivity analysis, manipulating, and viewing the inputs and outputs. Numerous specialized decision support mechanisms and generalized decision support mechanisms, such as EXCEL, are readily available to generate meaningful tables, graphs, charts, and text.

BI/BA software packages come with price tags, some rather hefty price tags, causing many organizations to be initially somewhat reluctant to make such a huge investment. By far, the most widely used tool used by decision-makers is the spreadsheet. Spreadsheet software, and in recent years with the dominance of Microsoft, Excel in particular, has radically changed the function and analytical capabilities of a decision-maker. For example, the accountant, the financial analyst, the business manager in general, previously spent a significant amount of their time inputting or recording the data and little time doing what they were actually trained and educated to do—analyzing the data. Now with spreadsheets, decision-makers can spend more time exploring the impact of different scenarios, asking “what-if” questions and addressing more complex situations.

In the section, we will examine the capacity of some of the powerful quantitative tools readily available in Excel to address analytical problems faced in the public and private sector. Detail directions to re-create some of the examples implemented using Excel tools are provided in the Appendix.

Excel Analytical Tools

Scroll Bar and One-way and Two-way Data Table

The scroll bar and one- and two-way data tables are very powerful Excel analytical tools that can be extremely useful in performing “What-If” analysis. To facilitate illustrating these Excel tools, we will initially use a breakeven analysis example faced by businesses:

The fixed costs for a product are US$3,000 and the variable costs are US$2/unit. In addition, the selling price is US$5/unit. Find out how many units must be sold such that the company breakeven, that is, the point where total costs equal total revenue.

The objective of this breakeven analysis example is to find when the profit is equal to 0 by varying the units sold. The scroll bar allows the decision-maker to vary incrementally the value in a cell in Excel. This cell is linked to other cells with formulas or even linked to values in a graph. The scroll bar permits the decision-maker to observe not only the breakeven point but also the sensitivity of varying the value of the number of units sold and its impact on the amount of profit.

In cells A1:C16, we put in the parameter values and the formulas as shown in Fig. 1. The right side of Fig. 1 shows the formulas for each of these cells.

Fig. 1. 
Breakeven Analysis Example (On the Left Are the Known Parameter Values and on the Right Are the Respective Formulas).

Fig. 1.

Breakeven Analysis Example (On the Left Are the Known Parameter Values and on the Right Are the Respective Formulas).

As we move the scroll bar, these values will change in Fig. 2:

  • The number of Units Sold, cell B4, will change.

  • Total Revenue, cell B5, Total Costs, cell B10, and Profit, cell B13, will change.

  • The values in cells B15:C16 are used to produce the top bar graph. Since B16 and C16 are linked to Profit and Units sold, these values will change and the bars on the graph will change.

  • The values in cells A22:C26 are used to produce the bottom graph. The vertical line will move left or right accordingly.

Fig. 2. 
Scroll Bar and Graphs for Breakeven Analysis Example.

Fig. 2.

Scroll Bar and Graphs for Breakeven Analysis Example.

Using the scroll bar, the decision-maker can dynamically see the sensitivity on profit by changing the number of units sold. The number of units sold at the breakeven point is 1,000.

The one-way and two-way data tables , like the scroll bar, allow you to change (or link to) one or two cell(s). Instead of seeing the changes in the corresponding formulas, the one-way/two-way data table generates a table with the changes. Therefore, the advantage of the one-way/two-way data table over the scroll bar is that the changes in the results due to varying the value of one cell/two cells can all be seen at one time.

Fig. 3 shows a one-way data table for our breakeven example as the profit is shown for various number of units sold values.

Fig. 3. 
One-way Data Table.

Fig. 3.

One-way Data Table.

Let’s look at another example utilizing the scroll bar, and the two-way data table, to examine the sensitivity of the results from an analytics technique. The example problem we will examine is:

Given a dataset with the values of several predictor variables of housing prices (square feet, # of bedrooms, # of bathrooms, and # of offers) of 128 houses, statistical regression is used to show the relationship of these predictor variables on the resulting price of the house.

Fig. 4 shows the regression output from the house price data set. The regression model is:

House price = 17,347 + 62 * SqFt + 9,320 * Bedrooms + 12,646 * Bathrooms 13,601 * Offers .
Beyond the regression, the analysis can examine the sensitivity of house prices as the predictor variable values change. The coefficients of the predictor variables show estimate change in house prices that accrue to a one unit increase in the predictor variable, holding all other predictor variable values constant. For example, a unit increase in the number of square feet will increase the house price by US$62. As the number of bedrooms and bathrooms increases, the price of the house increases; as the number of offers increases, the house price decreases by US$13,601 per offer.

Fig. 4. 
Excel Data Analysis Regression Output for House Price Data.

Fig. 4.

Excel Data Analysis Regression Output for House Price Data.

We can use the scroll bar and two-way data table to examine the interrelation of simultaneously changing the number of square feet, number of bedrooms, and number of bathrooms on the predicted price of a house, as shown in Fig. 5. The scroll bar is linked to cell O9, which is the number of square feet. The two-way data table in cells K15:Q19 shows the expected house price for different numbers of bedrooms and bathrooms (while keeping the number of offers constant) (in Fig. 5, offers = 2). Moving the scroll bar will change the number of square feet, cell O9. As a result, the values in the two-way data table will change, and the bar chart, which is linked to the values in the two-way data table, will change.

Fig. 5. 
Scroll Bar, Two-way Data Table and Graph for House Price Data.

Fig. 5.

Scroll Bar, Two-way Data Table and Graph for House Price Data.

Simulation

An analytical approach to exploring the effects of the uncertainty in the actual values of parameters used in a model on that models outcomes is to employ Monte-Carlo simulation (Miori, Klimberg, & Ratick, 2015b; Ratick & Schwarz, 2009). Instead of using just parameter averages, the degree of risk and sensitivity of these outcome values can be explored with a simulation model.

To illustrate a basic simulation model in Excel, we extend our breakeven analysis example. We will assume the amount of units sold varies from period to period and follows a normal distribution with a mean of 1,200 and standard deviation of 200. In this case, our goal is no longer to find a single breakeven point. Now, we are interested in the expected amount of profit per period and the likelihood that we would have a loss.

The simulation is run for 1,000 periods—1,000 potential realizations of profits which would be shown in cells A20 through E1019—Fig. 6 shows the results of the first 21 realizations using the simulation model. Each period provides a possible estimated profit result, for example, using the parameters randomly drawn from the normal distribution, a US$9.00 loss would accrue in period 1 (cell E20) and a US$465 profit in period 2 (cell E21). Each row/period is re-created 1,000 times (cells A20:E1019 in Fig. 6). The average profit for the 1,000 periods is US$ 644.49, cell G11, and the probability of a loss is 14.4%, cell G19. The graph in Fig. 6 is a histogram of the profits from the 1,000 periods. These outputs provide the decision-maker with a sense of what values the actual profit may be, and the possible risks associated with the situation.

Fig. 6. 
Breakeven Analysis Example’s Simulation Results.

Fig. 6.

Breakeven Analysis Example’s Simulation Results.

Another more practical example of utilizing simulation is to estimate the predicted cost of a data breach for an organization. Eighteen key factors were identified as affecting the costs of an organization’s potential data breach are shown in Fig. 7. If the decision-maker has no knowledge of a factor’s probability distribution, the decision-maker might be asked to estimate the minimum, most likely, and maximum values that parameter may obtain, which would define a triangular distribution. A simulation for each of these factors is run for 1,000 periods. The simulation results are shown in Fig. 8. Estimates of summary statistics estimates are provided, in addition, to cost estimates by factor. The decision-maker can run several experiments varying one or more factors and observe the sensitivity of the resulting cost estimates to changes in the factors.

Fig. 7. 
Survey Questions for Data Breach Simulation.

Fig. 7.

Survey Questions for Data Breach Simulation.

Fig. 8. 
Data Breach Simulation Output.

Fig. 8.

Data Breach Simulation Output.

Geographical Information Systems and Spatial Optimization: Multi-objective Land Allocation

A critical problem that local, state, and national organizations often have to address is to assure that the land under their control is put to its best use. For example, local governments may wish to zone land within their jurisdiction to enable an economically viable and environmentally sustainable future; national and international environmental organizations may want to purchase land for conservation purposes. The methods used to accomplish these goals often involve implementing Geographical Information Systems (GIS) (Clark Labs, 2017)—to assess and map current and potential land uses—together with spatial optimization to allocate land parcels to their best use (Dai & Ratick, 2014; Klimberg & Ratick, 2008; Tong & Murray, 2012). While sophisticated, and often expensive, tools exist to accomplish these tasks, we can utilize the functions in Excel to address these types of decisions within organizations. To demonstrate this, we first apply the multi-objective land allocation (MOLA) method (GITA, 2017), a mathematical programming method used with a GIS, to zone land parcels in a community into three types of land uses: Industrial, Residential, and Light Agriculture.

The MOLA modeling process involves the following tasks:

  1. Identify the important factors that affect the suitability of each land parcel for each particular land use. In our example, we will used the important factors for assessing the suitability of parcels for each land use type: Soil Type, Distance from Town Center, and Land Value.

  2. Create “Factor” maps that have the measure of each of the factors for each of the land parcels. Factor maps are usually created with GIS tools, but can also be done in Excel by using the Conditional Formatting function for each of the factor values in the cells of the Excel “map” (in a raster-based GIS, cells are called “pixels”).

    Three Factor maps are created in our example: (1) Soil Type, (2) Distance to Town Center, and (3) Land Value. Because each of the factors is measured in different units and scales, we standardized the factor values for each of the maps to values between 0 and 100, 0 representing the worst suitability value for that factor and 100 the best. The resulting factor maps are shown in Figs. 9–11.

  3. Determine the importance of each of the factors for each of the land uses utilizing an importance weighting technique. We used a direct weighting method for our example, but a number of other multi-attribute weighting methods that can readily be implemented in Excel are available, including the multi-attribute utility methods, the simple multi-attribute rating technique (SMART) and the analytical hierarchy process (AHP) (Miori et al., 2015a). We used the following importance weights for each of the factors in our example (Table 1).

  4. Multiply the factor values in each parcel by their importance weight, and combine the weighted factor maps into a suitability map for each parcel (Excel cells in our maps). Figs. 12 through 14 show the suitability maps for each land use type. We will be allocating 20 of these land parcels to one of the three different land uses; those 20 parcels are contained within the white rectangle on the maps (cells in the block from (6,8) through (10,11)).

  5. Using Solver in Excel allocate the 20 land parcels to each of the land use categories. For our example, we will allocate six parcels to Industrial land use, six to Residential, and eight to Agriculture using the following integer programming model:

(1) Maximize Z 1 = i j wlu j S ij X ij
subject to
(2) i X ij = D j j
(3) j X ij £ 1 i
where:
  • Xij  = 1 if land parcel i is allocated to land use type j;

  • 0 otherwise.

  • Sij  = the “suitability” of land parcel i for land use type j (the values for each parcel in the suitability maps Figs. 12–14).

  • Dj  = the total amount of land (here in parcels) to be allocated to land use type j.

  • wluj  = the relative importance weight of each land use type j.

Fig. 9. 
Factor Map for Soil Type.

Fig. 9.

Factor Map for Soil Type.

Fig. 10. 
Factor Map for Distance to Town Center.

Fig. 10.

Factor Map for Distance to Town Center.

Fig. 11. 
Factor Map for Land Values.

Fig. 11.

Factor Map for Land Values.

Table 1.

Importance Weights for Each Factor.

Factor Importance Weights
Industrial Residential Agriculture
Soil type 0.1 0.2 0.5
Distance to town center 0.5 0.4 0.1
Land value 0.4 0.4 0.4
Fig. 12. 
Suitability Values by Parcel for the Industrial Land Use.

Fig. 12.

Suitability Values by Parcel for the Industrial Land Use.

Fig. 13. 
Suitability Values by Parcel for the Residential Land Use.

Fig. 13.

Suitability Values by Parcel for the Residential Land Use.

Fig. 14. 
Suitability Values by Parcel for the Agricultural Land Use.

Fig. 14.

Suitability Values by Parcel for the Agricultural Land Use.

The objective function Z 1 (1) maximizes the weighted sum of suitability of each parcel for each land use. The constraints in (2) assure that we meet the zoning requirement (Dj ) for the different land use types. The sum of all parcels assigned to land use j has to be equal to the amount of land we exogenously want to have allocated to that land use; six parcels to Industrial, six parcels to Residential, and eight to Agriculture. Note: the sum is over all parcels (i) for a particular land use type (j). There is one constraint for each land use type, three for our example. The constraints in (3) assure that a parcel is assigned to at most one land use type, the sum over all land use types for a parcel must be less than or equal to 1. There is one constraint for each parcel, 20 in our example. Fig. 15 graphically shows the optimal solution to this land allocation problem when each land use is equally important (the wlujs are all equal, set to 1), obtained using Solver in Excel (for a tutorial on how Solver can be used to solve linear and integer programming problems (see Miori et al., 2015c). (The numbers bordering the map show the cell locations for the different land parcels within the white rectangle on each of the land use suitability maps.)

Fig. 15. 
Allocation of Parcels to Each Land Use Type to Maximize Total Suitability: All Land Uses Are Equally Important.

Fig. 15.

Allocation of Parcels to Each Land Use Type to Maximize Total Suitability: All Land Uses Are Equally Important.

The six industrial parcels run north to south at the easternmost part of the area to be zoned, with one parcel one unit to the west. The agriculture pattern runs somewhat diagonally from north to south. The residential pattern runs partially from north to south along the western boarder of the area, with one parcel further west near the center. The optimal total suitability value for this zoning pattern of land uses is 1,552. 1

As noted in the previous sections, using Excel to perform sensitivity analyses can provide very useful information to organizational decision-makers. Varying the land use importance weights in MOLA (the wluj) can be used for this purpose. As an example, the changes in the land use patterns and suitability scores when the importance weight for the industrial land use is increased from 1 to 50 are shown in Fig. 16 and Table 2. An importance weight of 50 for the industrial land use yields the maximum suitability score for the industrial land use pattern. When the importance weight is 2, industrial land use is allocated now mostly to the eastern part of the zoned area, swapping with residential; as the weight is increased to 10, one additional residential parcel is allocated to industrial; and at an importance weight of 50, one agricultural parcel is swapped with industrial. The largest loss of total suitability occurs when the weight on the industrial land use is raised to 2, the next largest when raised from 10 to 50. Overall, the suitability score for the industrial land use increases from 423 to 482, while the suitability scores for the other two land uses decrease.

Fig. 16. 
Changes in the Land Use Patterns When the Weight on Industrial Is Increased

Fig. 16.

Changes in the Land Use Patterns When the Weight on Industrial Is Increased

Table 2.

Suitability Scores by Land Use for Changing Weight on Industrial Land Use.

Weight for Industrial Suitability Scores by Land Use
Industrial Residential Agriculture Total
1 427.67 519.57 605.11 1,552.35
2 480.73 450.73 605.11 1,536.57
10 481.23 448.17 605.11 1,534.51
50 481.60 454.55 585.66 1,521.81

Another potentially useful sensitivity analysis would be to assess changes in the spatial patterns of the zoned land uses. While most of the allocations in the previous solutions, with a few exceptions, are contiguous (the zoned patterns show similar land uses are often neighbors), a more compact zoning pattern may be better for administrative and economic purposes. Testing the trade-off of optimal suitability patterns with maximizing compactness can be accomplished in Excel by adding the following objective function (4), and additional nonlinear constraint (5).

(4) Maximize Z 2 = i k j C i , k , j
(5) C i , k , j = N i , k , j X i , j X k , j
where:
  • Nikj  = 1 if parcel (i) and parcel (k) are neighboring parcels (they share a boundary), 0 otherwise.

  • Cikj will be 1 if parcel (i) and parcel (k) are neighbors, and both are allocated to the same land use (j).

To obtain the sensitivity results to this nonlinear multi-objective programming problem, a single objective function is created, Z that is the weighted sum of the suitability and compactness objectives: Z = w 1 Z 1 + w 2 Z 2. Changing the relative weights between total suitability (Z 1) and total compactness (Z 2) yields the trade-off curve shown in Fig. 17, the solid line shows the suitability─compactness trade-off when the importance weight on industrial is 1, the dotted line shows it for an industrial land use importance weight of 10. Figs. 18 and 19 show the land use patterns that result from each of the multi-objective solutions in both cases. Small decreases in suitability will yield relatively compact patterns, but a larger decrease is needed to yield the most compact patterns. Detailed information on the relative changes in suitability, by land use types for both trade-off curves, can also be easily calculated in Excel.

Fig. 17. 
Trade-off Curve for Total Suitability and Total Compactness.

Fig. 17.

Trade-off Curve for Total Suitability and Total Compactness.

Fig. 18. 
Resulting Land Use Zoning Patterns as the Relative Weight on Compactness Is Increased. All Land Uses Assigned Equal Importance Weight.

Fig. 18.

Resulting Land Use Zoning Patterns as the Relative Weight on Compactness Is Increased. All Land Uses Assigned Equal Importance Weight.

Fig. 19. 
Resulting Land Use Zoning Patterns as the Relative Weight on Compactness Is Increased. Industrial Land Use Importance Weight = 5.

Fig. 19.

Resulting Land Use Zoning Patterns as the Relative Weight on Compactness Is Increased. Industrial Land Use Importance Weight = 5.

Conclusions

Computer technology has radically changed the decision-making process. With increasing computer storage and processing capabilities, decision-makers are now faced with the opportunities and challenges of utilizing large (gigabytes, terabytes) databases and the analytical capability to analyze this data. Analytics analyses that may have taken weeks, or perhaps even months, to perform just two decades ago can now be accomplished in days or hours. Generally, today’s decision-makers are not satisfied with “black box” solutions. They want to be involved, to ask “what-if” questions, and to have the answer now. Analytics tools are readily available and the opportunities are there to increase significantly the use and success of the application of BI/BA tools. To accomplish this, BI/BA practitioners need to develop viable decision support mechanisms, that is, the interaction and assistance interfaces, between the BI/BA tools and the decision-maker, such that these tools are understandable and useful. The application of these tools, in general, will lead to better decisions. As a result, their application can and will provide decision-makers with a competitive advantage

In general, organizations are in the midst of a significant culture change on the importance and reliance on data and analytics. Many organizations are reluctant, regardless of where they are analytically, to spend large sums of money on software and hire the right people to utilize the software and develop the models and tools. There is a large degree of distrust due to the lack of understanding of what these tools could do for an organization. Microsoft Excel is readily available and has the capability to be a useful base for an analytics DSS. Nevertheless, many of these Excel analytical decision support tools are not well known or utilized. In this chapter, we illustrated the potential application of several analytical Excel tools. There are numerous more Excel tools including using VBA to build models. As a result, because of Excel’s availability, costs, and analytical capabilities, the option of utilizing Excel should be explored by organizations. Excel is not the analytical solution to all situations, but because of the lack of awareness of Excel’s analytical tools, many opportunities are being missed.

Note

1

This is a relative value, as the suitability values in the factor maps were scaled from 1 to 100. If they were scaled 1–10 this value would have been 15.52.

References

Clark Labs (2017) Clark Labs . (2017). TerrSet Geospatial Monitoring and Modeling Software. Retrieved from https://clarklabs.org/terrset/

Dai & Ratick (2014) Dai, W. , & Ratick, S. (2014). A spatial decision support system for conservation reserve design: Integrating geographical information systems (GIS) and multi-objective land allocation optimization. Transactions in GIS, 18(6), 936949.

G.I.T.A. Geographic Information Technology Training Alliance (2017) GITA. (Geographic Information Technology Training Alliance) . (2017). MOLA. Retrieved from http://gitta.info/Suitabilityi/en/html/MOAnalysis_learningObject3.html

Klimberg & McCullough (2013) Klimberg, R. , & McCullough, B. D. (2013). Business analytics—Today’s green? Contemporary Perspectives in Data Mining (Vol. 1, pp. 47-60). Charlotte, NC: IAP.

Klimberg & Ratick (2008) Klimberg, R. , & Ratick, S. (2008). Modeling data envelopment analysis (DEA) efficient location/allocation decisions. Computers and Operations Research, 35(2), 457474.

Miori, Klimberg, & Ratick (2015a) Miori, G. , Klimberg, R. , & Ratick, S. (2015a). Decision analysis. In Business analytics for the public and private sector. Philadelphia, PA: Haub School of Business, St. Joseph’s University.

Miori, Klimberg, & Ratick (2015b) Miori, G. , Klimberg, R. , & Ratick, S. (2015b). Forecasting. In Business analytics for the public and private sector. Philadelphia, PA: Haub School of Business, St. Joseph’s University.

Miori, Klimberg, & Ratick (2015c) Miori, G. , Klimberg, R. , & Ratick, S. (2015c). Linear programming. In Business analytics for the public and private sector. Philadelphia, PA: Haub School of Business, St. Joseph’s University.

Ratick & Schwarz (2009) Ratick, S. , & Schwarz, G. (2009). Monte Carlo simulation. In R. Kitchin & N. Thrift (Eds.), International encyclopedia of human geography (Vol. 1, pp. 184211). Amsterdam, The Netherlands: Elsevier.

Thearling (1997) Thearling, K. (1997, December 9). Understanding data mining: It’s all in the interaction. DS. Retrieved from http://www.thearling.com/text/dsstar/interaction.htm

Tong & Murray (2012) Tong, D. , & Murray, A. T. (2012). Spatial optimization in geography. Annals of the Association of American Geographers, 102(6), 12901309. doi:10.1080/00045608.2012.685044

Watson & Marett (1979) Watson, H. J. , & Marett, P. G. (1979). A survey of management science implementation problems. Interfaces, 9(4), 124128.

Wynne (1961) Wynne, B. E. (1961). A pattern of reporting operations research to the business executive. Management Technology, 1(3), 1623.

Appendix

Scroll Bar

First, we must add the scroll bar to your menu by adding the Developer tab to your top Menu:

  1. Click on File from the top Menu.

  2. Click on Options.

  3. Click on Customize Ribbon.

  4. On the right Main tabs list, click on the Developer box, as shown in Fig. A1.

Fig. A1. 
Click on the Developer Box.

Fig. A1.

Click on the Developer Box.

To create a scroll bar for the breakeven analysis example in Fig. 2:

  1. Click the Developer Tab in the top Menu.

  2. Under Controls, click the down arrow under Insert.

  3. As shown in Fig. A2, under Form Controls, click on the scroll bar icon.

  4. The mouse will now have crosshairs. Move the mouse to where you want the Northwest corner of the scroll to be. Hold the left mouse key down and drag the crosshairs to the Southeast corner and let go of the mouse.

  5. Right click on top of the scroll bar and select Format Control, as shown in Fig. A3.

  6. Input in the Format Control box, as shown in Fig. A4:

    • Current value: 0

    • Minimum value: 0

    • Maximum value: 5,000

    • Incremental change: 250

    • Cell link: $B$4

    The cell link is the cell whose values will be changing and in this case, it is the units sold.

  7. Click the Esc key to remove the square handles around the scroll bar.

    Fig. A2. 
The Scroll Bar Icon under Form Controls.

    Fig. A2.

    The Scroll Bar Icon under Form Controls.

    Fig. A3. 
The Menu List for Format Control.

    Fig. A3.

    The Menu List for Format Control.

    Fig. A4. 
The Values to Input into the Format Control Dialog Box.

    Fig. A4.

    The Values to Input into the Format Control Dialog Box.

    Click on the scroll bar’s arrow and the corresponding values in the spreadsheet will change.

You can further link the changing scroll bar cell value to a graph. For example:

  1. Highlight cells A23:C27.

  2. From the top Menu click on Insert/Scatter and choose the left most scatterplot graph on the second row.

The diagonal line is the Profit line and the vertical is the current units sold. As you click on the scroll bar arrow, notice not only the values on the spreadsheet change, but the vertical line shifts on the graph.

One-way Data Table

To generate a one-way data table for the breakeven analysis example in Fig. 2:

  1. Input 0 to 4,000 in cells B43:B51, incrementing by 500.

  2. In cell C42 put =B13.

  3. Highlight B42:C51.

  4. Click on Data from the top menu.

  5. Under Data Tools/What-If Analysis, click on data table.

  6. Input in Column cell box B4.

  7. Click OK.

A one-way data table similar to Fig. 3 will appear.

Two-way Data Table

As shown in Fig. 5, we need to explain a few steps we did prior to producing the two-way data table:

  • In cells K18:L12, we copied the coefficients names and values.

  • In cells O9:O12, we put arbitrary initial values for each predictor variables.

  • In cell O13, given the predictor values, is the estimated house price using the regression equation, using this formula: = $ L $ 8 + SUMPRODUCT ( L 9 : L 12 , O 9 : O 12 ) .

  • The scroll bar is linked to cell O9, the number of square feet.

To produce a two-way data table showing how house prices vary as the number of bedrooms and bathrooms vary, follow these steps:

  1. As shown, in cells K17:L19 and in cells M15:Q16, put the rows and columns labels.

  2. In cell I16, put the formula =O13.

  3. Highlight I16:Q19.

  4. From the top menu, click on Data/What-If Analysis, click on data table.

  5. Row Cell input O11 and Column Cell input O10.

  6. Click OK.

Moving the scroll bar will change the number of square feet, cell O9 and corresponding, the values in the two-way data table will change. The bar chart graph will change as well since it is linked to the values in the two-way data table.

Simulation

Breakeven Analysis Example

The formulas to generate the breakeven analysis example simulation are shown in Fig. A5. In Column B, the formula is repeated for each period and calculates a possible number of units sold for a normal distribution with a mean of 1,200 and standard deviation of 200. The formulas in columns C, D, and E calculate the revenue, costs, and profit for that period given the number of units sold generated in column C. The average is calculated in cell G10, and in cell G19 the number of losses is calculated.

Fig. A5. 
The Formulas for the Breakeven Analysis Simulation.

Fig. A5.

The Formulas for the Breakeven Analysis Simulation.

Appendix

Scroll Bar

First, we must add the scroll bar to your menu by adding the Developer tab to your top Menu:

  1. Click on File from the top Menu.

  2. Click on Options.

  3. Click on Customize Ribbon.

  4. On the right Main tabs list, click on the Developer box, as shown in Fig. A1.

Fig. A1. 
Click on the Developer Box.

Fig. A1.

Click on the Developer Box.

To create a scroll bar for the breakeven analysis example in Fig. 2:

  1. Click the Developer Tab in the top Menu.

  2. Under Controls, click the down arrow under Insert.

  3. As shown in Fig. A2, under Form Controls, click on the scroll bar icon.

  4. The mouse will now have crosshairs. Move the mouse to where you want the Northwest corner of the scroll to be. Hold the left mouse key down and drag the crosshairs to the Southeast corner and let go of the mouse.

  5. Right click on top of the scroll bar and select Format Control, as shown in Fig. A3.

  6. Input in the Format Control box, as shown in Fig. A4:

    • Current value: 0

    • Minimum value: 0

    • Maximum value: 5,000

    • Incremental change: 250

    • Cell link: $B$4

    The cell link is the cell whose values will be changing and in this case, it is the units sold.

  7. Click the Esc key to remove the square handles around the scroll bar.

    Fig. A2. 
The Scroll Bar Icon under Form Controls.

    Fig. A2.

    The Scroll Bar Icon under Form Controls.

    Fig. A3. 
The Menu List for Format Control.

    Fig. A3.

    The Menu List for Format Control.

    Fig. A4. 
The Values to Input into the Format Control Dialog Box.

    Fig. A4.

    The Values to Input into the Format Control Dialog Box.

    Click on the scroll bar’s arrow and the corresponding values in the spreadsheet will change.

You can further link the changing scroll bar cell value to a graph. For example:

  1. Highlight cells A23:C27.

  2. From the top Menu click on Insert/Scatter and choose the left most scatterplot graph on the second row.

The diagonal line is the Profit line and the vertical is the current units sold. As you click on the scroll bar arrow, notice not only the values on the spreadsheet change, but the vertical line shifts on the graph.

One-way Data Table

To generate a one-way data table for the breakeven analysis example in Fig. 2:

  1. Input 0 to 4,000 in cells B43:B51, incrementing by 500.

  2. In cell C42 put =B13.

  3. Highlight B42:C51.

  4. Click on Data from the top menu.

  5. Under Data Tools/What-If Analysis, click on data table.

  6. Input in Column cell box B4.

  7. Click OK.

A one-way data table similar to Fig. 3 will appear.

Two-way Data Table

As shown in Fig. 5, we need to explain a few steps we did prior to producing the two-way data table:

  • In cells K18:L12, we copied the coefficients names and values.

  • In cells O9:O12, we put arbitrary initial values for each predictor variables.

  • In cell O13, given the predictor values, is the estimated house price using the regression equation, using this formula: = $ L $ 8 + SUMPRODUCT ( L 9 : L 12 , O 9 : O 12 ) .

  • The scroll bar is linked to cell O9, the number of square feet.

To produce a two-way data table showing how house prices vary as the number of bedrooms and bathrooms vary, follow these steps:

  1. As shown, in cells K17:L19 and in cells M15:Q16, put the rows and columns labels.

  2. In cell I16, put the formula =O13.

  3. Highlight I16:Q19.

  4. From the top menu, click on Data/What-If Analysis, click on data table.

  5. Row Cell input O11 and Column Cell input O10.

  6. Click OK.

Moving the scroll bar will change the number of square feet, cell O9 and corresponding, the values in the two-way data table will change. The bar chart graph will change as well since it is linked to the values in the two-way data table.

Simulation

Breakeven Analysis Example

The formulas to generate the breakeven analysis example simulation are shown in Fig. A5. In Column B, the formula is repeated for each period and calculates a possible number of units sold for a normal distribution with a mean of 1,200 and standard deviation of 200. The formulas in columns C, D, and E calculate the revenue, costs, and profit for that period given the number of units sold generated in column C. The average is calculated in cell G10, and in cell G19 the number of losses is calculated.

Fig. A5. 
The Formulas for the Breakeven Analysis Simulation.

Fig. A5.

The Formulas for the Breakeven Analysis Simulation.