pls follow instructions

1

WEEK # 3 – EXERCISES CHAPTER # 2

Exercise Ch2-1: Level 1 – Analyzing Sales for Crèmes Ice Cream

Judd Hemming is the eastern regional marketing manager for Crèmes Ice Cream. Each quarter,

he completes two separate analyses: an analysis comparing ice cream flavor sales volumes from

all regional locations with the same quarter sales volumes from the previous year, and an analysis

comparing total sales in dollars, including mean, median, mode, and standard deviation, of sales

by store.

Sales by Flavor in Gallons

The first analysis, sales by flavor, compares the total quantities sold in gallons. The data collected

provides for each flavor the number of pints, gallons, and 10-gallon tubs sold for all stores. Pints

and gallons are sold directly to the public, whereas 10-gallon tubs are used for in-store sales of

cones, cups, and specialty items such as sundaes and banana splits. To eliminate any impact of

pricing changes or special promotions, Judd uses the ice cream volumes in gallons to compare

sales by flavor. Judd has asked for your help this quarter in completing this analysis. He created

two workbook files:

– StartFileCreme.xlsx, which contains the current quarter’s sales on a worksheet named

Flavors

– StartFileHCreme.xlsx, which contains the corresponding historical quarterly data for the

previous year on a worksheet named HFlavors.

For convenience, the flavors in both data sets are in identical order except for two new flavors

introduced this year, which appear at the bottom of the current data set.

Keep in mind the following conversions when analyzing this data:

• There are 8 pints per gallon.

• Each tub holds 10 gallons.

Also, when calculating values for 10-gallon tubs, Judd has asked you to use the convention of

rounding down the values to the nearest whole tub.

Sales by Store in Dollars

The second analysis you need to complete is to summarize sales in dollars by store and compare

the result with the previous year’s sales. The Stores worksheet in the same workbook contains

sales by store for the current quarter in dollars rounded to the nearest thousand dollars. You need

to calculate some basic statistics for store sales. In the same quarter of the previous year, these

values were:

• Mean: $8,817

• Median: $8,000

• Mode: $5,500

• Standard Deviation: $2,920

When writing formulas, be certain to use the most efficient method, including the use of functions

as well as relative and absolute cell referencing.

Complete the following:

1. Open the workbook named StartFileCreme.xlsx attached with the data of the assignment, and

then save it as 2-1-Crème-Current-Sales-Analysis-Your-Name.xlsx.

2. On the Flavors worksheet, for the current quarter, calculate the total number of gallons sold

for each favor. Place this calculation in the column adjacent to the data provided (Column E). Above

the list of flavors, in a separate area, list and clearly label the conversion values. Remember to

round down the tub quantities to the nearest whole tub when completing the calculation.

2

WEEK # 3 – EXERCISES CHAPTER # 2

Total number of gallons sold =

#Pints Containers Sold Current Qtr / Pints per Gallon +

#Gallons Containers Sold Current Qtr +

#Tubs Used Current Qtr * Gallons per Tub

Use Absolute reference in the cells yellow highlight

3. Add another column in which to enter the total number of gallons from last year. Copy the

values for the total amount of ice cream sold for the corresponding flavor for last year from the

StartFileHCreme.xlsx workbook in the Chapter 2 folder into the newly added column of the Flavors

worksheet.

4. Calculate the overall total and mean number of gallons sold for all flavors for this quarter and

for this quarter last year. (Note: Include all flavors in your calculation, whether or not there are sales

for them in a given year.). Use the SUM and AVERAGE Function

5. Calculate for each flavor the percent of total gallons Current this flavor represents compared

with total sales for the current quarter of all flavors. Copy this formula to the adjacent column to

calculate the percent of total gallons this flavor represents compared with historical total sales.

6. In the two adjacent columns, calculate for each flavor and for the totals the difference and percent

difference in sales. Assume a positive value represents an increase in sales. Flavors without sales

in the previous year should be left blank. (These cells should be completely empty.)

TROUBLESHOOTING: In order to complete this step successfully, pay attention to the order of

precedence rules when writing the formula. Percent difference is defined as the difference between

the new values minus the old value divided by the old value, so specifying the order in which the

