Scenario/Summary

Summit Ridge Mountain Resorts has decided to expand. Because of the popularity of the resort among the business community and families, Summit Ridge has decided to purchase some apartments for guests and seasonal employees to have an extended stay. Because these will prove to be a long-term investment, Summit Ridge has decided to put all complexes on a remodeling rotation to ensure the apartments each have a modern and functional style. 

It is now your job to analyze the information regarding the apartments. The owners are looking for some specific information about rentals by apartment size (such as the number of bedrooms per apartment). Based on current housing interest rates, Summit Ridge is considering a sixth apartment complex and has asked you to perform some financial calculations and analyses to determine how adding this new complex to their portfolio enhances the resort’s financial position. They will then use this information to come up with a marketing plan to attract additional customers to the resort.

Remember that you are making a professional analysis and presentation, so be sure to give some thought to what you are trying to recommend to the management of Summit Ridge and why.

Deliverables

After completing the steps below, turn in one Excel 2016 workbook. Rename the workbook with your lastname_first initial_Week5_Lab. xlsx. Example: If your name were Jane Doe, your workbook would be Doe_J_Week5_Lab.xlsx.

Lab 5 – Mountain Statistical AnalysisStepTaskPoints PossiblePoints ReceivedComments1Insert Functions1a – bInsert nested function7  2aCreate a search2a, bInsert nested lookup function4  3Manage Database List3a, cPerform advanced filter and enter database functions4  4Loan Amortization4a, cInsert functions, complete amortization table, and footer8  5Conditional Functions5a, gInsert conditional functions, calculate total and average8  6Statistical Functions6a-dCalculate rank, minimum, and remaining quartile6  6eCalculate the correlation and format the data6  7Using the Analysis of ToolPak7a-cUse Descriptive Analysis, complete input criteria, set out6  7d, eCreate histogram chart, use quartiles and format chart6  Comment: What you learned from completing this Lab5  TOTAL POINTS600 

Week 5 Grading Rubric

Lab Resources

Microsoft Office: Excel 2016

Options for Accessing Microsoft Excel 2016

Lab Steps

Preparation

You will be using Microsoft Excel 2016 for this lab.

Be sure you have read the required chapter materials and reviewed the hands-on exercise videos located on the Lesson page before you begin the lab.

Please do not rely solely on the hands-on exercise videos to complete this week’s lab. The videos provide detailed examples walking you through the hands-on exercises. Applying the hands-on exercise examples will provide both practice and instruction of what to complete.

Begin: Open and Save

Download the spreadsheet  (Links to an external siLinks to an external site. You will be prompted to save the file. Click yes. 

Open the saved file from your Download folder on your computer. 

Note: If you are using the Remote Lab environment, you will need to follow the instructions for uploading the file. These instructions can be found on the Lab page when you click on the Lab icon on Course Resources.

To save the spreadsheet with a new file name,

Step 1: Insert Functions

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.  

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.

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

Database Criteria

Step 4: New Apartment Loan Amortization

Summit Ridge Ski owners want to purchase 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 January 20, 2017. Please consider the loan calculations and build a loan amortization table on the Loan worksheet. Click on the Loan worksheet to begin. 

Loan Details

Step 5: Conditional Functions

In addition to adding the new apartments 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 all employee salaries to see if they have any effect on job satisfaction. Employee satisfaction surveys allow the company to get a pulse for how content employees are. A voluntary survey was administered to a cross-section sample of all employees in the company. This next bit of work will be on the Employee Satisfaction Worksheet.  

Employee Satisfaction

Step 6: Calculate Relative Standing and Central Tendency

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

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. Please create a summary of descriptive statistics using the Analysis ToolPak. The Analysis ToolPak add-in must be active before completing the following.

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.

Descriptive Statistics

Step 8: Create a Documentation Sheet

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

The Minimum RequiEconomics,Economics,,

Get a Unique Answer HERE or Order a Similar Paper

Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.