bis 155 complete week lab assignments pack devry n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
BIS 155 COMPLETE WEEK LAB ASSIGNMENTS PACK-DEVRY PowerPoint Presentation
Download Presentation
BIS 155 COMPLETE WEEK LAB ASSIGNMENTS PACK-DEVRY

Loading in 2 Seconds...

play fullscreen
1 / 60

BIS 155 COMPLETE WEEK LAB ASSIGNMENTS PACK-DEVRY - PowerPoint PPT Presentation


  • 17 Views
  • Uploaded on

\nVisit Below Link, To Download This Course:\n\nhttps://www.tutorialsservice.net/product/bis-155-complete-week-lab-assignments-pack-devry/\n\nOr \nEmail us on\nSUPPORT@TUTORIALSSERVICE.NET\n\nBIS 155 Complete Week Lab Assignments Pack-DeVry\nBIS155\nBIS 155 Week 1 DQ 1 Latest-DeVry\nSpecial Course Preparedness (graded)\nPlease read the course announcements and the Syllabus in particular. Pay particular attention to grading, attendance, discussion requirements, and the last portion of the Plagiarism section (Using APA) of the Syllabus. Address each of the items below:\nHave you taken COMP100? It is a prerequisite to this course.\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 COMPLETE WEEK LAB ASSIGNMENTS PACK-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 complete week lab assignments pack devry

BIS 155 COMPLETE WEEK LAB ASSIGNMENTS PACK-DEVRY

Visit Below Link, To Download This Course:

https://www.tutorialsservice.net/product/bis-155-complete-week-lab-assignments-pack-devry/

Or

Email us on

SUPPORT@TUTORIALSSERVICE.NET

BIS 155 Complete Week Lab Assignments Pack-DeVry

BIS155

BIS 155 Week 1 DQ 1 Latest-DeVry

Special Course Preparedness (graded)

Please read the course announcements and the Syllabus in particular. Pay particular attention to grading, attendance, discussion requirements,

and the last portion of the Plagiarism section (Using APA) of the Syllabus. Address each of the items below:

Have you taken COMP100? It is a prerequisite to this course.

Do you have Microsoft Excel 2016 software?

If not, have you accessed the Virtual Lab – Citrix to use Excel 2016?

Discuss the Syllabus – what was one thing that stood out and why?

provide two examples of apa citations one to cite

Provide two examples of APA citations – one to cite a web reference, and one to cite a book or journal. NOTE: a URL is not an APA citation.

BIS 155 Week 1 DQ 2 Latest-DeVry

Getting Familiar With Excel (graded)

Excel was introduced in 1985. Since then, it has become a standard business tool.

Discuss and explain at least six different (unique) features of Excel that make it a valuable business tool.

Discuss what type of Excel skills would be important in today’s job market and why. Be specific.

Note: In addition to the Microsoft Excel 2016 overview in the Course Home section, there are many Excel resources in addition to your text on the

Web. For example, try an Internet search on “top ten Excel 2016 features” and sift through some of the resources. Be sure to read your

classmates’ posts, and see how many features you can find that haven’t been listed yet.

BIS 155 Week 2 DQ Latest-DeVry

Formulas and Functions (graded)

One of the benefits of Excel is the ability to use formulas and functions.

1. Discuss the differences between formulas and functions. Pick a function in Excel and discuss how that function is used to calculate results in

your worksheets. Review your classmates’ posts to make sure that you’re not duplicating formulas or functions already discussed. Using the

following scenarios, discuss how you would apply the Excel functions or create a formula to solve the scenario. Be specific!

You wish to calculate the commission on sales. The commission is 6% on all sales that are at least 20% above cost.

You wish to calculate the total pay for an employee who receives regular time for 40 hours, time and a half for 40–50 hours, and double

time for hours over 50.

You have a list of contracts and due dates for annual maintenance fees. You wish to determine when you have a contract due in the next

45 days and provide a note that warns you that the payment is due.

Please feel free to add other, unique mathematical challenge questions to the discussion, and let us work together to figure out how you

might calculate it.

bis 155 week 3 dq latest devry

BIS 155 Week 3 DQ Latest-DeVry

Data Analysis Tools (graded)

1. Excel provides many different ways in which to analyze data. Discuss the different methods by which you can analyze data in your spreadsheet.

That is, try an Internet search for “data analysis with excel,” or use some of the methods you learned in this week’s lesson. (Please don’t use the

same methods. Show your research and creativity skills.)

2. Assume the role of a regional manager for a retail organization looking at ways to analyze the large amounts of sales data you have. Give some

examples.

Note: As you answer these questions, a recommended approach would be to attach a sample Excel spreadsheet that you can use to illustrate

your answers.

BIS 155 Week 4 DQ Latest-DeVry

Excel Data Exchange (graded)

Excel 2013 provides the capability to exchange data with other applications. Discuss the different types of ways to exchange data between Excel

and other Microsoft applications.

1. Are there limitations to the exchange process? Think of some specific examples.

2. How can you exchange data with other applications that are not part of the Microsoft Office Suite?

Discuss how you would exchange data with external data sources, such as websites. (Be certain to include your references from your research.)

