Step 1: Insert Functions

1. Select the Search worksheet and then the Pet Deposit column. Create a formula to determine the required pet deposit for each unit. If the unit has two or more bedrooms and was remodeled after 2006, the deposit is $150; if not, it is $100.
2. The Recommendation column needs a nested function to indicate the remodeling status. If the apartment is unoccupied and has not been remodeled before 2006, then display "Please remodel" in the Recommendation column. Display " ~ No Change" for apartments that do not meet the former criteria.
3. Make sure each field has the appropriate professional formatting for titles, headers, currency, percent, and so forth. Your worksheets need to be readable, clean, and professional. Please let spell-checkwork for you; use this feature to check for spelling errors.

Click Image to Expand

Step 2: Quick Search

Now that all of the rental properties are listed and organized, the owners would like to be able to search through the apartment numbers and return the price of the apartment number listed.

1. Insert number 1301 in cell B3. B3 is the cell that will be used to research apartment unit prices.
2. Create a nested lookup function in cell E3. Look up the rental price in column D using the apartment unit number in cell B3. (Use the INDEX function.)
3. Make sure each field has the appropriate professional formatting for titles, headers, currency, percent, and so forth. Your worksheets need to be readable, clean, and professional. Please let spell-check work for you; use this feature to check for spelling errors.

Click Image to Expand

Step 3:Database Functions

The Database tab of the workbook contains a duplicate listing of all of the apartments listed as rental properties. The owners are looking for you to help them filter through all of this data to come up with specific pieces of information. They would like to have answers to the following questions.

• How many two- and three-bedroom apartments need to be remodeled?
• What is the value of lost rent income?
• What is the year of the oldest remodel?

You know you will need to perform an advanced filter and use some database functions to address these interests. Select theDatabase worksheet to begin.

1. Enter conditions in the Specify Criteria area for unoccupied two- and three-bedroom apartments that need to be remodeled. Be very careful with data entry and spelling.
2. Select cells A2:H4 and give it the Range Name Criteria.
3. Insert database functions to determine the database statistics in the range D8:D10.
4. Complete an advanced filter based on the specify criteria range; filter the database in place.
5. Put the three Rs into practice. Adjust all titles and columns for proper spacing. Double check data formats and consider grid lines.

Database Criteria

Step 4:New Apartment Loan Amortization

Summit Ridge Ski owners want topurchase a sixth apartment complex. This decision is under review. Here are the details of their offer. The loan amount is $950,000 with a down payment of $400,000 for 30 years at 5.325%, with the first payment due on January20, 2017. Please consider the loancalculations and build a loan amortization table on the Loan worksheet. Click on the Loan worksheet to begin.

1. Enter the loan details provided above in the Input Area and place formulas to create allcalculations in the Summary Calculations. The loan payment is at the end of the period.
2. Createa loan amortization table. ThePayment Date column needs a date function. The Interest Paid and Principal Payment columns require financial functions.
3. Create a custom footer with your name on the left side, the page and page numberin the center, and your professor's nameon the right side of each worksheet. Make sure you put the page back to normal view after you insert the footer at the bottom.

Loan Details

Step 5:Conditional Functions

In addition to adding the newapartments to their financial portfolios, the owners of Summit Ridge Ski Resort would like to ensure that they are paying their employees at market value and want to continue to encourage employees to stay with the company. Another phase of your project is to research allemployeesalaries to see if they have any effect on job satisfaction. Employee satisfaction surveys allow the company to get a pulse for how contentemployees are. Avoluntary survey was administered to a cross-sectionsample of all employees inthe company. This next bit of work will be on the Employee Satisfaction Worksheet.

1. Calculate the average job satisfaction for Administrative Assistant in cell I5. Format the results with the number format and two decimal positions.
2. Use the fill handle fromcell I5 to copy the function down through the range I6:I11. Make certain to consider theappropriate mixed and/or absolute cell referencing.
3. Calculate the average salary of all Administrative Assistantsand place the result in cell J5.
4. Use the fill handle from cell J5 to copy the function down through the range J6:J11. Make certain to consider the appropriate mixed and/or absolute cell referencing.
5. Calculate the number of Sales and Marketing Directors in cell I14 that have a job satisfaction level of 4 orabove.
6. Calculate the average salary of Sales and Marketing Directors in cell I15 that have a job satisfaction level of 4 orabove.
7. Use a processlike that demonstratedin stepsEand Fto calculate the total number and the average salary of Managers that have a job satisfaction of 4 or greater.

Employee Satisfaction

Step 6:Calculate Relative Standing and Central Tendency

Please continue your analysis andcalculate both the salary rankings and thesalary quartile thresholds.

1. Insertafunction in cell F4 to determinethe rank of the salary in cell D4 against the range of all salaries.
2. Copy the Salary Rank function down column F. Make certain to consider the appropriate mixed and/or absolute cell referencing.
3. Calculate the minimum quartile value in the list of salaries. Place your quartile function in cell I22.
4. Copythe remaining quartile values in range I23:I26.Make certain to consider the appropriate mixed and/or absolute cell referencing.
5. Insert a function in cell H29 to determine the strength of the relationship between satisfaction and salary.Format the correlation of columns D and E as number format with two decimal positions.

