Home Uncategorized Excel work- Exp19_Excel_Ch05_Cap_Apartments
Uncategorized

Excel work- Exp19_Excel_Ch05_Cap_Apartments

Must follow instructions from the word document. 

Save Time On Research and Writing
Hire a Pro to Write You a 100% Plagiarism-Free Paper.
Get My Paper

2

>Summary

/21

Years Since Remodel

n-Refund Cleaning Deposit

3

Save Time On Research and Writing
Hire a Pro to Write You a 100% Plagiarism-Free Paper.
Get My Paper

Mountaintop View 3 No

$ 1,200 $ 1,320 $ 250 $ 1,570

3 No

$ 250

2 No

Mountaintop View 1 No

2 No

$ 1,200 $ 1,320 $ 200

Rolling Meadows 2 Yes

$ 200

Rolling Meadows 2 Yes 8/1/20 0.75 $ 995 $ 1,095 $ 200 $ 1,295

Rolling Meadows 3 No 8/1/20 0.75

$ 250

Lakeview Apartments 2 Yes

$ 1,200 $ 1,320 $ 200 $ 1,520

405 Mountaintop View 2 Yes

$ 200

Rolling Meadows 3 Yes 6/15/20 0.88

$ 250

210 Rolling Meadows 3 Yes

$ 1,050 $ 1,155 $ 250

Rolling Meadows 3 No

$ 1,500 $ 1,650 $ 250 $ 1,900

1 No

$ 875 $ 963 $ 150 $ 1,113

Mountaintop View 1 No

$ 875 $ 963 $ 150 $ 1,113

Mountaintop View 2 Yes

$ 200

Rolling Meadows 3 Yes

$ 1,500 $ 1,650 $ 250 $ 1,900

Mountaintop View 2 No

$ 950 $ 1,045 $ 200 $ 1,245

Sunset Valley 1 No 8/1/18 2.75 $ 875 $ 963 $ 150 $ 1,113

Lakeview Apartments 2 Yes

$ 1,200 $ 1,320 $ 200 $ 1,520

Lakeview Apartments 1 No

$ 875 $ 963 $ 150 $ 1,113

Oak Tree Living 3 No

$ 2,000 $ 2,200 $ 250 $ 2,450

Rolling Meadows 1 Yes

$ 950 $ 1,045 $ 150

Rolling Meadows 2 Yes

$ 1,100

$ 200 $ 1,410

Oak Tree Living 3 Yes

$ 2,000 $ 2,200 $ 250 $ 2,450

Lakeview Apartments 2 No

$ 1,200 $ 1,320 $ 200 $ 1,520

Rolling Meadows 2 Yes

$ 995 $ 995 $ 200 $ 1,195

Oak Tree Living 2 Yes

$ 1,200 $ 1,200 $ 200

Mountaintop View 2 Yes

$ 975 $ 975 $ 200

Rolling Meadows 2 No 11/15/15 5.46 $ 1,100 $ 1,100 $ 200

Rolling Meadows 2 Yes

$ 1,100 $ 1,100 $ 200 $ 1,300

Rolling Meadows 3 No 10/1/15 5.58 $ 1,500 $ 1,500 $ 250

Sunset Valley 1 Yes 10/1/15 5.58 $ 875 $ 875 $ 150

Oak Tree Living 2 No

$ 1,200 $ 1,200 $ 200 $ 1,400

Rolling Meadows 3 No

$ 1,050 $ 1,050 $ 250 $ 1,300

Lakeview Apartments 1 No

$ 900 $ 150 $ 1,050

Lakeview Apartments 1 Yes 5/5/15 5.99 $ 900 $ 900 $ 150 $ 1,050

Lakeview Apartments 1 No

$ 875 $ 875 $ 150 $ 1,025

Sunset Valley 1 Yes

$ 875 $ 875 $ 150 $ 1,025

Sunset Valley 2 No 7/2/14 6.83 $ 1,100 $ 1,100 $ 200 $ 1,300

Rolling Meadows 1 Yes

$ 875 $ 875 $ 150 $ 1,025

Rolling Meadows 1 Yes 4/13/14 7.05 $ 875 $ 875 $ 150 $ 1,025

Rolling Meadows 1 Yes

$ 950 $ 950 $ 150 $ 1,100

Oak Tree Living 1 Yes

$ 1,500 $ 1,500 $ 150 $ 1,650

Oak Tree Living 1 Yes

$ 1,500 $ 1,500 $ 150 $ 1,650

Sunset Valley 2 Yes

$ 1,100 $ 1,100 $ 200 $ 1,300

