Analyzing Market Forces in Colorado Retail Milk

Group Project

This is a Group Project. Max number per group is 4. Groups must be submitted via Canvas by the due date listed on Canvas.

Background

Cousins Supermarket is one of the largest food retailers in the U.S. They operate about 1,525 grocery retail stores in 20 states under a variety of banner names. Cousins operates about 15 food production or manufacturing facilities producing high quality private-label products. Data and science are at the core of their business. They collect 96% of sales on their customer loyalty card. Like many other agricultural and food businesses, they are increasingly interested in leveraging the data that they collect to increase profits.

Project

You and your team are working as market data analysts for Cousins Supermarket. Your manager sends you company data in the following Excel file: milkmarketfmmocwt.xlsx. She tells you that this file is PROJECT TEAM EYES ONLY and MUST not be shared outside of your team. The file contains data on Cousins milk sales in Colorado prior to the pandemic: June 2014 – February 2019. She was also able to obtain milk data on the local competitors: Jeminy, Legals, Greens, and Fresh & Better. There were a bunch of small stores that she could not get data on, but she has a good idea of sales for the total market area based on industry estimates. Your manager tells you that milk is a good indicator for overall sales.

Over the course of six emails, your manager asks you to complete a series of data-driven tasks to inform decision-making at Cousins Supermarket. These tasks include analyzing market trends, visualizing competitive data, and assessing strategic options such as mergers.

Submission Guidelines

For the final submission of your project, please ensure that your work is organized and presented professionally. Follow these instructions carefully:

  1. Organize all six emails sequentially in a Word Document.
    • Each email should be clearly labeled (e.g., Email 1: Market Analysis and Predictions).
    • Every file that you create must carry the designation PROJECT TEAM EYES ONLY to prevent a data breach.
    • Prepare a cover page before the emails using the template provided below.
    • Use Times New Roman, 12-point font, throughout.
  2. You must include the Excel file that generated the graphs referenced in your emails.
    • Each graph must be clearly labeled and formatted to appear on separate Worksheets in your Excel workbook, with axis labels and appropriate titles.
  3. Your final submission files will include:
    1. Your email packet, including a cover letter and the six emails, as a Word Doc or PDF.
    2. Your complete Excel workbook with all graphs and exhibits referenced in the emails, as well as the data and calculations used to create the graphs. Each graph must appear in separate Excel Worksheets.
  4. Submit your final documents via Canvas by the deadline.

Late submissions will not be accepted. Make sure to review all materials and complete the submission before the deadline.

Project Data

👉 Download data file here

NoteDescription of the data file
Variable Description
Year Year of the data point
Month Month (numeric) of the data point
MonthName Month (name) of the data point
q1 Quantity (i.e., gallons) of milk sold via Jeminy
q2 Quantity (i.e., gallons) of milk sold via Cousins
q3 Quantity (i.e., gallons) of milk sold via Legals
q4 Quantity (i.e., gallons) of milk sold via Fresh & Better
q5 Quantity (i.e., gallons) of milk sold via Greens
QM Quantity (i.e., gallons) of milk sold for the entire Colorado market
p1 Weighted average price of milk sold via Jeminy
p2 Weighted average price of milk sold via Cousins
p3 Weighted average price of milk sold via Legals
p4 Weighted average price of milk sold via Fresh & Better
p5 Weighted average price of milk sold via Greens
VM Value of milk sales for the entire Colorado market
fmmocwt The Federal Milk Marketing Order (fmmo) prices per hundredweight (cwt)

Email 1: Market Analysis and Predictions

Management contacts you to gather information about the retail milk market in Colorado. They are asking you to conduct market research and make educated predictions about the general direction of retail milk market growth. Write an email to your manager outlining what your predictions are and what information they are based on.

In your email, you must answer the following questions:

  1. What types of factors determine whether demand for retail milk will increase, decrease, or stay the same? What retail milk demand factors are at play currently in the Colorado market and what factors were at play during the June 2014 – February 2019 period?

  2. What types of factors determine whether supply for retail milk will increase, decrease, or stay the same? What retail milk supply factors are at play currently in the Colorado market and what factors were at play during the June 2014 – February 2019 period?

Assume that retail milk is a homogeneous product in a market with many buyers and sellers, near perfect information, and mobile resources.

Cite all your sources. I will check to make sure that all sources exist and that the information is true and accurate. MLA citation style is not required, but easily accessing where the information is available is required. Note that webpages can disappear from day to day, so it is best practice to save a pdf of any website you reference with the download date in case the link is not working when your manager clicks it.

Please refer to the syllabus for the course policy on academic integrity and use of generative AI.

Email 2: Data Visualization

Your next task is to design a series of graphs and send them in an email to management. The three graphs you are to send to management include:

  1. Graph the monthly quantity of milk sold by Cousins over time. Our data is collected from August 2014 to January 2019, so the graphs should be for each month-year point in the dataset.

  2. In a separate graph, do the same for Jeminy, Legals, Greens, and Fresh & Better. Make sure that the values of the axes are the same for ease of comparison. Discuss your findings.

  3. Graph the market shares of Cousins, Jeminy, Legals, Greens, and Fresh & Better. Discuss your findings. Note: Market share is the percentage of an industry’s total sales over a certain period. It is calculated by dividing total company sales by total industry sales. Your email must include a description of the method you used to create the exhibit, and a discussion of the results. The graphs should have labeled axes and make appropriate use of colors.