BIS 155 Week 5 DQ Latest-DeVry

Specialized and Statistical Functions (graded)

1. Discuss and compare the specialized and statistical functions in Excel. You may need to research your answer.

2 under what circumstances would you use logical

2. Under what circumstances would you use logical and lookup functions? What financial and statistical functions exist for use? Give examples of

how each can be used.

Please include your research references in APA format.

BIS 155 Week 6 DQ Latest-DeVry

What-If Analysis (graded)

Life is full of questions and each question deserves a good answer. Let’s explore the What-If Analysis tools available in Excel. Explain how these

tools are used to assist with solid decision making. Give examples of how each of the What-If Analysis tools can be used practically. Please

include your research references in APA format.

BIS 155 Week 7 DQ Latest-DeVry

Expert Tools (graded)

1. Excel shares very well with other applications. Why does it import data so easily from other external sources? What features make it easy to

manipulate this imported data?

2. Adding a macro to an Excel workbook is almost like clicking the “Easy” button. They can automate very simple tasks. Why are people often

intimidated by macros?

BIS 155 Week 1 Lab 1 Latest-DeVry

Lab Overview

Listen

Scenario/Summary

you are new to the staff of summit ridge mountain

You are new to the staff of Summit Ridge Mountain Resort. One of the benefits to your employment is a free mountain ski pass. You are anxious

to complete your work and enjoy a run down the mountain but know management is looking to you for important details and analysis for the ski

resort overall. This week, management has asked that you begin to prepare a report for the Village Shop sales. The report will summarize all sales

of all items and include the total profit-to-date with the amount remaining to reach the profit goal. You will open the partially completed workbook,

create formulas, and format your work professionally.

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

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

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.

Lab 1 – Village Shop Sales

StepTask

Points Possible

Points Received

Comments

1

Documentation sheet

1a

Insert new worksheet

1

1b

Rename worksheet tab

1

slide6

1c

Enter identifying information

2

1d

Format with text wrapping & color formatting

2

2

Calculations

2a

Calculate Net Selling Price for each item

3

Calculate the Total Sales for each item

3

Calculate the Total Profit per item

3

Calculate the Average Profit % for each item

3

2b

Calculate Total Profit to Date

3

2c

Calculate the Remaining Profit Need to meet the Goal

3

3

Formatting

3a

Center the spreadsheet title rows across the selection A – R

4

3b

Apply text wrapping to column headings

4

3c

Change Font size and color

2

3d

Change shading and borders

2

3e

Format currency and percentages

3

4

Professional Appearance (Cleanup)

4a

Tab color and names

4

4b

Hide columns D-M

4

4c

Custom header: Professor Names and Student Name

5

4d

Page orientation

3

Comment in Dropbox: What you learned from completing this Lab

5

total points

TOTAL POINTS

60

0

Week 1 Lab 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 a copy of Microsoft Office 2016 via the Student Software Store icon on the Course

Resources page. Instructions – How to get your copy of Office 2016

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.

for example your final assignment file should

For example, your final assignment file should include two worksheets (Documentation and Sales-Data).

Listen

Begin: Opening and Saving

Download the spreadsheet Week 1 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_Week1_Lab.xlsx (Jane Doe would save the file as

Doe_J_Week1_Lab.xlsx).

Listen

Step 1: Adding a Documentation Sheet

1. Insert a new spreadsheet into the workbook.

2. Change the tab name from sheet 2 to Documentation. The documentation sheet should be moved to the first sheet in the workbook.

3. Include your name, the date, and the purpose of the spreadsheet.

4. Make sure you include block color formatting and text wrapping as needed. Your documentation sheet should look something like the one

below.

Documentation Sheet

listen

Listen

Step 2: Adding Calculations (Formulas)

Now we need to begin working on the worksheet with all of the data.

1. Create the formulas needed to calculate the following for each item.

Column N: Total Quantity Sold = Sum of all Daily Sales

Column O: Net Selling Price = Selling Price * (1 – Sales Discount)

Column P: Total Sales = Total Quantity Sold * Net Selling Price

Column Q: Total Profit = Total Sales – (Cost per item * Total Quantity Sold)

Column R: Average Profit % = Total Profit / Total Sales

1. Create a Grand Total for the Total Profit column and place it in cell B21.

2. Create a formula to calculate the Remaining Profit needed and place it in cell B22.

Remaining Profit = The Profit Goal – Total Profit (all items)

One of the strengths of Excel is its ability to make tedious and repetitious calculations for speed and to reduce errors. Like all tools, there is always

the possibility that you could use it incorrectly, so with formulas, it is important to verify the formulas you enter are correct. Notice that the daily

sales columns between columns C and N are hidden in the picture below.

Click on Image to Expand

Listen

step 3 formatting

Step 3: Formatting

Apply formatting to your spreadsheet.

Your goal is to take into consideration readability, reliability, and reusability. Remember that presentation has an impact on the viewer, and

professional appearance is important for reporting data, just as personal appearance impacts perception. The goal is to make the data more

readable and possibly to highlight data that might otherwise be lost in many numbers.

The minimum formatting requirements (per the Grading Rubric) are the following.

