Data analytics

Data prep: in the Cracker worksheet, create an If statement where the result equals 1 if the customer: bought the Sunshine brand (reported in column O) and 0 otherwise in cell P2 & copy down to row 3293; bought the Keebler brand (reported in column O) and 0 otherwise in cell Q2 & copy down to row 3293; bought the Nabisco brand (reported in column O) and 0 otherwise in cell R2 & copy down to row 3293; bought the Private brand (reported in column O) and 0 otherwise in cell S2 & copy down to row 3293.

1.

Data & Statistics (Save your spreadsheet by your first-last name and assignment number. (e.g. stacey-brook2.xlsx). [50 points]

a. In the Market Share worksheet, calculate the total number of purchases for each brand in cells B2, C2, D2 & E2. [countif] makes this easy. Create a Cracker Market Share Pie chart in this worksheet, with the title Cracker Market Share.

b. In the Display worksheet, calculate the total number of times each brand has been on display in cells B2, C2, D2 & E2. [sum] or [countif] makes this easy. Calculate the total number of times that each brand has been on display was actually purchased in cells B3, C3, D3 & E3. [countifs] makes this easy. Calculate the percentage of purchases based on the number of times the product was on display in cells B4, C4, D4 & E4 (i.e. B3/B4, …). Finally, calculate the correlation between brand display and consumer brand purchase (by brand) in cells B6, C6, D6 & E6.

c. In the Features worksheet, calculate the total number of times each brand has been featured in cells B2, C2, D2 & E2. [sum] or [countif] makes this easy. Calculate the total number of times that each brand has been featured was actually purchased in cells B3, C3, D3 & E3. [countifs] makes this easy. Calculate the percentage of purchases based on the number of times the product was on display in cells B4, C4, D4 & E4 (i.e. B3/B4, …). Finally, calculate the correlation between brand feature and consumer brand purchase (by brand) in cells B6, C6, D6 & E6.

d. In the Price worksheet, calculate the average price for each brand in cells B2, C2, D2 & E2.

Calculate the standard deviation of price for each brand in cells B3, C3, D3 & E3. Calculate the correlation between price whether the brand was purchased for each brand in cells B4, C4, D4 & E4.

e. In the Brand Loyalty worksheet, calculate for each customer the total number of times each brand has been purchased in cells B2, C2, D2 & E2, then copy cells B2 to E2 and paste to row 137. [countifs] makes this easy. In cells I2 through M2 calculate the number of times each brand has a brand loyal customer (this calculation has been given to you in column G). [countif] makes this easy. Calculate the sum of cells I2 through M2 (it should equal 136, the number of customers). Create a Pie chart of customer brand loyalty from cells I2 through M2. 2. Written Explanation (Save the Microsoft Word file by your first-last name and assignment number (i.e. stacey-brook2.doc).

a.

b.

c.

d.

e.

a. Just based on the Cracker Market Share Pie chart, why do you think that some brands have greater market share than others? Explain your reasoning.

b. From part 1b, what do you conclude about the effectiveness of these displays? Explain your reasoning. From part 1c, what do you conclude about the effectiveness of these newspaper features? Explain your reasoning.

c. Economists state there is a law of demand for consumer purchases that basically says there is a negative relationship between a product’s price and the amount of that product a consumer purchases. From part 1d, does the correlation between each brands price and the amount they purchase support this idea? Explain your reasoning.

d. Brand loyalty is defined as a customer buying one brand 90% (or more) of the time. This is admittedly a simple definition. What do you conclude about these customer’s brand loyalty? Explain your reasoning.

e. Suppose you have been hired as a data consultant for Sunshine brand crackers. Use the information from your data analysis and written analysis and provide two suggestions to improve the Sunshine brand market share.