bis 155 week 5 lab 5 latest devry n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
BIS 155 WEEK 5 LAB 5 LATEST-DEVRY PowerPoint Presentation
Download Presentation
BIS 155 WEEK 5 LAB 5 LATEST-DEVRY

Loading in 2 Seconds...

play fullscreen
1 / 10

BIS 155 WEEK 5 LAB 5 LATEST-DEVRY - PowerPoint PPT Presentation


  • 2 Views
  • Uploaded on

\nVisit Below Link, To Download This Course:\n\nhttps://www.tutorialsservice.net/product/bis-155-week-5-lab-5-latest-devry/\n\nOr \nEmail us on\nSUPPORT@TUTORIALSSERVICE.NET\n\nBIS 155 Week 5 Lab 5 Latest-DeVry\nBIS155\nBIS 155 Week 5 Lab 5 Latest-DeVry\nLab Overview\nListen\nScenario/Summary\nSummit 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.\n

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'BIS 155 WEEK 5 LAB 5 LATEST-DEVRY' - tutorialsservicesnet


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
bis 155 week 5 lab 5 latest devry

BIS 155 WEEK 5 LAB 5 LATEST-DEVRY

Visit Below Link, To Download This Course:

https://www.tutorialsservice.net/product/bis-155-week-5-lab-5-latest-devry/

Or

Email us on

SUPPORT@TUTORIALSSERVICE.NET

BIS 155 Week 5 Lab 5 Latest-DeVry

BIS155

BIS 155 Week 5 Lab 5 Latest-DeVry

Lab Overview

Listen

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.

Lab 5 – Mountain Statistical Analysis

slide2

Step

Task

Points Possible

Points Received

Comments

1

Insert Functions

1a – b Insert nested function

7

2a

Create a search

2a, b

Insert nested lookup function

4

3

Manage Database List

3a, c

Perform advanced filter and enter database functions

4

4

Loan Amortization

4a, c

Insert functions, complete amortization table, and footer

8

5

Conditional Functions

5a, g

Insert conditional functions, calculate total and average

8

6

Statistical Functions

6a-d

Calculate rank, minimum, and remaining quartile

6

6e

Calculate the correlation and format the data

6

7

Using the Analysis of ToolPak

7a-c

Use Descriptive Analysis, complete input criteria, set out

6

7d, e

Create histogram chart, use quartiles and format chart

6

Comment in Dropbox: What you learned from completing this Lab

5

TOTAL POINTS

60

0

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

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.

Listen

Deliverables

NOTE

Submit your assignment to the Dropbox, located at the top of this page. For instructions on how to use the Dropbox, read these step-by-step

instructions.

(See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)

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

Listen

Lab Resources

Microsoft Office: Excel 2016

Options for Accessing Microsoft Excel 2016

1 use a personal copy on your pc you can request

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

2. If you are a MAC user, click to read the MAC User Information.

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

Listen

Lab Steps

Listen

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.

Listen

Begin: Open and Save

Download the spreadsheet Week 5 Lab – Summit Ridge Mountain Resort Student.xslx. 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

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

Listen

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-check work for you; use this feature to check for spelling errors.

Click Image to Expand

Listen

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

Click Image to Expand

Listen

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 the Database

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

Listen

Step 4: New Apartment Loan Amortization

summit ridge ski owners want to purchase a sixth

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.

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

2. Create a loan amortization table. The Payment 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 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

Listen

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.

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

3. Calculate the average salary of all Administrative Assistants and 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

5. Calculate the number of Sales and Marketing Directors in cell I14 that have a job satisfaction level of 4 or above.

6. Calculate the average salary of Sales and Marketing Directors in cell I15 that have a job satisfaction level of 4 or above.

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

Employee Satisfaction

Listen

Step 6: Calculate Relative Standing and Central Tendency

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

1. Insert a function in cell F4 to determine the 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. Copy the 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

Listen

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.

1. Go to the Data tab and select Data Analysis. Select Descriptive Statistics, and click OK.

2. Insert the input range using the salary data in column D.

3. Select the new worksheet output option to display on a new worksheet named Descriptive Statistics. Put a check in the box to the left of

Summary statistics. Click OK.

4 create a histogram with the analysis toolpak

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

Listen

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 a descriptive 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

Listen

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

slide10

Documentation Page

Search

Database

Loan

Descriptive Statistics

Employee Satisfaction

Submit one workbook to the Dropbox. When submitting the workbook, provide a comment in the Dropbox comments 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.

Download Now