Statistical Functions

Step 7:Using the Analysis ToolPak

You are almost finished with your analysis. Pleasecreate a summary of descriptive statistics using the Analysis ToolPak. TheAnalysis ToolPak add-inmust be active before completingthe following.

1. Go tothe Data tab and select Data Analysis. Select Descriptive Statistics, and click OK.
2. Insert the input rangeusing the salary data in column D.
3. Select the new worksheet output optionto display on a new worksheet namedDescriptive Statistics. Put a check in the boxto the left of Summary statistics. Click OK.
4. Create a histogram with the Analysis ToolPak. The salary column D will be the input range and the quartile range I22:I25 will be the bin range. Position the Output range in cell H31. Make sure the Chart output is checked. Click OK.
5. Format the worksheet professionally.

Descriptive Statistics

Step 8:Create a Documentation Sheet

Clean up the formatting of your Excel workbook, taking into account professional appearance.

The Minimum Requirement (per the Grading Rubric)

1. Insert a new spreadsheet into the workbook. The Documentation sheet should be the first sheet in the workbook.
2. Make certain each tab has adescriptive name and color for each tab (sheet) in the workbook.
3. Create the professional documentation worksheet. Be sure to include a description of each worksheet. An image is provided below.

Week 5 Documentation Sheet

Transcript

Finish and Submit

