Statistics and Excel Assignment: Frequency Distribution, Bar Charts, Pie Charts, and Pivot Tables

Task:

Please attempt all the problems in this assignment and submit a print copy of your work on the due date and time indicated above. You can copy and paste the charts and tables from MS Excel to MS Word to organize your answers for printing. The data sets required to answer some of these questions are posted on course webpage under Moodle.  You can work in pairs on this assignment but you must write and submit your individual answers for credit.  If you work in pairs, please indicate the name of the student you worked with next to your name. You are not allowed to discuss or share your answers with any other student except your group-mate. Please don’t hesitate to contact me if you have questions about the homework or anything else.

Using the “VLOOKUP” function in Microsoft Excel create a new variable called ‘class’ which identifies customers as low income, lower middle income, middle income, upper middle income and high income on the basis of the following income benchmarks.

b)Construct the frequency distribution for the income ‘class’ variable you created under (a) above and calculate the corresponding relative and percent frequencies.

c)  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 income class.

d) 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.

2.a) Using a reasonable number of classes construct the frequency distribution for customer income. Please include class-midpoint, frequency, relative frequency, percent frequency, cumulative frequency, and percent cumulative frequencies in your frequency table.

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

c)Using the same number of classes you used for customer income under (a) above, please construct a histogram for age of customers. Comment on the approximate shape of the distribution of the data.

d)  Based on your charts and frequency tables under a, b, and c write a brief report about your observations. Make sure that you include some actual numbers from the results in your report and comment on the purpose for which Y-tech could use the kind of information you presented above.

3.a. Using Excel please construct a pivot table for educational level of customers and income ‘class’ you constructed under question (1) above. Please write a brief report on your observations from the pivot table. For example do you observe any systematic relationship between educational level and income class? If so does this relationship make sense? etc.

b. Using Excel please construct pivot tables for average customer income and standard deviation of customer income by educational level and employer. Your pivot tables should look like the following table:

Please also write a brief report about your observations of the results in the two pivot tables.
Data 2: . The latest data for weekly stock prices in the US dollars for Google Inc., Amazon.com  Inc., Apple Inc., and Microsoft  are posted on the course webpage under Excel data file named ‘Weekly Stock prices for four tech companies.

a. Please construct line graphs for the stock prices of i) Google and Amazon ii) Apple and Microsoft for the period from 1/1/2018 to 10/14/2019 and comment on the volatility you observe in the stock prices of the two pairs of companies.
                                                                                        
b. Please calculate the mean, median, the 25th and the 75th percentiles, as well as the standard deviation, and coefficient of variation for the weekly returns from investment in the stocks of Google, Amazon, and Apple. Please write a report interpreting your results and comparing the three companies in terms of return and risk.