Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

ITIS 1P97: Data Analysis and Business Modeling

Lab Exercise 3: Data display and summary

Review Chapter 3

Problems and exercises (Chapters 3 of the textbook). The data files required for the exercises are available on Sakai for download.

1. (Problem 3.1) Create a line chart for the closing prices for all years and a stock chart for the high/low/close prices for August 2013 in the Excel file S&P 500.

2. (Problem 3.2) Create a pie chart showing the breakdown of occupations for each year in the Science and Engineering Jobs Excel file, and contrast these with simple column charts.

3. (Problem 3.7) The Excel file Internet Usage provides data about users of the Internet. Construct stacked bar charts that will allow you to compare any differences due to age or educational attainment and draw any conclusions that you can. Would another type of charts be more appropriate?

4. (Problem 3.21) The Excel file Credit Risk Data provides information about bank customers who had applied for loans. The data include the purpose of the loan, checking and savings account balances, number of months as a customer of the bank, months employed, gender, marital status, age, housing status and number of years at current residence, job type, and credit-risk classification by the bank.

a. Compute the combined checking and savings account balance for each record in the database. Then sort the records by the number of months as a customer of the bank. From examining the data, does it appear that customers with a longer association with the bank have more assets? Construct a scatter chart to validate your conclusions.

b. Use Excel’s filtering capability to extract all records for new-car loans. Construct a pie chart showing the marital status associated with these loans.

Do the same with pivot table and pivot chart.

c. Use Excel’s filtering capability to extract all records for individuals employed less than 12 months. Can you draw any conclusions about the credit risk associated with these individuals?

5.  (Problem 3.27) Credit rating – Use the Histogram tool to develop a frequency distribution and histogram for the number of months as a customer of the bank in the Excel file Credit Risk Data. Use your judgment for determining the number of bins to use. Compute the relative and cumulative relative frequencies and use a line chart to construct an ogive.

6. (Problem 3.40) Restaurant Sales – Create a useful dashboard for the data in the Excel file Restaurant Sales. Use appropriate charts and layouts and other visualization tools that help to convey the information. Explain why you chose the elements of the dashboard and how a manager might use them.