Get Instant Help From 5000+ Experts For

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

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

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.