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 constit

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 dat

Collepals.com Plagiarism Free Papers

Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.

Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS

Why Hire Collepals.com writers to do your paper?

Quality- We are experienced and have access to ample research materials.

We write plagiarism Free Content

Confidential- We never share or sell your personal information to third parties.

Support-Chat with us today! We are always waiting to answer all your questions.

Related Posts

All Rights Reserved Terms and Conditions
College pals.com Privacy Policy 2010-2018