Guaranteed Higher Grade!

Free Quote
SCMG305 Global Demand Management

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 |

Task1:

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 |

Task2:

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.

Task1:

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 5:

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 |

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

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

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 |

Task 1:

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 have you learned from this 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. Highlight the data and headings, then go to Insert -> X-Y Scatter plot. Input the correct title, legend, and trendline

Task 3:

In the area below, generate a Simple Linear Regression analysis. Then create a formula belo wusing the regression coefficients to create a formula that predicts 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)

Task 4:

Part 1 of task 4 is to use the regression formula you created above to calculate sales volume (x 10 units) by quarter for 2019, including for the year, based on the various Advertising expenditures (x $100). 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), the form will calculate your total year forecast, EBIT, and Full Year Sales $ (EBIT =Earnings Before Interest, Taxes, and Depreciation). Finally, as an individual, you have the capacity to produce around 14 units per week in a 52 week year, so In the large green box below the table, insert what is the maximum you should plan on spending for advertising per year to support your production, show why.

Forecasted Sales by Quarter in Units (X10) | Do not change G119 thru J132 | ||||||

Advertising Expenditure per quarter (X $100) | Q1 Forecast (Units X10) | Q2 Forecast (Units X10) | Q3 Forecast (Units X10) | Q4 Forecast (Units X10) | Total Year Forecast (Units X 10) | Full Year EBIT $ | Full Year Sales $ |

$1.0 | 0.00 | $(600.0) | $- | ||||

$1.5 | 0.00 | $(800.0) | $- | ||||

$3.0 | 0.00 | $(1,400.0) | $- | ||||

$3.5 | 0.00 | $(1,600.0) | $- | ||||

$4.0 | 0.00 | $(1,800.0) | $- | ||||

$4.5 | 0.00 | $(2,000.0) | $- | ||||

$5.0 | 0.00 | $(2,200.0) | $- | ||||

$5.5 | 0.00 | $(2,400.0) | $- | ||||

$6.0 | 0.00 | $(2,600.0) | $- | ||||

$6.5 | 0.00 | $(2,800.0) | $- | ||||

$7.0 | 0.00 | $(3,000.0) | $- | ||||

$7.5 | 0.00 | $(3,200.0) | $- | ||||

$9.0 | 0.00 | $(3,800.0) | $- | ||||

$10.0 | 0.00 | $(4,200.0) | $- | ||||

At a production level for you of 14 units per week in a 52 week year, what is the maximum you should spend on advertising per quarter and for the year? |