Question

Interpret the forecasting model for the selected product.

• Use a simple linear regression model to show your forecast.
• If using Wild Dog Coffee Company, forecast the pounds of espresso beans needed for month 7 if the advertising budget for month 7 is $1,350. Interpret the model and respond to the following questions for Wild Dog Coffee Company:
  • How many pounds of espresso beans will the company need, on average, each day?

• Wild Dog Coffee Business Information

  Questions	Responses
  How many espresso beverages are made each hour?	30, on average
  How many ounces of espresso beans are used for each beverage?	1.5 ounces
  How many hours per day is the coffee shop open?	6:00 a.m.–8:00 p.m., Sunday–Saturday
  How many days each month is the business open?	364 days per year. The coffee shop is closed on Christmas day.

You have the following six months of data to work with for pounds of espresso beans (y) used each month and monthly advertising expenditures (x):

Espresso Bean Use and Advertising

Month	Lbs/Beans(y)	Advertising Dollars (x)
1	987	$1,050
2	1,412	$1,500
3	1,020	$1,000
4	1,140	$1,250
5	1,322	$1,500
6	1,399	$1,500
7	1253	$1,350

Answer 1

From the above table data, using Simple linear regression model to show the forecast, here software used - Minitab, steps -

Go to Stat> Regression > Fit Regression Model, then let me put Y column data as Response and X Column data as Predictors, then click OK, we will get below result,

From the Above result, Regression Equation is shown, so knowing the Advertising dollars, we can calculate Lbs/ Beans,

As P value for Advertising dollars is Zero, which is less than 0.05 or alpha level, so we can conclude that Data for Advertising Dollars is statistically significant,

Next, we will look at the R Square Value, as R square value is 95.18%, thus it sounds good, and variability of all the response data, here Lbs/Beans data are almost around the mean

Next, we will calculate How many pounds of espresso beans will the company need, on average, each day, here i have used Linear forecasting to get Lbs/Bean data for upto 12 Month, using others forecasting techniques, result will come different,

A	B	C	D	E	F- Intercept	G - Slope
2	Month	Lbs/Beans(y)	Advertising Dollars (x)	Linear Forecasting	1065.57	38.36
3	1	987	$1,050	1103.93
4	2	1,412	$1,500	1142.29
5	3	1,020	$1,000	1180.64
6	4	1,140	$1,250	1219.00
7	5	1,322	$1,500	1257.36
8	6	1,399	$1,500	1295.71
9	7	1253	$1,350	1334.07
10	8			1372.43
11	9			1410.79
12	10			1449.14
13	11			1487.50
14	12			1525.86
15			Total	15778.71
16			Forecast data- pounds of espresso beans per day	43.35 = 43

Formula

A	B	C	D	E	F- Intercept	G - Slope
2	Month	Lbs/Beans(y)	Advertising Dollars (x)	Linear Forecasting	=INTERCEPT(C3:C9,B3:B9)	=SLOPE(C3:C9,B3:B9)
3	1	987	1050	=$F$2+($G$2*B3)
4	2	1412	1500	=$F$2+($G$2*B4)
5	3	1020	1000	=$F$2+($G$2*B5)
6	4	1140	1250	=$F$2+($G$2*B6)
7	5	1322	1500	=$F$2+($G$2*B7)
8	6	1399	1500	=$F$2+($G$2*B8)
9	7	1253	1350	=$F$2+($G$2*B9)
10	8			=$F$2+($G$2*B10)
11	9			=$F$2+($G$2*B11)
12	10			=$F$2+($G$2*B12)
13	11			=$F$2+($G$2*B13)
14	12			=$F$2+($G$2*B14)
15			Total	=SUM(E3:E14)
16			Forecast data - pounds of espresso beans per day	=E15/364