Stay a While Longer - Apartments Search Unit # Unit # 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1301 1302 1303 1304 1305 1306 1307 1308 1401 1402 1403 1404 1405 1406 1407 1408 1501 1502 1503 1504 1505 1506 Rental Price Apartment Complex # Bed Turning Leaf Circle 1 Turning Leaf Circle 1 Turning Leaf Circle 2 Turning Leaf Circle 2 Turning Leaf Circle 3 Turning Leaf Circle 3 Turning Leaf Circle 2 Turning Leaf Circle 2 Turning Leaf Circle 3 Turning Leaf Circle 3 Turning Leaf Circle 1 Turning Leaf Circle 1 Turning Leaf Circle 2 Turning Leaf Circle 2 Turning Leaf Circle 3 Turning Leaf Circle 3 Turning Leaf Circle 2 Turning Leaf Circle 2 Turning Leaf Circle 3 Turning Leaf Circle 3 Piedra Springs 1 Piedra Springs 1 Piedra Springs 1 Piedra Springs 1 Piedra Springs 2 Piedra Springs 2 Piedra Springs 2 Piedra Springs 2 Eagles Domain Road 1 Eagles Domain Road 2 Eagles Domain Road 3 Eagles Domain Road 1 Eagles Domain Road 2 Eagles Domain Road 3 Eagles Domain Road 2 Eagles Domain Road 2 Hickory Ridge Villas 1 Hickory Ridge Villas 1 Hickory Ridge Villas 1 Hickory Ridge Villas 1 Hickory Ridge Villas 2 Hickory Ridge Villas 2 Rental Price Occupied $ 765.00 Yes $ 765.00 Yes $ 895.00 No $ 895.00 Yes $ 1,225.00 No $ 1,225.00 Yes $ 895.00 No $ 895.00 Yes $ 1,225.00 No $ 1,225.00 Yes $ 775.00 Yes $ 775.00 Yes $ 875.00 Yes $ 875.00 No $ 1,050.00 Yes $ 1,050.00 No $ 875.00 Yes $ 875.00 Yes $ 1,050.00 No $ 1,050.00 Yes $ 875.00 No $ 875.00 No $ 900.00 No $ 900.00 Yes $ 1,355.00 No $ 1,355.00 Yes $ 1,355.00 Yes $ 1,355.00 No $ 875.00 No $ 950.00 No $ 1,355.00 Yes $ 875.00 No $ 950.00 Yes $ 1,355.00 No $ 975.00 Yes $ 975.00 Yes $ 550.00 Yes $ 550.00 No $ 550.00 No $ 550.00 Yes $ 850.00 No $ 850.00 Yes Last Remodel 2005 2003 1999 2005 2007 2005 2005 2009 2008 2009 2011 2010 2011 2011 2005 2005 2000 2000 2005 2005 2008 2001 2001 2001 2005 2005 2011 2011 2005 2011 2011 2011 2011 2002 2002 2002 2011 2011 2003 2001 2003 2011 Pet Deposit 1507 1601 1602 1603 1604 1605 1606 1607 Hickory Ridge Villas Cypress Trail Cypress Trail Cypress Trail Cypress Trail Cypress Trail Cypress Trail Cypress Trail 2 1 1 2 2 3 3 3 $ $ $ $ $ $ $ $ 850.00 1,225.00 1,225.00 1,355.00 1,355.00 1,675.00 1,695.00 1,710.00 No Yes Yes No Yes No Yes No 2011 2005 2005 2005 2011 2011 2005 2005 Recommendation Criteria Range Unit # Development # Bed Rental Price Occupied Database Statistics No. of Apts. to Remodel Value of Lost Rent Year of Oldest Remodel Advanced Filter based on Criteria Range Unit # Development 1101 Turning Leaf Circle 1102 Turning Leaf Circle 1103 Turning Leaf Circle 1104 Turning Leaf Circle 1105 Turning Leaf Circle 1106 Turning Leaf Circle 1107 Turning Leaf Circle 1108 Turning Leaf Circle 1109 Turning Leaf Circle 1110 Turning Leaf Circle 1201 Turning Leaf Circle 1202 Turning Leaf Circle 1203 Turning Leaf Circle 1204 Turning Leaf Circle 1205 Turning Leaf Circle 1206 Turning Leaf Circle 1207 Turning Leaf Circle 1208 Turning Leaf Circle 1209 Turning Leaf Circle 1210 Turning Leaf Circle 1301 Piedra Springs 1302 Piedra Springs 1303 Piedra Springs 1304 Piedra Springs 1305 Piedra Springs 1306 Piedra Springs 1307 Piedra Springs 1308 Piedra Springs 1401 Eagles Domain Road 1402 Eagles Domain Road 1403 Eagles Domain Road 1404 Eagles Domain Road 1405 Eagles Domain Road # Bed Rental Price Occupied 1 765 Yes 1 765 Yes 2 895 No 2 895 Yes 3 1225 No 3 1225 Yes 2 895 No 2 895 Yes 3 1225 No 3 1225 Yes 1 775 Yes 1 775 Yes 2 875 Yes 2 875 No 3 1050 Yes 3 1050 No 2 875 Yes 2 875 Yes 3 1050 No 3 1050 Yes 1 875 No 1 875 No 1 900 No 1 900 Yes 2 1355 No 2 1355 Yes 2 1355 Yes 2 1355 No 1 875 No 2 950 No 3 1355 Yes 1 875 No 2 950 Yes 1406 Eagles Domain Road 1407 Eagles Domain Road 1408 Eagles Domain Road 1501 Hickory Ridge Villas 1502 Hickory Ridge Villas 1503 Hickory Ridge Villas 1504 Hickory Ridge Villas 1505 Hickory Ridge Villas 1506 Hickory Ridge Villas 1507 Hickory Ridge Villas 1601 Cypress Trail 1602 Cypress Trail 1603 Cypress Trail 1604 Cypress Trail 1605 Cypress Trail 1606 Cypress Trail 1607 Cypress Trail 3 2 2 1 1 1 1 2 2 2 1 1 2 2 3 3 3 1355 No 975 Yes 975 Yes 550 Yes 550 No 550 No 550 Yes 850 No 850 Yes 850 No 1225 Yes 1225 Yes 1355 No 1355 Yes 1675 No 1695 Yes 1710 No Last Remodel Pet Depost Recommendation Last Remodel Pet Deposit 2005 2003 1999 2005 2007 2005 2005 2009 2008 2009 2011 2010 2011 2011 2005 2005 2000 2000 2005 2005 2008 2001 2001 2001 2005 2005 2011 2011 2005 2011 2011 2011 2011 Recommendation 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2002 2002 2002 2011 2011 2003 2001 2003 2011 2011 2005 2005 2005 2011 2011 2005 2005 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Input Area Complex Cost Down Payment # of Pmts per Year Years APR 1st Payment Date Payment # Summary Calculations Loan Amount No. Periods Monthly Rate Monthly Payment Total Interest Paid Payment Date Beginning Balance Interest Paid Principal Payment Ending Balance Employee Statistics ID Number Position 10474 Key Account Manager 11025 Sales Representative 11062 Key Account Manager 11653 Administrative Assistant 14731 Key Account Manager 14869 Support Specialist 15039 Support Specialist 15190 Sales Representative 16015 Sales Marketing Director 16034 Budget Manager 16754 Sales Representative 18283 Support Specialist 22923 Key Account Manager 24168 Sales Marketing Director 25280 Manager 27427 Budget Manager 30047 Administrative Assistant 30836 Sales Marketing Director 31615 Administrative Assistant 33143 Key Account Manager 34737 Support Specialist 36528 Key Account Manager 37828 Sales Representative 38088 Support Specialist 38180 Sales Representative 39483 Support Specialist 39692 Budget Manager 42434 Support Specialist 42694 Budget Manager 43987 Key Account Manager 45418 Manager 45849 Support Specialist 46521 Budget Manager 47711 Manager 49542 Sales Marketing Director Salary Job Satisfaction 35855 41708 47572 30176 45883 32650 32750 45670 89900 55389 49617 35230 44854 77846 70125 62263 30982 84937 32709 45983 33501 48706 45927 31632 49575 37404 65492 35316 68365 41130 72862 30701 66009 74767 97159 Salary Rank 3 2 3 4 4 1 3 5 4 2 3 3 2 3 3 4 2 4 4 5 1 5 2 5 3 4 3 2 2 4 3 5 2 4 5 Summary Information Position Administrative Assistant Budget Manager Sales Marketing Director Key Account Manager Manager Sales Representative Support Specialist Average Satisfaction Sales Marketing Directors with = 4 Satisfaction Count Average Salary Managers with = 4 Satisfaction Count Average Salary Quartile 1 2 3 4 Correlation Average Salary