WEEK 6

by | Aug 8, 2025 | Homework Answsers / Literature

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

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

Multiple Linear Regression

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). 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) Print Advertising Expenditures (per period) TV Advertising Expenditures (per period) Total Advertising Expenditures (per period) New Accounts (per period)
1 3000 3000 6000 100
2 3500 3500 7000 125
3 4000 4000 8000 175
4 4500 4500 9000 200
5 6000 2000 8000 165
6 8000 2000 10000 210
7 8000 4000 12000 230
8 9000 5000 14000 250
9 10000 8000 18000 325
10 9000 9000 18000 310
11 8000 10000 18000 330
12 7000 11000 18000 310
13 9000 11000 20000 345
14 11000 11000 22000 375
15 13000 11000 24000 410
16 11000 13000 24000 400
17 11000 18000 29000 430
18 11000 18000 29000 420
19 6000 13000 19000 325
20 14000 9000 23000 400
21 15000 18000 33000 475
22 11000 19000 30000 425
23 14000 18000 32000 450
24 12000 12000 24000 415
25 15000 8000 23000 390
26 16000 11000 27000 410
27 15000 13000 28000 415
28 11000 15000 26000 410
29 9000 18000 27000 412
30 11000 17000 28000 418
31 16000 8000 24000 421
32 14000 22000 36000 610
33 10000 22000 32000 445
34 13000 11000 24000 405
35 14000 8000 22000 380
36 15000 6000 21000 360
Task 1 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
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 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
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

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.
Task 1 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 4 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

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
Task 1 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:
Task 2 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
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

image1.png

Order a similar assignment, and have writers from our team of experts write it for you, guaranteeing you an A

Order Solution Now