Objectives:

By completing the analysis exercise in this case study, students will:

$$ Develop proficiency in performing detailed descriptive analysis of historical demand data using Excel.

$$ Master to use Excel to apply time series forecasting methods.

$$ Learn to extract strategic insights from the demand analysis and forecasting results.

Case Background:

DairyFresh Inc. was established in the early $1980$s in Ontario, Canada, born from a small family$-$run dairy farm. The company initially focused on producing high$-$quality milk and cheese, serving local communities. Their commitment to freshness and quality quickly rapidly attracted a loyal customer base. In the $1990$s$,$ DairyFresh Inc. began expanding its operations. They diversified their product line to include a wider range of dairy products like yogurt, butter, and cream. This period also marked their first steps outside Ontario, as they started distributing to neighboring provinces. Recognizing the need to modernize in early $2000,$ DairyFresh Inc. invested in technology to improve their production and supply chain processes. This included advanced refrigeration systems for better product freshness and a computerized supply chain management system to enhance distribution efficiency. The $2010$s saw DairyFresh Inc. shifting towards sustainable practices. They adopted eco$-$friendly packaging and waste reduction techniques. The company also started collaborating with local farmers to source ethically produced milk, aligning with growing consumer awareness and demand for sustainability. In recent years, DairyFresh Inc. has broadened its market reach into Western Canada, establishing a strong presence in British Columbia, in addition to its ongoing operations in Ontario and Quebec. Currently, DairyFresh$$s market share is $10\%$ in Ontario, $6\%$ in Quebec and $4\%$ in British Columbia. They are expecting to maintain their market share in Ontario and Quebec in $2023$ and increase their share in BC to $6\%.$

It's January $2023$ now and Amy, as the Supply Chain Analyst for the Milk category at DairyFresh Inc., is responsible for making decision regarding transportation contracts. Transporting milk effectively and safely is a process that demands stringent conditions, especially given the perishable nature of dairy products. Refrigerated trucks, commonly known as "reefers," are essential in this logistic chain. These specialized vehicles are equipped with cooling systems that maintain a constant temperature, ensuring the milk stays fresh and prevents bacterial growth during transit. Additionally, the interior of these trucks is designed to be easily sanitized, reducing the risk of contamination. This is crucial for DairyFresh Inc., as maintaining the highest quality and safety standards is not just a regulatory requirement but also a commitment to their customers. The transportation of milk also requires adherence to specific regulations regarding temperature control and hygiene standards, making the choice of a competent carrier with the right equipment and expertise a critical decision.

University Canada West

Amy is considering a strategic move to sign a long$-$term contract with a transportation carrier to manage all upcoming shipments in Ontario, Quebec, and British Columbia. She has identified five carrier companies operating in these provinces, each offering different shipment capacities and guaranteeing only a certain portion of this capacity for DairyFresh Inc. Importantly, Amy has verified that all the companies on her list meet the essential conditions for milk transportation, ensuring that DairyFresh Inc.$\text{'}$s products will be transported under the required standards for dairy product shipment.

Carrier Name

Capacity in Ontario $($kL$)$

Guaranteed Capacity for Amy in Ontario $($kL$)$

Capacity in BC $($kL$)$

Guaranteed Capacity for Amy in BC $($kL$)$

Capacity in Quebec $($kL$)$

Guaranteed Capacity for Amy in Quebec $($kL$)$

Northern Transit

$6000$

$3000$

$1200$

$600$

$2600$

$1300$

Maple Logistics

$5800$

$2900$

$1100$

$550$

$2500$

$1250$

Quick Dairy Move

$2000$

$1000$

$900$

$450$

$1500$

$750$

Eco Carrier Solutions

$3500$

$1750$

$1100$

$550$

$2000$

$1000$

Fresh Milk Transport

$3000$

$1500$

$800$

$400$

$1800$

$900$

Before deciding about the shipment carrier contracts, she needs to have an estimate of the upcoming demand in each region. She has gained access to a valuable resource through Statistics Canada. This resource is a comprehensive dataset that documents commercial sales of milk and creams for $12$ different dairy products across all $10$ provinces in Canada over the past $45$ years. $[1]$ Among these products, Amy's focus is on three key items that fall under her purview: Skim Milk, Milk with $1\%$ fat, and Milk with $2\%$ fat. She has also decided to use the data covering years between $2013$ and $2022$ for her analysis as she thinks these will be more relevant.

Recognizing the potential of this dataset, Amy has decided to utilize it for an in$-$depth study of demand behavior for these three dairy products specifically in Ontario $($ON$),$ British Columbia $($BC$),$ and Quebec. This ana