Decision Analysis
DECISION ANALYSIS ASSIGNMENT 1
Case Problem 1: Planning Advertising Campaign
EXCEL sensitivity report for the Flamingo Grill case problem:
Managerial Report
Question 1:
Data given for Flamingo Grill case problem:
Total budget = $279000 Advertising media | Exposure rating per ad | New customers per ad | Cost per ad ($) | HJ consultant limit application to | Television | 90 | 4000 | 10000 | 10 | Radio | 25 | 2000 | 3000 | 15 | Newspaper | 10 | 1000 | 1000 | 20 |
After consideration of limit for each advertising media: Advertising media | Exposure rating per ad | New customers per ad | Cost per ad ($) | Television | 55 | 1500 | 10000 | Radio | 20 | 1200 | 3000 | Newspaper | 5 | 800 | 1000 |
Model:
Decision variables: Advertising media | Num of campaigns for max effectiveness | Num of campaigns for reduced effectiveness | TOTAL | Television | 10 | 5 | 15 | Radio | 15 | 18 | 33 | Newspaper | 20 | 10 | 30 | TOTAL | 45 | 33 | |
TOTAL EXPOSURE = 2160
TOTAL NUMBER OF POTENTIAL NEW CUSTOMERS = 127100
Constraints:
* Must reach at least 100000 new customers
127100 ≥ 100000 * Total campaign slots for maximum effectiveness smaller or same with limit
Television: 10 ≤ 10
Radio: 15 ≤ 15
Newspaper: 20 ≤ 20 * At least twice as many radio ads as television ads
33 ≥ 30 * Use not more than 20 television ads
Television: 15 ≤ 20 * Television budget should be at least $140000
$150000 ≥ $140000 * Radio advertising budget is restricted to a maximum of $99000
$99000 ≤ $99000 * Newspaper budget is to be at least $30000
$30000 ≥ $30000 * Overall advertising budget is restricted to a maximum of $279000
$279000 ≤ $279000
Question 2:
If additional $10000 is added to the advertising budget, the total exposure will increase by 55 units as additional television campaign can be allocated.
Question 3:
The ranges indicate that if the allowable increase and decrease is within a very small range,
Case Problem 1: Planning Advertising Campaign
EXCEL sensitivity report for the Flamingo Grill case problem:
Managerial Report
Question 1:
Data given for Flamingo Grill case problem:
Total budget = $279000 Advertising media | Exposure rating per ad | New customers per ad | Cost per ad ($) | HJ consultant limit application to | Television | 90 | 4000 | 10000 | 10 | Radio | 25 | 2000 | 3000 | 15 | Newspaper | 10 | 1000 | 1000 | 20 |
After consideration of limit for each advertising media: Advertising media | Exposure rating per ad | New customers per ad | Cost per ad ($) | Television | 55 | 1500 | 10000 | Radio | 20 | 1200 | 3000 | Newspaper | 5 | 800 | 1000 |
Model:
Decision variables: Advertising media | Num of campaigns for max effectiveness | Num of campaigns for reduced effectiveness | TOTAL | Television | 10 | 5 | 15 | Radio | 15 | 18 | 33 | Newspaper | 20 | 10 | 30 | TOTAL | 45 | 33 | |
TOTAL EXPOSURE = 2160
TOTAL NUMBER OF POTENTIAL NEW CUSTOMERS = 127100
Constraints:
* Must reach at least 100000 new customers
127100 ≥ 100000 * Total campaign slots for maximum effectiveness smaller or same with limit
Television: 10 ≤ 10
Radio: 15 ≤ 15
Newspaper: 20 ≤ 20 * At least twice as many radio ads as television ads
33 ≥ 30 * Use not more than 20 television ads
Television: 15 ≤ 20 * Television budget should be at least $140000
$150000 ≥ $140000 * Radio advertising budget is restricted to a maximum of $99000
$99000 ≤ $99000 * Newspaper budget is to be at least $30000
$30000 ≥ $30000 * Overall advertising budget is restricted to a maximum of $279000
$279000 ≤ $279000
Question 2:
If additional $10000 is added to the advertising budget, the total exposure will increase by 55 units as additional television campaign can be allocated.
Question 3:
The ranges indicate that if the allowable increase and decrease is within a very small range,