This week and next, your studies focus on using statistical regression to forecast future demand....

Question

This week and next, your studies focus on using statistical regression to forecast future demand. Begin your studiesin theLessonssection of the classroom by completing the reading and viewing assignments. As was the case last week, do not try to complete this homework assignment without understanding the concepts involved with forecasting using statistical regression. Once you have a good understanding of how regression works, download the attached Excel Template onto your computer. You will use this template for your assignment during the next two weeks(Weeks 6 and 7). Begin by opening the Excel File and read through the Scenarioat the top of the page, then notice there are tabs at the bottom of the workbook that constitute your homework assignment.There are three different Task-groups for you to work on over the next two weeks. Next, take a look at each tab in the Excel book and scroll down through the spreadsheet, you will notice in the far left column, there are severaltasks for you to complete on each tab. Pace yourself and get some of the assignmentdone in the first week; it can bedangerous to leave all of this homework for week 7.

Next, hyperlinked below are several YouTube videos that will aid you in completing the assignment tasks. They are all short ( under 10minutes), so watch them all in successionbefore you start your homework. Then, as you start each Tab in the Excel Workbook, keep a copy of the appropriate video handy, each assignment almost exactly mirrors these videos; albeit with different scenarios.

Regression Forecasting Resources:

Using Excel to calculate a correlation coefficient || interpret relationship between variables(5:21 Minutes)
(Linear) Regression Equation Excel 2016(4:35 Minutes)
Multiple Linear Regression Equation using Excel 2016(7:15 Minutes)
Regression with a Dummy Variable using Excel(7:23 Minutes)

Instructions:

assignment-6-template-34bwvjpy.xlsx

Komalavalli · Accepted Answer

