Skip To...

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-terminvestment, 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 jobto analyze the information regarding the apartments. Theowners 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 analysesto determine how adding this new complexto their portfolio enhances the resort'sfinancial 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.

The grading rubric for this assignment is available in the weekly Lab assignment page. The most successful students always review the criteria in this rubric before uploading their lab. This allows them to check that they have completed all of the steps in the lab. It is a very helpful final review prior to submission.

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 theMAC User Information.

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. 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, but you will be completing the exact steps described below and submitting your workbook after you complete all steps.

Begin: Open and Save

Download the spreadsheetSTUDENT_BIS155_W5_Lab5_Starter_File.xlsx.(Links to an external site.)Links 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,

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-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.

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.

Click Image to Expand

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

In the Loan area (A1 through E7), enter the loan details in the Input Area based on the information provided above. Place formulas to create allcalculations in the Summary Calculations. The loan payment is at the end of the period.

Createa loan amortization table with the header row in A10 through F10. Add payment # (1 thru 360 for a 3-year loan). Use autofill to help. ThePayment Date column needs a date function. The Interest Paid and Principal Payment columns require financial functions.

Format the sheet professionally and 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 4: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.

Calculate the average job satisfaction for Administrative Assistant in cell H5. Format the results with the number format and two decimal positions.

Use the fill handle fromcell H5 to copy the function down through the range H6:H11. Make certain to consider theappropriate mixed and/or absolute cell referencing.

Calculate the average salary of all Administrative Assistantsand place the result in cell I5.

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 number of Sales and Marketing Directors in cell H14 that have a job satisfaction level of 4 orabove.

Calculate the average salary of Sales and Marketing Directors in cell H15 that have a job satisfaction level of 4 orabove.

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.

H. Use the Employee Satisfaction data to create a Pivot Table showing the average salary and Job Satisfation 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 eachtabhas adescriptive name and color for eachtab(sheet) in the workbook.

Create the professional documentation worksheet. Be sure to include a description of each worksheet. An image is provided below.

Lab 5 Documentation Sheet

Week 5 Documentation Sheet

Transcript

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

Submit one workbook. When submitting the workbook, provide a comment in thecomments area explaining what you learned from completing this lab activity. File naming convention: If your name is Jane Doe, then your file should be named very similar to Doe_J_Week5_Lab.xlsx.

Week 5: Lesson" aria-describedby="msf0-previous-desc" style="color: rgb(0, 0, 0); background: rgb(245, 245, 245); border: 1px solid rgb(199, 205, 209); border-radius: 0px; transition: background-color 0.2s ease-in-out 0s; display: inline-block; position: relative; padding: 8px 14px; margin-bottom: 0px; font-size: 1rem; line-height: 20px; text-align: center; vertical-align: middle; cursor: pointer; overflow: hidden; text-shadow: none; user-select: none; float: left;">Previous
Week 5: Specialized and Statistical Functions" style="float: right;">Next
Top

studentbis155w5lab5starterfile-z2wko34u.xlsx

Answered Same DayApr 09, 2021

Ashish · Accepted Answer

