Posted: April 25th, 2025

WEEK 6

Begin by opening the Excel File and read through the Scenario at the top of the page, then notice there are tabs at the bottom of the workbook that constitute your homework assignment.There are several “Tasks” in the workbook and they are on separate Tabs. Look over the tasks and make sure you understand the scenario, the data you are given, and the parameters for the regression analysis

<h

2

>Simple Lineare Regression

and you are planning your company’s sales volume in high-end graphite Fly rods for

. Your small garage entrepreneurship has been manufacturing high-end graphite Fishing Rods since

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.

is to complete a correlation analysis to understand the relationship between these two variables (Advertising dollars and Sales in units by quarter.

is to create a visual representation of the relationship between sales and Advertising dollars. Task

is to generate a simple linear regression formula that captures the trend in sales using advertising dollars as your predictor variable. Finally, task

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

is X$

0 and the sales units are 10. When you get to

, 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.

Advertising Dollars

1
2006
1
0
10

2

2
0
10

3

3
0
10

4

4
0
10

1

20

6

2
100
20

7

3
100
20

8

4
100
20

9

1

10

2

0
30

3

30

4
150
30

1
200
50

2
200
50

15

3
200
50

4
200
50
200

1

0

2

6

19

3
250
6

20

4
250
6

1.5

1

6

2
300

3
300
80

4
300
80

25

1

0

2
350
90

3
350
100

4
350

0

1

120

30

2
400

3
400

4
400
150

1

0

150

2

150

35

3
450

36

4
450
160

1

160

2
500

39

3
500

40

4
500
180

1

2
600
190

3
600
200

4
600
200

45

1

200

2
700

3
700

4
700

1

230

50

2
800

3
800
250

4
800

Task 1

Task 2

5). Finally create a Regression equation in

(see below).

Task 3.a

Task 4

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 14 units per week, what is the maximum you should plan on spending for advertising per year? Answer in the space below the table in 4.a.

0.0

0.0

$ – 0

0.0

$ – 0

0.0

$ – 0

0.0

$ – 0

0.0

$ – 0

0.0

$ – 0

0.0

$ – 0

0.0

$ – 0

0.0

$ – 0

0.0

$ – 0

0.0

$ – 0

0.0

$ – 0

0.0

$ – 0

																																																																																																																																																																																																																																																																																																																																																																																																																																																																																		It is January of 2																												0																																						1		9												20		19										200						6				Task 1				Task 2															3															4	Advertising Dollars												10			Task 4
Period	Year	Quarter	Sales (units)	Annual Sales (Units)	Sales per week
	40	0.		8
	5	200		7						100
		80		1.5
2008				1					50										30
					15
			11			150
	12		120	2.3
	13	2009
	14
	16	3.8
	17	2010										25	60
	18		250
78
	21	2011						300
	22	70
	23
	24	2		36	4.5
2012						35		90
	26
	27
	28	110		39	7.5
	29	2013						400
130
	31	140
	32	540	10.4
	33	2014					45
	34			450
		160
620	11.9
37	2015				500
38	170
	180
690	13.3
41	2016						600		190
42
43
44	780	15.0
2017				700
46		210
47	220
48			230	860	16.5
49	2018				800
240
51
52	260	980	18.8
Calculate a correlation Coefficient between sales in units by quarter and Advertising Dollars. 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 conclusion can you draw from this correlation analysis and is it reasonable to complete a regression analysis on the data that could be used to predict 2019?
Create a visual represenation of the Sales in units and Advertising Dollars in the area directly below these instructions. Start by Highlighting the data and headings, then go to Insert -> X-Y Scatter plot. Then, input the correct title, legend, and trendline.
		Task 3	Generate a Simple Linear Regression analysis with Sales in units as the dependent variable and advertising dollars as the independent variable . The regression analysis will create two coefficients that can be used to create a Forecasting formula that can be used to perdict 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.0	Task 3.a
Insert the Regression Formula Below.
Part 1 of task 4 is to use the regression formula you created above to calculate sales volume by quarter for 2025, including for the year, based on the various Advertising expenditures. Next, with a sales value of $250, a margin of $	125
Sales in Units Forecast for 2025
Advertising Expenditure per quarter	Q1 Forecast (Units)	Q2 Forecast (Units)	Q3 Forecast (Units)	Q4 Forecast (Units)	Total Year Forecast (Units)	Full Year EBIT $	Full Year Sales $
$100														$ – 0
$150
	$300
