Complete the following problems and upload your spreadsheet here.. Problems: (1) Download the S&P 500 prices for the past 60 months from http://finance.yahoo.com. Use conditional formatting in the following situations: o Format in bold each month in which the value of the S&P index increased and underline each month in which the value of the S&P index decreased. o Highlight in green each month in which the S&P index changed by maximum of 2 percent o Highlight the largest S&P index value in red and the smallest in purple. (2) In column A create names of 50 students in the format AAAXXXX where XXXX is a random 4 digit number (use RANDBETWEEN function to generate the random number). In column B generate random grades between 50 and 100 for these 50 students. The top 10 students receive an A, the next 20 students receive a B, and all other student receive a C. Highlight the A grades in red, the B grades in green, and the C grades in blue . Hint: The LARGE(D4:D63, 10) function gives you the tenth-highest grade on the test. (3) Generate dates of 365 days of any one year between 2005 and 2010. Highlight all weekdays in red. Highlight in blue all days that occur in the first10 days of the month. (4) Download the daily prices of a stock for any one year between 2005 and 2010. Calculate the daily returns using daily closing prices. Set up conditional formatting with three icons so that 10 percent of returns have an up arrow, 10 percent have a down arrow, and 80 percent have a horizontal arrow. (5) Suppose you are saving for your child?s college fund. You will contribute the same amount of money to the fund at the end of each year. Your goal is to end up with $100,000. For annual investment returns ranging from 4 percent to 10 percent and number of years investing varying from 10 to 18 years, determine your required annual contribution. Suppose you can save $10,000 per year. Use conditional formatting to highlight the minimum number of years needed for each annual return rate to accumulate $100,000. (6) Download the monthly level of the Dow Jones Index for years 1967 to 2012. Does this data indicate any unusual seasonal patterns in stock returns? Hint: You can extract the month (January, February, and so on) by using the TEXT (A4,?mmm?) formula copied to any column. (7) For years 2002 to 2012, download monthly 10 year bond rates. It?s often suggested that interest rates are more volatile-tend to change more-when interest rates are high. Does your data in the support this statement? (Hint: PivotTables can display standard deviations.) For questions 8-10, use the data in this attached file. (8) You own a fast-food restaurant and have done some market research in an attempt to understand your customers better. For a random sample of customers, you are given the income, gender, and number of days per week the residents go out for fast food. Use this information to determine how gender and income influence the frequency with which a person goes out to eat fast food. (9) Students at the School of Fine Art apply to study either English or Science. You have been assigned to determine whether the School of Fine Art discriminates against women in admitting students to the school of their choice. You are given the following data on the school of Fine Art?s students: o Female or male o Major applied for: English (Eng) or Science (Sci) o Admit? Yes or No Assuming that women are as qualified for each major as men are, does this data indicate that the college discriminates against women? Be sure you use all available information. (10) You have been assigned to evaluate the quality of care given to heart attack patients at Emergency Room (ER) and Chicago Hope (CH). For the past month, you are given the following patient data: o Hospital (ER or CH) o Risk category (high or low). How-risk people are less likely to survive than low-risk people. o Patient outcome (live or die) Use this data to determine which hospital is doing a better job of caring for heart attack patients. Hint: Use all the data.

Complete the following problems and upload your spreadsheet here.. Problems: (1) Download the S&P 500 prices for the past 60 months from http://finance.yahoo.com. Use conditional formatting in the following situations: o o o Format in bold each month in which the value of the S&P index increased and underline each month in which the value of the S&P index decreased. Highlight in green each month in which the S&P index changed by maximum of 2 percent Highlight the largest S&P index value in red and the smallest in purple. (2) In column A create names of 50 students in the format AAAXXXX where XXXX is a random 4 digit number (use RANDBETWEEN function to generate the random number). In column B generate random grades between 50 and 100 for these 50 students. The top 10 students receive an A, the next 20 students receive a B, and all other student receive a C. Highlight the A grades in red, the B grades in green, and the C grades in blue . Hint: The LARGE(D4:D63, 10) function gives you the tenthhighest grade on the test. (3) Generate dates of 365 days of any one year between 2005 and 2010. Highlight all weekdays in red. Highlight in blue all days that occur in the first10 days of the month. (4) Download the daily prices of a stock for any one year between 2005 and 2010. Calculate the daily returns using daily closing prices. Set up conditional formatting with three icons so that 10 percent of returns have an up arrow, 10 percent have a down arrow, and 80 percent have a horizontal arrow. (5) Suppose you are saving for your child's college fund. You will contribute the same amount of money to the fund at the end of each year. Your goal is to end up with $100,000. For annual investment returns ranging from 4 percent to 10 percent and number of years investing varying from 10 to 18 years, determine your required annual contribution. Suppose you can save $10,000 per year. Use conditional formatting to highlight the minimum number of years needed for each annual return rate to accumulate $100,000. (6) Download the monthly level of the Dow Jones Index for years 1967 to 2012. Does this data indicate any unusual seasonal patterns in stock returns? Hint: You can extract the month (January, February, and so on) by using the TEXT (A4,\"mmm\") formula copied to any column. (7) For years 2002 to 2012, download monthly 10 year bond rates. It's often suggested that interest rates are more volatiletend to change morewhen interest rates are high. Does your data in the support this statement? (Hint: PivotTables can display standard deviations.) For questions 810, use the data in this attached file. (8) You own a fastfood restaurant and have done some market research in an attempt to understand your customers better. For a random sample of customers, you are given the income, gender, and number of days per week the residents go out for fast food. Use this information to determine how gender and income influence the frequency with which a person goes out to eat fast food. (9) Students at the School of Fine Art apply to study either English or Science. You have been assigned to determine whether the School of Fine Art discriminates against women in admitting students to the school of their choice. You are given the following data on the school of Fine Art's students: o o o Female or male Major applied for: English (Eng) or Science (Sci) Admit? Yes or No Assuming that women are as qualified for each major as men are, does this data indicate that the college discriminates against women? Be sure you use all available information. (10) You have been assigned to evaluate the quality of care given to heart attack patients at Emergency Room (ER) and Chicago Hope (CH). For the past month, you are given the following patient data: Hospital (ER or CH) Risk category (high or low). Howrisk people are less likely to survive than lowrisk people. o Patient outcome (live or die) Use this data to determine which hospital is doing a better job of caring for heart attack patients. Hint: Use all the data. o o