MTSULiveHere is making several apartments available for new leases. Renters will frequently call the staff to receive quotes for rental expenses, including electricity and pets. As pet food prices have been fluctuating the last few years, customers have become more conscious of the price per pound and its impact on their total cost of housing. As a potential service to its renters, MTSULiveHere has modified its Rental workbook to include information on its apartments' electricity usage and average amount of food the renter's pets consume. All that remains to be added is a more detailed analysis of the interaction between pet food prices and total cost of housing, and the amount of pet food per week consumed and its impact on the total cost of housing. Complete the following: 1. Open the workbook named Rental.xlsx file and save it as MTSURental(your last name).xlsx. 2. Examine the Analysis worksheet and apply appropriate range names to cells D17:D20. 3. In cells A23:A28, enter the following cities in Tennessee: Murfreesboro, Smyrna, Manchester, Nashville, Franklin, and Woodbury. The values to enter are: Murfreesboro Smyma Mancheste Nashville Franklin Woodbury 146.900 $ 57,753 51,586 $ 58,302 11,038 $ 48,280 670,820 $52,858 83,097 $92,589 2.889 $ 33,333 4. In D23:28, calculate the ratio of each cities' median household income to Murfreesboro's median household income. Murfreesboro will have a ratio of 1 (its income divided by itself), and other cities will be > 1 when the income is higher than Murfreesboro's. Assume that MTSULiveHere keeps the rental rates for its apartments based on the differences in income in the cities as compared to Murfreesboro (meaning that cities with a greater household income will charge more for rent). You decide that you'd like to live in Smyrna. If Murfreesboro charges MTSULiveHere is making several apartments available for new leases. Renters will frequently call the staff to receive quotes for rental expenses, including electricity and pets. As pet food prices have been fluctuating the last few years, customers have become more conscious of the price per pound and its impact on their total cost of housing. As a potential service to its renters, MTSULiveHere has modified its Rental workbook to include information on its apartments' electricity usage and average amount of food the renter's pets consume. All that remains to be added is a more detailed analysis of the interaction between pet food prices and total cost of housing, and the amount of pet food per week consumed and its impact on the total cost of housing. Complete the following: 1. Open the workbook named Rental.xlsx file and save it as MTSURental(your last name).xlsx. 2. Examine the Analysis worksheet and apply appropriate range names to cells D17:D20. 3. In cells A23:A28, enter the following cities in Tennessee: Murfreesboro, Smyrna, Manchester, Nashville, Franklin, and Woodbury. The values to enter are: 146,900 $ 57,753 51,586 $ 58,302 11,038 $ 48,280 670,820 $ 52,858 Murfreesboro Smyma Mancheste Nashville Franklin Woodbury 83,097 $ 92,589 2,889 $ 33,333 4. In D23:28, calculate the ratio of each cities' median household income to Murfreesboro's median household income. Murfreesboro will have a ratio of 1 (its income divided by itself), and other cities will be > 1 when the income is higher than Murfreesboro's. Assume that MTSULiveHere keeps the rental rates for its apartments based on the differences in income in the cities as compared to Murfreesboro (meaning that cities with a greater household income will charge more for rent). You decide that you'd like to live in Smyrna. If Murfreesboro charges $1000 per month for rent, calculate the rental price for Smyrna in cell D7 (use an outside reference for the $1000 base rate, Label in cell F16, the rate in cell G16). 5. Set up the structure of a one variable data table in the marked cells that shows the apartment rental price, electricity charge, pet food expense, and projected housing expense given changes in the average price of pet food from $1.50 to $3.10 by increments of $0.20. 6. Instruct Excel to complete the one-variable data table. 6. Instruct Excel to complete the one-variable data table. HINT: To complete the one-variable data table successfully, be sure to select the correct data table range. If you instruct Excel to complete an incorrect range, you cannot just delete any "extra" results that might have appeared at the bottom or right of the intended data table area. When you try to do so, you will receive the "Cannot change part of a data table error message. You cannot delete only some results from a filled-in data table; you must delete all results values from a one-variable data table if you want to delete any results. 7. Add headings and basic formatting to the data table so it has a more professional appearance.