$350
$400
$450
$500
$550
$600
$650
$700
$750
$900
$1,000
Task 4.a
EBIT = Total year Forecasted units X $125 (margin) -$200 (annual overhead costs) – Advertising expense per quarter X 4 quarters X $100
EBIT is margin on units sold, minus fixed costs in this example (overhead costs) – advertising costs.

Multiple Linear Regression

. You want to build a multiple regression formula that 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. Task 2 is to project new accounts based on various combinations of Television and print advertising expenditures in the table below the regression analysis area.

Period (Month)

1

3000

100

2

3500

125

3

4000

4

4500

200

5
6000

8000

6
8000
2000

210

7
8000
4000

230

8
9000

250

9
10000
8000

10
9000
9000
18000

11
8000
10000
18000

12
7000

18000
310

13
9000
11000

14
11000
11000

15

11000

16
11000
13000
24000
400

17
11000
18000

18
11000
18000
29000

19
6000
13000

325

20
14000
9000

400

21

18000

22
11000
19000

23
14000
18000

450

24
12000
12000
24000

25
15000
8000
23000

26

11000

410

27
15000
13000

00

415

28
11000
15000

410

29
9000
18000
27000

30
11000

28000

31
16000
8000
24000

32
14000
22000

00

33
10000
22000
32000

34
13000
11000
24000

35
14000
8000
22000

36
15000
6000

360

Task 1

Task 2

Task 3

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

The company you work for, 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 three years of advertising by Period (month) in both media, along with new accounts by	Period (Month)
Print Advertising Expenditures (per period)	TV Advertising Expenditures (per period)	Total Advertising Expenditures (per period)	New Accounts (per period)
	3000				6000
	3500		7000
		4000									8000	175
	4500							9000
	2000	165
			10000
		12000
5000					14000
								18000		325
			310
330
												11000
20000	345
			22000			375
				13000					24000			410
	29000	430
420
	19000
	23000
				15000	33000			475
30000	425
	32000
	415
390
	16000		27000
			280
26000
412
17000	418
421
	360			610
445
405
380
21000
Find the correlation factor between total advertising (independent variable) and New Accounts (Dependent Variable). Discuss whether 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. Use the space below to insert the correlation factor and discuss your findings
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?
Step 1: Estimate New account sales using the regression formula and the TV and Print advertising expenditures in the table. 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

Data Analysis Questions

Task 1

Task 2

Task 3

Task 4

Task 4

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.

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

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.

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

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.

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.

Using Dummy Variables

Sales ($1000)
Unit Price
Free Freight

820
11.00
1

734
11.50
0

11.50
No

723
11.50
0

yes

818
12.00
1

No

716
12.75
0

No

713
13.50
0

yes

830
14.50
1

No

712
15.25
0

yes

760
15.75
1

15.75
No

659
15.75
0

No

594
16.25
0

610

yes

610
17.20
1

No

573
18.75
0

yes

615
15.00
1

15.00
No

521
15.00
0

No

517
16.00
0

600
16.00
yes

600
16.00
1

yes

510
16.50
1

450

No

450
17.00
0

475
17.00
yes

475
17.00
1

17.00
No

414
17.00
0

414

No

414
17.50
0

yes

456
18.00
1

yes

457
18.50
1

yes

413
19.00
1

No

387
19.25
0

No

363
20.00
0

375

yes

375
21.00
1

yes

365
22.00
1

22.00
No

323
22.00
0

22.00
No

311
22.00
0

310

yes

310
22.50
1

yes

315
23.00
1

300

yes

300
23.50
1

280

No

280
24.00
0

24.00
No

252
24.00
0

Task 1

Task 2

Unit Price

0

1

1

0

0

1

1

0

0

1

1

$300

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
	820		11.00																	yes
	734				11.50																			No
	723
	818		12.00
	716		12.75
	713		13.50
	830		14.50
	712		15.25
	760				15.75
	659
	594		16.25
	17.20
	573		18.75
	615				15.00
	521
	517				16.00
	510		16.50
					17.00
			414
	17.50
	456		18.00
	457		18.50
	413		19.00
	387		19.25
	363		20.00
	21.00
	365						22.00
	323
	311
	22.50
	315		23.00
	23.50
			24.00
	252
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:
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 = Actual Sales- Forecasted sales. Forecast error % = Forecast Error/Actual Sales
Free Freight (1=Yes, 0 = No)	Forecast Sales (x $1000)	Actual Sales (x $1,000)	Forecast Error (x $1,000)	Forecast Error %
$11.50	$734
$11.00	$820
$12.00	$818
$15.25	$712
$16.00	$517
$17.00	$475
$21.00	$375
$19.25	$387
$20.00	$363
$22.50	$310
$23.50

image1