CGS 2100 Valencia College Lab Wk5 Apartments Acquisition Analysis Worksheet Lab Overview Scenario/Summary Summit Ridge Mountain Resorts has decided to exp

CGS 2100 Valencia College Lab Wk5 Apartments Acquisition Analysis Worksheet Lab Overview
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.

Don't use plagiarized sources. Get Your Custom Essay on
CGS 2100 Valencia College Lab Wk5 Apartments Acquisition Analysis Worksheet Lab Overview Scenario/Summary Summit Ridge Mountain Resorts has decided to exp
For $10/Page 0nly
Order Essay

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.

WEEK 5 GRADING RUBRIC
Lab 5 – Mountain Statistical Analysis
Step Task Points Possible Points Received Comments
1 Insert Functions
1a Insert nested function for pet deposit 5
1b Insert nested function for Remodeling Status 5
1c Apply professional formatting to data, headers, etc. 3
2a Create a search
2a, b Use the INDEX function to give the rental price based on unit # 5
3 Loan Amortization
3a Complete Summary Calculations based on data provided in Input area 5
3a Calculate Monthly Payment and Total Interest over the life of the loan 3
3B In the loan amortization table., Use a date function for the Payment Date and financial functions for Interest and principle 4
3b Calculate the ending and beginning balance and then copy the rows down to complete at least 10 months of the amortization schedule 5
4 Conditional Functions
4a,b On the Employee Satisfaction worksheet, calculate the average job satisfaction for each role in the company 6
4c,d Calculate the Average salary for all employee types 6
4e Calculate Number and Average salary for Sales & Marketing Directors with >=4 Satisfaction 6
4g Apply Count and average salary to Managers 6
4h Create a Pivot Table based on employee role to compare satisfaction and salary averages 8
5 Documentation
5a,c Add and complete a Documentation sheet with all required information, including sheet descriptions 4
5b Ensure tabs have descriptive names and distinct colors 4
Comment: What you learned from completing this Lab 5
TOTAL POINTS 80 0

Lab Resources
MICROSOFT OFFICE: EXCEL 2016

Options for Accessing Microsoft Excel 2016

Use a personal copy on your PC. You can request a copy of Microsoft Office 2016 via the Student Software Store icon on the Course Resources page.
If you are a MAC user, click to read the MAC User Information (Links to an external site.).
If you do not have Excel 2016 installed locally, then access the software by going to the Course Resources page, Lab Resources section, and click the Virtual Lab Citrix icon.
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 Copy of Student_File_Week5.xlsx

To save the spreadsheet with a new file name,

open the workbook in Excel 2016; and
in Excel, click File, then Save as, and rename it as lastname_first initial_Week5_Lab.xlsx (Jane Doe would save the file as Doe_J_Week5_Lab.xlsx).
Step 1: Insert Functions
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.
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.
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.

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.

Insert number 1301 in cell B3. B3 is the cell that will be used to research apartment unit prices.
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.)
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.

Step 3: 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.

Enter the loan details provided above in the Input Area and place formulas to create all calculations in the Summary Calculations. The loan payment is at the end of the period.
Create a loan amortization table. The Payment Date column needs a date function. The Interest Paid and Principal Payment columns require financial functions.
Create a custom footer with your name on the left side, the page and page number in the center, and your professor’s name on 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 4: 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.

Calculate the average job satisfaction for Administrative Assistant in cell I5. Format the results with the number format and two decimal positions.
Use the fill handle from cell I5 to copy the function down through the range I6:I11. Make certain to consider the appropriate mixed and/or absolute cell referencing.
Calculate the average salary of all Administrative Assistants and place the result in cell J5.
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.
Calculate the number of Sales and Marketing Directors in cell I14 that have a job satisfaction level of 4 or above.
Calculate the average salary of Sales and Marketing Directors in cell I15 that have a job satisfaction level of 4 or above.
Use a process like that demonstrated in steps E and F to calculate the total number and the average salary of Managers that have a job satisfaction of 4 or greater.

Use the Employee Satisfaction data to create a Pivot Table showing the average salary and Job Satisfaction by Position (rows). Format professionally and sort highest salary to lowest.

Step 5: Create a Documentation Sheet

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

The Minimum Requirement (per the Grading Rubric)

Insert a new spreadsheet into the workbook. The Documentation sheet should be the first sheet in the workbook.
Make certain each tab has a descriptive name and color for each tab (sheet) in the workbook.
Create the professional documentation worksheet. Be sure to include a description of each worksheet. An image is provided below.
Week 5 Documentation Sheet
Transcript (Links to an external site.)
Finish and Submit

Save your Excel file. Make sure you are aware as to where your files are physically saved. Saving your file often is good practice (Ctrl + s).

Your Excel file should contain five worksheets.

Documentation Page
Search
Loan
Employee Satisfaction
Satisfaction Pivot Table 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
Rental Price
Apartment Complex
Turning Leaf Circle
Turning Leaf Circle
Turning Leaf Circle
Turning Leaf Circle
Turning Leaf Circle
Turning Leaf Circle
Turning Leaf Circle
Turning Leaf Circle
Turning Leaf Circle
Turning Leaf Circle
Turning Leaf Circle
Turning Leaf Circle
Turning Leaf Circle
Turning Leaf Circle
Turning Leaf Circle
Turning Leaf Circle
Turning Leaf Circle
Turning Leaf Circle
Turning Leaf Circle
Turning Leaf Circle
Piedra Springs
Piedra Springs
Piedra Springs
Piedra Springs
Piedra Springs
Piedra Springs
Piedra Springs
Piedra Springs
Eagles Domain Road
Eagles Domain Road
Eagles Domain Road
Eagles Domain Road
Eagles Domain Road
Eagles Domain Road
Eagles Domain Road
Eagles Domain Road
Hickory Ridge Villas
Hickory Ridge Villas
Hickory Ridge Villas
Hickory Ridge Villas
# 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
2
1
2
3
1
2
3
2
2
1
1
1
1
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
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
Pet Deposit
1505
1506
1507
1601
1602
1603
1604
1605
1606
1607
Hickory Ridge Villas
Hickory Ridge Villas
Hickory Ridge Villas
Cypress Trail
Cypress Trail
Cypress Trail
Cypress Trail
Cypress Trail
Cypress Trail
Cypress Trail
2
2
2
1
1
2
2
3
3
3
$
$
$
$
$
$
$
$
$
$
850.00
850.00
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
No
Yes
Yes
No
Yes
No
Yes
No
2003
2011
2011
2005
2005
2005
2011
2011
2005
2005
Recommendation
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
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
Sales & Marketing Directors with >= 4 Satisfaction
Count
Average Salary
Managers with >= 4 Satisfaction
Count
Average Salary
Average Satisfaction
Average Salary

Purchase answer to see full
attachment

Calculator

Calculate the price of your paper

Total price:$26

Need a better grade?
We've got you covered.

Order your paper