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

Loading in 2 Seconds...

play fullscreen
1 / 9

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

  • Uploaded on

nVisit Below Link, To Download This Course:nn nEmail us onnSUPPORT@TUTORIALSSERVICE.NETnnBIS 155 Week 4 Lab 4 Latest-DeVrynBIS155nBIS 155 Week 4 Lab 4 Latest-DeVrynLab OverviewnListennScenario/SummarynSummit 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.n

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


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 4 lab 4 latest devry


Visit Below Link, To Download This Course:


Email us on


BIS 155 Week 4 Lab 4 Latest-DeVry


BIS 155 Week 4 Lab 4 Latest-DeVry

Lab Overview



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

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.




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


(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



Points Possible

Points Received



Data Consolidation

1a – g

Group Sheets & format



3D Formulas

2a, b

Create 3-D cell references to complete Summary table



Create Grand totals



Insert Comment



Data validation with Error Alert



Comparison Worksheet



Create cell references



Create totals for seasons



Create & Format the Chart



X Games Calculator


Ticket Type drop down list



VLOOKUP for ticket price



Validation with error message for quantity


4d, e, f

Format & add clip art



Documentation Sheet

5a, b

Format sheet & tabs



Author set to student name



Describe how to protect



Make a recommendation


Comment in Dropbox: What you learned from completing this Lab





Week 4 Grading Rubric


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.


Lab Steps



You will be using Microsoft Excel 2016 for this lab.

Be sure you have read the required chapter materials and reviewed the hands-on exercise videos located on the Lesson page before you begin

the lab.

Please do not rely solely on the hands-on exercise videos to complete this week’s lab. The videos provide detailed examples walking you through

the hands-on exercises. Applying the hands-on exercise examples will provide both practice and instruction of what to complete.


Begin: Open and Save

Download the spreadsheet 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

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



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

Click Image to Expand


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


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

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


Step 4: Create X Games Calculator

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



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


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.


Summit View

Bunny Den

Mogul Hill Samurai


Year Totals


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


Download Now