Homework answers / question archive / 15) Use the Excel file Credit Risk Data that was introduced in this chapter to perform the following activities: a

15) Use the Excel file Credit Risk Data that was introduced in this chapter to perform the following activities: a

MS Excel

Share With

---

15) Use the Excel file Credit Risk Data that was introduced in this chapter to perform the following activities:

a. Compute the combined checking and savings account balance for each record in the Base Data worksheet.--à

 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?

b. Apply Pareto analysis (80-20) to draw conclusions about the combined amount of money in checking and savings accounts.

c. Use Excel’s filtering capability to extract all records for new-car loans. How many individuals with new-car loans are single, married, and divorced?

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

 

19. Modify the data in the Base Data worksheet in the Excel file Credit Risk Data using IF functions to include new columns, classifying the checking and savings account balances as low if the balance is less than $250, medium if at least $250 but less than $2000, and high otherwise.

 

20. The Excel file President’s Inn Guest Database pro-vides a list of customers, rooms they occupied, arrival and departure dates, number of occupants, and daily rate for a small bed-and-breakfast inn during one month.7 Room rates include breakfast and are the same for one or two guests; however, any additional guests must pay an extra $20 per person per day for breakfast. Parties staying for seven days or more receive a 10% discount on the room rate as well as any additional breakfast fees. Modify the spreadsheet to calculate the number of days that each party stayed at the inn and the total revenue for the length of stay.

 

10) The Excel file Internet Usage provides data about of the Internet. Construct stacked bar charts that will allow you to compare any differences due to age education attainment and draw any conclusions that you can. Would another type of chart be more appropriate?

 

11) Construct an appropriate chart to show the relative value of funds in each investment category in the Excel file Retirement Portfolio.

 

14) In the Excel file Banking Data, apply the following data visualization tools:

a. Use data bars to visualize the relative values of median home value.

b. Use color scales to visualize the relative values of median household wealth.

c. Use an icon set to show high, medium, and low average bank balances, where high is above $30,000, low is below $10,000, and medium is anywhere in between.