Chapter 2 Theory With Examples

Chapter 2
Introduction to Spreadsheet Modeling

Introduction
• This book is all about spreadsheet modeling.
– By the time you are finished, you will have seen some
reasonably complex—and realistic—models.
– Many of you will also be transformed into Excel “power”
users.
• This chapter provides an introduction to Excel
modeling and illustrates some interesting and
relatively simple models.
• The chapter also covers the modeling process and
includes some of the less well known, but
particularly helpful, Excel tools that are available.
Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning
Thomson/South-Western © 2012
2007 ©
Basic spreadsheet modeling:
Concepts 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.
• 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.
2007 ©
continued
• After the outputs are obtained, you can proceed in
several directions.
– You might want to perform a sensitivity analysis to see how
one or more outputs change as selected inputs or decision
variables change.
– You might want to find the values of the decision
variable(s) that minimize or maximize a particular output,
possibly subject to certain constraints.
– You might also want to create charts that show graphically
how certain parameters of the model are related.
– These operations are illustrated with several examples in
this chapter.
2007 ©
continued
• You should construct your models with readability
in mind, especially if the models are shared with
others.
• Features that improve readability include:
– 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
– Use of range names
2007 ©
continued
• Readability features continued:
– Use of boldface, italics, larger font size, coloring,
indentation, and other formatting features
– Use of cell comments
– Use of text boxes for assumptions and explanations
• The formulas and logic in any spreadsheet model must be
correct.
• Much of the power of spreadsheets derives from their
flexibility.
• Plan ahead before diving in, and if your plan doesn’t look
good after you start filling in the spreadsheet, revise your
plan.
2007 ©
Example 2.1:
TShirt Sales Finished.xlsx
• Objective: To build a spreadsheet model in a series
of stages, all stages being correct but each stage
being more readable and flexible than the previous
stages.
• Background information:
– Randy Kitchell is a NCAA t-shirt vendor. The fixed cost of any order
is $750, the variable cost is $8 per shirt.
– Randy’s selling price is $18 per shirt, until a week after the
tournament when it will drop to $6 apiece. The expected demand at
full price is 1500 shirts.
– He is thinking about ordering 1450 t-shirts, but he wants to build a
spreadsheet model that will let him experiment with the uncertain
demand and his order quantity.
2007 ©
Example 2.1 continued:
Building a model
• Solution: The logic behind the model is simple. An
Excel IF function will be used.
• In this base model the profit is calculated with the

