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

Loading in 2 Seconds...

play fullscreen
1 / 10

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


  • 1 Views
  • Uploaded on

\nVisit Below Link, To Download This Course:\n\nhttps://www.tutorialsservice.net/product/bis-155-week-3-lab-3-latest-devry/\n\nOr \nEmail us on\nSUPPORT@TUTORIALSSERVICE.NET\n\nBIS 155 Week 3 Lab 3 Latest-DeVry\nBIS155\nBIS 155 Week 3 Lab 3 Latest-DeVry\nLab Overview\nListen\nScenario/Summary\nYou are becoming more confident in your new position at Summit Ridge Mountain Resort. You are still meeting new coworkers and slowly but surely being asked to use more of your Excel skills for different departments at the resort. The manager handling sales for all of the condos in the Summit Ridge Mountain Village needs some help with his sales analysis. This report needs to clearly list and summarize all new loans for condominium sales and incorporate advanced functions and state loan statistics. In addition, you have been asked to investigate the claim made by the financing company to determine the true track record of the company in selling condos for the asking price. This will require advanced analysis techniques.\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 3 LAB 3 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 3 lab 3 latest devry

BIS 155 WEEK 3 LAB 3 LATEST-DEVRY

Visit Below Link, To Download This Course:

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

Or

Email us on

SUPPORT@TUTORIALSSERVICE.NET

BIS 155 Week 3 Lab 3 Latest-DeVry

BIS155

BIS 155 Week 3 Lab 3 Latest-DeVry

Lab Overview

Listen

Scenario/Summary

You are becoming more confident in your new position at Summit Ridge Mountain Resort. You are still meeting new coworkers and slowly but

surely being asked to use more of your Excel skills for different departments at the resort. The manager handling sales for all of the condos in the

Summit Ridge Mountain Village needs some help with his sales analysis. This report needs to clearly list and summarize all new loans for

condominium sales and incorporate advanced functions and state loan statistics. In addition, you have been asked to investigate the claim made

by the financing company to determine the true

by the financing company to determine the true track record of the company in selling condos for the asking price. This will require advanced

analysis techniques.

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

Example: If your name were Jane Doe, your workbook would be Doe_J_Week3_Lab.xlsx.

Lab 3 – Luxury Condo Sales

Step

Task

Points Possible

Points Received

Comments

1

Condo Sales Worksheet

1a

Calculate % of asking price

2

1b, c

Format worksheet (column width, headings, colors, data types)

4

2

Loan Data Worksheet

2a

VLOOKUP for Interest Rate and Down Payment

3

Calculate Amount Financed

2

PMT function for Monthly Payment and functions for loan statistics

4

2b, c, d Format worksheet (column width, headings, colors, data types)

2

slide3

3

Sort and Create Summary Report and Chart

3a, b

Create Sorted Data worksheet

2

3c

Sort and Order Sorted Data

2

3d,e

Create a summary report and a column chart

3

4

Filter the Data

4a

Create Filtered Data worksheet

2

4b, c, d Filter the Data

3

5

Conditional Formatting

5a, b

Create Conditional Format worksheet and Insert a table

2

5c

Set up conditional formatting rules

2

5d

Sort by color

2

6

Pivot Table

6a

Select Data and Name Range of Cells

2

6b

Create Pivot table

3

6c

Formatting

2

7

Draw conclusion

5

Documentation sheet completed

4

Is the slogan a true statement?

4

Comment in Dropbox: What you learned from completing this Lab

5

TOTAL POINTS

60

0

Week 3 Grading Rubric

when submitting the workbook provide a comment

When submitting the workbook, provide a comment in the Dropbox comments area explaining what you learned from completing this lab activity.

Make certain you compare your finished work with the Grading Rubric included here.

Listen

Lab Resources

Microsoft Office: Excel 2016

Options for Accessing Microsoft Excel 2016

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

Listen

Begin: Opening and Saving

Download the spreadsheet Week 3 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,

open the workbook in Excel 2016; and

in Excel, click File then Save as and rename it as lastname_first initial_Week3_Lab.xlsx (Jane Doe would save the file as

Doe_J_Week3_Lab.xlsx).

Listen

Step 1: Add Formulas, Functions, and Formatting to Condo Sales Data Worksheet

Now we need to begin working on the worksheet 1. Let’s begin by renaming sheet 1 to Condo Sales Data.

1. Create the formula needed to calculate the percent of asking price.

Column H: Selling Price/ Asking Price

1. Format the data on this sheet for currency, percent, and so forth.

2. Format the title rows and center them across the column selection. Format the column headings appropriately.

An example of what your Condo Sales worksheet might look like is below.

Click Image to Expand

listen 1

Listen

Step 2: Add Formulas, Functions, and Formatting to Loan Data Worksheet

Now we need to begin working on the worksheet 2. Let’s begin by renaming sheet 2 to Loan Data.

1. Use the VLOOKUP function to determine the Interest Rate & PMT function to determine the monthly payment.

Column E: Interest Rate using VLOOKUP

Column F: Down Payment = VLOOKUP to determine % Required for Down Payment * Selling Price

Column G: Amount Financed = Selling Price – Down Payment

Column H: Monthly Payment = PMT function (Monthly payment is at the beginning of each period)

Cells C41 – C44: Use functions COUNT, MIN, MAX & SUM to complete these loan statistics

1. Format the data on this sheet for currency, percent, and so forth.

