We start by opening edible grocery.csv in Excel. We immediately save it as an Excel workbook, say chapter 3.xlsx. • Looking at units, we see that sometimes more than one unit of a SKU was purchased on a given transaction. We create a spend variable, which is simply the product of units and price. We first enter spend in cell H1. Next we enter =E2*F2 in H2 and copy this formula down to H43557

# edible_grocery
chapter3 <- read.csv("edible_grocery.csv")
chapter3$spend=chapter3$units*chapter3$price

In order to create the weekly summary of brand and category revenue, we insert a pivot table where Rows is week and Columns is brand, and Values is (Sum of) spend. This creates the Sheet1 worksheet.

The weight of each SKU is given in SKU weight.csv. We copy the contents of this file into cells M1:N120 of edible grocery. We want to report in column I the weight of the SKU associated with each row. We need to identify the SKU id and extract the associated weight from the table in cells M1:N120. We will make use of the VLOOKUP function. We first enter weight in cell I1. Next, we enter =VLOOKUP(D2,M2:N120,2) in I2 and copy this formula down to I43557. • We create a volume variable, which is simply the product of units and weight, expressing the answer in kilograms. We first enter volume in cell J1. Next we enter =E2*I2/1000 in J2 and copy this formula down to J43557.

Next, we insert a pivot table where Rows is week and Columns is brand, and Values is (Sum of) volume. This creates the Sheet3 worksheet.

We copy the contents of the pivot table (A5:G108) into a new worksheet, which we will call Volume, leaving the first row empty. We add the following variables names in cells A1:G1: Week Alpha Bravo Charlie Delta Other Category. • Next, we need to compute each brand’s volume market in each week. We add the following headings in cells I1:M1: Alpha Bravo Charlie Delta Other. Next we enter =B2/$G2 in cell I2, formatting it as a percentage, and copy this formula across and down to M105. • Figure 3.3 is simply the plot of Alpha and Bravo.

## Warning: Using `size` aesthetic for lines was deprecated in ggplot2 3.4.0.
## ℹ Please use `linewidth` instead.

##              Alpha      Bravo    Charlie      Delta      Other
## Alpha    1.0000000 -0.8529611 -0.3423218 -0.1101068 -0.1509740
## Bravo   -0.8529611  1.0000000 -0.1136076 -0.2309738 -0.1033333
## Charlie -0.3423218 -0.1136076  1.0000000  0.1318588  0.2463137
## Delta   -0.1101068 -0.2309738  0.1318588  1.0000000  0.3056677
## Other   -0.1509740 -0.1033333  0.2463137  0.3056677  1.0000000

Going back to edible grocery, we want to create a year variable which indicates whether each record is associated with the first or second year of the observed data. We first enter year in cell K1. Next we enter =IF(C2<=52,1,2) in K2 and copy this formula down to K43557.

We insert a pivot table where Rows is year and Columns is brand, and Values is (Sum of) spend. This creates the Sheet6 worksheet. • We compute each brand’s annual value (or dollar) market share by entering =B5/G5 in cell J5, formatting it as a percentage, and copying this formula across and down to N6. We add the relevant row and column labels. • We note that Alpha’s revenue grew by 5%, even as category revenue dropped by 2%. Alpha’s market share grew by 3.3 percentage points, a 7.1% increase in its annual value market share.