University of the District of Columbia Excel Advanced Business Statistics Quiz The Quiz will be based on Advanced Excel PPT slides from slide 1 to slide 52

University of the District of Columbia Excel Advanced Business Statistics Quiz The Quiz will be based on Advanced Excel PPT slides from slide 1 to slide 52.I have attached file has 9 questions please answer all of them. Advanced Business
Statistics
Advanced Excel
1
MSC 30 Advanced xcel
The purpose of this module is to either provide you with new Excel skills or to refresh
your memory of these skills. In particular, we will focus on sorting, filtering, logic
statements, pivot tables, scenario manager, and a few “tricks of the trade”– all of
which can save you a huge amount of time and reduce the chance for human error.
Pivot tables can significantly reduce the memory requirements for your file, also. You
will need this material for many of the case studies.
Please download the file “Basic Data Set” from Canvas. This is data from a small
hypothetical company. We will used this basic data set throughout the module. It is a
very small file. Although most of the examples and exercises in this module using this
data set could be answered by just counting people or some other simple brute force
method, keep in the back of your mind that you may be analyzing Excel files of
perhaps 80,000 rows and 25 columns. So these techniques can be applied to these
huge Excel files. That is the main goal of this module – to help you save a huge
amount of time and reduce the chance for human error when analyzing huge Excel
files.
2
MSC 300 Advanced Excel
THE BASIC DATA SET
Here are the top rows of the basic data set. There are 26 rows and 9 columns of data
in the complete file.
3
SORTING
Place yourself in the position of a business analyst or some other kind of quantitative
analyst for your organization. You are asked to produce a report with all nine columns
which just shows the males for the organization. One way to do this is to sort all of the
rows by gender (in this case, the field name is “Title” with males being “Mr.” and females
being “Ms.”). There can be several ways of doing this, but here is a simple way.
1. Highlight the entire data set.
2. Click on Data/Sort. Here is the dialog box that should come up. Change the settings as
appropriate.
4
MSC 300 Advanced Excel
Apply or click on the following settings.
a. On the top right, click on “My data has headers.” This will ensure that
the field/column names do not get sorted along with the data.
b. On the “Sort by” drop down, click the drop down arrow and select
“Title.” This will separate all of the Mr. and Ms. Employees.
5
c. For the “Sort on” dropdown, click the arrow and select “Values”.
d. For the “Order” dropdown, click the arrow and select “A to Z.”
e. Click on “OK.”
6
MSC 300 Advanced Excel
Your spreadsheet should now look something like below. The important thing is to
notice that all of the male employees are sorted to the top of the spreadsheet,
although the order of their names might be different than below.
To produce the report, you can simply delete all of the female rows and what
remains is the requirement, which can be printed out.
7
You may want to list the male employees in alphabetical order by last name.
1. Click on Data/Sort.
2. Keep the same settings as before, except click on “Add level” on the top left of the
dialog box. The second sorting row will appear as below. In the “Then by” drop
down, select “Name.”
3. In the second row, click on “Sort on” and choose “Values” and click on “Order” and
choose “A to Z.”
8
MSC 300 Advanced Excel
Here is what the spreadsheet should look like after this second level of sort and
with the females removed from the data set.
You are free to add many more levels of sorting as you see fit using this
same procedure for each new sort level.
NOTE: ensure that all of the data is highlighted and headers are also
highlighted before sorting. We want to make sure that all of the data stays
corresponding with the names, etc.
9
MSC 300 Advanced Excel
Review: produce a report sorted by job series (from A to Z) and then sorted by
salary level (largest to smallest), then sorted by last name (from A to Z). The answer
is on the next page.
10
MSC 300 Advanced Excel
11
MSC 300 Advanced Excel
FILTERING
There is another way to produce a report showing only males in the organization. This is
through a process called “filtering.” Sorting is sometimes the best way to support
generation of reports, and filtering is sometimes the best way.
Here is a way to produce a report using filtering to show only the males in the
organization.
1. Click on Data/Filter. The screen will change to look as below. Notice the new drop
down arrows at the top of each column.
12
MSC 300 Advanced Excel
After the new drop down arrows are shown, click on the drop down arrow for
“Title.” The drop down dialog box will come up, with (at the bottom of the
dialog box) “Select All,” “Mr.,” and “Ms.” being displayed. To show only males,
click off the “Select All” and “Ms.” so that the only check mark is by the “Mr.”
Although the names may be in a different order below than yours, the important
point is that only “Mr.” are shown on the report.
13
MSC 300 Advanced Excel
Several layers of filtering are possible. For example, filter showing only the females
from New York.
1. Click on Data/Filter to clear the current filtering.
2. Click on Data/Filter to start the new filtering.
3. From the “Title” drop down, click so that only “Ms.” is shown in the dialog box.
4. From the “Home State” drop down, click off all states other than New York.
The report is below, although the order of employees may be different than yours.
14
MSC 300 Advanced Excel
Review: filter the data set to show all employees from either New York or Virginia,
who are female, and in the organization’s software development area.
15
MSC 300 Advanced Excel
LOGIC STATEMENTS
Below is a report showing all employees from New York. This was produced
using filtering.
There is another way to generate a report showing all employees from New
York, which can be very powerful in certain circumstances. It is by using a
logic formula.
16
MSC 300 Advanced Excel
Here is the top part of the basic data set, sorted by “Name.” In cell J2, put in this
formula.
=IF(G2=”New York”,1,””)
Read this formula as “if the cell value in G2 is “New York,” then place a “1” in cell
J2, else place a blank in J2. We are looking here for certain character strings, which
are always placed between quotation marks. “New York” is a character string. The
“” at then end means “place nothing in the cell J2.”
17
MSC 300 Advanced Excel
Here is what the first row of the report looks like. Note that since employee is
from New York, a “1” appears in cell J2.
Next, copy and paste the J2 formula down all of the cells in column J corresponding to
the basic data set. The top of the spreadsheet now looks like this.
18
MSC 300 Advanced Excel
Now put a label in Column J’s header, and filter by “1” in column J. Here is the top
of the report, although names may be in a different order.
“Trick of the trade.” Many people do a standard copy/paste or a drag procedure to copy
the formula from J2 down to J27. That works. However, picture a situation where that
formula needs to be copied down, say, 80,000 rows. That can take time and is open to human
error. Instead, click on J2 and place the mouse cursor over the lower right cornet of J2. The
cursor should become a “+” sign. While the “+” is showing, double left click the mouse. The
formula will automatically copy down the entire column as long as the cell in column I has a
19
value in it. See the next slide.
Notice that there is no “Rank” value in Ms. Golf ’s cell. The automatic copying of cell
J2 stops at Ms. Foxtrot’s row. Manually, the formula I J2 had to be recopied/pasted
into Ms. Hotel’s cell in column J and then copied down the rest of the spreadsheet. So
do the automat copying, go to the end of the spreadsheet to make sure that the copying
was successful, and if not, then find the error and correct it. Here is the top of the
report, although names may be in a different order.
20
Produce a report showing all company members who are from either Delaware or Virginia.
Place a “1” in column J for all those who fit the criteria.
Here is the formula to use. Note that this is an “OR” statement. First click on the cell J2.
=IF(OR(G2=”Delaware”,G2 = “Virginia”), 1,””)
This formula is read as “if G2 = “Delaware” or if G2 = “Virginia”, then place a “1” into
the cell J2, else place nothing into J2. Here is the top of the report, although names may be
in a different order.
21
Produce a report showing all company members who are both from Virginia and have a
Bachelor’s degree. Place a “1” in column J for all those who fit the criteria.
Here is the formula to use. Note that this is an “AND” statement. First click on the cell
J2.
=IF(AND (G2=“Virginia”,F2 = “Bachelor’s”), 1,””)
This formula is read as “if G2 = “Virginia” and if F2 = “Bachelor’s”, then place a “1” into
the cell J2, else place nothing into J2. Here is the top of the report, although names may be
in a different order.
22
MSC 300 Advanced Excel
Produce a report showing all company members who are either both from Virginia and
have a Bachelor’s degree, or have a High School degree Place a “1” in column J for all
those who fit the criteria.
Here is the formula to use. Note that this is an “AND” statement. First click on the cell
J2.
=IF(OR(AND(G2=”Virginia”, F2=”Bachelor’s”),(F2 = “High School”)),1,””)
This formula is read as “if G2 = “Virginia” and if F2 = “Bachelor’s”, or if F2 = “High
School,” then place a “1” into the cell J2, else place nothing into J2. Here is the top of the
report, although names may be in a different order.
23
MSC 300 Advanced Excel
REVIEW
Write the formula corresponding to –
If the value in cell A3 is greater than 5, then place “OK” in cell J2, else place
“Error” onto J2.
Click on cell J2. Enter the following formula in it.
=if(A3>5,”OK”,”Error”)
24
MSC 300 Advanced Excel
Write the formula corresponding to –
If the value in cell A3 is either than greater than 5 or less than 0, then place
“OK” in cell J2, else place “Error” onto J2.
Click on cell J2. Enter the following formula in it.
=IF(OR(A3>5, A350,B2 = “Mr.”),”OK”, “Error”)
26
MSC 300 Advanced Excel
Write the formula corresponding to –
If the value in cell E2 is greater than 50 and the person is from New York, or if
the person’s Rank is Analyst, then place “OK” in cell J2, else place “Error” into
J2.
Click on cell J2. Enter the following formula in it.
=IF(OR(AND(E2>50,G2=”New York”),(I2 =
“Analyst”)),”OK”,”Error”)
27
ANCHORING
Please open the file of the Basic Data Set.
Suppose that we want to experiment with giving everyone in the company a percentage
salary increase. One way to do this is to add two column headers – “New” Salary and
Salary Increase. We want to see how the new salaries change with the proposed
increases. Cell L2 shows an increase of 5%. Cell K2 is the “new” salary, a possible
formula being =+L2*E2.
28
There may be several ways to finish the calculations, but let’s try this one. COPY and
PASTE the formula in K2 down the column to K27. Below shows the result.
What happened??? All new salaries below K2 are now $0. The formula for K2, copied
down, says to multiple the value to the right of the K column cell by the corresponding
cell in column E. Any cell below L2 basically carries a value of 0 for this example.
29
What is really needed is to, instead of the K column formulas referring to the cell to
their right in L, to refer only to the cell L2. This may make it easy in huge spreadsheets
to change a value in only one cell which will affect one or more long columns.
Instead of using the formula =+L2*E2, change it to =+$L$2*E2. What this does, when
copying and pasting the formula down column K, to refer not to the cell at the left of
the K cell, but only to cell L2. The first part, $L, means “only refer to column L values.”
The second part, $2, means “only refer to row 2 values.” Therefore, only the cell L2 will
be referred to. This method has the advantage that only the value in cell Ls needs to
change if one wants to experiment with different values.
30
MSC 300 Advanced Excel Including Pivot Tables
This procedure is called “anchoring” — in this case the cell L2 is anchored in the copying
and pasting.
It is also possible to just anchor a row, such as =+L$2*E2, or just anchor a column, such
as =+$L2*E2 – depending on the situation.
Anchoring can be extremely helpful in certain situations, so it is certainly recommended
when possible.
31
MSC 300 Advanced Excel
PIVOT TABLES
There are times when it is necessary to produce reports from a large
Excel file in the format of a table. For example, build a table from
the basic data set with the rows being gender, and the columns being
job series. The cells in the table represent the count of employees by
gender in each job series. Such a table might be as below. From the
table, we see that both the CEO and CFO are male, and the CIO is
female.
32
MSC 300 Advanced Excel
Although building such a table for this company and its 26 people should be
relatively easy, go back and imagine building a table from a data set of 80,000 rows
and 25 columns. Such a task can be undaunting.
Excel has a feature which builds such a table in a user-friendly manner. This is
called the “pivot table” feature. This feature can save huge amounts of time and
significantly reduce the chance for human error.
Please return to the basic data set. Start by sketching how the final table will look.
One such sketch is below. Note that the table has four sections: a blank, a gender,
a Job Series, and the Counts.
Table columns
Table rows
Table cells
33
MSC 300 Advanced Excel
Now go into the Basic Data Set and build such a table semi-automatically.
1. Bring up the Basic Data Set.
2. Click on Insert from the ribbon.
3. Click anywhere inside the Basic Data Set.
4. Click on the upper left corner of the ribbon “PivotTable.”
A dialog box as below will appear.
34
MSC 300 Advanced Excel
Notice that “Select a table or range” is automatically entered. This is the entire
data set.
Now tell Excel where you want the completed table to appear. I suggest for this
exercise clicking on “Existing Worksheet” and cell K7.
35
MSC 300 Advanced Excel
This is what the dialog box looks like at this stage.
If your dialog box is the same as this one, click on “OK.” The Excel screen
will change with the blank table on the right.
36
MSC 300 Advanced Excel
Note that the bottom part of
this dialog box has four
sections. These correspond
to the four sections of the
sketch from slide 29.
This is called the “field list”
dialog box.
37
MSC 300 Advanced Excel
Reproduce the sketch here.
For example, first go to
“Title” on the top half. Left
click on “Title” and drag it
down to the “Row Labels”
section. Your table should
look as it is here.
Then left click on “Job
Series” on the top half, and
then drag it down to
“Column Labels.” Finally.
Left click on “Title” on the
top half and drag it down to
“Values.”
38
MSC 300 Advanced Excel
Here is what the dialog
box should look like now.
If yours matches this,
then on the Excel
spreadsheet, slide over to
K7 area. The pivot table
has been built and all
calculations are finished.
See next slide.
39
MSC 300 Advanced Excel
This is what might be called a “raw table.” It needs some formatting to make
it presentable, but all of the heavy lifting has been done.
Foe example, it might be easier to read if all of the columns are centered.
Highlight the entire table, and click on the centering button on the ribbon as
you would center any columns.
40
MSC 300 Advanced Excel
Notice that there are two filtering arrows. Click on the filter for Row Labels.
This allows changing the table to show the results of the filtering. For
example, suppose we wanted to show only females in the company. Filter by
“Ms.” and the table will automatically change. On the column labels, filter by
“Software Development.” The result is a count of all of the females in the
Software Development area.
41
There are other format changes that can be made. Go back to the pivot table, go
to each filter, and then include all items (“Select All”) for each filter.
Note that the column labels are in alphabetical order. Suppose the table needed
to be completed with the CEO, CFO, and CIO occupying the first three
columns. We need to move each of those one column to the left.
42
MSC 300 Advanced Excel
Go to the CEO cell and left click. Then right click. A drop down will appear.
Find “Move.” Left click on “Move,” then left click on “Move CEO Left.” This
is what the pivot table now looks like.
43
MSC 300 Advanced Excel
Go to the CFO cell and move it to the left. Then go to the CIO cell and move it
to the left. This is what the pivot table now looks like.
44
MSC 300 Advanced Excel
Review. Build a pivot table, with centered columns, showing “Home State”
in the rows and “Title” in the columns. Have the cells of the table show the
count of employees by Home State.
45
MSC 300 Advanced Excel
So far the exercises have asked
for “counts” of employees. It
is also possible to do other
mathematical calculations such
as sums, (such as sums of
money), averages, etc.
Recall the field list dialog box
from slide 35. Go to “Count
of Title” in the bottom right
section and left click the down
arrow. In the pop up box, look
at the last entry “Value Field
Settings.” Left click that, and
you will see several math
calculations which are possible.
46
Produce a report of all
employees, by gender, and
the total salaries paid to that
gender (do not account for
the fact that there are more
females than males – just the
total salaries).
After completing the field
list dialog box, and clicking
on the Value Field Settings
and clicking on “Sum” the
field list dialog box should
look like this.
47
MSC 300 Advanced Excel
The pivot table should look like this.
If an abbreviated form of the table is wanted, giving just the grand totals, build the
field list dialog box, but do not put anything in the “Column Labels” section.
48
Review. Build a pivot table having the Job Series in the rows, and total salary paid in the
columns.
49
MSC 300 Advanced Excel
The last section of the table is
the upper left section, Report
Filter.”
Suppose the requirement
included the need to filter by
gender. Drag “Title” into the
upper left section.
The pivot table report will
change in format as on the
next slide.
50
Notice the top line “Title” and “(All)”
above the report. From the down
arrow one can filter for gender.
Suppose we needed to filter by female.
Click the down arrow from the top line
and select “Ms.”
Notice that the values in the “Sum of Salary in
$1000” have changed to reflect only female
salaries.
51
MSC 300 Advanced Excel
Review. From the Basic Data Set, build a pivot table showing the employee degrees
in the rows, and the average salary per degree in the columns, with the cells providing
the total average per degree type. Arrange the report so that “Master’s” is in the first
row, “Bachelor’s” is in the second row, and “High School” is in the third row.
52
Review. From the Basic Data Set, build a pivot table showing gender in the rows,
“Rank” in the columns, and average salary in the cells. Put in a filter and filter by
�…
Purchase answer to see full
attachment