Computer scienec projet E7
steve_92
e_ch07_expv2_h1.zipE_CH07_EXPV2_H1_Instructions.docx
Office 2013 – myitlab:grader – Instructions Exploring Series Vol. 2
Specialized Functions
Project Description: In the following project, you will use Excel to perform calculations regarding rental properties. You will create a basic search, utilize database functions, and create an amortization table
Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Download and open the file named exploring_e07_grader_h1.xlsx, and then save the file as e07c2Apartment_LastFirst, replacing LastFirst with your name. 0 2 Insert functions in the Pet Deposit column of the Summary worksheet to calculate the required pet deposit for each unit. If the unit has two or more bedrooms and was remodeled after 2008 the deposit is $125, if not it is $75. Hint: On the Summary worksheet, in cell G7, enter =IF(AND(C7>=2,F7>2008),125,75) and then press ENTER. Use the Fill handle to copy the function down through the column. 10 3 Enter nested functions in the Recommendation column to indicate Need to remodel if the apartment is unoccupied and was last remodeled before 2005. For all other apartments, display No change. Hint: In cell H7, enter =IF(AND(E7="no",F7<2005),"Need to remodel","No change") and then press ENTER. Use the Fill handle to copy the function down through the column. 10 4 Type 101 in cell B2. 4 5 Insert a nested lookup function in cell E2 that will look up the rental price in column D using the apartment number referenced in cell B2. Hint: In cell E2, enter =INDEX(A7:H24,MATCH(B2,A7:A24,0),4) and then press ENTER. 10 6 Click the Database worksheet and enter conditions in the Criteria Range for unoccupied two- and three-bedroom apartments that need to be remodeled. Hint: On the Database worksheet, in cell D3, enter 2. In cells F3 and F4, enter No. In cells H3 and H4, enter Need to remodel. In cell D4, enter 3. 10 7 Perform an advanced filter based on the criteria range. Filter the existing database in place. 10 8 In cell C7, enter a DCOUNTA function to calculate the number of apartments to remodel. 6 9 In cell C8, enter a database function to calculate the total lost rent for the month. 2 10 Enter a database function to calculate the year of the oldest remodel in cell C9. 2 11 Click the Loan worksheet and enter 3/20/2015 in cell B7. 2 12 Insert a formula in cell E2 to calculate the loan amount based on the loan parameters in the input area. 2 13 Insert a formula in cell E3 to calculate the total number of periods. 2 14 Insert a formula in cell E4 to calculate the periodic monthly rate. 2 15 Insert a function in cell E5 to calculate the monthly payment. Ensure that the function returns a positive value. 2 16 In cell E6, insert a function to calculate the total interest paid on the loan. Ensure that the function returns a positive value. Hint: In cell E6, enter =-CUMIPMT(E4,E3,E2,1,E3,0) and then press ENTER. 2 17 Complete the loan amortization table for the first five payments only. In cell A11, enter 1. In cell B11, create a relative reference to cell B7 and in cell C11, create a relative reference to cell E2. Use the DATE function to complete the Payment Date column and financial functions for the Interest Paid and Principal Payment columns. In cell F11, enter =C11-E11. In cell C12, create a relative reference to cell F11. Note: Be sure to only complete the table through row 15. Hint: In cell A11, enter 1 and then press TAB. In cell B11, enter =B7 and then press TAB. In cell C11, enter =E2 and then press TAB. In cell D11, enter =IPMT(E$4,A11,E$3,-E$2) and then press TAB. In cell E11, enter =PPMT(E$4,A11,E$3,-E$2) and then press TAB. In cell F11, enter =C11-E11 and then click cell B12. In cell B12, enter =DATE(YEAR(B11),MONTH(B11)+1,DAY(B11)) and then press TAB. In cell C12, enter =F11 and then press ENTER. Use the Fill handle in each column to complete the table. 18 18 Create a footer with the sheet name code in the center, and the file name code on the right side of each worksheet. 6 19 Save the file making sure the worksheets are in the following order: Summary, Database, and Loan. Close Excel. Submit the file as directed. 0 Total Points 100
Updated: 08/03/2013 1 E_CH07_EXPV2_H1_Instructions.docx
exploring_e07_grader_h1.xlsx
Summary
| Search Engine | |||||||
| Unit # | Rental Price | ||||||
| List of Rental Property | |||||||
| Unit # | Apartment Complex | # Bed | Rental Price | Occupied | Last Remodel | Pet Deposit | Recommendation |
| 101 | Rolling Meadows | 1 | $ 750.00 | Yes | 2004 | ||
| 103 | Rolling Meadows | 2 | $ 850.00 | No | 1999 | ||
| 104 | Rolling Meadows | 2 | $ 850.00 | Yes | 2005 | ||
| 105 | Rolling Meadows | 3 | $ 1,000.00 | No | 2007 | ||
| 302 | Lakeview Apartments | 1 | $ 875.00 | No | 2001 | ||
| 303 | Lakeview Apartments | 1 | $ 900.00 | No | 2001 | ||
| 306 | Lakeview Apartments | 2 | $ 1,200.00 | Yes | 2004 | ||
| 406 | Mountaintop View | 3 | $ 1,200.00 | No | 2002 | ||
| 407 | Mountaintop View | 2 | $ 975.00 | Yes | 2002 | ||
| 408 | Mountaintop View | 2 | $ 975.00 | Yes | 2002 | ||
| 501 | Sunset Valley | 1 | $ 550.00 | Yes | 2010 | ||
| 502 | Sunset Valley | 1 | $ 550.00 | No | 2010 | ||
| 503 | Sunset Valley | 1 | $ 550.00 | No | 2003 | ||
| 504 | Sunset Valley | 1 | $ 550.00 | Yes | 2003 | ||
| 505 | Sunset Valley | 2 | $ 700.00 | No | 2003 | ||
| 605 | Oak Tree Living | 3 | $ 1,500.00 | No | 2010 | ||
| 606 | Oak Tree Living | 3 | $ 1,500.00 | Yes | 2004 | ||
| 607 | Oak Tree Living | 3 | $ 1,500.00 | No | 2004 |
Database
| Criteria Range | |||||||
| Unit # | Development | Unit # | # Bed | Rental Price | Occupied | Last Remodel | Recommendation |
| Database Statistics | |||||||
| No. of Apts. to Remodel | |||||||
| Value of Lost Rent | |||||||
| Year of Oldest Remodel | |||||||
| List of Rental Property | |||||||
| Unit # | Development | # Bed | Rental Price | Occupied | Last Remodel | Recommendation | |
| 101 | Rolling Meadows | 1 | 750 | Yes | 2004 | No change | |
| 103 | Rolling Meadows | 2 | 850 | No | 1999 | Need to remodel | |
| 104 | Rolling Meadows | 2 | 850 | Yes | 2005 | No change | |
| 105 | Rolling Meadows | 3 | 1,000 | No | 2007 | No change | |
| 302 | Lakeview Apartments | 1 | 875 | No | 2001 | Need to remodel | |
| 303 | Lakeview Apartments | 1 | 900 | No | 2001 | Need to remodel | |
| 306 | Lakeview Apartments | 2 | 1,200 | Yes | 2004 | No change | |
| 406 | Mountaintop View | 3 | 1,200 | No | 2002 | Need to remodel | |
| 407 | Mountaintop View | 2 | 975 | Yes | 2002 | No change | |
| 408 | Mountaintop View | 2 | 975 | Yes | 2002 | No change | |
| 501 | Sunset Valley | 1 | 550 | Yes | 2010 | No change | |
| 502 | Sunset Valley | 1 | 550 | No | 2010 | No change | |
| 503 | Sunset Valley | 1 | 550 | No | 2003 | Need to remodel | |
| 504 | Sunset Valley | 1 | 550 | Yes | 2003 | No change | |
| 505 | Sunset Valley | 2 | 700 | No | 2003 | Need to remodel | |
| 605 | Oak Tree Living | 3 | 1,500 | No | 2010 | No change | |
| 606 | Oak Tree Living | 3 | 1,500 | Yes | 2004 | No change | |
| 607 | Oak Tree Living | 3 | 1,500 | No | 2004 | Need to remodel |
Loan
| Input Area | Summary Calculations | ||||
| Complex Cost | $ 850,000.00 | Loan Amount | |||
| Down Payment | $ 375,000.00 | No. Periods | |||
| # of Pmts per Year | 12 | Monthly Rate | |||
| Years | 30 | Monthly Payment | |||
| APR | 5.75% | Total Interest Paid | |||
| 1st Payment Date | |||||
| Payment # | Payment Date | Beginning Balance | Interest Paid | Principal Payment | Ending Balance |
