Throughout the text we will demonstrate how to solve management science models on the computer by using Excel spreadsheets and QM for Windows, a general-purpose quantitative methods software package by Howard Weiss. QM for Windows has program modules to solve almost every type of management science problem you will encounter in this book. There are a number of similar quantitative methods software packages available on the market, with similar characteristics and capabilities as QM for Windows. In most cases you simply input problem data (i.e., model parameters) into a model template, click on a solve button, and the solution appears in a Windows format. QM for Windows is included on the CD that accompanies this text.
Spreadsheets are not always easy to use, and you cannot conveniently solve every type of management science model by using a spreadsheet. Most of the time you must not only input the model parameters but also set up the model mathematics, including formulas, as well as your own model template with headings to display your solution output. However, spreadsheets provide a powerful reporting tool in which you can present your model and results in any format you choose. Spreadsheets such as Excel have become almost universally available to anyone who owns a computer. In addition, spreadsheets have become very popular as a teaching tool because they tend to guide the student through a modeling procedure, and they can be interesting and fun to use. However, because spreadsheets are somewhat more difficult to set up and apply than is QM for Windows, we will spend more time explaining their use to solve various types of problems in this text.
One of the difficult aspects of using spreadsheets to solve management science problems is setting up a spreadsheet with some of the more complex models and formulas. For the most complex models in the text we will show how to use Excel QM, a supplemental spreadsheet macro that is included on the CD that accompanies this text. A macro is a template or an overlay that already has the model format with the necessary formulas set up on the spreadsheet so that the user only has to input the model parameters. We will demonstrate Excel QM in six chapters, including this chapter, Chapter 6 ("Transportation, Transshipment, and Assignment Problems"), Chapter 12 ("Decision Analysis"), Chapter 13 ("Queuing Analysis"), Chapter 15 ("Forecasting"), and Chapter 16 ("Inventory Management").
Later in this text we will also demonstrate two spreadsheet add-ins, TreePlan and Crystal Ball. TreePlan is a program for setting up and solving decision trees that we use in Chapter 12 ("Decision Analysis"), whereas Crystal Ball is a simulation package that we use in Chapter 14 ("Simulation"). Also, in Chapter 8 ("Project Management") we will demonstrate Microsoft Project.
In this section we will demonstrate how to use Excel, Excel QM, and QM for Windows, using our break-even model example for Western Clothing Company.
To solve the break-even model using Excel, you must set up a spreadsheet with headings to identify your model parameters and variables and then input the appropriate mathematical formulas into the cells where you want to display your solution. Exhibit 1.1 shows the spreadsheet for the Western Clothing Company example. Setting up the different headings to describe the parameters and the solution is not difficult, but it does require that you know your way around Excel a little. Appendix B provides a brief tutorial titled "Setting Up and Editing a Spreadsheet" for solving management science problems.
Notice that cell D10 contains the break-even formula, which is displayed on the toolbar near the top of the screen. The fixed cost of $10,000 is typed in cell D4, the variable cost of $8 is in cell D6, and the price of $23 is in cell D8.
As we present more complex models and problems in the chapters to come, the spreadsheets we will develop to solve these problems will become more involved and will enable us to demonstrate different features of Excel and spreadsheet modeling.
The Excel QM Macro for Spreadsheets
Excel QM is included on the CD that accompanies this text. You can install Excel QM onto your computer by following a brief series of steps displayed when the program is first accessed.
After Excel is started, Excel QM is normally accessed from the computer's program files, where it is usually loaded. When Excel QM is activated, "QM" will appear at the top of the spreadsheet (as indicated in Exhibit 1.3). Clicking on "QM" will pull down a menu of the topics in Excel QM, one of which is break-even analysis. Clicking on "Break-Even Analysis" will result in the window for spreadsheet initialization shown in Exhibit 1.2. Every Excel QM macro listed on the menu will start with a "Spreadsheet Initialization" window similar to this one.
In the window in Exhibit 1.2 you can enter a spreadsheet title and choose under "Options" whether you also want volume analysis and a graph. Clicking on "OK" will result in the spreadsheet shown in Exhibit 1.3. The first step is to input the values for the Western Clothing Company example in cells B10 to B13, as shown in Exhibit 1.3. The spreadsheet shows the break-even volume in cell B17. However, notice that we have also chosen to perform some volume analysis by entering a hypothetical volume of 800 units in cell B13, which results in the volume analysis in cells B20 to B23.
QM for Windows
You begin using QM for Windows by clicking on the "Module" button on the toolbar at the top of the main window that appears when you start the program. This will pull down a window with a list of all the model solution modules available in QM for Windows. Clicking on the "Break-even Analysis" module will access a new screen for typing in the problem title. Clicking again will access a screen with input cells for the model parametersthat is, fixed cost, variable cost, and price (or revenue). Next, clicking on the "Solve" button at the top of the screen will provide the solution to the Western Clothing Company example, as shown in Exhibit 1.4.
You can also get the graphical model and solution for this problem by clicking on "Window" at the top of the solution screen and selecting the menu item for a graph of the problem. The break-even graph for the Western Clothing example is shown in Exhibit 1.5.