formula
=-750-8*B4+IF(B3>B4,18*B4,18*B3+6*(B4-B3))
• This model is entirely correct, but it is not very
readable or flexible.
2007 ©
Building a model
• The formula can be rewritten to be more flexible.
=-B3-B4*B9+IF(B8>B9,10*B8+B6*(B9-B8))
• It can be made more readable by using range
names. The formula would then read
=-Fixed_order_cost-Variable_cost*Order + IF(Demand >
Order, Selling_price*Order, Selling_
Price*Demand+Discount_Price* (Order-Demand)

2007 ©
Building a model
• Randy might like to have profit broken down into
various costs and revenues, rather one single
profit cell. The profit formula would be
= -(B12+B13)+(B15+B16).
• Range names could be used for these
intermediate output cells, but it is probably more
work than it is worth.
• Labels and/or color coding can help a lot with
readability.

2007 ©
Building a model
• The model on this slide is still not the last word on this
example.
– But the model on previous slide is now much more readable
and flexible than the original model.
– Data tables could be used to see how sensitive profit is to the
inputs, the demand, and the order quantity, and charts to
show any numerical results graphically.
2007 ©
Cost projections
• In the next 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.

2007 ©
Example 2.2:
Background information
• The Woodworks Company knows that wood prices
and labor costs are likely to increase in the future,
and it would like to project its costs of
manufacturing the bookshelves into the future.
• Build a spreadsheet model that allows 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 bookshelves will vary in the next few years.

2007 ©
Objectives
• Business objectives: To build a model that allows
Woodworks to see, numerically and graphically,
how its costs of manufacturing bookshelves
increase in the future and to allow the company to
answer what-if questions with this model.
• Excel objectives : To learn good spreadsheet
practices, to enable copying formulas with the
careful use of relative and absolute addresses,
and to create line charts from multiple series of
data.
2007 ©
Planning the model
• The reasoning behind the model is straightforward.

• First project the unit costs for wood and labor into
the future. Then for any year, multiply the unit
costs by the required numbers of board-feet and
labor hours per bookshelf.
• Finally, add the wood ad labor costs to obtain the
total cost of a bookshelf.
2007 ©
Bookshelf Costs.xlsx
• The completed spreadsheet model is shown.

2007 ©
Developing the model
• Develop the model with the following steps.
1. Inputs: Enter the inputs into the upper left corner of a
worksheet. These can be referred to later with Excel
formulas.
2. Design output table: Think ahead of time how you want
to structure your outputs. The important point is that you
should have some logical design in mind before diving in.
3. Projected unit costs of wood: It is important to have a
strategy in mind before you enter the formulas. You
should design your spreadsheet so that you can enter a
single formula and then copy it whenever possible.
• For example: enter the formula =B9 in cell B19 and copy it to
cell C19. Then enter the general formula =B19*(1+B$10) in cell
B20 and copy it to the range B20:C25.
2007 ©
4. Projected unit labor costs: To calculate projected
hourly labor costs, enter the formula =B13 in cell D19.
Then enter the formula =D19*(1+B$14) in cell D20 and copy
it down to column D.
5. Projected bookshelf costs: With careful use of
absolute and relative addresses, enter a single formula for
these costs – for all years and for both types of wood. To
do this, enter the formula =B$5*B19+B$6*$D19 in cell E19
and copy it to the range E19:F25.
6. Chart: Highlight the range E18:F25 (including labels) and
click on Excel’s Chart Wizard button. This leads you
through a sequence of steps. You should experiment with
the possibilities.
2007 ©
• The model can be used to answer any what-if questions
Woodworks might want to ask.
• It has been built in such a way that a manager can enter
any desired values in the input cells, and all of the
outputs, including the chart, will update automatically.
• Burying input numbers inside Excel formulas is a bad
practice.

2007 ©
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.
• We discuss a typical breakeven analysis in the
following example.

2007 ©
Example 2.3:
• The Quality Sweaters Company sells hand-knit
sweaters. The company is planning to print a
catalog of its products and undertake a direct mail
campaign.
• The cost of printing the brochure is $20,000 plus
$0.10 a catalog. The cost of mailing each catalog
is $0.15. In addition, the company will include
direct reply envelopes in it’s mailings. It incurs
$0.20 in extra cost for each direct mail envelope
that is used by a respondent.
2007 ©
• The average size of a customer order is $40, and the
company’s variable cost per order averages around 80% of
the order’s value ($32).
• The company plans to mail 100,000 catalogs. It wants to
develop a spreadsheet model to answer the following
questions:
– How does a change in the response rate affect profit?
– For what response rate does a company break even?
– If the company estimates a response rate of 3%, should it proceed
with the mailing?
– How does the presence of uncertainty affect the usefulness of the
model?

2007 ©
Breakeven Analysis.xlsx
• Business objectives :To create a model to determine the
company’s profit and to see how sensitive the profit is to the
response rate from the mailing.
• Excel objectives: 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 the
auditing toolbar.
• This file contains the completed model.
• The key variables appear below.

2007 ©
Example 2.3 continued
• Note the clear layout of the model
– The input cells are colored blue and separated from the
outputs.
– There are boldfaced headings, several headings are
indented.
– Numbers are formatted appropriately.
– A list to the right spell out all the range names used.
– Following the convention used throughout the book, the
decision variable (number mailed) is colored red, and
the bottom-line output (profit) is colored gray.

2007 ©
Example 2.3 continued

2007 ©
To create this model, proceed through the following
steps.
1. Heading and range names. Obviously we have a lot
of cells, more than you might want to enter, but you will
see their value when we start entering formulas.
2. Values of input variables and the decision variable.
The values in the blue cells are all given in the
statement of the problem. Enter these values and
format them appropriately.

2007 ©
3. Model the responses. Enter any reasonable values such as 8%
in the Response_rate cell – you will perform sensitivity on this
value later on – and enter the formula
=Number_mailed*Response_rate in cell E5.
4. Model the revenue, costs and profits. Enter the formula
=Number_of_responses*Average_order in cell E8.
Enter the formula =Fixed_cost_of_printing

=Variable_cost_of_printing_mailing*Number_mailed and
=Number_of_responses*Variable_cost_per_order in the Cost cells
(E9, E10, E11).
Enter the formula =SUM(E9:E11) in the cell E12, and enter the
formula =Total_revenue-Total_cost in cell E13.

2007 ©
• Now that a basic model has been created, we can
answer the questions posed by the company.
• For question 1, we form a data table to show how
profit varies with the response rate. The table is
shown here.

2007 ©
• First, enter a sequence of trial values of the
response rate in column A, and enter a “link” to
profit in cell B17 with the formula =Profit.
• Finally, highlight the entire table range, A17:B27,
and select Data Table from the What-if Analysis
dropdown in the data ribbon to bring up the dialog
box shown here.

2007 ©
• It should be filled in as shown to indicate that the
only input is Response_rate, listed along a column.
• When you click OK, Excel substitutes each
response rate value in column A in to the
Response_rate cell, recalculates profit, and reports
it in the data table.

2007 ©
• Clearly, profit increases in a linear manner as
response rate varies. More specifically, a 1%
increase in the response rate always increased
profit by $7800.
• Here is the reasoning.
– Each 1% in response rate results in 100,000 x
0.01=1000 more orders.
– Each order yields an average revenue of $40 but incurs
a variable cost of $40 x 80% = $32 and a $0.20
envelope cost.
– The net gain is $7.80 per order, or $7800 for 1000
orders.
2007 ©
Using Goal Seek
• From the data table, we see that profit goes from
negative to positive when the response rate is
somewhere between 5% and 6%.
• Question 2 asks for the exact breakeven point.
This could be found with trial and error but is easy
with Excel’s Goal Seek tool. Goal Seek is used to
solve a single equation with a single unknown.
• Here the equation is Profit=0, and the single
unknown is the response rate.

2007 ©
Using Goal Seek continued
• In Excel terminology, the unknown is called the
changing cell because we are allowed to change it
to make the equation true.
• To implement Goal Seek, select Goal Seek from
the What-If Analysis dropdown in the Data ribbon
and fill in the resulting dialog box as shown below.

2007 ©
Answering the questions
• After clicking on OK, the Response_rate and Profit cells
have values 5.77% and $0. In words, if the response rate is
5.77%, Great Threads breaks even. If the response rate is
greater then 5.77%, the company makes money; otherwise,
it loses money.
• Question 3 asks if the company should proceed with the
mailing if the response rate is only 3%. From the data table,
the apparent answer is “no” because profit is negative, a
loss. However, like many U.S. companies, we are taking
the short term view with this reasoning.

2007 ©
• We should realize that many customers who respond to
direct mail will reorder in the future. The company makes
$7.80 per order. If each of the respondents ordered two or
more times, say, the company would earn 3000 x $7.80 X 2
= $46,800 more than appears in the model, and profit
would then be positive.
• The moral is that managers must look at long-term impact
of our decisions. However, if we want to incorporate the
long term explicitly into the model, we must build a more
complex model.

2007 ©
• Finally, question 4 asks about the impact of uncertainty in
the model. We would be kidding ourselves to think that all
model inputs are known with certainty.
• For example, the size of an order is not always $40 – it
might be, say, from $10 to $100. When there is a high
degree of uncertainty about model inputs, it makes little
sense to talk about the profit level or the breakeven
response rate.
• It makes more sense to talk about the probability that profit
will have a certain value or the probability that the company
will break even.

2007 ©
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.
• Except for these features, the problem is very
similar to the one discussed in Example 2.1.

2007 ©
Example 2.4:
• Sam’s Bookstore, with many locations across
the United States, places orders for all of the
latest books and then distributes them to
individual bookstores.
• Sam’s needs a model to help it order the
appropriate number of any title.
• For example, it 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.
2007 ©
• Specifically
– If the number ordered is less than 1000, the unit cost is
$24
– For at least 1000 copies the price is $23
– For at least 2000 copies the price is $22.25
• Sam’s is very uncertain about the demand for this
book – it estimates that demand could be
anywhere from 500-4500.

2007 ©
• 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?

2007 ©
Quantity Discounts.xlsx
• Business objectives: To create a model to determine the
company’s profit, given fixed values of demand and the
order quantity, and then to model the demand uncertainty
explicitly and to choose the expected profit-maximizing
order quantity.
• Excel objectives: To learn how to build in complex logic
with IF formulas, to get online help about Excel functions
with the fx button, to learn how to use lookup functions, to
see how two-way data tables allow you to answer more
extensive what-if questions, and to learn about Excel’s
SUMPRODUCT function.

2007 ©
The solution
• First we show how any combination of demand
and order quantity determines the number of units
sold, both at the regular and the leftover price
• Second, we calculate the total ordering cost for
any order quantity
• Finally, we can model the uncertainty of demand
explicitly and then choose the “best” order quantity

2007 ©
The solution
• The profit model shown on the next slide can be
found in the file.
• Note that the Order_quantity and Demand cells are
“trial” values. We can put any values in these cells,
just to test the logic of the model.
• Also note how we have used a table to indicate
quantity discounts for ordering. After entering the
inputs and trial values of order quantity and
demand, use the following steps to complete the
model.
2007 ©
The Profit model

2007 ©
1. Inputs and range names. Enter all inputs and
name the ranges as indicated in columns H and I.
2. Revenues. Sam’s can sell only what it has, and it
will sell any leftovers at the sale price. Therefore
enter the formulas
=MIN(Order_quantity,Demand)
=IF(Order_quantity>Demand, Order_quantity-Demand,0),
=Units_sold_at_regular price*Regular_price
+Units_sold_at_leftover price*Leftover_price
in cells B15, B16 and B17.
Note that you can use the following equivalent alternative
to the IF function in B16:
=MAX(Order_quantity-Demand, 0)
2007 ©
3. Total ordering cost. Depending on order quantity, we
find the appropriate unit cost from the unit cost table
and multiply it by the order quantity to obtain the total
ordering cost. This could be accomplished with a
complex nested IF formula, but a much better way is to
use the VLOOKUP function. Specifically, enter the
formula
=VLOOKUP(Order_quantity,CostLookup,2)*Order_quantity
in the cell B18 (Cost cell).
4. Profit. Calculate the profit with the formula
=Revenue-Cost

2007 ©
Creating a Data Table
• The next step is to create a two-way data table for
profit as a function of the order quantity and
demand. The data table is shown here.

2007 ©
Creating a data table
• This table shows that profit depends heavily on
both order quantity and demand, and how higher
demands lead to larger profits.
• But is it still unclear which order quantity Sam’s
should select.
• Remember that Sam’s has complete control over
the order quantity, but it also has no direct control
over demand.

2007 ©
Answering the question
• The ordering decision depends not only on which
demands are possible, but on which demands are
likely to occur.
• The usual way to express this information is with a
set of probabilities that sum to 1.
• Sam’s would need to estimate these probabilities,
possibly on the basis of other similar novels it has
sold in the past.
• These probabilities can be used to find an
expected profit for each quantity.

2007 ©

2007 ©
• The most likely demands are 2000 and 2500, with
other values on either side less likely.
• These probabilities can be used to find an
expected profit for each order quantity.
• This expected profit is a weighted average of the
profits in any row in the data table, using the
probabilities as the weights. The easiest way to do
this is to enter the formula
=SUMPRODUCT(B23:J23,Probabilities)
in cell B38 and copy it down to cell B46.

2007 ©
• The largest of the expected profits, $12,250,
corresponds to an order quantity of 2000, so we
would recommend that Sam’s order 2000 copies of
the book.
• This does not guarantee that Sam’s will make a
profit of $12,250 – the actual profit depends on the
eventual demand – but it represents a reasonable
way to proceed in the face of uncertain demand.

2007 ©
Estimating the relationship
between price and demand
• The following example illustrates a very important
modeling concept: estimating relationships
between variables by curve fitting.
• You will study this topic in much more depth in the
discussion of regression in Chapter 14, but the
ideas can be illustrated at a relatively low level by
taking advantage of some of Excel’s useful
features.

2007 ©
Example 2.5:
• The Links Company sells its golf clubs at golf
outlet stores throughout the United States.
• The company knows that demand for its clubs
varies considerably with price.
• In fact, the price has varied over the past 12
months, and the demand for each price level has
been observed.

2007 ©
Golf Club Demand.xlsx
• The data are in the Data Sheet of this file.
• For example, during the last month, when the price
was $390, 6800 sets of clubs were sold. A sample
of the data can be seen here.

2007 ©
The questions
• The company would like to estimate the
relationship between demand and price and then
use this estimated relationship to answer the
following questions.
1. Assuming the unit cost of producing a set of clubs is
$250 and the price must be a multiple of $10, what
price should Links charge to maximize its profit?
2. How does the optimal price depend on the unit cost of
producing a set of clubs?
3. Is the model an accurate representation of reality?

2007 ©
• This example is divided into two parts: estimating
the relationship between price and demand, and
creating the profit model.
• A scatterplot of demand versus price appears
here.

2007 ©
• Obviously demand decreases as price increases,
but we want to be specifically quantify this
relationship.
• Therefore, after creating this chart, select More
Trendline Options from the Trendline Dropdown on
the Chart Tools Layout ribbon to bring up the
dialog box shown on the next slide
• This allows you to superimpose several different
curves (including a straight line) on the scatterplot

2007 ©
• We will consider the linear,
power and exponential
curves, defined by the
general equations where y
and x, a general output and a
general input, correspond to
demand and price.
– Linear: y = a+bx
– Power: y = axb
– Exponential: y = aebx

2007 ©
Estimating the relationship:
The functions
• The three functions have some general properties
that should be noted because of their widespread
applicability.
• The linear function is the easiest.
– Its graph is a straight line.
– When x changes by 1 unit, y change by b units.
– The constant a is called the
intercept, and b is called
the slope

2007 ©
The functions continued
• The power function is a curve except in the special case
where the exponent b is 1 (then it is a straight line). The
shape of the curve depends primarily on the exponent b.
– If b >1, y increases at an increasing rate as x increases.
– If 0 < b < 1, y increases, but at a decreasing rate, as x increases.
– If b < 0, y decreases as x increases.
• An important property of the power curve is that when x
changes by 1%, y changes by a constant percentage, and
this percentage is approximately equal to b%.
– For example, if y = 100x-2.35, then every 1% increase in x leads to an
approximate 2.35% decrease in y.

2007 ©
The functions continued
• The exponential function also represents a curve whose
shape depends primarily on the constant b in the exponent.
– If b > 0, y increases as x increases.
– If b < 0, y decreases as x increases.
• An important property of the exponential function is that if x
changes by 1 unit, y changes by a constant percentage,
and this percentage is approximately equal to 100 x b%.
• Another important note about the equation is that it
contains e, the special number 2.7182…. In Excel, e to any
power can be calculated by the EXP function.

2007 ©
continued
• If we superimpose any one of these curves on the
scatterplot for demand versus price, Excel will
choose the best fitting curve of that type.
• Better yet if we check the Display Equation on
Chart option, we see the equation of this best-
fitting curve.
• Doing this for each type of curve we obtain the
results in the following figures.

2007 ©
Best-fitting power curve

2007 ©
Best-fitting exponential curve

2007 ©
continued
• Each of these curves provides the best-fitting
member of its “family” to the demand/price
data, but which of these three is best overall?
• We answer this question by finding the mean
absolute percentage error (MAPE) for each
of the three curves.
• To do this, for any price in the data set and
any of the three curves, we first predict
demands by substituting the given price into
the equation for the curve.
2007 ©
continued
• The predicted demand will typically not be the
same as the observed demand, so we can
calculate the absolute percentage error (APE) with
the general formula
Observed demand  Predicted demand
APE 
Observed demand
• Then we average these values of the APE for any

curve to get its MAPE. We will consider the curve
with the smallest MAPE as the best fit overall.
• The calculations appear on the following slide.
2007 ©
The spreadsheet model

2007 ©
• After manually entering the parameters of the
equations from the scatterplots into column B,
proceed as follows.
1. Predicted demands. Substitute observed prices into
the linear, power, and exponential functions to obtain
the predicted demands in columns E, F, and G.
Specifically, enter the formulas
=$B$19+$B$20*B4,
=$B$22*B4^$B$23, and
=$B$25*EXP($B$26*B4) in cells E19, F19, and G19,
and copy them down their respective columns.
2007 ©
2. Average percentage errors. Apply equation 2.1 to
calculate APEs in column H, I and J. Specifically, enter the
general formula
=ABS($C4-E19)/$C4
in cell H19 and copy it to the range H19:J30.
3. MAPE. Average the APEs in each column with the
AVERAGE function to get the MAPEs in row 32.
– Evidently the power curve provides the best fit, with
MAPE of 5.88%. In other words, its predictions are off,
on average, by 5.88%.
– This power curve predicts that each 1% increase in price
leads to an approximate 1.9% decrease in demand.

2007 ©
The profit model
• The profit model will use the best-fitting power
curve to predict demand from price.
• The model appears below. Note there is now one
input variable and one decision variable.

2007 ©
The profit model
• The profit model is straightforward.
1. Predicted Demand. Calculate the predicted
demand from the power function with the formula
=B4*B11^B5
2. Revenue, cost, profits. Enter the formulas in
B15, B16, and B17:
=B11*B14, =B8*B14, and =B15-B16.
The assumption here is that the company produces
exactly enough sets of clubs to meet customer
demand.
2007 ©
• We can answer the company’s questions.
• To see which price maximizes profit, we build
the data table shown on the next slide.
• The column input cell is B11 and the “linking”
formula in cell B25 is =B17.
• The corresponding chart shows that profit first
increases, then decreases.
• We can find the maximum profit and
corresponding price in at least three ways.

2007 ©
• First, we can attempt to read them off the chart.
• Second, we can scan down the data table for the
maximum profit, which we indicate in the figure.
• The third method uses some of Excel’s more
powerful features.
– One of these tools is conditional formatting
– It was completely revised in Excel 2007, and is not only
more prominent (on the Home ribbon), but is much
easier to use

2007 ©

2007 ©
• To color the maximum profit, select the range of
profits, B26:B75, select the Conditional Formatting
dropdown, then Top/Bottom Rules, and then Top
10 Items to bring the following dialog box:
• By asking for the top 1 item, we automatically color

the maximum value in the range.

2007 ©
• What about the corresponding best price in cell B21? You
can enter it manually or you can enter the formula
=INDEX(A26:A75,MATCH(B20:B75,0),1)
• This formulas uses two Excel functions, MATCH and
INDEX.
– MATCH compares the first argument to the range specified in the
second argument and returns the index of the cell where a match
appears.
– The INDEX function is called effectively as INDEX(A26:A75,28,1).

2007 ©
Sensitivity to variable cost
• We can answer question 2: How does the best
price change as the unit variable cost changes?
with a two way data table.
• This is a data table with two inputs, one along the
left side and the other across the top row and a
single output.
• The two inputs for our problem are unit variable
cost and unit price, and the single output is profit.
• The following slide shows the top part of the
corresponding data table.

2007 ©
• To develop the table, enter desired inputs in column A and
row 83, enter the “linking” formula =B17 in cell A83,
highlight the entire table, select the Data Table from the
What-If Analysis dropdown, and enter B8 as the Row Input
cell and B11 as the Column Input Cell.
• As before, you can scan the columns of the data for the
maximum profits and enter them (manually) in rows 79 and
80 (or, use Excel features described above)
• Then you can create a chart of maximum profit versus unit
cost. The chart shows that maximum profit decreases, but
at a decreasing rate, as unit cost increases.

2007 ©

2007 ©
Limitations of the model
• Finally, question 3 asks us to step back from all of
these details and evaluate whether the model is
realistic.
• First, there is no real reason why golf club prices
should be restricted to be multiples of $10. We
required this only so we could use a data table to
find the profit-maximizing price.
• Ideally, we would like to have a way to search over
all possible prices to find the profit-maximizing
price.
2007 ©
• Fortunately, Excel’s built-in Solver tool enables us
to accomplish this task fairly easily.
• The problem of finding a profit-maximizing price is
an example of an optimization model.
• A second possible limitation in our model is that
implicit assumption that price is the only factor that
influences demand.
• In reality, other factors such as advertising, the
state of the economy, competitors prices, strength
of competition and promotional expenses also
influence demand.
2007 ©
• A final flaw in our model is that demand might not
equal sales.
• For example, if the actual demand for golf clubs
during a year is 70,000 but the company’s annual
capacity is only 50,000, the company would
observe sales of only 50,000.
• This would cause us to underestimate actual
demand, and our curve fitting method would
produce biased predictions.
• As these comments indicate, most models are not
perfect, but we have to start somewhere!
2007 ©
Decisions involving the time
value of money
• In many business situations, cash flows are
received at different points in time, and a company
must determine a course of action that maximizes
the “value” of cash flows. Here are some
examples:
– Should a company buy a more expensive machine that
lasts for 10 years or a less expensive machine that lasts
for 5 years?
– What level of plant capacity is best for the next 20
years?
– A company must market one of several midsize cars.
Which car should it market?
2007 ©
value of money continued
• To make decisions when cash flows are received
at different points in time, the key concept is that
the later a dollar is received, the less valuable the
dollar is.
• The value of the dollar at some time in the future is
given by the equation:
$1.00 x 1/(1+r) now = $1.00 a year from now
• The value 1/(1+r) is called the discount factor,
and it is always less than 1.

2007 ©
value of money continued
• In general, if money can be invested at annual rate
r compounded each year, then $1 received t years
from now has the same value as 1(1+r)t dollars
received today.
• If you multiply a cash flow received t years from
now by 1(1+r)t to obtain its present value, then the
total of these present values over all years is
called the net present value (NPV) of the cash
flows.
• The rate r (usually called the discount rate) used
by major
Winston/Albright
corporations generally
Practical Management Science, 4e
comes from
South-Western/Cengage
some
Learning © 2012
Thomson/South-Western 2007 ©
version of the capital asset pricing model.
Example 2.6:
• Acron is a large drug company. Acron is trying to
decide whether one of its new drugs, Niagra, is
worth pursuing. Niagra is in the final stages of
development.
• The final cost of development is $9.3 million.
• Acron estimates that the demand for Niagra will
gradually grow and then decline over its useful
lifetime of 20 years.

2007 ©
• The company expects its gross margin to be $1.2
million in year 1, then to increase at an annual rate
of 10% through year 8, and finally to decrease at
an annual rate of 5% through year 20.
• Acron wants to develop a spreadsheet model of its
20 year cash flows, assuming its cash flows, other
than the initial development cost, are incurred at
the ends of the respective years.

2007 ©
• Using an annual discount rate of 12% for the
purpose of calculating NPV, the drug company
wants to answer the following questions:
1. Is the drug worth pursuing, or should Acron abandon it
now and not incur the $9.3 million development cost?
2. How do changes in the model inputs change the
answer to question 1?
3. How realistic is the model?

2007 ©
Objectives
• Business objectives: To develop a model that
calculates the NPV of Acron’s cash flows,to use
this model to determine whether the drug should
be developed further and then marketed, and to
see how sensitive the answer to this question is
to model parameters.
• Excel objectives: To illustrate efficient selection
and copying of large ranges and to learn Excel’s
NPV function.

2007 ©
The model
• The key variables in Acron’s problem appear
below.
• The model is shown on the next slide.
• As with many financial spreadsheet models that
extend over a multiyear period, you enter “typical”
formulas in the first year or two and then copy this
logic across to all years.

2007 ©
The model

2007 ©
Calculating NPV.xlsx
• To create the model, complete the following
steps.
1. Inputs and range names. Enter the input data in the
input (blue cells) section. As usual, note that the range
names for cells B4 though B9 can be created all at
once with the Create from Selection shortcut.
2. Cash flows. Enter the formula =Gross_margin_year_1
in cell B13 for the year 1 gross margin. Then enter the
general formula
=IF(A14<=Increase_through_year,B13*(1+Rate_of_increase),
B13*(1-Rate_of_decrease))
in cell B14 and copy down to cell B32 to calculate the
other yearly gross margins.
2007 ©
The model
3. Net present value. The NPV is based on the sequence
of cash flows in column B. From our general discussion
of NPV, the value in cell B13 should be multiplied by
1/(1+r)1, the value in cell B14 should be multiplied by
1/(1+r)2, and so on, and these quantities should be
summed to obtain the NPV.
Enter the formula
=Development_cost+NPV(Discount_rate,
Gross_margin)
in the NPV cell (B34).
The NPV function takes two arguments: the discount rate

and a range of cash flows.
2007 ©
Making a decision
• If Acron is comfortable with its predictions of future
cash flows, it should continue with the
development and marketing of the drug.
• However, Acron might first want to see how
sensitive NPV is to changes in the sales
predictions.
• One possible sensitivity analysis is shown on the
next slide. Build a one-way data table to see how
the NPV changes when the number of years of
increase (the input in cell B7) changes.

2007 ©
Making a decision
• Another possibility is to see how long and how good years

are.
• To do this, you create a two-way data table as shown on
the next slide, where cell B6 is the row input cell and cell B7
is the column input cell.
• Now you can see that if sales increase through year 6, all
reasonable yearly increases result in a positive NPV.
However, if sales increase only through year 5, then a low
enough yearly increase can produce a negative NPV.

2007 ©
Making a decision
• Acron might want to step back and estimate how
likely these “bad” scenarios are before proceeding
with the drug.

2007 ©
• Probably the major flaw is that it ignores
uncertainty, and future cash flows are highly
uncertain due mainly to uncertain demand for the
drug.
• Of course, there are almost always ways to make
any model more realistic – at the cost of increased
complexity.
• For example, you could model the impact of
competition on Niagara’s profitability. Alternatively,
you could allow Acron to treat its prices as decision
variables.
2007 ©
• However, this might influence the likelihood of
competition entering the market, which would
certainly complicate the model.
• The point is that this model is only a start. When
millions of dollars are at stake, a more thorough
analysis is certainly warranted.

2007 ©
Conclusion
• The examples in this chapter provide a glimpse of
things to come in later chapters.
– You have seen the spreadsheet modeling approach to
realistic business problems, learned how to design
spreadsheet models for readability, and explored some
of Excel’s powerful tools, particularly data tables.
• In addition, at least three important themes have
emerged from these examples:
– relating inputs and decision variables to outputs by
means of appropriate formulas, optimization (for
example, finding a “best” order quantity), and the role of
uncertainty(uncertain response rate or demand).
2007 ©
Summary of key management
science terms

2007 ©
Summary of key Excel terms

2007 ©
Summary of key Excel terms
continued

2007 ©
End of Chapter 2

Chapter 2 Theory With Examples

Uploaded by

Copyright:

Available Formats

Chapter 2 Theory With Examples

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Chapter 2 Theory With Examples

Uploaded by

Copyright:

Available Formats

Chapter 2

Introduction to Spreadsheet Modeling

• In this base model the profit is calculated with the

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

• The reasoning behind the model is straightforward.

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Enter the formula =Fixed_cost_of_printing

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

• Then we average these values of the APE for any

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning

Winston/Albright Practical Management Science, 4e South-Western/Cengage Learning