Unit 4

[GB513: Business Analytics]

Assignment

This Assignment requires you to use Excel in all three questions. Make sure you explain your
answers and provide the regression output tables for questions 1 and 2.
Submit your Assignment to the Unit 4 Dropbox. Make sure to use the Unit 4 Assignment template
from Doc Sharing when you turn in your answers.
Question 1
Shown below are rental and leasing revenue ?gures for of?ce machinery and equipment in the United
States over a 7-year period according to the U.S. Census Bureau. Use these data and the regression
tool in the data analysis tool pack to run a linear regression.
Based on the formula you get from the regression output, answer the following questions:
a) What is the forecast for the rental and leasing revenue for the year 2011?
b) How confident are you in this forecast? Explain your answer by citing the relevant metrics.
Year Rental and Leasing ($ millions)
2004 5,860
2005 6,632
2006 7,125
2007 6,000
2008 4,380
2009 3,326
2010 2,642

Question 2
Suppose a researcher gathered survey data from 19 employees and asked the employees to rate
their job satisfaction on a scale from 0 to 100 (with 100 being perfectly satis?ed). Suppose the
following data represent the results of this survey. Assume that relationship with supervisor is rated
on a scale from 0 to 50 (0 represents a poor relationship and 50 represents an excellent relationship);
overall quality of the work environment is rated on a scale from 0 to 100 (0 represents poor work
environment and 100 rep resents an excellent work environment); and opportunities for advancement
1

[GB513: Business Analytics]

Unit 4

is rated on a scale from 0 to 50 (0 represents no opportunities and 50 represents excellent
opportunities).
Answer the following questions:
A) What is the regression formula?
B) How reliable do you think the estimates will be based on this formula? Explain your answer by
citing the relevant metrics.
C) Are there any variables that do not appear to be good predictors of job satisfaction? How can
you tell?
D) If a new employee reports that her relationship with her supervisor is 40, rates her
opportunities for advancement to be at 30, finds the quality of the work environment to be at
75, and works 60 hours per week, what would you expect her job satisfaction score to be?

Job
satisfaction

Relationship
with
supervisor

Opportunities
for
advancement

Overall
quality of
work
environment

Total
hours
worked
per week

55

27

65

50

42

20

12

13

60

28

85

40

79

45

7

65

35

53

65

48

45

29

43

40

32

70

42

62

50

41

35

22

18

75

18

60

34

75

40

32

95

50

84

45

48

65

33

68

60

11

85

40

72

55

33

10

5

10

50

21

75

37

64

45

42

80

42

82

40

46

2

Unit 4

[GB513: Business Analytics]

50

31

46

60

48

90

47

95

55

30

75

36

82

70

39

45

20

42

40

22

65

32

73

55

12

Question 3
Investment analysts generally believe the interest rate on bonds is inversely related to the prime
interest rate for loans; that is, bonds perform well when lending rates are down and perform poorly
when interest rates are up.
Use the following data to construct a scatter graph and then fit a regression line to the data.
Report the regression formula and the r-squared values from the chart (right click on the data points,
select Add Trend line, and select options to show these metrics).
Do you think the bond rate can be predicted by the prime interest rate? Justify your answer using the
relevant metrics.
Bond Rate

Prime Interest Rate

5%

16%

12

6

9

8

15

4

7

7

3

Unit 4

[GB513: Business Analytics]

Grading Rubric
Your Assignment will be graded based on the following breakdown.
Unit 4 Assignment

Criteria

Points
Possible

Points
Earned

Question 1a
6
2011 forecast for rental and leasing revenue is accurate.
Question 1b
Appropriate analysis of the reliability of forecast based on
this formula.

6

Question 2a
5
Correct regression formula.
Question 2b
Appropriate analysis of the reliability of estimates based on
this formula.

5

Question 2c
Appropriate analysis of whether there are any variables that
do not appear to be good predictors.

5

Question 2d
5
Correct expected job satisfaction score.
Question 3a
6
Constructed a scatter graph and included a regression line.
Question 3b
Appropriate analysis of whether a bond rate could be
predicted by the prime interest rate.

7

Properly completed assignment template.

5

Total

50
4

Unit 4

[GB513: Business Analytics]

5

Unit 4 Assignment Rubric
Instructors: to complete the rubric, please enter the points the student earned in the green cells of column E. Then determine point deductions for writing, late policy, etc in the red cells to calculate the final grade.
Assignment Requirements        Points possible         Points earned by student
Overview of the profession        0-24
Educational requirements including any relevant certifications or credentials        0-24
Personality traits and skill sets required for your profession. Explain how the traits you learned about yourself from the personality profile in Unit 3 line up with your field and areas you may need to work on.        0-28
Summary of your career goals        0-24
How Kaplan University Career Services can assist you in obtaining your career goals        0-24
Where you see yourself in 5 years        0-24
Total (Sum of all points)         148        0
*Writing Deductions (Maximum 30% from points earned):        Points indicated below represent the maximum point deduction possible for each of the listed writing criteria
Grammar/Punctuation/Spelling:    30%    0
Order of Ideas/Length requirement (if applicable):    30%    0
Format    10%    0
*Source citations    30%    0
Late Submission Deduction: (refer to Syllabus for late policy)
Adjusted total points        0
*If sources are not cited and work is plagiarized, grade is an automatic zero and further action may take place in accordance with the Academic Integrity Policy as described in the university catalog.         Final Percentage        0%
Feedback:

Unit 4 Dropbox Assignment Answers by (Insert your name here)
In the summary tables below, insert only the answers. You will show work after the summary section.
Question 1 answers
1a: forecast
1b: reliability
Question 2 answers
2a: formula
2b: reliability
2c: variables
2d: expected score

Question 3 answers
Can bond rate be predicted by the prime?
Paste scatter graph here:
Work
Show all your work for the three questions below.
Question 1
Show your work here.
Question 2
Show your work here.
Question 3
Show your work here.