Transcript Document

• Modelling
– Mathematical Models
– Put into Excel Spreadsheets
Modelling ideas
• Input Variables
• Decision Variables
• Targets
• First Maths
– Profit = Revenue - Cost
– Linear Cost:
• Cost = Fixed Cost + N* Variable Cost
– Linear Revenue
• Revenue = N* Price
– Piecewise Linear Revenue
• Revenue = N1*Price1 + N2*Price2 + …
(e.g. when you can sell some t-shirts at a
premium)
• Projected Costs
• Dealing with uncertainty
– (actually still to come in lecture)
• Projections using falling value of money
– 100 pounds now is worth more than the
promise of 100 pounds next year
• Break-even analyses
Spreadsheets
•
•
•
•
•
•
Absolute and relative addresses
If statements
Range names
Making one-way tables
Two-way tables
Goal Seek
Uncertainty
• Sam’s Bookshop
• Sam does not know how many books to
order.
– They are cheaper the more he orders
– He has to sell them cheaply if he does not sell
them quickly
– He does not know how many he can sell
• They are cheaper the more he orders
• First 1000 books are 24 dollars each; next
1000 23 etc.
• Use a Vertical Lookup Table
Ordering decision with quantity discounts
Inputs
Unit cost - see table to right
Regular price
Leftover price
$30
$10
Decision variable
Order quantity
2500
Uncertain quantity
Demand
2000
Quantity discount structure
At least Unit cost
0
$24.00
1000
$23.00
2000
$22.25
3000
$21.75
4000
$21.30
Profit model
Units sold at regular price
Units sold at leftover price
Revenue
Cost
Profit
Data table of profit as a function of order quantity (along side) and demand (along top)
Uncertain Sales
Model of expected demands
Demand
Probability
500
0.025
1000
0.05
1500
0.15
2000
0.25
2500
0.25
3000
0.15
Expected Value
• Suppose somebody throws a dice and
gives you a pound for each dot on the side
that comes up
• E.g. if a 4 is thrown you get 4 pounds
• How much money can you expect to get
on average?
• Well there is a 1 in 6 chance of getting one
pound + a 1 in 6 chance of getting 2
pounds etc.
• 1/6 (1) + 1/6 (2) + … + 1/6 (6)
= 3.5 pounds
Back to Sam
Want to maximise Profit
Profit =Revenue-Cost
Revenue
=Units_sold_at_regular_price*Regular_price+
Units_sold_at_leftover_price*Leftover_price
• Units_sold_at_leftover_price =
Order - Units_sold_at_regular_price
Units_sold_at_regular_price =
MIN(Order_quantity,Demand)
= IF(Order_quantity < Demand,
Order_quantity,Demand)
• Cost =VLOOKUP
(Order_quantity,CostLookup,2)
*
Order_quantity
Make a Table
Data table of profit as a function of order quantity (along side) and demand
$65,000
500
1000
1500
2000
500
$15,000 $15,000 $15,000 $15,000
1000
$20,000 $30,000 $30,000 $30,000
1500
$25,000 $35,000 $45,000 $45,000
2000
$30,000 $40,000 $50,000 $60,000
2500
$35,000 $45,000 $55,000 $65,000
3000
$40,000 $50,000 $60,000 $70,000
3500
$45,000 $55,000 $65,000 $75,000
4000
$50,000 $60,000 $70,000 $80,000
4500
$55,000 $65,000 $75,000 $85,000
(along top)
2500
3000
$15,000 $15,000
$30,000 $30,000
$45,000 $45,000
$60,000 $60,000
$75,000 $75,000
$80,000 $90,000
$85,000 $95,000
$90,000 $100,000
$95,000 $105,000
3500
4000
$15,000 $15,000
$30,000 $30,000
$45,000 $45,000
$60,000 $60,000
$75,000 $75,000
$90,000 $90,000
$105,000 $105,000
$110,000 $120,000
$115,000 $125,000
4500
$15,000
$30,000
$45,000
$60,000
$75,000
$90,000
$105,000
$120,000
$135,000
• For each line of the table we can compute
an expected value of profit by multiplying
the profits by the demand probailities and
adding
Demand
Probability
2000
500
0.025
$30,000
1000
0.05
$40,000
1500
0.15
$50,000
2000
0.25
$60,000
2500
0.25
$60,000
Expected profit for order of 2000 is:
.025*30000 + .05*40,000 + ….
3000
0.15
$60,000
3500
0.07
$60,000
4000
0.04
$60,000
4500
0.015
$60,000
Order quantity Expected profit
500
$15,000
1000
$29,750
1500
$44,000
2000
$56,750
2500
$67,000
3000
$74,750
3500
$81,000
4000
$86,550
4500
$91,700
Order 2000 to
maximize the
expected profit.