Email 3: Market Structure Analysis

Management is interested in understanding the competitive landscape of the Colorado retail milk market. They want to assess the level of market concentration and determine if the market structure resembles perfect competition or tends towards oligopoly. To achieve this, you are asked to calculate and analyze key market structure indicators. These calculations will help determine Cousins’ position in the market relative to its competitors and provide insights into the overall market dynamics.

Management wants to understand the position of the firm in the market. They ask you to perform the following calculations and report back in one, easy-to-understand email:

  1. Calculate the HHI of the market and graph it over time. Discuss your findings.
  2. Calculate the CR4 and the CR2 and graph each over time. Discuss your findings.
  3. Based on these findings, can we assume the market is behaving as if it is perfectly competitive? Describe your rationale in your email.

Email 4: Price Analysis

Management has requested an analysis of price movements to gain a deeper understanding of price trends in the Colorado retail milk market. This analysis will help identify patterns in pricing strategies across the industry and specifically for Cousins. Additionally, it will provide insights into how retail prices compare to the cost of milk at the wholesale level, indicating the potential for profit in the supply chain.

  1. Graph the monthly weighted aggregate price index of milk sold by each retailer over time. Add labels and title to your graph. Include a legend.

  2. Calculate the Laspeyres and Paasche indices to show how the aggregate price in the market is moving over time. Graph them on the same chart in a new worksheet. Add labels and title to your graph. Include a legend.

    • Note: The “market” is represented by Market_Quantity (total quantity) and Market_V_Corrected (value of milk sales). The price index will represent the price changes relative to the base period.

  3. Summarize your findings for parts 1 and 2 in a concise, professional email to your manager. This email should describe how you constructed each measure, outline the key insights from your analysis, highlighting any significant trends or patterns you’ve identified. Be sure to interpret your findings and explain the implications for Cousins’ overall market position.

Email 5: Margin Analysis

  1. Using the Federal Milk Marketing Order Class I price (variable = fmmocwt), do the following:

    1. Create a new variable fmmogal = fmmocwt/11.63. The Federal Milk Marketing Order prices are per hundredweight (cwt), so you will need to convert them to gallons to match the retail data you were given. There are roughly 11.63 pounds of milk in a gallon.
    2. Determine the marketing margin of the industry over time. Graph it.
    3. Determine the marketing margin for Cousins only over time. Graph it.

  2. Summarize your findings in a concise, professional email to your manager. This email should outline the key insights from your analysis, highlighting any significant trends or patterns you’ve identified. This email must contain discussion of the results of the marketing margins and an interpretation of your findings. Be sure to explain the implications of your findings for Cousins’ overall market position.

Email 6: Demand Analysis

Your manager also shares some confidential information from the CEO. The CEO believes that one way to increase profits will be to merge with another retailer in Colorado.

  1. Estimate the demand for Cousins milk. What is the own-price elasticity? What are the cross-price elasticities? Interpret them.

  2. Look at your demand results. Which other retailer’s price has a negative and significant cross-price elasticity with Cousins milk? Explain:

    • Why your results show they are complements
    • How merging with that retailer could help increase profits (for example, through joint promotions, product bundles, or shared customers)
    • Any possible challenges or risks (like competition or store overlap)

How You’ll Be Graded

Your project will be evaluated based on:

Criteria Points Description
Depth of research + prep 25 Evidence of thoughtful background research, preparation, and familiarity with the mentor’s work and industry.
Interview quality + flow 25 Questions are clear and engaging; conversation flows naturally and professionally; follow-ups deepen the discussion.
Insightful show notes 25 Notes accurately summarize the episode, highlight key takeaways, and are clearly written for an external audience.
Team coordination & clarity 25 All team members contribute meaningfully; roles are clear; final product is cohesive and well-organized.

Total: 100 points (10% of final grade)

COVER PAGE TEMPLATE

Cousins Supermarket Data Analytics Project: Final Submission

Team Name: [Insert Team Name]

Project Title: Market Data Analysis and Strategic Recommendations for Cousins Supermarket

Date of Submission: [Insert Date]

Instructor: Dr. Chenarides

Course: [Insert Course Name]

Team Members:

  1. [Insert Full Name]
  2. [Insert Full Name]
  3. [Insert Full Name]
  4. [Insert Full Name]

Summary of Project:

This project analyzes the retail milk market in Colorado for Cousins Supermarket from June 2014 to February 2019, using sales data to assess market trends, pricing strategies, competition, and the potential for mergers.

Contents:

Email 1: Market Analysis and Predictions
Email 2: Data Visualization
Email 3: Market Structure Analysis
Email 4: Price Analysis
Email 5: Margin Analysis
Email 6: Demand Analysis