Hybrid rose sales model: Forecast & Regression.

SCMG305 Global Demand Management

Task:

Using Dummy Variables

Your company manufactures and sells hybred Rose Bushes through the internet. You want to create a forecasting model that includes unit price and a whether the company offers free freight as an incentive (the dummy variable). Use Excel to create a Predictive formula generated with Excel in the space below the data table. The "Creating a dummy variable for Regression" video hyperlink to follow for this task is provided in the Assignment tab for weeks 6 & 7 assignment. Once you have completed and inserted your Regression Model (formula) below, then complete the table in Task 2 that forecasts sales by unit price and whether free freight is offered or not.

Dummy Variable (1 = Y, 0 = N)

Sales ($1000)	Unit Price	Free Freight	Sales ($1000)	Unit Price	Free Freight
820	11.00	yes	820	11.00	1
734	11.50	No	734	11.50	0
723	11.50	No	723	11.50	0
818	12.00	yes	818	12.00	1
716	12.75	No	716	12.75	0
713	13.50	No	713	13.50	0
830	14.50	yes	830	14.50	1
712	15.25	No	712	15.25	0
760	15.75	yes	760	15.75	1
659	15.75	No	659	15.75	0
594	16.25	No	594	16.25	0
610	17.20	yes	610	17.20	1
573	18.75	No	573	18.75	0
615	15.00	yes	615	15.00	1
521	15.00	No	521	15.00	0
517	16.00	No	517	16.00	0
600	16.00	yes	600	16.00	1
510	16.50	yes	510	16.50	1
450	17.00	No	450	17.00	0
475	17.00	yes	475	17.00	1
414	17.00	No	414	17.00	0
414	17.50	No	414	17.50	0
456	18.00	yes	456	18.00	1
457	18.50	yes	457	18.50	1
413	19.00	yes	413	19.00	1
387	19.25	No	387	19.25	0
363	20.00	No	363	20.00	0
375	21.00	yes	375	21.00	1
365	22.00	yes	365	22.00	1
323	22.00	No	323	22.00	0
311	22.00	No	311	22.00	0
310	22.50	yes	310	22.50	1
315	23.00	yes	315	23.00	1
300	23.50	yes	300	23.50	1
280	24.00	No	280	24.00	0
252	24.00	No	252	24.00	0

Task1:

For this task, complete the Regression Analysis and insert it in the space below. Then, create the Regression formula that will predict Sales (x $1000) based on Unit Price and the Dummy variable whether free freight is offered (yes = 1 or no = 0).

Create the Regression Formula using the Coefficients generated in the Regression Model and insert in this space:

Unit Price	Free Freight (1=Yes, 0 = No)	Forecast Sales (x $1000)	Actual Sales (x $1,000)	Forecast Error (x $1,000)	Forecast Error %
$11.50	0		$734
$11.00	1		$820
$12.00	1		$818
$15.25	0		$712
$16.00	0		$517
$17.00	1		$475
$21.00	1		$375
$19.25	0		$387
$20.00	0		$363
$22.50	1		$310
$23.50	1		$300

Task2:

In this task you will use the regression formula to Forecast sales and compare to actual data taken from the table above. The formula for Forecasted sales should be created using excel. Finally, use excel to calculate the Forecast Error (in $1000) and the percent error in the final two columns of the table. Forecast error = Forecasted sales - Actual Sales. Forecast error % = Forecast Error/Actual Sales

Data Analysis Questions

In each of the four tasks below, you will be referring back to the simple-linear regression and Multiple-linear gregression analyses you performed In the two tabs prior to this tab. You will find some of the information you need to answer these questions in your Textbook. However, it is recommended that to complete a thorough explanation of these components of the Regression analysis, you will want to refer to "Expert Resources" online.

Task1:

In your own words, explain the Coefficient of Determination. Why is it important to calculate, what it tells you about a Regression.

Task 2:

In both the Simple and Multiple linear regression analyses you completed in the first two tabs of this Excel book, you were given an F statistic. Discuss what that statistic tells you in general, and, more specifically, what does it tell you about both of the regression formulas you completed in these two tabs.

Task 3:

In both of the Regression analyses you performed in the first two tabs of this Excel workbook, you were given an: Multiple R, R Square, Adjusted R Square, and Standard Error. What do these statistics tell you in general, and in specific regarding each Regression forumula. In addition to your textbook, you may want to read about these terms online in an authoritative resource on Regression analysis.

Task 4:

