Unit 1 Group Assignment

Regression analysis is often utilized to understand which independent variables are related to the dependent variable and to explore the relationship of these relationships. An independent variable cannot be changed by any other variable in the relationship. A dependent variable is a variable being studied by the analysis. For example, if we want to forecast the value of gold, the price of a commodity (gold) is the dependent variable within the study. An independent variable may be a seasonality to which the price of gold will depend.

There are several steps in analyzing a regression analysis, however, we will discuss the first two steps in regression analysis:

Thefirst stepin evaluating a regression model is to ask whether the model is logical. By this we mean, does the model show the direction of the relationship that is consistent with logic? If business/economic logic tells us that there should be a direct relationship the algebraic sign should be positive. If logic tells us there should be an inverse relationship the algebraic sign should be negative. This is a very important step in the evaluation because we never would want to use a relationship that does not conform to logic. For example, if we were looking at car sales as a function of the interest rate, logic tells us the relationship would be inverse. When interest rates fall we expect an increase in car sales and vice versa. If we did a regression and got a positive slope (coefficient) the model would simply not be consistent with what we know about the industry. In which case we shouldnot use the model.

Thesecond stepis to determine whether the relationship is statistically significant at the desire level of confidence. Can you confidently understand the relationship between the dependent and independent variable?

Barbara Lynch is the product manager for a line of skiwear produced by HeathCo Industries and privately branded for sale under several different names, including Northern Slopes and Jacque Monri. A new part of Ms. Lynch's job is to provide a quarterly forecast of sales for the northern United States: a 10-year sales history is shown below: (c4p11)

	Sales ($000)
	Year	1st Quarter	2nd Quarter	3rd Quarter	4th Quarter
	2007	$ 72,962	$ 81,921	$ 97,729	$ 142,161
	2008	145,592	117,129	114,159	151,402
	2009	153,907	100,144	123,242	128,497
	2010	176,076	180,440	162,665	220,818
	2011	202,415	211,780	163,710	200,135
	2012	174,200	182,556	198,990	243,700
	2013	253,142	218,755	225,422	253,653
	2014	257,156	202,568	224,482	229,879
	2015	289,321	266,095	262,938	322,052
	2016	313,769	315,011	264,939	301,479

a. Ms. Lynch has hired you to help with the forecasting effort. First, she would like you to prepare time-series plot of the data and to write her a memo indicating what the plot appears to show and whether it seems likely that a simple linear trend would be useful in preparing forecasts.

The plot of the sales data show an upward trend and some seasonality. A linear trend model should capture the overall movement in sales but will not capture what appears to be seasonal fluctuations.

b. Do your regression results indicate to you that there is a significant trend to the data? Explain why or why not.

The results show a significant positive trend as demonstrated by the Excel regression results below. Since the graph indicates upward movement in the sales data a positive trend is expected and thus a one tailed t-test is appropriate. We see the t-Stat is VERY large and P/2 = 0.000.

	Coefficients	Std Error	t Stat	P-value	P/2
Intercept	88,741.012	8,043.906	11.032	0.000	0.000
Time	5,362.623	341.907	15.684	0.000	0.000

To see more about developing a linear regression model, please watch thisYouTube video.

In this assignment you are to act as the manager of the Mid-Valley Travel Agency (MVTA), which has offices in 12 cities. The company believes that its monthly airline bookings are related to the mean income in those cities and has collected the following data: (c4p10)

Location	Bookings	Income		Location	Bookings	Income
1	1,098	43,299		7	855	27,482
2	1,131	45,021		8	1,054	33,025
3	1,120	40,290		9	1,081	34,687
4	1,142	41,893		10	982	28,725
5	971	30,620		11	1,098	37,892
6	1,403	48,105		12	1,387	46,198

1. Develop a linear regression model of monthly airline bookings as a function of income.
2. Develop a scatter plot of the data and trend line.
3. Use the process described above to evaluate your results.
4. How would you state your findings to upper management?

Content Applies the linear regression model to the data set and explains what these findings mean.	30
Content Thoroughly evaluates the regression results analytically and theoretically.	20
Content Examines the findings and relays them to management in a meaningful way	20
Content Creates the linear regression and applies it correctly	15
Written Mechanics or Oral Presentation (for onground) Responses are written at a college level with proper sentence and paragraph structure, proper punctuation, spelling, and grammar as required in this assignment.	15
Total	100

I need answer with excel formula