Excel Analysis Questions

Hi Class,

Based on feedback I received at the end of lecture 2 for section A, am sending the other sections beforehand the questions on which the excel analysis is based.

This is directly from the relevant slides:

Open the dataset given.

Study its structure and scan its contents.

Answer the following questions quickly and briefly.
1. Product level: Is the dataset at the brand level or the SKU level?
2. Time period aggregation: Is the data weekly, monthly or yearly?
3. Does the dataset allow one to distinguish between light and regular beer?
4 Does the dataset have price information available?
5. Does the dataset give any idea about how widely a product is distributed?
6. What is the distribution measure used?
7. Does the dataset give any idea about how widely a product is promoted?
8. What is the promotion metric used?
9. Does the dataset tell us what type of packaging is available?
10. Is brewery information available?
11. For what time periods is the dataset about?

The above were to understand the structure of the dataset. Some illumination on the contents, the terms used etc can be found below:

FDM stands for “Food, Drug and Mass” – the 3 main US retail formats.
‘ACV’ stands for ‘All Commodity Volume’.

Measure that weighs distribution or promotion of a product in any store with that store’s total revenue.

So 15% ACV distribution means the product was available to/exposed to 15% of the gross retail purchasing power in the US.

‘Feature’ means featured in the store magazine or print advertised locally.

‘Display’ means the product was put up on special display outside its regular place on the shop shelf.

The precise questions asked are:

Task (1) Find the top 5 brands by sales revenue for the entire time period? Draw a pie chart and a bar chart to represent this information.

One How-to procedure:
(i) Brand information is given on the ‘beer characteristics’ worksheet. Need to transfer it to the ‘US FDM’ worksheet.
(ii) Before doing anything, first create a leftmost ‘serial num’ column in both worksheets.
(iii) Now sort the PROD columns in both sheets.
(iv) Now use the VLOOKUP() function in excel and create a column called BRAND next to PROD in the ‘US FDM’ worksheet.
(v) Now switch on a PIVOT table, use FCT as page filter, and ensoi.

More questions:

Task (2) Plot marketshare for each of the top 5 brands for each of the years in the sample
Use a single line-chart.
Task (3) Can you obtain prices ? How about price per unit volume?
Do so for each of the top 5 brands for each year in the sample. Plot on a line-chart.
Task (4) Compute the Number of SKUs per brand for the top 5. How about revenue per SKU?
Task (5) Obtain info on which brands are most promoted. Which SKUs within the most promoted brands are most promoted?

And so on. What kind of questions can be answered by such secondary data analysis?

The point of doing this is that folk can work out a\beforehand perhaps what was covered in class, or go home from class and lookup the same dataset and work on the same exercise.

Excel skills are critical for everyday functioning in the business world aajkal. If any particular function is new or unfamiliar, ask around, lookup help and so on. But make sure end of the day you know you are good to go with any similar exercise.

Let me know if there are any questions or comments. Tks for all those who gave me this feedback.

Sudhir