Placeholder

BIS 155 Week 2 Lab 2 Latest-DeVry

$12.00

Quantity:

Description

BIS 155 Week 2 Lab 2 Latest-DeVry

BIS 155 Week 2 Lab 2 Latest-DeVry

BIS155

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

https://devry.equella.ecollege.com/file/928a5cc1-3b3b-4126-96dc-36ce43c3bd3b/162/1.png

  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

https://devry.equella.ecollege.com/file/928a5cc1-3b3b-4126-96dc-36ce43c3bd3b/162/2.png

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

https://devry.equella.ecollege.com/file/928a5cc1-3b3b-4126-96dc-36ce43c3bd3b/162/3.png

  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

https://devry.equella.ecollege.com/file/928a5cc1-3b3b-4126-96dc-36ce43c3bd3b/162/Week_2_lab_-_Daily_Sales_with_Functions.PNG

Listen

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.

https://devry.equella.ecollege.com/file/928a5cc1-3b3b-4126-96dc-36ce43c3bd3b/162/image_--_wk2_-_Daily_Totals_Bar_Chart.pngHorizontal 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. https://devry.equella.ecollege.com/file/928a5cc1-3b3b-4126-96dc-36ce43c3bd3b/162/image_--_wk2_-_Percentage_of_Sales_Pie_Chart.pngPie 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. https://devry.equella.ecollege.com/file/928a5cc1-3b3b-4126-96dc-36ce43c3bd3b/162/Week_2_lab_-_Weekly_Profits_Worksheet.PNGWeekly Profits Worksheet
  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.

https://devry.equella.ecollege.com/file/928a5cc1-3b3b-4126-96dc-36ce43c3bd3b/162/image_--_wk2_-_Profits_by_Day_Line_Chart.png

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 Doe, then your file should be named very similar to Doe_J_Week2_Lab.xlsx.

BIS 155 Week 2 Lab 2 Latest

BIS 155 Week 2 Lab 2 Latest

ABS,AC,ACC,ACCT,ACT,ADJ,AH,AJS,AMP,ANT,ART,BA,BAM,BBA,BCOM,BIO,BIOS,BIS,BMGT,BPA,BSA,BSE,BSHS,BSOP,BUS,BUSN,CARD,CEIS,CHEM,CIS,CIT,CJA,CJS,CMC,CMGT,COLL,COM,COMM,COMP,CPN1,CRJ,CRMJ,CRT,CS,CWV,DBM,EBUS,ECE,ECET,ECN,ECO,ECOM,ECON,ECT,EDU,EED,EMM,ENG,ENGL,ENT,ENTR,ESE,ETH,ETHC,FIN,FIS,FP,GB,GBM,GED,GEN,GENERALQUESTIONS,GLG,GM,GSCM,HCA,HCS,HHS,HIS,HIST,HLT,HOSP,HPE,HRM,HSA,HSM,HTM,HTT,HUM,HUMN,IFSM,INFT,INT,IS,ISCOM,IT,ITB,JADM,JUS,JWI,LAS,LAW,LAWS,LDR,LEG,LIT,LTC,MA,MAT,MATH,MGMT,MGT,MIS,MKT,MKTG,MTH,NETW,NR,NRS,NSCI,NTC,NUR,NURS,OI,OMM,OPS,ORG,PA,PAD,PCN,PHI,PHL,PHYS,PM,POL,POLI,POS,PPA,PRG,PROJ,PSC,PSY,PSYC,PSYCH,QNT,QRB,RDG,REL,RELI,RES,SBE,SCI,SEC,SOC,SOCS,SPE,SRM,STAT,STR,SYM,TCH,UNV,WEB,XACC,XBIS,XCOM,XECO,XMGT