Excel Assignment (read carefully)

GB 513 UNIT 4

SUCCESS GUIDE

GB 513 SUPPORT MATERIALS

1 | GB 513 Unit 4 Success Guide v.6.13.17

UNIT 4 SUCCESS GUIDE

1. As always, start by reading the chapters and studying the solved examples.

2. Watch the lecture video on regression in document sharing. It explains how to interpret

the metrics from the output report. This should help you answer all of Question 2 in the

Assignment.

3. To learn about creating a scatter graph and fitting a trend line (for questions 1 and 3),

you can either watch the last part of the forecasting lecture (after minute 28) or this

video: http://www.youtube.com/watch?v=6rOlGbLeQxI.

4. Watch the sample problem solutions in Course Documents.

5. If you still want more videos, search YouTube for “multiple regression.”

Email your instructor if you find any of these links to be broken.

Avoid these mistakes!

 In regression, the coefficient is not what determines if a variable is “significant”!

 Similarly, the P-value does not show the impact of a variable, or which variable is more

important.

 Some students do not calculate the forecast in Question 1 and stop after writing

the formula.

 Question 3 asks “Can the bond rate be predicted by the prime interest rate?” Many

students just run a regression and leave that question unanswered. Note that the

grading rubric has a section specifically for that answer.

The questions below are very similar to what you need to solve in the Assignment.

Some, but not all, of these solutions were demonstrated on video and recorded

for the live binder by the math tutors.

SAMPLE PROBLEM 1 FOR ASSI GNMENT PROBLEM 1 Determine the equation of the trend line for the data shown below on U.S.

exports of fertilizers to Indonesia over a five-year period provided by the U.S

Census Bureau. Using the trend line equation, forecast the value for the year

2013.

RESOURCES

COMMON MISTAKES IN THE ASSIGNMENT

SAMPLE PROBLEMS AND SOLUTIONS

2 | GB 513 Unit 4 Success Guide v.6.13.17

Year Fertilizer ($ millions)

2007 11.9

2008 17.9

2009 22.0

2010 21.8

2011 26.0.

The forecast for the year 2012 is: y= -6428.97 + (2013) 3.21 = 32.76

SOLUTION

The first problem in the Assignment requires you to run a regression and use the

results to make the forecast. It is NOT meant to be solved using manual formulas.

The table below shows the output from running the regression tool in Excel. Note

that the y variable is the export amount and the x variable is the year.

SUMMARY OUTPUT

Regression Statistics

Multiple R 0.953955

R Square 0.910031 Adjusted R

Square

0.880041

Standard

Error

1.842733

Observations 5

ANOVA

df

SS

MS

Regression 1 103.041 103.041 30.34485

Residual 3 10.187 3.395667

Total 4 113.228

Coefficients

Standard Error

t Stat

P-value

Intercept -6428.97 1170.692 -5.4916 0.01188

Year 3.21 0.582723 5.508616 0.011778

3 | GB 513 Unit 4 Success Guide v.6.13.17

SAMPLE PROBLEM 1 FOR ASSIGNMENT PROBLEM 2 The problem solved in the regression lecture is almost exactly like problem 2 in

your Assignment. You will have to interpret the metrics on the report, then write

and solve the regression formula.

SAMPLE PROBLEM 1 FOR ASSI GNMENT PROBLEM 3 This problem is intended to be solved through graphing. When fitting a trend line

to a scatter or line graph, it is only a matter of a button click to fit a linear or a

quadratic trend line. Just watch the second part of the forecasting lecture (after

minute 28) to see an example of fitting trend lines. The other video listed in the

resources is also a good example.

4 | GB 513 Unit 4 Success Guide v.6.13.17

Resources

Corman, L. (2010, September 28). Trend lines and regression analysis in excel. Retrieved

from https://www.youtube.com/watch?v=6rOlGbLeQxI