can you please show the steps as well Step Instructions Possible 1 0 2 3 3 5 2 5 7 Start Excel Download and open the file named Exp19 Excel Cho5 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 occupied apartments Display the Rentals sheet and create a blank PivotTable on a new worksheet to the left of the Rentals sheet Change the name of the worksheet to Rental Revenue Name the Pivot Table Rental Revenue Display the Apartment Complex and Bed fields in Rows and the Rental Price field as Values Format the Sum of Rental Price for Accounting Number Format with zero decimal places and enter the custom name Total Rent Collected, 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 03 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 6 6 3 7 3 8 9 15 10 5 11 Apply Light Orange, Pivot Style Medium 10 to the PivotTable and display banded rows Gradur Instructions En 2015 Pro Step Instructions Points Possible 12 5 13 6 14 5 15 5 16 5 Insert a slicer for Bed so that you can filter the dataset by number of bedrooms Change the slicer caption to of Bedrooms Change the slicer height to 1 4 inches and wielth to 1 75 inches Apply Light Orange, Slicer Style Light 2 Cut the slicer and paste it in cell E2 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 Pivot Table BederomData Select the Apartment Name field from the COMPLEX table for Rows, the 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 using the Destination Theme Select the 3 bedroom data series and apply the Black, Text 1, Lighter 50 solid fill color, Apply Black, Text 1 font color to the vertical axis and category axis Change the chart height to 3 inches and the width to 5 inches, if necessary Hide the field buttons in the PivotChart 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 Excol Cho5 Cap Apartments xlsx Exit Excel Submit the file as directed 17 5 18 5 19 5 20 5 0 21 Total Points 100 IF fx SUBTOTALE A D E F G 2 3 Constants for Formulas Comparison Date Years Since Remodel Deposit Older (Rent 1 25) Deposit Newer (Rent'1,5) 2021 05 01 5 0 1 0 1 1 INN Unit Apartment Complex 403 Lakeview Apartments 406 Lakeview Apartments 605 Lakeview Apartments 107 Lakeview Apartments 404 Lakeview Apartments 308 Lakeview Apartments 207 Lakeview Apartments 208 Lakeview Apartments 209 Mountaintop View 307 Mountaintop View 405 Mountaintop View 106 Mountaintop View 210 Mountaintop View 109 Mountaintop View 503 Mountaintop View 401 Mountaintop View 407 Oak Tree Living 110 Oak Tree Living 402 Oak Tree Living 502 Oak Tree Living 306 Oak Tree Living 301 B27 Oak Tree Living 507 Oak Tree Living 102 Rolling Meadows 08 Rolling Meadows 06 Rolling Meadows 05 Rolling Meadows 03 Rolling Meadows 04 Rolling Meadows Summary Rentals Bed Occupied Last Remodel 3 Yes 2021 05 05 3 No 2021 04 01 3 No 2020 11 30 2 No 2020 11 01 1 No 2020 10 04 2 No 2020 09 30 Yes 2020 08 01 Yes 2020 08 01 3 No 2020 08 01 2 Yes 2020 06 30 2 Yes 2020 06 15 3 2020 06 15 3 Yes 2019 11 04 3 No 2019 08 15 1 No 2019 08 01 1 No 2019 07 01 2 Yes 2019 03 01 3 Yes 2018 10 04 2 No 2018 08 01 1 No 2018 08 01 2 Yes 2018 06 15 1 No 2018 04 15 3 No 2018 03 30 1 Yes 2018 03 01 2 Yes 2017 08 15 3 Yes 2017 03 30 2 No 2016 10 04 2 Yes 2016 04 01 2 Yes 2015 12 01 Databases Non Refund Years Since Security Cleaning Total Remodel Rental Price Deposit Deposit Deposit 0 01$ 1,200 $ 1,320 $ 250 $ 1,570 0 08 $ 1,200 $ 1,320 $ 250 $ 1 570 0 42 $ 2,000 $ 2,200 $ 250 $ 2,450 0 50 IS 1 100 S 1,210 $ 200 $ 1,410 0 58S 875$ 963 $ 150 $ 1,113 0 59 $ 1,200 $ 1,320 $ 200 $ 1 520 0 75 995 S 1,095 $ 200 $ 1,295 0 75$ 995 $ 1,095$ 200 $ 1 295 0 75 1,050 $ 1 155 $ 250 $ 1,405 0 84 1,200 $ 1,320 $ 200 $ 1,520 0 88 $ 950 $ 1,045 $ 200 $ 1,245 0 88 $ 1,500 $ 1,650 $ 250 $ 1,900 1 49 $ 1,050 $ 1,155$ 250 $ 1,405 1 71 $ 1,500 5 1,650 $ 250 $ 1,900 1 75$ 875 S 963 $ 150 $ 1,113 1 83 $ 875 $ 963 $ 150 $ 1,113 2 17 $ 975 $ 1,073 $ 200 $ 1,273 2 58 $ 1,500 $ 1,650 $ 250 $ 1,900 2 75 $ 950 $ 1,045 $ 200 $ 1,245 2 75 S 875 $ 963 S 150 $ 1,113 2 88 $ 1,200 $ 1,320 $ 200 $ 1,520 3 04 $ 875 $ 963 $ 150 $ 1,113 3 09 $ 2,000 $ 2,200 $ 250 $ 2,450 3 17 $ 950 $ 1,0455 150 $ 1,195 3 71S 1,100 $ 1,210 $ 200 $ 1,410 4 09 $ 2,000 $ 2,200 $ 250 $ 2,450 4 58 5 1,200 $ 1,320 $ 200 $ 1,520 5 08 $ 995 $ 995 $ 200 $ 1,195 5 42 $ 1,200 $ 1,200 $ 200 $ 1,400 F x fx SUBTOTAL G F 1 1 C D NO No Yes No No Yes Yes 108 Yes No Yes Yes Yes NO Yes No Yes A 8 OUT 402 Oak Tree Living 502 Oak Tree Living 306 Oak Tree Living 301 Oak Tree Living 507 Oak Tree Living 102 Rolling Meadows Rolling Meadows 606 Rolling Meadows 305 Rolling Meadows 203 Rolling Meadows 604 Rolling Meadows 408 Rolling Meadows 103 Rolling Meadows 104 Rolling Meadows 105 Rolling Meadows 501 Rolling Meadows 603 Rolling Meadows 206 Rolling Meadows 303 Rolling Meadows 304 Rolling Meadows 302 Rolling Meadows 504 Rolling Meadows 505 Rolling Meadows 201 Rolling Meadows 202 Rolling Meadows 101 Sunset Valley 602 Sunset Valley 601 Sunset Valley 506 Sunset Valley 507 Sunset Valley 205 Sunset Valley 204 Sunset Valley 2 1 2 1 3 1 2 3 2 2 2 2 2 2 3 1 2 3 1 1 1 1 2 1 1 1 1 1 2 2 3 Z E ZOO 2018 08 01 2018 08 01 2018 06 15 2018 01 15 2018 03 30 2018 03 01 2017 08 15 2017 03 30 2016 10 04 2016 04 01 2015 12 01 2015 11 15 2015 11 15 2015 10 01 2015 10 01 2015 10 01 2015 08 15 2015 07 01 2015 05 05 2015 05 05 2015 05 01 2014 07 02 2014 07 02 2014 04 13 2014 04 13 2014 04 01 2013 11 01 2013 08 15 2011 10 10 2011 10 10 2011 08 15 2011 04 01 D56 2 755 2 75$ 2 88 $ 3 045 3 09 $ 3 17 3 71 S 4 09 $ 4 58 $ 5 085 5 42 $ 5 46 S 5 46 $ 5 58S 5 58S 5 58 S 5 71 $ 5 83 $ 5 99 $ 5 99 $ 6 00 $ 6 83 $ 6 83 7 05 7 05 S 7 08 $ 7 505 7 71 $ 9 56 S 9 565 9 71 5 10 085 950 $ 875 $ 1,200 $ 875$ 2,000 $ 950 S 1,100 $ 2,000 $ 1,200 $ 995 $ 1,200 $ 975 S 1,100 $ 1 100 S 1,500 $ 875$ 1 2005 1,050 S 900 900 875 875$ 1,100 S 875$ 8755 950 $ 1,500 $ 1,500 $ 1,100 $ 1,100 $ 1,050 $ 995 $ UU 1,045$ 963 $ 1,320 $ 963 $ 2,200 $ 1,045 5 1,210 $ 2,200 $ 1,320 S 995 $ 1,200 $ 975 S 1,100 $ 1,100 $ 1,500 875 $ 1,200 $ 1 050 S 900 $ 900 S 875 $ 875$ 1 100 S 875 $ 875 S 950 $ 1,500 $ 1,500 $ 1,100 $ 1,1005 1,050 S 995 $ 200 $ 1,245 150 $ 1,113 200 5 1,520 150 $ 1,113 250 $ 2,450 150 $ 1,195 200 $ 1,410 250 $ 2 450 200 $ 1,520 200 $ 1,195 2005 1 400 200 $ 1,175 200 $ 1,300 200 $ 1,300 250 $ 1 750 150 $ 1,025 200 $ 1,400 250 5 1,300 150 $ 1,050 150 5 1,050 150 S 1,025 150 $ 1,025 200 5 1,300 150 $ 1,025 150 S 1,025 150 5 1,100 150 $ 1,650 150 5 1,650 200 $ 1,300 200 $ 1,300 250 $ 1,300 200 $ 1,195 NO No No Wes No Yes No Yes Yes Yes Yes Yes Yes No Yes No Excel File Edit View Insert Format Tools Data Window Help Anchal Exp19 Excel ch05 c Home Insert Draw Page Layout Formulas Data Review View Cut Calibri Body) 11 A A General WIDTaxt Copy Paste B 1 Merge Ce Formal IF Xfx SUBTOTAL(Rentals B D 1 1 2 2 2 2 1 1 2 2 2 3 301 302 303 304 305 306 307 308 401 404 402 405 407 408 403 405 601 602 603 604 605 606 507 101 102 201 202 103 104 107 108 203 204 207 200 105 Apartment Complex Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Mountaintop View Mountaintop View Mountaintop View Mountaintop View Mountaintop View Mountaintop View Mountaintop View Mountaintop View Oak Tree living Oak Tree Living Oak Tree in Oak Tree Living Oak Tree Living Oak Tree Living Oak Tree Living Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Mollie Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows F G Years Since Security No Refund Remodel tnt P Deposit Cleaning Deposit Total Depot 3 04 875 5 963 5 150 5 1 113 6 00 5 875 $ 8755 1505 1 025 5 99 $ 900 5900 S 150 $ 1 050 5 99 $ 900 $ 900$ 1505 1 050 4 585 1,200 $ 1 320 $ 2005 1,520 2 885 1,200 $ 1,320 $ 2005 1 520 0 845 1 200 $ 1 320 S 2005 1,520 0 59 $ 1,200 5 1,320$ 2005 1,520 1835 875 S 9635 1505 9,113 0 585 875 $ 953 S 150 $ 1,113 2 755 950 5 1,0455 200 1 245 0 885 950 $ 1 045$ 2005 1 245 2 17 $ 975 5 1,073 $ 2005 1,273 5 46 5 975 S 9755 2005 1,175 0 01$ 1,200 5 1,3205 250 $ 1,570 0 08 1,200 5 1,320$ 250 $ 1,570 7715 1 500 $ 1,500 $ 150 $ 1,650 7 50 $ 1 500 5 1,500 5 150 $ 1,650 5 715 1,200 $ 1 2005 2005 1,400 5 42 $ 1,200 $ 1 2005 2005 1 400 0 42 $ 2,000 $2,200 250 5 2 450 4 095 2 000 $2,200 $ 250 $ 2,450 3 09 $ 2,000 5 2,200 $ 250 $ 2,450 7 085 9505 950 $ 150 S 1 100 3 175 950 $ 1 045 $ 150 $ 1,195 7 055 875 $ 875 $ 1505 1,025 705$ 875 $ 875$ 150 S 1 025 5 46 5 1,100 $ 1,100 $ 2005 1,300 5 585 1 100 1 100 2005 1,300 0 50 $ 1 100 $ 1 210 $ 2005 1 410 3 71 5 1,100 5 1,210 S 200 $ 1,410 5 085 995 $ 9955 2009 1,195 10 08 995 $ 9955 200 $ 1 195 0 755 995 $ 1 0955 2005 1,295 0 755 995 $ 1 0955 2005 12295 5 585 1 500 $ 1 500 $ 250 $ 1,750 Occupied Lant Remodel Na 2018 04 15 No 2015 05 01 No 2015 05 05 Yes 2015 OS OS No 2016 10 04 Yes 2015 06 15 Yes 2020 06 30 No 2020 09 30 No 2019 07 01 No 2020 10 04 No 2018 08 01 Yes 2020 06 15 Yes 2019 03 01 Yes 2015 11 15 Yes 2021 05 05 No 2021 04 01 Yes 2013 05 15 Yes 2013 11 01 No 2015 08 15 Yel 2015 12 01 No 2020 11 30 Yes 2017 03 30 No 2018 03 30 Yes 2014 04 01 Yes 2018 03 01 Yes 2014 04 13 Yes 2014 04 13 NO 2015 11 15 Yes 2015 10 01 No 2020 11 01 Yes 2017 08 15 Yes 2016 04 01 No 2011 04 01 Yes 2020 08 01 Yes 2020 08 01 No 2015 10 01 Databases 1 2 2 3 3 3 1 1 1 1 2 2 2 2 2 2 2 2 3 Summary Rentals Point O 1 4 Excel File Edit View Insert Format Tools Data Help Window Anchal Exp19 Excel ch05 Ca Formulas Data Review View General A wp Text Merge Center IF 1 20 29 10 31 32 33 1025 1,300 1,100 1 410 1 450 Home Insert Draw Page Layout Out Caitl (Bodyt 1 Copy B VU Po xfxSUBTOTAL Rentals D 202 Robin Meadows 1 Yies 108 Rolling Meadows 2 NO 104 Rolling Meadows 2 Yes 107 Rolling Meadows 2 NO 108 Rolling Meadows 2 Yes 203 Rolling Meadows 2 Yes 204 Rolling Meadows 2 No 207 Ring Meadows 2 Ves 208 Rolling Meadows 2 Yes 105 Rolling Meadows 3 No 106 Rolling Meadows 3 Yes 109 Rolling Meadows 3 No 110 Rolling Meadows Yes 205 Rolling Meadows 3 Yes 206 Rolling Meadows No 209 Rolling Meadows Ne 210 3 Rolling Meadows Yes 501 Sunset Valley 1 Yes SO2 Sunset Valley 1 No SOB Sunset Valley 1 No 504 Sunt Valley Ves 505 Sunset Valley 2 No 506 Sunset Valley Yes 507 Sunset Valley 2 No 16 17 3 9 0 1 2014 04 11 2015 11 15 2015 10 01 2020 11 03 2017 18 2016 04 03 2011 04 01 2020 08 01 2020 08 01 2015 10 01 2000 06 15 2019 08 15 2018 10 04 2011 08 15 2015 07 01 202008 01 2019 11 04 2015 10 01 2018 08 01 2019 08 01 2014 07 02 2014 07 02 2011 10 10 2011 10 10 2 05 5 46 5 SSSS 0 505 3 715 5 085 10 08 0755 0 75 550 $ 0 88 1 713 2 58 9 71 5 83 $ 0 75$ 1493 5 585 2 755 1 75$ 6 835 6835 9 565 9 55$ G H 375 38755 1,100 $ 1,100 1 100 1 1005 1,100 $ 1 210 1 100 S12105 995 5 9955 995 $ 9955 995 $ 1 095 995 $ 1 0955 1 500 $1,500 $ 1,500 $ 1 650 1 500 5 1,650 $ 1,500 $ 1 650 1,050 $ 1 050 $ 1,050 $ 1 050 $ 1,050 $ 1 1555 1,050 $ 1 355 875 5875 875 5 963 8755 963 8755 875$ 1,100 $ 1 100 5 1,100 5 1,100 5 1,100 $ 1 100 5 1505 2005 2003 2005 2003 2005 2005 200 $ 2005 250 250 5 250 S 250 $ 2505 250 $ 250 $ 250 S 1505 1505 1505 150 $ 2009 200 $ 200 $ 1 195 1 195 1 295 1,295 1750 1 900 1,900 1 900 1,300 1 100 1,405 1 405 1,025 1 113 NE 1 025 1,300 1 300 1,300 Summan Pantal natan IF A 1 x fx SUBTOTAL(Databases B E Constants for Formulas Comparison Date 2021 05 01 Years Since Remodel 5 0 Deposit Older Rent 1 25) 1 0 Deposit Newer Rent 1 5) 1 1 2 4 $ 302 7 Unite Code Bed 301 LA 9 LA 10 303 LA 1 11 304 LA 12 305 LA 2 13 306 LA 2 14 307 LA 2 15 308 LA 2 16 401 MV 1 17 404 MV 1 18 402 MV 2 19 405 MV 2 20 407 MV 2 21 408 MV 2 22 403 MV 3 23 406 MV 3 24 601 OTL 1 25 602 OTL 1 26 603 OTE 2 27 604 OTL 2 28 605 on 3 29 606 OTL 3 30 507 OTL 3 31 101 RM 2 32 102 RM 33 201 RM 1 14 202 RM 35 103 RM 2 36 104 RM 37 107 RM 2 38 Occupied Last Remodel Years Since Remodel Rental Price Security Depost Non Refund Cleanins Deposit No 2018 04 15 3 04 $ 8755 9635 No 2015 05 01 6 00 $ 875 $ 875$ No 2015 05 05 5,99 $ 900 $ 900 5 Yes 2015 OS OS 5 99 $ 900 $ 500 $ No 2016 10 04 4 58 $ 1,2005 1 3205 Yes 2018 06 15 2 88 $ 1,200 $ 1 320$ Yes 2020 06 30 0 84 5 1,200 $ 1 320$ No 2020 09 30 0 59 $ 1,200 $ 1,320$ No 2019 07057 1 83 875 S 963 5 No 2020 10 04 0 58$ 875 $ 963 $ No 2018 08 01 2 75$ 950 S 1,045 5 Yes 2020 06 15 0 88 $ 950 $ 1,045$ Yes 2019 03 01 2 17 5 975$ 1,073 $ Yes 2015 11 15 5 46 $ 975 $ 975 $ Yes 2021 05 os 0 01$ 1,200 $ 1,320 S No 2021 04 01 0 08 $ 1,200 $ 1,320$ Yes 2013 08 15 7 71 $ 1,500 $ 1,500 $ Yes 2013 11 01 7 50 $ 1,500 1 500 $ No 2015 08 15 5 71 $ 1,2005 1,200 $ Yes 2015 12 01 5 42 $ 1,200 $ 1,200 $ No 2020 11 30 0 42 $ 2,000 $ 2 2005 F27 Yes 2017 03 24 4 09 $ 2,000 $ 2,200 $ No 2018 03 30 3 09 $ 2,000 $ 2,200 $ Yes 2014 04 01 7 08 $ 950 $ 950 $ Yes 2018 03 01 3 17 $ 950 $ 1,045 $ Yes 2014 04 13 7 05 $ 875 S 8755 Yes 2014 04 13 7 05 S 875$ 875 $ No 2015 11 15 5,46 $ 1,1005 1,100 $ Yes 2015 10 01 S 58 S 1,100 $ 1,100 5 No 2020 11 01 0 50 $ 1,100 $ 1,210 5 17025 Rentals Databases Total Deposit 150 $ 1,113 1505 1,025 1505 1,050 150 $ 1,050 2005 1,520 2005 1 520 2005 2005 1,520 1505 1 111 1505 2005 1,245 2005 1,245 200 $ 1,273 2005 1,175 2505 1 570 2505 1,570 150 5 1 650 150 5 1 650 2005 1,400 200 $ 1 400 250$ 2 450 2505 2 450 250 S 2,450 150 $ 1,100 150 5 1,195 150 5 1,025 150 S 1,025 2005 1,300 2005 1 800 2005 1,410 Summary Point Conditional for Formatting as Home Insert Draw Page Layout Formulas Data Review View X Cut Calibri (Body) 11 A Wrap Text General Copy Paste B 1 U A Format Merge Center S IF x fxSUBTOTAL(Databases Unit Code Bled Occupied Last Remodel Years Since Remodel Rental Price Security Deposit Non Refund Cleaning Deposit 28 605 OT No 2020 11 30 0 42 2,000 $ 2 2005 606 OTL 3 Yes 2017 03 30 4 09 $ 2,000 $ 2,200 $ 30 507 OTL 3 No 2018 03 30 3 09 $ 2,000 $ 2,200 $ 31 101 RM 1 Yes 2014 04 01 7 08 $ 950 $ 950 $ 32 102 RM 1 Yes 2018 03 01 3 17 $ 950 $ 1,045$ 33 201 RM 1 Yes 2014 04 13 7 05 875$ 875 $ 34 202 RM 1 Yes 2014 04 13 7 05 $ 8755 875 S 35 103 RM 2 No 2015 11 15 5 46 5 1 100 S 1,100 $ 36 104 RM 2 Yes 2015 10 01 5 58 S 1 100 $ 1 100 S 32 107 RM 2 No 2020 11 01 0 50 $ 1,100 $ 1,210 5 38 108 RM 2 Yes 2017 08 15 3 71 $ 1,100 $ 1 210 $ 203 RM 2 Yes 2016 04 01 5 08 $ 995 $ 9955 10 204 RM 2 No 2011 04 01 10 08 995 $ 995 $ 207 EM 2 Yes 2020 08 01 0 75 $ 9955 1,095 $ 42 RM 2 Yes 2020 08 01 0 755 995$ 1,095 105 RM 3 No 2015 10 03 5 58 $ 1,500 $ 1,500 $ 106 RM 3 Yes 2020 06 15 0 88 $ 1 500 5 1,650 $ 45 109 RM 3 No 2019 08 15 1 71 5 1,500 $ 1 6505 110 RM 3 Yes 2018 10 04 2 58 $ 1,500 $ 1,650 $ 205 RM 3 Yes 2011 08 15 9 71 $ 1,050 $ 1,050 $ 18 206 RM 3 No 2015 07 01 5 83 1,050 $ 1,0505 19 209 RM 3 No 2020 08 01 0 75 $ 1,050 $ 1,155 $ 50 210 RM 3 Yes 2019 11 04 1 49 $ 1,050 $ 1,155$ 501 SV 1 Yes 2015 10 01 5 58 $ 875 $ 875$ 502 5V 1 No 2018 08 01 2 75$ 8755 963 $ 53 SO3 SV 1 No 2019 08 01 1 75$ 875$ 963 $ 504 SV 1 Yes 2014 07 02 6 83 $ 875 $ 8755 55 505 5V 2 No 2014 07 02 6 83 $ 1,100 $ 1,100 $ 56 506 SV Yes 2011 10 10 9 56 $ 1 100 $ 507 SV 2 No 2011 10 16 9 56 $ 1,100 $ 1,100 $ F56 Total Deposit 2505 2 450 250 $ 2 450 250 5 2 450 150 $ 1,100 1505 1,195 150 $ 1,025 150 $ 1,025 200 $ 1,300 2005 1 300 2005 1,410 2005 1 410 2005 1,195 2005 1,195 2005 1,295 2005 1,295 2505 1,750 250 S 1,900 250 $ 1 900 2505 1,900 250 5 1,300 250 5 1 100 250 5 1,405 250 S 1,405 150 5 1,025 150 S 150 S 1 115 150 $ 200 $ 1 300 2005 1,300 200 $ 1 300 1 025 1 100 S 59 30 62 63 Wrap Text General Fill Insert Delete Format $ ) Merge Center Conditional Format Formatting as Table 00 Cell Styles Sort Filte Clear K M N H 10 Code LA MV OTL RM SV Apartment Name Lakeview Apartments Mountaintop View Oak Tree Living Rolling Meadows Sunset Valley Security Deposit Non Refund Cleaning Deposit 8755 9636 8755 875 $ 900 $ 900S 900 $ 900 $ 1,200 $ 1,320 S 1,200 $ 1,320 $ 1,200 $ 1,320 $ 1,200 $ 1,320 S 875 S 963 $ 875$ 963 S 950 $ 1,045 $ 950 $ 1,045 $ 975$ 1,0735 9755 975 S 1,200 $ 1,320 $ 1,200 $ 1,3205 1 500 5 1,500S 1,500 $ 1,500 $ 1,200 $ 1,200 $ 1 200 $ 1,200 $ 2,000 5 2 2005 2,000 5 2 2005 2 000 5 2,200 $ 950 5 950 $ 950 5 1,045 5 875$ 875$ 75 8255 Total Deposit 150 $ 1,113 150 $ 1,025 150 $ 1,050 150 $ 1,050 200 $ 1,520 200 $ 1,520 2005 1,520 200 $ 1,520 150 $ 1,113 150 S 1,113 200 1,245 117 200 1,245 200 $ 1,273 200 $ 1,175 250 $ 1,570 250 $ 1,570 150 $ 1,650 150 $ 1,650 200 $ 1,400 200 $ 1,400 250 $ 2,450 250 $ 2,450 250 $ 2,450 150 $ 150 S 1 195 150 5 1,025 150 $ 1,025 1,100 Step Instructions Possible 1 0 2 3 3 5 2 5 7 Start Excel Download and open the file named Exp19 Excel Cho5 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 occupied apartments Display the Rentals sheet and create a blank PivotTable on a new worksheet to the left of the Rentals sheet Change the name of the worksheet to Rental Revenue Name the Pivot Table Rental Revenue Display the Apartment Complex and Bed fields in Rows and the Rental Price field as Values Format the Sum of Rental Price for Accounting Number Format with zero decimal places and enter the custom name Total Rent Collected, 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 03 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 6 6 3 7 3 8 9 15 10 5 11 Apply Light Orange, Pivot Style Medium 10 to the PivotTable and display banded rows Gradur Instructions En 2015 Pro Step Instructions Points Possible 12 5 13 6 14 5 15 5 16 5 Insert a slicer for Bed so that you can filter the dataset by number of bedrooms Change the slicer caption to of Bedrooms Change the slicer height to 1 4 inches and wielth to 1 75 inches Apply Light Orange, Slicer Style Light 2 Cut the slicer and paste it in cell E2 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 Pivot Table BederomData Select the Apartment Name field from the COMPLEX table for Rows, the 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 using the Destination Theme Select the 3 bedroom data series and apply the Black, Text 1, Lighter 50 solid fill color, Apply Black, Text 1 font color to the vertical axis and category axis Change the chart height to 3 inches and the width to 5 inches, if necessary Hide the field buttons in the PivotChart 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 Excol Cho5 Cap Apartments xlsx Exit Excel Submit the file as directed 17 5 18 5 19 5 20 5 0 21 Total Points 100 IF fx SUBTOTALE A D E F G 2 3 Constants for Formulas Comparison Date Years Since Remodel Deposit Older (Rent 1 25) Deposit Newer (Rent'1,5) 2021 05 01 5 0 1 0 1 1 INN Unit Apartment Complex 403 Lakeview Apartments 406 Lakeview Apartments 605 Lakeview Apartments 107 Lakeview Apartments 404 Lakeview Apartments 308 Lakeview Apartments 207 Lakeview Apartments 208 Lakeview Apartments 209 Mountaintop View 307 Mountaintop View 405 Mountaintop View 106 Mountaintop View 210 Mountaintop View 109 Mountaintop View 503 Mountaintop View 401 Mountaintop View 407 Oak Tree Living 110 Oak Tree Living 402 Oak Tree Living 502 Oak Tree Living 306 Oak Tree Living 301 B27 Oak Tree Living 507 Oak Tree Living 102 Rolling Meadows 08 Rolling Meadows 06 Rolling Meadows 05 Rolling Meadows 03 Rolling Meadows 04 Rolling Meadows Summary Rentals Bed Occupied Last Remodel 3 Yes 2021 05 05 3 No 2021 04 01 3 No 2020 11 30 2 No 2020 11 01 1 No 2020 10 04 2 No 2020 09 30 Yes 2020 08 01 Yes 2020 08 01 3 No 2020 08 01 2 Yes 2020 06 30 2 Yes 2020 06 15 3 2020 06 15 3 Yes 2019 11 04 3 No 2019 08 15 1 No 2019 08 01 1 No 2019 07 01 2 Yes 2019 03 01 3 Yes 2018 10 04 2 No 2018 08 01 1 No 2018 08 01 2 Yes 2018 06 15 1 No 2018 04 15 3 No 2018 03 30 1 Yes 2018 03 01 2 Yes 2017 08 15 3 Yes 2017 03 30 2 No 2016 10 04 2 Yes 2016 04 01 2 Yes 2015 12 01 Databases Non Refund Years Since Security Cleaning Total Remodel Rental Price Deposit Deposit Deposit 0 01$ 1,200 $ 1,320 $ 250 $ 1,570 0 08 $ 1,200 $ 1,320 $ 250 $ 1 570 0 42 $ 2,000 $ 2,200 $ 250 $ 2,450 0 50 IS 1 100 S 1,210 $ 200 $ 1,410 0 58S 875$ 963 $ 150 $ 1,113 0 59 $ 1,200 $ 1,320 $ 200 $ 1 520 0 75 995 S 1,095 $ 200 $ 1,295 0 75$ 995 $ 1,095$ 200 $ 1 295 0 75 1,050 $ 1 155 $ 250 $ 1,405 0 84 1,200 $ 1,320 $ 200 $ 1,520 0 88 $ 950 $ 1,045 $ 200 $ 1,245 0 88 $ 1,500 $ 1,650 $ 250 $ 1,900 1 49 $ 1,050 $ 1,155$ 250 $ 1,405 1 71 $ 1,500 5 1,650 $ 250 $ 1,900 1 75$ 875 S 963 $ 150 $ 1,113 1 83 $ 875 $ 963 $ 150 $ 1,113 2 17 $ 975 $ 1,073 $ 200 $ 1,273 2 58 $ 1,500 $ 1,650 $ 250 $ 1,900 2 75 $ 950 $ 1,045 $ 200 $ 1,245 2 75 S 875 $ 963 S 150 $ 1,113 2 88 $ 1,200 $ 1,320 $ 200 $ 1,520 3 04 $ 875 $ 963 $ 150 $ 1,113 3 09 $ 2,000 $ 2,200 $ 250 $ 2,450 3 17 $ 950 $ 1,0455 150 $ 1,195 3 71S 1,100 $ 1,210 $ 200 $ 1,410 4 09 $ 2,000 $ 2,200 $ 250 $ 2,450 4 58 5 1,200 $ 1,320 $ 200 $ 1,520 5 08 $ 995 $ 995 $ 200 $ 1,195 5 42 $ 1,200 $ 1,200 $ 200 $ 1,400 F x fx SUBTOTAL G F 1 1 C D NO No Yes No No Yes Yes 108 Yes No Yes Yes Yes NO Yes No Yes A 8 OUT 402 Oak Tree Living 502 Oak Tree Living 306 Oak Tree Living 301 Oak Tree Living 507 Oak Tree Living 102 Rolling Meadows Rolling Meadows 606 Rolling Meadows 305 Rolling Meadows 203 Rolling Meadows 604 Rolling Meadows 408 Rolling Meadows 103 Rolling Meadows 104 Rolling Meadows 105 Rolling Meadows 501 Rolling Meadows 603 Rolling Meadows 206 Rolling Meadows 303 Rolling Meadows 304 Rolling Meadows 302 Rolling Meadows 504 Rolling Meadows 505 Rolling Meadows 201 Rolling Meadows 202 Rolling Meadows 101 Sunset Valley 602 Sunset Valley 601 Sunset Valley 506 Sunset Valley 507 Sunset Valley 205 Sunset Valley 204 Sunset Valley 2 1 2 1 3 1 2 3 2 2 2 2 2 2 3 1 2 3 1 1 1 1 2 1 1 1 1 1 2 2 3 Z E ZOO 2018 08 01 2018 08 01 2018 06 15 2018 01 15 2018 03 30 2018 03 01 2017 08 15 2017 03 30 2016 10 04 2016 04 01 2015 12 01 2015 11 15 2015 11 15 2015 10 01 2015 10 01 2015 10 01 2015 08 15 2015 07 01 2015 05 05 2015 05 05 2015 05 01 2014 07 02 2014 07 02 2014 04 13 2014 04 13 2014 04 01 2013 11 01 2013 08 15 2011 10 10 2011 10 10 2011 08 15 2011 04 01 D56 2 755 2 75$ 2 88 $ 3 045 3 09 $ 3 17 3 71 S 4 09 $ 4 58 $ 5 085 5 42 $ 5 46 S 5 46 $ 5 58S 5 58S 5 58 S 5 71 $ 5 83 $ 5 99 $ 5 99 $ 6 00 $ 6 83 $ 6 83 7 05 7 05 S 7 08 $ 7 505 7 71 $ 9 56 S 9 565 9 71 5 10 085 950 $ 875 $ 1,200 $ 875$ 2,000 $ 950 S 1,100 $ 2,000 $ 1,200 $ 995 $ 1,200 $ 975 S 1,100 $ 1 100 S 1,500 $ 875$ 1 2005 1,050 S 900 900 875 875$ 1,100 S 875$ 8755 950 $ 1,500 $ 1,500 $ 1,100 $ 1,100 $ 1,050 $ 995 $ UU 1,045$ 963 $ 1,320 $ 963 $ 2,200 $ 1,045 5 1,210 $ 2,200 $ 1,320 S 995 $ 1,200 $ 975 S 1,100 $ 1,100 $ 1,500 875 $ 1,200 $ 1 050 S 900 $ 900 S 875 $ 875$ 1 100 S 875 $ 875 S 950 $ 1,500 $ 1,500 $ 1,100 $ 1,1005 1,050 S 995 $ 200 $ 1,245 150 $ 1,113 200 5 1,520 150 $ 1,113 250 $ 2,450 150 $ 1,195 200 $ 1,410 250 $ 2 450 200 $ 1,520 200 $ 1,195 2005 1 400 200 $ 1,175 200 $ 1,300 200 $ 1,300 250 $ 1 750 150 $ 1,025 200 $ 1,400 250 5 1,300 150 $ 1,050 150 5 1,050 150 S 1,025 150 $ 1,025 200 5 1,300 150 $ 1,025 150 S 1,025 150 5 1,100 150 $ 1,650 150 5 1,650 200 $ 1,300 200 $ 1,300 250 $ 1,300 200 $ 1,195 NO No No Wes No Yes No Yes Yes Yes Yes Yes Yes No Yes No Excel File Edit View Insert Format Tools Data Window Help Anchal Exp19 Excel ch05 c Home Insert Draw Page Layout Formulas Data Review View Cut Calibri Body) 11 A A General WIDTaxt Copy Paste B 1 Merge Ce Formal IF Xfx SUBTOTAL(Rentals B D 1 1 2 2 2 2 1 1 2 2 2 3 301 302 303 304 305 306 307 308 401 404 402 405 407 408 403 405 601 602 603 604 605 606 507 101 102 201 202 103 104 107 108 203 204 207 200 105 Apartment Complex Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Mountaintop View Mountaintop View Mountaintop View Mountaintop View Mountaintop View Mountaintop View Mountaintop View Mountaintop View Oak Tree living Oak Tree Living Oak Tree in Oak Tree Living Oak Tree Living Oak Tree Living Oak Tree Living Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Mollie Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows F G Years Since Security No Refund Remodel tnt P Deposit Cleaning Deposit Total Depot 3 04 875 5 963 5 150 5 1 113 6 00 5 875 $ 8755 1505 1 025 5 99 $ 900 5900 S 150 $ 1 050 5 99 $ 900 $ 900$ 1505 1 050 4 585 1,200 $ 1 320 $ 2005 1,520 2 885 1,200 $ 1,320 $ 2005 1 520 0 845 1 200 $ 1 320 S 2005 1,520 0 59 $ 1,200 5 1,320$ 2005 1,520 1835 875 S 9635 1505 9,113 0 585 875 $ 953 S 150 $ 1,113 2 755 950 5 1,0455 200 1 245 0 885 950 $ 1 045$ 2005 1 245 2 17 $ 975 5 1,073 $ 2005 1,273 5 46 5 975 S 9755 2005 1,175 0 01$ 1,200 5 1,3205 250 $ 1,570 0 08 1,200 5 1,320$ 250 $ 1,570 7715 1 500 $ 1,500 $ 150 $ 1,650 7 50 $ 1 500 5 1,500 5 150 $ 1,650 5 715 1,200 $ 1 2005 2005 1,400 5 42 $ 1,200 $ 1 2005 2005 1 400 0 42 $ 2,000 $2,200 250 5 2 450 4 095 2 000 $2,200 $ 250 $ 2,450 3 09 $ 2,000 5 2,200 $ 250 $ 2,450 7 085 9505 950 $ 150 S 1 100 3 175 950 $ 1 045 $ 150 $ 1,195 7 055 875 $ 875 $ 1505 1,025 705$ 875 $ 875$ 150 S 1 025 5 46 5 1,100 $ 1,100 $ 2005 1,300 5 585 1 100 1 100 2005 1,300 0 50 $ 1 100 $ 1 210 $ 2005 1 410 3 71 5 1,100 5 1,210 S 200 $ 1,410 5 085 995 $ 9955 2009 1,195 10 08 995 $ 9955 200 $ 1 195 0 755 995 $ 1 0955 2005 1,295 0 755 995 $ 1 0955 2005 12295 5 585 1 500 $ 1 500 $ 250 $ 1,750 Occupied Lant Remodel Na 2018 04 15 No 2015 05 01 No 2015 05 05 Yes 2015 OS OS No 2016 10 04 Yes 2015 06 15 Yes 2020 06 30 No 2020 09 30 No 2019 07 01 No 2020 10 04 No 2018 08 01 Yes 2020 06 15 Yes 2019 03 01 Yes 2015 11 15 Yes 2021 05 05 No 2021 04 01 Yes 2013 05 15 Yes 2013 11 01 No 2015 08 15 Yel 2015 12 01 No 2020 11 30 Yes 2017 03 30 No 2018 03 30 Yes 2014 04 01 Yes 2018 03 01 Yes 2014 04 13 Yes 2014 04 13 NO 2015 11 15 Yes 2015 10 01 No 2020 11 01 Yes 2017 08 15 Yes 2016 04 01 No 2011 04 01 Yes 2020 08 01 Yes 2020 08 01 No 2015 10 01 Databases 1 2 2 3 3 3 1 1 1 1 2 2 2 2 2 2 2 2 3 Summary Rentals Point O 1 4 Excel File Edit View Insert Format Tools Data Help Window Anchal Exp19 Excel ch05 Ca Formulas Data Review View General A wp Text Merge Center IF 1 20 29 10 31 32 33 1025 1,300 1,100 1 410 1 450 Home Insert Draw Page Layout Out Caitl (Bodyt 1 Copy B VU Po xfxSUBTOTAL Rentals D 202 Robin Meadows 1 Yies 108 Rolling Meadows 2 NO 104 Rolling Meadows 2 Yes 107 Rolling Meadows 2 NO 108 Rolling Meadows 2 Yes 203 Rolling Meadows 2 Yes 204 Rolling Meadows 2 No 207 Ring Meadows 2 Ves 208 Rolling Meadows 2 Yes 105 Rolling Meadows 3 No 106 Rolling Meadows 3 Yes 109 Rolling Meadows 3 No 110 Rolling Meadows Yes 205 Rolling Meadows 3 Yes 206 Rolling Meadows No 209 Rolling Meadows Ne 210 3 Rolling Meadows Yes 501 Sunset Valley 1 Yes SO2 Sunset Valley 1 No SOB Sunset Valley 1 No 504 Sunt Valley Ves 505 Sunset Valley 2 No 506 Sunset Valley Yes 507 Sunset Valley 2 No 16 17 3 9 0 1 2014 04 11 2015 11 15 2015 10 01 2020 11 03 2017 18 2016 04 03 2011 04 01 2020 08 01 2020 08 01 2015 10 01 2000 06 15 2019 08 15 2018 10 04 2011 08 15 2015 07 01 202008 01 2019 11 04 2015 10 01 2018 08 01 2019 08 01 2014 07 02 2014 07 02 2011 10 10 2011 10 10 2 05 5 46 5 SSSS 0 505 3 715 5 085 10 08 0755 0 75 550 $ 0 88 1 713 2 58 9 71 5 83 $ 0 75$ 1493 5 585 2 755 1 75$ 6 835 6835 9 565 9 55$ G H 375 38755 1,100 $ 1,100 1 100 1 1005 1,100 $ 1 210 1 100 S12105 995 5 9955 995 $ 9955 995 $ 1 095 995 $ 1 0955 1 500 $1,500 $ 1,500 $ 1 650 1 500 5 1,650 $ 1,500 $ 1 650 1,050 $ 1 050 $ 1,050 $ 1 050 $ 1,050 $ 1 1555 1,050 $ 1 355 875 5875 875 5 963 8755 963 8755 875$ 1,100 $ 1 100 5 1,100 5 1,100 5 1,100 $ 1 100 5 1505 2005 2003 2005 2003 2005 2005 200 $ 2005 250 250 5 250 S 250 $ 2505 250 $ 250 $ 250 S 1505 1505 1505 150 $ 2009 200 $ 200 $ 1 195 1 195 1 295 1,295 1750 1 900 1,900 1 900 1,300 1 100 1,405 1 405 1,025 1 113 NE 1 025 1,300 1 300 1,300 Summan Pantal natan IF A 1 x fx SUBTOTAL(Databases B E Constants for Formulas Comparison Date 2021 05 01 Years Since Remodel 5 0 Deposit Older Rent 1 25) 1 0 Deposit Newer Rent 1 5) 1 1 2 4 $ 302 7 Unite Code Bed 301 LA 9 LA 10 303 LA 1 11 304 LA 12 305 LA 2 13 306 LA 2 14 307 LA 2 15 308 LA 2 16 401 MV 1 17 404 MV 1 18 402 MV 2 19 405 MV 2 20 407 MV 2 21 408 MV 2 22 403 MV 3 23 406 MV 3 24 601 OTL 1 25 602 OTL 1 26 603 OTE 2 27 604 OTL 2 28 605 on 3 29 606 OTL 3 30 507 OTL 3 31 101 RM 2 32 102 RM 33 201 RM 1 14 202 RM 35 103 RM 2 36 104 RM 37 107 RM 2 38 Occupied Last Remodel Years Since Remodel Rental Price Security Depost Non Refund Cleanins Deposit No 2018 04 15 3 04 $ 8755 9635 No 2015 05 01 6 00 $ 875 $ 875$ No 2015 05 05 5,99 $ 900 $ 900 5 Yes 2015 OS OS 5 99 $ 900 $ 500 $ No 2016 10 04 4 58 $ 1,2005 1 3205 Yes 2018 06 15 2 88 $ 1,200 $ 1 320$ Yes 2020 06 30 0 84 5 1,200 $ 1 320$ No 2020 09 30 0 59 $ 1,200 $ 1,320$ No 2019 07057 1 83 875 S 963 5 No 2020 10 04 0 58$ 875 $ 963 $ No 2018 08 01 2 75$ 950 S 1,045 5 Yes 2020 06 15 0 88 $ 950 $ 1,045$ Yes 2019 03 01 2 17 5 975$ 1,073 $ Yes 2015 11 15 5 46 $ 975 $ 975 $ Yes 2021 05 os 0 01$ 1,200 $ 1,320 S No 2021 04 01 0 08 $ 1,200 $ 1,320$ Yes 2013 08 15 7 71 $ 1,500 $ 1,500 $ Yes 2013 11 01 7 50 $ 1,500 1 500 $ No 2015 08 15 5 71 $ 1,2005 1,200 $ Yes 2015 12 01 5 42 $ 1,200 $ 1,200 $ No 2020 11 30 0 42 $ 2,000 $ 2 2005 F27 Yes 2017 03 24 4 09 $ 2,000 $ 2,200 $ No 2018 03 30 3 09 $ 2,000 $ 2,200 $ Yes 2014 04 01 7 08 $ 950 $ 950 $ Yes 2018 03 01 3 17 $ 950 $ 1,045 $ Yes 2014 04 13 7 05 $ 875 S 8755 Yes 2014 04 13 7 05 S 875$ 875 $ No 2015 11 15 5,46 $ 1,1005 1,100 $ Yes 2015 10 01 S 58 S 1,100 $ 1,100 5 No 2020 11 01 0 50 $ 1,100 $ 1,210 5 17025 Rentals Databases Total Deposit 150 $ 1,113 1505 1,025 1505 1,050 150 $ 1,050 2005 1,520 2005 1 520 2005 2005 1,520 1505 1 111 1505 2005 1,245 2005 1,245 200 $ 1,273 2005 1,175 2505 1 570 2505 1,570 150 5 1 650 150 5 1 650 2005 1,400 200 $ 1 400 250$ 2 450 2505 2 450 250 S 2,450 150 $ 1,100 150 5 1,195 150 5 1,025 150 S 1,025 2005 1,300 2005 1 800 2005 1,410 Summary Point Conditional for Formatting as Home Insert Draw Page Layout Formulas Data Review View X Cut Calibri (Body) 11 A Wrap Text General Copy Paste B 1 U A Format Merge Center S IF x fxSUBTOTAL(Databases Unit Code Bled Occupied Last Remodel Years Since Remodel Rental Price Security Deposit Non Refund Cleaning Deposit 28 605 OT No 2020 11 30 0 42 2,000 $ 2 2005 606 OTL 3 Yes 2017 03 30 4 09 $ 2,000 $ 2,200 $ 30 507 OTL 3 No 2018 03 30 3 09 $ 2,000 $ 2,200 $ 31 101 RM 1 Yes 2014 04 01 7 08 $ 950 $ 950 $ 32 102 RM 1 Yes 2018 03 01 3 17 $ 950 $ 1,045$ 33 201 RM 1 Yes 2014 04 13 7 05 875$ 875 $ 34 202 RM 1 Yes 2014 04 13 7 05 $ 8755 875 S 35 103 RM 2 No 2015 11 15 5 46 5 1 100 S 1,100 $ 36 104 RM 2 Yes 2015 10 01 5 58 S 1 100 $ 1 100 S 32 107 RM 2 No 2020 11 01 0 50 $ 1,100 $ 1,210 5 38 108 RM 2 Yes 2017 08 15 3 71 $ 1,100 $ 1 210 $ 203 RM 2 Yes 2016 04 01 5 08 $ 995 $ 9955 10 204 RM 2 No 2011 04 01 10 08 995 $ 995 $ 207 EM 2 Yes 2020 08 01 0 75 $ 9955 1,095 $ 42 RM 2 Yes 2020 08 01 0 755 995$ 1,095 105 RM 3 No 2015 10 03 5 58 $ 1,500 $ 1,500 $ 106 RM 3 Yes 2020 06 15 0 88 $ 1 500 5 1,650 $ 45 109 RM 3 No 2019 08 15 1 71 5 1,500 $ 1 6505 110 RM 3 Yes 2018 10 04 2 58 $ 1,500 $ 1,650 $ 205 RM 3 Yes 2011 08 15 9 71 $ 1,050 $ 1,050 $ 18 206 RM 3 No 2015 07 01 5 83 1,050 $ 1,0505 19 209 RM 3 No 2020 08 01 0 75 $ 1,050 $ 1,155 $ 50 210 RM 3 Yes 2019 11 04 1 49 $ 1,050 $ 1,155$ 501 SV 1 Yes 2015 10 01 5 58 $ 875 $ 875$ 502 5V 1 No 2018 08 01 2 75$ 8755 963 $ 53 SO3 SV 1 No 2019 08 01 1 75$ 875$ 963 $ 504 SV 1 Yes 2014 07 02 6 83 $ 875 $ 8755 55 505 5V 2 No 2014 07 02 6 83 $ 1,100 $ 1,100 $ 56 506 SV Yes 2011 10 10 9 56 $ 1 100 $ 507 SV 2 No 2011 10 16 9 56 $ 1,100 $ 1,100 $ F56 Total Deposit 2505 2 450 250 $ 2 450 250 5 2 450 150 $ 1,100 1505 1,195 150 $ 1,025 150 $ 1,025 200 $ 1,300 2005 1 300 2005 1,410 2005 1 410 2005 1,195 2005 1,195 2005 1,295 2005 1,295 2505 1,750 250 S 1,900 250 $ 1 900 2505 1,900 250 5 1,300 250 5 1 100 250 5 1,405 250 S 1,405 150 5 1,025 150 S 150 S 1 115 150 $ 200 $ 1 300 2005 1,300 200 $ 1 300 1 025 1 100 S 59 30 62 63 Wrap Text General Fill Insert Delete Format $ ) Merge Center Conditional Format Formatting as Table 00 Cell Styles Sort Filte Clear K M N H 10 Code LA MV OTL RM SV Apartment Name Lakeview Apartments Mountaintop View Oak Tree Living Rolling Meadows Sunset Valley Security Deposit Non Refund Cleaning Deposit 8755 9636 8755 875 $ 900 $ 900S 900 $ 900 $ 1,200 $ 1,320 S 1,200 $ 1,320 $ 1,200 $ 1,320 $ 1,200 $ 1,320 S 875 S 963 $ 875$ 963 S 950 $ 1,045 $ 950 $ 1,045 $ 975$ 1,0735 9755 975 S 1,200 $ 1,320 $ 1,200 $ 1,3205 1 500 5 1,500S 1,500 $ 1,500 $ 1,200 $ 1,200 $ 1 200 $ 1,200 $ 2,000 5 2 2005 2,000 5 2 2005 2 000 5 2,200 $ 950 5 950 $ 950 5 1,045 5 875$ 875$ 75 8255 Total Deposit 150 $ 1,113 150 $ 1,025 150 $ 1,050 150 $ 1,050 200 $ 1,520 200 $ 1,520 2005 1,520 200 $ 1,520 150 $ 1,113 150 S 1,113 200 1,245 117 200 1,245 200 $ 1,273 200 $ 1,175 250 $ 1,570 250 $ 1,570 150 $ 1,650 150 $ 1,650 200 $ 1,400 200 $ 1,400 250 $ 2,450 250 $ 2,450 250 $ 2,450 150 $ 150 S 1 195 150 5 1,025 150 $ 1,025 1,100

Question

can you please show the steps as well  Step Instructions Possible 1 0 2 3 3 5 2 5 7 Start Excel  Download and open the file named Exp19 Excel Cho5 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 occupied apartments  Display the Rentals sheet and create a blank PivotTable on a new worksheet to the left of the Rentals sheet  Change the name of the worksheet to Rental Revenue  Name the Pivot Table Rental Revenue Display the Apartment Complex and   Bed fields in Rows and the Rental Price field as Values Format the Sum of Rental Price for Accounting Number Format with zero decimal places and enter the custom name Total Rent Collected, 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 03 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 6 6 3 7 3 8 9 15 10 5 11 Apply Light Orange, Pivot Style Medium 10 to the PivotTable and display banded rows  Gradur   Instructions En 2015 Pro Step Instructions Points Possible 12 5 13 6 14 5 15 5 16 5 Insert a slicer for   Bed so that you can filter the dataset by number of bedrooms  Change the slicer caption to   of Bedrooms Change the slicer height to 1 4 inches and wielth to 1 75 inches  Apply Light Orange, Slicer Style Light 2  Cut the slicer and paste it in cell E2  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 Pivot Table BederomData  Select the Apartment Name field from the COMPLEX table for Rows, the  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 using the Destination Theme Select the 3 bedroom data series and apply the Black, Text 1, Lighter 50  solid fill color, Apply Black, Text 1 font color to the vertical axis and category axis  Change the chart height to 3 inches and the width to 5 inches, if necessary  Hide the field buttons in the PivotChart  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 Excol Cho5 Cap Apartments xlsx  Exit Excel  Submit the file as directed 17 5 18 5 19 5 20 5 0 21 Total Points 100 IF fx  SUBTOTALE A D E F G 2 3 Constants for Formulas Comparison Date Years Since Remodel Deposit Older (Rent 1 25) Deposit Newer (Rent'1,5) 2021 05 01 5 0 1 0 1 1   INN Unit   Apartment Complex 403 Lakeview Apartments 406 Lakeview Apartments 605 Lakeview Apartments 107 Lakeview Apartments 404 Lakeview Apartments 308 Lakeview Apartments 207 Lakeview Apartments 208 Lakeview Apartments 209 Mountaintop View 307 Mountaintop View 405 Mountaintop View 106 Mountaintop View 210 Mountaintop View 109 Mountaintop View 503 Mountaintop View 401 Mountaintop View 407 Oak Tree Living 110 Oak Tree Living 402 Oak Tree Living 502 Oak Tree Living 306 Oak Tree Living 301 B27 Oak Tree Living 507 Oak Tree Living 102 Rolling Meadows 08 Rolling Meadows 06 Rolling Meadows 05 Rolling Meadows 03 Rolling Meadows 04 Rolling Meadows Summary Rentals  Bed Occupied Last Remodel 3 Yes 2021 05 05 3 No 2021 04 01 3 No 2020 11 30 2 No 2020 11 01 1 No 2020 10 04 2 No 2020 09 30 Yes 2020 08 01 Yes 2020 08 01 3 No 2020 08 01 2 Yes 2020 06 30 2 Yes 2020 06 15 3 2020 06 15 3 Yes 2019 11 04 3 No 2019 08 15 1 No 2019 08 01 1 No 2019 07 01 2 Yes 2019 03 01 3 Yes 2018 10 04 2 No 2018 08 01 1 No 2018 08 01 2 Yes 2018 06 15 1 No 2018 04 15 3 No 2018 03 30 1 Yes 2018 03 01 2 Yes 2017 08 15 3 Yes 2017 03 30 2 No 2016 10 04 2 Yes 2016 04 01 2 Yes 2015 12 01 Databases   Non Refund Years Since Security Cleaning Total Remodel Rental Price Deposit Deposit Deposit 0 01$ 1,200 $ 1,320 $ 250 $ 1,570 0 08 $ 1,200 $ 1,320 $ 250 $ 1 570 0 42 $ 2,000 $ 2,200 $ 250 $ 2,450 0 50 IS 1 100 S 1,210 $ 200 $ 1,410 0 58S 875$ 963 $ 150 $ 1,113 0 59 $ 1,200 $ 1,320 $ 200 $ 1 520 0 75 995 S 1,095 $ 200 $ 1,295 0 75$ 995 $ 1,095$ 200 $ 1 295 0 75 1,050 $ 1 155 $ 250 $ 1,405 0 84 1,200 $ 1,320 $ 200 $ 1,520 0 88 $ 950 $ 1,045 $ 200 $ 1,245 0 88 $ 1,500 $ 1,650 $ 250 $ 1,900 1 49 $ 1,050 $ 1,155$ 250 $ 1,405 1 71 $ 1,500 5 1,650 $ 250 $ 1,900 1 75$ 875 S 963 $ 150 $ 1,113 1 83 $ 875 $ 963 $ 150 $ 1,113 2 17 $ 975 $ 1,073 $ 200 $ 1,273 2 58 $ 1,500 $ 1,650 $ 250 $ 1,900 2 75 $ 950 $ 1,045 $ 200 $ 1,245 2 75 S 875 $ 963 S 150 $ 1,113 2 88 $ 1,200 $ 1,320 $ 200 $ 1,520 3 04 $ 875 $ 963 $ 150 $ 1,113 3 09 $ 2,000 $ 2,200 $ 250 $ 2,450 3 17 $ 950 $ 1,0455 150 $ 1,195 3 71S 1,100 $ 1,210 $ 200 $ 1,410 4 09 $ 2,000 $ 2,200 $ 250 $ 2,450 4 58 5 1,200 $ 1,320 $ 200 $ 1,520 5 08 $ 995 $ 995 $ 200 $ 1,195 5 42 $ 1,200 $ 1,200 $ 200 $ 1,400 F x fx  SUBTOTAL G F 1 1 C D NO No Yes No No Yes Yes 108 Yes No Yes Yes Yes NO Yes No Yes A 8 OUT 402 Oak Tree Living 502 Oak Tree Living 306 Oak Tree Living 301 Oak Tree Living 507 Oak Tree Living 102 Rolling Meadows Rolling Meadows 606 Rolling Meadows 305 Rolling Meadows 203 Rolling Meadows 604 Rolling Meadows 408 Rolling Meadows 103 Rolling Meadows 104 Rolling Meadows 105 Rolling Meadows 501 Rolling Meadows 603 Rolling Meadows 206 Rolling Meadows 303 Rolling Meadows 304 Rolling Meadows 302 Rolling Meadows 504 Rolling Meadows 505 Rolling Meadows 201 Rolling Meadows 202 Rolling Meadows 101 Sunset Valley 602 Sunset Valley 601 Sunset Valley 506 Sunset Valley 507 Sunset Valley 205 Sunset Valley 204 Sunset Valley 2 1 2 1 3 1 2 3 2 2 2 2 2 2 3 1 2 3 1 1 1 1 2 1 1 1 1 1 2 2 3 Z E ZOO 2018 08 01 2018 08 01 2018 06 15 2018 01 15 2018 03 30 2018 03 01 2017 08 15 2017 03 30 2016 10 04 2016 04 01 2015 12 01 2015 11 15 2015 11 15 2015 10 01 2015 10 01 2015 10 01 2015 08 15 2015 07 01 2015 05 05 2015 05 05 2015 05 01 2014 07 02 2014 07 02 2014 04 13 2014 04 13 2014 04 01 2013 11 01 2013 08 15 2011 10 10 2011 10 10 2011 08 15 2011 04 01 D56 2 755 2 75$ 2 88 $ 3 045 3 09 $ 3 17   3 71 S 4 09 $ 4 58 $ 5 085 5 42 $ 5 46 S 5 46 $ 5 58S 5 58S 5 58 S 5 71 $ 5 83 $ 5 99 $ 5 99 $ 6 00 $ 6 83 $ 6 83   7 05 7 05 S 7 08 $ 7 505 7 71 $ 9 56 S 9 565 9 71 5 10 085 950 $ 875 $ 1,200 $ 875$ 2,000 $ 950 S 1,100 $ 2,000 $ 1,200 $ 995 $ 1,200 $ 975 S 1,100 $ 1 100 S 1,500 $ 875$ 1 2005 1,050 S 900 900 875 875$ 1,100 S 875$ 8755 950 $ 1,500 $ 1,500 $ 1,100 $ 1,100 $ 1,050 $ 995 $ UU 1,045$ 963 $ 1,320 $ 963 $ 2,200 $ 1,045 5 1,210 $ 2,200 $ 1,320 S 995 $ 1,200 $ 975 S 1,100 $ 1,100 $ 1,500 875 $ 1,200 $ 1 050 S 900 $ 900 S 875 $ 875$ 1 100 S 875 $ 875 S 950 $ 1,500 $ 1,500 $ 1,100 $ 1,1005 1,050 S 995 $ 200 $ 1,245 150 $ 1,113 200 5 1,520 150 $ 1,113 250 $ 2,450 150 $ 1,195 200 $ 1,410 250 $ 2 450 200 $ 1,520 200 $ 1,195 2005 1 400 200 $ 1,175 200 $ 1,300 200 $ 1,300 250 $ 1 750 150 $ 1,025 200 $ 1,400 250 5 1,300 150 $ 1,050 150 5 1,050 150 S 1,025 150 $ 1,025 200 5 1,300 150 $ 1,025 150 S 1,025 150 5 1,100 150 $ 1,650 150 5 1,650 200 $ 1,300 200 $ 1,300 250 $ 1,300 200 $ 1,195 NO No No Wes No Yes No Yes Yes Yes Yes Yes Yes No Yes No Excel File Edit View Insert Format Tools Data Window Help Anchal Exp19 Excel ch05 c Home Insert Draw Page Layout Formulas Data Review View Cut Calibri Body)   11 A  A General WIDTaxt Copy Paste B 1 Merge Ce Formal IF Xfx  SUBTOTAL(Rentals B D 1 1 2 2 2 2 1 1 2 2 2 3 301 302 303 304 305 306 307 308 401 404 402 405 407 408 403 405 601 602 603 604 605 606 507 101 102 201 202 103 104 107 108 203 204 207 200 105 Apartment Complex Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Mountaintop View Mountaintop View Mountaintop View Mountaintop View Mountaintop View Mountaintop View Mountaintop View Mountaintop View Oak Tree living Oak Tree Living Oak Tree in Oak Tree Living Oak Tree Living Oak Tree Living Oak Tree Living Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Mollie Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows F G Years Since Security No Refund Remodel tnt P  Deposit Cleaning Deposit Total Depot 3 04 875 5 963 5 150 5 1 113 6 00 5 875 $ 8755 1505 1 025 5 99 $ 900 5900 S 150 $ 1 050 5 99 $ 900 $ 900$ 1505 1 050 4 585 1,200 $ 1 320 $ 2005 1,520 2 885 1,200 $ 1,320 $ 2005 1 520 0 845 1 200 $ 1 320 S 2005 1,520 0 59 $ 1,200 5 1,320$ 2005 1,520 1835 875 S 9635 1505 9,113 0 585 875 $ 953 S 150 $ 1,113 2 755 950 5 1,0455 200 1 245 0 885 950 $ 1 045$ 2005 1 245 2 17 $ 975 5 1,073 $ 2005 1,273 5 46 5 975 S 9755 2005 1,175 0 01$ 1,200 5 1,3205 250 $ 1,570 0 08 1,200 5 1,320$ 250 $ 1,570 7715 1 500 $ 1,500 $ 150 $ 1,650 7 50 $ 1 500 5 1,500 5 150 $ 1,650 5 715 1,200 $ 1 2005 2005 1,400 5 42 $ 1,200 $ 1 2005 2005 1 400 0 42 $ 2,000 $2,200 250 5 2 450 4 095 2 000 $2,200 $ 250 $ 2,450 3 09 $ 2,000 5 2,200 $ 250 $ 2,450 7 085 9505 950 $ 150 S 1 100 3 175 950 $ 1 045 $ 150 $ 1,195 7 055 875 $ 875 $ 1505 1,025 705$ 875 $ 875$ 150 S 1 025 5 46 5 1,100 $ 1,100 $ 2005 1,300 5 585 1 100 1 100 2005 1,300 0 50 $ 1 100 $ 1 210 $ 2005 1 410 3 71 5 1,100 5 1,210 S 200 $ 1,410 5 085 995 $ 9955 2009 1,195 10 08 995 $ 9955 200 $ 1 195 0 755 995 $ 1 0955 2005 1,295 0 755 995 $ 1 0955 2005 12295 5 585 1 500 $ 1 500 $ 250 $ 1,750 Occupied Lant Remodel Na 2018 04 15 No 2015 05 01 No 2015 05 05 Yes 2015 OS OS No 2016 10 04 Yes 2015 06 15 Yes 2020 06 30 No 2020 09 30 No 2019 07 01 No 2020 10 04 No 2018 08 01 Yes 2020 06 15 Yes 2019 03 01 Yes 2015 11 15 Yes 2021 05 05 No 2021 04 01 Yes 2013 05 15 Yes 2013 11 01 No 2015 08 15 Yel 2015 12 01 No 2020 11 30 Yes 2017 03 30 No 2018 03 30 Yes 2014 04 01 Yes 2018 03 01 Yes 2014 04 13 Yes 2014 04 13 NO 2015 11 15 Yes 2015 10 01 No 2020 11 01 Yes 2017 08 15 Yes 2016 04 01 No 2011 04 01 Yes 2020 08 01 Yes 2020 08 01 No 2015 10 01 Databases   1 2 2 3 3 3 1 1 1 1 2 2 2 2 2 2 2 2 3 Summary Rentals Point O 1 4 Excel File Edit View Insert Format Tools Data Help Window Anchal Exp19 Excel ch05 Ca Formulas Data Review View General A wp Text Merge Center IF 1 20 29 10 31 32 33 1025 1,300 1,100 1 410 1 450 Home Insert Draw Page Layout Out Caitl (Bodyt 1 Copy B VU Po xfxSUBTOTAL Rentals D 202 Robin Meadows 1 Yies 108 Rolling Meadows 2 NO 104 Rolling Meadows 2 Yes 107 Rolling Meadows 2 NO 108 Rolling Meadows 2 Yes 203 Rolling Meadows 2 Yes 204 Rolling Meadows 2 No 207 Ring Meadows 2 Ves 208 Rolling Meadows 2 Yes 105 Rolling Meadows 3 No 106 Rolling Meadows 3 Yes 109 Rolling Meadows 3 No 110 Rolling Meadows Yes 205 Rolling Meadows 3 Yes 206 Rolling Meadows No 209 Rolling Meadows Ne 210 3 Rolling Meadows Yes 501 Sunset Valley 1 Yes SO2 Sunset Valley 1 No SOB Sunset Valley 1 No 504 Sunt Valley Ves 505 Sunset Valley 2 No 506 Sunset Valley Yes 507 Sunset Valley 2 No 16 17 3 9 0 1 2014 04 11 2015 11 15 2015 10 01 2020 11 03 2017 18 2016 04 03 2011 04 01 2020 08 01 2020 08 01 2015 10 01 2000 06 15 2019 08 15 2018 10 04 2011 08 15 2015 07 01 202008 01 2019 11 04 2015 10 01 2018 08 01 2019 08 01 2014 07 02 2014 07 02 2011 10 10 2011 10 10 2 05 5 46 5 SSSS 0 505 3 715 5 085 10 08 0755 0 75 550 $ 0 88 1 713 2 58 9 71 5 83 $ 0 75$ 1493 5 585 2 755 1 75$ 6 835 6835 9 565 9 55$ G H 375 38755 1,100 $ 1,100 1 100 1 1005 1,100 $ 1 210 1 100 S12105 995 5 9955 995 $ 9955 995 $ 1 095 995 $ 1 0955 1 500 $1,500 $ 1,500 $ 1 650 1 500 5 1,650 $ 1,500 $ 1 650 1,050 $ 1 050 $ 1,050 $ 1 050 $ 1,050 $ 1 1555 1,050 $ 1 355 875 5875 875 5 963 8755 963 8755 875$ 1,100 $ 1 100 5 1,100 5 1,100 5 1,100 $ 1 100 5 1505 2005 2003 2005 2003 2005 2005 200 $ 2005 250 250 5 250 S 250 $ 2505 250 $ 250 $ 250 S 1505 1505 1505 150 $ 2009 200 $ 200 $ 1 195 1 195 1 295 1,295 1750 1 900 1,900 1 900 1,300 1 100 1,405 1 405 1,025 1 113   NE 1 025 1,300 1 300 1,300 Summan Pantal natan IF A 1 x fx  SUBTOTAL(Databases  B E Constants for Formulas Comparison Date 2021 05 01 Years Since Remodel 5 0 Deposit Older Rent 1 25) 1 0 Deposit Newer Rent 1 5) 1 1 2 4 $ 302 7 Unite Code Bed 301 LA 9 LA 10 303 LA 1 11 304 LA 12 305 LA 2 13 306 LA 2  14 307 LA 2 15 308 LA 2 16 401 MV 1 17 404 MV 1 18 402 MV 2 19 405 MV 2 20 407 MV 2 21 408 MV 2 22 403 MV 3 23 406 MV 3 24 601 OTL 1 25 602 OTL 1 26 603 OTE 2 27 604 OTL 2 28 605 on 3 29 606 OTL 3 30 507 OTL 3 31 101 RM 2 32 102 RM 33 201 RM 1 14 202 RM 35 103 RM 2 36 104 RM 37 107 RM 2 38 Occupied Last Remodel Years Since Remodel Rental Price Security Depost Non Refund Cleanins Deposit No 2018 04 15 3 04 $ 8755 9635 No 2015 05 01 6 00 $ 875 $ 875$ No 2015 05 05 5,99 $ 900 $ 900 5 Yes 2015 OS OS 5 99 $ 900 $ 500 $ No 2016 10 04 4 58 $ 1,2005 1 3205 Yes 2018 06 15 2 88 $ 1,200 $ 1 320$ Yes 2020 06 30 0 84 5 1,200 $ 1 320$ No 2020 09 30 0 59 $ 1,200 $ 1,320$ No 2019 07057 1 83 875 S 963 5 No 2020 10 04 0 58$ 875 $ 963 $ No 2018 08 01 2 75$ 950 S 1,045 5 Yes 2020 06 15 0 88 $ 950 $ 1,045$ Yes 2019 03 01 2 17 5 975$ 1,073 $ Yes 2015 11 15 5 46 $ 975 $ 975 $ Yes 2021 05 os 0 01$ 1,200 $ 1,320 S No 2021 04 01 0 08 $ 1,200 $ 1,320$ Yes 2013 08 15 7 71 $ 1,500 $ 1,500 $ Yes 2013 11 01 7 50 $ 1,500 1 500 $ No 2015 08 15 5 71 $ 1,2005 1,200 $ Yes 2015 12 01 5 42 $ 1,200 $ 1,200 $ No 2020 11 30 0 42 $ 2,000 $ 2 2005 F27 Yes 2017 03 24 4 09 $ 2,000 $ 2,200 $ No 2018 03 30 3 09 $ 2,000 $ 2,200 $ Yes 2014 04 01 7 08 $ 950 $ 950 $ Yes 2018 03 01 3 17 $ 950 $ 1,045 $ Yes 2014 04 13 7 05 $ 875 S 8755 Yes 2014 04 13 7 05 S 875$ 875 $ No 2015 11 15 5,46 $ 1,1005 1,100 $ Yes 2015 10 01 S 58 S 1,100 $ 1,100 5 No 2020 11 01 0 50 $ 1,100 $ 1,210 5 17025 Rentals Databases   Total Deposit 150 $ 1,113 1505 1,025 1505 1,050 150 $ 1,050 2005 1,520 2005 1 520 2005 2005 1,520 1505 1 111 1505 2005 1,245 2005 1,245 200 $ 1,273 2005 1,175 2505 1 570 2505 1,570 150 5 1 650 150 5 1 650 2005 1,400 200 $ 1 400 250$ 2 450 2505 2 450 250 S 2,450 150 $ 1,100 150 5 1,195 150 5 1,025 150 S 1,025 2005 1,300 2005 1 800 2005 1,410 Summary Point Conditional for Formatting as Home Insert Draw Page Layout Formulas Data Review View X Cut Calibri (Body) 11 A Wrap Text  General Copy Paste B 1 U A Format Merge   Center S IF x fxSUBTOTAL(Databases Unit Code Bled Occupied Last Remodel Years Since Remodel Rental Price Security Deposit Non Refund Cleaning Deposit 28 605 OT No 2020 11 30 0 42 2,000 $ 2 2005 606 OTL 3 Yes 2017 03 30 4 09 $ 2,000 $ 2,200 $ 30 507 OTL 3 No 2018 03 30 3 09 $ 2,000 $ 2,200 $ 31 101 RM 1 Yes 2014 04 01 7 08 $ 950 $ 950 $ 32 102 RM 1 Yes 2018 03 01 3 17 $ 950 $ 1,045$ 33 201 RM 1 Yes 2014 04 13 7 05 875$ 875 $ 34 202 RM 1 Yes 2014 04 13 7 05 $ 8755 875 S 35 103 RM 2 No 2015 11 15 5 46 5 1 100 S 1,100 $ 36 104 RM 2 Yes 2015 10 01 5 58 S 1 100 $ 1 100 S 32 107 RM 2 No 2020 11 01 0 50 $ 1,100 $ 1,210 5 38 108 RM 2 Yes 2017 08 15 3 71 $ 1,100 $ 1 210 $ 203 RM 2 Yes 2016 04 01 5 08 $ 995 $ 9955 10 204 RM 2  No 2011 04 01 10 08 995 $ 995 $ 207 EM 2 Yes 2020 08 01 0 75 $ 9955 1,095 $ 42 RM 2 Yes 2020 08 01 0 755 995$ 1,095 105 RM 3 No 2015 10 03  5 58 $ 1,500 $ 1,500 $ 106 RM 3 Yes 2020 06 15 0 88 $ 1 500 5 1,650 $ 45 109 RM 3 No 2019 08 15 1 71 5 1,500 $ 1 6505 110 RM 3 Yes 2018 10 04 2 58 $ 1,500 $ 1,650 $ 205 RM 3 Yes 2011 08 15 9 71 $ 1,050 $ 1,050 $ 18 206 RM 3 No 2015 07 01 5 83   1,050 $ 1,0505 19 209 RM 3 No 2020 08 01 0 75 $ 1,050 $ 1,155 $ 50 210 RM 3 Yes 2019 11 04 1 49 $ 1,050 $ 1,155$ 501 SV 1 Yes 2015 10 01 5 58 $ 875 $ 875$ 502 5V 1 No 2018 08 01 2 75$ 8755 963 $ 53 SO3 SV 1 No 2019 08 01 1 75$ 875$ 963 $ 504 SV 1 Yes 2014 07 02 6 83 $ 875 $ 8755 55 505 5V 2 No 2014 07 02 6 83 $ 1,100 $ 1,100 $ 56 506 SV Yes 2011 10 10 9 56 $ 1 100 $ 507 SV 2 No 2011 10 16 9 56 $ 1,100 $ 1,100 $ F56 Total Deposit 2505 2 450 250 $ 2 450 250 5 2 450 150 $ 1,100 1505 1,195 150 $ 1,025 150 $ 1,025 200 $ 1,300 2005 1 300 2005 1,410 2005 1 410 2005 1,195 2005 1,195 2005 1,295 2005 1,295 2505 1,750 250 S 1,900 250 $ 1 900 2505 1,900 250 5 1,300 250 5 1 100 250 5 1,405 250 S 1,405 150 5 1,025 150 S 150 S 1 115 150 $ 200 $ 1 300 2005 1,300 200 $ 1 300 1 025 1 100 S 59 30 62 63 Wrap Text General Fill  Insert Delete Format $  ) Merge   Center   Conditional Format Formatting as Table 00 Cell Styles Sort   Filte Clear K M N H 10 Code LA MV OTL RM SV Apartment Name Lakeview Apartments Mountaintop View Oak Tree Living Rolling Meadows Sunset Valley Security Deposit Non Refund Cleaning Deposit 8755 9636 8755 875 $ 900 $ 900S 900 $ 900 $ 1,200 $ 1,320 S 1,200 $ 1,320 $ 1,200 $ 1,320 $ 1,200 $ 1,320 S 875 S 963 $ 875$ 963 S 950 $ 1,045 $ 950 $ 1,045 $ 975$ 1,0735 9755 975 S 1,200 $ 1,320 $ 1,200 $ 1,3205 1 500 5 1,500S 1,500 $ 1,500 $ 1,200 $ 1,200 $ 1 200 $ 1,200 $ 2,000 5 2 2005 2,000 5 2 2005 2 000 5 2,200 $ 950 5 950 $ 950 5 1,045 5 875$ 875$ 75 8255 Total Deposit 150 $ 1,113 150 $ 1,025 150 $ 1,050 150 $ 1,050 200 $ 1,520 200 $ 1,520 2005 1,520 200 $ 1,520 150 $ 1,113 150 S 1,113 200 1,245 117 200 1,245 200 $ 1,273 200 $ 1,175 250 $ 1,570 250 $ 1,570 150 $ 1,650 150 $ 1,650 200 $ 1,400 200 $ 1,400 250 $ 2,450 250 $ 2,450 250 $ 2,450 150 $ 150 S 1 195 150 5 1,025 150 $ 1,025 1,100 Step Instructions Possible 1 0 2 3 3 5 2 5 7 Start Excel  Download and open the file named Exp19 Excel Cho5 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 occupied apartments  Display the Rentals sheet and create a blank PivotTable on a new worksheet to the left of the Rentals sheet  Change the name of the worksheet to Rental Revenue  Name the Pivot Table Rental Revenue Display the Apartment Complex and   Bed fields in Rows and the Rental Price field as Values Format the Sum of Rental Price for Accounting Number Format with zero decimal places and enter the custom name Total Rent Collected, 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 03 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 6 6 3 7 3 8 9 15 10 5 11 Apply Light Orange, Pivot Style Medium 10 to the PivotTable and display banded rows  Gradur   Instructions En 2015 Pro Step Instructions Points Possible 12 5 13 6 14 5 15 5 16 5 Insert a slicer for   Bed so that you can filter the dataset by number of bedrooms  Change the slicer caption to   of Bedrooms Change the slicer height to 1 4 inches and wielth to 1 75 inches  Apply Light Orange, Slicer Style Light 2  Cut the slicer and paste it in cell E2  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 Pivot Table BederomData  Select the Apartment Name field from the COMPLEX table for Rows, the  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 using the Destination Theme Select the 3 bedroom data series and apply the Black, Text 1, Lighter 50  solid fill color, Apply Black, Text 1 font color to the vertical axis and category axis  Change the chart height to 3 inches and the width to 5 inches, if necessary  Hide the field buttons in the PivotChart  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 Excol Cho5 Cap Apartments xlsx  Exit Excel  Submit the file as directed 17 5 18 5 19 5 20 5 0 21 Total Points 100 IF fx  SUBTOTALE A D E F G 2 3 Constants for Formulas Comparison Date Years Since Remodel Deposit Older (Rent 1 25) Deposit Newer (Rent'1,5) 2021 05 01 5 0 1 0 1 1   INN Unit   Apartment Complex 403 Lakeview Apartments 406 Lakeview Apartments 605 Lakeview Apartments 107 Lakeview Apartments 404 Lakeview Apartments 308 Lakeview Apartments 207 Lakeview Apartments 208 Lakeview Apartments 209 Mountaintop View 307 Mountaintop View 405 Mountaintop View 106 Mountaintop View 210 Mountaintop View 109 Mountaintop View 503 Mountaintop View 401 Mountaintop View 407 Oak Tree Living 110 Oak Tree Living 402 Oak Tree Living 502 Oak Tree Living 306 Oak Tree Living 301 B27 Oak Tree Living 507 Oak Tree Living 102 Rolling Meadows 08 Rolling Meadows 06 Rolling Meadows 05 Rolling Meadows 03 Rolling Meadows 04 Rolling Meadows Summary Rentals  Bed Occupied Last Remodel 3 Yes 2021 05 05 3 No 2021 04 01 3 No 2020 11 30 2 No 2020 11 01 1 No 2020 10 04 2 No 2020 09 30 Yes 2020 08 01 Yes 2020 08 01 3 No 2020 08 01 2 Yes 2020 06 30 2 Yes 2020 06 15 3 2020 06 15 3 Yes 2019 11 04 3 No 2019 08 15 1 No 2019 08 01 1 No 2019 07 01 2 Yes 2019 03 01 3 Yes 2018 10 04 2 No 2018 08 01 1 No 2018 08 01 2 Yes 2018 06 15 1 No 2018 04 15 3 No 2018 03 30 1 Yes 2018 03 01 2 Yes 2017 08 15 3 Yes 2017 03 30 2 No 2016 10 04 2 Yes 2016 04 01 2 Yes 2015 12 01 Databases   Non Refund Years Since Security Cleaning Total Remodel Rental Price Deposit Deposit Deposit 0 01$ 1,200 $ 1,320 $ 250 $ 1,570 0 08 $ 1,200 $ 1,320 $ 250 $ 1 570 0 42 $ 2,000 $ 2,200 $ 250 $ 2,450 0 50 IS 1 100 S 1,210 $ 200 $ 1,410 0 58S 875$ 963 $ 150 $ 1,113 0 59 $ 1,200 $ 1,320 $ 200 $ 1 520 0 75 995 S 1,095 $ 200 $ 1,295 0 75$ 995 $ 1,095$ 200 $ 1 295 0 75 1,050 $ 1 155 $ 250 $ 1,405 0 84 1,200 $ 1,320 $ 200 $ 1,520 0 88 $ 950 $ 1,045 $ 200 $ 1,245 0 88 $ 1,500 $ 1,650 $ 250 $ 1,900 1 49 $ 1,050 $ 1,155$ 250 $ 1,405 1 71 $ 1,500 5 1,650 $ 250 $ 1,900 1 75$ 875 S 963 $ 150 $ 1,113 1 83 $ 875 $ 963 $ 150 $ 1,113 2 17 $ 975 $ 1,073 $ 200 $ 1,273 2 58 $ 1,500 $ 1,650 $ 250 $ 1,900 2 75 $ 950 $ 1,045 $ 200 $ 1,245 2 75 S 875 $ 963 S 150 $ 1,113 2 88 $ 1,200 $ 1,320 $ 200 $ 1,520 3 04 $ 875 $ 963 $ 150 $ 1,113 3 09 $ 2,000 $ 2,200 $ 250 $ 2,450 3 17 $ 950 $ 1,0455 150 $ 1,195 3 71S 1,100 $ 1,210 $ 200 $ 1,410 4 09 $ 2,000 $ 2,200 $ 250 $ 2,450 4 58 5 1,200 $ 1,320 $ 200 $ 1,520 5 08 $ 995 $ 995 $ 200 $ 1,195 5 42 $ 1,200 $ 1,200 $ 200 $ 1,400 F x fx  SUBTOTAL G F 1 1 C D NO No Yes No No Yes Yes 108 Yes No Yes Yes Yes NO Yes No Yes A 8 OUT 402 Oak Tree Living 502 Oak Tree Living 306 Oak Tree Living 301 Oak Tree Living 507 Oak Tree Living 102 Rolling Meadows Rolling Meadows 606 Rolling Meadows 305 Rolling Meadows 203 Rolling Meadows 604 Rolling Meadows 408 Rolling Meadows 103 Rolling Meadows 104 Rolling Meadows 105 Rolling Meadows 501 Rolling Meadows 603 Rolling Meadows 206 Rolling Meadows 303 Rolling Meadows 304 Rolling Meadows 302 Rolling Meadows 504 Rolling Meadows 505 Rolling Meadows 201 Rolling Meadows 202 Rolling Meadows 101 Sunset Valley 602 Sunset Valley 601 Sunset Valley 506 Sunset Valley 507 Sunset Valley 205 Sunset Valley 204 Sunset Valley 2 1 2 1 3 1 2 3 2 2 2 2 2 2 3 1 2 3 1 1 1 1 2 1 1 1 1 1 2 2 3 Z E ZOO 2018 08 01 2018 08 01 2018 06 15 2018 01 15 2018 03 30 2018 03 01 2017 08 15 2017 03 30 2016 10 04 2016 04 01 2015 12 01 2015 11 15 2015 11 15 2015 10 01 2015 10 01 2015 10 01 2015 08 15 2015 07 01 2015 05 05 2015 05 05 2015 05 01 2014 07 02 2014 07 02 2014 04 13 2014 04 13 2014 04 01 2013 11 01 2013 08 15 2011 10 10 2011 10 10 2011 08 15 2011 04 01 D56 2 755 2 75$ 2 88 $ 3 045 3 09 $ 3 17   3 71 S 4 09 $ 4 58 $ 5 085 5 42 $ 5 46 S 5 46 $ 5 58S 5 58S 5 58 S 5 71 $ 5 83 $ 5 99 $ 5 99 $ 6 00 $ 6 83 $ 6 83   7 05 7 05 S 7 08 $ 7 505 7 71 $ 9 56 S 9 565 9 71 5 10 085 950 $ 875 $ 1,200 $ 875$ 2,000 $ 950 S 1,100 $ 2,000 $ 1,200 $ 995 $ 1,200 $ 975 S 1,100 $ 1 100 S 1,500 $ 875$ 1 2005 1,050 S 900 900 875 875$ 1,100 S 875$ 8755 950 $ 1,500 $ 1,500 $ 1,100 $ 1,100 $ 1,050 $ 995 $ UU 1,045$ 963 $ 1,320 $ 963 $ 2,200 $ 1,045 5 1,210 $ 2,200 $ 1,320 S 995 $ 1,200 $ 975 S 1,100 $ 1,100 $ 1,500 875 $ 1,200 $ 1 050 S 900 $ 900 S 875 $ 875$ 1 100 S 875 $ 875 S 950 $ 1,500 $ 1,500 $ 1,100 $ 1,1005 1,050 S 995 $ 200 $ 1,245 150 $ 1,113 200 5 1,520 150 $ 1,113 250 $ 2,450 150 $ 1,195 200 $ 1,410 250 $ 2 450 200 $ 1,520 200 $ 1,195 2005 1 400 200 $ 1,175 200 $ 1,300 200 $ 1,300 250 $ 1 750 150 $ 1,025 200 $ 1,400 250 5 1,300 150 $ 1,050 150 5 1,050 150 S 1,025 150 $ 1,025 200 5 1,300 150 $ 1,025 150 S 1,025 150 5 1,100 150 $ 1,650 150 5 1,650 200 $ 1,300 200 $ 1,300 250 $ 1,300 200 $ 1,195 NO No No Wes No Yes No Yes Yes Yes Yes Yes Yes No Yes No Excel File Edit View Insert Format Tools Data Window Help Anchal Exp19 Excel ch05 c Home Insert Draw Page Layout Formulas Data Review View Cut Calibri Body)   11 A  A General WIDTaxt Copy Paste B 1 Merge Ce Formal IF Xfx  SUBTOTAL(Rentals B D 1 1 2 2 2 2 1 1 2 2 2 3 301 302 303 304 305 306 307 308 401 404 402 405 407 408 403 405 601 602 603 604 605 606 507 101 102 201 202 103 104 107 108 203 204 207 200 105 Apartment Complex Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Lakeview Apartments Mountaintop View Mountaintop View Mountaintop View Mountaintop View Mountaintop View Mountaintop View Mountaintop View Mountaintop View Oak Tree living Oak Tree Living Oak Tree in Oak Tree Living Oak Tree Living Oak Tree Living Oak Tree Living Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Mollie Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows F G Years Since Security No Refund Remodel tnt P  Deposit Cleaning Deposit Total Depot 3 04 875 5 963 5 150 5 1 113 6 00 5 875 $ 8755 1505 1 025 5 99 $ 900 5900 S 150 $ 1 050 5 99 $ 900 $ 900$ 1505 1 050 4 585 1,200 $ 1 320 $ 2005 1,520 2 885 1,200 $ 1,320 $ 2005 1 520 0 845 1 200 $ 1 320 S 2005 1,520 0 59 $ 1,200 5 1,320$ 2005 1,520 1835 875 S 9635 1505 9,113 0 585 875 $ 953 S 150 $ 1,113 2 755 950 5 1,0455 200 1 245 0 885 950 $ 1 045$ 2005 1 245 2 17 $ 975 5 1,073 $ 2005 1,273 5 46 5 975 S 9755 2005 1,175 0 01$ 1,200 5 1,3205 250 $ 1,570 0 08 1,200 5 1,320$ 250 $ 1,570 7715 1 500 $ 1,500 $ 150 $ 1,650 7 50 $ 1 500 5 1,500 5 150 $ 1,650 5 715 1,200 $ 1 2005 2005 1,400 5 42 $ 1,200 $ 1 2005 2005 1 400 0 42 $ 2,000 $2,200 250 5 2 450 4 095 2 000 $2,200 $ 250 $ 2,450 3 09 $ 2,000 5 2,200 $ 250 $ 2,450 7 085 9505 950 $ 150 S 1 100 3 175 950 $ 1 045 $ 150 $ 1,195 7 055 875 $ 875 $ 1505 1,025 705$ 875 $ 875$ 150 S 1 025 5 46 5 1,100 $ 1,100 $ 2005 1,300 5 585 1 100 1 100 2005 1,300 0 50 $ 1 100 $ 1 210 $ 2005 1 410 3 71 5 1,100 5 1,210 S 200 $ 1,410 5 085 995 $ 9955 2009 1,195 10 08 995 $ 9955 200 $ 1 195 0 755 995 $ 1 0955 2005 1,295 0 755 995 $ 1 0955 2005 12295 5 585 1 500 $ 1 500 $ 250 $ 1,750 Occupied Lant Remodel Na 2018 04 15 No 2015 05 01 No 2015 05 05 Yes 2015 OS OS No 2016 10 04 Yes 2015 06 15 Yes 2020 06 30 No 2020 09 30 No 2019 07 01 No 2020 10 04 No 2018 08 01 Yes 2020 06 15 Yes 2019 03 01 Yes 2015 11 15 Yes 2021 05 05 No 2021 04 01 Yes 2013 05 15 Yes 2013 11 01 No 2015 08 15 Yel 2015 12 01 No 2020 11 30 Yes 2017 03 30 No 2018 03 30 Yes 2014 04 01 Yes 2018 03 01 Yes 2014 04 13 Yes 2014 04 13 NO 2015 11 15 Yes 2015 10 01 No 2020 11 01 Yes 2017 08 15 Yes 2016 04 01 No 2011 04 01 Yes 2020 08 01 Yes 2020 08 01 No 2015 10 01 Databases   1 2 2 3 3 3 1 1 1 1 2 2 2 2 2 2 2 2 3 Summary Rentals Point O 1 4 Excel File Edit View Insert Format Tools Data Help Window Anchal Exp19 Excel ch05 Ca Formulas Data Review View General A wp Text Merge Center IF 1 20 29 10 31 32 33 1025 1,300 1,100 1 410 1 450 Home Insert Draw Page Layout Out Caitl (Bodyt 1 Copy B VU Po xfxSUBTOTAL Rentals D 202 Robin Meadows 1 Yies 108 Rolling Meadows 2 NO 104 Rolling Meadows 2 Yes 107 Rolling Meadows 2 NO 108 Rolling Meadows 2 Yes 203 Rolling Meadows 2 Yes 204 Rolling Meadows 2 No 207 Ring Meadows 2 Ves 208 Rolling Meadows 2 Yes 105 Rolling Meadows 3 No 106 Rolling Meadows 3 Yes 109 Rolling Meadows 3 No 110 Rolling Meadows Yes 205 Rolling Meadows 3 Yes 206 Rolling Meadows No 209 Rolling Meadows Ne 210 3 Rolling Meadows Yes 501 Sunset Valley 1 Yes SO2 Sunset Valley 1 No SOB Sunset Valley 1 No 504 Sunt Valley Ves 505 Sunset Valley 2 No 506 Sunset Valley Yes 507 Sunset Valley 2 No 16 17 3 9 0 1 2014 04 11 2015 11 15 2015 10 01 2020 11 03 2017 18 2016 04 03 2011 04 01 2020 08 01 2020 08 01 2015 10 01 2000 06 15 2019 08 15 2018 10 04 2011 08 15 2015 07 01 202008 01 2019 11 04 2015 10 01 2018 08 01 2019 08 01 2014 07 02 2014 07 02 2011 10 10 2011 10 10 2 05 5 46 5 SSSS 0 505 3 715 5 085 10 08 0755 0 75 550 $ 0 88 1 713 2 58 9 71 5 83 $ 0 75$ 1493 5 585 2 755 1 75$ 6 835 6835 9 565 9 55$ G H 375 38755 1,100 $ 1,100 1 100 1 1005 1,100 $ 1 210 1 100 S12105 995 5 9955 995 $ 9955 995 $ 1 095 995 $ 1 0955 1 500 $1,500 $ 1,500 $ 1 650 1 500 5 1,650 $ 1,500 $ 1 650 1,050 $ 1 050 $ 1,050 $ 1 050 $ 1,050 $ 1 1555 1,050 $ 1 355 875 5875 875 5 963 8755 963 8755 875$ 1,100 $ 1 100 5 1,100 5 1,100 5 1,100 $ 1 100 5 1505 2005 2003 2005 2003 2005 2005 200 $ 2005 250 250 5 250 S 250 $ 2505 250 $ 250 $ 250 S 1505 1505 1505 150 $ 2009 200 $ 200 $ 1 195 1 195 1 295 1,295 1750 1 900 1,900 1 900 1,300 1 100 1,405 1 405 1,025 1 113   NE 1 025 1,300 1 300 1,300 Summan Pantal natan IF A 1 x fx  SUBTOTAL(Databases  B E Constants for Formulas Comparison Date 2021 05 01 Years Since Remodel 5 0 Deposit Older Rent 1 25) 1 0 Deposit Newer Rent 1 5) 1 1 2 4 $ 302 7 Unite Code Bed 301 LA 9 LA 10 303 LA 1 11 304 LA 12 305 LA 2 13 306 LA 2  14 307 LA 2 15 308 LA 2 16 401 MV 1 17 404 MV 1 18 402 MV 2 19 405 MV 2 20 407 MV 2 21 408 MV 2 22 403 MV 3 23 406 MV 3 24 601 OTL 1 25 602 OTL 1 26 603 OTE 2 27 604 OTL 2 28 605 on 3 29 606 OTL 3 30 507 OTL 3 31 101 RM 2 32 102 RM 33 201 RM 1 14 202 RM 35 103 RM 2 36 104 RM 37 107 RM 2 38 Occupied Last Remodel Years Since Remodel Rental Price Security Depost Non Refund Cleanins Deposit No 2018 04 15 3 04 $ 8755 9635 No 2015 05 01 6 00 $ 875 $ 875$ No 2015 05 05 5,99 $ 900 $ 900 5 Yes 2015 OS OS 5 99 $ 900 $ 500 $ No 2016 10 04 4 58 $ 1,2005 1 3205 Yes 2018 06 15 2 88 $ 1,200 $ 1 320$ Yes 2020 06 30 0 84 5 1,200 $ 1 320$ No 2020 09 30 0 59 $ 1,200 $ 1,320$ No 2019 07057 1 83 875 S 963 5 No 2020 10 04 0 58$ 875 $ 963 $ No 2018 08 01 2 75$ 950 S 1,045 5 Yes 2020 06 15 0 88 $ 950 $ 1,045$ Yes 2019 03 01 2 17 5 975$ 1,073 $ Yes 2015 11 15 5 46 $ 975 $ 975 $ Yes 2021 05 os 0 01$ 1,200 $ 1,320 S No 2021 04 01 0 08 $ 1,200 $ 1,320$ Yes 2013 08 15 7 71 $ 1,500 $ 1,500 $ Yes 2013 11 01 7 50 $ 1,500 1 500 $ No 2015 08 15 5 71 $ 1,2005 1,200 $ Yes 2015 12 01 5 42 $ 1,200 $ 1,200 $ No 2020 11 30 0 42 $ 2,000 $ 2 2005 F27 Yes 2017 03 24 4 09 $ 2,000 $ 2,200 $ No 2018 03 30 3 09 $ 2,000 $ 2,200 $ Yes 2014 04 01 7 08 $ 950 $ 950 $ Yes 2018 03 01 3 17 $ 950 $ 1,045 $ Yes 2014 04 13 7 05 $ 875 S 8755 Yes 2014 04 13 7 05 S 875$ 875 $ No 2015 11 15 5,46 $ 1,1005 1,100 $ Yes 2015 10 01 S 58 S 1,100 $ 1,100 5 No 2020 11 01 0 50 $ 1,100 $ 1,210 5 17025 Rentals Databases   Total Deposit 150 $ 1,113 1505 1,025 1505 1,050 150 $ 1,050 2005 1,520 2005 1 520 2005 2005 1,520 1505 1 111 1505 2005 1,245 2005 1,245 200 $ 1,273 2005 1,175 2505 1 570 2505 1,570 150 5 1 650 150 5 1 650 2005 1,400 200 $ 1 400 250$ 2 450 2505 2 450 250 S 2,450 150 $ 1,100 150 5 1,195 150 5 1,025 150 S 1,025 2005 1,300 2005 1 800 2005 1,410 Summary Point Conditional for Formatting as Home Insert Draw Page Layout Formulas Data Review View X Cut Calibri (Body) 11 A Wrap Text  General Copy Paste B 1 U A Format Merge   Center S IF x fxSUBTOTAL(Databases Unit Code Bled Occupied Last Remodel Years Since Remodel Rental Price Security Deposit Non Refund Cleaning Deposit 28 605 OT No 2020 11 30 0 42 2,000 $ 2 2005 606 OTL 3 Yes 2017 03 30 4 09 $ 2,000 $ 2,200 $ 30 507 OTL 3 No 2018 03 30 3 09 $ 2,000 $ 2,200 $ 31 101 RM 1 Yes 2014 04 01 7 08 $ 950 $ 950 $ 32 102 RM 1 Yes 2018 03 01 3 17 $ 950 $ 1,045$ 33 201 RM 1 Yes 2014 04 13 7 05 875$ 875 $ 34 202 RM 1 Yes 2014 04 13 7 05 $ 8755 875 S 35 103 RM 2 No 2015 11 15 5 46 5 1 100 S 1,100 $ 36 104 RM 2 Yes 2015 10 01 5 58 S 1 100 $ 1 100 S 32 107 RM 2 No 2020 11 01 0 50 $ 1,100 $ 1,210 5 38 108 RM 2 Yes 2017 08 15 3 71 $ 1,100 $ 1 210 $ 203 RM 2 Yes 2016 04 01 5 08 $ 995 $ 9955 10 204 RM 2  No 2011 04 01 10 08 995 $ 995 $ 207 EM 2 Yes 2020 08 01 0 75 $ 9955 1,095 $ 42 RM 2 Yes 2020 08 01 0 755 995$ 1,095 105 RM 3 No 2015 10 03  5 58 $ 1,500 $ 1,500 $ 106 RM 3 Yes 2020 06 15 0 88 $ 1 500 5 1,650 $ 45 109 RM 3 No 2019 08 15 1 71 5 1,500 $ 1 6505 110 RM 3 Yes 2018 10 04 2 58 $ 1,500 $ 1,650 $ 205 RM 3 Yes 2011 08 15 9 71 $ 1,050 $ 1,050 $ 18 206 RM 3 No 2015 07 01 5 83   1,050 $ 1,0505 19 209 RM 3 No 2020 08 01 0 75 $ 1,050 $ 1,155 $ 50 210 RM 3 Yes 2019 11 04 1 49 $ 1,050 $ 1,155$ 501 SV 1 Yes 2015 10 01 5 58 $ 875 $ 875$ 502 5V 1 No 2018 08 01 2 75$ 8755 963 $ 53 SO3 SV 1 No 2019 08 01 1 75$ 875$ 963 $ 504 SV 1 Yes 2014 07 02 6 83 $ 875 $ 8755 55 505 5V 2 No 2014 07 02 6 83 $ 1,100 $ 1,100 $ 56 506 SV Yes 2011 10 10 9 56 $ 1 100 $ 507 SV 2 No 2011 10 16 9 56 $ 1,100 $ 1,100 $ F56 Total Deposit 2505 2 450 250 $ 2 450 250 5 2 450 150 $ 1,100 1505 1,195 150 $ 1,025 150 $ 1,025 200 $ 1,300 2005 1 300 2005 1,410 2005 1 410 2005 1,195 2005 1,195 2005 1,295 2005 1,295 2505 1,750 250 S 1,900 250 $ 1 900 2505 1,900 250 5 1,300 250 5 1 100 250 5 1,405 250 S 1,405 150 5 1,025 150 S 150 S 1 115 150 $ 200 $ 1 300 2005 1,300 200 $ 1 300 1 025 1 100 S 59 30 62 63 Wrap Text General Fill  Insert Delete Format $  ) Merge   Center   Conditional Format Formatting as Table 00 Cell Styles Sort   Filte Clear K M N H 10 Code LA MV OTL RM SV Apartment Name Lakeview Apartments Mountaintop View Oak Tree Living Rolling Meadows Sunset Valley Security Deposit Non Refund Cleaning Deposit 8755 9636 8755 875 $ 900 $ 900S 900 $ 900 $ 1,200 $ 1,320 S 1,200 $ 1,320 $ 1,200 $ 1,320 $ 1,200 $ 1,320 S 875 S 963 $ 875$ 963 S 950 $ 1,045 $ 950 $ 1,045 $ 975$ 1,0735 9755 975 S 1,200 $ 1,320 $ 1,200 $ 1,3205 1 500 5 1,500S 1,500 $ 1,500 $ 1,200 $ 1,200 $ 1 200 $ 1,200 $ 2,000 5 2 2005 2,000 5 2 2005 2 000 5 2,200 $ 950 5 950 $ 950 5 1,045 5 875$ 875$ 75 8255 Total Deposit 150 $ 1,113 150 $ 1,025 150 $ 1,050 150 $ 1,050 200 $ 1,520 200 $ 1,520 2005 1,520 200 $ 1,520 150 $ 1,113 150 S 1,113 200 1,245 117 200 1,245 200 $ 1,273 200 $ 1,175 250 $ 1,570 250 $ 1,570 150 $ 1,650 150 $ 1,650 200 $ 1,400 200 $ 1,400 250 $ 2,450 250 $ 2,450 250 $ 2,450 150 $ 150 S 1 195 150 5 1,025 150 $ 1,025 1,100

Accepted Answer

The Answer is in the image, click to view ...

Question

can you please show the steps as well. Step Instructions Possible 1 0 2 3 3 5 2 5 7 Start Excel. Download and open

Step by Step Solution

Step: 1

Get Instant Access to Expert-Tailored Solutions

Step: 2

Step: 3

Ace Your Homework with AI

Recommended Textbook for

Database Design For Mere Mortals

Students also viewed these Databases questions

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question