Simple Lineare Regression
	It is January of 2019 and you are planning your company's sales volume in high-end graphite Fly rods for 2019.  Your small garage entrepreneurship has been manufacturing high-end graphite Fishing Rods since 2006 for sale by independent  fishing supply stores around your region.  You have gathered the sales in units and advertising dollars for fliers and brochures you have spent since 2006 and want to complet a regression analysis that can predict sales in units for the next year based on advertising dollars spent.  You have suspected that advertising dollars (your independent variable) has had some effect on quarterly sales (your dependent variable), but you are not sure to what extent there is a direct linear correlation.  You have four tasks to complete for this first analysis.  Task 1 is to complete a correlation analysis to understand the relationship between these two variables (Advertising dollars and Sales in units.  Task 2 is to create a visual representation of the relationship between sales and Advertising dollars. Task 3 is to generate a simple linear regression formula that captures the trend in sales using advertising dollars as your predictor variable.  Finally, task 4 is to generate a forecast based on the regression formula for 2019.   Be extra careful with the units for Advertising dollars and Sales as the table for Advertising Dollars is X$100 and the sales units are 10.  When you get to Task 4, inputting the wrong unit value will throw off the calculations of EBIT.  Before getting started on the four tasks below, watch the first video hyperlinked in the Assignments Tab.
		Period	Year	Quarter	Advertising Dollars (X$100)	Sales                (units)	Annual Sales (Units)
		1	2006	1	0	1
		2		2	0	1
		3		3	0	1
		4		4	0	1	4
		5	2007	1	1	2
		6		2	1	2			 
		7		3	1	2
		8		4	1	2	8
		9	2008	1	1.5	3
		10		2	1.5	3			 
		11		3	1.5	3
		12		4	1.5	3	12
		13	2009	1	2	5
		14		2	2	5
		15		3	2	5
		16		4	2	6	21
		17	2010	1	2.5	6
		18		2	2.5	6
		19		3	2.5	6
		20		4	2.5	6	24
		21	2011	1	3	7
		22		2	3	7
		23		3	3	8
		24		4	3	8	30
		25	2012	1	3.5	9
		26		2	3.5	9
		27		3	3.5	10
		28		4	3.5	11	39
		29	2013	1	4	12
		30		2	4	13
		31		3	4	14
		32		4	4	15	54
		33	2014	1	4.5	15
		34		2	4.5	15
		35		3	4.5	16
		36		4	4.5	16	62
		37	2015	1	5	16
		38		2	5	17
		39		3	5	18
		40		4	5	18	69
		41	2016	1	6	19
		42		2	6	19
		43		3	6	20
		44		4	6	20	78
		45	2017	1	7	20
		46		2	7	21
		47		3	7	22
		48		4	7	23	86
		49	2018	1	8	23
		50		2	8	24
		51		3	8	25
		52		4	8	26	98
						 
	Task 1	There are two options for calculating the Correlation analysis.  You can use either the Data->Analysis->Correlation Analysis or use the function Correll as you saw in the Video inserted in the Assignments section.  Then,  explain the correlation factor you have found.  Is it a postive correlation?  Would you consider it to be a strong, medium, or weak correlation?  Finally, what have you learned from this analysis and is it reasonable to complete a regression analysis on the data that could be used to predict 2019?  
		0.9836621188
		Correlation factor value is 0.98, which means strong postive positive correlation between two variables Advertising dollar and Sales									 	 
			If advertising dollar increases then sales will also increases 
			yes , since it is a positive strong correlation 
				If we use these variable for regression analysis to predict it will give us reasonable prediction
	Task 2	Create a visual represenation of the Sales in units and Advertising Dollars.  Highlight the data and headings, then go to Insert -> X-Y Scatter plot.  Input the correct title, legend, and trendline
	Task 3	Generate a Simple Linear Regression analysis.  Then create a formula using the regression coefficients to create a formula that predicts sales (dependent variable) based on Advertising Dollars Spent in a Quarter (Independent Variable).  Is the regression formula "Significant"  (Hint: is the P-value for the Slope of the Regression line below 0.05)
		SUMMARY OUTPUT
		Regression Statistics
		Multiple R	0.9836621188
		R Square	0.9675911639
		Adjusted R Square	0.9669429872
		Standard Error	1.3967282794
		Observations	52
		ANOVA
			df	SS	MS	F	Significance F
		Regression	1	2912.2075056754	2912.2075056754	1492.7891304395	0
		Residual	50	97.5424943246	1.9508498865
		Total	51	3009.75
		Sales 	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95%	Upper 95%
		Intercept	-0.8521566402	0.3682790538	-2.3138884262	0.0248229978	-1.5918668748	-0.1124464056	-1.5918668748	-0.1124464056
		Advertising Dollars	3.2776674234	0.0848331616	38.6366293877	6.66414323490119E-39	3.1072750071	3.4480598397	3.1072750071	3.4480598397
		Insert the Regression Formula Below.  Second, answer whether this analysis show the coefficient for Advertising dollars to be Statistically Significant, and how do you know? 
		Regression Formula y = -0.85+3.27 xt ; y = Sales, xt = advertising for different time period .Advertising Dollars is significant at 1% level of significance because p-value 0,which is less than 0.01     
	Task 4	Part 1 of task 4 is to use the regression formula you created above to calculate sales volume (x 10 units) by quarter for 2019, including for the year, based on the various Advertising expenditures (x $10).  Next, with a sales value of $250, a margin of $125 per unit, and an annual overhead costs per year of $200 per year (excluding advertising costs), calculate the EBIT (Earnings Before Interest, Taxes, and Depreciation for each level of advertising) and Sales $ per year for each level of  Advertising Expenditure.   Be extra careful of your units.  You have a capacity to produce around 2 units per week, what is the maximum you should plan on spending for advertising per year?
			Sales in Units (X100)
		Advertising Expenditure per quarter (X $100)	Q1 Forecast (Units)	Q2 Forecast (Units)	Q3 Forecast       (Units)	Q4 Forecast (Units)	Total Year Forecast (Units)	Full Year EBIT $	Full Year Sales $
		$1.0	2.4	2.4	2.4	2.4	9.7	$   2,775.0	$   2,420.0
		$1.5	4.1	4.1	4.1	4.1	16.2	$   4,818.8	$   4,055.0
		$3.0	9.0	9.0	9.0	9.0	35.8	$   10,950.0	$   8,960.0
		$3.5	10.6	10.6	10.6	10.6	42.4	$   12,993.8	$   10,595.0
		$4.

This week and next, your studies focus on using statistical regression to forecast future demand. Begin your studiesin the Lessons section of the classroom by completing the reading and viewing...

Solution

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment