Home Insert Draw Page Layout Formulas Data Review ViewHelp Tell me what you want to do . 1 . Conditional Format Gall ommag es She urobum - 97 Son & P B T . . 0.A. F Merge Center. e rt Delele format Find - 1 X for Mountaintop View Constants for Formulas Comparison Date 51/2001 Years Since Remodel Deposit Older (Rent 1.25) Deposit Newer ent 1.5) Occupied Non-Refund Years Since Security Total Remodel Rental Price Deposit Deposit Deposit 0.01 $ 1.2005 1.105 250S 1.570 0.08 $ 1,200 $ 1.320 S 250 $ 150 20 2450 0.50 $ 1.100 $ 1210 2 00S 1.410 Last Remodel 5/9/2021 4/1/2021 11/10/2000 11/1/2020 10/4/2020 W /2000 /1/2020 1/1/2020 8/1/2020 2020 6/15/2020 1.320 S 1,200 S 15 S 985 $ 1.00 $ 1095 S 1,155 S Unik Apartment Complex 403 Mountaintop View 406 Mountaintop View 605 Oak Tree Living 107 Rolling Meadows 404 Mountaintop View 308 Lakeview Apartments 207 Rolling Meadows 208 Rolling Meadows 2013 Rolling Meadows 307 Lakeview Apartments 405 Mountaintop View 106 Rolling Meadows 210 Rolling Meadows 109 Rolling Meadows 503 Sunset Valley 401 Mountaintop View 407 Mountaintop View 110 Rolling Meadows 402 Mountaintop View 502 Sunset View Summary Rentals 3 0.59 S 05 S 0.75 S 0. 7 5 OS O $ 5 140 5 171 $ 1.25$ 200 $ 1.520 200 $ 1795 200 $ 1.295 50S 1405 200 $ 1.520 200 5 1,265 1,0455 950 S 1 500 5 10 1.500 1.155 S 3 1 .000 5 963 S M/S $ 11/4/2019 IV/2019 1/2019 2/1/2019 1/2019 1074/2018 1/2018 1/1/2018 20140 250 $ 1,900 150 S 1,113 150 $ 1,113 2005 1273 250 $ 1.900 20051205 1 5 2175 2.58 2.755 1.500S 1.650 90S 0SS Databases 1 Type here to search O e BE File Home insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do . Axon In copy - Format Printer Calibri BIU 11. AA .A A . E = S wap Ted E Merge Center 1 D formataset % -% 13 Condo Delete comm e deat ZV er h und Select - Lakew Apartments Years Since Deruled Last Rode Rod e ntal Price Depout Cleanne De Tots Depos 1 04 S31 SS Lakeview Apartments Lakeview Apartments 302 900 5900 5/1/2015 5/5/2015 5/5/2015 10/4/2016 306 307 Lakeview Apartm Lakeview Apartments 20/2020 W 2020 71/2019 09 S 1 .200 $ 1.320 S 10 11 12 13 401 404 402 405 Mountaintop View Mountaintop View Mountaintop View Mountaintop View 7.75 950 S 1055 1/2018 15/2020 2.17 $ 11/15/2015 5/5/2021 0.01$ 1,200 1,200 $ 1.320 $ 1.320 S S 15/2013 11/1/2011 7.50 $ 1.500 S VOS 12/1/2015 15 408 Mountaintop View 16 403 Mountaintop View 17 406 Mountaintop View 18 601 Oak Tree Living 19 ) Oak Tree Living 2001 Ore Living 21 604 Tree 22 05 Oak Tree 23 606 Oak Treeting 24 Oak Tree Living 5 101 | H A n . 26 100 Rolling Meadows 27 201 Rolling Meadows 28 202 Rolling Meadows Summary Rentals 542 5 0.42 $ 1,200 2,000 2.000 2,000 $ 1.2005 $2,200 ,00 ,00 S 0 1/2014 V1/2018 3175 950 $ 1.0455 7.055 205/5585 1 2014 Databases D Type here to search O ei The Home Insert Draw Page Layout Formulas Data Review View Help Design Tell me what you want to do - InCopy some winter BI. . .A Fa m e $ . % 9 Gear Son & Filter Ainda Select . XfA Constants for Formulas Comparison Dante Years Since Remodel Deposit Older Hent 1.25) Deposit Newer (Rent 1.5) Sede Apartment Name Dupied Last Remodel 7 Unk 8 301 C ode LA Red 1 Years Since Ren odel- 201$ 6.00 $ 825$ Montaltop View Meadow 303 304 105 A A A 10/4/2016 1,120 $ 105 2005 135 402 MV 1/2018 15/2020 11/15/2015 5/2001 4/2021 0015 1.2005 1.2005 22 403 MV 23 6 MV 401 L1 1,230 1,005 11/1/2013 2005 26 27 604 TL TL 20$ 2100 12/1/2015 11/ 2020 2017 29 60 Summary Rentals Databases Type here to search O e NO References Mailings Review View Help Search Step Instructions Points Possible Start Excel. Download and open the file named Exp19 Exo 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 PivotTable to determine the total monthly rental revenue for occupied 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 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 C3 and apply these formats wap text Align Right horizontal alignment and 30 row height Select column B and set 9.20 column width Select column C and set 14.43 column width Exp 19.Excel.Chos_Cap.Apartments_Instructions (1) . Saved to this PC References Mailings Review View Help Search Select the range 3 3 and apply these formats wrap text, Align Right horizontal alignment, and 30 row height. Select couran 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 Pivottable and display banded rows. Created On 02212019 p19 Excel Chos Cap - Apartments 10 Step Instructions Points Possible 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 shoor height to 14 inches and width to 1.75 inches. Apply Light Orange Slicer Style Light 2 Cut the slicer and paste in cell E2 Insert a timeline for the Last Remodel bield Change the time period to YEARS Apply Light Orange. Timeline Style Light 2 Change the timeline height to 1.4 inches and with to 175 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 be using The Code old and the COMPLEX t i ng the Code Seld You want to create a Prvote from the related tables. Create a PivoTable using the data model on a new sheet Change the sheet came to DOB e a w References Mailing Review View Help Search Excel 2019 Projed Grader - Instructions Step Instructions Points Possible 12 Insert a sicer for Bed so that you can filter the dataset by number of bedrooms, Change the Slicer caption for of Bedrooms 13 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 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 PivolTable 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 Bedroom Data 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 total 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 fill color Apply Black Teod 1 font color to the vertical axis and category audis Change the chart height to 3 inches and the width to 5 inches, if necessary. Hide the field buttons in the PivotChart 20 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 21 Save and close Exp 19_Excolchos_Cap_Apartments xlsx Exit Excel Submit the file as directed Dp 19.Excel_CHO_Cap Apartments_Instructions (1) - Protected View - Saved to this PC Review View Help Tell me what you want to do References Mailings contain viruses. Unless you need to edit it's safer to stay in Protected View Enable Editing Grade-Instructions Excel 2019 Project Exp19_Excel_Ch05_Cap_Apartments Project Description: You manage several apartment complexes in Phoenix Arcona You created a data that for each apartment Complex, such aspartment number, including number of bedrooms whether the trend or vacant the last model daberent and deposits You will use the datasets to agregate data to analyze the apartments the comples Steps to Perform: Step Instructions Points Possible Start Excel Download and open the file named Exp19 Excel Chos Cap Apartments Grader has auto cally added your last name to the beginning of the filename Before sub ling the data, you need to sort the data Select the Summary sheet Sort the data by Apartment Complex inhabbal order and further sort 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 wenge Tot Deposit Add a second subtotal (without removing the first subtotal) by Bed to calculate the verge Total Deposit by the number of bedrooms Use the outline symbols to display only the subtotal rows. Create an automatic outline and collapse the o ne above Total Deposit You want to create a Pivot Table to determine the total monthly rental apartments o ccupied Deplay the Rental sheet and create a bank Pivot Table on a new worksheet to the of the Rental sheet Change the name of the worksheet to Rental Revenue Name the Portable Rental Revenue Deplay the Apartment complex and Bed fields in Rows and the Rental Priced as Values Fom the sum of Rental Price for Accounting Number Forma who decimal places and Dan Cara Ote + Add to Ten mode view Apartments , Mountain View $410 069 1 Type here to search - 40 AIN Home Insert Draw Page Layout Formulas Data Review ViewHelp Tell me what you want to do . 1 . Conditional Format Gall ommag es She urobum - 97 Son & P B T . . 0.A. F Merge Center. e rt Delele format Find - 1 X for Mountaintop View Constants for Formulas Comparison Date 51/2001 Years Since Remodel Deposit Older (Rent 1.25) Deposit Newer ent 1.5) Occupied Non-Refund Years Since Security Total Remodel Rental Price Deposit Deposit Deposit 0.01 $ 1.2005 1.105 250S 1.570 0.08 $ 1,200 $ 1.320 S 250 $ 150 20 2450 0.50 $ 1.100 $ 1210 2 00S 1.410 Last Remodel 5/9/2021 4/1/2021 11/10/2000 11/1/2020 10/4/2020 W /2000 /1/2020 1/1/2020 8/1/2020 2020 6/15/2020 1.320 S 1,200 S 15 S 985 $ 1.00 $ 1095 S 1,155 S Unik Apartment Complex 403 Mountaintop View 406 Mountaintop View 605 Oak Tree Living 107 Rolling Meadows 404 Mountaintop View 308 Lakeview Apartments 207 Rolling Meadows 208 Rolling Meadows 2013 Rolling Meadows 307 Lakeview Apartments 405 Mountaintop View 106 Rolling Meadows 210 Rolling Meadows 109 Rolling Meadows 503 Sunset Valley 401 Mountaintop View 407 Mountaintop View 110 Rolling Meadows 402 Mountaintop View 502 Sunset View Summary Rentals 3 0.59 S 05 S 0.75 S 0. 7 5 OS O $ 5 140 5 171 $ 1.25$ 200 $ 1.520 200 $ 1795 200 $ 1.295 50S 1405 200 $ 1.520 200 5 1,265 1,0455 950 S 1 500 5 10 1.500 1.155 S 3 1 .000 5 963 S M/S $ 11/4/2019 IV/2019 1/2019 2/1/2019 1/2019 1074/2018 1/2018 1/1/2018 20140 250 $ 1,900 150 S 1,113 150 $ 1,113 2005 1273 250 $ 1.900 20051205 1 5 2175 2.58 2.755 1.500S 1.650 90S 0SS Databases 1 Type here to search O e BE File Home insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do . Axon In copy - Format Printer Calibri BIU 11. AA .A A . E = S wap Ted E Merge Center 1 D formataset % -% 13 Condo Delete comm e deat ZV er h und Select - Lakew Apartments Years Since Deruled Last Rode Rod e ntal Price Depout Cleanne De Tots Depos 1 04 S31 SS Lakeview Apartments Lakeview Apartments 302 900 5900 5/1/2015 5/5/2015 5/5/2015 10/4/2016 306 307 Lakeview Apartm Lakeview Apartments 20/2020 W 2020 71/2019 09 S 1 .200 $ 1.320 S 10 11 12 13 401 404 402 405 Mountaintop View Mountaintop View Mountaintop View Mountaintop View 7.75 950 S 1055 1/2018 15/2020 2.17 $ 11/15/2015 5/5/2021 0.01$ 1,200 1,200 $ 1.320 $ 1.320 S S 15/2013 11/1/2011 7.50 $ 1.500 S VOS 12/1/2015 15 408 Mountaintop View 16 403 Mountaintop View 17 406 Mountaintop View 18 601 Oak Tree Living 19 ) Oak Tree Living 2001 Ore Living 21 604 Tree 22 05 Oak Tree 23 606 Oak Treeting 24 Oak Tree Living 5 101 | H A n . 26 100 Rolling Meadows 27 201 Rolling Meadows 28 202 Rolling Meadows Summary Rentals 542 5 0.42 $ 1,200 2,000 2.000 2,000 $ 1.2005 $2,200 ,00 ,00 S 0 1/2014 V1/2018 3175 950 $ 1.0455 7.055 205/5585 1 2014 Databases D Type here to search O ei The Home Insert Draw Page Layout Formulas Data Review View Help Design Tell me what you want to do - InCopy some winter BI. . .A Fa m e $ . % 9 Gear Son & Filter Ainda Select . XfA Constants for Formulas Comparison Dante Years Since Remodel Deposit Older Hent 1.25) Deposit Newer (Rent 1.5) Sede Apartment Name Dupied Last Remodel 7 Unk 8 301 C ode LA Red 1 Years Since Ren odel- 201$ 6.00 $ 825$ Montaltop View Meadow 303 304 105 A A A 10/4/2016 1,120 $ 105 2005 135 402 MV 1/2018 15/2020 11/15/2015 5/2001 4/2021 0015 1.2005 1.2005 22 403 MV 23 6 MV 401 L1 1,230 1,005 11/1/2013 2005 26 27 604 TL TL 20$ 2100 12/1/2015 11/ 2020 2017 29 60 Summary Rentals Databases Type here to search O e NO References Mailings Review View Help Search Step Instructions Points Possible Start Excel. Download and open the file named Exp19 Exo 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 PivotTable to determine the total monthly rental revenue for occupied 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 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 C3 and apply these formats wap text Align Right horizontal alignment and 30 row height Select column B and set 9.20 column width Select column C and set 14.43 column width Exp 19.Excel.Chos_Cap.Apartments_Instructions (1) . Saved to this PC References Mailings Review View Help Search Select the range 3 3 and apply these formats wrap text, Align Right horizontal alignment, and 30 row height. Select couran 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 Pivottable and display banded rows. Created On 02212019 p19 Excel Chos Cap - Apartments 10 Step Instructions Points Possible 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 shoor height to 14 inches and width to 1.75 inches. Apply Light Orange Slicer Style Light 2 Cut the slicer and paste in cell E2 Insert a timeline for the Last Remodel bield Change the time period to YEARS Apply Light Orange. Timeline Style Light 2 Change the timeline height to 1.4 inches and with to 175 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 be using The Code old and the COMPLEX t i ng the Code Seld You want to create a Prvote from the related tables. Create a PivoTable using the data model on a new sheet Change the sheet came to DOB e a w References Mailing Review View Help Search Excel 2019 Projed Grader - Instructions Step Instructions Points Possible 12 Insert a sicer for Bed so that you can filter the dataset by number of bedrooms, Change the Slicer caption for of Bedrooms 13 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 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 PivolTable 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 Bedroom Data 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 total 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 fill color Apply Black Teod 1 font color to the vertical axis and category audis Change the chart height to 3 inches and the width to 5 inches, if necessary. Hide the field buttons in the PivotChart 20 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 21 Save and close Exp 19_Excolchos_Cap_Apartments xlsx Exit Excel Submit the file as directed Dp 19.Excel_CHO_Cap Apartments_Instructions (1) - Protected View - Saved to this PC Review View Help Tell me what you want to do References Mailings contain viruses. Unless you need to edit it's safer to stay in Protected View Enable Editing Grade-Instructions Excel 2019 Project Exp19_Excel_Ch05_Cap_Apartments Project Description: You manage several apartment complexes in Phoenix Arcona You created a data that for each apartment Complex, such aspartment number, including number of bedrooms whether the trend or vacant the last model daberent and deposits You will use the datasets to agregate data to analyze the apartments the comples Steps to Perform: Step Instructions Points Possible Start Excel Download and open the file named Exp19 Excel Chos Cap Apartments Grader has auto cally added your last name to the beginning of the filename Before sub ling the data, you need to sort the data Select the Summary sheet Sort the data by Apartment Complex inhabbal order and further sort 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 wenge Tot Deposit Add a second subtotal (without removing the first subtotal) by Bed to calculate the verge Total Deposit by the number of bedrooms Use the outline symbols to display only the subtotal rows. Create an automatic outline and collapse the o ne above Total Deposit You want to create a Pivot Table to determine the total monthly rental apartments o ccupied Deplay the Rental sheet and create a bank Pivot Table on a new worksheet to the of the Rental sheet Change the name of the worksheet to Rental Revenue Name the Portable Rental Revenue Deplay the Apartment complex and Bed fields in Rows and the Rental Priced as Values Fom the sum of Rental Price for Accounting Number Forma who decimal places and Dan Cara Ote + Add to Ten mode view Apartments , Mountain View $410 069 1 Type here to search - 40 AIN