1. Title Rows formatted as titles, centered across all columns A–R

2. Apply text wrapping to the column headers.

3. Make professional font size and color changes.

4. Make use of shading and borders.

5. Format for currency, percentage, and numeric formatting.

Click Image to Expand

Listen

Step 4: Professional Appearance (Cleanup)

Cleanup the formatting of your report, 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. Hide the Daily Sales columns (D–M).

3. Create a custom page header with your professor’s name and your name.

4. Set the page orientation to landscape.

remember professional appearance doesn t only

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

Click Image to Expand

Listen

Finish: Save and Submit

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

Your Excel file should contain two worksheets.

Documentation

Sales-Data

Remember to provide a comment in the Dropbox comments area explaining what you learned from completing this lab activity. Submit your

completed Excel file to the Dropbox (see the Deliverables above).

Keep this finished work, because you will be using it to begin the lab for Week 2.

BIS 155 Week 2 Lab 2 Latest-DeVry

Lab Overview

Listen

Scenario/Summary

Management at Summit Ridge Mountain Resort is very pleased with your initial report. This week, management has asked that you add to the

prior analysis and incorporate some visual images concerning the Village Shop sales. This new analysis will require the use of new formulas, new

functions and several charts to graphically

functions, and several charts to graphically depict the report findings. This updated Excel workbook will be used for a presentation to the board of

directors.

Listen

Deliverables

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

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

Lab 2 – Shop Analysis

Step

Task

Points Possible

Points Received

Comments

Documentation sheet completed

5

1

Daily Item Totals worksheet

1a

Copy Daily Sales to new worksheet

2

1b

SUM function for items

2

SUM function for dates

2

1c

Formula percentage of sales

3

1d

Statistical functions for sales

3

1e

Formula profit per item

2

2

Create Bar Chart

2a

2D Bar Chart

5

slide13

2b

On new worksheet

2

2c

Title and tab named

2

3

Create Pie Chart

3a

Pie Chart

5

3b

On new worksheet

2

3c

Title and tab named

2

4

Create Weekly Profits

4a

Copy Daily Sales to new worksheet

2

4b

Calculate profit per item

2

4c

Calculate profits for each item

4

4d

Autofill profits for all items

3

4e

Create Line Chart

5

4f

Title and tab named

2

Comment in Dropbox: What you learned from completing this Lab

5

TOTAL POINTS

60

0

Week 2 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

This lab will use Microsoft Excel 2016.

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.

For example, your final assignment file should include six worksheets (Documentation, Sales Data, Daily Item Totals, Total Items Bar Chart,

Percent of Sales Pie Chart, and Profits by Day Line Chart).

Please open the Week 1 Lab submitted to the Dropbox and make any corrections or modifications as directed via your Week 1 Dropbox feedback.

Save this new Excel workbook as Doe_J_Week2_Lab.xlsx. Follow the lab steps to create formulas, utilize functions, and add charts to graphically

depict the sales over time and by product.

Listen

Step 1: Create Daily Item Totals Worksheet

click on the sales data worksheet we need

Click on the Sales Data worksheet. We need to see all of the hidden data, so unhide all hidden columns. Once you can see all the hidden data, we

need to select data to copy to a new worksheet. Sort your data in A to Z order. Select the accessories data in column A, hold your control key

down, and then select the daily sales data in columns D through M. Copy this selected data to a new sheet.

Click on the Image to Expand

1. The Daily Sales data will look as follows on the new worksheet. Please name the tab for this worksheet Daily Item Totals.

Click on the Image to Expand

Create daily totals using the SUM function for each day and for each item. Make these pronounced by adding a bold font and some color

formatting.

Click on the Image to Expand

1. Let’s add a formula to calculate the percentage of sales for each item in column M. Percent of sales can be determined by dividing the

total of each item (L4) by the total of all items (L17).

2. Add functions to determine the best sale item, the lowest sale item, and the average of all sale items.

3. Last, add a column called Profit per Item. Subtract the cost of the item from net selling price and place it in column N.

Click on the Image to Expand

Listen

step 2 create the total items bar chart

Step 2: Create the Total Items Bar Chart

1. Using the Daily Item Totals sheet, select the accessories data in column A, hold your control key down, and then select the Total Items

Column L. While this data is selected, click the Insert tab on the ribbon. Choose a 2-D Bar Chart.

2. Move this chart to a new worksheet.

3. Your chart needs a professional title, and the worksheet tab should also be named appropriately.

Horizontal Bar Chart

Listen

Step 3: Create the Percentage of Sales Pie Chart

1. Using the Daily Item Totals sheet, select the accessories data in column A, hold your control key down, and then select the Percent of

Sales Column M. While this data is selected, click the Insert tab on the ribbon. Choose a 3-D Pie Chart.

2. Move this chart to a new worksheet.

3. Your chart needs a professional title, and the worksheet tab should also be named appropriately.

Pie Chart

Listen

Step 4: Create Weekly Profits by Item and Line Chart

1. Copy the Daily Sales data to a new worksheet.

2. Calculate the profit per item. Subtract the cost of the item from the net selling price and place it in column L.

