Basic Skills Test

There are four basic skills that my undergraduate students in applied business statistics must complete in order to pass the class. They are also used in some other classes, including graduate, as required foundational skills. The word "skills" is used because this is more about the ability to mimic the steps than about understanding the concepts. Generally, all four skills will be tested on the same day so there is limited time. Therefore you should practice,practice, practice to make the steps automatic (again, that's why they're labelled "skills").

They will be graded pass/fail. You don't get credit for the skill unless you get all of it correct. The instructor will NOT provide any hints or instruction during the test. If you get any portion of a skill wrong, the instructor will go over your error(s) with you so that you know what to practice before your next attempt.

Basic Skill #1: Compute Basic Descriptive Statistics for Quantitative Data

You will be given a dataset with multiple variables. The data will be arranged in columns with the first row of each column containing the name of the variable. You will be required to compute all of the following statistics for one of the quantitative variables in dataset.

• Mean
• Sample Standard Deviation (There are different Excel functions for population and sample standard deviations. We’ll assume that the dataset is sample data.)
• Minimum
• First Quartile (There are multiple quartile functions in Excel. It doesn't matter which you use as long as you are consistent.)
• Median
• Third Quartile
• Maximum
• Interquartile Range

Here's a link to the instructional video and practice data for this skill. There's also a handout available using an older version of Excel and older version of the data.

Note: Unless stated otherwise, the instructional videos use Excel 2016 for Windows.

Basic Skill #2: Frequency Table and Bar Charts for Qualitative Data

You will be given a dataset with multiple variables. The data will be arranged in columns with the first row of each column containing the name of the variable. You will be required to do all of the following for a qualitative (i.e. categorical) variable:

• Create a Frequency Table (actual counts)
• Create a Bar Chart based on the Frequency Table. Since this will be a single data series, you need to remove the legend. You will also need to provide a reasonable title.
• Convert both the Table and Chart to relative (i.e. percent) frequency

Here are links to the instructional video. It uses the same practice data as Skill #1. (Here's a link to an older instructional video version and the older practice data.)

NOTE: The video uses Excel's Pivot Tables to create the frequency table. Since Pivot Table knowledge is important beyond statistics, I suggest that you learn it this way. However, a frequency table can also be created using Excel's COUNTIF function. On the test, you can use either method as long as you get the correct output.

Basic Skill #3: Frequency Table and Histogram for Quantitative Data

You will be given a dataset with multiple variables. The data will be arranged in columns with the first row of each column containing the name of the variable. You will be required to do all of the following for a quantitative (i.e. numeric) variable:

• Create a grouped Frequency Table (actual counts).  I will specify the starting point and class widths.
• Create a Histogram based on the Frequency Table.
• Since this will be a single data series, you need to remove the legend.
• You will need to provide a reasonable title.
• Since it’s a histogram for quantitative data, you need to remove all spaces between bars and add a border to the bars.

Note: The above items are covered in the Histograms Part 1 video. The next items are in the Histograms Part 2 video. The practice data is the same as for Skill #1 and Skill #2.
(Older version: Histograms Part 1 video, Histograms Part 2 video, and practice data.)

• Check to see if there are any classes that have no observations. Excel’s default is to simply not show those classes. However, statistical rules require that they show with a zero frequency. Pay careful attention to the video. The process to show empty classes will also show class both below and above everything. You need to turn those two classes back off.
• Convert both the Table and Histogram to relative (i.e. percent) frequency

NOTE: The video uses Excel's Pivot Tables to create the frequency table. Since Pivot Table knowledge is important beyond statistics, I suggest that you learn it this way. However, a grouped frequency table can also be created using Excel's FREQUENCY function. On the test, you can use either method as long as you get the correct output. Also, Excel 2016 introduced a histogram tool in Charts. It works quite well with skewed data. However, it's not used in this Basic Skills series because it doesn't handle relative frequency very well.

Basic Skill #4: Basic Regression for Two Quantitative Variables

You will be given a dataset with multiple variables. The data will be arranged in columns with the first row of each column containing the name of the variable. You will be required to do all of the following for two quantitative (i.e. numeric) variables:

• Create a scatter plot.
• Adjust the Title (I’ll probably just have you change it to your name)