two homework

sty1219179471

COMPUTERASSIGNMENT6_4eSU20192.docx

Home >Business & Finance homework help >Management homework help >two homework

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)

The article below provides 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.

A Report on Ice Cream Industry

Ice Cream Industry: The Wall Street Journal magazine, Smart Money (August, 2012) had a report on the ice cream industry in the United States. The article reported that the $8 billion dollars ice cream industry has experienced a sales decline in the recent years but investors in this industry can still profit with the right strategy. Following are some recent facts and data reported about the ice cream industry. The report compiled from the International Dairy Foods Association report (www.idfa.org/news-views/media-kits/ice-cream/ice-cream-sales-trends).

· 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 dessert manufacturers 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 for 10 percent of the market for U.S. companies. Source: IDFA 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 Brand
Vanilla	29%	Chocolate Chip	3.9%
Chocolate	8.9%	Cherry	1.6%
Nut & Caramel	6%	Rocky Road	2%
Neapolitan	6%	Coffee	2%
Strawberry	5.3%	Butter Pecan	5.3%
Cookies ‘N Cream	3.6%	French vanilla	3.8%

The leading ice cream brands of the United States in 2016, based on sales (in million U.S. dollars)

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 source cited here, total ice cream category sales amounted to about 5.15 billion U.S. dollars. Chocolate, vanilla and cookie dough/cookies and cream were among the leading ice cream flavors in the United States in 2013. www.statista.com/statistics/190426/top-ice-cream-brands-in-the-united-states/

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 deserts in the U.S.

Brand	Sales ($million)
Private label	$1128.5
Breyers	$499.9
Ben & Jerry’s	$465.4
Hagen-Dazs	$418.8
Wells Blue Bunny	$ 284.2
Dreyer’s/Edy’s Grand	$251.5
Dreyer’s/Edy’s Slow Churned	$249.3
Turkey Hill	$238.7
Blue Bell	$222.4
Other Unilever Best Foods N. America	$207.2

Table 2CS.2: Total Sales of Ice Cream Brands

Table 2CS.3: Major Importers of U.S.

Ice Cream

Country	Percent of total
Mexico	32%
Canada	9%
South Korea	5%
Trinidad & Tobago	5%
All others	49%

Table 2CS.4: Cost of Opening an Ice Cream Franchise

Category	Cost
Construction	$130,000
Equipment	$101,000
Franchise Fee	$42,000
Signs and Advertising	$12,500
Ice Cream	$8,000
Local Permits	$2,500

Table 2CS.5: Frozen Desserts Produced in the U.S.

Frozen Dessert	Gallons (million)
Regular Ice Cream	912
Low-fat and Nonfat Ice cream	396
Water Ice	61
Frozen Yogurt	50
Sherbet	49
Novelties/other	12

(Source: For all the data above except Tables 2CS.1 and 2CS 2CS.2 the sources cited in the report were: Cold Stone Creamery, IBISWORLD, National Ice Cream Retailer Association, NPD Group, Symphony Iri Group, USDA, U.S. International Trade Commission).

Refer to the facts and data presented about the ice cream industry. Create the data files and use a computer package, such as, Excel to create appropriate charts and graphs. Analyze each graph and provide your comments in each case. A box is provided for each question.

To get full credit, you must provide all charts and graphs with complete analysis and report.

1. Create appropriate charts (for example, a bar chart and pie chart) to display the most popular ice cream flavors in Table 2CS.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.

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.

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. Find any useful information that might be helpful in opening your own franchise. Summarize your findings in a report.

Create appropriate charts and graphs and perform the required 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.

2. Compare the sales of different brands using a bar chart. Analyze your graphs and charts.

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.

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 status of this industry. How the industry is shaping up and performing in the past two years. Find any useful information that might be helpful in opening your own franchise. Summarize your findings in a report.