Sunset Valley 2 No 10/10/11 9.56 $ 1,100 $ 1,100 $ 200 $ 1,300

Rolling Meadows 3 Yes

$ 1,050 $ 1,050 $ 250 $ 1,300

Rolling Meadows 2 No

$ 995 $ 995 $ 200 $ 1,195

Constants for Formulas
Comparison Date 5/

 1
Years Since Remodel 5.0
Deposit Older (Rent*1.25) 1.0
Deposit Newer (Rent*1.5) 1.1
Unit # Apartment Complex # Bed Occupied Last Remodel Rental Price Security Deposit No Total Deposit
40

 3 Mountaintop View Yes 5/5/21 0.01 $ 1,200 $ 1,320 $ 250 $ 1,570
406 4/1/21 0.08
605 Oak Tree Living 11/30/20 0.42 $ 2,000 $ 2,200 $ 2,450
107 Rolling Meadows 11/1/20 0.50 $ 1,100 $ 1,

 210 $ 200 $ 1,410
404 10/4/20 0.58 $ 875 $ 963 $ 150 $ 1,113
308 Lakeview Apartments 9/30/20 0.59 $ 1,520
207 8/1/20 0.75 $ 995 $ 1,095 $ 1,295
208
209 $ 1,050 $ 1,155 $ 1,

 405
307 6/30/20 0.84
6/15/20 0.88 $ 950 $ 1,045 $ 1,245
106 $ 1,500 $ 1,650 $ 1,900
11/4/19 1.49 $ 1,405
109 8/15/19 1.71
503 Sunset Valley 8/1/19 1.75
401 7/1/19 1.83
407 3/1/19 2.17 $ 975 $ 1,073 $ 1,273
110 10/4/18 2.58
402 8/1/18 2.75
502
306 6/15/18 2.88
301 4/15/18 3.04
607 3/30/18 3.09
102 3/1/18 3.17 $ 1,195
108 8/15/17 3.71 $ 1,210
606 3/30/17 4.09
305 10/4/16 4.58
203 4/1/16 5.08
604 12/1/15 5.42 $ 1,400
408 11/15/15 5.46 $ 1,175
103 $ 1,300
104 10/1/15 5.58
105 $ 1,750
501 $ 1,025
603 8/15/15 5.71
206 7/1/15 5.83
303 5/5/15 5.99 $ 900
304
302 5/1/15 6.00
504 7/2/14 6.83
505
201 4/13/14 7.05
202
101 4/1/14 7.08
602 11/1/13 7.50
601 8/15/13 7.71
506 10/10/11 9.56
507
205 8/15/11 9.71
204 4/1/11 10.08

Rentals

Unit # Apartment Complex # Bed Occupied Last Remodel Years Since Remodel Rental Price Security Deposit

Total Deposit
301 Lakeview Apartments 1 No 4/15/18 3.04 $ 875 $ 963 $ 150 $ 1,113
302 Lakeview Apartments 1 No 5/1/15 6.00 $ 875 $ 875 $ 150 $ 1,025
303 Lakeview Apartments 1 No 5/5/15 5.99 $ 900 $ 900 $ 150 $ 1,050
304 Lakeview Apartments 1 Yes 5/5/15 5.99 $ 900 $ 900 $ 150 $ 1,050
305 Lakeview Apartments 2 No 10/4/16 4.58 $ 1,200 $ 1,320 $ 200 $ 1,520
306 Lakeview Apartments 2 Yes 6/15/18 2.88 $ 1,200 $ 1,320 $ 200 $ 1,520
307 Lakeview Apartments 2 Yes 6/30/20 0.84 $ 1,200 $ 1,320 $ 200 $ 1,520
308 Lakeview Apartments 2 No 9/30/20 0.59 $ 1,200 $ 1,320 $ 200 $ 1,520
401 Mountaintop View 1 No 7/1/19 1.83 $ 875 $ 963 $ 150 $ 1,113
404 Mountaintop View 1 No 10/4/20 0.58 $ 875 $ 963 $ 150 $ 1,113
402 Mountaintop View 2 No 8/1/18 2.75 $ 950 $ 1,045 $ 200 $ 1,245
405 Mountaintop View 2 Yes 6/15/20 0.88 $ 950 $ 1,045 $ 200 $ 1,245
407 Mountaintop View 2 Yes 3/1/19 2.17 $ 975 $ 1,073 $ 200 $ 1,273
408 Mountaintop View 2 Yes 11/15/15 5.46 $ 975 $ 975 $ 200 $ 1,175

Mountaintop View 3 Yes 5/5/21 0.01 $ 1,200 $ 1,320 $ 250 $ 1,570
406 Mountaintop View 3 No 4/1/21 0.08 $ 1,200 $ 1,320 $ 250 $ 1,570
601 Oak Tree Living 1 Yes 8/15/13 7.71 $ 1,500 $ 1,500 $ 150 $ 1,650
602 Oak Tree Living 1 Yes 11/1/13 7.50 $ 1,500 $ 1,500 $ 150 $ 1,650
603 Oak Tree Living 2 No 8/15/15 5.71 $ 1,200 $ 1,200 $ 200 $ 1,400
604 Oak Tree Living 2 Yes 12/1/15 5.42 $ 1,200 $ 1,200 $ 200 $ 1,400
605 Oak Tree Living 3 No 11/30/20 0.42 $ 2,000 $ 2,200 $ 250 $ 2,450
606 Oak Tree Living 3 Yes 3/30/17 4.09 $ 2,000 $ 2,200 $ 250 $ 2,450
607 Oak Tree Living 3 No 3/30/18 3.09 $ 2,000 $ 2,200 $ 250 $ 2,450
101 Rolling Meadows 1 Yes 4/1/14 7.08 $ 950 $ 950 $ 150 $ 1,100
102 Rolling Meadows 1 Yes 3/1/18 3.17 $ 950 $ 1,045 $ 150 $ 1,195
201 Rolling Meadows 1 Yes 4/13/14 7.05 $ 875 $ 875 $ 150 $ 1,025
202 Rolling Meadows 1 Yes 4/13/14 7.05 $ 875 $ 875 $ 150 $ 1,025
103 Rolling Meadows 2 No 11/15/15 5.46 $ 1,100 $ 1,100 $ 200 $ 1,300
104 Rolling Meadows 2 Yes 10/1/15 5.58 $ 1,100 $ 1,100 $ 200 $ 1,300
107 Rolling Meadows 2 No 11/1/20 0.50 $ 1,100 $ 1,210 $ 200 $ 1,410
108 Rolling Meadows 2 Yes 8/15/17 3.71 $ 1,100 $ 1,210 $ 200 $ 1,410
203 Rolling Meadows 2 Yes 4/1/16 5.08 $ 995 $ 995 $ 200 $ 1,195
204 Rolling Meadows 2 No 4/1/11 10.08 $ 995 $ 995 $ 200 $ 1,195
207 Rolling Meadows 2 Yes 8/1/20 0.75 $ 995 $ 1,095 $ 200 $ 1,295
208 Rolling Meadows 2 Yes 8/1/20 0.75 $ 995 $ 1,095 $ 200 $ 1,295
105 Rolling Meadows 3 No 10/1/15 5.58 $ 1,500 $ 1,500 $ 250 $ 1,750
106 Rolling Meadows 3 Yes 6/15/20 0.88 $ 1,500 $ 1,650 $ 250 $ 1,900
109 Rolling Meadows 3 No 8/15/19 1.71 $ 1,500 $ 1,650 $ 250 $ 1,900
110 Rolling Meadows 3 Yes 10/4/18 2.58 $ 1,500 $ 1,650 $ 250 $ 1,900
205 Rolling Meadows 3 Yes 8/15/11 9.71 $ 1,050 $ 1,050 $ 250 $ 1,300
206 Rolling Meadows 3 No 7/1/15 5.83 $ 1,050 $ 1,050 $ 250 $ 1,300
209 Rolling Meadows 3 No 8/1/20 0.75 $ 1,050 $ 1,155 $ 250 $ 1,405
210 Rolling Meadows 3 Yes 11/4/19 1.49 $ 1,050 $ 1,155 $ 250 $ 1,405
501 Sunset Valley 1 Yes 10/1/15 5.58 $ 875 $ 875 $ 150 $ 1,025
502 Sunset Valley 1 No 8/1/18 2.75 $ 875 $ 963 $ 150 $ 1,113
503 Sunset Valley 1 No 8/1/19 1.75 $ 875 $ 963 $ 150 $ 1,113
504 Sunset Valley 1 Yes 7/2/14 6.83 $ 875 $ 875 $ 150 $ 1,025
505 Sunset Valley 2 No 7/2/14 6.83 $ 1,100 $ 1,100 $ 200 $ 1,300
506 Sunset Valley 2 Yes 10/10/11 9.56 $ 1,100 $ 1,100 $ 200 $ 1,300
507 Sunset Valley 2 No 10/10/11 9.56 $ 1,100 $ 1,100 $ 200 $ 1,300

Non-Refund Cleaning Deposit 403

Databases

Constants for Formulas
Comparison Date

Years Since Remodel 5.0
Deposit Older (Rent*1.25) 1.0
Deposit Newer (Rent*1.5) 1.1

Unit #

# Bed Occupied Last Remodel Years Since Remodel Rental Price Security Deposit Non-Refund Cleaning Deposit Total Deposit Code

301

1 No 4/15/18 3.04 $ 875 $ 963 $ 150 $ 1,113 LA Lakeview Apartments

302 LA 1 No 5/1/15 6.00 $ 875 $ 875 $ 150 $ 1,025

Mountaintop View

303 LA 1 No 5/5/15 5.99 $ 900 $ 900 $ 150 $ 1,050

Oak Tree Living

304 LA 1 Yes 5/5/15 5.99 $ 900 $ 900 $ 150 $ 1,050

Rolling Meadows

305 LA 2 No 10/4/16 4.58 $ 1,200 $ 1,320 $ 200 $ 1,520

Sunset Valley

306 LA 2 Yes 6/15/18 2.88 $ 1,200 $ 1,320 $ 200 $ 1,520
307 LA 2 Yes 6/30/20 0.84 $ 1,200 $ 1,320 $ 200 $ 1,520
308 LA 2 No 9/30/20 0.59 $ 1,200 $ 1,320 $ 200 $ 1,520
401 MV 1 No 7/1/19 1.83 $ 875 $ 963 $ 150 $ 1,113
404 MV 1 No 10/4/20 0.58 $ 875 $ 963 $ 150 $ 1,113
402 MV 2 No 8/1/18 2.75 $ 950 $ 1,045 $ 200 $ 1,245
405 MV 2 Yes 6/15/20 0.88 $ 950 $ 1,045 $ 200 $ 1,245
407 MV 2 Yes 3/1/19 2.17 $ 975 $ 1,073 $ 200 $ 1,273
408 MV 2 Yes 11/15/15 5.46 $ 975 $ 975 $ 200 $ 1,175
403 MV 3 Yes 5/5/21 0.01 $ 1,200 $ 1,320 $ 250 $ 1,570
406 MV 3 No 4/1/21 0.08 $ 1,200 $ 1,320 $ 250 $ 1,570
601 OTL 1 Yes 8/15/13 7.71 $ 1,500 $ 1,500 $ 150 $ 1,650
602 OTL 1 Yes 11/1/13 7.50 $ 1,500 $ 1,500 $ 150 $ 1,650
603 OTL 2 No 8/15/15 5.71 $ 1,200 $ 1,200 $ 200 $ 1,400
604 OTL 2 Yes 12/1/15 5.42 $ 1,200 $ 1,200 $ 200 $ 1,400
605 OTL 3 No 11/30/20 0.42 $ 2,000 $ 2,200 $ 250 $ 2,450
606 OTL 3 Yes 3/30/17 4.09 $ 2,000 $ 2,200 $ 250 $ 2,450
607 OTL 3 No 3/30/18 3.09 $ 2,000 $ 2,200 $ 250 $ 2,450
101 RM 1 Yes 4/1/14 7.08 $ 950 $ 950 $ 150 $ 1,100
102 RM 1 Yes 3/1/18 3.17 $ 950 $ 1,045 $ 150 $ 1,195
201 RM 1 Yes 4/13/14 7.05 $ 875 $ 875 $ 150 $ 1,025
202 RM 1 Yes 4/13/14 7.05 $ 875 $ 875 $ 150 $ 1,025
103 RM 2 No 11/15/15 5.46 $ 1,100 $ 1,100 $ 200 $ 1,300
104 RM 2 Yes 10/1/15 5.58 $ 1,100 $ 1,100 $ 200 $ 1,300
107 RM 2 No 11/1/20 0.50 $ 1,100 $ 1,210 $ 200 $ 1,410
108 RM 2 Yes 8/15/17 3.71 $ 1,100 $ 1,210 $ 200 $ 1,410
203 RM 2 Yes 4/1/16 5.08 $ 995 $ 995 $ 200 $ 1,195
204 RM 2 No 4/1/11 10.08 $ 995 $ 995 $ 200 $ 1,195
207 RM 2 Yes 8/1/20 0.75 $ 995 $ 1,095 $ 200 $ 1,295
208 RM 2 Yes 8/1/20 0.75 $ 995 $ 1,095 $ 200 $ 1,295
105 RM 3 No 10/1/15 5.58 $ 1,500 $ 1,500 $ 250 $ 1,750
106 RM 3 Yes 6/15/20 0.88 $ 1,500 $ 1,650 $ 250 $ 1,900
109 RM 3 No 8/15/19 1.71 $ 1,500 $ 1,650 $ 250 $ 1,900
110 RM 3 Yes 10/4/18 2.58 $ 1,500 $ 1,650 $ 250 $ 1,900
205 RM 3 Yes 8/15/11 9.71 $ 1,050 $ 1,050 $ 250 $ 1,300
206 RM 3 No 7/1/15 5.83 $ 1,050 $ 1,050 $ 250 $ 1,300
209 RM 3 No 8/1/20 0.75 $ 1,050 $ 1,155 $ 250 $ 1,405
210 RM 3 Yes 11/4/19 1.49 $ 1,050 $ 1,155 $ 250 $ 1,405
501 SV 1 Yes 10/1/15 5.58 $ 875 $ 875 $ 150 $ 1,025
502 SV 1 No 8/1/18 2.75 $ 875 $ 963 $ 150 $ 1,113
503 SV 1 No 8/1/19 1.75 $ 875 $ 963 $ 150 $ 1,113
504 SV 1 Yes 7/2/14 6.83 $ 875 $ 875 $ 150 $ 1,025
505 SV 2 No 7/2/14 6.83 $ 1,100 $ 1,100 $ 200 $ 1,300
506 SV 2 Yes 10/10/11 9.56 $ 1,100 $ 1,100 $ 200 $ 1,300
507 SV 2 No 10/10/11 9.56 $ 1,100 $ 1,100 $ 200 $ 1,300
5/1/21
Code Apartment Name
LA
MV
OTL
RM
SV

Grader – Instructions Excel 2019 Project

Exp19_Excel_Ch05_Cap_Apartments

Project Description:

You manage several apartment complexes in Phoenix, Arizona. You created a dataset that lists details for each apartment complex, such as apartment number, including number of bedrooms, whether the unit is rented or vacant, the last remodel date, rent, and deposits. You will use the datasets to aggregate data to analyze the apartments at the complexes.

Steps to Perform:

Step

Instructions

Points Possible

1

Start Excel. Download and open the file named Exp19_Excel_Ch05_Cap_Apartments.xlsx. Grader has automatically added your last name to the beginning of the filename.

0

2

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.

3

3

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.

5

4

Use the outline symbols to display only the subtotal rows. Create an automatic outline and collapse the outline above Total Deposit.

2

5

You want to create a PivotTable 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 PivotTable Rental Revenue.

7

6

Display the Apartment Complex and # Bed fields in Rows and the Rental Price field as Values.

6

7

Format the Sum of Rental Price for Accounting Number Format with zero decimal places and enter the custom name Total Rent Collected.

3

8

Select the Occupied field for the filter and set the filter to Yes to display data for occupied apartments.

3

9

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.

15

10

Select the range B3:C3 and apply these formats: wrap text, Align Right horizontal alignment, and 30 row height. Select column B and set 9.29 column width. Select column C and set 14.43 column width.

5

11

Apply Light Orange, Pivot Style Medium 10 to the PivotTable and display banded rows.

5

12

Insert a slicer for # Bed so that you can filter the dataset by number of bedrooms. Change the slicer caption to # of Bedrooms.

5

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.

6

14

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.

5

15

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.

5

16

You want to create a PivotTable from the related tables.
Create a PivotTable using the data model on a new sheet. Change the sheet name to Bedrooms. Name the PivotTable BedroomData.

5

17

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.

5

18

Create a Clustered Column PivotChart. Cut the chart and paste it in cell A13.

5

19

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.

5

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.

5

21

Save and close Exp19_Excel_Ch05_Cap_Apartments.xlsx. Exit Excel. Submit the file as directed.

0

Total Points

100

Created On: 08/22/2019 1 Exp19_Excel_Ch05_Cap – Apartments 1.0

Hire a Writer
Calculate your order
Pages (275 words)
Standard price: $0.00
Client Reviews
4.9
Sitejabber
4.6
Trustpilot
4.8
Our Guarantees
100% Confidentiality
Information about customers is confidential and never disclosed to third parties.
Original Writing
We complete all papers from scratch. You can get a plagiarism report.
Timely Delivery
No missed deadlines – 97% of assignments are completed in time.
Money Back
If you're confident that a writer didn't follow your order details, ask for a refund.

Calculate the price of your order

You will get a personal manager and a discount.
We'll send you the first draft for approval by at
Total price:
$0.00
Power up Your Academic Success with the
Team of Professionals. We’ve Got Your Back.
Power up Your Study Success with Experts We’ve Got Your Back.
Order Now Order Now

Order your essay today and save 30% with the discount code ESSAYHELP

Order Now