CBTL212

Managerial Problem Solving

Gmail Account Information

Username:

Password: cbtl212unizul

Chapter 2

Introduction to Spreadsheet Modeling

2.1 INTRODUCTION

We are not teaching Excel just for its many interesting features. Rather, we plan to use these features to provide insights into real business problems. In short, Excel is a problem-solving tool, not an end in itself, in this book.

2.2 BASIC SPREADSHEET MODELING: CONCEPT AND BEST PRACTICES

Most mathematical models, including spreadsheet models, involve inputs, decision variables, and outputs. The inputs have given fixed values, at least for the purposes of the model. The decision variables are those a decision maker controls. The outputs are the ultimate values of interest; they are determined by the inputs and the decision variables.

Example:

A manager must place an order for a certain seasonal product. This product will go out of date fairly soon, so this is the only order that will be made for the product.

The inputs are the fixed cost of the order;

  • the unit variable cost of each item ordered;
  • the price charged for each item sold;
  • the “salvage” value for each item, if any, left in inventory after the product has gone out of date;
  • and the demand for the product.

The decision variable is the number of items to order.

Finally, the key output is the profit (or loss) from the product. You can also break this output into the outputs that contribute to it:

  • the total ordering cost,
  • the revenue from sales, and
  • the salvage value from leftover items.

You certainly have to calculate these outputs to obtain profit.

Spreadsheet modeling is the process of entering the inputs and decision variables into

a spreadsheet and then relating them appropriately, by means of formulas, to obtain the outputs.

Remember to construct your spreadsheet models with readability in mind. Several features that can improve readability include the following:

  • A clear, logical layout to the overall model
  • Separation of different parts of a model, possibly across multiple worksheets
  • Clear headings for different sections of the model and for all inputs, decision variables, and outputs
  • Liberal use of range names
  • Liberal use of boldface, italics, larger font size, coloring, indentation, and other for matting features
  • Liberal use of cell comments
  • Liberal use of text boxes for assumptions and explanations

GO TO EXAMPLE 2.1 - EXCEL

TIPS FOR EDITING AND DOCUMENTING SPREADSHEETS

Editing and documenting your spreadsheet models is crucial, and the following tips make

these tasks much easier.

Format Appropriately

Appropriate formatting can make a spreadsheet model much easier to read. To boldface, for example, select one or more cells and click on the B button on the Home ribbon (or press Ctrl+B). Similarly, to italicize, indent, increase or decrease the number of decimal places, right-justify, or perform other common formatting tasks, use the buttons on the Home ribbon.

UseRange Names

Naming ranges takes time but makes formulas much easier to read and understand. To enter a range name, highlight any cell or range of cells and enter a name for the range in the Name box (just to the left of the Formula Bar). If you want to edit or delete range names, select Name Manager on the Formulas ribbon. Here are some other options you have from the Defined Names group on the Formulas ribbon.

  • After you’ve named some ranges, you can get a list of them in your spreadsheet by placing the cursor at the top of the range where you want the list to be placed, selecting the Use in Formula dropdown on the Formula ribbon, and clicking on the Paste List option. Alternatively, press the F3 button.
  • Suppose you have labels such as Fixed Cost, Variable Cost, Revenue, and Profit in the range A3:A6, with their values next to them in column B. If you want to name the cells in column B with the labels in column A highlight the range A3:B6, select. Create from Selection on the Formulas ribbon (or press Ctrl+Shift+F3) and make sure the Left Column box is checked. This creates the range names you want.
  • If you have a formula such as =SUM(Al0:A20) and then you name the range Al0:A20 Costs, say, the formula does not change automatically to =SUM(Costs). However you can make it adapt to your new range name by selecting Apply Names from the Define Name dropdown on the Formulas ribbon

Use Text Boxes

Text boxes are very useful for documenting your work. To enter an explanation or any other text into a text box, go to the Insert ribbon, click on the Text Box button and drag a box, and start typing. This technique is much better than typing explanations into cells be cause text boxes have word wrap. Therefore, text in text boxes is much easier to edit than text in cells.

Use Cell Comments

Cell comments provide another good way to document your work. To enter a comment in a cell, select the cell and right-click. This brings up a dialog box (which is also useful for other tasks such as formatting). Click on the Insert Comment item to enter a comment. If a comment is already in the cell, this menu will contain Edit Comment and Delete Comment items, The cells with comments should have small red triangles in their corners.