Modelling Mathematical Models Put into Excel Spreadsheets Modelling

  • Slides: 20
Download presentation
• Modelling – Mathematical Models – Put into Excel Spreadsheets

• Modelling – Mathematical Models – Put into Excel Spreadsheets

Modelling ideas • Input Variables • Decision Variables • Targets

Modelling ideas • Input Variables • Decision Variables • Targets

• First Maths – Profit = Revenue - Cost – Linear Cost: •

• First Maths – Profit = Revenue - Cost – Linear Cost: • Cost = Fixed Cost + N* Variable Cost – Linear Revenue • Revenue = N* Price – Piecewise Linear Revenue • Revenue = N 1*Price 1 + N 2*Price 2 + … (e. g. when you can sell some t-shirts at a premium)

• Projected Costs • Dealing with uncertainty – (actually still to come in

• 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

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.

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

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

Uncertain Sales

Uncertain Sales

Expected Value • Suppose somebody throws a dice and gives you a pound for

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

• 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 =Revenue-Cost Revenue =Units_sold_at_regular_price*Regular_price+ Units_sold_at_leftover_price*Leftover_price

Back to Sam Want to maximise 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 = MIN(Order_quantity, Demand) = IF(Order_quantity < Demand,

• Units_sold_at_leftover_price = Order - Units_sold_at_regular_price = MIN(Order_quantity, Demand) = IF(Order_quantity < Demand, Order_quantity, Demand)

• Cost =VLOOKUP (Order_quantity, Cost. Lookup, 2) * Order_quantity

• Cost =VLOOKUP (Order_quantity, Cost. Lookup, 2) * Order_quantity

Make a Table

Make a Table

• For each line of the table we can compute an expected value

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

Expected profit for order of 2000 is: . 025*30000 +. 05*40, 000 + ….

Expected profit for order of 2000 is: . 025*30000 +. 05*40, 000 + ….