Freezy's Yogurt Shoppe sells a variety of single-serve frozen yogurt flavors and toppings. The owner of Freezy's is trying to forecast its frozen yogurt sales for the upcoming year. The owner suspects that the number of times that Freezy's is mentioned in any news or social media each week helps to predict the sales volume in quarts of its frozen yogurt. The owner has gathered the weekly sales volume of frozen yogurt from the past year and the number of media mentions in those same weeks. The owner used Excel to analyze the data. An excerpt from that analysis follows.

	A	B	C	D	E	F
1	Week	Number of Media Mentions	Frozen Yogurt Sales Volume (Quarts)
2	1	434	719		Slope	0.6091
3	2	154	525		Intercept	437
4	3	75	497		RSQ	0.949
5	4	472	716
6	5	373	639		Media Mentions	Expected Volume
7	6	340	626		600	802
8	7	369	679		625	817
9	8	277	611		650	833
10	9	444	681
11	10	397	660

1. In this data set, what field is the predictor variable (x)? What field is the outcome variable (y)?

The predictor variable (x) is A: Media Mentions B. The sales volume of frozen yogurt quarts

and the outcome variable (y) is A: Media Mentions B. The sales volume of frozen yogurt quarts

2. What function(s) did the owner use to prepare the analysis in Excel?

The owner used

FORECAST.LINEAR, SKEW, INTERCEPT, and RSQ

FORECAST.LINEAR, SLOPE, INTERCEPT, and PROB

FORECAST.LINEAR, SLOPE, INTERCERPT, and RANKAVG

FORECAST.LINEAR, SLOPE, INTERCEPT, and RSQ

functions to prepare the analysis in Excel.

3. What steps would be involved in preparing the analysis in Excel?

The steps below represent the steps you would take to create the analysis using Excel. Place the items below in proper sequential order. 1-8

		Sequence of events
Next, choose the Formulas tab on the ribbon, then More Functions, and then Statistical. An alphabetized list of available statistical functions will appear. Click on SLOPE, INTERCEPT, or RSQ depending on which function you want.		1,2,3,4,5,6,7,8
On the ribbon, choose the Formulas tab, then More Functions, and then Statistical. An alphabetized list of available statistical functions will appear. Scroll down the list until you get to FORECAST.LINEAR. Click on it.		1,2,3,4,5,6,7,8
To find the slope, intercept, and R-squared value: First, select the cell where you want to see the answer.		1,2,3,4,5,6,7,8
Use a data set with the predictor variable (x) in the column to the left of the outcome variable (y).		1,2,3,4,5,6,7,8
Drag and drop the fill handle to copy the function to the cells below.		1,2,3,4,5,6,7,8
Go to an area of the worksheet where you would like to display forecasted values, and then type the values for which you would like predictions. Select the first cell where you would like to see the forecast.		1,2,3,4,5,6,7,8
A dialog box will open, requesting the range of cells for the data about y and x. Input the information either by typing or selecting the cells with your cursor. Then click ok. The resulting slope, intercept, or R-squared value will be returned in the destination cell.		1,2,3,4,5,6,7,8
The FORESCAST.LINEAR dialog box will open. Cell addresses are needed for three pieces of information. You may either type in the cell addresses or select them with your cursor. First, input the cell address for the x-value for which you want the prediction. Second, input the range of cells that contains the data about the y-variable. Finally input the range of cells that contains the data about the x variable. Make these cell references absolute by adding dollar signs to the addresses so that you can copy the FORECAST. LINEAR function to the other cells where you want predictions. Finally, click OK.		1,2,3,4,5,6,7,8

4. Does it appear that the number of media mentions will be a good predictor of sales? How do you know?

It appears that media mentions

will be

will not be

a good predictor of sales because

as media mentions decrease, so does volume

as media mentions increase, so does volume

.

5. How many quarts of frozen yogurt could Freezy's expect to sell if there were 625 mentions of Freezy's frozen yogurt in news and social media in a given week?

If there were 625 mentions in news and social media in a given week, the number of quarts of frozen yogurt Freezy's could expect to sell is

.

6. Which cell(s) could you change in the Excel worksheet to predict quarts sold if there were 700 mentions in news and social media in a given week?

You could enter 700 in

Cells B2:B11

Cells E7, E8, or E9

Cells F7, F8, or F9

to predict quarts sold for a new volume.