In-Class Lab: Getting Started with the Market Analysis Project

This lab gives you hands-on practice with the tools and steps you’ll use in your Market Analysis Project.
By the end of class, you’ll have time to explore the dataset, practice building visualizations, and connect these early steps to the questions you’ll address in your final submission.

Time: 50 minutes
Files needed: milkmarketfmmocwt.xlsx

---

Objectives

By the end of this lab, you will:

• Understand the structure and variables in the dataset.
  
• Create new variables (e.g., a date variable combining year and month).
  
• Generate and label clear Excel graphs of milk prices.
  
• Interpret trends and patterns in your visualizations.
  
• See how these tasks connect to the first two project emails.

Step 1: Explore the Data

1. Open the Excel file milkmarketfmmocwt.xlsx.
   

2. Review the variable list in the “Project Data” section of the project instructions.
   

3. Answer the following in your notes (you’ll need this for Email 1):

   • What variables represent quantities sold by each retailer?
     
   • What variables represent prices?
     
   • What variables represent market totals?
     
   • What is the unit for fmmocwt? How does it differ from the retail price variables? Hint: Be sure to review “Email 4: Price Analysis” from the project instructions.

Understanding these distinctions is key for interpreting supply, demand, and margins later.

Step 2: Rename Variables to Align with Retailer Names

To make your analysis easier to follow, rename the quantity and price variables to match each retailer’s name. This will help you keep track of which series belongs to which firm when creating graphs later.

1. In your Excel file, rename each variable as follows:

   Original Variable	New Name
   q1	Jeminy_Q
   q2	Cousins_Q
   q3	Legals_Q
   q4	FreshBetter_Q
   q5	Greens_Q
   p1	Jeminy_P
   p2	Cousins_P
   p3	Legals_P
   p4	FreshBetter_P
   p5	Greens_P

2. Check that your variable names are consistent (e.g., use underscores instead of spaces) so formulas and charts reference them correctly.
   

3. Save your workbook before moving on.

Question to consider:

Why is it useful to rename variables before starting your analysis? How might inconsistent naming create problems later?

Step 3: Create a Date Variable

1. Insert a new column to the left of Year.
   
2. In this column, create a Date variable using Excel’s DATE() function: =DATE([Year cell], [Month cell], 1)
   • This gives you a continuous date value for plotting time series.
     
3. Format the column as Date (MM/DD/YYYY) for readability.
   
4. Name the column Date.

Answer the following questions:

• What is the period covered by the data?
• What is the frequency of the data (e.g., monthly, quarterly, annually)?
• Are there any missing months or irregular gaps in the data? If so, how might that affect your analysis?

Step 4: Plot Prices Over Time

1. Highlight your new Date column and the price variable for Cousins (Cousins_P).
   
2. Insert a Line Chart showing Cousins’ average price of milk over time.
   

• Add axis titles:
  • X-axis: Date
    
  • Y-axis: Price per gallon ($)
    
• Title your chart clearly: “Cousins Average Retail Milk Price, 2014–2019”
  

3. Move this chart to a new worksheet named Cousins_Price_Trend.
   

• To do this: Right-click → “Move Chart” → “New Sheet.”

Step 5: Compare Retailers

1. Using the same Date variable, add all five price series (Jeminy_P, Cousins_P, Legals_P, FreshBetter_P, Greens_P). *Important: Which cells do you need to
   
2. Rename this new worksheet Retailer_Prices_Comparison.
   
3. Insert a multi-line chart with each retailer as a different color line.
   

• Add axis titles:
  • X-axis: Date
    
  • Y-axis: Price per gallon ($)
    
• Title your chart clearly: “Price Comparison for Leading Retail Chains, 2014-2019”
  

4. Make sure all lines share the same axis range for fair comparison.
   
5. Move this chart to a new worksheet named Retailer_Prices_Comparison_Graph.
   

• To do this: Right-click → “Move Chart” → “New Sheet.

Questions to discuss or jot down:

• Which retailer tends to have the highest and lowest prices?
  
• Are there visible seasonal or cyclical trends?
  
• Does Cousins follow, lead, or lag other retailers in price changes?

Step 6: Preview Next Steps

Before the end of class, discuss with your group or reflect individually:

• How could these graphs help you write Email 2: Data Visualization?
  
• What additional information might you need to explain the trends (e.g., input costs, competition, or seasonality)?
  
• How might you connect these patterns to market structure for Email 3?

Step 7: Save and Organize

Before leaving class:

• Save your Excel file as teamname_lab1.xlsx.
  
• Make sure your graphs appear on separate worksheets with descriptive names. Important: The charts should be on their own worksheets, not as charts on top of a blank worksheet.
• Label each worksheet clearly (e.g., Cousins_Price_Trend, Retailer_Prices_Comparison).
  
• Keep this file—you can build directly from it for your project.

End of Lab