$($a$)$ test for difference in means between test vs controls for both products $($watch the # of stores$),($b$)$ provide a unit sales forecast with and without ads $($use the "sales potential" information and the "represented X$\%$ of sales" information$),($c$)$ estimate profits with and without ads for Arizona and California $($assume they are running the same ad that was used in the experiment, ie treat the development of the ads as a sunk cost, and the $3$M ad budget in this roll$-$out is the national total, i$.$e$.$ you need to adjust it for Arizona and California combined based on population $($tracks with the retailer's national sales percentages$))$

In other words, you only do AZ and CA; you need to think of them as one joint market; the $3$M national ad budget needs to be prorated for AZCA based on population $($the $12$ and $2$ figure can be used for this purpose $($actual numbers are $12\mathrm{.}18$ and $2\mathrm{.}12)$

Arizona for four weeks from June $17,2007,$ to July $14,2007.$ The company purchased $600$ gross

rating points $($GRPs$)$ for the television advertisements for a total cost of $$31,500.$ The ads were

aimed at girls between the ages of $2$ and $11$ and were aired on local cable channels, such as

Nickelodeon and the Cartoon Network. Management also purchased $64$ GRPs for radio

commercials for a total cost of $$8,022.$ The radio commercials were aired during morning and

evening commutes. Each of the television and radio programs selected for the commercials

reached about $1\mathrm{.}8\%$ of the population in Phoenix. The cost of developing the commercial

through an outside agency was $$150,000.$

Management estimated that an equivalent ad budget for eight to ten weeks of preholiday

advertising, factoring in certain economies as well as the higher seasonal cost of media, would be

approximately $$3$ million. The average retail selling price of Betty Spaghetty during the test was

about $$15\mathrm{.}00.$ Retailer and Ohio Art margins were about the same as for EAS, $36\%$ and $58\%,$

respectively. Given that some time would be required to read the test, obtain shelf space, and

ship product to stores, management estimated that the four$-$week test market sales period

represented about $10\%$ of the total remaining sales potential for the year.

Table $4$ reports weekly sales in $23$ Arizona stores $($test$)$ and in $24$ stores of the same mass

merchant in California $($control$)$ for two versions of Betty Spaghetty. The stores represented $50\%$

of the retailer's Arizona sales and $10\%$ of California sales, respectively. Arizona and California

represented $2\%$ and $12\%,$ respectively, of the retailer's national sales, and that same retailer was

expected to account for $25\%$ of total Betty Spaghetty sales. Management intended to use the test

to help estimate Betty Spaghetty sales with and without advertising.

Table $4.$ Weekly unit sales of Betty Spaghetty in test and control cities.

Data source: Ohio Art. Used with permission. Please show step by step setup in Excel for questions a$,$ b$,$ and c above.