It cannot be argued that the essence of management is about decision-making. Although other functions might be attributable to managers, it can be stated that decision-making might lie within the core of such functions. The exception might be seen in such functions as motivation, inspiration and leadership. Such factors as the lack of information, situations of uncertainty, and resource constraints might lead to variations in the difficulty of the decision-making process. In such cases, managers might rely on different models that will aid them in the decision-making process (Monahan, 2000, p. 6). In that regard, this paper will provide an explanation of how modeling can be used to help managers in the decision-making process and help them solve problems.
Modeling with Spreadsheets
Spreadsheet modeling can be seen as one of the tools that can aid managers in solving problems. Despite the absence of a standard procedure to follow, there is a structure that outlines the basic steps that should be followed when modeling a problem. The first step in such a structure is planning, considering that the starting point in any modeling process is a certain problem, planning is concerned with the desired outcome or the goal, i.e. the answer to the question formulated by the problem. When the desired outcome is established, a calculation by hand should be another step in the planning process, in which the necessary formulas are identified (Hillier & Hillier, 2010, p. 123). With that in mind, a sketch of the spreadsheet will finalize the planning stage of modeling. The next stage is an iterative process of building a small version of the model and testing it. Such a process will enable managers to make sure that the model is working correctly. Finally, the small model should be expanded into a full scale, which similarly should be tested by managers. The final step of the modeling process can be seen through using the model and analyzing the results that it should provide. In that regard, there are different types of analysis managers can utilize spreadsheets for, a few of which will be discussed in the following sections.
Building a Good Spreadsheet Model
The aforementioned steps in modeling are too general and merely show the structure in solving problems through spreadsheets. In order for the spreadsheet model to be effective, there are guidelines following which will result in a ‘good’ spreadsheet model. The guidelines advise managers to enter and layout all the data available on a particular problem. The organization of the data is another important factor distinguishing a good model from a bad one. The organization is concerned with such actions as grouping data, making appropriate labels, using absolute and relative formulas, and using different coloring to differentiate various data. Such actions, not only facilitate using the model in general, but it also makes it easier to reuse the model in other models and by different managers. Accordingly, a good model will be easier to modify in the future as it will enable even new managers to understand the structure of the model and apply the necessary revisions and updates (Powell & Baker, 2010, p. 135).
A good model is useless without applying analysis to the data outputs of such a model. One of the most common models of analysis is a what-if-analysis. What-if –analysis can be defined as the process of “assessing the change in outputs associated with a given change in inputs” (Powell & Baker, 2010, p. 120). What-if-analysis can be perceived as a prediction tool that answers questions such as what would happen to a solution “if different assumptions were made about future conditions” (Hillier & Hillier, 2010, p. 144). With managers looking for more than just finding an optimal solution, what-if-analysis can provide them with beneficial insights into their problems.
In addition to optimal solutions, managers might be interested in worst-case scenarios, in which the real numbers might differ significantly from those estimated. Such an aspect is especially important in the light of the dynamic environment in which the business operates in general and some industries in particular. Varying input(s) will show managers’ parameters are sensitive, i.e., “those parameters where extra care is needed to refine their estimates because even small changes in their values can change the optimal solution” (Hillier & Hillier, 2010, p. 145). Additionally, solutions provided by spreadsheet models can be based on parameters representing policy decisions. In that regard, what-if-analysis can guide managers on the influence of changing such policy decisions (p. 146).
Another method of analyzing spreadsheet models can be seen through networks. A network can be seen as a system of a schematic representation of events. A representation of such a network portrays the relationships between the elements of such a system. Taking the supply chain network of a particular manufacturer as an example, the system will consist of many elements such as the supplier, the manufacturer, the retailer, the customer, the warehouses, etc (Teigen, 1997). Accordingly, there are certain processes that occur in each and between each element of such a system, such as manufacturing, transportation, storage, etc. Each of such processes takes a certain time to be completed, and accordingly, each of those processes relies on the completion of another process within the system. Such a system can be visually represented, where the various interrelations between the elements of the system can be named network.
Another example of a network can be seen through a distribution scheme of a particular product into warehouses. Each starting and ending position in such a scheme can be considered as an element of the network, which in this case are the manufacturing plants and the warehouses. Transportation and shipping are the only processes occurring between each element within such a system. Accordingly, each process takes a particular time and does not start until a previous process ends, e.g. transportation starts after shipping ends. The visual representation of the relations with such a system is a network.
Often in networks such as those represented previously many problems occur. Such problems can be related to factors such as costs, time, efficiency, etc. In that regard, another type of analysis can be useful to solve such a problem, which is called optimization analysis. Optimization analysis can be defined as the process of identifying variables and their values that will “achieve the best possible value of an output” (Powell & Baker, 2010, p. 132). In a network context the best possible value might be represented through three typical network optimization problems, which are explained as follows:
- Minimum-cost flow problem – a problem solving which reduces the costs of sending supply through the network to satisfy demands, and accordingly maximizes profits. Such analysis will enable finding optimal values of dependent variables, amount moved and time consumed, which will result in the least independent variable possible, which is cost.
- Maximum flow problems – a problem solving which will maximize the amount of flow from the source, i.e., the nod at which flow originates, to the sink, i.e., the node at which the flow is terminated (Hillier & Hillier, 2010, p. 201). Such analysis will enable finding optimal values of dependent variables, costs and time consumed, which will result in the maximum value possible of the independent variable, which is costs.
- Shortest paths problems – a problem solving which will show the shortest path between two points. Only the origin and the destination are vital in such a problem. Such analysis will enable finding optimal values of dependent variables, cost and time, that will result in the least independent variable possible, which is distance.
Such types of analysis as what-if-analysis, network optimization answer such questions of what will happen and how to optimize the process. In many cases, managers require a simple approval or rejection of a particular option. In such cases, such analysis as yes-or-no decisions might be considered. Yes-or-no decisions, as the title implies, are decisions with the only options that should be considered are whether to go with the option – yes, or reject the option – no. In that regard, managers are not concerned with the reasons; they are only concerned with the outcome. Such a decision is not necessarily single, where there might be an array of options, which are mutually exclusive. Going further with one option lead to other options being considered, etc, and thus, an array of several combinations should be considered. An example of the latter can be seen through the decision of building a warehouse, a decision accepting which lead to that another option might occur which is whether building such warehouse in San Francisco or Los Angeles, etc. It can be seen that there are different combinations of yes-or-no decisions that should be made, all of which have the objective of maximizing the profit of companies.
Binary Variables and Binary Decision Variables
An important aspect that should aid managers in taking yes-or-no decisions is binary variables. Binary variables are those variables that can take only one of two states 1 or 0, on or off, present or absent, etc. The main characteristic of such variables is that their states are mutually exclusive, where they cannot take more than one state at once; taking on state excludes the other. Applying such variable for yes-or-no-decisions, each binary variable implies a certain decision outcome, i.e., 1 for yes and 0 for no (Hillier & Hillier, 2010, p. 231). There are different types of decisions for which binary variables can be applied. Decisions that are mutually exclusive are one type, where taking one alternative implies that the latter is not considered other types of binary decision variables are contingency decision variables, the state of a variable is dependant on the state of another variable. The decision to build a warehouse in a city (yes) is dependent on whether the decision to build a factory in the same city took a yes state (Hillier & Hillier, 2010).
BIP, Pure BIP, and Mixed BIP
When a particular model of analysis fits linear programming using binary decision variables such model is called binary integer programming (BIP). There are two different types of BIPs, pure BIP, i.e., a BIP in which variables used are binary, and mixed BIP, i.e., a model in which only some of the variables are binary (Hillier & Hillier, 2010). An example of pure BIP can be seen through the problem of building a warehouse in a city in which a factory should be built. A mixed BIP, on the other hand, can be seen through the same case in which a what-if-analysis will be conducted to analyze the setup costs. A BIP model might imply any of the concepts or the methods of analysis previously introduced. Thus, a spreadsheet might be modeled for a situation that involves binary decision variables, for which a what-if-analysis might be used for any part of the model.
The present paper provided an overview of the spreadsheets modeling and linear programming basics as tools that can aid managers in decision-making and help them solve problems. Additionally, the paper provided examples of the way these models can be used. It can be concluded that decision-making is one of the most important functions of management, for which different models might be used to aid managers to perform such functions. Despite the fact that the problems that managers face might be different, most of them rely on data, for which the presented spreadsheet modeling might be a suitable tool for analysis. In that regard, it can be concluded that spreadsheet modeling is a helpful tool that aids managers in analyzing problems and making decisions that shall solve such problems.
Hillier, F. S., & Hillier, M. S. (2010). Introduction to Management Science: A Modeling and Case Studies Approach with Spreadsheets: McGraw-Hill Education.
Monahan, G. E. (2000). Management decision-making: spreadsheet modeling, analysis, and application. Cambridge, UK; New York: Cambridge University Press.
Powell, S. G., & Baker, K. R. (2010). Management science: the art of modeling with spreadsheets (3rd ed.). Hoboken, N.J.: Wiley.
Teigen, R. (1997). Supply Chain Management. Enterprise Integration Laboratory. Web.