Get Instant Help From 5000+ Experts For

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

SCMG305 Global Demand Management

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

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:

 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

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 = Forecasted sales - Actual Sales.  Forecast error % = Forecast Error/Actual Sales

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.

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.

Multiple Linear Regression
The company you work, 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 two years of advertising in both media and want to build a multiple regression formula that you hope can 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.  The second task is to project new accounts based on various combinations of Television and print advertising expenditures.  New accounts (the dependent variable) in 100s, while TV and Print advertising (the Independent Variables) are in \$1,000s.

 Period Print Advertising Expenditures                          (X\$1000) TV Advertising Expenditures (X\$1000) Total Advertising Expenditures      (X\$1000) New Accounts (x100) 1 3.0 3.0 6.0 0.5 2 3.5 3.5 7.0 0.6 3 4.0 4.0 8.0 0.7 4 4.5 4.5 9.0 0.7 5 6.0 2.0 8.0 0.6 6 8.0 2.0 10.0 0.7 7 8.0 4.0 12.0 0.6 8 9.0 5.0 14.0 0.8 9 10.0 8.0 18.0 0.85 10 9.0 9.0 18.0 0.85 11 8.0 10.0 18.0 1.1 12 7.0 11.0 18.0 1.5 13 9.0 11.0 20.0 1.6 14 11.0 11.0 22.0 1.6 15 13.0 11.0 24.0 1.65 16 11.0 13.0 24.0 1.9 17 5.0 18.0 23.0 2.1 18 11.0 18.0 29.0 2.3 19 6.0 13.0 19.0 1.7 20 14.0 9.0 23.0 1.8 21 15.0 18.0 33.0 2.6 22 11.0 19.0 30.0 3.1 23 14.0 18.0 32.0 3.2 24 12.0 12.0 24.0 2.7 25 15.0 8.0 23.0 2.75 26 16.0 11.0 27.0 3.15 27 15.0 13.0 28.0 3.2 28 11.0 15.0 26.0 3.95 29 9.0 18.0 27.0 4.8 30 11.0 17.0 28.0 4.85 31 16.0 8.0 24.0 3.7 32 14.0 22.0 36.0 5.1 33 10.0 22.0 32.0 5.2 34 13.0 11.0 24.0 4.6 35 14.0 8.0 22.0 4.3 36 15.0 6.0 21.0 4.1

Find the correlation factor between total advertising (independent variable) and New Accounts (Dependent Variable).  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.

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

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
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.  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.   The total year forecast, EBIT, and Total sales for the year ara all calculated for  you, so all you need to do in Task 4 is use the regression analysis coefficients and a regression formula to create a forecast.  Note in this forecast Q1 = Q2 = Q3 = Q4 in this example.

 Period Year Quarter Advertising Dollars (X\$100) Sales                (units X10) Annual Sales (Units X10) 1 2006 1 0 1 2 2 0 1 3 3 0 1 4 4 0 1 4 5 2007 1 1 2 6 2 1 2 7 3 1 2 8 4 1 2 8 9 2008 1 1.5 3 10 2 1.5 3 11 3 1.5 3 12 4 1.5 3 12 13 2009 1 2 5 14 2 2 5 15 3 2 5 16 4 2 6 21 17 2010 1 2.5 6 18 2 2.5 6 19 3 2.5 6 20 4 2.5 6 24 21 2011 1 3 7 22 2 3 7 23 3 3 8 24 4 3 8 30 25 2012 1 3.5 9 26 2 3.5 9 27 3 3.5 10 28 4 3.5 11 39 29 2013 1 4 12 30 2 4 13 31 3 4 14 32 4 4 15 54 33 2014 1 4.5 15 34 2 4.5 15 35 3 4.5 16 36 4 4.5 16 62 37 2015 1 5 16 38 2 5 17 39 3 5 18 40 4 5 18 69 41 2016 1 6 19 42 2 6 19 43 3 6 20 44 4 6 20 78 45 2017 1 7 20 46 2 7 21 47 3 7 22 48 4 7 23 86 49 2018 1 8 23 50 2 8 24 51 3 8 25 52 4 8 26 98