BIS 155 COMPLETE WEEK LAB ASSIGNMENTS PACK-DEVRY

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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