# Decision Analysis PART-1 Please go through Examples 1.2 ( at page 12) and 1.4 (Sam’s Bookstore at page 20) from our textbook (in attachment ) and create E

Decision Analysis PART-1 Please go through Examples 1.2 ( at page 12) and 1.4 (Sam’s Bookstore at page 20) from our textbook (in attachment ) and create Excel files as described in examples. Show all the work they implemented and get familiar with some nice features of Excel. Each example will be 20 POINTS

Page : 38-41, 46-50 in attachment

PART-2 For each of below questions I provide the solution values. So for each question you need to:`z

a) Understand problem

b) Decide on decision variables

c) Form your objective function

d) Form your constraints

e) Bring parts b, c and d all together to create a linear programming model

f) Transfer this model into Excel

g) Use solver to find optimum solution

h) Interpret the results and tell me what would be your and interpretations recommendation based on this solution.

Deliverable:

Create a single excel file and name it as Assignment week1_ yourname_lastname

For each question use a separate Excel worksheet not different files. Name worksheet-1 as Example-1, worksheet-2 as Example-2, Worksheet-3 as Q1 and worksheet-4 as Q2

Show all of your model and interpretation in the same worksheet.

Please do not copy and paste from other students. Please remember that everybody’s design will be different and there is no chance that two student’s models would be exactly same.

Please also make sure that you used SOLVER for optimum solution.

DUE DATE : Please submit your excel file before our first class on Sept 10, 2021 before our next class. Late assignments will not be accepted.

Question 1 30 POINTS

A company produces two types of tables, T1 and T2. It takes 2 hours to produce the parts of one unit of T1, 1 hour to assemble and 2 hours to polish. .It takes 4 hours to produce the parts of one unit of T2, 2.5 hour to assemble and 1.5 hours to polish. Per month, 7000 hours are available for producing the parts, 4000 hours for assembling the parts and 5500 hours for polishing the tables. The profit per unit of T1 is $90 and per unit of T2 is $110. How many of each type of tables should be produced in order to maximize the total monthly profit?

Answer 1

The maximum profit is $273000 . Hence the company needs to produce 2300 tables of type T1 and 600 tables of type T2 in order to maximize its profit.

Question-2 30 POINTS

John has $20,000 to invest in three funds F1, F2 and F3. Fund F1 is offers a return of 2% and has a low risk. Fund F2 offers a return of 4% and has a medium risk. Fund F3 offers a return of 5% but has a high risk. To be on the safe side, John invests no more than $3000 in F3 and at least twice as much as in F1 than in F2. Assuming that the rates hold till the end of the year, what amounts should he invest in each fund in order to maximize the year end return?

Answer_2

The return R is maximum at 603.33.

For maximum return, John has to invest $11333 in fund F1, $5667 in fund F2 and $3000 in fund F3. 1-3b Cost Projections

In the following example, a company wants to project its costs of producing products,

given that material and labor costs are likely to increase through time. We build a simple

model and then use Excel’s charting capabilities to obtain a graphical image of projected

costs.

The model in Figure 1.6 is still not the last word on this example. As shown in later examples, you can create data tables

to see how sensitive profit is to the inputs, the demand, and the order quantity. You can also create charts to show results graph-

ically. But this is enough for now. You can see that the model in Figure 1.6 is now much more readable and flexible than the

original model in Figure 1.2.

1 2 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

A B C D E F

NCAA t-shirt sales

Input egnaRselbairav names used

Fixed order cost $750 Demand =’Model 5′!$B$10

Variable cost $8 Discount_price =’Model 5′!$B$7

Selling price $18 Fixed_order_cost =’Model 5′!$B$4

Discount price $6 Order =’Model 5′!$B$13

Selling_price =’Model 5′!$B$6

Uncertain variable Variable_cost =’Model 5′!$B$5

0051dnameD

Decision variable

0541redrO

Output variables

Costs

Fixed cost $750

Variable costs $11,600

Revenues

Full-price shirts $26,100

Discount-price shirts $0

$13,750tiforP

Figure 1.6 Model with

Category Labels and Color

Coding

EXAMPLE

1.2 PROJECTING THE COSTS OF BOOKSHELVES

AT WOODWORKS

The Woodworks Company produces a variety of custom-designed wood furniture for its customers. One favorite item is a

bookshelf, made from either cherry or oak. The company knows that wood prices and labor costs are likely to increase in the

future. Table 1.1 shows the number of board-feet and labor hours required for a bookshelf, the current costs per board-foot and

labor hour, and the anticipated annual increases in these costs. (The top row indicates that either type of bookshelf requires

30 board-feet of wood and 16 hours of labor.) Build a spreadsheet model that enables the company to experiment with the

growth rates in wood and labor costs so that a manager can see, both numerically and graphically, how the costs of the book-

shelves increase in the next few years.

resource Cherry Oak Labor

Required per bookshelf 30 30 16

Current unit cost $5.50 $4.30 $18.50

Anticipated annual cost

increase

2.4% 1.7% 1.5%

Table 1.1 Input Data for

Manufacturing a Bookshelf

Objective

To learn good spreadsheet practices, to create copyable formulas with the careful use of relative and absolute addresses, and to

create line charts from multiple series of data.

09953_ch01_ptg01_001-036.indd 12 04/03/19 10:51 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).

Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-202

1-3 Introduction to Spreadsheet Modeling 1 3

Solution

The completed spreadsheet model appears in Figure 1.7 and in the file Bookshelf Costs Finished.xlsx. You can develop it with

the following steps.

1. Inputs. You should usually enter the inputs for a model in the upper-left corner of a work-

sheet as you can see in the shaded ranges in Figure 1.7. We have used our standard conven-

tion of coloring inputs—the numbers from the statement of the problem—blue. You can

develop your own convention, but the input cells should be distinguished in some way. Note

that the inputs are grouped logically and are explained with appropriate labels. You should

always document your spreadsheet model with descriptive labels. Also, note that by entering

inputs explicitly in input cells, you can refer to them later in Excel formulas.

Always enter input values

in input cells and then

refer to them in Excel

formulas. Do not bury

input values in formulas.

Figure 1.7 Bookshelf Cost

Model

Relative and Absolute Addresses in Formulas

Relative and absolute addresses are used in Excel formulas to facilitate copying. A dollar sign next to a column or

row address indicates that the address is absolute and will not change when copying. The lack of a dollar sign indi-

cates that the address is relative and will change when copying. After you select a cell in a formula, you can press the

F4 key repeatedly to cycle through the relative/absolute possibilities: =B4 (both column and row relative); =$B$4

(both column and row absolute); =B$4 (column relative, row absolute); and =$B4 (column absolute, row relative).

Excel Tip

Always try to organize

your spreadsheet model

so that you can copy

formulas across multiple

cells.

2. Design output table. Plan ahead for how you want to structure your outputs. We created a table where there is a row for

every year in the future (year 0 corresponds to the current year), there are three columns for projected unit costs (columns

B–D), and there are two columns for projected total bookshelf costs (columns E and F). The headings reflect this design.

This isn’t the only possible design, but it works well. The important point is that you should have some logical design in

mind before you dive in.

3. Projected unit costs of wood. The dollar values in the range B19:F25 are calculated from

Excel formulas. Although the logic in this example is straightforward, it is still important to

have a strategy in mind before you enter formulas. In particular, you should always try to

design your spreadsheet so that you can enter a single formula and then copy it. This saves

work and avoids errors. For the costs per board-foot in columns B and C, enter the formula

=B9

in cell B19 and copy it to cell C19. Then enter the general formula

5B19*(11B$10)

in cell B20 and copy it to the range B20:C25. We assume you know the rules for absolute

and relative addresses (dollar sign for absolute, no dollar sign for relative), but it takes some

planning to use these so that copying is possible. Make sure you understand why we made

row 10 absolute but column B relative.

Typing dollar signs in

formulas for absolute

references is inefficient.

Press the F4 key instead.

Press the Fn key and

the F4 key (together) on

Mac keyboards.

09953_ch01_ptg01_001-036.indd 13 04/03/19 10:51 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).

Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-202

4 Projected unit labor costs. To calculate projected hourly labor costs, enter the formula

=B13

in cell D19. Then enter the formula

5D19*(11B$14)

in cell D20 and copy it down column D.

5 Projected bookshelf costs. Each bookshelf cost is the sum of its wood and labor costs. By a careful use of absolute and

relative addresses, you can enter a single formula for these costs—for all years and for both types of wood. To do this,

enter the formula

5B$5*B191B$6*$D19

in cell E19 and copy it to the range E19:F25. The idea here is that the units of wood and labor per bookshelf are always

in rows 5 and 6, and the projected unit labor cost is always in column D, but all other references are relative to allow

copying.

6 Chart. A chart is a valuable addition to any table of data, especially in the busi-

ness world, so charting in Excel is a skill worth mastering. We illustrate some

of the possibilities here, but we urge you to experiment with other possibilities

on your own. Start by selecting the range E18:F25—yes, including the labels in

row 18. Next, click the Line dropdown list on the Insert ribbon and select the

Line with Markers type. You instantly get the line chart you want, with one series

for Cherry and another for Oak. Also, when the chart is selected (that is, it has a

border around it), you see two Chart Tools tabs, Design and Format. There are

also three useful buttons to the right of the chart. (These three buttons were intro-

duced in Excel 2013, and the two tabs condense the tools in the three tabs from

Excel 2007 and 2010.) The most important button is the Select Data button on the Design ribbon. It lets you choose the

ranges of the data for charting in case Excel’s default choices aren’t what you want. (The default choices are based on the

selected range when you create the chart.)

Click Select Data now to obtain the dialog box in Figure 1.8. On the left, you control the series (one series or multiple

series) being charted; on the right, you control the data used for the horizontal axis. By selecting E18:F25, you have the

series on the left correct, including the names of these series (Cherry and Oak), but if you didn’t, you could select one of

the series and click the Edit button to change it. The data on the horizontal axis are currently the default 1, 2, and so on. To

use the data in column A, click the Edit button on the right and select the range A19:A25. Then you can experiment with

various formatting options to make the chart even better. For example, we rescaled the vertical axis to start at $300 rather

than $0 (right-click any of the numbers on the vertical axis and select Format Axis), and we added a chart title at the top

and a title for the horizontal axis at the bottom.

1 4 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

The many chart options are easily

accessible from the Chart Tools

tabs that are visible when a chart

is selected. Don’t be afraid to

experiment with them to produce

professional-looking charts.

Figure 1.8 Select Data Source

Dialog Box

The Select Data

Source dialog box in

Excel for Mac has a

different layout, but

the options are basically the

same.

The three buttons to the

right of the chart don’t

appear in Excel for Mac.

09953_ch01_ptg01_001-036.indd 14 04/03/19 10:51 PM

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).

Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-202

Using the Model for What-If Questions

The model in Figure 1.7 can now be used to answer many what-if questions. In fact, many models are built for the purpose of

permitting experimentation with various scenarios. The important point is that the model has been built in such a way that a

manager can enter any desired values in the input cells, and all outputs, including the chart, will update automatically.

1-3 Introduction to Spreadsheet Modeling 1 5

1-3c Breakeven Analysis

Many business problems require you to find the appropriate level of some activity. This

might be the level that maximizes profit (or minimizes cost), or it might be the level that

allows a company to break even—no profit, no loss. The following example illustrates a

typical breakeven analysis.

EXAMPLE

1.3 BREAKEVEN ANALYSIS AT QUALITY SWEATERS

The Quality Sweaters Company sells hand-knitted sweaters. The company is planning to print a catalog of its products and

undertake a direct mail campaign. The cost of printing the catalog is $20,000 plus $0.10 per catalog. The cost of mailing each

catalog (including postage, order forms, and buying names from a mail-order database) is $0.15. In addition, the company

plans to include direct reply envelopes in its mailings and incurs $0.20 in extra costs for each direct mail envelope used by a

respondent. The average size of a customer order is $40, and the company’s variable cost per order (primarily due to labor and

material costs) averages about 80% of the order’s value—that is, $32. The company plans to mail 100,000 catalogs. It wants to

develop a spreadsheet model to answer the following questions:

1. How does a change in the response rate affect profit?

2. For what response rate does the company break even?

Objective

To learn how to work with range names, to learn how to answer what-if questions with one-way data tables, to introduce Excel’s

Goal Seek tool, and to learn how to document and audit Excel models with cell comments and Excel’s formula auditing tools.

Solution

The completed spreadsheet model appears in Figure 1.9. (See the file Breakeven

Analysis Finished.xlsx.) First, note the clear layout of the model. The input cells

are colored blue, they are separated from the outputs, headings are boldfaced, several

headings are indented, numbers are formatted appropriately, and a list to the right

spells out all range names we have used. (See the next Excel Tip on how to create this

list.) Also, following the convention we use throughout the book, the decision variable

(number mailed) is colored red, and the bottom-line output (profit) is colored gray.

Creating Range Names

To create a range name for a range of cells (which could be a single cell), highlight the cell(s), click in the Name Box

just to the left of the Formula Bar, and type a range name. Alternatively, if a column (or row) of labels appears next

to the cells to be range-named, you can use these labels as the range names. To do this, highlight the labels and the

cells to be named (for example, A4:B5 in Figure 1.9), click Create from Selection on the Formulas ribbon, and make

sure the appropriate box in the resulting dialog box is checked. The labels in our example are to the left of the cells

to be named, so the Left column box should be checked. This is a quick way to create range names, and we did it for

all range names in the example. Note that if a label contains any “illegal” range-name characters, such as a space, the

illegal characters are converted to underscores.

Excel Tip

Adopt some layout and formatting

conventions, even if they

differ from ours, to make your

spreadsheets readable and easy

to follow.

09953_ch01_ptg01_001-036.indd 15 04/03/19 10:51 PM

Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Figure 1.16 Precedents of

Total_revenue Cell 1

2

3

4

5

6

7

8

9

10

11

12

13

EDCBA

Quality Sweaters direct mail model

Catalog ledoMstupni of responses

Fixed cost of printing $20,000 Response %8etar

Variable cost of printing mailing $0.25 Number of 0008sesnopser

Decision ledoMelbairav of revenue, costs, and profit

Number latoT000001deliam 000,023$euneveR

Fixed cost of printing $20,000

Order inputs Total variable cost of printing mailing $25,000

Average latoT04$redro variable cost of orders $257,600

Variable cost per order $32.20 Total 006,203$tsoc

004,71$tiforP

1-3d Ordering with Quantity Discounts and Demand

Uncertainty

In the following example, we again attempt to find the appropriate level of some activity:

how much of a product to order when customer demand for the product is uncertain. Two

important features of this example are the presence of quantity discounts and the explicit

use of probabilities to model uncertain demand.

Formula Auditing Toolbar

The formula auditing toolbar allows you to see dependents of a selected cell (which cells have formulas that

reference this cell) or precedents of a given cell (which cells are referenced in this cell’s formula). You can

even see dependents or precedents that reside on a different worksheet. In this case, the auditing arrows appear

as dashed lines and point to a small spreadsheet icon. By double-clicking the dashed line, you can see a list of

dependents or precedents on other worksheets. These tools are especially useful for understanding how someone

else’s spreadsheet works.

Excel Tool

2 0 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

EXAMPLE

1.4 ORDERING WITH QUANTITY DISCOUNTS AT SAM’S

BOOKSTORE

Sam’s Bookstore, with many locations across the United States, places orders for all of the latest books and then distributes

them to its individual bookstores. Sam’s needs a model to help it order the appropriate number of any title. For example,

Sam’s plans to order a popular new hardback novel, which it will sell for $30. It can purchase any number of this book

from the publisher, but due to quantity discounts, the unit cost for all books it orders depends on the number ordered. If the

number ordered is less than 1000, the unit cost is $24. After each 1000, the unit cost drops: to $23 for at least 1000 copies;

to $22.25 for at least 2000; to $21.75 for at least 3000; and to $21.30 (the lowest possible unit cost) for at least 4000. For

example, if Sam’s orders 2500 books, its total cost is $22.25(2500) 5 $55,625. Sam’s is uncertain about the demand for

this book—it estimates that demand could be anywhere from 500 to 4500. Also, as with most hardback novels, this one will

eventually come out in paperback. Therefore, if Sam’s has any hardbacks left when the paperback comes out, it will put them

on sale for $10, at which price it believes all leftovers will be sold. How many copies of this hardback novel should Sam’s

order from the publisher?

09953_ch01_ptg01_001-036.indd 20 04/03/19 10:51 PM

Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

1-3 Introduction to Spreadsheet Modeling 2 1

Objective

To learn how to build in complex logic with IF formulas, to get help about Excel functions, to learn how to use lookup

functions, to see how two-way data tables provide answers to more extensive what-if questions, and to learn about Excel’s

SUMPRODUCT function.

Solution

The profit model appears in Figure 1.17. (See the file Quantity Discounts Finished.xlsx.) The order quantity and demand

in the Order_quantity and Demand cells are trial values. (Comments in these cells are a reminder of this.) You can enter any

values in these cells to test the logic of the model. The Order_quantity cell is colored red because the company can choose its

value. In contrast, the Demand cell is colored green to indicate that this input value is uncertain and is being treated explicitly as

such. Also, a table is used to indicate the quantity discounts cost structure. You can use the following steps to build the model.

1

A B C D E F G H I J K

Ordering decision with quantity egnaRstnuocsid names used:

2

3

4

5

6

7

8

9

10

11

12

!$ledoM=tsoC B$18

ytitnauQstupnI discount structure 9$E$:5$D$!ledoM=pukooLtsoC

Unit cost – see table to tAthgir least Unit 21$B$!ledoM=dnameDtsoc

Regular 6$B$!ledoM=ecirp_revotfeL00.42$003$ecirp

Leftover 9$B$!ledoM=ytitnauq_redrO00.32$000101$ecirp

53$J$:53$B$!ledoM=seitilibaborP52.22$0002

Decision variable 91$B$!ledoM=tiforP57.12$0003

Order 5$B$!ledoM=ecirp_ralugeR03.12$00040052ytitnauq

71$B$!ledoM=euneveR

Uncertain quantity Units_sold_at_leftover_price =Model!$B$16

Units_sold_at_regular_price =Model!$B$150002dnameD

13

14

15

16

17

18

19

Profit model

Units sold at regular price 2000

Units sold at leftover price 500

000,56$euneveR

526,55$tsoC

573,9$tiforP

Figure 1.17 Sam’s Profit Model

1. Inputs and range names. Enter all inputs and name the ranges as indicated. The Create from Selection shortcut was used

to name all ranges except for CostLookup and Probabilities. For these latter two, you can select the ranges and enter the

names in the Name Box—the “manual” method.

2. Revenues. The company can sell only what it has, and it sells any leftovers at the discounted sale price. Therefore, enter

the following formulas in cells B15, B16, and B17:

5MIN(Order_quantity,Demand)

5IF(Order_quantity>Demand, Order_quantity-Demand,0)

5Units_sold_at_regular_price*Regular_price1Units_sold_at_leftover_price*Leftover_price

The logic in the first two of these cells is necessary to account correctly for the cases when the order quantity is greater

than demand and when it is less than or equal to demand. You could use the following equivalent alternative to the IF function

in cell B16:

5 MAX(Order_quantity-Demand,0)

fx Button and Function Library Group

To learn more about an Excel function, click the fx button next to the

Formula bar. This is called the Insert Function button, although some people call it the Function Wizard. If there

is already a function, such as an IF function, in a cell and you then click the fx button, you will get help on

this function. If you select an empty cell and then click the fx button, you can choose a function to get help on.

(The same help is available from the Function Library group on the Formulas ribbon.)

Excel Tool The fx button in Excel for Mac opens a Formula Builder pane to

the right, but the functionality is

essentially the same as in Excel

for Windows.

09953_ch01_ptg01_001-036.indd 21 04/03/19 10:51 PM

Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

2 2 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

3. Total ordering cost. Depending on the order quantity, you can find the appropriate unit cost from the unit cost table and

multiply it by the order quantity to obtain the total ordering cost. This can be accomplished with a complex nested IF for-

mula, but a better way is to use the VLOOKUP function. Specifically, enter the formula

5VLOOKUP(Order_quantity,CostLookup,2)*Order_quantity

in cell B18. The VLOOKUP part of this formula says to compare the order quantity to the first (leftmost) column of the

table in the CostLookup range and return the corresponding value in the second column (because the third argument is 2).

VLOOKUP

The VLOOKUP function is one of Excel’s most useful functions. To use it, first create a vertical lookup table,

with values to use for comparison listed in the left column of the table and corresponding output values in as many

columns to the right as you like. (See the CostLookup range in Figure 1.17 for an example.) Then the VLOOKUP

function takes three or four arguments: (1) the value you want to compare to the values in the left column of the

table; (2) the lookup table range; (3) the index of the column you want the returned value to come from, where

the index of the left column is 1, the index of the next column is 2, and so on; and optionally (4) TRUE (for an

approximate match, the default) or FALSE (for an exact match). If you omit the last argument, the values in the

left column of the table must be entered in ascending order. (See online help for more details.) If the last argument

is TRUE or is omitted, Excel scans down the leftmost column of the table and finds the last entry less than or

equal to the first argument. (In this sense, it finds an approximate match.) There is also an HLOOKUP function

that works exactly the same way, except that the lookup table is arranged in rows, not columns.

Excel Function

4. Profit. Calculate the profit with the formula

5Revenue-Cost

Two-Way Data Table

The next step is to create a two-way data table for profit as a function of the order quantity and

demand (see Figure 1.18). To create this table, first enter a link to the profit with the formula

5Profit in cell A22, and enter possible order quantities and possible demands in column A and

row 22, respectively. (We used the same values for both order quantity and demand, from 500 to

4500 in increments of 500. This is not necessary—the demand could change in increments of 100

or even 1—but it is reasonable. Perhaps Sam’s is required by the publisher to order in multiples of

500.) Then select Data Table from the What-If Analysis dropdown list on the Data ribbon, and enter

the Demand cell as the Row Input cell and the Order_quantity cell as the Column Input cell.

A two-way data table

allows you to see how

a single output varies

as two inputs vary

simultaneously.

21

A B C D E F G H I J

Data table of profit as a function of order quantity (along side) and demand (along top)

22

23

24

25

26

27

28

29

30

$9,375 500 1000 1500 2000 2500 3000 3500 4000 4500

500 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000

1000 -$3,000 $7,000 $7,000 $7,000 $7,000 $7,000 $7,000 $7,000 $7,000

1500 -$9,500 $500 $10,500 $10,500 $10,500 $10,500 $10,500 $10,500 $10,500

2000 -$14,500 -$4,500 $5,500 $15,500 $15,500 $15,500 $15,500 $15,500 $15,500

2500 -$20,625 -$10,625 -$625 $9,375 $19,375 $19,375 $19,375 $19,375 $19,375

3000 -$25,250 -$15,250 -$5,250 $4,750 $14,750 $24,750 $24,750 $24,750 $24,750

3500 -$31,125 -$21,125 -$11,125 -$1,125 $8,875 $18,875 $28,875 $28,875 $28,875

4000 -$35,200 -$25,200 -$15,200 -$5,200 $4,800 $14,800 $24,800 $34,800 $34,800

31 4500 -$40,850 -$30,850 -$20,850 -$10,850 -$850 $9,150 $19,150 $29,150 $39,150

Figure 1.18 Profit as a Function of Order Quantity and Demand

09953_ch01_ptg01_001-036.indd 22 04/03/19 10:51 PM

Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

1-3 Introduction to Spreadsheet Modeling 2 3

Two-Way Data Table

A two-way data table allows you to see how a single output cell varies as you vary two input cells. Unlike a one-

way data table, only a single output cell can be used. To create this type of table, enter a reference to the output

cell in the top-left corner of the table, enter possible values of the two inputs below and to the right of this corner

cell, and select the entire table. Then select Data Table from the What-If Analysis dropdown on the Data ribbon,

and enter references to the cells where the original two input variables live. The Row Input cell corresponds to

the values along the top row of the table, and the Column Input cell corresponds to the values along the left-most

column of the table. When you click OK, Excel substitutes each pair of input values into these two input cells,

recalculates the spreadsheet, and enters the corresponding output value in the table.

Excel Tool

SUMPRODUCT

The SUMPRODUCT function takes two range arguments, which must be exactly the same size and

shape, and it sums the products of the corresponding values in these two ranges. For example, the formula

5SUMPRODUCT(A10:B11,E12:F13) is a shortcut for a formula involving the sum of four products:

5A10*E12 1A11*E13 1B10*F12 1B11*F13. This is an extremely useful function, especially when the rang-

es involved are large, and it is used repeatedly throughout the book. (Actually, the …