Case Study - - - About 1.53 billion gallons of ice cream and related frozen desserts were produced in the U.S in 2013. Sales of ice cream and frozen desserts rose 2.1 percent to #5.4 billion in the 52 weeks ended August 10, 2014, according to data from IRI, Chicago. Ice cream production reached 894 million gallons in 2012 and 897 million gallons in 2013, according to the U.S Department of Agriculture. (Source: Dairy Foods magazine). The majority of U.S ice cream and frozen desserts manufactures have been in business for more than 50 years and many are still family-owned businesses. According to a recent survey of International Ice Cream Association member companies, vanilla remains the most popular flavor among their consumers. Companies said that Chocolate Chip Mint and Cookies and Cream were the next most popular flavors. Source : IDFA ice cream company survey,2012 The majority of ice cream and frozen desserts are marketed regionally. More than 66.7 percent of U.S ice cream and frozen dessert manufacturers say they market their products regionally, with 16 percent marketing nationally. The international market accounts 10 percent of the market for U.S companies. Source: IFDA ice cream company survey , 2012 Based on the reports, the most popular ice cream flavors reported are shown in table 2CS.1. Table 2CS.1: Most Popular Ice Cream Flavors Brand Vanilla Chocolate Nut & Caramel Neapolitan Strawberry Cookies 'N Cream Chocolate Chip Cherry Rocky Road Coffee Butter Pecan French vanilla 29% 8.9% 6% 6% 5.3% 3.6% 3.9% 1.6% 2% 2% 5.3% 3.8% Table 2CS.2 shows the sales of the leading ice cream brands of the United States in 2016. Ben & Jerry's was the third ranked ice cream brand of the United States with about 465.4 million U.S dollars' worth of sales. According to the cited here, total ice cream category sales amounted to about 5.15 billion U.S dollars .Chocolate, Vanilla and cookies dough /cookies and cream were among the leading ice cream flavors in the United States in 2013. Table 2CS.2: Total Sales of Ice Cream Brands BRAND Private label Breyers Ben & Jerry's Hagen-Dazs Wells Blue Bunny Dreyer's/Edy's Grand Dreyer's /Edy's Slow Churned Turkey Hill Blue Bell Other Unilever Best Foods N.America Sale ($million) $1128.5 $499.9 $465.4 $418.8 $284.2 $251.5 $249.3 $238.7 $222.4 $207.2 Some other details and facts about the ice cream industry are given below. Table 2CS.3 lists the total export of U.S ice cream to the different countries. Tables 2CS.4, and 2CS.5 show the cost of opening an ice cream franchise and the production of frozen desserts in the U.S. Table 2CS.3: Major Importers of U.S Ice Cream COUNTRY Mexico Canada South Korea Trinidad & Tobago All others Percent of Total 32% 9% 5% 5% 49% Table 2CS.4: Cost of Opening an Ice Cream Franchise Category Construction Equipment Franchise Free Signs and Advertising Ice Cream Local Permits Cost $130,000 $101,000 $42,000 $12,500 $8,000 $2,500 Table 2CS.5: Frozen Desserts Produced in the U.S Frozen Dessert Regular Ice Cream Low-fat and Nonfat Ice cream Water Ice Frozen Yogurt Sherbet Novelties/other Gallons ( million) 912 396 61 50 49 12 Computer Assignment 6 (A Case on Ice Cream Industry) Name_________________________________________Class Day and Time_____________ You will need to create all the data files for this assignment. The data contains small tables that you need to type-n in Excel to perform your analyses. Boxes are provided to show the graphs and analysis. Expand them as needed. . Case Study 2.2 (Ice Cream Industry) Pages 137 to 139 of your text provide a report on the ice cream industry. Read the case and perform all the analyses using Excel. Do all the required charts and graphs identify each problem, and analyze each of the graphs/charts. To get full credit, you must provide all charts and graphs with complete analysis and report Create appropriate charts and graphs and perform the following analyses. Expand the boxes as desired. But put your graphs and analysis in the box. 1. Create appropriate charts (for example, a bar chart and pie chart) to display the most popular ice cream flavors in Table 2CS.1. 1 2. Compare the sales of different brands using a bar chart. Analyze your graphs and charts. 3. Convert the sale of each franchise to the proportion of the total sales using the data in Table 2CS.2. Compare the market share (in terms of sales) for each franchise based on the total sales. Prepare a pie chart of the proportion of each franchise sales to the total sales. Comment on your finding regarding the sales. 2 4. Prepare a bar chart of the frozen desserts produced in the U.S. (Table 2CS.5). Using an appropriate chart, compare the production of each category in Table 2CS.5 as a proportion of total production of all categories. Comment on your finding. 5. According to the report, the total export of U.S. ice cream to different countries is $812 million (Table 2CS.3). Convert each country's export in this table in dollar values then construct an appropriate chart to compare the dollar value and the percent for each category. You can create two bars side by side for each category to show the dollar values and the percentage. 3 6. The average cost of opening an ice cream franchise is shown in Table 2CS.4. Prepare a pie chart showing the percent cost of each category as a total cost. Comment on the major cost categories. 7. If you are planning on starting an ice cream franchise, which brand or brands you would consider? Besides sales, what factors will affect your decisions. Use the web to search for information regarding the current status of this industry. 4 Find any useful information that might be helpful in opening your own franchise. Summarize your findings in a report. 5