Order Now

Expert answer:Excel assignment help..

Uncategorized

Solved by verified expert:I attached 2 files below. The Excel file is where to do the work. Word file contains questions.
duong_exploring_e07_grader_h1_apartment.xlsx

ex16xlch07gradercaphw___apartment_complex_17_instructions.docx

Unformatted Attachment Preview

Search Engine
Enter Unit # to see rent
Rental Price
Unit #
101
102
103
104
105
106
107
108
109
110
201
202
203
204
205
206
207
208
209
210
301
302
303
304
305
306
307
Exploring
Constants for Formulas
101
Apartment Complex
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Lakeview Apartments
Lakeview Apartments
Lakeview Apartments
Lakeview Apartments
Lakeview Apartments
Lakeview Apartments
Lakeview Apartments
Comparison Date
No. of Years Since Remodel
Pet Deposit 2 bedroom newer
Pet Deposit other
# Bed
1
1
2
2
3
3
2
2
3
3
1
1
2
2
3
3
2
2
3
3
1
1
1
1
2
2
2
Rental Price
$
950
$
950
$
1,100
$
1,100
$
1,500
$
1,500
$
1,100
$
1,100
$
1,500
$
1,500
$
875
$
875
$
995
$
995
$
1,050
$
1,050
$
995
$
995
$
1,050
$
1,050
$
875
$
875
$
900
$
900
$
1,200
$
1,200
$
1,200
Occupied
Yes
Yes
No
Yes
No
Yes
No
Yes
No
Yes
Yes
Yes
Yes
No
Yes
No
Yes
Yes
No
Yes
No
No
No
Yes
No
Yes
Yes
Summary
Last Remodel
4/1/2004
3/1/2013
11/15/2005
10/1/2015
10/1/2015
6/15/2004
11/1/2015
8/15/2009
8/15/2009
10/4/2006
4/13/2016
3/1/2013
4/1/2004
4/1/2010
8/15/2011
7/1/2005
8/1/2015
8/1/2015
8/1/2015
11/4/2006
4/15/2008
5/1/2015
5/5/2010
5/5/2010
10/4/2006
6/15/2018
6/30/2008
Years Since
Remodel
1/1/2018
10
$275
$200
Pet Deposit
Recommendation
20171031182627duong_exploring_e07_grader_h1_apartment.xlsx
308
401
402
403
404
405
406
407
408
501
502
503
504
505
506
507
601
602
603
604
605
606
607
Exploring
Lakeview Apartments
Mountaintop View
Mountaintop View
Mountaintop View
Mountaintop View
Mountaintop View
Mountaintop View
Mountaintop View
Mountaintop View
Sunset Valley
Sunset Valley
Sunset Valley
Sunset Valley
Sunset Valley
Sunset Valley
Sunset Valley
Oak Tree Living
Oak Tree Living
Oak Tree Living
Oak Tree Living
Oak Tree Living
Oak Tree Living
Oak Tree Living
2
1
2
3
1
2
3
2
2
1
1
1
1
2
2
2
1
1
2
2
3
3
3
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
1,200
875
950
1,200
875
950
1,200
975
975
875
875
875
875
1,100
1,100
1,100
1,500
1,500
1,200
1,200
2,000
2,000
2,000
No
No
No
Yes
No
Yes
No
Yes
Yes
Yes
No
No
Yes
No
Yes
No
Yes
Yes
No
Yes
No
Yes
No
Summary
9/30/2007
7/1/2005
8/1/2015
5/5/2010
10/4/2006
6/15/2018
4/1/2004
3/1/2013
11/15/2005
10/1/2015
8/1/2015
8/1/2015
7/2/2003
7/2/2003
10/10/2010
10/10/2010
8/15/2004
11/1/2000
8/15/2004
12/1/2003
11/30/2000
3/30/2002
3/30/2002
20171031182627duong_exploring_e07_grader_h1_apartment.xlsx
Criteria Range
Unit #
Apartment Complex
# Bed
Rental Price
Occupied
Last Remodel
Years Since
Remodel
Recommendation
Last Remodel
4/1/2004
3/1/2013
11/15/2005
10/1/2015
10/1/2015
6/15/2004
11/1/2015
8/15/2009
8/15/2009
10/4/2006
4/13/2016
3/1/2013
4/1/2004
4/1/2010
8/15/2011
7/1/2005
Years Since
Remodel
13.75
4.83
12.13
2.25
2.25
13.54
2.17
8.38
8.38
11.24
1.72
4.83
13.75
7.75
6.38
12.50
Recommendation
No Change
No Change
Need to Remodel
No Change
No Change
No Change
No Change
No Change
No Change
No Change
No Change
No Change
No Change
No Change
No Change
Need to Remodel
Database Statistics
No. of Apts. to Remodel
Value of Lost Rent
Year of Oldest Remodel
List of Rental Property
Unit #
101
102
103
104
105
106
107
108
109
110
201
202
203
204
205
206
Exploring
Apartment Complex
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
# Bed
1
1
2
2
3
3
2
2
3
3
1
1
2
2
3
3
Rental Price
950
950
1,100
1,100
1,500
1,500
1,100
1,100
1,500
1,500
875
875
995
995
1,050
1,050
Occupied
Yes
Yes
No
Yes
No
Yes
No
Yes
No
Yes
Yes
Yes
Yes
No
Yes
No
Database
20171031182627duong_exploring_e07_grader_h1_apartment.xlsx
207
208
209
210
301
302
303
304
305
306
307
308
401
402
403
404
405
406
407
408
501
502
503
504
505
506
507
601
602
603
604
605
606
607
Exploring
Rolling Meadows
Rolling Meadows
Rolling Meadows
Rolling Meadows
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
Sunset Valley
Sunset Valley
Sunset Valley
Sunset Valley
Sunset Valley
Sunset Valley
Sunset Valley
Oak Tree Living
Oak Tree Living
Oak Tree Living
Oak Tree Living
Oak Tree Living
Oak Tree Living
Oak Tree Living
2
2
3
3
1
1
1
1
2
2
2
2
1
2
3
1
2
3
2
2
1
1
1
1
2
2
2
1
1
2
2
3
3
3
995
995
1,050
1,050
875
875
900
900
1,200
1,200
1,200
1,200
875
950
1,200
875
950
1,200
975
975
875
875
875
875
1,100
1,100
1,100
1,500
1,500
1,200
1,200
2,000
2,000
2,000
Database
Yes
Yes
No
Yes
No
No
No
Yes
No
Yes
Yes
No
No
No
Yes
No
Yes
No
Yes
Yes
Yes
No
No
Yes
No
Yes
No
Yes
Yes
No
Yes
No
Yes
No
8/1/2015
8/1/2015
8/1/2015
11/4/2006
4/15/2008
5/1/2015
5/5/2010
5/5/2010
10/4/2006
6/15/2018
6/30/2008
9/30/2007
7/1/2005
8/1/2015
5/5/2010
10/4/2006
6/15/2018
4/1/2004
3/1/2013
11/15/2005
10/1/2015
8/1/2015
8/1/2015
7/2/2003
7/2/2003
10/10/2010
10/10/2010
8/15/2004
11/1/2000
8/15/2004
12/1/2003
11/30/2000
3/30/2002
3/30/2002
2.42
2.42
2.42
11.16
9.71
2.67
7.66
7.66
11.24
0.46
9.50
10.25
12.50
2.42
7.66
11.24
0.46
13.75
4.83
12.13
2.25
2.42
2.42
14.50
14.50
7.23
7.23
13.38
17.17
13.38
14.08
17.09
15.75
15.75
No Change
No Change
No Change
No Change
No Change
No Change
No Change
No Change
Need to Remodel
No Change
No Change
Need to Remodel
Need to Remodel
No Change
No Change
Need to Remodel
No Change
Need to Remodel
No Change
No Change
No Change
No Change
No Change
No Change
Need to Remodel
No Change
No Change
No Change
No Change
Need to Remodel
No Change
Need to Remodel
No Change
Need to Remodel
20171031182627duong_exploring_e07_grader_h1_apartment.xlsx
Input Area
Complex Cost
Down Payment
# of Pmts per Year
Years
APR
1st Payment Date
$ 1,850,000.00
$ 750,000.00
12
30
4.75%
3/1/2018
Payment #
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Exploring
Summary Calculations
Loan Amount
No. Periods
Monthly Rate
Monthly Payment
Total Interest Paid
Payment Date Beginning Balance
What If
Collect Monthly Rent
# of Units in Complex
PV of Future Rent
Interest Paid
Loan
Principal
Payment
$2,000
8
Ending
Balance
20171031182627duong_exploring_e07_grader_h1_apartment.xlsx
24
Exploring
Loan
20171031182627duong_exploring_e07_grader_h1_apartment.xlsx
Office 2016 – myitlab:grader – Instructions
Excel Project
EX16_XL_CH07_GRADER_CAP_HW – Apartment Complex 1.7
Project Description:
You own five apartment complexes. You created a dataset listing the apartment numbers, apartment complex
names, number of bedrooms, rental price, whether the apartment is occupied or not, and the date the apartment
was last remodeled. You want to insert some functions to perform calculations to help you decide which
apartments need to be remodeled. To focus on the apartments that need to be remodeled, you will use
advanced filtering and database functions for your analysis. Finally, you are considering purchasing a sixth
apartment complex. You will perform some financial calculations and analysis to help you decide if you will
purchase the complex.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
1
2
3
4
5
Points
Possible
Instructions
Open exploring_e07_grader_h1_Apartment.xlsx and save it as
exploring_e07_grader_h1_Apartment_LastFirst.
0
In cell G8 in the Summary worksheet, insert a date function to calculate the number of years
between 1/1/2018 in cell H2 and the last remodel date in the Last Remodel column (cell F8).
Use relative and mixed references correctly. Copy the function to the range G9:G57. Unit 101
was last remodeled 13.75 years ago. Ensure that the function you use displays that result.
In cell H8, insert a nested logical function to display the required pet deposit for each unit. If
the unit has two or more bedrooms (C8) AND was remodeled less than 10 years ago (cell H3),
the deposit is $275 (cell H4); if not, the deposit is $200 (cell H5). Use relative and mixed
references correctly. The pet deposit for Unit 101 is $200.
In cell I8, enter a nested logical function to display Need to Remodel if the apartment is
unoccupied (No) AND was last remodeled more than 10 years ago (H3). For all other
apartments, display No Change. Although Unit 101 was last remodeled over 10 years ago,
the recommendation is No Change because the unit is occupied.
Copy the functions in the range H8:I8 to the range H9:I57.
6
5
5
2
6
In cell B3 insert a nested MATCH function within an INDEX function that will look up the rental
price in column D using the apartment number referenced in cell B2. With 101 entered in cell
B2, the lookup function displays $950.00.
5
7
In the Database sheet, enter conditions in the criteria range for unoccupied two- and threebedroom apartments that need to be remodeled.
6
8
Apply an advanced filter based on the criteria range (A2:H4). Filter the existing database
(range A15:H65) in place. Nine apartments meet the advanced filter conditions.
5
Updated: 10/31/2017
1
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
Excel Project
Points
Possible
Instructions
9
In cell C8, use the DCOUNTA database function to calculate the number of apartments that
need to be remodeled based on the advanced filter you created.
5
10
In cell C9, enter a database function to calculate the total value of monthly rent lost for the
apartments that need to be remodeled based on the advanced filter you created.
5
11
In cell C10, enter a database function to display the date of the apartment that had the oldest
remodel date based on the filtered data. Format the result with Short Date format.
5
12
In the Loan sheet, insert formulas in the range E2:E4 to calculate the loan amount, the
number of payment periods, and the monthly interest rate, respectively. Use cell references in
all formulas.
6
13
In cell E5, enter a financial function to calculate the monthly payment. In cell E6, insert a
financial function to calculate the cumulative total interest paid throughout the loan. Make
sure the results display as positive numbers.
8
14
In cell C11, enter a formula to reference the date stored in cell B7. Insert a nested function in
cell C12 to calculate the date for the next payment. Nest the YEAR, MONTH, and DAY
functions within the DATE function. Add 1 to the month result. Copy the function to the range
C13:C34.
6
15
In cell D11, enter a formula to reference the value stored in cell E2. Insert a formula in cell
D12 that references the ending balance for the previous payment row (G11). Copy the
formula in D12 to the range D13:D34.
5
16
In cell E11, enter a financial function to calculate the interest paid. Copy the formula to the
range E12:E34. The result should be a positive value.
6
17
In cell F11, enter a financial function to calculate the principal payment. Copy the function to
the range F12:F34. The result should be a positive value.
6
18
In cell G11, enter a formula to calculate the ending balance. Copy the formula to the range
G12:G34. Adjust the width of column G, if needed, to display the values. Select the range
D11:G34 and apply Accounting Number Format.
6
19
20
In cell I4 insert a financial function to calculate the present value of the total monthly rent you
will collect for the 8 units for 30 years. Use the number of periods and monthly rate in the
Summary Calculations section and the cell references in the What If section. The result should
be a positive value.
In the Loan sheet, set 0.5″ left and right margins and repeat row 10 on all pages.
Updated: 10/31/2017
2
5
3
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
21
Excel Project
Points
Possible
Instructions
Save and close the workbook, and submit the file as directed.
0
Total Points
Updated: 10/31/2017
3
100
Current_Instruction.docx
…
Purchase answer to see full
attachment

How it works

  1. Paste your instructions in the instructions box. You can also attach an instructions file
  2. Select the writer category, deadline, education level and review the instructions 
  3. Make a payment for the order to be assignment to a writer
  4.  Download the paper after the writer uploads it 

Will the writer plagiarize my essay?

You will get a plagiarism-free paper and you can get an originality report upon request.

Is this service safe?

All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades

Continue to order Get a quote
Place your order
(550 words)

Approximate price: $22

Recent Comments

No comments to show.

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more

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

Get a plagiarism free paper now