operations must occur is critical.

7. Switch to the Stores worksheet. Calculate the total sales, mean, median, mode, and standard

deviation for this data set. Label the cells so that they can be easily identified.

8. On the same worksheet, set up a table to analyze the change and percent change of each of

these statistical values as compared with the historical values.

Based on the changes, explain on the worksheet (just below your analysis) whether the stores are

doing better this year, and if sales in stores are more or less likely to vary from mean sales than

they did last year.

9. Based on sales to all stores and total gallons sold for the current quarter, what is the price of a

gallon of ice cream on average? Write a formula to determine this value and place it below the

statistical analysis on the Stores worksheet.

10. Add appropriate worksheet titles and formatting to make the worksheets easy to read.

11. Add your name and date at the end of the exercise

12. Save and close the 2-1-Crème-Current-Sales-Analysis-Your-Name.xlsx workbook.

3

WEEK # 3 – EXERCISES CHAPTER # 2

The solution should be similar to:

In the Flavor worksheet:

In the Store worksheet:

4

WEEK # 3 – EXERCISES CHAPTER # 2

Exercise Ch2-2: Level 2- Analyzing Demographic Data for Your

Restaurant.

You have recently decided to open a restaurant that you plan to name “Select your Restaurant

Name”. In your restaurant, you plan to feature specialty desserts, along with fine cuisine. One

critical decision you must make is where to locate the restaurant. Right now, you are considering

two different locations—one near a large retail area on the fringe of several affluent suburbs (site

A), and the other in the downtown district (site B). Before making the decision, you hired a local

market research firm to provide you with some demographics of the areas and the specific dining

habits of the local population that frequent other restaurants in these areas. You have entered the

raw results of this research on several worksheets in the StartFileYourRestaurante.xlsx

workbook.

Each worksheet (SiteA and SiteB) contains the detailed responses of each of the participants of

the study, including questions about their age, their income, and the number of meals and

desserts they eat outside of their homes per month.

Table 2.15 shows the breakdown of the age categories and corresponding income level closest

to the respondent’s own income.

Complete the following:

1. Open the workbook named StartFileYourRestaurante.xlsx attached with the data of the

assignment, and then save it as 2-2-YourRestaurante-Demog-Analysis-Your-Name.xlxs.

2. On the SiteA and SiteB worksheets, rank each respondent by the number of meals they eat

out per month and the number of desserts they eat out per month, respectively— ranking from

most meals and desserts out to the least. Freeze the panes of the window on each worksheet to

make the category headings at the top visible at all times.

3. On a separate worksheet named Compare, calculate the difference and percent difference of

the mean and standard deviation for the data sets (A and B) for the number of meals and number

of desserts per month obtained. On the same worksheet, set up a table to list the four highest

number of desserts per month from each data set (A and B) and the ten lowest number of meals

eaten out by respondents for each of the data sets (A and B). Below the data, discuss how these

values differ between the data sets, and recommend either A or B for further analysis, highlighted

in pink.

5

WEEK # 3 – EXERCISES CHAPTER # 2

4. On a separate worksheet named Summary, determine the following for the location you think

should be selected:

• The total number of respondents

• The total number of respondents with incomes at or above $90,000

• The total number of respondents who eat fewer than three desserts out each month

• The total number of meals eaten out per month reported by respondents who earn at

least $50,000

• The total number of desserts per month reported by respondents who are in the

31 to 35 or over 35 age categories

5. Include appropriate titles, labels, and formatting so that the worksheets are easy to read.

6. Add your name and date at the end of the exercise

7. Save and close the 2-2-YourRestaurante-Demog-Analysis-Your-Name.xlxs workbook.

6

WEEK # 3 – EXERCISES CHAPTER # 2

NOTE:

For Step 2

For SiteA Calculate the RANK for Column F & G

For SiteB Calculate the RANK for Column F & G

7

WEEK # 3 – EXERCISES CHAPTER # 2

For Step 3

Add a new Tab to Compare:

For Step 4

Add a new Tab to Summary:

Week # 3 – Chapter # 2 Assignment

This week we cover the levels 1 and 2 of Chapter Two “Solving Problems with Statistical

