CASE 1: ORGANISING SEGEMENTS
_________________________________________________________________________________
A Market Research Survey has been undertaken to identify consumers preferences with relation to;
Product: Interest in customized products
Campaign: Sales campaign type
Price: Price consciousness
Fifteen respondents completed the market research survey. The survey was arranged as indicated below:
Assumptions
Product: Interest in customized products was ranked from 1 = preference for timeless classics to 9 = preference for customised products.
Campaign: Interest in sales campaign types was ranked from 1 = preference for traditional media to 9 = preference for social media
Price: Price consciousness was ranked from 1 = little interest in price consciousness to 9 = high interest in price consciousness.
Through the Post-Hoc segmentation technique “Clustering” make use of Ward’s and K-Means analysis to find the best fit of consumers to market segments. Ensure you perform the repeated iteration required to bring the segments closer
Your task:
Using the above data, click on the worksheet tab ‘Case 1’ and complete the following calculations:
Table: Calculate Min/Max/Avg
Calculate the minimum value for the three variables
Calculate the maximum value for the three variables
Calculate the median value for the three variables
Use the minimum value for “Product” to identify the start point for segment 1
Use the maximum value for “Product” to identify the start point for segment 2
Use the median value for “Product” to identify the start point for segment 3
Table: Using “SUMXMY2” Function
Calculate the sum of squares for each start point for each respondent using SUMXMY2.
Calculate the minimum value across all start points for each respondent
Compare the minimum value for each respondent to their start values and assign each participant to the appropriate segment
Calculate the sum of squared error
Table: Means using “IF” Statements
In this table you will see that the scores for each respondent have been copied across to the appropriate segments.
Calculate the mean average for the significant variables across all segments.
Table: Distance from revised mean
Calculate the sum of squares for all segments for each respondent using SUMXMY2.
Calculate the minimum value for each respondent across the three segments.
Compare the minimum value for each respondent to their segment values and assign each participant to the appropriate segment – note if there have been any changes here
Calculate the sum of squared error – note the change to this from the original SSE result.
Analysis of results:
Write up a critical analysis of the results achieved through the marketing analytics calculations. Based upon the original market research variables state;
What three segments have been identified (*Note these will link back to the consumer profiles from assessment one).
Consider what the variables suggest about each segment.
State the significance of the reduced sum of squared error in calculating the distance for the revised mean values.
State recommendations that could be made to Bluebird based upon your findings.
_________________________________________________________________________________
CASE 2: ECOMMERCE SALES
_________________________________________________________________________________
Based upon the three segments you identified in case 1, and with reference to the 3 digital marketing campaigns used by Bluebird (see table 1), you have been asked to predict sales information. Specifically, you have been asked to predict the following sales data for the 3 segments across the marketing campaigns;
The number of orders,
The campaign responses
The budget and sales for the three segments.
After analysing the company’s historic sales performance over the last year, your team have assembled the data shown below:
Table 1: Sales History Data
Input: Sales Annual Data (averaged)
Sales Forecast: £140,000
Average revenue/order £250
Segment 1: % of sales 50%
Segment 2: % of sales 30%
Segment 3: % of sales 20%
(Source: BlueBird.com – Online Sales)
After analysing the campaign sales performance, your team have assembled the data shown below:
Table 2: Sales Campaign Effectiveness Data from Bluebird’s Online Sales
Input: Campaigns Campaign % of Sales Conversion Rate Cost Per Response (Lead)
Campaign A: Newsletter 30% 1% £2.20
Campaign B: PPC 30% 2% £1.00
Campaign C: Social Media 40% 2% £1.40
(Source: BlueBird.com – Online Sales)
Your task
Using the above data, click on the worksheet tab ‘Case 2’ and complete the following calculations:
Input Table: Sales
Calculate the Sales Forecast for the Fiscal Year
Calculate the Revenue/Sale for the Fiscal Year
Calculate the percentage of sales for each segment for the Fiscal Year
Input Table: Orders
Calculate the total number of orders for the Fiscal Year
Calculate the number of orders for each segment
Results Table: Segment 1
Calculate the orders for segment 1 for each type of campaign.
Calculate the required responses for segment 1 for each type of campaign.
Calculate the budget required to reach segment 1 prospects for each type of campaign.
Calculate the expected sales for segment 1 for each type of campaign.
Calculate the sales campaign effectiveness (spend/sales ratio) for segment 1 for each type of campaign.
Calculate the totals for orders, responses, budget, sales and sales effectiveness for segment 1
Results Table: Segment 2
Calculate the orders for segment 2 for each type of campaign.
Calculate the required responses for segment 2 for each type of campaign.
Calculate the budget required to reach segment 2 prospects for each type of campaign.
Calculate the expected sales for segment 2 for each type of campaign.
Calculate the sales campaign effectiveness (spend/sales ratio) for segment 1 for each type of campaign.
Calculate the totals for orders, responses, budget, sales and sales effectiveness for segment 2
Results Table: Segment 3
Calculate the orders for segment 3 for each type of campaign.
Calculate the required responses for segment 3 for each type of campaign.
Calculate the budget required to reach segment 3 prospects for each type of campaign.
Calculate the expected sales for segment 3 for each type of campaign.
Calculate the sales campaign effectiveness (spend/sales ratio) for segment 1 for each type of campaign.
Calculate the totals for orders, responses, budget, sales and sales effectiveness for segment 3
Results Table: Total Segments, Total Fiscal Year
Populate this table with the following data:
Total orders for each segment
Total number of responses required for each segment
Total budget required to reach prospects for each segment
Total budget required to reach prospects across all three segments
Total expected sales for each segment
Total expected sales across all three segments
Total sales effectiveness for each segment
Total sales effectiveness across all three segments
Analysis of results:
Write up a critical analysis of the results achieved through the marketing analytics calculations. These should be discussed in relation to;
The three segments.
The three marketing campaigns.
A discussion of overall findings.
State recommendations that could be made to Bluebird based upon your findings.
*Note: Remember that definitions of variables are included on lecture and seminar slides, you will be expected to use the appropriate terminology in discussing results.
___________________________________________________________________________
CASE 3: PERFORMANCE METRICS
_________________________________________________________________________
Bluebird is facing its largest competitor (The Boot N’ Shoe Co) who manufacture and sell customised shoes into the same market. You have been tasked with assessing and monitoring the strategic metrics for both Bluebird Footwear and the Boot N’ Shoe Co in order to understand Bluebird’s competitive advantages and keep tabs on the Boot N’ Shoe Co.
Your team have assembled the data shown below:
Table 3: Company Performance Data
Data Blue Bird Footwear Boot N’ Shoe Co
Customer Costs (average) £90,000 £90,000
Customer Revenues (avg) £140,000 £120,000
Discount Rate 8.0% 9.0%
Durability 0.70 0.50
Effective Market Share 0.14 0.12
Margin (per customer) £210 £200
Marketing Spending £15,000 £11,000
Relative Price 1.10 0.90
Retention Rate (Customers) 0.7 0.4
Revenue from Marketing £140,000 £150,000
Revenue, Year 1 £90,000 £100,000
Revenue, Year 2 £120,000 £110,000
Revenue, Year 3 £140,000 £115,000
Selling Price per Unit £250 £300
Variable Cost per Unit £40 £30
(Source: BlueBird.com and BootNshoe.com)
In assessing the above, you will draw upon the following metrics;
Market Approach Performance Metrics
Market Growth Performance Metrics
Brand Development Metrics
Customer Development Metrics
Your task
Using the above data, click on the worksheet tab ‘Case 3’ and complete the following calculations:
Table: Performance Metrics / Bluebird Footwear
Calculate the Brand Equity Index for Bluebird
Calculate the Contribution Margin for Bluebird
Calculate the Customer Lifetime Value for Bluebird
Calculate the Profit for Bluebird
Calculate the Revenue (Compound Annual Growth Rate) for years 1 – 3 for Bluebird
Calculate the Revenue (Year on Year Growth) for years 1 -2 for Bluebird
Calculate the Return on Marketing Investment Bluebird
Table: Performance Metrics / Boot N’ Shoe Co
Calculate the Brand Equity Index for Boot N’ Shoe.
Calculate the Contribution Margin for Boot N’ Shoe.
Calculate the Customer Lifetime Value for Boot N’ Shoe
Calculate the Profit for Boot N’ Shoe
Calculate the Revenue (Compound Annual Growth Rate) for years 1 – 3 for Boot N’ Shoe
Calculate the Revenue (Year on Year Growth) for years 1 -2 for Boot N’ Shoe
Calculate the Return on Marketing Investment Boot N’ Shoe
Analysis of results:
Write up a critical analysis of the results achieved through the marketing analytics calculations. These should be discussed in relation to;
A comparison of the strategic metrics for Bluebird Footwear and the Boot N’ Shoe Co
The performance of both Bluebird Footwear and the Boot N’ Shoe Co
Competitive advantages of Bluebird based upon the metrics
State recommendations that could be made to Bluebird based upon your findings.
*Note: Remember that definitions of metrics are included on lecture and seminar slides, you will be expected to use the appropriate terminology in discussing results.
___________________________________________________________________________
CASE 4: PRICING
_________________________________________________________________________________
The company CEO has asked you to check the launch plan for Bluebird’s new customisable product line. Having originally set the product price to £250, the CEO has concerns that this price might be too high for most consumers within the target segments. You have been asked to select an appropriate price for the product.
You have been provided with the following data:
Table 4 – Launch Data
Attribute Data
Investment £30,000
Fixed Cost £30,000
Variable Cost £40
Unit Sales (year) 5,000
% Markup 70%
Target ROI 50%
Based upon the data shown in table 4 you have been asked to calculate a target price using Markup/Cost Pricing and Target Return Pricing.
Your task
Using the above data, click on the worksheet tab ‘Case 4’ and complete the following calculations:
Table: Markup/Cost-Plus Pricing Calculations
Calculate the unit cost
Calculate the mark-up price
Table: Target Return Pricing Calculations
Calculate the unit cost
Calculate the target return price
Analysis of results:
Write up a critical analysis of the results achieved through the marketing analytics calculations. These should be discussed in relation to;
A comparison of the markup/cost pricing and target return pricing findings
A consideration of the advantages and disadvantages of Markup/Cost-Plus Pricing
A consideration of the advantages and disadvantages of Target Return Pricing
State recommendations that could be made to Bluebird based upon your findings.