3. Next, create a new area on the same worksheet and calculate the profits for each item. For example, the profits for gloves in cell B19 are

calculated as B3 * $L3.

4. Autofill all weekly profits by item and total.

5. Lastly, select the dates across the horizontal, hold down the control key, and select the daily totals along the bottom. Insert a Line Chart

like below.

6. Give the chart a title, but leave it on the same worksheet.

Weekly Profits Worksheet

profits by day line chart

Profits by Day Line Chart

Listen

Step 5: 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. Update the documentation worksheet to include contents of each worksheet.

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

4. Set the page orientation to landscape.

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

Listen

Finish: Save and Submit

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

Your Excel file should contain six worksheets.

Documentation

Sales Data

Daily Item Totals

Total Items Bar Chart

Percent of Sales Pie Chart

Profits by Day

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

File naming convention: If your name is Jane Doe, then your file should be named very similar to Doe_J_Week2_Lab.xlsx.

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

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

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

slide20

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

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: 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 2

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

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

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

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

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 3

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

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.

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.

BIS 155 Week 4 Lab 4 Latest-DeVry

lab overview

Lab Overview

Listen

Scenario/Summary

Summit Ridge Mountain Resort has three full-service warming huts on its majestic mountains. The Summit View, the Bunny Den, and the Mogul

Hill Samurai warming huts are rest stops that provide skiing customers a place to relax, eat, and warm up. At the end of the month, the manager of

each warming hut prepares a worksheet that summarizes the monthly income.

Please note that during the summer months, Summit Ridge conducts activities such as zip lining, nature photography tours, off-trail biking, and

hiking. As the assistant to the general manager of Summit Ridge Mountain Resort, you will need to consolidate each warming hut’s data into one

worksheet so that decisions such as inventory, purchasing, and profitability can be assessed in order to manage the business well. In this lab, you

will also apply other data management tools that are part of the overall management of an organization.

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

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

Lab 4 – Mountain Consolidation

slide28

Step

Task

Points Possible

Points Received

Comments

1

Data Consolidation

1a – g

Group Sheets & format

7

2

3D Formulas

2a, b

Create 3-D cell references to complete Summary table

5

2c

Create Grand totals

4

2d

Insert Comment

2

2e

Data validation with Error Alert

3

3

Comparison Worksheet

3a

Create cell references

2

3b

Create totals for seasons

2

3c

Create & Format the Chart

5

4

X Games Calculator

4a

Ticket Type drop down list

4

4b

VLOOKUP for ticket price

2

4c

Validation with error message for quantity

2

4d, e, f

Format & add clip art

4

5

Documentation Sheet

5a, b

Format sheet & tabs

3

5c

Author set to student name

2

5d

Describe how to protect

3

slide29

5e

Make a recommendation

5

Comment in Dropbox: What you learned from completing this Lab

5

TOTAL POINTS

60

0

Week 4 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 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

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 4 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_Week4_Lab.xlsx (Jane Doe would save the file as

Doe_J_Week4_Lab.xlsx).

Listen

Step 1: Group and Format the Worksheets

Once your Week 4 Lab file has been opened and saved, you’ll notice the first three worksheets contain data corresponding to the warming hut

monthly incomes.

Let’s do some formatting for ease of readability. Please keep in mind that formatting isn’t just to make things look good, but it’s also necessary for

efficiency and for reducing errors.

Group your warming hut worksheets so that anything you do on one sheet will also affect the other grouped sheets. Click on the View worksheet,

press and hold the shift key, and click on the Year Totals worksheet.

click image to expand 1

Click Image to Expand

1. Merge and center the title through column N, and then repeat for the subtitle. Explore the Themed Cell Styles in the Styles area of the

ribbon and apply a professional and appropriate looking style to the worksheets.

2. Format the months and various products sold by each warming hut with a complementary accent format.

3. Apply the accounting number format with two decimals to the ranges B4:N12.

4. Add Total to the column header N3, to the right of December.

