| 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 by quarter. 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 |
Sales (units) |
Annual Sales (Units) |
Sales per week |
| |
1 |
2006 |
1 |
0 |
10 |
|
|
| |
2 |
|
2 |
0 |
10 |
|
|
| |
3 |
|
3 |
0 |
10 |
|
|
| |
4 |
|
4 |
0 |
10 |
40 |
0.8 |
| |
5 |
2007 |
1 |
100 |
20 |
|
|
| |
6 |
|
2 |
100 |
20 |
|
|
|
| |
7 |
|
3 |
100 |
20 |
|
|
| |
8 |
|
4 |
100 |
20 |
80 |
1.5 |
| |
9 |
2008 |
1 |
150 |
30 |
|
|
| |
10 |
|
2 |
150 |
30 |
|
|
|
| |
11 |
|
3 |
150 |
30 |
|
|
| |
12 |
|
4 |
150 |
30 |
120 |
2.3 |
| |
13 |
2009 |
1 |
200 |
50 |
|
|
| |
14 |
|
2 |
200 |
50 |
|
|
| |
15 |
|
3 |
200 |
50 |
|
|
| |
16 |
|
4 |
200 |
50 |
200 |
3.8 |
| |
17 |
2010 |
1 |
250 |
60 |
|
|
| |
18 |
|
2 |
250 |
6 |
|
|
| |
19 |
|
3 |
250 |
6 |
|
|
| |
20 |
|
4 |
250 |
6 |
78 |
1.5 |
| |
21 |
2011 |
1 |
300 |
6 |
|
|
| |
22 |
|
2 |
300 |
70 |
|
|
| |
23 |
|
3 |
300 |
80 |
|
|
| |
24 |
|
4 |
300 |
80 |
236 |
4.5 |
| |
25 |
2012 |
1 |
350 |
90 |
|
|
| |
26 |
|
2 |
350 |
90 |
|
|
| |
27 |
|
3 |
350 |
100 |
|
|
| |
28 |
|
4 |
350 |
110 |
390 |
7.5 |
| |
29 |
2013 |
1 |
400 |
120 |
|
|
| |
30 |
|
2 |
400 |
130 |
|
|
| |
31 |
|
3 |
400 |
140 |
|
|
| |
32 |
|
4 |
400 |
150 |
540 |
10.4 |
| |
33 |
2014 |
1 |
450 |
150 |
|
|
| |
34 |
|
2 |
450 |
150 |
|
|
| |
35 |
|
3 |
450 |
160 |
|
|
| |
36 |
|
4 |
450 |
160 |
620 |
11.9 |
| |
37 |
2015 |
1 |
500 |
160 |
|
|
| |
38 |
|
2 |
500 |
170 |
|
|
| |
39 |
|
3 |
500 |
180 |
|
|
| |
40 |
|
4 |
500 |
180 |
690 |
13.3 |
| |
41 |
2016 |
1 |
600 |
190 |
|
|
| |
42 |
|
2 |
600 |
190 |
|
|
| |
43 |
|
3 |
600 |
200 |
|
|
| |
44 |
|
4 |
600 |
200 |
780 |
15.0 |
| |
45 |
2017 |
1 |
700 |
200 |
|
|
| |
46 |
|
2 |
700 |
210 |
|
|
| |
47 |
|
3 |
700 |
220 |
|
|
| |
48 |
|
4 |
700 |
230 |
860 |
16.5 |
| |
49 |
2018 |
1 |
800 |
230 |
|
|
| |
50 |
|
2 |
800 |
240 |
|
|
| |
51 |
|
3 |
800 |
250 |
|
|
| |
52 |
|
4 |
800 |
260 |
980 |
18.8 |
| |
|
|
|
|
|
| Task 1 |
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? |
| |
|
|
|
|
|
|
|
|
|
|
|
| Task 2 |
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.05). Finally create a Regression equation in Task 3.a (see below). |
| Task 3.a |
Insert the Regression Formula Below. |
| Task 4 |
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 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. |
| |
|
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.0 |
|
$ – 0 |
| |
$150 |
|
|
|
|
0.0 |
|
$ – 0 |
| |
$300 |
|
|
|
|
0.0 |
|
$ – 0 |
| |
$350 |
|
|
|
|
0.0 |
|
$ – 0 |
| |
$400 |
|
|
|
|
0.0 |
|
$ – 0 |
| |
$450 |
|
|
|
|
0.0 |
|
$ – 0 |
| |
$500 |
|
|
|
|
0.0 |
|
$ – 0 |
| |
$550 |
|
|
|
|
0.0 |
|
$ – 0 |
| |
$600 |
|
|
|
|
0.0 |
|
$ – 0 |
| |
$650 |
|
|
|
|
0.0 |
|
$ – 0 |
| |
$700 |
|
|
|
|
0.0 |
|
$ – 0 |
| |
$750 |
|
|
|
|
0.0 |
|
$ – 0 |
| |
$900 |
|
|
|
|
0.0 |
|
$ – 0 |
| |
$1,000 |
|
|
|
|
0.0 |
|
$ – 0 |
| 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. |
| |
|
| |
|
