SCMG305 BargeT Module 6 Assignment - Artifact 2
xlsx
keyboard_arrow_up
School
St. Petersburg College *
*We aren’t endorsed by this school
Course
MISC
Subject
Industrial Engineering
Date
Jan 9, 2024
Type
xlsx
Pages
30
Uploaded by BarristerArt12404
Period
Year
Quarter
1
2006
1
0
2
2
0
3
3
0
4
4
0
5
2007
1
1
6
2
1
7
3
1
8
4
1
9
2008
1
1.5
10
2
1.5
11
3
1.5
12
4
1.5
13
2009
1
2
14
2
2
15
3
2
16
4
2
17
2010
1
2.5
18
2
2.5
19
3
2.5
20
4
2.5
21
2011
1
3
22
2
3
23
3
3
24
4
3
25
2012
1
3.5
26
2
3.5
27
3
3.5
28
4
3.5
29
2013
1
4
It is January of 2019 and you are planning your company's sales volume in high-end graphite Fly rod
high-end graphite Fishing Rods since 2006 for sale by independent fishing supply stores around you
fliers and brochures you have spent since 2006 and want to complet a regression analysis that can p
You have suspected that advertising dollars (your independent variable) has had some effect on qua
extent there is a direct linear correlation. You have four tasks to complete for this first analysis. Ta
between these two variables (Advertising dollars and Sales in units. Task 2 is to create a visual repr
3 is to generate a simple linear regression formula that captures the trend in sales using advertising
based on the regression formula for 2019. The total year forecast, EBIT, and Total sales for the yea
regression analysis coefficients and a regression formula to create a forecast. Note in this forecast Advertising Dollars (X$100)
30
2
4
31
3
4
32
4
4
33
2014
1
4.5
34
2
4.5
35
3
4.5
36
4
4.5
37
2015
1
5
38
2
5
39
3
5
40
4
5
41
2016
1
6
42
2
6
43
3
6
44
4
6
45
2017
1
7
46
2
7
47
3
7
48
4
7
49
2018
1
8
50
2
8
51
3
8
52
4
8
Task 1
Advertising Dollars (X$100)
1
Sales (units X10)
0.983662118789938
1
There are two options for calculating the Correlation analysis. You can use either the Da
function Correll as you saw in the Video inserted in the Assignments section. Then, expl
a postive correlation? Would you consider it to be a strong, medium, or weak correlatio
analysis and is it reasonable to complete a regression analysis on the data that could be Advertising Dollars (X$100)
Sales (units X10)
The correlation betwe
that the higher the
Task 2
Task 3
Insert Regression Analysis in B90
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.983662118789938
R Square
0.96759116394231
Adjusted R Square
0.966942987221156
Standard Error
1.39672827940606
Observations
52
ANOVA
df
SS
MS
Regression
1
2912.207506
2912.20750567537
Residual
50
97.54249432
1.95084988649262
Total
51
3009.75
Create a visual represenation of the Sales in units and Advertising Dollars. Highlight the Scatter plot. Input the correct title, legend, and trendline
In the area below, generate a Simple Linear Regression analysis. Then create a formula b
create a formula that predicts sales (dependent variable) based on Advertising Dollars Sp
the regression formula "Significant" (Hint: is the P-value for the Slope of the Regression
0
1
2
3
4
5
0
5
10
15
20
25
30
Sales (units) against Advertising
Advertising Dollars (X
Sales (units)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Coefficients
Standard Error
t Stat
Intercept
-0.852156640181612 0.368279054
-2.31388842620392
Advertising Dollars (X$100)
3.27766742338252 0.084833162
38.6366293876617
Task 4
Note only complete the Q1, Q2, Q3, & Q4 forecast in this table (Note mak
Forecasted Sales by Quarter in Units (X10
$1.0
2.43 2.43 2.43 $1.5
4.06 4.06 4.06 $3.0
8.98 8.98 8.98 $3.5
10.62 10.62 10.62 $4.0
12.26 12.26 12.26 $4.5
13.90 13.90 13.90 $5.0
15.54 15.54 15.54 $5.5
17.18 17.18 17.18 $6.0
18.81 18.81 18.81 $6.5
20.45 20.45 20.45 $7.0
22.09 22.09 22.09 $7.5
23.73 23.73 23.73 $9.0
28.65 28.65 28.65 $10.0
31.92 31.92 31.92 Insert the Regression Formula Below. Second, answer whether this analysis show the co
Statistically Significant, and how do you know
? 1. Equation = -0.852156640181612+3.27766742338252*Advertising Dollars (X$100) value of the slope is 0.02482 and is less than 0.05, thus the regression line is significant.
Part 1 of task 4 is to use the regression formula you created above to calculate sales volu
for the year, based on the various Advertising expenditures (x $100). Next, with a sales v
an annual overhead costs per year of $200 per year (excluding advertising costs), the for
and Full Year Sales $ (EBIT =Earnings Before Interest, Taxes, and Depreciation). Finally, a
produce around 14 units per week in a 52 week year, so In the large green box below the
plan on spending for advertising per year to support your production, show why.
Advertising Expenditure per quarter (X $100)
Q1 Forecast (Units X10)
Q2 Forecast (Units X10)
Q3 Forecast (Units X10)
221.849184018008
1
1
1
1
4
2
2
2
2
8
3
3
3
3
12
5
5
5
6
21
6
6
6
6
24
7
7
8
8
30
9
9
10
11
39
12
ds for 2019. Your small garage entrepreneurship has been manufacturing ur region. You have gathered the sales in units and advertising dollars for predict sales in units for the next year based on advertising dollars spent. arterly sales (your dependent variable), but you are not sure to what ask 1 is to complete a correlation analysis to understand the relationship resentation of the relationship between sales and Advertising dollars. Task g dollars as your predictor variable. Finally, task 4 is to generate a forecast ar ara all calculated for you, so all you need to do in Task 4 is use the Q1 = Q2 = Q3 = Q4 in this example.
Sales (units X10)
Annual Sales (Units X10)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
13
14
15
54
15
15
16
16
62
16
17
18
18
69
19
19
20
20
78
20
21
22
23
86
23
24
25
26
98
ata->Analysis->Correlation Analysis or use the lain the correlation factor you have found. Is it on? Finally, what have you learned from this used to predict 2019? een advertising dollars and sales is 0.98 and this is a strong positive correlation. This implies e amount spent on advertising dollars, the higher will be the unit of sales to be reported.
F
Significance F
1492.78913044 6.6641432E-39
data and headings, then go to Insert -> X-Y belo wusing the regression coefficients to pent in a Quarter (Independent Variable). Is n line below 0.05)
5
6
7
8
9
g Dollars (X$100)
X$100)
P-value
Lower 95%
Upper 95%
Lower 95.0%
Upper 95.0%
0.024822998065 -1.5918668895 -0.1124463909 -1.5918668895 -0.112446
6.66414323E-39 3.1072750037 3.4480598431
3.1072750037 3.4480598
ke the Q1 forecast = Q2 = Q3 = Q4)
0)
Do not change G119 thru J132
2.43 9.70 $ 11,527.6 $ 24,255.1 4.06 16.26 $ 19,521.7 $ 40,643.4 8.98 35.92 $ 43,504.2 $ 89,808.5 10.62 42.48 $ 51,498.4 $ 106,196.8 12.26 49.03 $ 59,492.6 $ 122,585.1 13.90 55.59 $ 67,486.7 $ 138,973.5 15.54 62.14 $ 75,480.9 $ 155,361.8 17.18 68.70 $ 83,475.1 $ 171,750.1 18.81 75.26 $ 91,469.2 $ 188,138.5 20.45 81.81 $ 99,463.4 $ 204,526.8 22.09 88.37 $ 107,457.6 $ 220,915.2 23.73 94.92 $ 115,451.7 $ 237,303.5 28.65 114.59 $ 139,434.3 $ 286,468.5 31.92 127.70 $ 155,422.6 $ 319,245.2 oefficient for Advertising dollars to be 2.p-
ume (x 10 units) by quarter for 2019, including value of $250, a margin of $125 per unit, and rm will calculate your total year forecast, EBIT, as an individual, you have the capacity to e table, insert what is the maximum you should Q4 Forecast (Units X10)
Total Year Forecast (Units X 10)
Full Year EBIT $
Full Year Sales $
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Period
1
3.0
2
3.5
3
4.0
4
4.5
5
6.0
6
8.0
7
8.0
8
9.0
9
10.0
10
9.0
11
8.0
12
7.0
13
9.0
14
11.0
15
13.0
16
11.0
17
5.0
18
11.0
19
6.0
20
14.0
21
15.0
22
11.0
23
14.0
24
12.0
The company you work, New Cellular, advertises monthly on both regional Southeaste
have two years of advertising in both media and want to build a multiple regression fo
Print). The data from the last 36 months is below. Task 1 is to create a multiple Regre
project new accounts based on various combinations of Television and print advertisin
Independent Variables) are in $1,000s. Print Advertising Expenditures (X$1000)
25
15.0
26
16.0
27
15.0
28
11.0
29
9.0
30
11.0
31
16.0
32
14.0
33
10.0
34
13.0
35
14.0
36
15.0
Task 1
Correlation
0.78528470767588
Task 2
The two correlation factors affecting print and TV are significant.
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.788020151164253
R Square
0.620975758640932
Adjusted R Square
0.598004592497958
Standard Error
0.957320526063075
Observations
36
Find the correlation factor between total advertising (independent variable) and New Acc
Finally what does this correlation factor (r) tell you about advertising in total as it applies
The correlation between total advertising and new accounts is .785. The correlation is clo
means that the higher the expenditure on advertising, the higher the new account sales. Create the multiple regression formula that predicts New accounts (x 100) based o two in
both of the correlation factors that affect print and TV significant?
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
ANOVA
df
Regression
2
Residual
33
Total
35
Coefficients
Intercept
-0.91981820941575
FORMULAR
Print Advertising Exp
0.174761590731675
TV Advertising Expen
0.135674003478575
EQUATION = 0.17*Print advertising expenditure + 0.13*TV advertis
Task 3
11
15
10
15
20
10
25
20
25
30
10
20
30
65
65
Y=0.17x
1
+ 0.13x
2
-0.91
Step 1: Estimate New account sales using the regression formula and the TV and Print adv
Step 2: Use the remainder of the table to find the optimum print and TV expend
budget of $65,000 (65 x$1000)
Print Advertising (x$1000)
3.0
6.0
0.5
3.5
7.0
0.6
4.0
8.0
0.7
4.5
9.0
0.7
2.0
8.0
0.6
2.0
10.0
0.7
4.0
12.0
0.6
5.0
14.0
0.8
8.0
18.0
0.85
9.0
18.0
0.85
10.0
18.0
1.1
11.0
18.0
1.5
11.0
20.0
1.6
11.0
22.0
1.6
11.0
24.0
1.65
13.0
24.0
1.9
18.0
23.0
2.1
18.0
29.0
2.3
13.0
19.0
1.7
9.0
23.0
1.8
18.0
33.0
2.6
19.0
30.0
3.1
18.0
32.0
3.2
12.0
24.0
2.7
ern television stations and in several prominent newspapers in an attempt to grow yo
ormula that you hope can can predict new account sales based on any combination o
ession model that can predict New accounts based on the data for the last 36 months
ng expenditures. New accounts (the dependent variable) in 100s, while TV and Print TV Advertising Expenditures (X$1000)
Total Advertising Expenditures (X$1000)
New Accounts (x100)
8.0
23.0
2.75
11.0
27.0
3.15
13.0
28.0
3.2
15.0
26.0
3.95
18.0
27.0
4.8
17.0
28.0
4.85
8.0
24.0
3.7
22.0
36.0
5.1
22.0
32.0
5.2
11.0
24.0
4.6
8.0
22.0
4.3
6.0
21.0
4.1
counts (Dependent Variable). Is it positive or negative, strong, weak or non-existant. to new accounts.
ose to 1 and so shows a strong positive relationship between the two variables. This ndependent variables: Advertising expenditures for TV and Print (in $1000). Is one or
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
SS
MS
F
ignificance F
49.54916509804
24.77458254902 27.032835632982 1.117E-07
30.2432654575155
0.916462589621683
79.7924305555556
Standard Error
t Stat
P-value
Lower 95% Upper 95% Lower 95.0%
0.495690657838569
-1.85562950374446 0.0724565763848 -1.928308 0.08867202 -1.9283084
0.046039428760294
3.79591136201054 0.0005981556887 0.081094 0.26842951 0.08109367
0.030434581931963
4.45788950812185
9.022725559E-05 0.073754 0.19759363 0.07375438
sing expenditure -0.91
11
2.39
15
3.59
15
2.74
10
2.94
10
3.79
20
3.39
25
6.59
25
5.74
20
5.94
10
5.49
30
4.69
30
6.39
20
6.79
0
10.14
<=
65
vertising expenditures in the table. ditures (in $1000) to maximize new account growth using an annual Advertising TV Advertising (x$1000)
New Account Forecasted Sales
our customer base. You of expenditures (TV and s. The second task is to advertising (the
Upper 95.0%
0.088672
0.26843
0.197594
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Task 1
Task 2
In each of the four tasks below, you will be referring back to the simple-linear regr
and Multiple-linear gregression analyses you performed In the two tabs prior to th
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 components of the Regression analysis, you will want to refer to "Expert Resource
online. In your own words, explain the Coefficient of Determination. Why is it important to calculate, what it tells you about a Regression. The coefficient of determination is a statistical expression that describes how much variability in one factor may be explained by its connection with another. In otherwords, this coefficient assesses the strength of a linear relationship between two variables. It is computed to determine a regression equation's predictability and may be used as a comparison when new variables are included to the model.
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.
The F-statistic is performed on the null hypothesis, which indicates that the model with no independent variables represents the data well, against the alternate hypothesis, which suggests that the model fits the data better than the intercept alone. The F-Test p-value is compared to the significance level. In this regard, if the p-value does not measure up to the significance level, the sample data utilized offers enough evidence to infer that the regression model fits the data better than the model with no independent variables.
Task 3
Task 4
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 The Multiple R is the correlation coefficient and it indicates how strong the linear rela
is. In other words, it is the correlation between the response variable and predictors.
squared us the coefficient of determination. It is the square of correlation between v
and Y. This coefficient indicates the number of points that fall on the regression line. Standard Error of the regression entails how precisely the regression coefficient is me
When comparing regression equations to see which is more useful, the standard erro
estimate is usually utilized. When other predictor variables are included, the R-Squa
used to assess the model's fitness, and it can rise. With the Adjusted R Square used a
indication of the weights the variables have on the model, adding variables might res
inclusion of non-significant variables.
In both of the Regression analyses you performed in the first two tabs of this Excel w
you were also given a t-statistic (your textbook calls this the "t-TEST." What does thi
tell you in general about the constants you calculated in both of the regression analy
More specifically, what does the value you calculated in both regression analyses exp
about the constants. Again, you textbook has information on the t-TEST, however, y
want to do some additional research online to complete your answer to this Task.
A t-statistic is the coefficient of a predictor divided by its standard error. For the cons
is used to determine if there is a significant difference between the means of two gro
these two groups may share a relationship in certain features.
Task 4
In both of the Regression analyses you performed in the first two tabs of this Excel w
you were also given a P-value
. What does this value tell you in general about the con
you calculated in both of the regression analyses? More specifically, what does the v
calculated in each regression analysis explain about the constant. Again, you textboo
information on the P-value
, however, you may want to do some additional research complete your answer to this Task.
The p-value for each term is used to test the null hypothesis that the coefficients are e
zero, implying that they have no impact on the regression model, against an alternate
hypothesis that the coefficients are not equal to zero, implying that they have an effec
regression model Th. The null hypothesis is rejected if the p-value is less than the signi
level sought (usually 0.5), indicating that the predictor contributes significantly to the m
effect on the response variable. A higher p-value indicates that the predictor isn't signi
the model.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
ression his tab. of these es"
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
ationship . The R variables X The easured. or of ared is as an sult in the workbook, is value yses? plain you may stants, it oups and
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
workbook, nstants value you ok has online to equal to e ct on the ificant model's ificant in
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Sales ($1000)
Unit Price
Free Freight
Sales ($1000)
820
11.00
yes
820
734
11.50
No
734
723
11.50
No
723
818
12.00
yes
818
716
12.75
No
716
713
13.50
No
713
830
14.50
yes
830
712
15.25
No
712
760
15.75
yes
760
659
15.75
No
659
594
16.25
No
594
610
17.20
yes
610
573
18.75
No
573
615
15.00
yes
615
521
15.00
No
521
517
16.00
No
517
600
16.00
yes
600
510
16.50
yes
510
450
17.00
No
450
475
17.00
yes
475
414
17.00
No
414
414
17.50
No
414
456
18.00
yes
456
457
18.50
yes
457
413
19.00
yes
413
387
19.25
No
387
363
20.00
No
363
375
21.00
yes
375
365
22.00
yes
365
323
22.00
No
323
311
22.00
No
311
310
22.50
yes
310
315
23.00
yes
315
300
23.50
yes
300
280
24.00
No
280
Your company manufactures and sells hybred Rose Bushes through the internet. You want to creat
whether the company offers free freight as an incentive (the dummy variable). Use Excel to create below the data table. The "Creating a dummy variable for Regression" video hyperlink to follow fo
7 assignment. Once you have completed and inserted your Regression Model (formula) below, the
price and whether free freight is offered or not.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
252
24.00
No
252
Task 1
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.922574594492
R Square
0.851143882403
Adjusted R Squar 0.842122299518
Standard Error
69.34282132121
Observations
36
ANOVA
df
SS
MS
Regression
2 907304.88555 453652.44277615
Residual
33 158678.08667 4808.4268687858
Total
35 1065982.9722
Coefficients
Standard Error
t Stat
Intercept
1255.44435608 56.812250909 22.098127357805
Unit Price
-43.1533214758 3.1496974068 -13.700783250675
Free Freight
43.42164864583 23.198399092 1.8717519460653
Regression Equation = 1255.44435608023-43.1533214757581*UnitPrice
Task 2
For this task, complete the Regression Analysis and insert it in the space below. Then, cr
will predict Sales (x $1000
) based on Unit Price
and the Dummy variable whether free fre
In this task you will use the regression formula to Forecast sales and compare to actual d
from the table above. The formula for Forecasted sales should be created using excel. F
excel to calculate the Forecast Error (in $1000) and the percent error in the final two col
table. Forecast error = Forecasted sales - Actual Sales. Forecast error % = Forecast Erro
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Unit Price
$11.50
0
$759.18
$734
$25
$11.00
1
$824.18
$820
$4
$12.00
1
$781.03
$818
-$37
$15.25
0
$597.36
$712
-$115
$16.00
0
$564.99
$517
$48
$17.00
1
$565.26
$475
$90
$21.00
1
$392.65
$375
$18
$19.25
0
$424.74
$387
$38
$20.00
0
$392.38
$363
$29
$22.50
1
$327.92
$310
$18
$23.50
1
$284.76
$300
-$15
Free Freight (1=Yes, 0 = No)
Forecast Sales (x $1000)
Actual Sales (x $1,000)
Forecast Error (x $1,000)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Unit Price
Free Freight
11.00
1
11.50
0
11.50
0
12.00
1
12.75
0
13.50
0
14.50
1
15.25
0
15.75
1
15.75
0
16.25
0
17.20
1
18.75
0
15.00
1
15.00
0
16.00
0
16.00
1
16.50
1
17.00
0
17.00
1
17.00
0
17.50
0
18.00
1
18.50
1
19.00
1
19.25
0
20.00
0
21.00
1
22.00
1
22.00
0
22.00
0
22.50
1
23.00
1
23.50
1
24.00
0
te a forecasting model that includes unit price and a a Predictive formula generated with Excel in the space or this task is provided in the Assignment
tab for weeks 6 & en complete the table in Task 2 that forecasts sales by unit Dummy Variable (1 = Y, 0 = N)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
24.00
0
F
Significance F
94.345293202 2.24207609027E-14
P-value
Lower 95%
Upper 95%Lower 95.0%
Upper 95.0%
2.3763842E-21
1139.85896252246 1371.02975 1139.859
1371.03
3.5873484E-15 -49.5614290322259 -36.7452139 -49.56143 -36.74521
0.07012717578 -3.77584918293908 90.6191465 -3.775849 90.61915
e+43.4216486458288*FreeFreight
reate the Regression formula that eight is offered (yes = 1 or no = 0). data taken Finally, use lumns of the or/Actual Sales
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
3.4%
0.5%
-4.5%
-16.1%
9.3%
19.0%
4.7%
9.8%
8.1%
5.8%
-5.1%
Forecast Error %
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help