In both of the Regression analyses you performed in the first two tabs of this Excel workbook, you were also given a t-statistic (your textbook calls this the "t-TEST." What does this value tell you in general about the constants you calculated in both of the regression analyses? More specifically, what does the value you calculated in both regression analyses explain about the constants. Again, you textbook has information on the t-TEST, however, you may want to do some additional research online to complete your answer to this Task.

Task 5:

In both of the Regression analyses you performed in the first two tabs of this Excel workbook, you were also given a P-value. What does this value tell you in general about the constants you calculated in both of the regression analyses? More specifically, what does the value you calculated in each regression analysis explain about the constant. Again, you textbook has information on the P-value, however, you may want to do some additional research online to complete your answer to this Task.

Multiple Linear Regression

The company you work, New Cellular, advertises monthly on both regional Southeastern television stations and in several prominent newspapers in an attempt to grow your customer base. You have two years of advertising in both media and want to build a multiple regression formula that you hope can can predict new account sales based on any combination of expenditures (TV and Print). The data from the last 36 months is below. Task 1 is to create a multiple Regression model that can predict New accounts based on the data for the last 36 months. The second task is to project new accounts based on various combinations of Television and print advertising expenditures. New accounts (the dependent variable) in 100s, while TV and Print advertising (the Independent Variables) are in $1,000s.

Period	Print Advertising Expenditures (X$1000)	TV Advertising Expenditures (X$1000)	Total Advertising Expenditures (X$1000)	New Accounts (x100)
1	3.0	3.0	6.0	0.5
2	3.5	3.5	7.0	0.6
3	4.0	4.0	8.0	0.7
4	4.5	4.5	9.0	0.7
5	6.0	2.0	8.0	0.6
6	8.0	2.0	10.0	0.7
7	8.0	4.0	12.0	0.6
8	9.0	5.0	14.0	0.8
9	10.0	8.0	18.0	0.85
10	9.0	9.0	18.0	0.85
11	8.0	10.0	18.0	1.1
12	7.0	11.0	18.0	1.5
13	9.0	11.0	20.0	1.6
14	11.0	11.0	22.0	1.6
15	13.0	11.0	24.0	1.65
16	11.0	13.0	24.0	1.9
17	5.0	18.0	23.0	2.1
18	11.0	18.0	29.0	2.3
19	6.0	13.0	19.0	1.7
20	14.0	9.0	23.0	1.8
21	15.0	18.0	33.0	2.6
22	11.0	19.0	30.0	3.1
23	14.0	18.0	32.0	3.2
24	12.0	12.0	24.0	2.7
25	15.0	8.0	23.0	2.75
26	16.0	11.0	27.0	3.15
27	15.0	13.0	28.0	3.2
28	11.0	15.0	26.0	3.95
29	9.0	18.0	27.0	4.8
30	11.0	17.0	28.0	4.85
31	16.0	8.0	24.0	3.7
32	14.0	22.0	36.0	5.1
33	10.0	22.0	32.0	5.2
34	13.0	11.0	24.0	4.6
35	14.0	8.0	22.0	4.3
36	15.0	6.0	21.0	4.1

Task 1 :

Find the correlation factor between total advertising (independent variable) and New Accounts (Dependent Variable). Is it positive or negative, strong, weak or non-existant. Finally what does this correlation factor (r) tell you about advertising in total as it applies to new accounts.

Task 2:

Create the multiple regression formula that predicts New accounts (x 100) based o two independent variables: Advertising expenditures for TV and Print (in $1000). Is one or both of the correlation factors that affect print and TV significant?

Task 3:

Step 1: Estimate New account sales using the regression formula and the TV and Print advertising expenditures in the

Step 2: Use the remainder of the table to find the optimum print and TV expenditures (in $1000) to maximize new account growth using an annual Advertising budget of $65,000 (65 x$1000)

Print Advertising (x$1000)	TV Advertising (x$1000)	New Account Forecasted Sales
11	11
15	15
10	15
15	10
20	10
10	20
25	25
20	25
25	20
30	10
10	30
20	30
30	20

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. The total year forecast, EBIT, and Total sales for the year ara all calculated for you, so all you need to do in Task 4 is use the regression analysis coefficients and a regression formula to create a forecast. Note in this forecast Q1 = Q2 = Q3 = Q4 in this example.

Period	Year	Quarter	Advertising Dollars (X$100)	Sales (units X10)	Annual Sales (Units X10)
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?

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:

In the area below, generate a Simple Linear Regression analysis. Then create a formula belo wusing 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)

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 $100). 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), the form will calculate your total year forecast, EBIT, and Full Year Sales $ (EBIT =Earnings Before Interest, Taxes, and Depreciation). Finally, as an individual, you have the capacity to produce around 14 units per week in a 52 week year, so In the large green box below the table, insert what is the maximum you should plan on spending for advertising per year to support your production, show why.

	Forecasted Sales by Quarter in Units (X10)				Do not change G119 thru J132
Advertising Expenditure per quarter (X $100)	Q1 Forecast (Units X10)	Q2 Forecast (Units X10)	Q3 Forecast (Units X10)	Q4 Forecast (Units X10)	Total Year Forecast (Units X 10)	Full Year EBIT $	Full Year Sales $
$1.0					0.00	$(600.0)	$-
$1.5					0.00	$(800.0)	$-
$3.0					0.00	$(1,400.0)	$-
$3.5					0.00	$(1,600.0)	$-
$4.0					0.00	$(1,800.0)	$-
$4.5					0.00	$(2,000.0)	$-
$5.0					0.00	$(2,200.0)	$-
$5.5					0.00	$(2,400.0)	$-
$6.0					0.00	$(2,600.0)	$-
$6.5					0.00	$(2,800.0)	$-
$7.0					0.00	$(3,000.0)	$-
$7.5					0.00	$(3,200.0)	$-
$9.0					0.00	$(3,800.0)	$-
$10.0					0.00	$(4,200.0)	$-

At a production level for you of 14 units per week in a 52 week year, what is the maximum you should spend on advertising per quarter and for the year?