Share
Explore BrainMass

Statistics Problems: Stock Prices and Customer Databases

Please answer the following questions with the information provided in the attached files.

1. Data on stock prices of Citigroup at the beginning of each month for the period from 1/3/1977 to 1/2/2013 are provided in the first attachment. Please construct a line graph demonstrating the closing stock prices of Citigroup at the beginning of each month for the period from 1/4/2010 to 1/2/2013 making sure that you properly label the x and y axis. Please comment on the fluctuations in the prices demonstrated in the graph. For example what do you observe in the month of May 2011? If you observe any major shift in the graph, please explain the cause for the shift consulting online sources if necessary.

Use the "Customer database for Y-tech company" for the following questions. (See word document for data)

1. Identify qualitative and quantitative variables in the data. Also identify discrete and continuous variables in the data.
2. Using Microsoft Excel construct a stem-and-leaf display for the first 150 observations on the 'amtspent' and briefly state your observations about the results.

3. a) Using the "VLOOKUP" function in Microsoft Excel create a new variable called 'class' which identifies customers as low income, middle income, upper middle income and high income on the basis of the following income benchmarks.

Income benchmark Class
0 -35,000 Low
35,000-85,000 Middle
85,000-135,000 Uppermiddle
?135,000 High

[Note: You don't need to report the data with the new variable. I'll know whether you did it right or not based on your answer for b here below].
b) Construct the frequency distribution for the income 'class' variable you created under (a) above and calculate the corresponding relative and percent frequencies.

b) Construct a bar chart and pie chart for the income 'class' variable. Make sure the bar charts are labeled by the frequency values and the pie charts are labeled by the percent share of each laptop in the total sales. (2 points)
c) Write a brief report about your observations from the frequency table and the charts. Make sure that you include some actual numbers from the results in your report. (4 points)

4. a) Using a reasonable number of classes construct the frequency distribution for customer income and calculate the corresponding relative and percent frequencies. Also include cumulative and percent cumulative frequencies in your table. (4 points)

b) Construct a histogram, frequency polygon and ogive for customer income. Make sure that both the histogram and the frequency polygon are labeled by the percent frequencies of the classes in the frequency distribution while the ogive is labeled by the percent cumulative frequencies.

c) Using the same number of classes you used for customer income under (a) above please construct a histogram for age of customers and comment on its shape.

d) Based on your charts and frequency tables write a brief report about your observations. Make sure that you include some actual numbers from the results in your report.

5. Using Microsoft excel construct a pivot table for educational level of customers and income 'class' you constructed under question (3) above. For example do you see any systematic relationship between educational level and income class? If so does this relationship make sense? etc.

Attachments

Solution Preview

Please see the attached documents for response.

Question #1
The following are the steps to create the line graph in Microsoft Excel 2007.
Highlight cells A1:A38.
Click on the insert tab.
Click on the Line button.
Click on the Line chart.
Right click on the chart.
Click on Select Data.
Click on the Edit button for the Horizontal (Category) Axis Labels.
Highlight cells E1:E38.
Click on the OK button.
Click on the OK button.

An examination of the data shows that in May 2010 the Citi Group stock had a reverse stock split. The stock went from $4.60 to $40.99.

Question 2.)
1. Identify qualitative and quantitative variables in the data. Also identify discrete and continuous variables in the data. (3 points).
Qualitative variables are interval data that are real numbers such as: heights, weights, incomes, distances, stock prices, etc.
Quantitative variables are the values of a category such as: responses to marital status such as: single, married, divorced and windowed.
Discrete variables can take on a countable number of values (i.e. if x was defined as the number of tails observed in an experiment to flip a coin 5 times, the values of x are 0, 1, 2, 3, 4, 5).
Continuous variables are variables that are uncountable (i.e. the amount of time it takes to complete a task).

Qualitative Quantitative Discrete Continuous
Age Employer
Income Education
Amount Spent Gender
History Married
Catalogs
Close

2. Using Microsoft Excel construct a stem-and-leaf display for the first 150 observations on the 'amtspent' and briefly state your observations about the results. (3 points)

Click on the Add-Ins tab.
Click on the MegaStat drop down list.
Select Descriptive Statistics.
Type A1:A151 in the Input Range.
Add a check nest to Stem and Leaf.
Click the OK button.

3. a) Using the "VLOOKUP" function in Microsoft Excel create a new variable called 'class' which identifies ...

Solution Summary

The stock prices and customer database statistics is examined.

$2.19