Analytical Techniques for Supply Chain Management

Analytical Techniques for Supply Chain Management
Student’s Name
Institution Affiliation
1

Case 1: Cranfield Grill
a. In the case described above the decision variable will be the number of advertisements
that are to be put in each of the three advertising medias.
This is described in the table below
Advertising
Media
Number of
Ads
TV 11
Radio 22
Online 60
b. Objective function and the constraints.
These are described by the table below.
Advertising
Media
Number of
Ads
Exposure Rating
per Ad
New
Customers Cost of Ads
Maximum
Number of
Ads with full
Exposure
beyond Max
Number of
Customers
captured
TV 11 935 44,000 £123,200 12 0 0
Radio 22 990 46,200 £83,600 10 240 14,400
Online 60 2400 51,600 £165,000 15 900 23,400
Budget
available £371,800 <= £374,000
Customers to
be reached 179,600 >= 120,000
Objective Function
Exposure Rating 5465
Other constraints
At least twice as many radio ads as TV ads 22 >= 22
At most 18 TV ads 11 <= 18
At least £120,000 on TV £123,200 >= £120,000
At most £120,000 on radio £83,600 <= £120,000
At least £50,000 on online £165,000 >= £50,000
c. Model solution
The model was solved using the excel solver add in. The output of the solver indicate that
11 ads should be put on TV, 22 ads on Radio while 60 ads should be put on the online
2

media platform. The TV ads will attract a total of 44,000 new customers, Radio ads a
total of 60600 while the online ads will attract a total of 75000 new customers.
Customers to
be reached 179,600 >= 120,000
Objective Function
Exposure Rating 5465
The table in part (b) above gives a summary of the excel solver outputs describing the
various outcomes of the optimal decision.
d. In the answer report generated by the excel solver, the budget available is a non-binding
constraint with a slack value of 2200. Adding an additional 5000 euros will allow the
firm to increase the objective function by 120 units, that is from the current value of 5465
to 5585.
e. For the final budget expenditure constraint, the solution remains unchanged when the
value reduces by up to 2200.
f. Changing the objective function
When the model is defined to optimize the number of new customers other than the total
exposure, the resultant model will be as displayed by the photo below.
Advertising
Media
Number of
Ads
Exposure Rating
per Ad
New
Customers Cost of Ads
Maximum
Number of Ads
with full customer
capture
Exposure
beyond Max
Number of
Customers captured
beyond maximum
ads
TV 15 1275 60,000 £168,000 12 180 6,900
Radio 31 1395 65,100 £117,800 10 420 25,200
Online 32 1280 27,520 £88,000 15 340 8,840
Budget
available £373,800 <= £374,000
Customers to
be reached 193,560 >= 120,000
Objective Function
Total number of new customers 193,560
Other constraints
At least twice as many radio ads as TV ads 31 >= 30
At most 18 TV ads 15 <= 18
At least £120,000 on TV £168,000 >= £120,000
At most £120,000 on radio £117,800 <= £120,000
At least £50,000 on online £88,000 >= £50,000
3

From the table the total budget utilized will be 373,800 which is higher than the 371800
which was utilized previously. In addition, the total new customers acquired will go up
from 179,600 to 193,560.
g. Best objective function.
The best objective function is the total number of new customers acquired. The aim of
advertising a restaurant is to increase its sales revenue. This is optimized when the
number of new customers acquired goes up. Since maximizing the total number of
customers optimizes the number of new customers acquired more than maximizing the
total exposure, it is the most preferred objective function (Lewis, 2009).
Case 2: Supplier Selection
a. Weight of the main criteria
This is obtained by summing the weight of the sub-criteria. The solution is summarised
by the table below.
Criteria Sub-Criteria Importance Weight
C1 Economic C11: Cost 7.7
C12: Quality 8.9
C13: Lead Time 7.9
C14: Energy Efficiency 8.2 32.7
C2: Environmental C21: CO2 emissions 8.3
C22: Water
consumption 7.1
C23: Resource
consumption 7.6 23
C3: Social
C31: Employee
satisfaction 7.3
C32: Support of
Professional
Development 8.8
C33: Work-Life
Balance 5.9
C34: Ethical Labour
Practices 7.4 29.4
4