I need solution for excel

the word file have all the question and have to answer or create in excel which is data

2 Grader - Instructions Excel 2019 Project Exp19_Excel_Ch05_Cap_Apartments Project Description: You manage several apartment complexes in Phoenix, Arizona. You created a dataset that lists details for each apartment complex, such as apartment number, including number of bedrooms, whether the unit is rented or vacant, the last remodel date, rent, and deposits. You will use the datasets to aggregate data to analyze the apartments at the complexes. Steps to Perform: Step Instructions Points Possible 0 1 3 2. 5 3 I Start Excel Download and open the file named Exp19 Excel Chos Cap Apartments xlsx Grader has automatically added your last name to the beginning of the filename Before subtotalling the data, you need to sort the data Select the Summary sheet Sort the data by Apartment Complex in alphabetical order and further sort it by #Bed (the number of bedrooms) from smallest to largest You want to use the Subtotal feature to display the average total deposit by number of bedrooms for each apartment complex Use the Subtotal feature to insert subtotal rows by Apartment Complex to calculate the average Total Deposit Add a second subtotal (without removing the first subtotal) by #Bed to calculate the average Total Deposit by the number of bedrooms Use the outline symbols to display only the subtotal rows Create an automatic outline and collapse the outline above Total Deposit You want to create a Pivot Table to determine the total monthly rental revenue for occupad apartments Display the Rentals sheet and create a blank Pivot Table on a new worksheet to the left of the Rentals sheet Change the name of the worksheet to Rental Revenue. Name the Prvot Table Rental Revenue Display the Apartment Complex and Bled fields in Rows and the Rental Price field as Values Formatte Sum of Rental Price for Accounting Number Format with zero decimal places and enter the custom mame Total Rent Collected 2 4 7 5 6 7 I hp 3 9 15 Select the Occupied field for the filter and set the filter to Yes to display data for occupied apartments You want to calculate the total monthly rental revenue if the rates increase by 5% for the occupied apartments Insert a calculated field to multiply the Rental Price by 1.05 Change the name to New Rental Revenue Apply Accounting Number Format with zero decimal places Select the range B3 C3 and apply these formats: wrap text, Align Right horizontal alignment, and 30 row height Select column B and set 9.29 column width Select column C and set 14.43 column width Apply Light Orange, Pivot Style Medium 10 to the Pivot Table and display banded rows 10 5 11 5 Created On: 08/22/2019 Exp19 Excel CMOS_Cap - Apartments 10 Geader - Instructions Excel 2019 Project Step Instructions Points Possible 12 5 13 6 Insert a slicer for #Bed so that you can filter the dataset by number of bedrooms Change the slicer caption to 8 of Bedrooms Change the slicer height to 1.4 inches and width to 1.75 inches Apply Light Orange, Slicer Style Light 2 Cut the slicer and paste it in cel E2 Insert a timeline for the Last Remodelfeld Change the time period to YEARS Apply Light Orange, Timeline Style Light 2 Change the timeline height to 1.4 inches and with to 3.75 inches The Databases sheet contains two tables You will create a relationship between those tables 14 15 hp 10 00 es. Unless you need to edit, it's safer to stay in Protected View. Enable Editing 5 CSICS GROW.UTTU MOGUCE Style Light 2. Cut the slicer and paste it in cell E2 14 5 15 5 16 5 17 Insert a timeline for the Last Remodel field. Change the time period to YEARS Apply Light Orange, Timeline Style Light 2. Change the timeline height to 1.4 inches and with to 3.75 inches The Databases sheet contains two tables. You will create a relationship between those tables Display the Databases sheet. Create a relationship between the APARTMENTS table using the Code field and the COMPLEX table using the Code field You want to create a Pivot Table from the related tables Create a Pivot Table using the data model on a new sheet Change the sheet name to Bedrooms. Name the Pivottable BedroomData Select the Apartment Name field from the COMPLEX table for Rows, the 3 Bed field for Columns, and the Bed field as Values. This will display the number of apartments with the specified number of bedrooms per apartment complex Display the values as a percentage of row totals Create a Clustered Column PivotChart Cut the chart and paste it in cell A13. Select the 3 bedroom data series and apply the Black, Text 1, Lighter 50% solid il color Apply Black Text 1 font color to the vertical ads and category axis Change the chart height to 3 inches and the width to 5 inches, if necessary. Hide the field buttons in the PivoChart Create a footer on all worksheets with your name in the left the sheet name code in the center, and the file name code in the right Save and close Exp19_Excel_Chos Cap Apartments xlsx Exit Excel Submit the file as directed 5 18 5 19 5 20 5 21 0 Total Points 100 O hp Help O PROTECTED VIEW Be carefules from the Internet can contain viruses. Unless you need to edit, it's safe to stay in Protected View Tell me what you want to do B31 Erable Editing > Rolling Meadows A D E G H 1 2 3 4 5 6 Constants for Formulas Comparison Date 5/1/2021 Years Since Remodel SD Deposit Older (Rent 1.25) 1.0 Deposit Newer (Rent 15) 1.1 # Bed 3 3 2 1 2 2 2 3 2 7 Unit # Apartment Complex B 403 Mountaintop View 9 406 Mountaintop View 10 605 Oak Tree Living 11 107 Rolling Meadows 12 404 Mountaintop View 13 308 Lakeview Apartments 14 207 Rolling Meadows 15 208 Rolling Meadows 16 209 Rolling Meadows 17 307 Lakeview Apartments 18 405 Mountaintop View 19 106 Rolling Meadows 20 210 Rolling Meadows 21 109 Rolling Meadows 22 503 Sunset Valley 23 401 Mountaintop View 24 Mountaintop View 25 110 Rolling Meadows 26 402 Mountaintop View S02 Sunt Valley 28 300 Lakeview Apartments 29 101 Lakeview Apartments 30 607 Oak Tree Living Summary Rentals Last Remodel 5/5/2021 4/1/2021 11/30/2020 11/1/2020 10/4/2020 9/30/2020 8/1/2020 8/1/2020 8/1/2020 6/30/2020 6/15/2020 6/15/2020 11/4/2019 8/15/2019 8/1/2019 7/1/2019 3/1/2019 10/4/2018 8/1/2018 /1/2018 V2018 4/19 2018 3/30/2018 Occupied Yes No Na No No No Yes Yes No Yes Yes Yes Yes No No NO Yes Yes No No Yes No No Non-Refund Years Since Security Cleaning Total Remedel Rental Price Deposit Deposit Deport 0.01$ 1,200 S 1,320$ 250 $ 1,570 0.08 S 1,200 $ 1,320 $ 250 S 1,570 0.425 2,000 $ 2,200 $ 2505 2,450 0.50 $ 1,100 S 1,210 S 200 $ 1.410 0.58 $ 875 S 963 S 150 S 1,113 0.595 1,200 $ 1,320 S 200 S 1,520 0.755 9955 1,095 $ 200 $1.295 0.755 995$ 1.095 S 200 $ 1,295 0.75S 1,050 S 1,155 250 $ 1.405 0.84 1,200 $ 1,320 $ 200 $ 1.520 D.RAS 950 $ 1,045 S 200 S 1,25 OS 250 S 1,900 1.495 1,050 1.155 250 $ 1,405 1.71 S 1,500 1,650 S 250 $1,900 1.755 875 S 963 5 150 S 1,113 1.835 8755 963 $ 150 $ 1,113 2.17 S 975 S 1,0715 200 $1,271 258S 1.500 5 1,650 $ 250 $ 1.000 9505 1,0455 200 S 1,245 2.25 8755 963 150 $ 1,113 2.885 1,200 5 1,120 S 200 5 1,520 3.01 9635 150 $ 1,113 105 2.000 5 2,200 $ 2 3 3 3 1,650 1 407 2 3 2 1 2 1 3 Databases Search O hop $ 9 6 5 4 2 3 R Y T w E Q Prajapati Exp19 Excel C5 Cap Apartments Protected View File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do PROTECTED VIEW Be careful files from the Internet can contain viruses. Unless you need to edit alerto stay in Protected View Enable Editing 31 Rolling Meadows 2 1 0 3 1 2 3 2 2 2 D Yes No No Yes Yes Yes No Yes Yes Yes 2 2 3 501 1 2 A 306 Lakeview Apartments 301 Lakeview Apartments 607 Oak Tree Living 31 102 Rolling Meadows 2 108 Rolling Meadows 33 606 Oak Tree Living 305 Lakeview Apartments 35 203 Rolling Meadows 36 604 Oak Tree Living 37 408 Mountaintop View 38 103 Rolling Meadows 39 104 Rolling Meadows 40 105 Rolling Meadows 41 Sunset Valley 42 603 Oak Tree Living 206 Rolling Meadows 44 303 Lakeview Apartments 45 304 Lakeview Apartments 46 302 Lakeview Apartments 504 Sunset Valley 48 505 Sunset Valley 49 201 Rolling Meadows 50 202 Rolling Meadows 51 101 Rolling Meadows 52 602 Oak Tree Living 53 601 Oak Tree Living 54 506 Sunset Valley 55 507 Sunt Valley 56 Rolling Meadows 57 204 Rolling Meadows 58 59 Summary Rentals 6/15/2018 4/15/2018 3/30/2018 3/1/2018 8/15/2017 3/30/2017 10/4/2016 4/1/2016 12/1/2015 11/15/2015 11/15/2015 10/1/2015 10/1/2015 10/1/2015 8/15/2015 7/1/2015 5/5/2015 5/5/2015 5/1/2015 7/2/2014 7/2/2014 4/11/2014 4/13/2014 4/1/2014 11/1/2013 8/15/2013 10/10/2011 10/10/2011 /15/2011 4/1/2011 2.885 3.04 S 3.09 S 3.17 S 3.715 4.09 $ 4.58 $ SONS 5.42 $ 5.46 5.46 5 5.585 5.58 $ 5.585 5.71 S 5.83 $ 5.95 5.99 6.00 $ 6.815 6.83 S 7.05$ 7.055 7.085 7.50 $ 7,715 99.5 9.565 9.15 10.00 $ G H 1,200 $ 1.320 S 875 S 9635 2.000 $ 2,200 $ 9505 10455 1,100 S 1,210 S 2,000 $ 2.2005 1,200 $ 1,320 S 995 $ 9955 1,200 $ 1,200 $ 975 S 975 S 1,100 S 1,100 S 1,100'S 1,1005 1,500S 1,500 5 875 S 875 5 1,2005 1,200 $ 1.0505 1,050 900 $ 900 $ 900 S 900 S N75 S 8255 R255 875 S 1,100 $ 1,100 $ 8755 8755 875 5 875$ 950 $ 950 $ 1,500 $ 1.500 5 1,500 1,500 1.100 1.100 S 1,1005 1,100 $ 1,0505 1,050 $ 995 9955 No Yes No Yes No No No Yes Na Yes No Yes Yes Yes Yes Yes Yes NO Yes No 200 $ 1520 150 S 1,113 250 S 2450 150 $ 1,195 200 5 1,410 2505 2,450 200 $ 1,520 200 $ 1195 200 $ 1400 200S 1,175 2005 1.300 200 5 1,300 250 S 1,750 150 $ 1,025 200 $ 1,400 250 $ 1,100 150 S 1,050 150 5 1,050 150 S 1,025 150 S 1,025 200 $1,300 150 $ 1.025 150 $ 1,025 150 $ 1.100 150 S 19 150 S 1,650 200 S 1.100 200 5 1,100 250 $ 1,300 2005 1.195 3 1 1 1 1 2 1 1 1 1 1 2 2. 1 2 Databases Search # $ 96 02 5 2 3 Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do PROTECTED VIEW Be careful hiles from the Internet can contain viruses. Unless you need to edit safe to stay in Protected View Enable Editing BB fr Lakeview Apartments D A B E 1 1 1 1 1 Unit Apartment Complex 2 301 Lakeview Apartments 302 Lakeview Apartments 303 Lakeview Apartments 5 304 Lakeview Apartments 6 305 Lakeview Apartments 7 306 Lakeview Apartments 8 307 Lakeview Apartments 9 30R Lakeview Apartments 10 401 Mountaintop View 11 404 Mountaintop View 12 402 Mountaintop View 13 405 Mountaintop View 14 407 Mountaintop View 15 408 Mountaintop View 16 403 Mountaintop View 17 406 Mountaintop View 18 601 Oak Tree Living 19 602 Oak Tree Living 20 603 Oak Tree Living 21 604 Oak Tree Living 22 605 Oak Tree Living 23 606 Oak Tree Living 24 02 Oak Tree Living 25 101 Rolling Meadows 26 102 Rolling Meadows 27 201 Rolling Meadows 28 200 Rolling Meadows 103 Rolling Meadows 104 Rolling Meadows 31 107 Rolling Meadows Summary Rentals Bed Occupied Last Remodel No 4/15/2018 No 5/1/2015 No 5/5/2015 1 Yes 5/5/2015 2 No 10/4/2016 2 Yes 6/15/2018 2 Yes 6/30/2020 2 No 9/30/2020 No 7/1/2019 1 No 10/4/2020 2 No 8/1/2018 2 Yes 6/15/2020 2 Yes 3/1/2019 Yes 11/15/2015 3 Yes 5/5/2021 3 No 4/1/2021 1 Yes 8/15/2013 1 Yes 11/1/2013 2 No 2/15/2015 2 Yes 12/1/2015 3 NO 11/10/2020 3 Yes V10/2017 3 No W30/2018 1 Yes 4/1/2014 1 Yes 3/1/2018 1 Yes 4/11/2016 Yes 4/11/2014 2 No 11/15/2015 2 Yes 10/1/2015 2 NO 11/1/2020 Databases G H Years Since Security Non-Refund Remodel Rental Price Deposit Cleaning Deposit Total Deposit 3.045 875 5 963 S 150 $ 1,113 6.00 $ 875 S 875$ 150 $ 1,025 5.99 $ 900 $ 000 $ 150 S 1,050 5.99 $ 900 $ 900 1505 1,050 4.50 S 1,200 $ 1,320 200 S 1,520 2.85 1,200 $ 1,120 S 200 S 1,520 085 1,200 $ 1,320 S 2005 1,520 0.595 1,200 $1,3205 2005 1.520 1.85 875 $ 963 $ 150 $ 1,113 0.585 875 S 963 + 150 S 1,113 2.755 950 $ 1,045$ 200 $ 1245 0.88 950 $ 1,0155 2005 1,245 2.17 S 975 S 1,073 $ 200 $ 1.273 5.46 $ 975 S 9755 2005 1,175 0.01$ 1,200 $1,320 S 250S 1,570 DOS 1,200 $ 1,3205 2505 1,520 7.71$ 1.500 $ 1.500 5 1505 1.650 7.50 $ 1,500 $1,500 $ 1505 1,650 5.71 S 1,200 $ 1.2005 2005 1.400 5.42 $ 1,200 S 1,200 S 2005 0.42 $ 2.000 $2,200 $ 2505 2,450 4.09 2.000 $2,200 $ 2505 2.450 3.095 2,000 5 2.2005 250 $ 2450 2.085 950 $950 190$ 3.175 950 5 1,0455 1505 1,195 7.055 875 5 / 5 150 $ 1.005 7.05 8.75 SR75S 1505 5.6 S 1.100 S 1,1005 200 $ 1.100 1.100 1.100 2005 1,300 0.50 1,100 $ 1210S 200 1.410 1400 LOS Search O 2 3 W E R J_ rom the Internet can contain Vituses. Unless you need to edit it safe to stay in Protected View Enable Eng 88 Lakeview Apartments 3 606 3 3 1 1 1 1 2 2 2 A 22 Oak Tree Living 23 Oak Tree Living 24 607 Oak Tree Living 25 101 Rolling Meadows 26 102 Rolling Meadows 27 201 Rolling Meadows 28 202 Rolling Meadows 29 103 Rolling Meadows 30 104 Rolling Meadows 31 107 Rolling Meadows 32 108 Rolling Meadows 33 203 Rolling Meadows 34 204 Rolling Meadows 35 207 Rolling Meadows 36 208 Rolling Meadows 37 105 Rolling Meadows 38 106 Rolling Meadows 39 109 Rolling Meadows 40 110 Rolling Meadows 205 Rolling Meadows 42 206 Rolling Meadows 209 Rolling Meadows 44 210 Rolling Meadows 45 501 Sunset Valley 46 502 Sunset Valley 503 Sunset Valley 48 504 Sunset Valley 49 SOS Sunset Valley 50 506 Sunset Valley 51 507 Sunset Valley 52 53 Summary Rentals 2 2 2 2 2 3 3 3 3 D No Yes No Yes Yes Yes Yes No Yes No Yes Yes No Yes Yes No Yes No Yes Yes Na No Yes Yes No No Yes No Yes No E 11/30/2020 3/30/2017 3/30/2018 4/1/2014 3/1/2018 4/13/2014 4/13/2014 11/15/2015 10/1/2015 11/1/2020 8/15/2017 1/1/2016 4/1/2011 8/1/2020 R/1/2020 10/1/2015 6/15/2020 8/15/2019 10/4/2018 8/15/2011 7/1/2015 8/1/2020 11/4/2019 10/1/2015 8/1/2018 1/2019 7/2/2014 7/2/2014 10/10/2011 10/10/2011 0.42 $ 4.09 3.09 $ 7.08 $ 3.17 $ 7.05 S 7.055 5.46 5.58 0.50 $ 3.71 $ 5.08 10.00 $ 0.755 0.755 5.58S 0.88 $ 1.71 $ 2.58 S 9.71 $ 5.8) 0.755 1.49 $ 5.58 2.75$ 1.53 6.83 6835 956 9.56 $ G 2,000 $2,200 S 2,000 $2,200 $ 2,000 $2,200 $ 9505 950 S 950 $ 1,045 S 875 $ 875 875 S 875 $ 1,100 $ 1,100 S 1,100 $ 1,100 $ 1,100 $ 1,210 S 1,100 $ 1.210 S 995 $995 995 $ 9955 995 $ 1.095 995 S 1,095 $ 1,500 $1,500S 1,500 $1,650 1,500 $1,650 $ 1,500 5 1,650 $ 1,050 5 1,050 S 1,650 $ 1.050 1,050 $ 1.155 S 1,050 $ 1.155$ 875 S 875 875 S 9635 R75S961 S 875 $ 8755 1.100 $ 1,100 1.100 1.100 1,100 $ 1,100 S 250 $ 2,450 250 $ 2,450 250 $ 2.450 150 $ 1,100 150 $ 1,195 150 $ 1,025 150 $ 1,025 2005 1,300 200 $ 1,300 2005 1,410 2005 1410 200 $ 1,195 2005 1,195 200 $ 1.295 200S 1,295 250 S 1,750 250 $ 1,900 250 S 1.900 2505 1.900 250 $ 1,300 250 S 1,100 250 $ 1405 2505 150 $ 1025 150 $ 150 S 1,113 150 200S 1,300 200 S 1,300 2005 1,100 3 3 3 3 1 1 1 1 2 2 2 Databases Search O RI $ 4 3 2 5 20 W E T G H File Home Page Layout Fom View M PROTECTED VIEW Be Careful toestrom the Internet can contan you need to 00S Cascarotend Vielf tel me what you want to do ng F G 2005 $ 65 10 005 500$ 1.2005 2005 005 1120 1505 150 10 2005 M OT LA Constants for Formulas Comparison Date 11/2001 4 50 Deposit Oder R25 5 to Deposit Newer Rent 6 11 7 URCode Bed Owed Last Remodel 301 lin Ne 300 LA 1 10 100 IA 51/2015 1 NO 11 304 LA 5/9/2015 1 12 OS 2 NO 13 10/4/2016 206 TA 2 Yes 14 107 6/15/2013 LA 2 Yes 15 308 2020 LA 2 No 16 401 9/30/2020 MV 1 Ne 7/1/2019 17 404 MY 1 NO 101/2000 18 402 MV 2 Nu 8/1/2018 19 MV 2 6/15/2020 20 07 MV Yes 3/1/2019 21 COM MV 2 Yes 11/15/2015 22 403 MY 3 Yes S2021 23 400 MV N 4/1/2001 24 603 OT 1 Y 8/15/2013 25 00 OTL 1 Yes 11/1/2011 26 OTL 2 No 8/15/2015 27 604 OTE 2 Yr 12/1/2015 28 05 OTL No 11/30/2020 29 BOG OTL 1 Yes 3/30/2017 30 01 3 No 3/30/2018 31 101 RM 1 Yes 4/1/2014 32 102 M 1/1/2018 Summary Rentals Databases 1.2005 05 1,105 Los 1,520 1520 1520 1.520 0$ 2.25S 825$ 9505 05 9.55 965 2615 1,0455 2005 200 150 150 2005 2125 5.463 15 DOWS 7.713 7.50 $ 5,71 S 1175 1.5.0 1.570 1,2005 1.500 5 1,500 1,2005 1.2005 1,0735 9755 1.120 1.320 S 1.500 $ 1,500 2005 1.2005 2.2005 2.2005 2,200 9505 1.0455 2005 2005 2505 20 150 $ 150 200 2005 0.42 $ 2.450 3.095 7055 2.000$ 2,000$ 950 950 $ 05 2505 1505 1505 1300 Search O IN 62520 no 1.Excel Os Caps Pulcie Vielface Home Tell me what you do O PROTECTED VIW tle care estrom the Internet can continues you need to deve BUN Unit Code e Bed A Occupied on 28 29 30 Yer OTL OTL RM RM 3 Security Depot Botund Gering Depot 2003 2.2005 22 Yes 2000 2.450 SOS SS 1 1 1 2 2 1505 34 35 36 37 3.8 7S OP 101 102 201 202 103 104 107 108 203 204 207 1,1005 RM RM HM RM RM AM RM EM RM RM 1.100 11003 1,500 1,100 11105 123 40 Last Remodel Years Since Remodel 11/30/2020 V10/2011 4095 1/2013 4/1/2014 2015 7.85 1/2018 3175 413/2014 05 4/11/2014 11/15/2015 5.5 10/1/2015 5. 0.505 R/112017 1s 5085 4/1/2011 10.05 8/1/2020 055 8/1/2020 0.75 10/1/2015 5555 6/15/2020 DS 15/2019 10/2018 2.585 1/2011 9715 7/1/2015 585 11/2010 0.755 11/2011 1.095 10/1/2015 S55 2.755 100 1.410 140 10 15 2005 2005 2005 2005 2005 2005 2005 2005 20$ 995 1,005 1955 43 Yes Yes NO Yes No Yes Yes NO Yes Yes NO Yes No Yes Yes Na NO Yes Yes No No Yes No 2 2 2 2 2 2 3 3 1 3 3 2 3 3 1 1 OS 1 45 46 M 995 9955 1.500 1 SOS 1.500 5 1.500 S R 105 106 100 110 205 206 2009 210 501 502 03 504 5.05 506 503 163 LOS 1.6505 1,0505 SOS 48 49 50 51 52 1,005 13 875 250 250 2505 350 2505 7505 150 150 1503 150 2005 RM M RM SY SV SV SV SV SV SV 8735 1/2018 8255 54 55 56 57 58 59 1 1 2 2 2 /2014 10/10/2011 10/10/2011 5 6.35 9.565 9465 1,100 $ 1,100 $ 11005 13005 No 00 Summary Rentals Databases O O Search 2 Grader - Instructions Excel 2019 Project Exp19_Excel_Ch05_Cap_Apartments Project Description: You manage several apartment complexes in Phoenix, Arizona. You created a dataset that lists details for each apartment complex, such as apartment number, including number of bedrooms, whether the unit is rented or vacant, the last remodel date, rent, and deposits. You will use the datasets to aggregate data to analyze the apartments at the complexes. Steps to Perform: Step Instructions Points Possible 0 1 3 2. 5 3 I Start Excel Download and open the file named Exp19 Excel Chos Cap Apartments xlsx Grader has automatically added your last name to the beginning of the filename Before subtotalling the data, you need to sort the data Select the Summary sheet Sort the data by Apartment Complex in alphabetical order and further sort it by #Bed (the number of bedrooms) from smallest to largest You want to use the Subtotal feature to display the average total deposit by number of bedrooms for each apartment complex Use the Subtotal feature to insert subtotal rows by Apartment Complex to calculate the average Total Deposit Add a second subtotal (without removing the first subtotal) by #Bed to calculate the average Total Deposit by the number of bedrooms Use the outline symbols to display only the subtotal rows Create an automatic outline and collapse the outline above Total Deposit You want to create a Pivot Table to determine the total monthly rental revenue for occupad apartments Display the Rentals sheet and create a blank Pivot Table on a new worksheet to the left of the Rentals sheet Change the name of the worksheet to Rental Revenue. Name the Prvot Table Rental Revenue Display the Apartment Complex and Bled fields in Rows and the Rental Price field as Values Formatte Sum of Rental Price for Accounting Number Format with zero decimal places and enter the custom mame Total Rent Collected 2 4 7 5 6 7 I hp 3 9 15 Select the Occupied field for the filter and set the filter to Yes to display data for occupied apartments You want to calculate the total monthly rental revenue if the rates increase by 5% for the occupied apartments Insert a calculated field to multiply the Rental Price by 1.05 Change the name to New Rental Revenue Apply Accounting Number Format with zero decimal places Select the range B3 C3 and apply these formats: wrap text, Align Right horizontal alignment, and 30 row height Select column B and set 9.29 column width Select column C and set 14.43 column width Apply Light Orange, Pivot Style Medium 10 to the Pivot Table and display banded rows 10 5 11 5 Created On: 08/22/2019 Exp19 Excel CMOS_Cap - Apartments 10 Geader - Instructions Excel 2019 Project Step Instructions Points Possible 12 5 13 6 Insert a slicer for #Bed so that you can filter the dataset by number of bedrooms Change the slicer caption to 8 of Bedrooms Change the slicer height to 1.4 inches and width to 1.75 inches Apply Light Orange, Slicer Style Light 2 Cut the slicer and paste it in cel E2 Insert a timeline for the Last Remodelfeld Change the time period to YEARS Apply Light Orange, Timeline Style Light 2 Change the timeline height to 1.4 inches and with to 3.75 inches The Databases sheet contains two tables You will create a relationship between those tables 14 15 hp 10 00 es. Unless you need to edit, it's safer to stay in Protected View. Enable Editing 5 CSICS GROW.UTTU MOGUCE Style Light 2. Cut the slicer and paste it in cell E2 14 5 15 5 16 5 17 Insert a timeline for the Last Remodel field. Change the time period to YEARS Apply Light Orange, Timeline Style Light 2. Change the timeline height to 1.4 inches and with to 3.75 inches The Databases sheet contains two tables. You will create a relationship between those tables Display the Databases sheet. Create a relationship between the APARTMENTS table using the Code field and the COMPLEX table using the Code field You want to create a Pivot Table from the related tables Create a Pivot Table using the data model on a new sheet Change the sheet name to Bedrooms. Name the Pivottable BedroomData Select the Apartment Name field from the COMPLEX table for Rows, the 3 Bed field for Columns, and the Bed field as Values. This will display the number of apartments with the specified number of bedrooms per apartment complex Display the values as a percentage of row totals Create a Clustered Column PivotChart Cut the chart and paste it in cell A13. Select the 3 bedroom data series and apply the Black, Text 1, Lighter 50% solid il color Apply Black Text 1 font color to the vertical ads and category axis Change the chart height to 3 inches and the width to 5 inches, if necessary. Hide the field buttons in the PivoChart Create a footer on all worksheets with your name in the left the sheet name code in the center, and the file name code in the right Save and close Exp19_Excel_Chos Cap Apartments xlsx Exit Excel Submit the file as directed 5 18 5 19 5 20 5 21 0 Total Points 100 O hp Help O PROTECTED VIEW Be carefules from the Internet can contain viruses. Unless you need to edit, it's safe to stay in Protected View Tell me what you want to do B31 Erable Editing > Rolling Meadows A D E G H 1 2 3 4 5 6 Constants for Formulas Comparison Date 5/1/2021 Years Since Remodel SD Deposit Older (Rent 1.25) 1.0 Deposit Newer (Rent 15) 1.1 # Bed 3 3 2 1 2 2 2 3 2 7 Unit # Apartment Complex B 403 Mountaintop View 9 406 Mountaintop View 10 605 Oak Tree Living 11 107 Rolling Meadows 12 404 Mountaintop View 13 308 Lakeview Apartments 14 207 Rolling Meadows 15 208 Rolling Meadows 16 209 Rolling Meadows 17 307 Lakeview Apartments 18 405 Mountaintop View 19 106 Rolling Meadows 20 210 Rolling Meadows 21 109 Rolling Meadows 22 503 Sunset Valley 23 401 Mountaintop View 24 Mountaintop View 25 110 Rolling Meadows 26 402 Mountaintop View S02 Sunt Valley 28 300 Lakeview Apartments 29 101 Lakeview Apartments 30 607 Oak Tree Living Summary Rentals Last Remodel 5/5/2021 4/1/2021 11/30/2020 11/1/2020 10/4/2020 9/30/2020 8/1/2020 8/1/2020 8/1/2020 6/30/2020 6/15/2020 6/15/2020 11/4/2019 8/15/2019 8/1/2019 7/1/2019 3/1/2019 10/4/2018 8/1/2018 /1/2018 V2018 4/19 2018 3/30/2018 Occupied Yes No Na No No No Yes Yes No Yes Yes Yes Yes No No NO Yes Yes No No Yes No No Non-Refund Years Since Security Cleaning Total Remedel Rental Price Deposit Deposit Deport 0.01$ 1,200 S 1,320$ 250 $ 1,570 0.08 S 1,200 $ 1,320 $ 250 S 1,570 0.425 2,000 $ 2,200 $ 2505 2,450 0.50 $ 1,100 S 1,210 S 200 $ 1.410 0.58 $ 875 S 963 S 150 S 1,113 0.595 1,200 $ 1,320 S 200 S 1,520 0.755 9955 1,095 $ 200 $1.295 0.755 995$ 1.095 S 200 $ 1,295 0.75S 1,050 S 1,155 250 $ 1.405 0.84 1,200 $ 1,320 $ 200 $ 1.520 D.RAS 950 $ 1,045 S 200 S 1,25 OS 250 S 1,900 1.495 1,050 1.155 250 $ 1,405 1.71 S 1,500 1,650 S 250 $1,900 1.755 875 S 963 5 150 S 1,113 1.835 8755 963 $ 150 $ 1,113 2.17 S 975 S 1,0715 200 $1,271 258S 1.500 5 1,650 $ 250 $ 1.000 9505 1,0455 200 S 1,245 2.25 8755 963 150 $ 1,113 2.885 1,200 5 1,120 S 200 5 1,520 3.01 9635 150 $ 1,113 105 2.000 5 2,200 $ 2 3 3 3 1,650 1 407 2 3 2 1 2 1 3 Databases Search O hop $ 9 6 5 4 2 3 R Y T w E Q Prajapati Exp19 Excel C5 Cap Apartments Protected View File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do PROTECTED VIEW Be careful files from the Internet can contain viruses. Unless you need to edit alerto stay in Protected View Enable Editing 31 Rolling Meadows 2 1 0 3 1 2 3 2 2 2 D Yes No No Yes Yes Yes No Yes Yes Yes 2 2 3 501 1 2 A 306 Lakeview Apartments 301 Lakeview Apartments 607 Oak Tree Living 31 102 Rolling Meadows 2 108 Rolling Meadows 33 606 Oak Tree Living 305 Lakeview Apartments 35 203 Rolling Meadows 36 604 Oak Tree Living 37 408 Mountaintop View 38 103 Rolling Meadows 39 104 Rolling Meadows 40 105 Rolling Meadows 41 Sunset Valley 42 603 Oak Tree Living 206 Rolling Meadows 44 303 Lakeview Apartments 45 304 Lakeview Apartments 46 302 Lakeview Apartments 504 Sunset Valley 48 505 Sunset Valley 49 201 Rolling Meadows 50 202 Rolling Meadows 51 101 Rolling Meadows 52 602 Oak Tree Living 53 601 Oak Tree Living 54 506 Sunset Valley 55 507 Sunt Valley 56 Rolling Meadows 57 204 Rolling Meadows 58 59 Summary Rentals 6/15/2018 4/15/2018 3/30/2018 3/1/2018 8/15/2017 3/30/2017 10/4/2016 4/1/2016 12/1/2015 11/15/2015 11/15/2015 10/1/2015 10/1/2015 10/1/2015 8/15/2015 7/1/2015 5/5/2015 5/5/2015 5/1/2015 7/2/2014 7/2/2014 4/11/2014 4/13/2014 4/1/2014 11/1/2013 8/15/2013 10/10/2011 10/10/2011 /15/2011 4/1/2011 2.885 3.04 S 3.09 S 3.17 S 3.715 4.09 $ 4.58 $ SONS 5.42 $ 5.46 5.46 5 5.585 5.58 $ 5.585 5.71 S 5.83 $ 5.95 5.99 6.00 $ 6.815 6.83 S 7.05$ 7.055 7.085 7.50 $ 7,715 99.5 9.565 9.15 10.00 $ G H 1,200 $ 1.320 S 875 S 9635 2.000 $ 2,200 $ 9505 10455 1,100 S 1,210 S 2,000 $ 2.2005 1,200 $ 1,320 S 995 $ 9955 1,200 $ 1,200 $ 975 S 975 S 1,100 S 1,100 S 1,100'S 1,1005 1,500S 1,500 5 875 S 875 5 1,2005 1,200 $ 1.0505 1,050 900 $ 900 $ 900 S 900 S N75 S 8255 R255 875 S 1,100 $ 1,100 $ 8755 8755 875 5 875$ 950 $ 950 $ 1,500 $ 1.500 5 1,500 1,500 1.100 1.100 S 1,1005 1,100 $ 1,0505 1,050 $ 995 9955 No Yes No Yes No No No Yes Na Yes No Yes Yes Yes Yes Yes Yes NO Yes No 200 $ 1520 150 S 1,113 250 S 2450 150 $ 1,195 200 5 1,410 2505 2,450 200 $ 1,520 200 $ 1195 200 $ 1400 200S 1,175 2005 1.300 200 5 1,300 250 S 1,750 150 $ 1,025 200 $ 1,400 250 $ 1,100 150 S 1,050 150 5 1,050 150 S 1,025 150 S 1,025 200 $1,300 150 $ 1.025 150 $ 1,025 150 $ 1.100 150 S 19 150 S 1,650 200 S 1.100 200 5 1,100 250 $ 1,300 2005 1.195 3 1 1 1 1 2 1 1 1 1 1 2 2. 1 2 Databases Search # $ 96 02 5 2 3 Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do PROTECTED VIEW Be careful hiles from the Internet can contain viruses. Unless you need to edit safe to stay in Protected View Enable Editing BB fr Lakeview Apartments D A B E 1 1 1 1 1 Unit Apartment Complex 2 301 Lakeview Apartments 302 Lakeview Apartments 303 Lakeview Apartments 5 304 Lakeview Apartments 6 305 Lakeview Apartments 7 306 Lakeview Apartments 8 307 Lakeview Apartments 9 30R Lakeview Apartments 10 401 Mountaintop View 11 404 Mountaintop View 12 402 Mountaintop View 13 405 Mountaintop View 14 407 Mountaintop View 15 408 Mountaintop View 16 403 Mountaintop View 17 406 Mountaintop View 18 601 Oak Tree Living 19 602 Oak Tree Living 20 603 Oak Tree Living 21 604 Oak Tree Living 22 605 Oak Tree Living 23 606 Oak Tree Living 24 02 Oak Tree Living 25 101 Rolling Meadows 26 102 Rolling Meadows 27 201 Rolling Meadows 28 200 Rolling Meadows 103 Rolling Meadows 104 Rolling Meadows 31 107 Rolling Meadows Summary Rentals Bed Occupied Last Remodel No 4/15/2018 No 5/1/2015 No 5/5/2015 1 Yes 5/5/2015 2 No 10/4/2016 2 Yes 6/15/2018 2 Yes 6/30/2020 2 No 9/30/2020 No 7/1/2019 1 No 10/4/2020 2 No 8/1/2018 2 Yes 6/15/2020 2 Yes 3/1/2019 Yes 11/15/2015 3 Yes 5/5/2021 3 No 4/1/2021 1 Yes 8/15/2013 1 Yes 11/1/2013 2 No 2/15/2015 2 Yes 12/1/2015 3 NO 11/10/2020 3 Yes V10/2017 3 No W30/2018 1 Yes 4/1/2014 1 Yes 3/1/2018 1 Yes 4/11/2016 Yes 4/11/2014 2 No 11/15/2015 2 Yes 10/1/2015 2 NO 11/1/2020 Databases G H Years Since Security Non-Refund Remodel Rental Price Deposit Cleaning Deposit Total Deposit 3.045 875 5 963 S 150 $ 1,113 6.00 $ 875 S 875$ 150 $ 1,025 5.99 $ 900 $ 000 $ 150 S 1,050 5.99 $ 900 $ 900 1505 1,050 4.50 S 1,200 $ 1,320 200 S 1,520 2.85 1,200 $ 1,120 S 200 S 1,520 085 1,200 $ 1,320 S 2005 1,520 0.595 1,200 $1,3205 2005 1.520 1.85 875 $ 963 $ 150 $ 1,113 0.585 875 S 963 + 150 S 1,113 2.755 950 $ 1,045$ 200 $ 1245 0.88 950 $ 1,0155 2005 1,245 2.17 S 975 S 1,073 $ 200 $ 1.273 5.46 $ 975 S 9755 2005 1,175 0.01$ 1,200 $1,320 S 250S 1,570 DOS 1,200 $ 1,3205 2505 1,520 7.71$ 1.500 $ 1.500 5 1505 1.650 7.50 $ 1,500 $1,500 $ 1505 1,650 5.71 S 1,200 $ 1.2005 2005 1.400 5.42 $ 1,200 S 1,200 S 2005 0.42 $ 2.000 $2,200 $ 2505 2,450 4.09 2.000 $2,200 $ 2505 2.450 3.095 2,000 5 2.2005 250 $ 2450 2.085 950 $950 190$ 3.175 950 5 1,0455 1505 1,195 7.055 875 5 / 5 150 $ 1.005 7.05 8.75 SR75S 1505 5.6 S 1.100 S 1,1005 200 $ 1.100 1.100 1.100 2005 1,300 0.50 1,100 $ 1210S 200 1.410 1400 LOS Search O 2 3 W E R J_ rom the Internet can contain Vituses. Unless you need to edit it safe to stay in Protected View Enable Eng 88 Lakeview Apartments 3 606 3 3 1 1 1 1 2 2 2 A 22 Oak Tree Living 23 Oak Tree Living 24 607 Oak Tree Living 25 101 Rolling Meadows 26 102 Rolling Meadows 27 201 Rolling Meadows 28 202 Rolling Meadows 29 103 Rolling Meadows 30 104 Rolling Meadows 31 107 Rolling Meadows 32 108 Rolling Meadows 33 203 Rolling Meadows 34 204 Rolling Meadows 35 207 Rolling Meadows 36 208 Rolling Meadows 37 105 Rolling Meadows 38 106 Rolling Meadows 39 109 Rolling Meadows 40 110 Rolling Meadows 205 Rolling Meadows 42 206 Rolling Meadows 209 Rolling Meadows 44 210 Rolling Meadows 45 501 Sunset Valley 46 502 Sunset Valley 503 Sunset Valley 48 504 Sunset Valley 49 SOS Sunset Valley 50 506 Sunset Valley 51 507 Sunset Valley 52 53 Summary Rentals 2 2 2 2 2 3 3 3 3 D No Yes No Yes Yes Yes Yes No Yes No Yes Yes No Yes Yes No Yes No Yes Yes Na No Yes Yes No No Yes No Yes No E 11/30/2020 3/30/2017 3/30/2018 4/1/2014 3/1/2018 4/13/2014 4/13/2014 11/15/2015 10/1/2015 11/1/2020 8/15/2017 1/1/2016 4/1/2011 8/1/2020 R/1/2020 10/1/2015 6/15/2020 8/15/2019 10/4/2018 8/15/2011 7/1/2015 8/1/2020 11/4/2019 10/1/2015 8/1/2018 1/2019 7/2/2014 7/2/2014 10/10/2011 10/10/2011 0.42 $ 4.09 3.09 $ 7.08 $ 3.17 $ 7.05 S 7.055 5.46 5.58 0.50 $ 3.71 $ 5.08 10.00 $ 0.755 0.755 5.58S 0.88 $ 1.71 $ 2.58 S 9.71 $ 5.8) 0.755 1.49 $ 5.58 2.75$ 1.53 6.83 6835 956 9.56 $ G 2,000 $2,200 S 2,000 $2,200 $ 2,000 $2,200 $ 9505 950 S 950 $ 1,045 S 875 $ 875 875 S 875 $ 1,100 $ 1,100 S 1,100 $ 1,100 $ 1,100 $ 1,210 S 1,100 $ 1.210 S 995 $995 995 $ 9955 995 $ 1.095 995 S 1,095 $ 1,500 $1,500S 1,500 $1,650 1,500 $1,650 $ 1,500 5 1,650 $ 1,050 5 1,050 S 1,650 $ 1.050 1,050 $ 1.155 S 1,050 $ 1.155$ 875 S 875 875 S 9635 R75S961 S 875 $ 8755 1.100 $ 1,100 1.100 1.100 1,100 $ 1,100 S 250 $ 2,450 250 $ 2,450 250 $ 2.450 150 $ 1,100 150 $ 1,195 150 $ 1,025 150 $ 1,025 2005 1,300 200 $ 1,300 2005 1,410 2005 1410 200 $ 1,195 2005 1,195 200 $ 1.295 200S 1,295 250 S 1,750 250 $ 1,900 250 S 1.900 2505 1.900 250 $ 1,300 250 S 1,100 250 $ 1405 2505 150 $ 1025 150 $ 150 S 1,113 150 200S 1,300 200 S 1,300 2005 1,100 3 3 3 3 1 1 1 1 2 2 2 Databases Search O RI $ 4 3 2 5 20 W E T G H File Home Page Layout Fom View M PROTECTED VIEW Be Careful toestrom the Internet can contan you need to 00S Cascarotend Vielf tel me what you want to do ng F G 2005 $ 65 10 005 500$ 1.2005 2005 005 1120 1505 150 10 2005 M OT LA Constants for Formulas Comparison Date 11/2001 4 50 Deposit Oder R25 5 to Deposit Newer Rent 6 11 7 URCode Bed Owed Last Remodel 301 lin Ne 300 LA 1 10 100 IA 51/2015 1 NO 11 304 LA 5/9/2015 1 12 OS 2 NO 13 10/4/2016 206 TA 2 Yes 14 107 6/15/2013 LA 2 Yes 15 308 2020 LA 2 No 16 401 9/30/2020 MV 1 Ne 7/1/2019 17 404 MY 1 NO 101/2000 18 402 MV 2 Nu 8/1/2018 19 MV 2 6/15/2020 20 07 MV Yes 3/1/2019 21 COM MV 2 Yes 11/15/2015 22 403 MY 3 Yes S2021 23 400 MV N 4/1/2001 24 603 OT 1 Y 8/15/2013 25 00 OTL 1 Yes 11/1/2011 26 OTL 2 No 8/15/2015 27 604 OTE 2 Yr 12/1/2015 28 05 OTL No 11/30/2020 29 BOG OTL 1 Yes 3/30/2017 30 01 3 No 3/30/2018 31 101 RM 1 Yes 4/1/2014 32 102 M 1/1/2018 Summary Rentals Databases 1.2005 05 1,105 Los 1,520 1520 1520 1.520 0$ 2.25S 825$ 9505 05 9.55 965 2615 1,0455 2005 200 150 150 2005 2125 5.463 15 DOWS 7.713 7.50 $ 5,71 S 1175 1.5.0 1.570 1,2005 1.500 5 1,500 1,2005 1.2005 1,0735 9755 1.120 1.320 S 1.500 $ 1,500 2005 1.2005 2.2005 2.2005 2,200 9505 1.0455 2005 2005 2505 20 150 $ 150 200 2005 0.42 $ 2.450 3.095 7055 2.000$ 2,000$ 950 950 $ 05 2505 1505 1505 1300 Search O IN 62520 no 1.Excel Os Caps Pulcie Vielface Home Tell me what you do O PROTECTED VIW tle care estrom the Internet can continues you need to deve BUN Unit Code e Bed A Occupied on 28 29 30 Yer OTL OTL RM RM 3 Security Depot Botund Gering Depot 2003 2.2005 22 Yes 2000 2.450 SOS SS 1 1 1 2 2 1505 34 35 36 37 3.8 7S OP 101 102 201 202 103 104 107 108 203 204 207 1,1005 RM RM HM RM RM AM RM EM RM RM 1.100 11003 1,500 1,100 11105 123 40 Last Remodel Years Since Remodel 11/30/2020 V10/2011 4095 1/2013 4/1/2014 2015 7.85 1/2018 3175 413/2014 05 4/11/2014 11/15/2015 5.5 10/1/2015 5. 0.505 R/112017 1s 5085 4/1/2011 10.05 8/1/2020 055 8/1/2020 0.75 10/1/2015 5555 6/15/2020 DS 15/2019 10/2018 2.585 1/2011 9715 7/1/2015 585 11/2010 0.755 11/2011 1.095 10/1/2015 S55 2.755 100 1.410 140 10 15 2005 2005 2005 2005 2005 2005 2005 2005 20$ 995 1,005 1955 43 Yes Yes NO Yes No Yes Yes NO Yes Yes NO Yes No Yes Yes Na NO Yes Yes No No Yes No 2 2 2 2 2 2 3 3 1 3 3 2 3 3 1 1 OS 1 45 46 M 995 9955 1.500 1 SOS 1.500 5 1.500 S R 105 106 100 110 205 206 2009 210 501 502 03 504 5.05 506 503 163 LOS 1.6505 1,0505 SOS 48 49 50 51 52 1,005 13 875 250 250 2505 350 2505 7505 150 150 1503 150 2005 RM M RM SY SV SV SV SV SV SV 8735 1/2018 8255 54 55 56 57 58 59 1 1 2 2 2 /2014 10/10/2011 10/10/2011 5 6.35 9.565 9465 1,100 $ 1,100 $ 11005 13005 No 00 Summary Rentals Databases O O Search