NEED A PERFECT PAPER? PLACE YOUR FIRST ORDER AND SAVE 15% USING COUPON:

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 E

Click here to Order a Custom answer to this Question from our writers. It’s fast and plagiarism-free.

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

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

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

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    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

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

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

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    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 …

Place your order now for a similar assignment and have exceptional work written by one of our experts, guaranteeing you an A result.

Need an Essay Written?

This sample is available to anyone. If you want a unique paper order it from one of our professional writers.

Get help with your academic paper right away

Quality & Timely Delivery

Free Editing & Plagiarism Check

Security, Privacy & Confidentiality