Linear Programming Model Building and Decision Trees in Logistics and Supply Chain Management

SCHOOL OF MANAGEMENT
MSC LOGISTICS AND SUPPLY CHAIN MANAGEMENT
MSC PROCUREMENT AND SUPPLY CHAIN MANAGEMENT

Case 1: Linear Programming Model Building and Computer Solution
Q1.1 Cranfield Grill
Part [a]
Here, the decision variables are number of TV ads, Number of radio ads and number of
online ads.
Part [b]
Let us consider, T denotes the number of TV ads, R denotes the number of radio ads and O
denotes number of online ads.
Therefore, the objective function will be:
90*T+25*R+10*O + (T-10)*55+(R-15)*20+(O-20)*5
The constraints are:
2*R>=T
T<=20
10000*T >=140000
3000*R<=99000
1000*O>=30000
10000*T + 3000*R + 1000*O = 279000
4000*T+2000*R+1000*O + (T-10)*1500 + (R-15)*1200 + (O-20)*800 >= 100000
Part [c]
Excel model:
Advertising
Media
Exposure
Rating per
Ad
New
Customers per
Ad
Cost per
Ad
Max Ads Exposure
beyond
Max
New
customers
beyond
max
exposure
TV 90 4000 10000 10 55 1500
Radio 25 2000 3000 15 20 1200
Online 10 1000 1000 20 5 800
No of ads Total Expense
TV 14 279000 = 279000
Radio 7 Advertise constraints
Online 118 0 >= 0
14 <= 20
Total
Customer
Total Exposure
TV 62000 1480

Radio 14000 175 3325
Online 196400 1670
272400 >= 1,00,000
Expense
TV 140000 >= 140000
Radio 21000 <= 99000
Online 118000 >= 30000
Here the total exposure will be 3325 and the total number of customer will be 272400
Part [d] The exposure will change by 150 rating if 10000 budget is increased. This will result
in increase of customer of 18000.
Part [e]
The exposure rating is directly related to exposure rating and very much sensitive.
Part [f]
If the problem converted to maximize customer reach, the solution will be as mentioned
below:
Advertising
Media
Exposure
Rating per
Ad
New
Customers per
Ad
Cost per
Ad
Max Ads Exposure
beyond
Max
New
customers
beyond
max
exposure
TV 90 4000 10000 10 55 1500
Radio 25 2000 3000 15 20 1200
Online 10 1000 1000 20 5 800
No of ads Total Expense
TV 14 279000 = 279000
Radio 7 Advertise constraints
Online 118 0 >= 0
14 <= 20
Total
Customer
Total Exposure
TV 62000 1120
Radio 14000 375 2185
Online 196400 690
272400 >= 1,00,000
Expense
TV 140000 >= 140000
Radio 21000 <= 99000
Online 118000 >= 30000

It means, maximum 272400 people can be reached.
Part [g]
Maximizing customer reach will make much more sense than maximizing exposure, as this
will directly improve the sales volume, when there is a chance that the exposure will not be
converted to sales to some extent.
Q1.2 Cranfield Materials
[a] Model:
Manufacturing and Purchase Costs
Component Manufacturing
Cost
Purchasing
cost
Frame £38.00 £51.00
Support £11.50 £15.00
Strap £6.50 £7.50
Production Time and Available
Capacity in Minutes
Cutting Milling Shaping
Frame 3.5 2.2 3.1
Support 1.3 1.7 2.6
Strap 0.8 0 1.7
Capacity (min) 21000 25200 40800
Cutting Constraint Milling
Constraint
Shaping
Constraint
20999.6 15576.4 22499.2
Manufacturing
Units
Total
Requirements
Purchase
units
Frame 5000 5000 0
Support 2692 10000 7308
Strap 0 5000 5000