Analysis Tools” of your textbook. Before you start on the Excel exercises be sure to read

and make sure you understand the Chapter Introduction and VERY IMPORTANT, the

Case Scenario on page 79.

Power Points are available in Blackboard Lectures and, video tutorials guiding you

through the Learning part of each level of each chapter are available on youtube.com.

See your course Syllabus for the link.

WATCH THE VIDEOS– WATCH THE VIDEOS — WATCH THE VIDEOS.

Use the Chapter # 2 – Videos presentation to see the videos if you prefer.

Let’s get started:

==========================================================================================

Week # 3 – Chapter # 2 Assignment

Level 1: Objectives

• Understand basic concepts related to statistics (Mean, Median, Mode and Standard

Deviation) (page 80 – 84)

• Specify the precision of values using the ROUND function (page 84 – 88)

• Copy and paste information in a worksheet using Paste Special options (Page 90)

• Calculate basic statistics: arithmetic mean, mode, median, standard deviation (page 93 –

94)

• Manage large worksheets by freezing panes and splitting the window (page 95 – 100)

Level One: LEARNING

The material covered in Level 1 (pages 80-100) Use Statistical Functions to compare Data

Values – Understanding the fundamentals of Statistics.

The CH02 Level 1 Figures.xlsx file in Lectures (Student Data Files Chapter Two) replicates all the

Figures in this section so you can easily follow along with the textbook discussions

You should read learning material on pages 80-100 and the youtube.com video tutorial for

this level.

Use the attached presentation in Level 1 to see the video Level 1: “Using Statistical

Functions to Compare Data Values”

Or use the link:

dex=4

After you, understand the material:

Level One: PRACTICE:

Using the Week # 3 – Chapter 2 – Exercises.pdf

Make the Exercise Ch2-1: Level 1 – Analyzing Sales for Crèmes Ice Cream

Post your completed file to Blackboard for review and grading.

The file Name should be 2-1-Crème-Current-Sales-Analysis-Your-Name.xlsx

POST A COPY OF YOUR WORK TO BLACKBOARD FOR REVIEW AND GRADING (40 POINTS)

==================================================================

Week # 3 – Chapter # 2 Assignment

Level 2: Objectives

• Use the Quick Analysis tool to aggregate data (page 102 – 105)

• Evaluate the rank of each value in a data set (page 105 – 107)

• Determine the highest and lowest values in a data set (page 107 – 110)

• Determine the number of items that meet specified criteria (page 111 – 116)

• Determine a total value for items that meet specified criteria (page 116 – 121)

Level Two: LEARNING

The material covered in Level 2: Organizing and Evaluating Different Data Groupings on pages

102 to 121. The CH02 Level 2 Figures.xlsx file in Lectures (Student Data Files Chapter Three)

replicates all of the Figures in this section so you can easily follow along with the textbook

discussions.

You should read the learning material covered on pages 102-121 and the youtube.com video

tutorial for this level.

Use the attached presentation in Level 2 to see the video Level 2: Organizing and

Evaluating Different Data Groupings

Or use the link:

dex=5

After you, understand the material:

Level Two: APPLYING

Using the Week # 3 – Chapter 2 – Exercises.pdf

Make the Exercise Ch2-2: Level 1 – Level 2- Analyzing Demographic Data for Your

Restaurant.

Post your completed file to Blackboard for review and grading.

The file Name should be 2-2-YourRestaurante-Demog-Analysis-Your-Name.xlsx.

POST A COPY OF YOUR WORK TO BLACKBOARD FOR REVIEW AND GRADING (40 POINTS)

==================================================================

Week # 3 – Chapter # 2 Assignment

Let me know if there is anything you don’t understand — the earlier the better. ALWAYS attach a copy of

yours excel file clearly indicating where you have a problem.

The solution files that you need to attach are:

2-1-Crème-Current-Sales-Analysis-Your-Name.xlsx (40 points)

2-2-YourRestaurante-Demog-Analysis-YourName.xlsx (40 points)

After you understand the material and complete the exercises: Answer the

questions in the Practical Test – Chapter# 2 (20 POINTS)