Transcript Lecture 2 Robert Zimmer Room 6, 25 St James

Lecture 2
Robert Zimmer
Room 6, 25 St James
This course is about building models
and making decisions
► It
is about organising information
► It is about being able to ask what-if
questions
► It is about applying powerful mathematical
models (I might try to teach you some
maths when you aren’t looking but that is
incidental)
Example of a decision:
should I have another beer?
► Organising







Information:
How much money I have
How much money a beer costs
How drunk am I?
Do I have to drive?
How fat am I?
How much do I like the people in the pub?
How much do I like the people at home?
Another Question
► What
is the most money I am prepared to
pay for this drink? That is at what price
does the pleasure of the drink become less
than its price?
Some more questions
► What
is the geometric shape of all the
points at which the pleasure of the beer
exactly matches the pain of the payment?
► How will my pleasure, my weight, and my
mental state compare if instead of a beer I
have chips?
► or do my Java coursework?
► 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.
► Once a model is created there are several
directions in which to proceed.
 Sensitivity analysis to see how one or more
outputs change as selected inputs or decision
variables change.
 Finding the value of a decision variable that
maximizes or minimizes a particular output.
 Create graphs to show graphically how certain
parameters of the model are related.
► Good
spreadsheet modeling practices are
essential.
► Spreadsheet models should be designed with
readability in mind.
► Several features that improve readability
include:
►A
clear logical layout to the overall model
► Separation of different parts of a model
► Clear headings for different sections of the model
► Liberal use of range names
► Liberal use of formatting features
► Liberal use of cell comments
► Liberal use of text boxes for assumptions, lists or
explanations
Example 2.1 – Building a
Model
► Randy
Kitchell is a NCAA t-shirt vendor. The
fixed cost of any order is $750, the variable
cost is $6 per shirt.
► Randy’s selling price is $10 per shirt, until a
week after the tournament when it will drop
to $4 apiece. The expected demand at full
price is 1500 shirts.
► He wants to build a spreadsheet model that
will let him experiment with the uncertain
demand and his order quantity.
► In
this model the profit is calculated with
the formula
Profit = Revenue – Cost
and the Cost = 750 + 6*B4
Revenue
Case 1:
Demand outstrips order (B3 > B4)
In that case everything gets sold for 10
dollars
Revenue is then simply 10*B4
(since B4 is the number ordered)
Revenue
Case 2:You have ordered too many.
That is order (B3) is less than peak demand
Then you can only sell B3 at 10 dollars and
the rest (B4-B3) at 4 dollars
Revenue = 10*B3+4*(B4-B3)
Revenue Formula
Revenue =
IF(B3>B4,10*B4,10*B3+4*(B4-B3))
Profit Formula
Profit =
IF(B3>B4,10*B4,10*B3+4*(B4-B3)) –
(750 + 6* B4)
Adding Flexibility
► We
add flexibility by allowing more things to
vary
Ex. 2.1(cont’d) - Building a Model
► The
formula can be rewritten to be more
flexible.
=-B3B4*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_costVariable_cost*Order + IF(Demand >
Order, Selling_price*Order,
10*Demand+Salvage_value* (OrderDemand)
Ex. 2.1(cont’d) - Building a Model
► We
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.
Ex. 2.1(cont’d) - Building a 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.
Example 2.2 – Cost Projections
► The
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.
► The data can be found in Table 2.1.
► 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.
Ex. 2.2(cont’d) - 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.
Ex. 2.2(cont’d) – The Model
Ex. 2.2(cont’d) – Developing the
Model
► Develop
the model with the following steps.
 Inputs: Enter the inputs into the upper left corner
of a worksheet. These can be referred to later
with Excel formulas.
 Design output table: You need to 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.
 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.
Ex. 2.2(cont’d) – Developing the
Model
►
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.
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.
Developing the Model -continued
6. Chart: Highlight the range E19:F25 and click on
Excel’s Chart Wizard button. This leads you
through a sequence of steps. You should
experiment with the possibilities.
The model can be used to answer any whatif questions Woodworks might want to ask.
► The model 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 bad practice.
►
2.4 Breakeven Analysis
► Many
business problems require us to find
the appropriate level of some activity.
► This might be the level that maximizes
profit, or it might be the level that allows a
company to break even – no profit, no loss.
Example 2.3 - Breakeven Analysis
► The
Great Threads Company is planning to
print a brochure 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.
► 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.
Ex. 2.3(cont’d) - Breakeven
Analysis
► 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?
Ex. 2.3(cont’d) - Planning the Model
►A
single “bottom line” output variable, in this
case profit, is of most concern.
► The logic for converting inputs and the
decision variable into outputs is quite
straightforward. Then it must be investigated
how the response rate affects the profit with
a sensitivity analysis.
Ex. 2.3(cont’d) - Developing the
Model
►
To create this model, proceed through the
following steps.
1. Heading and range names: Be cautious not to
go overboard with range names.
2. Enter input values: Some of the values have
been combined in the statement of the problem.
To document this process, enter comments in a
few cells. Inserting comments in cells is a great
way to document your spreadsheet models
without making it too cluttered.
3. Model the responses: Enter any reasonable
value, such as 8%, in the Respone_rate cell.
=Number_mailed*Response_rate in cell E5
Ex. 2.3(cont’d) - Developing the
Model
4. Model the revenues, costs and profits:
►
►
►
Enter the formula
=Number_of_responses*Average_order in the 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 cells E9, E10, and E11.
Enter the formula =SUM(E9:E11) in the cell E12, and enter
the formula =Total_revenue-Total_cost in the cell E13.
Ex. 2.3(cont’d) - Data Table
►A
a one-way data table is formed to show
how profit varies with the response rate.
► Data tables are called “what-if” tables. They
illustrate what happens to selected outputs if
selected inputs change.
► From the data table it can be seen that profit
changes from negative to positive when the
response rate is somewhere between 5% and
6%.
► This could be found by trial and error, but it is
easier to find with Excel’s Goal Seek tool.
Ex. 2.3(cont’d) - Goal Seek
seek is useful for solving a single
equation in a single unknown.
► The unknown is called the changing cell
because it is allowed to be changed to make
the equation true.
► Select the Tools/Goal Seek menu item and
fill in the resulting dialog box.
► If the response rate is 5.77%, Great
Threads breaks even.
► Goal
Ex. 2.3(cont’d) - Limitations of the
Model
► Question
3 asks whether the company should
proceed with the mailing if the response rate is
only 3%.
► The apparent answer is “no” because profit is
negative. This reasoning is taking the short-term
view.
► To consider the long term impact of our decisions
the model must incorporate the long term
explicitly into the model. To do this a more
complex model must be built.
Ex. 2.3(cont’d) - Limitations of
the Model
► Question
4 asks about the impact of
uncertainty in the model.
► 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.
2.6 Decisions Involving the Time
Value of Money
► 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.
► The later a dollar is received, the less valuable the
dollar is. This is useful in making decisions.
$1.00 X 1/(1+r) now = $1.00 a year from now
► The value 1/(1+r) in the above equation is called
the discount factor.
► The
quantity on the left is called the present
value of $1.00 received a year from now.
► 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 – that is, the
$1 is discounted by the discount factor raised
to the t power.
► By multiplying a cash flow received t years
from now by 1/(1+r)t (its present value), then
the total value of all cash flows over all years
is called the net present value (NPV) of
our cash flows.
rate r (usually called the discount
rate) used by major corporations generally
comes from some version of the capital
asset pricing model.
► The discount factor is 1 divided by 1 plus
the discount rate.
► The NPV is the sum of all discounted cash
flows.
► The