Exercise 11 Estimating Ad Budgets: Although the markets still happen to be ranked in the same order, the new Est Value changed each market's relative worth. In addition to ranking markets, EV% can also be used to help allocate budgets. With a total budget of $200,000, a planner could allocate the funds for Rhodes using the Est. Value% from Exhibit II by converting the percentages to decimals this way Reno estimated ad budget: Salt Lake estimated ad budget: Portland estimated ad budget Seattle estimated ad budget. Chili's Marketing Mini-Case $200,000 X 14 $28,000 $200,000 X 22 $44,000 $200,000 X 29 $58,000 $200,000 X 35 $70,000 Niche marketing is an excellent strategy for building brand share in the Eating & Drinking/Restaurant cat- egory. Research conducted by Chili's discovered the Hispanic market niche (respondents of Hispanic heritage 42%) has the potential to increase national sales by 5% within two years. Research also suggests that the strongest appeal for this ethnic target audience would be one of Chill's most successful menu items-"Baby Back Ribs." Multi-ethnic, non-traditional promotions, combined with radio advertising, have been prepared to test Hispanic responses to Chili's ribs. Chili's will test in seven southwestern and southeastern markets. To prepare for the test, your job is to analyze the syndicated data in Exhibit I. Using a calculator, cre- ate a simple factor spreadsheet to rank seven designated test markets: San Antonio, Jacksonville, Tucson, Albuquerque, Corpus Christi, St. Petersburg, and Orlando. Follow the math models explained above (Exhibits I and II) to create your own factor spreadsheets for Chili's test market. Using the blank tables provided below, do the following: 1. Create an Original Data Factor Spreadsheet for all 7 METRO AREAS. Use the blank table on the next page. 2. Fill in the factor spreadsheet using data provided in Exhibit III (last page of exercise). Follow these impor- tant instructions as you consult the three tables: Population Table: Use Total (000) from the Population table. Remember numbers represent popu lation in thousands (000). It is not necessary to convert the numbers before using them in a factor spreadsheet (and we won't), however, it is absolutely necessary that you are consistent in recording data for a factor. Don't use 1244.7 to record San Antonio and 291,300 for Corpus Christi. Use the same format for both. For this exercise, record the figures in thousands (e.g. 1,244.-7 and 291.3, etc.) as reported in Exhibit Ill on the very last page of this exercise (next to Weighting Factor Spreadsheets exercise). . Retail Sales & Store Count: There are eight columns of data in the Retail Sales & Store Count table. Look at all eight columns carefully. Choose only one of the eight columns in addition to the # of Chili's stores that best represents Chili's business. Discretionary HH Income: Use Discretionary HH Income. Again, record the numbers as reported in (000). For example, Discretionary HH Income San Antonio: 28,601 Now, fill in the Total Across and calculate the Estimated Value % as shown above. Round EV%s to one decimal place Exercise 11 Original Data Factor Spreadsheet Choice from Markets San Antonio Pop (000) Retail Sales: Store Count Discretionary HH Income (000) Total Across Jacksonville Tucson. Albuquerque Corpus Christi St Petersburg Orlando Estimated Value % Total 3. Create a Percent Conversion Spreadsheet. Convert all data from step 1 to percentages in the spreadsheet below. Round to one decimal place-for example: 24.9% or 3.6%. Percent Conversion Factor Spreadsheet Markets % Pop (000) %Choice from Retail Sales: %Store % Discretionary HH Income % Total Count (000) Across Estimated Value % San Antonio Jacksonville Tucson Albuquerque: Corpus Christi St Petersburg Orlando Total 4. Based on the Estimated Value % column, rank order all seven markets from strongest to weakest. This list of markets will be based on your answers to question 3 above. 130 Exercise 11 5. a) After the percent conversion, only San Antonio stays in the same position-second. All the remaining markets changed their ranking in the percent conversion. List them showing the old EV% ranking (based on raw numbers) and their new EV% ranking after percent conversions. Market Old EV% Rank New EV% Rank b) Why did this happen? (Tip: Review the text below Exhibit II.) 6. Total advertising budget for the seven metro markets is $1,500,000. Based on the Estimated Values (EV%) from the Percent Conversion Factor Spreadsheet, calculate the total advertising budget allocated to each of the seven markets, and rank them from 1 to 7. For example, if one market had 11.7%6 EV, then multiply 117 X $1,500,000 in order to find that market's budget ($175,500). 111 112 Exercise 11 Exhibit III Population Median % of Pop. by Age Group Total Age Of 18-24 25-34 35-49 50 & Pop. Years Years Years Over Metro Market (000) % US HHs (000) San Antonio 1,244.7 0.410 40.1 8.3 14.2 17.8 38.9 53.9 Jacksonville 798.1 0.270 38.3 8.1 14.0 18.2 36.1 70.5 Tucson $21.7 0.170 33.0 11.6 17.3 20.0 26.1 365.9 Albuquerque 488.6 0.160 32.1 11.4 17.1 21.0 24.0 49.3 Corpus Christi 291.3 0.097 12.3 31.2 St. Petersburg 274.5 0.091 51.1 Orlando 215.0 0.072 40.5 260 17.5 20.7 22.7 128.6 6.7 11.7 15.1 51.1 115.7 8.7 14.4 17.3 39.6 838.9 Retail Sales $(000) and Stores Elec & Grocery Eating/ Cons & Super Drinking General Furniture & Auto Parts Phar Chill's Metro Market Prods store & Restrnt Mdse. Appliance & Access macy Stores San Antonio 1,170.0 257.0 152.3 899.0 117.7 250.7 40.0 17 Jacksonville 123.8 261.5 109.4 150.8 71.3 249.8 49.3 12 Tucson 7,210.4 1,385.9 1,046.9 838.7 321.6 1,996.0 190.1 12 Albuquerque 848.3 129.3 143.7 113.7 44.7 231.3 28.6 14 Corpus Christi 2,009.4 408.6 249.4 332.5 109.3 497.3 57.1 9 St. Petersburg 2,200.9 418.7 285.0 245.5 204.2 517.1 95.5 12 Orlando 13,442.9 2,903.6 1,742.0 1,760.5 874.1 3,132.8 461.2 10 Income % of Households By Income Group (A) $10,000-$19,999 (B) $20,000-$34,999 Discretionary (C) $35,000-$49,999 Total HHI HH Income (D) $50,000 & Over Household Buying Power Metro Market (000) $(000) A B C D Index San Antonio 28,702.3 28,601 25.8 26.4 12.0 15.4 0.0602 Jacksonville 27,238.6 28,300 31.4 25.9 9.4 7.9 0.0652 Tucson 28,542.2 26,188 23.4 27.0 16.7 17.1 0.4085 Albuquerque 26,686.1 22,702 29.8 25.8 11.6 7.6 0.0456 Corpus Christi 23,889.2 23,420 22.8 26.3 16.6 15.0 0.1301 St. Petersburg 31,164.5 33,818 23.3 23.6 14.4 15.9 0.0847 Orlando 25,703.9 24,469 26.5 26.8 14.6 14.4 0.8236