Lab Assignment Information Excel features
Scenario
Your CEO has tasked you with doing some research about data and and how to work with data. The reading and activities this week introduce data topics and explain how Microsoft Excel can help analyze the data. Your task is to create a Microsoft Excel file (workbook) and use the following Microsoft Excel skills.
-Open sample data file, rename, and save.
-Create formulas for the data.
-Format cells, fonts, shading, and use bold and cell formats.
-Insert a pie chart and add a title.
-Filter and sort data.
-The following is the necessary information to complete this lab assignment.
Deliverables
The deliverable for this lab is a Microsoft Excel Workbook, including Steps 1–6 results and a Microsoft Word document.
Required Software
This lab will use the following Lab Resources.
Virtual Lab Citrix (MS Excel 2016 or 2019 & MS Word 2016 or 2019)
Use a personal copy of the software or access the Lab Resources (Citrix) located in the Course Resources page.
CLICK HERE TO PLACE YOUR ORDERStep 1: Lab Preparation
Resources for this lab are located in Citrix or on your personal computer, using Microsoft Excel. There are 27 videos located in spreadsheets, reading, and resources. Use these videos to learn about the spreadsheet skills you will need for the CEO. The spreadsheet you will be working with is BIAM110_Week2_Data.xlsx (Links to an external site.).
Please review the videos that you need to complete the different steps below.
Note: You also have the option to complete the Microsoft Excel Practice Exercise if you are seeking additional practice and information to use your skills. This exercise is not required.
Step 2: Open and Save Data File
First, download the BIAM110 Week 2 data file provided above. Next, save your workbook as Week2_Lab_YourLastName_YourFirstName.xlsx.
The data contains the following columns.
Units A, B, and C
Months
Step 3: Create Format and Formulas
Format the data for currency where needed.
Add a title to the data worksheet. Make it larger, bold, and centered over the data using the merge and center option on the home tab. You can also add color to the title.
Add shading for the month labels and the units.
In Column E under total costs, use the sum function to total all three units.
Add totals at the bottom of the data for all columns, using the sum function.
Add the average to each column by adding the function for average.
Step 4: Sort and Filter
Using your knowledge of Microsoft Excel, what can you ascertain about the data? Are there interesting insights that come to light as you analyze it using sorting and filtering?
Select the data except for the total and the average.
Click the filter (funnel) on the data tab. You will see small drop-down arrows on all columns.
Sort the total cost ascending. Take a screen print of your sort results. Paste the screen print into your MS Word document.
Click undo or clear filter at the bottom. Sort the month column by JAN. Select all to turn check marks off and put a check mark in JAN. Take a screen print of your sort results and paste it into your MS Word document.
Click undo or uncheck JAN and select DEC and sort the month column by DEC. Take a screen print of the sort results and paste it into your MS Word document.
Step 5: Charting
Create a pie chart of the total units and the three types of units. You may need to use a different area of the worksheet to set up the pie chart labels and numbers with total cost.
Label and format the chart.
Step 6: Analysis
Open your MS Word document with the sorting and filtering screen prints.
Below the screen prints, tell a brief story about what you noticed about the data. Summarize what the data tells you in terms of which units sell more and what months are the highest sales.
Step 7: Complete and Submit Work
Save and submit your MS Exccdsel file to Canvas.
