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.