Scenario
docx
keyboard_arrow_up
School
Hazara University, Dodhial, Mansehra *
*We aren’t endorsed by this school
Course
LAB
Subject
Information Systems
Date
Nov 24, 2024
Type
docx
Pages
3
Uploaded by ConstableFlag11264
Scenario
Your CEO has tasked you with doing some research about data 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.
Lab Components and
Instructions
Expand AllPanels Collapse AllPanels
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.
Step 1: Lab Preparation
The spreadsheet you need to download and work with
is
BIAM110_Week2_Data.xlsx
.
Step 2: Open and Save Data File
First, open the workbook you downloaded from Step 1. Next, save your
workbook as Week2_Lab_YourLastName_YourFirstName.xlsx.
The data contains the following columns.
Units A, B, and C
Months
Years
Step 3: Create Format and Formulas
1.
Format the data for currency where needed.
2.
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.
3.
Add shading for the labels.
4.
In Column F under total costs, use the sum function to total all
three units.
5.
Add totals at the bottom of the data for all columns, using the
sum function.
6.
Add the average to each column by adding the function for
average.
7.
Screenshot or copy your file in a Microsoft Word document
under a label that says Step 3.
8.
Save both files.
Step 4: Sort and Filter
1.
Select the data except for the total and the average.
2.
Click the filter (funnel) on the data tab. You will see small drop-
down arrows on all columns.
3.
Sort the total cost ascending. Take a screenshot of your sort
results. Paste the screenshot into your MS Word document
under a label that says Step 4 - Part 1.
4.
Click undo or clear filter at the bottom. Select all to turn check
marks off and put a check mark in JAN. Take a screenshot of
your filtered results and paste it into your MS Word document
under the a label that says Step 4 - Part 2.
5.
Click undo or uncheck JAN and select DEC. Take a screenshot of
the sort results and paste it into your MS Word document under
the a label that says Step 4 - Part 3.
6.
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?
Please provide a short summary (50 to 100 words) under the
last screenshot.
7.
Save both files.
Step 5: Charting
1.
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.
2.
Label and format the chart to make it look professional.
3.
Screenshot or copy your file in a Microsoft Word document
under a label that says Step 5.
4.
Under the screenshot in the document, please summarize what
you can see in this chart within 50 to 100 words.
5.
Save both files.
Step 6: Reflection
1.
Open your MS Word document with the sorting and filtering
screen prints.
2.
Write a short reflection of what you have learned doing this lab
exercise. This should be between 100 to 200 words. Within
your summary, consider what the data tells you in terms of
which units costs more and what months are the highest costs.
Step 7: Complete and Submit Work
Save and submit both your MS Excel file and MS Word document to Canvas.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help