Expert answer:week 3: MINOR PROJECT

Solved by verified expert:Week 3: Minor ProjectAttached Files: BUSN 420 Minor Project Instructions.pdf (417.458 KB) Technology Plug-In T3 Problem Solving Using Excel.pdf (2.076 MB) Minor Project Data.xlsx (15.311 KB) Minor Project Rubric.xlsx (13.558 KB)Please see this file: Minor Project Instructions for details about your minor project. You will also need this file: Minor Project Data to complete this project. You will be graded according to rubric: Minor Project Rubric. This exercise will introduce you to the variety of uses of PivotTables. For more information on PivotTables please see this file named Technology Plug-In 3: Problem Solving Using Excel. Please read the information provided and complete each exercise.
busn_420_minor_project_instructions.pdf

technology_plug_in_t3_problem_solving_using_excel.pdf

minor_project_data.xlsx

minor_project_rubric.xlsx

Unformatted Attachment Preview

BUSN 420 Management Information Systems
Minor Project
The following exercises will introduce you to Microsoft Excel PivotTables. Having a good
understanding of Pivot Tables will give you an advantage in the business world. When your supervisor
asks you to analyze a large pile of data to solve a problem, you will be able to use a Pivot Table to slice
and dice data to gain information and create usable business intelligence to solve a real world problem.
Please read the information provided and complete each exercise. Once you have completed all of the
exercises below, submit your Excel and Word documents using the assignment link in Blackboard.
Please see the grading rubric as the end of these instructions for more information.
Using the PivotTable Feature
A powerful built-in data analysis feature in Excel is PivotTable. The PivotTable feature analyzes, summarizes, and
manipulates data in large lists, databases, worksheets, or other col- lections. Pivot tables are so named because fields can
be moved within the table to create different types of summary lists, providing a “pivot.” The PivotTable feature offers
flexible and intuitive analysis of data.
Although the data that appear in pivot tables look like any other worksheet data, the data in the data area of the PivotTable
feature cannot be directly entered or changed. The PivotTable feature is linked to the source data; the output in the cells of
the table are read-only data. The formatting (number, alignment, font, etc.) can be changed, and a variety of computational
options are available, such as SUM, AVERAGE, MIN, and MAX.
PIVOTTABLE TERMINOLOGY
Some notable PivotTable terms are:
• Row field—Row fields have a row orientation in a PivotTable report and are displayed as row labels. These
appear in the ROW area of a PivotTable report layout.
• Column field—Column fields have a column orientation in a PivotTable report and are displayed as column
labels. These appear in the COLUMN area of a PivotTable report layout.
• Data field—Data fields from a list or table contain summary data in a PivotTable report, such as numeric data (e.g.,
statistics, sales amounts). These are summarized in the DATA area of a PivotTable report layout.
• Page field—Page fields filter out the data for other items and display one page at a time in a PivotTable report.
BUILDING A PIVOT TABLE
The PivotTable Wizard steps through the process of creating a pivot table, allowing a visual
breakdown of the data in the Excel list or database. When the wizard steps are complete, a
diagram, such as Figure T3.14, with the labels PAGE, COLUMN, ROW, and DATA appears.
The next step is to drag the field buttons onto the PivotTable grid. This step tells Excel about
the data needed to be analyzed with the table.
To use the PivotTable feature:
1. If the workbook T3_ProblemSolving_Data.xlsx is closed, open it.
2. Select the worksheet PivotTableData. Click any cell in the list. Now the active cell is
within the list, and Excel knows to use the data in the Excel list to create a pivot table.
3. Click the Insert tab, and then click the PivotTable button in the Tables group, and click
on PivotTable.
4. The Create PivotTable dialog box opens. In the Select a table or range box, make sure
you see $A$1:$E$97.
5. Click OK. Your spreadsheet will now look like Figure T3.14.
*
6. Using the PivotTable Field List, drag the Month button to the Report Filter area. This
field operates like the row and column fields but provides a third dimension to the data. It
allows another variable to be added to the pivot table without necessarily viewing all its
values at the same time.
7. Drag the Region button to the COLUMN area. The column field is another variable used
for comparison.
FIGURE T3.14
PivotTable Grid, PivotTable
Toolbar, and PivotTable Field
List
8.
9.
*
Drag the Magazine button to the ROW area. A row field in a pivot table is a variable that takes on different values.
Drag the Sale button to the DATA area. The data field is the variable that the PivotTable report summarizes. Your
pivot table should now look like Figure T3.15.
FIGURE T3.15
PivotTable Output with Data,
PivotTable Toolbar, and
PivotTable Field List
MODIFYING A PIVOT TABLE VIEW
A pivot table can be modified at any time. For example, examining the sales for a particular
region would mean that the Region field would need to be changed. Use the drop-down list to
the right of the field name. Select a region and click OK. The grand total dollar amounts by
region are at the bottom of each item; they have been recalculated according to the selected
region(s).
This report can be used in various ways to analyze the data. For instance, click the Clear
button in the PivotTable report, as seen in Figure T3.16.
Then arrange the fields like this:
1.
2.
3.
4.
Magazine in the Report Filter area.
Month in the COLUMN area.
Sales Rep in the ROW area.
Sale in the DATA area.
The completed PivotTable dialog box should look like the one in Figure T3.17. The pivot
table now illustrates the sales by month for each salesperson, along with the total amount of
sales for each sales representative.
BUILDING A PIVOT CHART
A pivot chart is a column chart (by default) that is based on the data in a pivot table. The chart
type can be changed, if desired. To build a pivot chart:
1. Click the Insert tab, and then choose the PivotChart button in the Charts group.
2. Select the Stacked Column chart and click OK.
*
FIGURE T3.16
Clearing the PivotTable
Fields
Clear button
FIGURE T3.17
Rearranged PivotTable Data
3. The pivot chart should look like Figure T3.18.
4. The chart appears in your PivotTable worksheet. If you like, click the Move Chart button
on the Analyze tab and select New sheet from the Move Chart dialog box.
Note: Whatever changes are selected in the PivotChart feature are also made to the pivot
table, as the two features are linked dynamically.
*
FIGURE T3.18
PivotChart Output
SOLVE A PROBLEM USING A PIVOT TABLE
College chums Hannah Baltzan and Tyler Phillips are working on opening a third espresso
drive-through stand in Highlands Ranch, Colorado, called Brewed Awakening. Their original
drive-through stand, Jitters, and their second espresso stand, Bean Scene, have done well
in their current locations in Englewood, Colorado, five miles away. Since Hannah and Tyler
want to start with low overhead, they need assistance analyzing the data from the past year
on the different types of coffee and amounts that they sold from both stands. Hannah and
Tyler would like a recommendation of the four top sellers to start offering when Brewed
Awakening opens. Although Hannah loves to look at spreadsheets Tyler really like graphs, so please
also create a PivotChart for him to better understand your recommendation. They have provided you
with the data file T3_JittersCoffee_Data.xls for you to perform the analysis that will support your
recommendation. Please make sure you are clear in your explanations to Hannah and Tyler.
PIVOT TABLE APPLICATIONS
How have you in the past, or could you in the future, use pivot tables in a business or ministry setting?
Considering the power of Pivot Tables to analyze data, what ethical issues might arise from the
improper usage of this tool to analyze business or ministry data. How should a Christian Worldview
inform these ethical dilemmas?
Please be brief and keep you response to around 300-500 words. Your answer should be supported
with at least one peer-reviewed scholarly journal reference.
The following rubric will be used to grade your submissions:
*
BUSN 420 Minor Project Grading Rubric (100 points)
PivotTable
Excellent/Good
Fair/Poor
Demonstrates knowledge of the
PivotTable wizard through
accurately placing the field
buttons onto the PivotTable grid.
The PivotTable represents the
information presented in Figure
T3.17 with the magazine in the
page area, the month in the
column area, the sales rep field
in the row area, and the sale
field in the data area.
Demonstrates limited
knowledge of the
PivotTable wizard through
inaccurately placing the
field buttons onto the
PivotTable grid. The
PivotTable has the
information presented in
Figure T3.17, however the
information is not in the
same order.
The monthly totals and grand
totals exactly match the totals in
Figure T3.17.
The monthly totals and
grand totals are different
from the totals in Figure
T3.17.
The Sales Rep field list is
not in alphabetical order
(A to Z).
The monthly sales total
columns are not listed in
order and do not start in
January.
The Sales Rep field list is in
alphabetical order (A to Z).
The monthly sales total columns
are listed in order starting with
January.
Comments:
PivotTable Chart
The PivotTable chart is in a
stacked column format.
The sales rep stacked columns
are listed in alphabetical order
(A to Z).
The chart legend is located on
the right side of the chart.
The chart legend dates are in
descending order from April to
January.
Comments:
The PivotTable chart is
not in a stacked column
format.
The sales rep stacked
columns are not listed in
alphabetical order (A to
Z).
The chart legend is not
located on the right side of
the chart.
The chart legend dates
are not in descending
order from April to
January.
Possible Points
Actual Points
10
10
5
5
5
5
5
5
25
25
Possible Points
Possible Points
5
5
5
5
5
5
5
5
20
20
Using Pivot Tables to Solve a Problem
The pivot table and chart have
been properly constructed from
the provided data.
The pivot table and chart clearly
identifies the top four selling
coffee products.
The student has made a clear
recommendation that identifies
the top four selling coffee
products.
The pivot table and chart
have not been properly
constructed from the
provided data.
The pivot table and chart
does not clearly identify
the top four selling coffee
products.
The student has not made
a clear recommendation
that identifies the top four
selling coffee products.
Comments:
PivotTable Applications
Student has provided robust
examples of the use of Pivot
Tables.
Student has provided
minor examples of the use
of Pivot Tables.
Student has supported his or her
text with at least one peerreviewed scholarly journal
references.
Student has not supported
his or her text with at least
one peer-reviewed
scholarly journal
references.
Student has strayed
significantly from the 300500 word count
requirement.
Spelling, punctuation,
grammar, and APA
formatting are poor.
Student has kept close to the
300-500 word count
requirement.
Spelling, punctuation, grammar,
and APA formatting are stellar.
Comments:
Late deductions (e.g., -10):
Final Score:
Overall Comments:
*
Possible Points
Possible Points
10
10
10
10
5
5
25
25
Possible Points
Possible Points
10
10
5
5
5
5
10
10
30
30
100
100
Confirming Pages
T3
P L U G – I N
Problem Solving Using Excel 2016
LE A R N I N G O UTC O M E S
1.
2.
3.
4.
5.
Describe how to create and sort a list in Excel.
Explain why you would use conditional formatting in Excel.
Describe the use of the Excel AutoFilter feature.
Explain how to use the Excel Subtotal command.
Describe the use of the Excel PivotTable feature.
Introduction
If you routinely track large amounts of information, such as customer mailing lists, phone
lists, product inventories, sales transactions, and so on, you can use the extensive list management capabilities of Excel to make your job easier.
In this plug-in you will learn how to create a list in a workbook, sort the list based on one
or more fields, locate important records by using filters, organize and analyze entries by using
subtotals, and create summary information by using pivot tables and pivot charts. The lists
that you create will be compatible with Microsoft Access 2016, and if you are not already
familiar with Access, the techniques that you learn here will give you a head start on learning several database commands and terms. Plug-In T6, “Basic Skills and Tools Using Access
2016,” will provide detail on many of the Access database commands and terms.
This plug-in covers the following five topics:
1.
2.
3.
4.
5.
Building lists.
Creating conditional formatting.
Using Autofilter to find records.
Analyzing a list with the Subtotals command.
Using the PivotTable feature.
Plug-In T3
baL6732X_pluginT03_001-016.indd
T3-1
*
T3-1
12/21/16 07:44 AM
Confirming Pages
Building Lists
A list is a collection of rows and columns of consistently formatted data adhering to somewhat stricter rules than an ordinary worksheet. To build a list that works with all of Excel’s
list management commands, you need to follow a few guidelines.
When you create a list, keep the following in mind:
■
■
■
■
■
Maintain a fixed number of columns (or categories) of information; you can alter the
number of rows as you add, delete, or rearrange records to keep your list up to date.
Use each column to hold the same type of information.
Don’t leave blank rows or columns in the list area; you can leave blank cells, if necessary.
Make your list the only information in the worksheet so that Excel can more easily
recognize the data as a list.
Maintain your data’s integrity by entering identical information consistently. For
example, don’t enter an expense category as Ad in one row, Adv in another, and
Advertising in a third if all belong to the same classification.
To create a list in Excel:
1. Open a new workbook or a new sheet in an existing workbook.
2. Create a column heading for each field in the list, format the headings in bold type, and
adjust their alignment.
3. Format the cells below the column headings for the data that you plan to use. This can
include number formats (such as currency or date), alignment, or any other formats.
4. Add new records (your data) below the column headings, taking care to be consistent in
your use of words and titles so that you can organize related records into groups later. Enter
as many rows as you need, making sure that there are no empty rows in your list, not even
between the column headings and the first record. See Figure T3.1 for a sample list.
FIGURE T3.1
Each column represents a field
containing one type of information.
An Excel List
Each row
represents a
record in
the list.
T3-2
*
Plug-In T3 Problem Solving Using Excel 2016
baL6732X_pluginT03_001-016.indd
T3-2
12/21/16 07:44 AM
Confirming Pages
SORTING ROWS AND COLUMNS
Once your records are organized into a list, you can use several commands on the Data menu
to rearrange and analyze the data. The Sort command allows you to arrange the records in a
different order based on the values in one or more columns. You can sort records in ascending
or descending order or in a custom order, such as by days of the week, months of the year, or
job title.
To sort a list based on one column:
1. Select the SortData worksheet from the T3_ProblemSolving_Data.xlsx workbook that
accompanies this textbook.
2. Click any cell in the Sales Rep column; you want to use this column as the basis for sorting
the list.
3. Click the Data tab.
4. Click the Ascending button to specify the order to sort by (A to Z, lowest to highest,
earliest date to latest).
Your screen will look similar to Figure T3.2.
FIGURE T3.2
A Sorted List
SORTING MORE THAN ONE COLUMN
If you have records in your list that have identical entries in the column you are sorting, you
can specify additional sorting criteria to further organize your list.
To sort a list based on two or three columns:
1. Click any cell in the Sales Rep column.
2. Click the Data tab, and then click the Sort button. The Sort dialog box opens.
3. Click the Column list arrow, and then select the Sales Rep in the Sort by drop-down list.
Click the Order list arrow and specify A to Z order for that column.
4. Click the Add Level button, then click Magazine in the Then by drop-down list. Specify
A to Z order for the second sort.
5. Click the Add Level button, then click Sale for the sort. Specify Smallest to Largest order
for the third sort. The Sort dialog box should look like Figure T3.3 when you are done.
6. Click OK to run the sort.
Figure T3.4 shows how the sort looks based on the options you selected above.
Plug-In T3
baL6732X_pluginT03_001-016.indd
T3-3
*
T3-3
12/21/16 07:44 AM
Confirming Pages
FIGURE T3.3
Sort Dialog Box with Multiple
Records
FIGURE T3.4
Data Sort Using More
Than One Column
CREATING YOUR OWN CUSTOM SORT ORDER
Excel allows you to create custom sort orders so that you can rearrange lists that do not follow
predictable alphanumerical or chronological patterns. For example, you can create a custom
sort order for the regions of the country (West, North, East, South). When you define a custom sort order, it appears in the Options dialog box and is available to all the workbooks on
your computer.
To create a custom sort order:
1. Click the File tab, scroll down to the Options button. A box will open, choose Advanced.
Scroll down the dialog box that opens. Under the General category, click the box Edit
Custom Lists.
2. Click the line NEW LIST under the Custom Lists section, and the text pointer appears in
the List entries: list box. This is where you will type the items in your custom list.
T3-4
*
Plug-In T3 Problem Solving Using Excel 2016
baL6732X_pluginT03_001-016.indd
T3-4
12/21/16 07:44 AM
Confirming Pages
3. Type West, North, South, East, and then click Add. You can either separate each value
with a comma or type each one on a separate line. The new custom order appears in the
Custom Lists list box, as shown in Figure T3.5
4. Click OK to close the Custom Lists dialog box, and again for the Excel Options box.
FIGURE T3.5
Creating a Custom Sort
To use a custom sort order:
1. Click any cell in your list.
2. Click the Home tab. Then under the Editing group, click Sort & Filter, and then click
Custom Sort.
3. Under Column, in the Sort by drop-down box, select the Region field.
4. Under Order, select Custom List.
5. In the Custom Lists dialog box, select West, North, South, East, as shown in
Figure T3.6.
6. Click OK to run the sort. Your list appears sorted with the custom criteria you specified.
FIGURE T3.6
Sort Options Dialog Box
Creating Conditional Formatting
Excel gives you the ability to add conditional formatting—formatting that automatically
adjusts depending on the contents of cells—to your worksheet. This means you can highlight
important trends in your data, such as the rise in a stock price, a missed milestone, or a sudden
Plug-In T3
baL6732X_pluginT03_001-016.indd
T3-5
*
T3-5
12/21/16 07:44 AM
Confirming Pages
spurt in your college expenses, based on conditions you set in advance using the Conditional
Formatting dialog box. With this feature, an out-of-the-ordinary number jumps out at anyone
who routinely uses the worksheet.
For example, if a stock in a Gain/Loss column rises by more than 20 percent, you want to
display numbers in bold type on a light blue background. In addition, if a stock in the Gain/
Loss column falls by more than 20 percent, you want to display the number in bold type on a
solid red background. This is when you want to use conditional formatting.
To create such a conditional format:
1. If the workbook T3_ProblemSolving_Data.xlsx is closed, open it.
2. Select the worksheet Conditional Formatting.
3. S
elect the column Sale. (Note: Each cell can maintain its own, unique conditional
formatting, so that you can set up several different conditions.)
4. Click the Home tab.
5. In the Styles section, click the Conditional Formatting button, and then point to
Highlight Cell Rules and click Between. . . .
6. In the first text box, type the number 1000.
7. In the second text box, type the number 1200.
8. I n the third text box, use the drop-down arrow to select Green Fill with Dark Green
Text. Figure T3.7 displays the settings for this example.
9. Click OK. If any numbers fall into the ranges …
Purchase answer to see full
attachment

How it works

1. Paste your instructions in the instructions box. You can also attach an instructions file
2. Select the writer category, deadline, education level and review the instructions 
3. Make a payment for the order to be assignment to a writer
4.  Download the paper after the writer uploads it 

Will the writer plagiarize my essay?

You will get a plagiarism-free paper and you can get an originality report upon request.

Is this service safe?

All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades