Modelling

1

• Modelling
• Mathematical Models
• Put into Excel Spreadsheets

2
Modelling ideas

• Input Variables
• Decision Variables
• Targets

3

• First Maths
• Profit Revenue - Cost
• Linear Cost
• Cost Fixed Cost N Variable Cost
• Linear Revenue
• Revenue N Price
• Piecewise Linear Revenue
• Revenue N1Price1 N2Price2
• (e.g. when you can sell some t-shirts at a
  premium)

4

• 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

5
Spreadsheets

• Absolute and relative addresses
• If statements
• Range names
• Making one-way tables
• Two-way tables
• Goal Seek

6
Uncertainty

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

7

• They are cheaper the more he orders
• First 1000 books are 24 dollars each next 1000
  23 etc.
• Use a Vertical Lookup Table

8
(No Transcript)
9
Uncertain Sales
10
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?

11

• 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

12
Back to Sam

• Want to maximise Profit
• Profit Revenue-Cost
• Revenue Units_sold_at_regular_priceRegular_price
  
• Units_sold_at_leftover_priceLeftover_price

13

• Units_sold_at_leftover_price
• Order - Units_sold_at_regular_price
• Units_sold_at_regular_price
• MIN(Order_quantity,Demand)
• IF(Order_quantity lt Demand, Order_quantity,Dema
  nd)

14

• Cost VLOOKUP (Order_quantity,CostLookup,2)
• Order_quantity

15
(No Transcript)
16
Make a Table
17

• For each line of the table we can compute an
  expected value of profit by multiplying the
  profits by the demand probailities and adding

18
Expected profit for order of 2000 is .02530000
.0540,000 .
19
(No Transcript)
20
(No Transcript)