5. Total all of the cell values for the three warming huts both vertically and horizontally. (Expand the columns where ## signs appear.)

6. Add gridlines for the cell range A4:N12.

7. When you are finished, ungroup the worksheets.

8. In order to better differentiate the worksheets, apply a different color to each warming hut’s worksheet title and subtitle.

Click Image to Expand

Listen

Step 2: Add 3-D Formulas

The 3-D formulas will take the values from all three warming hut worksheets and calculate the sum in the Year Totals worksheet.

1. In cell B4 of the Year Total worksheet, create a 3-D formula that will calculate the sum of breakfast values from the three warming huts.

2. Once you are satisfied that the 3-D formula is correct, copy the 3-D formulas down and across the worksheet for the remaining cells.

3. Make certain you have grand totals on the Year Totals worksheet.

4. Add a comment to grand total cell stating, “This is the yearly total of all products of all warming huts.”

5. The Bunny Den does not sell any alcohol products; however, sometimes values get entered by mistake. Open the Bunny Den worksheet

and create a data validation rule that will pop up an Error Alert and prevent any values from being entered in these cells. Apply this

validation rule to all months of alcohol products.

Click Image to Expand

now that you have all warming hut worksheets

Now that you have all warming hut worksheets consolidated into the Year Totals worksheet, give each of the warming hut worksheet tabs a

different accent color. Make sure the tab color matches the color formatting selected for each sheet.

Listen

Step 3: Complete the Comparison Worksheet

The owners of Summit Ridge Ski Lodge have their yearly management meeting in order to review the operating results of the lodge and determine

future direction. They are considering expansion of the off-season activities in order to generate additional revenue. Off season is considered from

the beginning of May through October, when all ski slopes are closed. They have asked you to create some charts that compare winter income

with off-season income.

The following work will be on the Comparison worksheet.

1. Create cell references from the monthly totals data on the Yearly Totals worksheet into the Comparison worksheet so that the values link

to the originating cells. (Hint: Paste it using the link option. This will create a link to the referenced cells.)

2. Create a total using the SUMIF function for the winter season and the off season.

3. Using this data, please create a chart that will show in-season and off-season incomes of the three warming huts. Place this chart on the

Comparison worksheet.

4. Format the chart and the worksheet professionally. Make sure the chart has an appropriate title.

Click Image to Expand

Listen

Step 4: Create X Games Calculator

summit ridge has decided to add some exciting

Summit Ridge has decided to add some exciting winter sports and will be hosting their own X Games competition the last weekend in January.

They need a calculator to help validate and determine ticket costs. Click on the last worksheet in your Week 4 Lab file.

1. Add data validation for the Ticket Type. This needs to be a drop-down list and allow the selection of all eight ticket options.

2. Add a VLOOKUP to determine the ticket price based on the ticket type.

3. Add validation to the quantity input cell. It must be a number between 1 and 100. If a number above 100 is entered, display an error

message stating, “Tickets in excess of 100 cannot be purchased. Please contact group sales at 555-545-9080.”

4. Create a formula to calculate the total cost based on price and quantity.

5. Format your calculator professionally.

6. Choose a cool clip art to balance your calculator visually.

X Games Calculator

Listen

Step 5: Documentation Sheet and Recommendations

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

The Minimum Requirement (per the Grading Rubric)

1. Make certain each tab has a descriptive name and connecting color for each sheet in the workbook.

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

3. Check the properties of your Excel workbook. Make sure you are noted as the author of your workbook.

4. Describe on your documentation sheet how you would protect this workbook and why. Please do not password protect your workbook.

(Although this is not shown in the picture below, it can be added after your worksheet contents and should include similar professional

color and text formatting.)

5. Based on your analysis, provide your recommendation about the comparison of the winter income and the off-season income. Provide

your answer on the Documentation sheet. Make sure to provide color formatting for your recommendation that is consistent with the rest of

the worksheet. (The recommendation is not shown on the picture below. Please make sure your recommendation is formatted in a

professional manner consistent with the entire documentation sheet formatting.)

Click Image to Expand

listen 4

Listen

Step 6: 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 seven worksheets.

Documentation

Summit View

Bunny Den

Mogul Hill Samurai

Year Totals

Comparison

X Game Calculator

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

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

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

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 1

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

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

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

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

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

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

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

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.

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

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

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

Your Excel file should contain six worksheets.

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.

BIS 155 Week 6 Lab 6 Latest-DeVry

Lab Overview

Listen

Scenario/Summary

This week it seems like everyone needs an answer to a question. The Accounting Manager for Operations has asked you to update some analysis

prepared earlier in the year. In an attempt to predict year-end changes in net income, you have been asked to prepare some reports on changes

in condominium rental fees, ski lift tickets, and X Game tickets and pricing.

The Sales Department for Luxury Condominiums wants to know how increasing their rental pricing by varying percentages will affect the existing

pricing. The manager would like to know how that will increase the Summit Ridge Mountain Resort’s total revenue. A one-variable data table might

be good to create these results.

The supervisor in charge of setting prices for lift tickets also needs some help. She wants to price the lift tickets so that they are affordable but yet

offer the resort a chance to improve its net income at the end of the season. A one-variable data table might be able to answer this question.

resort management has a target income

Resort Management has a target income for the X Games. They need help determining the best combination of pricing and quantity of tickets to

sell for the X Games to be a success. A two-variable data table might be best to display these results.

And there are more puzzling questions. Resort Management has reviewed variables such as lift ticket prices, restaurant revenues, retail revenues,

maintenance costs, and payroll. They would like you to create a Scenario Summary to determine optimistic, mid-range, and pessimistic scenarios

for the resort. The Operations Manager would like to know how these changes might affect revenue, expenses, and net income. A Scenario

Summary would be a good choice for this analysis.

Last, all of the Warming Hut Management need a little help with some investigation on their data. They have quite a bit of data collected but need

some assistance making sense of it. It may be time to polish your skills with Pivot Tables, because the management has many questions and

concerns. Your boss is confident about your ability to be creative and customize some Pivot Tables that are unique to each Hut.

What-If questions need solid answers. It is time to provide a professional response.

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

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

LAB 6 – What-If-Analysis

Step

Task

Points Possible

Points Received

Comments

slide45

1

Complete Income Statement

1a – g

Complete formulas & formatting

4

2

Build One-Variable Data Tables

2a

One-Variable Data Table for condo rentals

3

2b

One-Variable Data Table for ski lift tickets

3

2c

Apply conditional formatting

2

2d

Answer question in G17

5

3

Build Two-Variable Data Table

3a

Enter Net Income and format

3

3b

Apply conditional formatting

3

3c-d

Answer question in G17

5

4

Scenarios and Summary

4a

Name Cells for Scenario

2

4b

Create three scenarios

4

4c

Create Scenario Summary

2

4d

Move sheet & apply formatting

2

5

Create Pivot Tables

5a, b

Build Pivot Table 1

3

5c

Build Pivot Table 2

3

5d

Build Pivot Table 2

3

5e

Create a chart for one pivot table

2

slide46

5f

Apply professional formatting

2

6

Create Documentation Sheet

6a

Create Documentation Sheet

2

6b

Organize worksheet contents

1

6c, d

Format Documentation Sheet

1

Comment in Dropbox: What you learned from completing this Lab

5

TOTAL POINTS

60

0

Week 6 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 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

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 6 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 and then Save As and rename it as lastname_first initial_Week6_Lab.xlsx (Jane Doe would save the file as

Doe_J_Week6_Lab.xlsx).

Listen

Step 1: Complete Income Statement

It is time to build the formulas needed to complete the income statement. Begin working on the Income Statement worksheet and complete

formulas for all of the cells marked in gray.

1 enter the formula to calculate the condo

1. Enter the formula to calculate the Condo Rentals Revenue in C13 Multiple the Quantity of Condo Rental Days * the Price for Condo

Rental.

2. Enter the formula to calculate the Ski Lift Revenue in C14 Multiple the Quantity of Ski Lift Tickets * the Price for Ski Lift Tickets.

3. Enter the formula to calculate the Winter X Games Revenue in C17 Multiple the Quantity of X Games Tickets * the Price for X Games

Tickets.

4. Enter the formula to calculate the Total Revenues in C20 and Total Expenses in C34.

5. Make sure the cell references for Total Revenues and Total Expenses are placed in cells C37 and C38 respectively.

6. Enter the formula to calculate the Net Income in cell C39.

7. Apply professional formatting to all of this data using the image below as a guide.

Income Statement

Listen

Step 2: Build the One-Variable Data Tables

Now that the Income Statement is complete, begin to address the questions about the condo rentals and ski lift tickets.

1. Build a one-variable data table based on condo rental days. The initial values for Revenue and Net Income for cells I6 and I7 should be

cell references from the income statement.

2. Build a one-variable data table based on the quantity of ski lift tickets. The initial values for Revenue and Net Income for cells I14 and I15

should be cell references from the income statement.

3. Apply conditional formatting to highlight Net Income of at least $250,000 for each of the one-variable data tables.

4. In cell G17, a question exists. “If management has a target net income of at least $250,000, which of the above SPECIFIC scenarios in

the condo rentals or ski tickets one-variable data table would you recommend using? Explain your reasoning.” Make sure you place your

answer in the text box beginning in cell G20.

5. Apply professional formatting to all of this data using the image below as a guide.

One-Variable Data Tables

Listen

step 3 build the two variable data table

Step 3: Build the Two-Variable Data Table

Once the one-variable data tables are complete, begin to address the questions about the X Games tickets sold.

1. Build a two-variable data table based on the quantity and price of the X Games tickets sold. Enter a reference to Net Income in

cell I27 from the income statement.

2. Apply conditional formatting to highlight Net Income of at least $250,000 for each of the two-variable data table.

3. In cell G37, a question exists. “If management has a target net income of at least $250,000. Which of the above SPECIFIC combinations

of price and quantity of X Games Tickets in the two-variable data table would you recommend using? Explain your reasoning.”

4. Make sure you place your answer in the text box beginning in cell G40.

5. Apply professional formatting to all of this data using the image below as a guide.

Two-Variable Data Table

Listen

Step 4: Create the Scenario Summary

You addressed quite a few questions. Now it is time to create a Scenario Summary.

1. Assign names to all of the income statement cell values in column C in the assumptions, revenues, expenses, and summary sections

using the labels in column B. For example, select cells B5:C10, and then on the Formula tab in the Defined Names Group, select “Create

from Selection” and use the left column as the name (repeat on lower sections).

2. Build three scenarios by changing cells C8, C15, C16, C29, and C30 using the following data: Optimistic, Mid-range, and Pessimistic.

Scenario Details

3. Generate the Scenario Summary using C37:C39 as the result cells.

4. Move the Scenario Summary sheet after the Income Statement and apply professional formatting to all of this data using the image below

as a guide.

Completed Scenarios

Listen

step 5 create pivot tables from warming hut data

Step 5: Create Pivot Tables From Warming Hut Data

Select the Warming Hut Sales Worksheet. You notice the data are not formatted or organized well.

1. Use the Warming Hut Sales data to build Pivot Tables.

2. Build the first Pivot Table to summarize total sales by category and by location. Name this sheet Pivot Table 1.

3. Build the second Pivot Table to summarize total sales by category and by season for only the Summit View location. Name this sheet

Pivot Table 2.

4. Build the third Pivot Table to summarize total sales by month and by product for only the Mogul Hill location. Name this sheet Pivot Table

3.

5. Choose one of the created Pivot Tables and add a professional chart to the same worksheet.

6. Apply professional formatting to all of this data using the image below as a guide.

Click Image to Expand

Listen

Step 6: 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 all contents of the workbook are properly noted on the documentation sheet.

3. Make certain each tab has a descriptive name for each tab (sheet) in the workbook.

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

Click Image to Expand

transcript

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

Documentation Page

Income Statement

Scenario Summary

WarmingHutSales

Pivot Table 1

Pivot Table 2

Pivot Table 3

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

BIS 155 Week 7 Lab 7 Latest-DeVry

Lab Overview

Listen

Scenario/Summary

Your manager could not be happier with the help you’ve provided to all of those departments requesting Excel assistance within the resort. He is

now very confident handing over complex tasks to your capable hands. This week he is requesting that you exercise some expert skills. It seems

he has some data that needs to be imported

he has some data that needs to be imported and cleaned up so that it is useful to the resort. He has quite a list of tasks he needs done with this

data. Once the data are presentable, the analysis needs to be summarized in a short memo and placed on the last sheet in the workbook.

Additionally, your manager has also heard that macros can be added to a worksheet. He wants to make the X Games calculator even more

efficient and foolproof for all those using it.

Just like traversing a double black diamond slope, you have the Excel knowledge to maneuver all of these tasks with excellence. This lab is for

experts only.

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

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

LAB 6 – What-If-Analysis

Step

Task

Points Possible

Points Received

Comments

1

Import and Organize Data

1a – b

Import Data

5

1c -i

Professional Clean up & Formatting

5

2

Create Macro for X Games Calculator

slide53

2a – l

Create & Record Macro

6

3

Assign Macro

3a – f

Draw Macro button

5

3g – i

Associate Macro with button

5

4

Save Macro enabled file & Protect Calculator

4a, b

Save as .xlsm file type

3

4c

unlock user input cells

4

4d

Associate Macro with button

4

5

Create a Documentation Sheet – Apply Watermark

5a-c

Insert and create documentation sheet

4

5d-j

Create watermark

4

6

Summarize and Prepare Memo

6a – d

Summarize and write memo on last worksheet

10

Comment in Dropbox: What you learned from completing this Lab

5

TOTAL POINTS

60

0

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

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 7 Lab – Summit Ridge Mountain Resort Student.xslx. You will be prompted to save the file. Click yes.

Download the spreadsheet Week 7 Lab – Import.txt. You will be prompted to save the file. Click yes.

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 and then Save As and rename it as lastname_first initial_Week7_Lab.xlsx (Jane Doe would save the file as

Doe_J_Week7_Lab.xlsx).

Listen

Step 1: Import and Clean Up Text File

1. Download starting student Excel file and the text file. Open the starting Excel file and navigate to Sheet1.

2. Import the text file by using the Get External Data function on the Data Ribbon. When the Text Import Wizard displays, select My Data has

headers and use a comma delimiter to organize the data.

3. Insert a new column in between A and B and use the Text to Columns command to separate first and last name. Be sure to revise or add

appropriate column names.

4. Insert a column to the right of the HOME ADDRESS column and use a function to capitalize just the first letter in the address names. Then

hide the original Address column.

5. Insert two new columns in between City State Zip and the Unit column, and then use Text to Columns to separate these fields. Adjust

column widths appropriately.

6. On the right of the Agent column, add a column and use the Substitute function to replace “JG” with “Jesse Gonzales,” and then hide the

original Agent column and display the full name.

7. Add statistics to add up all sales, and then apply a 7% commission to calculate the Gonzales commission.

8. Change the sheet name to Gonzales.

9. Under Page Layout, select Themes and choose a business-like theme and possibly a color combination to apply professional formatting

and column widths to all of this data using the image below as a guide.

Cleaned Up Import

Listen

Step 2: Create the Macro in the X Games Ticket Calculator

In this section, we will actually build a macro. A macro is simply a recording of every keystroke you perform while the Macro Recorder is turned on.

You are quite simply automating keystrokes and building efficiency into your Excel file.

1 select the x game calc sheet 2 in order to have

1. Select the X Game Calc sheet.

2. In order to have the Macro Tools available to you, you must have the Developer Tools. If you do not see the Developer Tab, click the File

and select Options.

3. Locate the Developer checkbox option in the Custom Ribbon tab in the Options menu, make sure it is checked, and then click OK.

Developer

1. You will need to set the Macro Security level in order to develop Macro code. From the Developer Tab, click on Macro Security in the

Code group. Check the Enable all macros and then click OK.

Enable All Macros

1. Remember, a macro records every keystroke you perform while the Macro Recorder is turned on. For this Macro, we will clear the values

from the ticket type and ticket quantity cells in our X Game Calculator sheet. We will turn on the Macro Recorder, delete the data in Cells

E4 and E6, and then turn the Macro Recorder off.

2. Click the Record Macro button from the Developer Tab, Code group. The Macro Recorder dialog window will open.

3. Macro Name: Provide a user-friendly name for this recording such as CalcWipe. Note: Do not use spaces in the name.

4. Shortcut key: Designate a shortcut key the user can press to start the macro. Try using the letter w as your shortcut key. Note: Avoid using

a shortcut commonly used in Windows applications, such as CTRL-C or CTRL-P. The macro shortcut will overwrite the Windows shortcut

and your users will not understand why the key combination is not copying or pasting.

5. Store: Store the macro in this Workbook. Note: It can be stored in the Personal Macro Workbook so it can be used by other applications.

Macros for common actions such as printing a workbook can be stored in the personal workbook, but they will only be available on the

computer on which they are stored.

6. Description: Provide an explanation of the functionality of the macro. Use something such as Clear input cells for X Game Calculator. See

image below.

7. When you click OK, the Macro Recording begins. Select Cell E4, press the Delete key, and then select Cell E6 and press the Delete key.

8. Then turn the Macro Recorder off by clicking the Stop Recording button from the Macro group.

Record Macro

Listen

Step 3: View the Macro and Assign a Macro Button in the X Games Calculator Sheet

Behind the scenes, Excel has written a little Visual Basic for Applications (VBA) program, which you may access by clicking the Macros command.

1 select the macro you just created and then

1. Select the Macro you just created, and then click the Edit button.

Macro Name

1. VBA will open with your code.

VBA Code

Transcript

1. In later courses, you may learn more about programming; however, you can follow the code and see how it instructs Excel to select a

range and clear its contents. Click the X in the top-right corner of this MS VBA screen to close macro editing and return to the Excel file.

2. Begin to add a macro button to make it easy for users to run this code. Add a Button Shape to your application using the Insert Tab,

Shapes.

Add a Button Shape

3. Draw the shape onto the application, using your mouse or drawing tool.

4. Add a text description to the button: “Clear calculator values!”

5. Associate this button with the macro. Right-click on the shape and select Assign Macro from the menu.

Draw the Shape

Associate Button With Macro

1. Select CalcWipe and click OK to assign the macro to the button.

2. To prepare to test the macro, provide input into cells E4 and E6 by selecting a ticket type in cell E4 and enter a quantity in cell E6 for

starting values. Then test your macro by simply clicking the button. Did it clear the contents of your input cells? If yes, congratulations on

creating your first macro. If no, please return to Step 2, Part E to build the macro again.

Listen

Step 4: Save the Macro-Enabled File and Turn on Protection in the X Games Calculator Sheet

1 you will need to save this file as a macro

1. You will need to save this file as a macro-enabled workbook. Click the File tab and select Save As.

2. Select Excel Macro-Enabled Workbook, which is also the .xlsm file type.

3. By default, all cells in a worksheet are locked. This means that when the worksheet is protected, the users will not be able to enter or

change data in those cells. We will protect the worksheet, but first we must unlock the user input cells. Select cells E4 and E6. Right-click

and select Format Cells from the menu. In the Protection tab, uncheck the box labeled Locked.

4. Protect the worksheet. From the Home Menu Cells group, select Format, Protection, Protect sheet. DO NOT add a password.

Protect Sheet

5. Remember to save the file as an .xlsm (Macro-enabled) file.

Listen

Step 5: Create a Documentation Sheet and Apply a Footer With Watermark Ski Image

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

The Minimum Requirement (per the Grading Rubric)

1. Insert a new sheet into the workbook, name it Documentation, and move it to be the first sheet.

2. As always, properly document all contents of the workbook on this sheet including names and brief descriptions of each sheet.

3. Apply a business such as formatting to all of these cells.

4. Create a watermark footer containing an experts-only ski image by first searching the Internet for an appropriate free image and save it.

Use something such as the following.

Experts Only

5. Select Page Layout and then open the Page Setup window.

Page Layout

1. Select Custom Footer and then use the icon on the right side to insert the image saved above into the Center section of the Footer.

2. Once the &[Picture] is displayed in the Center section, the Format Picture icon on the far right will be available. Click it.

3. In the Format Picture pop-up window, select the Picture tab.

4. Under the Image control section, select the drop-down option by Color and select Washout, and then click OK to accept it.

5. Click Print Preview on the Page Setup window to view the results!

print preview

Print Preview

Transcript

1. IMPORTANT: Remember to save this file as a Macro-enabled workbook.

Listen

Step 6: Summarize and Write Memo

Return to the worksheet named Gonzales. The analysis on this worksheet needs to be summarized in a short memo. Here are some details and

ideas about the memo to be written.

1. The memo should be written to Jesse Gonzales. Your name should also be in the From portion of the memo.

2. Include a summary of the analysis completed.

3. Possible thoughts to include: target sales for new fiscal period, sales for new developments, sharing expertise with others in sales, and a

thank you for good work.

4. The memo needs to be written professionally and placed on the last sheet in the workbook. Name the last worksheet Memo.

Listen

Finish and Submit

Save your Excel file as a Macro-enabled workbook!! Your Excel file should contain four worksheets.

Documentation

Gonzales

X Games Calc

Memo

submit one macro enabled excel file

Submit one Macro-enabled Excel file to the Dropbox. When submitting the file, 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_Week7Lab.xlsm.

Download Now