Search
	Stay a While Longer - Apartments Search
	Unit #	1607		Rental Price	$1,710.00
	Unit #	Apartment Complex	# Bed	Rental Price	Occupied	Last Remodel	Pet Deposit	Recommendation
	1101	Turning Leaf Circle	1	$   765.00	Yes	2005	$100.00	No Change
	1102	Turning Leaf Circle	1	$   765.00	Yes	2003	$100.00	No Change
	1103	Turning Leaf Circle	2	$   895.00	No	1999	$100.00	Needs to Remodel
	1104	Turning Leaf Circle	2	$   895.00	Yes	2005	$100.00	No Change
	1105	Turning Leaf Circle	3	$   1,225.00	No	2007	$150.00	No Change
	1106	Turning Leaf Circle	3	$   1,225.00	Yes	2005	$100.00	No Change
	1107	Turning Leaf Circle	2	$   895.00	No	2005	$100.00	No Change
	1108	Turning Leaf Circle	2	$   895.00	Yes	2009	$150.00	No Change
	1109	Turning Leaf Circle	3	$   1,225.00	No	2008	$150.00	No Change
	1110	Turning Leaf Circle	3	$   1,225.00	Yes	2009	$150.00	No Change
	1201	Turning Leaf Circle	1	$   775.00	Yes	2011	$100.00	No Change
	1202	Turning Leaf Circle	1	$   775.00	Yes	2010	$100.00	No Change
	1203	Turning Leaf Circle	2	$   875.00	Yes	2011	$150.00	No Change
	1204	Turning Leaf Circle	2	$   875.00	No	2011	$150.00	No Change
	1205	Turning Leaf Circle	3	$   1,050.00	Yes	2005	$100.00	No Change
	1206	Turning Leaf Circle	3	$   1,050.00	No	2005	$100.00	No Change
	1207	Turning Leaf Circle	2	$   875.00	Yes	2000	$100.00	No Change
	1208	Turning Leaf Circle	2	$   875.00	Yes	2000	$100.00	No Change
	1209	Turning Leaf Circle	3	$   1,050.00	No	2005	$100.00	No Change
	1210	Turning Leaf Circle	3	$   1,050.00	Yes	2005	$100.00	No Change
	1301	Piedra Springs	1	$   875.00	No	2008	$100.00	No Change
	1302	Piedra Springs	1	$   875.00	No	2001	$100.00	Needs to Remodel
	1303	Piedra Springs	1	$   900.00	No	2001	$100.00	Needs to Remodel
	1304	Piedra Springs	1	$   900.00	Yes	2001	$100.00	No Change
	1305	Piedra Springs	2	$   1,355.00	No	2005	$100.00	No Change
	1306	Piedra Springs	2	$   1,355.00	Yes	2005	$100.00	No Change
	1307	Piedra Springs	2	$   1,355.00	Yes	2011	$150.00	No Change
	1308	Piedra Springs	2	$   1,355.00	No	2011	$150.00	No Change
	1401	Eagles Domain Road	1	$   875.00	No	2005	$100.00	No Change
	1402	Eagles Domain Road	2	$   950.00	No	2011	$150.00	No Change
	1403	Eagles Domain Road	3	$   1,355.00	Yes	2011	$150.00	No Change
	1404	Eagles Domain Road	1	$   875.00	No	2011	$100.00	No Change
	1405	Eagles Domain Road	2	$   950.00	Yes	2011	$150.00	No Change
	1406	Eagles Domain Road	3	$   1,355.00	No	2002	$100.00	Needs to Remodel
	1407	Eagles Domain Road	2	$   975.00	Yes	2002	$100.00	No Change
	1408	Eagles Domain Road	2	$   975.00	Yes	2002	$100.00	No Change
	1501	Hickory Ridge Villas	1	$   550.00	Yes	2011	$100.00	No Change
	1502	Hickory Ridge Villas	1	$   550.00	No	2011	$100.00	No Change
	1503	Hickory Ridge Villas	1	$   550.00	No	2003	$100.00	Needs to Remodel
	1504	Hickory Ridge Villas	1	$   550.00	Yes	2001	$100.00	No Change
	1505	Hickory Ridge Villas	2	$   850.00	No	2003	$100.00	Needs to Remodel
	1506	Hickory Ridge Villas	2	$   850.00	Yes	2011	$150.00	No Change
	1507	Hickory Ridge Villas	2	$   850.00	No	2011	$150.00	No Change
	1601	Cypress Trail	1	$   1,225.00	Yes	2005	$100.00	No Change
	1602	Cypress Trail	1	$   1,225.00	Yes	2005	$100.00	No Change
	1603	Cypress Trail	2	$   1,355.00	No	2005	$100.00	No Change
	1604	Cypress Trail	2	$   1,355.00	Yes	2011	$150.00	No Change
	1605	Cypress Trail	3	$   1,675.00	No	2011	$150.00	No Change
	1606	Cypress Trail	3	$   1,695.00	Yes	2005	$100.00	No Change
	1607	Cypress Trail	3	$   1,710.00	No	2005	$100.00	No Change
Loan
	Input Area			Summary Calculations
	Complex Cost	$950,000.00		Loan Amount	550,000
	Down Payment	$400,000.00		No. Periods	360
	# of Pmts per Year	12		Monthly Rate	0.44%
	Years	30		Monthly Payment	$3,062.72
	APR	5.325%		Total Interest Paid	$552,579.17
	1st Payment Date	1/20/17
	Payment #	Payment Date	Beginning Balance	Interest Paid	Principal Payment	Ending Balance
	1	1/20/17	$   550,000.00	$2,440.63	$622.09	$   549,377.91
	2	2/20/17	$   549,377.91	$2,437.86	$624.86	$   548,753.05
	3	3/20/17	$   548,753.05	$2,435.09	$627.63	$   548,125.42
	4	4/20/17	$   548,125.42	$2,432.31	$630.41	$   547,495.01
	5	5/20/17	$   547,495.01	$2,429.51	$633.21	$   546,861.80
	6	6/20/17	$   546,861.80	$2,426.70	$636.02	$   546,225.78
	7	7/20/17	$   546,225.78	$2,423.88	$638.84	$   545,586.93
	8	8/20/17	$   545,586.93	$2,421.04	$641.68	$   544,945.26
	9	9/20/17	$   544,945.26	$2,418.19	$644.53	$   544,300.73
	10	10/20/17	$   544,300.73	$2,415.33	$647.39	$   543,653.34
	11	11/20/17	$   543,653.34	$2,412.46	$650.26	$   543,003.09
	12	12/20/17	$   543,003.09	$2,409.58	$653.14	$   542,349.94
	13	1/20/18	$   542,349.94	$2,406.68	$656.04	$   541,693.90
	14	2/20/18	$   541,693.90	$2,403.77	$658.95	$   541,034.95
	15	3/20/18	$   541,034.95	$2,400.84	$661.88	$   540,373.07
	16	4/20/18	$   540,373.07	$2,397.91	$664.81	$   539,708.26
	17	5/20/18	$   539,708.26	$2,394.96	$667.76	$   539,040.49
	18	6/20/18	$   539,040.49	$2,391.99	$670.73	$   538,369.76
	19	7/20/18	$   538,369.76	$2,389.02	$673.70	$   537,696.06
	20	8/20/18	$   537,696.06	$2,386.03	$676.69	$   537,019.37
	21	9/20/18	$   537,019.37	$2,383.02	$679.70	$   536,339.67
	22	10/20/18	$   536,339.67	$2,380.01	$682.71	$   535,656.96
	23	11/20/18	$   535,656.96	$2,376.98	$685.74	$   534,971.21
	24	12/20/18	$   534,971.21	$2,373.93	$688.79	$   534,282.43
	25	1/20/19	$   534,282.43	$2,370.88	$691.84	$   533,590.59
	26	2/20/19	$   533,590.59	$2,367.81	$694.91	$   532,895.68
	27	3/20/19	$   532,895.68	$2,364.72	$698.00	$   532,197.68
	28	4/20/19	$   532,197.68	$2,361.63	$701.09	$   531,496.59
	29	5/20/19	$   531,496.59	$2,358.52	$704.20	$   530,792.38
	30	6/20/19	$   530,792.38	$2,355.39	$707.33	$   530,085.06
	31	7/20/19	$   530,085.06	$2,352.25	$710.47	$   529,374.59
	32	8/20/19	$   529,374.59	$2,349.10	$713.62	$   528,660.97
	33	9/20/19	$   528,660.97	$2,345.93	$716.79	$   527,944.18
	34	10/20/19	$   527,944.18	$2,342.75	$719.97	$   527,224.21
	35	11/20/19	$   527,224.21	$2,339.56	$723.16	$   526,501.05
	36	12/20/19	$   526,501.05	$2,336.35	$726.37	$   525,774.68
	37	1/20/20	$   525,774.68	$2,333.13	$729.59	$   525,045.09
	38	2/20/20	$   525,045.09	$2,329.89	$732.83	$   524,312.25
	39	3/20/20	$   524,312.25	$2,326.64	$736.08	$   523,576.17
	40	4/20/20	$   523,576.17	$2,323.37	$739.35	$   522,836.82
	41	5/20/20	$   522,836.82	$2,320.09	$742.63	$   522,094.19
	42	6/20/20	$   522,094.19	$2,316.79	$745.93	$   521,348.26
	43	7/20/20	$   521,348.26	$2,313.48	$749.24	$   520,599.02
	44	8/20/20	$   520,599.02	$2,310.16	$752.56	$   519,846.46
	45	9/20/20	$   519,846.46	$2,306.82	$755.90	$   519,090.56
	46	10/20/20	$   519,090.56	$2,303.46	$759.26	$   518,331.30
	47	11/20/20	$   518,331.30	$2,300.10	$762.62	$   517,568.68
	48	12/20/20	$   517,568.68	$2,296.71	$766.01	$   516,802.67
	49	1/20/21	$   516,802.67	$2,293.31	$769.41	$   516,033.26
	50	2/20/21	$   516,033.26	$2,289.90	$772.82	$   515,260.44
	51	3/20/21	$   515,260.44	$2,286.47	$776.25	$   514,484.19
	52	4/20/21	$   514,484.19	$2,283.02	$779.70	$   513,704.49
	53	5/20/21	$   513,704.49	$2,279.56	$783.16	$   512,921.34
	54	6/20/21	$   512,921.34	$2,276.09	$786.63	$   512,134.70
	55	7/20/21	$   512,134.70	$2,272.60	$790.12	$   511,344.58
	56	8/20/21	$   511,344.58	$2,269.09	$793.63	$   510,550.95
	57	9/20/21	$   510,550.95	$2,265.57	$797.15	$   509,753.80
	58	10/20/21	$   509,753.80	$2,262.03	$800.69	$   508,953.12
	59	11/20/21	$   508,953.12	$2,258.48	$804.24	$   508,148.88
	60	12/20/21	$   508,148.88	$2,254.91	$807.81	$   507,341.07
	61	1/20/22	$   507,341.07	$2,251.33	$811.39	$   506,529.67
	62	2/20/22	$   506,529.67	$2,247.73	$814.99	$   505,714.68
	63	3/20/22	$   505,714.68	$2,244.11	$818.61	$   504,896.07
	64	4/20/22	$   504,896.07	$2,240.48	$822.24	$   504,073.82
	65	5/20/22	$   504,073.82	$2,236.83	$825.89	$   503,247.93
	66	6/20/22	$   503,247.93	$2,233.16	$829.56	$   502,418.37
	67	7/20/22	$   502,418.37	$2,229.48	$833.24	$   501,585.14
	68	8/20/22	$   501,585.14	$2,225.78	$836.94	$   500,748.20
	69	9/20/22	$   500,748.20	$2,222.07	$840.65	$   499,907.55
	70	10/20/22	$   499,907.55	$2,218.34	$844.38	$   499,063.17
	71	11/20/22	$   499,063.17	$2,214.59	$848.13	$   498,215.04
	72	12/20/22	$   498,215.04	$2,210.83	$851.89	$   497,363.15
	73	1/20/23	$   497,363.15	$2,207.05	$855.67	$   496,507.48
	74	2/20/23	$   496,507.48	$2,203.25	$859.47	$   495,648.01
	75	3/20/23	$   495,648.01	$2,199.44	$863.28	$   494,784.73
	76	4/20/23	$   494,784.73	$2,195.61	$867.11	$   493,917.62
	77	5/20/23	$   493,917.62	$2,191.76	$870.96	$   493,046.66
	78	6/20/23	$   493,046.66	$2,187.89	$874.83	$   492,171.83
	79	7/20/23	$   492,171.83	$2,184.01	$878.71	$   491,293.

Skip To ContentDashboard Account Dashboard Courses Calendar 30Inbox Help BIS155 Modules Week 5: Lab Overview March 2019 Home Syllabus Announcements Modules Files Grades People Media Gallery My Media...

Week 5: Lab Overview

Lab Overview

Scenario/Summary