2. Format the title rows and center them across the column selection. Format the column headings appropriately.

3. Format the Loan Term Data and Loan Statistics with a clean outside border, color formatting, currency, and percent.

An example of what your Loan Data worksheet might look like is below.

Loan Data Worksheet

Listen

Step 3: Sort and Create Summary Report and Chart

1 to create the sorted data worksheet begin

1. To create the Sorted Data worksheet, begin by creating a copy of the Condo Sales Data. Once a copy has been made of the Condo Sales

Data and moved to a new sheet, name the new worksheet Sorted Data. We will not need the Address column, so please delete that

column of data from the Sorted Data worksheet.

2. Convert the data in the Sorted Data worksheet into a table. Select the rectangular block of cells contained in the Sorted Data worksheet.

Click the Insert tab and select Tables from the Tables group. Remember to check that your table has headers.

3. Sort the data by selling agent and then by asking price. Once the data has been sorted, you can convert it back into a range. You must

convert to range before you can provide a summary report.

4. Create a summary report showing total asking and total selling price by agent. Using subtotals, you will select the criteria for your subtotal.

Each time the agent name changes, you want the total (sum) of both the Asking Price and Selling Price. Make sure the outline level of

your subtotal is set to two on the left top area of your screen. Make sure you format your worksheet professionally.

Click Image to Expand

1. Create a Column Chart to compare asking price versus selling price by agent. Make certain you provide a professional chart title and

formatting for your chart. Include a legend to show what each color represents. Move this chart to a new worksheet and name it Ask vs.

Sell Price Chart.

Click Image to Expand

Listen

Step 4: Create Filtered Data Worksheet

1. To create the Filtered Data worksheet, begin by creating a copy of the Condo Sales Data. Once a copy has been made of the Condo

Sales Data and moved to a new sheet, name the new worksheet Filtered Data.

2. Convert the data in the Filtered Data worksheet into a table. Select the rectangular block of cells contained in the Filtered Data worksheet.

Click the Insert tab and select Tables from the Tables group. Remember to check that your table has headers.

3. Click the drop-down box next to Selling Agent. Uncheck Select All, and then check Langston.

4. Now click the drop-down next to City and select only Pagosa. Now only the properties sold by Langston in Pagosa will display.

Click Image to Expand

listen 2

Listen

Step 5: Create Conditional Formatting Worksheet

1. To create the Conditional Formatting worksheet, begin by creating a copy of the Condo Sales Data. Once a copy has been made of the

Condo Sales Data and moved to a new sheet, name the new worksheet Conditional Formatting.

2. Convert the data in the Conditional Formatting worksheet into a table. Click the Insert tab and select Tables from the Tables group.

Remember to check that your table has headers.

3. Select the data for just the column reflecting the % of Asking Price field. From the Home tab, select Conditional Formatting from the Styles

group and choose Manage Rules. Three new rules will need to be created.

1. Format only cells that are greater than or equal to 100%; associate a green fill color and a white font with this rule.

2. Format only cells that are between 95% and 99.9%; associate a yellow fill color and black font with this rule.

3. Format only cells that are less than 95%; associate a red fill color and white font with this rule.

4. Now sort the data based on the percentage of the asking price. Click the filter drop-down arrow and select Sort by Smallest to Largest.

Click Image to Expand

Listen

Step 6: Create a Pivot Table

1. In this step, we will create a named range called Condo Sales Data. Open the Condo Sales Data worksheet and select Cells A3

through J33. From the Formulas tab, select Define Name from the Defined Names group. Give this segment of the worksheet the name

Condo Sales Data. Notice it will appear in the name box when all of these cells are selected.

2. Create a pivot table based on the Condo Sales Data range.

1. Select Selling Agent as your row labels.

2. Click on City and then drag it into the Column Labels field.

3. Select Percent of Asking (Price) as your values by clicking and then dragging it onto the values area.

4. The summary value of the percent will be a SUM. To change this, click the drop-down area in the Values field and select Value

Field Settings.

1. Change the summary type to Average.

2. Change the number format to percent with two decimals by clicking the Number Format button.

3 change the summary label to average of asking

3. Change the summary label to Average % of Asking Price.

3. Apply Data Bars Conditional Formatting to the percentages. Last, hide the field headers. Rename the sheet PivotTable, and move it so it

is the last tab in the workbook.

Click Image to Expand

Listen

Step 7: Professional Appearance (Cleanup)

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

The Minimum Requirement (per the Grading Rubric)

1. Apply a color and assign a descriptive name to each tab (sheet) in the workbook.

2. Create the documentation worksheet using a format similar to Labs 1 and 2. Be sure to include a description of each worksheet. An image

is provided below.

3. Make certain to include a custom page header with your name and your instructor’s name on the Condo Sales Data worksheet.

4. Based on your analysis, provide your conclusion concerning the Finance Company slogan. Do they really sell a condo for the asking

price? Provide your answer on the Documentation sheet.

Remember, professional appearance doesn’t only promote better readability of your report; it represents you as a professional.

Week 3 Documentation Sheet

Listen

Step 8: Finish and Submit

Save your Excel file. Saving your file often is good practice (Ctrl + s).

Your Excel file should contain eight worksheets.

slide10

Documentation

Condo Sales Data

Loan Data

Sorted Data

Ask vs. Sell Price Chart

Filtered Data

Conditional Formatting Data

PivotTable

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

Download Now