Question

please post with pictures of step by step solution

oject Description: u own five apartment complexes. You created a dataset listing the apartment numbers, apartment complex mes, as last remodeled. You want artments need to number of bedrooms, rental price, whether the apartment is occupied or not, and the date the apartment to insert some functions to perform calculations to help you decide which be remodeled. To focus on the apartments that need to be remodeled, you will use vanced fitering and database functions for your analysis. Finaly, you are considering purchasing a artment complex. You will perform rchase the complex. some financial calculations and analysis to help you decide if you will structions: r the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Open exploring e07 orader h1 Apartment.xisx and save it as exploring e07 grader h1 Apartment LastFirst. 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). 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. F 2 Use relative and mibxed references correctly. Copy the function to the range G9:G57. Unit 101 the unit has two or more bedrooms (C8) AND was remodeled less than 10 years ago (cell H3), 3 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 18, 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 4 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:18 to the range H9:157 In cel 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 6 82, the lookup funiction displays $950.00 In the Database sheet, enter conditions in the criteria range for unoccupied two- and three- bedroom apartments that need to be remodeled. Enter criteria as text only, without use of quotation marks. Be sure to enter the criteria separately in rows 3 and 4. Apply an advanced fiter based on the criteria range (A2:H4). Fiter the existing database 8 (range A15:H65) in place. Nine apartments meet the advanced filter conditions. Step In cell C8, use the DCOUNTA database function to calculate the number of apartments that Points 9need to be remodeled based on the advanced filter you created. In cell C9, enter a database function to calculate the total value of monthly rent lost for the 10 apartments that need to be remodeled based on the advanced filter you created. In cell C10, enter a database function to display the date of the apartment that had the oldest 11remodel date based on the filtered data. Format the result with Short Date format. 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 referencesin 12 all formulas. In cell ES, enter a financiall function to calculate the monthly payment. In cell E6, insert a financial function to calculate the cumulative total interest paid throughout the loan. Make 13 sure the results display as positive numbers. 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 14 functions within the DATE function. Add 1 to the month result. Copy the function to the range6 C13:C34 In ceil D11, enter a formula to reference the value stored In cell 2. Insart a formula in cel D12 that references the ending balance for the previous payment row (GID, Copy the 15 formula in D12 to the range D13:D34 In cell E11, enter a financial function to calculate the interest paid. Copy the formula to the 16 range E12:E34. The result should be a positive value. In cell F11, enter a financial function to calculate the principal payment. Copy the function to 17 the range F12:F34. The result should be a positive value. In cell G!1, enter a formula to calculate the ending balance, Copy the formula to the range G12:G34, Adust the width of column G r needed, to display the values Select then e D11:G34 and apply Accounting Number Format. Incelǐ4 insert a financial function to calculate the present value of the total mothlynntyou the What If section. The result should will collect for the 8 units for 30 years. Use the number of penods and monthy rate in the Summary Calculations section and the cell references in the What If section be a positive value. In the Loan sheet, set 0.5" loft and right margins and repeat row 1

Answer 1

YEARFRAC(F8,SH$2) Insert Fünctio Search Engine Constants for Formulas 101 Enter Unit # to see rent Rental Price Comparison Date No. of Years Since Remodel Pet Deposit 2 bedroom newer Pet Deposit other 01/01/2018 10 $275 Years Since Unit # 101 102 103 104 105 106 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 #Bed Rental Price Last Remodel 01/04/2004 01/03/2013 $ 1,100 No15/11/2005 01/10/2015 01/10/2015 15/06/2004 01/11/2015 Occupied Remodel Pet Deposit Recommendation 1 950 Yes 1 50 Yes $200 No Change 4.83 $200 No Change 12.13 $200 Need to Remodel 2 1,100Yes 3 1,500 No 3 $ ,500 Yes 2 $1,100 No 2 1,10 Yes 3 1,500 No 1,500 Yes 8Yes 2.25 $275 No Change 2.25 $275 No Change 13.54$200 No Change 2.17 $275 No Change 8.38 $275 No Change 8.38 $275 No Change 11.24 $200 No Change 1.72 $200 No Change No Change 108 109 110 201 15 15/08/2009 04/10/2006 13/04/2016 1

X YEARFRAC(F8,SH$2) Function Arguments H YEARFRAC T38078 个1-43101 Start date F8 Search Engine Constants for Formulas 101 01/01 End date SHS2 Enter Unit # to see rent Comparison Date No. of Years Since Remodel Pet Deposit 2 bedroom newer Rental Price any 13.75 Returns the year fraction representing the number of whole days bet een start date and end date. Start date is a serial date number that r resents the start date Years Since Remodel $2) Unit # Apartment Complex Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows #Bed Rental Price 950 950 Occupied Yes Yes Last Remodel Pet Dep 102 01/03/2013 4.83 $200 Formula result - 12.13 $200 2.25 $2 2.25$275 13.54$200 No Change 2.17 $275 No Change 8.38 $275 No Change 8.38 $275 No Change 11.24 S200 No Change 1.72 $200 No Change 13.75 1,100 No15/11/200 103 OK 1,100 Yes 01/10/2015 1,500 No 01/10/2015 15/06/2004 1,100 No 01/11/2015 15/08/2009 ●5/08/2009 04/10/2006 105 o Change 106 1,500 Yes 2 108 109 110 201 2 1,100 Yes 3 1,500 No 3 1,500 Yes 875 Yes 3

0 Search Engine Constants for Formulas Enter Unit # to see rent Comparison Date No. of Years Since Remodel Pet Deposit 2 bedroom n Pet De 018 10 275 101 Rental Price Years Since Unit # 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 Mea # Bed Rental Price Occupied Last Remodel Remodel Pet 01/03/2013 15/11/2009 01/10/20195 01/10/20195 15/06/2004 01/11/20195 15/08/2009 15/08/2009 04/10/2006 13/04/2016 AND(logicall, [logical2], [logical31 12.13$200 Need to Remodel 2 1,100 No 2 1,100 Yes 3 $,5 No 3 1,500 Yes 2 1,100 No 2 1,100 Yes 3 $,5 No 3 1,500 Yes Yes 2.25 $275 No Change 2.25 $275 No Change 13.54 $200 No Change 2.17 $275 No Change 8.38 $275 No Change 8.38 $275 No Change 11.24 $200 No Change 1.72 $200 No Change 107 108 110 875 Summary Database Loan

1 3 INDEX Function Arguments CP Search Engine Constants for Forn Logical-test AND(CE>-2,G3cSHS3ǐ Value_if true SH$4 Value_if false SH$5 1FALSE -275 -200 -200 2 Enter Unit # to see rent 3 Rental Price Comparison Date No. of Years Since Remodel Pet Deposit 2 bedroom newer Checks whether a condition is met, and retuns one value it TRUE, and another value if FALSE Logical_test is any value or expression that can be evaluated to TRUE or FALSE Years Since Unit # Apartment Complex Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows #Bed Last Remodel 50 Yes 01/04/2004 1 $ 950 Yes 01/03/2013 15/11/2005 Rental Price Occupied Remodel 13.75 4.83 Formula Formula result = $200 102 103 10 2 $1,100 No OK 1,100 Yes01/0/2015 12 13 14 15 16 17 18 I 105 106 107 108 2.25 $275 No Change 13.54$200 No Change 2.17 $275 No Change 8.38 $275 No Change 8.38 $275 No Change 11.24 $200 No Change 1.72 $200 No Change 4.83 S200 No Change 15/06/2004 01/11/20195 2 1,100 No 3 1,500No 15/08/2009 110 201 $1,500 Yes 04/10/2006 13/04/2016 875 Yes Summary Database Loan Edit 嚣ロ凹-

INDEX v- ,C :F(AND(E8-"NO",G8>10),"Need to Remodel-,"No Change") Search Engine Constants for Formulas 101 2 Enter Unit # to see rent 3 Rental Price Comparison Date No. of Years Since Remodel Pet Deposit 2 bedroom newer Pet De 01/01/2018 10 $275 Years Since Apartment Complex Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows Rolling Meadows #Bed Rental Price Occupied Last Remodel 01/04/2004 01/03/2013 15/11/2009 01/10/20195 01/10/20195 15/06/2004 01/11/20195 15/08/2009 15/08/2009 04/10/2006 13/04/2016 2 Unit # Remodel Pet Deposit Recommendati 13.75$200 IF(AND E8-"NO,G810,"Need to Remodel","No Change") 4.83 $200 12.13 $200 Need to Remodel IF(logical test, [value_if truel [value if false) 2 1,100 No 2 $ 1,100 Yes 3 1,500 No 3 1,500 Yes 2 1,100No 2$ ,1Yes 3 1,500 No 3 1,500 Yes 10 12 13 14 15 16 75 No Change 2.25 $275 No Change 13.54 $200 No Change 75 No Change 8.38 $275 No Change No Change No Change 1.72 $200 No Change 8.38 $275 110 18 875 Summary Database Loan Edit 嚣ロ凹-

INDEX INDEXIAS8 D$57,MATCH(B2,A8:A57,0),4) Search Engine Constants for Formulas 01/01/2018 10 $275 2 Enter Unit # to see rent Comparison Date No. of Years Since Remodel Pet Deposit 2 bedroom newer 101 ] 3 |Rental Price -INDEX(A :D$57,MATCH(B2,A8:A57,0),4 Years Since Unit # 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 Me #Bed Rental Price Last Remodel 01/04/2004 01/03/2013 No 15/11/2005 01/10/20195 1,500 No 01/10/2015 15/06/2004 01/11/20195 1,100 Yes 15/08/2009 15/08/2009 04/10/2006 875 Yes 13/04/2016 Occupied Remodel Pet Deposit Recommendation 950 Yes 950 Yes 13.75 $200 No Change 4.83 $200 No Change 12.13 $200 Need to Remodel 2.25 $275 No Change 2.25 $275 No Change 13.54 $200 No Change 2.17 $275 No Change 8.38 $275 No Change 8.38 $275 No Change 11.24 $200 No Change 1.72 200 No Change 4.83 102 2 S,100 2 $ 1,100Yes 3 $ 3 $ 1,500Yes 2 S,100 No 2 S 3 $ 1,500No 3 ,500 Yes it 104 12 13 14 15 16 17 18 | 106 107 108 109 110 201 Summary Database Loarn Edit 嚣ロ凹

-INDEX(A$8 D$57,MATCH B2,A8:A57,0),A) Function Arguments Array AS8 DS57 Row_num MATCHB2 A8:A57,0 个丨 {101,-Rolling Meadows,, 1,950, 102," = Column_num 4 3 $1,500 -950 Returns a value or reterence of the cell at the intersection of a particular row and column, in a given range Array is a range of cells or an array constant. Cancel 7.75 $275 No Change 6.38 $275 No Change 12.50 $200 Need to Remodel 2.42 $275 No Change 2.42 $275 No Change Yes 3 0 Yes 11.16 $200 No Change Summary Database Loan Edit 100%

Criteria Range Years Since Unit # Apartment Complex #Bed Rental Price Occupied Last Remodel Remodel Need to Remodel Need to Remodel No No Database Statistics No. of Apts. to Remodel Value of Lost Rent $ 12,050 10 12 List of Rental Property 13 14 Years Since Unit # Apartment Complex #Bed Rental Price Occupied Last Remodel Remodel Recommendation Need to Remode Summary Database Loan Ready Filter Mode + 100%

Advanced Filter? Years Since Remodel Action Unit # Apartment Complex #Bed Rental Price occupied No Last Remodel Recommendation Need to Remodel Need to Remodel O Eilter the list in-place O Copy to another location List range SAS15:SHS6 Criteria range: SAS2:SHS4 Copy to Database Statistics Unique records only No. of Apts.to Remodel Value of Lost Rent Year of Oldest Remodel $ 12,050 OK Cancel 10 12 13 14 List of Rental Property Years Since Unit # Apartment Complex Rolling Meadows Rolling Meadows #Bed Rental Price occupied Last Remodel Remodel Recommendation 12.13 Need to Remodel 12.50 Need to Remodel 15 1,100 No 1,00No 01/07/2005 Summary Database Loan Point Filter Mode Average: 2.5 Count 14 Sum:

INDEX -DCOUNTA(A15:H65,H2,Criteria) Criteria Range Years Since Remodel Unit # Apartment Complex #Bed Rental Price 0ccupied Last Remodel Recommendation Need to Remodel Need to Remodel No Database Statistics No, ofApts. =DCOUNTA(A15: Criteria) Value of Lost Ren DCOUNTA (database, field, criteria) 10 12 13 14 List of Rental Property Years Since Unit # #Bed Rental Price Occupied Last Remodel Remodel Recommendation lling Me Summary Database Lo oan Edit Filter Mode 嚣ロ凹-

C8 丘 1 :0COUNTA(A15:H65,H2,Criteria) Function Arguments G DCOUNTA No. of Apts. to Remodel 2,Criteri Value of Lost Rent Unit , Apartment Complex, B.. Database A15: H65 Field H2 Criteria Criteria $12,050 尤·#VALUE! 12 Counts nonblank cells in the field (column) of records in the database that match the conditions you specity. List of Rental Property E 13 14 Database is the range of cells that makes up the list or database. A database is a list of related data. HI Years Unit # 103 Apartment Complex Rolling Meadows Rolling Meadows Lakeview Apartments Lakeview Apartments Mountaintop view Sunset Valley Oak Tree Living Oak Tree Living Oak Tree Living 15 #Bed Rental Price Occupied Last Remodel Remo Formula results 9 Heln on this function OK 1,050 No 01/07/2005 1,200 No 1,200 No 1,200 No 01/04/2004 1,100 No 02/07/2003 1,200 No 2,000 2,000 No 30/03/2002 305 308 30/09/2007 10.25 Need to Remodel 13.75 Need to Remodel 14.50 Need to Remodel 13.38 Need to Remodel 17.09 Need to Remodel 15.75 Need to Remodel 43 56 505 15/08/2004 30/11/2000 63 605 No 65 Summary bse Loan Edit ier Mode

丘 1100 Database Statistics No. of Apts. to Remod Value of Lost Rent Year of Oldest Remod $ 12,050 10 I 12 13 14 List of Rental Property Years Since Unit # Apartment Comple:x Rolling Meadows Rolling Meadows Lakeview Apartments Lakeview Apartments Mountaintop Vievw Sunset Valley Oak Tree Living Oak Tree Living #Bed Rental Price Occupied Last Remodel 15/11/2005 01/07/2005 04/10/2006 Remodel Recommendation Need to Remodel Need to Remodel Need to Remodel Need to Remodel Need to Remodel Need to Remodel Need to Remodel Need to Remodel 18 1,100 12.13 12.50 11.24 10.25 13.75 14.50 13.38 17.09 1,050No 40 43 308 1,100 02/07/2003 63 SummaDstabass Loan Ready Filter Mode Average: 1,339 Count 9 :2,05 E 13凹 + 100%

INDEX DSUM(A15:H65,D15,Criteria) Database Statistics No. of Apts. to Remodel Value of Lost Rent Year of Oldest Remodel DSUM database, field, criteria) -DSUMIA15:H65,D15,Criterial 10 画| 12 13 List of Rental Property 14 Years Since Remodel Unit # # Bed Rental Price 1,100 1,050 occupied Last Remodel Apartment Complex Rolling Meadows Rolling Meadows Lakeview Apartments Lakeview Apartments Mountaintop Vievw Sunset Valley Oak Tree Living Oak Tree Living 12.13 12.50 11.24 10.25 13.75 14.50 13.38 17.09 Recommendation Need to Remodel Need to Remodel Need to Remodel Need to Remodel Need to Remodel Need to Remodel Need to Remodel Need to Remodel 01/07/2005 04/10/2006 308 01/04/2004 02/07/2003 1,100 603 Summary Database Lo Edit Filter Mode 嚣ロ凹-

INDEX DMAx(A15:H65,G15,Criteria) Database Statistics No. of Apts. to Remodel Value of Lost Rent Year of Old DMAX(A15:H65,G15,Criteria) S 12,050 10 12 13 DMAX(database, field, criteria) List of Rental Property Years Since Unit # #Bed Rental Price Occupied Last Remo Apartment Complex Rolling Meadows Rolling Meadows Lakeview Apartments Lakeview Apartments Mountaintop Vievw Sunset Valley Oak Tree Living Oak Tree Living Remodel mmendation 12.13 Ned to Remodel 12.50 Need to Remodel 11.24 Ned to Remodel 10.25 Ned to Remodel 13.75 Ned to Remodel 14.50 Ned to Remodel 13.38 Ned to Remodel 17.09 Ned to Remodel 1,100 1,050 01/07/200 04/10/200 43 308 406 1,100 02/07/200 603 Summary Database Loan Edit Filter Mode 嚣ロ凹-

C10 x vDMAX(A15:H65,G15,Criteria) Function Arguments i3 Database A15:H65 -runit ,'Apartment Complex,'ウB." Value of Lost Rent Year of Oldest Remodel 5,Criteria Field G15 Years Since Remodel 10 Criteria Crteria VALUE! 12 Returns the largest number in the field (column) of records in the database that match the conditions you 13 List of Rental Property Database is the range of cells that makes up the list or database. A database is a list of related data. Years 15 Unit # Apartment Complex Rolling Meadows Rolling Meadows Lakeview Apartments Lakeview Apartments Mountaintop Vieww Sunset Valley Oak Tree Living Oak Tree Living Oak Tree Living #Bed Rental Price occupied Last Remodel Remo 15/11/2005 01/07/2005 103 1,100No 1,050No 1,200 No 04/10/2006 1,200 No 1,200No 1,100 No 02/07/2003 1,200 No ,000No 2,000 No 30/03/2002 OK 31 305 308 Need to Remodel Need to Remodel Need to Remodel Need to Remodel Need to Remodel Need to Remodel 43 10.25 13.75 14.50 13.38 17.09 15.75 01/04/2004 56 505 603 63 30/11/2000 65 607 Summary Database Loan Edit Filter Mode 嚣ロ凹-

Clipboard Font Alignment Format Cells Number AlignmentBorder Fill Protection C10 x DMAX(A15:H65,G15,Criteria) General 17-1-00 Currency Accounting Database Statistics Percentage No. of Apts. to Remodel Value of Lost Rent Year of Oldest Remodel17-01-00 12,050 Text ,"#0.00-k-l" 1+."#0.00);-(" ??上_c@」 10 12 13 rs.. #.##0.):.rs."R#a0k.rs.. ??.);-@j [S-en-IN dd mmmm ywy [S-en-US]h:mm:ss AM/PM sx-sysdate]dddd, mmmm dd, ww List of Rental Property 14 Years Since Unit # Type the number format code, using one of the existing codes as a starting point. Apartment Comple:x Rolling Meadows Rolling Meadows Lakeview Apartments Lakeview Apartments Mountaintop Viev Sunset Valley Oak Tree Living Oak Tree Living # Bed Last Remodel Rental Price 1,100 Occupied Remodel 12.13 12.50 11.24 10.25 13.75 14.50 Need to Remodel 13.38 Need to Remodel 17.09 Need to Remodel 01/07/2005 04/10/2006 305 308 406 OK 1,100 02/07/2003 603 30/11/2000 Summary Database Loan Ready Filter Mode + 100%