- Hello, I am Professor Rick Jerz,
and I welcome you back to my Week One
reinforcement session of business analytics.
By now, you should have listened to my lectures
up to this one,
and hopefully you have begun doing assignments.
To some of you, you might be a little overwhelmed
with four chapters of material.
Maybe I could have combined some of these
into one or two videos,
but I thought it would better to keep the videos
separated by chapter,
so that you can more easily go back
and review specific chapter materials.
I think that you have seen
that I keep my lecture videos brief,
and I hope you have taken advantage
of this course content being accessible
on your mobile device.
Today, what I would like to do
is review what we have learned so far,
and to apply some of what we have learned
to a few more situations,
meaning more problems.
And to learn a little bit more about Excel
for data analysis.
Please don't skip these weekly review sessions.
I think that you will find some of the material
and topics that I cover
within these weekly reinforcement lectures
very exciting and useful.
Okay, let's get started.
My objectives for this session are
to review a few administrative items,
to review graphing data,
to review descriptive statistics calculations,
to review weighted data calculations,
and to introduce pivot tables.
I'm going to do this by solving problems.
You should refer to the week one
reinforcement web page
for information relating to this session.
By now, you should be getting a feel
for the overall course presentation.
Each week, I will provide you textbook readings
or supplemental readings.
You should watch my lecture videos.
You should do the self-assessments on Moodle,
and you should watch these weekly reinforcement videos.
By now you should also be getting the hang
of how all of this works
on my course management software product Moodle.
I think that you should be seeing that Moodle
is pretty easy.
I do want to mention to you that
if you ever have problems,
you have to let me know.
For example, maybe you were doing a self-assessment,
and you come across a problem
that you keep getting wrong.
What I would recommend you do
is to highlight this question,
copy it to your clipboard,
then start a new forum topic in the
"I need help with" forum for each week on Moodle,
and then describe the problem that you are having,
including what you have tried to do
that doesn't seem to work,
and paste this question from Moodle
into your discussion post.
By using this technique,
I and other students will see your question
and either I, or sometimes even other students
might jump in to help you out.
Moodle will email your original post
out to every student,
and it will also email the replies to every student.
Using this technique,
every student in the class can benefit
from questions that other students have.
Each week I will have a link to a new Excel file
on Moodle.
These files will contain the model for this week,
and they also contain all previous chapter models.
There is no need to keep the previous week's model.
This week's Excel file contains worksheets for graphing,
descriptive statistics,
and weighted data.
I gave you an assignment to create a variety of graphs
using M and M color data,
which represents nominal data.
On this first worksheet,
I'm giving you my solution to this assignment.
Yep, you will have my solution,
but try to do it on your own.
You might see that I got a little creative
with this particular
relative frequency diagram.
I took a picture of my M and Ms,
and added it as a background graphic to a chart.
I'm not expecting you to do this,
I just wanted to illustrate
how graphing can be creative.
In fact, I really don't care for this technique,
because sometimes it draws the reader's attention
from the data that you're trying to display.
Well, I have known situations where people
intentionally tried to do this.
Oh well.
On the next worksheet, I give you my solution
to the M and Ms weights assignment.
Again, make sure to try to do this on your own.
Usually the most difficult part of this assignment
is to get the weight data into Microsoft Excel.
This is because the weight data that I gave to you
is a graphic of the data,
and typically you will have to add this manually
into Excel.
In my lecture video on graphing,
I showed you how to create a combination chart in Excel.
I do want to mention to you
that in Excel 2016 for the PC,
there is now a built-in option
for a combination chart.
You might want to give this a try.
Let me now turn our attention to descriptive statistics,
and I will do some problems
using this descriptive statistics model.
Let's first do a problem with a small dataset.
The problem can be found on the week one
reinforcement web page.
The manager of a local Wal-Mart supercenter
is studying the number of items purchased by customers
in the evening hours.
Listed below is the number of items
for a sample of 30 customers.
Let's do only parts A and B.
We are to find the mean and the median
of the number of items.
We're also supposed to find the range
and standard deviation of the number of items.
I encourage you to pause this video,
and try to solve this problem on your own,
before seeing how I solved this problem.
If I wanted to, I could manually enter this data
into our Excel model in the green cells.
However, you might also notice
that I have given you an Excel dataset
containing these values.
It is always better to copy and paste information
if you have it in its original form,
instead of manually entering data.
Manual keyboard data entry can contain errors.
So instead, what I will do
is to stroke through the data that has been provided,
copy this to my clipboard with Control C,
or Command C on the Mac,
then go over to my descriptive statistics worksheet,
and instead of just pasting this data
into the worksheet,
I prefer to right click on the first cell,
and also do a paste special values.
This paste special values
pastes only the data into the cells
without disrupting the formatting.
If you ever have any data left over
from your previous use of this model,
make sure to delete it.
And I am done doing all the calculations.
I have found the mean and the median,
the range, and the standard deviation for this sample.
I have also calculated a lot of other statistics
that the author didn't ask us for.
Why didn't the author ask for more?
Well, the author thinks that the average student,
who has to use pencil and paper, or a calculator,
might struggle with this problem a little bit.
In our case, we are using Microsoft Excel,
and we could calculate all of these statistics
in no time at all.
We can spend more of our time
on interpreting the results.
The mean and the median are nearly the same.
The central tendency for most customers
is to purchase nine items.
For this sample,
customers purchased between four and 18 items,
or a range of 14.
The sample standard deviation is about 3.5,
and we will learn more about how to interpret
this statistic later in the course.
The box plot below shows the middle, or median value,
the beginning and ending values,
and it shows that 50 percent of the inner data
is between six and 12.
This box plot provides a good visual summary of the data.
Let's do another problem with a little bit bigger dataset.
Let's assume that we want to calculate
our descriptive statistics
for the M and M weight data.
How would we do this?
I encourage you to pause this video,
and try to solve this problem on your own,
before seeing how I solve this problem.
I can go over to the spreadsheet with the M and M data,
copy it, then return to this descriptive statistics
worksheet, and once again,
paste special values the data
into the appropriate cells.
When I do this, however, I have another problem.
The quantity of data that I have
exceeds the green cells.
What do I do about this?
This is a situation where the easiest thing to do
is to just redefine the named range.
Let me show you on the PC version of Excel.
I notice that my data goes down to cell B102.
Now, I got to formulas, name manager,
and click on the My Data Variable.
Right here, I will change the ending value
to be something bigger
than where my data ends.
Oh, maybe like B200.
That's it, I'm all done calculating
the descriptive statistics for this M and M weight data.
Once again, the calculations are easy.
We can sit back and interpret the data,
just as we did for the previous problem.
Macintosh users do not have an icon for name manager.
Instead, you have this tool called "Define Name"
that works very similar to the PC version of name manager.
Using Define Names in Excel
can be a very powerful technique.
With the M and M weight data,
you saw me copy it from another worksheet,
and paste it into this descriptive statistics worksheet.
With name ranges, I really don't have to do that.
I can reference data from other worksheets.
Let me show you.
On the PC, I pick name manager,
then My Data,
I'll delete what is here in this text box,
I then point to the M and M weight worksheet,
activate it, and I swipe through the data
on this other worksheet,
and then finally end what I am doing.
Of course, when you use this technique
to reference data somewhere else,
the data that is in the green cells
in the descriptive statistics worksheet is ignored.
Okay, let's do another descriptive statistics problem
with an even bigger dataset.
On the week one reinforcement web page,
I have provided you a dataset that shows transactions
by customer for approximately 8,300 customers.
Let's assume that your boss
has asked you to tell her something
about the order amount for these customers.
I encourage you to pause this video
and try to solve this problem on your own
before seeing how I solve this problem.
Okay, for us this means that we want to calculate
some descriptive statistics
and see what it tells us.
On my descriptive statistics worksheet,
all that I have to do is change the name range
from whatever it happens to be,
and point it to this transaction data.
This is almost identical to what I did
with our M and M data,
except in this case, the transaction data
is in an external file.
It is not in the same file as my models.
But this doesn't matter.
I go to name manager,
pick My Data, delete what happens to be there,
and then I point to this other transaction data file.
My technique is to pre-select
just a few of the cells of the amount data.
Then, since I know the data goes down to
approximately row 8300,
I just make a little edit right here,
change it to 8300,
and I am done.
What I would tell my boss is that the average amount ordered
is about 120 dollars.
The median is 100 dollars,
which somewhat suggests that we might have a few big orders.
The minimum order is eight dollars,
the maximum order is 380.
The standard deviation is about 86 dollars
from the empirical rule,
we would say that it is very unlikely
to have any order more than three standard deviations
from the mean,
meaning, more than 325 dollars approximately.
We might also look at the box plot for this data,
the scale looks a little bit strange,
but we can still see that 50 percent of the orders
are between 55 and 165 dollars.
We did these calculations pretty quickly using Excel
and my descriptive statistics model.
I hope that you can put this model to use
in your own work environment.
Let's now shift our focus over to weighted,
or grouped data scenarios.
From our week one reinforcement website,
here is our problem.
Bidwell Electronics Incorporated
recently surveyed a sample of employees
to determine how far they lived from corporate headquarters.
The results are shown below.
Compute the mean and standard deviation.
I encourage you to pause this video
and try to solve this problem on your own
before seeing how I solve this problem.
What I do is to go to my weighted data model,
and simply enter this data into my model.
Once entered, I can look at the summary of results
right below.
The weighted average or mean is 13,
and the weighted sample standard deviation,
since this is a sample, is 5.12.
That's it.
One problem with this grouped, or weighted data,
is that we lose the ability to calculate
as many descriptive statistics
as we do with raw data.
But sometimes you are provided this kind of data,
and we have to do the best with what we have.
Okay, let's return to this problem
that we saw a little bit earlier,
and now do parts C and D.
In part C, we are being asked to organize
the number of items into a frequency distribution.
You may want to review the guidelines in chapter two
for establishing the class interval,
and the number of classes.
We're also being asked to find the mean
and standard deviation of the data
organized in frequency distributions,
and compare this to what we calculated in part A.
Why are they different?
I encourage you to pause this video
and try to solve this problem on your own
before seeing how I solve this problem.
Below this weighted data model,
I give you another model
that can help us calculate these frequency tables.
Let me show how to do this.
I copy the data from the provided dataset,
and I paste it into this area of my model.
Remember I always like to do paste special values.
Using the rules from chapter two,
the model suggests that we should have five categories.
If you like this,
just go ahead and enter it in the green cell.
The model calculates an interval width of 2.8.
I don't like this,
I am going to set this to 3.
The model shows that the minimum value is 4.
I'm going to start my frequency table from 1.
Off to the right, this model creates a frequency table.
I can copy this data from this part of the model,
and paste it into the upper weighted data model
to calculate the mean and the standard deviation.
The mean is 9.5,
and the sample standard deviation is 3.55.
I can compare this to what I had calculated
when using raw data.
The mean was 9.1,
and the standard deviation for the sample was 3.566.
This difference between the two models
is the result of grouped data
losing some accuracy.
I recommend calculating descriptive statistics
from raw data whenever you have it.
Let's do one more problem like this.
Merrill Lynch recently completed a study
regarding the size of online investment portfolios
for a sample of clients in the 40 to 50 year old age group.
Listed below is the data.
We're supposed to organize the data into a frequency
distribution.
How many classes would you suggest?
What value would you suggest for the class interval?
Draw a histogram and interpret the results.
I encourage you to pause this video
and try to solve this problem on your own
before seeing how I solve this problem.
On our week one reinforcement web page
I gave you the data for this problem.
I copy this data, and paste it into the lower part
of my weighted data model
for creating frequency tables.
One problem that you can see again with this data,
is that I have more data
than I have allocated in green cells.
But in this part of the model,
I have also used a define name.
So all that I need to do
is change the definition of this named range
to include more cells, and my model is good.
The model suggests that I have seven categories,
and I will use this.
The model suggests a category width of about 142,
hey, I don't like that,
I'll use 150.
And I will start my frequency table at zero.
Off to the right, I have my frequency table.
I can use the data in this frequency table
to create a graph.
I left drag through the midpoint and count data,
and then insert a graph.
Sure, to make this chart look a little bit better,
I would have to spend some time formatting it.
But I think you can see how quickly one can do this.
The interesting thing of this particular data
is that it is definitely skewed to the right.
I would prefer to use the raw data
in my descriptive statistics model,
instead of having somebody group the data
and giving it to me.
I'd like to spend a moment
showing you one more technique
that can be used to create frequency tables.
It is called PivotTables.
We have learned how to use the frequency function
to tally, or group data into bins.
This frequency function works with both interval
and ratio level data.
It does not work with nominal or ordinal data.
Let me show you what I mean.
I'm going to try to use the frequency function
to count up the colors of my M and Ms.
If I try to do this with the frequency function,
it simply doesn't work.
But the tool PivotTable does work.
PivotTable can do both simple and complex tallying.
It is a complex Excel tool
that oftentimes is avoided by many people.
It works in Excel 2010, 11, 13, and 16.
In fact, PivotTable has been around for a long time.
It goes back to 1994, with Excel 5 in Windows.
Let me show you how we can use it to tally
our M and M colors.
The technique is very similar in all versions of Excel.
The first requirement is that your nominal data
must have a heading.
I will make my heading be called "color."
Position your cursor within any cell within your data.
Then, on the Insert ribbon, click Pivot Table.
This dialogue box suggests the data range,
and usually it guesses correctly.
The default place to put your PivotTable
is on a new worksheet,
but I am going to put my PivotTable
right next to the data,
so that we can see both our data,
and the results of the PivotTable.
Excel provides a little graphic showing you
where this PivotTable will be positioned.
When you are positioned on the PivotTable
a right dialogue box will appear.
This dialogue box looks a little complex, and it is,
but we are just going to use it in a very simple way
to tally our nominal data.
I'm going to drag our heading, which I called "color,"
to the row labels area.
I am also going to drag the word color
to the values area.
If you look at this PivotTable,
you will notice that it has already completed
exactly what we want it to do.
It has tallied our nominal data,
and given us a count for each color.
It's as simple as this.
We can then use this data to create a frequency diagram.
Okay, we have seen how PivotTable can be used
to tally nominal data.
PivotTable also has the ability to tally
interval and ratio data.
Let me show you how to do this
with our M and M weight data.
My weight data already has a column heading
called "all data."
So it is in a form that PivotTable
should be able to recognize.
Let me give it a try.
I'm going to insert PivotTable,
and sure enough, my entire set of M and M weights
is seen by this dialogue box.
I'm going to put this on the existing worksheet,
in a cell where I will be able to compare it
with the results of our frequency function.
I drag and drop the field all data
into the rows label area,
and then I also drag it once again
into the values area.
I change the values aggregation method
from sum to count.
At this point PivotTable is simply counting up
the value of each weight of M and M.
We still need to put this data into logical groups.
I can do this by first clicking on one of the values,
and then on the PivotTable options ribbon,
I will click on the group field icon.
What we see is a grouping dialogue box
that is very similar to what we've learned about
how to group continuous data.
We need a starting point, an ending point,
and an interval.
Let's use .825 as the starting point,
.025 as the interval,
and 1.05 as the ending point.
There we have it,
a frequency table
that is identical to the frequency table
that we generated using the frequency function.
How about ordinal data?
Remember, ordinal data is very similar to nominal data,
except that it does have some order to it.
For example, on the week one reinforcement web page,
I am giving you a link to some exam grade data.
What I want to be able to do
is to tally the number of letter grades for this data.
However, instead of me doing this,
I'm gonna leave this as a challenge for you.
But here's the twist.
When you eventually get your PivotTable for grades,
you wanna make sure that the order is from A plus,
to A, to A minus, B plus, to B, to B minus,
and so forth.
The challenge is, how do you do this?
I'll give you a little while to try to do this
as a bonus exercise,
and later on this semester,
I will revisit this task.
So as you can see, we now have two methods of tallying
interval and ratio data.
One method is by using the frequency function
and the other method is by using PivotTable.
Let me mention a few advantages and disadvantages
of each of these approaches.
One advantage of the frequency function
is that when the data changes,
the tally is immediately updated.
A disadvantage of the frequency function
is that it only works on interval and ratio data.
An advantage of PivotTable
is that it can tally nominal, ordinal,
interval, and ratio data.
Another subtle advantage of PivotTable
is that it does put border data,
for example .875, into the proper bin.
You might remember that we had to modify the bins
very slightly for our M and M weight data
with the frequency function
in order for it to handle these borderline cases properly.
An interesting disadvantage of PivotTable
is that when the data changes,
for example I make one of these colors go from blue to brown
the PivotTable will not update.
You need to manually refresh the PivotTable
in order for it to see these changes.
Well, I am done.
You might need to replay this week one reinforcement video
and practice some of these techniques.
Thanks for taking some time
to spend a few moments with me.
I hope you have enjoyed what I have shared with you.
If you ever have any questions, or need my help,
please let me know.