Project Objectives:
The purpose of this project is perform an in-depth decision-making analysis of complex and inter-related data using Microsoft Excel. The software we will be using for Assignment 1 is Microsoft Excel 2010, Excel 2013 or Excel 2016/365.
Project Tasks:
.Task1. Preparing for this Assignment .
Read through the entire assignment. Complete the pivot table tutorial posted to Bb/Assignment 1.
.Task2. Download the input file from Blackboard
1) From Blackboard, download the input file - cis310Winter2018Input.xls. This file is the starting point of your project, as it contains the initial survey data you will use to create the new workbook for the assignment.
Task 3. Create a new Excel workbook
1) Create a new Excel workbook and title it myWorkbook.xlsx. This workbook will contain multiple worksheets created in the subsequent tasks for this assignment. The completed workbook will enable Dr. Pryor to be more effective in evaluating his company's course offerings and instructor performance.
Task 4A Create a new Response worksheet in the workbook.
1) Create a new worksheet (titled Response) in the workbook (myWorkbook.xlsx) created in Task 3. This worksheet will contain survey results for a two-week period.
2) Copy data and paste it into the Response worksheet. Format the Response worksheet by including descriptive headings for each column and the following title: (note: The title should be located above all descriptive headings. The title should not be placed in a Header section, but directly onto the worksheet using the insert cells function [Right click the top-most cell>Insert...>Entire row). Insert a row for each line.])
Fred Pryor Seminars
Consolidated Survey Results
For September 2012 (Two-Week Period)
3) When the survey forms are returned from the survey respondents, Dr. Pryor's secretary will code and enter the individual survey results into the Response worksheet. When entering survey data into the Response worksheet, Dr. Pryor wants the results for each survey assigned a respondent number.
This requires the insertion of a new (left most) column titled Survey Respondent into the Response worksheet. For example, for the first 20 surveys, the surveys would be numbered 1 through 20, respectively. For the next five surveys, they would be numbered 21 through 25, etc.
You are required to add a new column (left most column) to the Response worksheet and title it Survey Respondent; assign a unique value, starting at 1 and incremented by 1, to each row of the new column to specify survey respondent number. Refer to the Exhibit 4 for a fragment of a partial data sample in the Response worksheet after the Survey Respondent column was added to the worksheet.
Exhibit. 4 Partial Sample of the left-most data in the Response worksheet.
|
Survey
Respon
dent
|
Course
No.
|
Locati
on
|
Instructor
ID
|
Class
Size
|
Seminar
End Date
|
Question 1:
PS
Attendance
|
Question
2:
Attended
Another
|
Other
columns
|
|
1
|
RTS1
|
1
|
3
|
20
|
09/15/2012
|
2
|
1
|
...
|
|
2
|
RTS1
|
1
|
3
|
20
|
09/15/2012
|
2
|
2
|
...
|
(note: other columns of the Response worksheet are not displayed in Exhibit 4)
4) Add a new column (right most column) to the Response worksheet and title the column Attended a Seminar Before. (See Exhibit 5 for a fragment of a partial data sample in the Response worksheet after the Attended a Seminar Before column was added to the worksheet as the right most column).
Exhibit. 5 Partial Sample of data in the Response worksheet.
|
Survey
Respondent
|
Course No.
|
Question 1:
PS Attendance
|
Question 2:
Attended
Another
|
Other
columns
|
Attended a
Seminar
Before
|
|
1
|
RTS1
|
2
|
1
|
...
|
3
|
|
2
|
RTS1
|
2
|
2
|
...
|
4
|
(note: other columns of the Response worksheet are not displayed in Exhibit 5)
Attended a Seminar Before column must keep track of whether a given respondent attended both, either one, or neither one of the seminars.
The possible values for this column are 2, 3 or 4. Do not enter these values manually! You must use an Excel function \WMJIH\hHH4XP QN Ialue for each row based on the following rules:
a. If a respondent did not attend the PS seminar AND did not attend a seminar offered by another company, value 2 must appear in the Attended a Seminar Before column for the given row;
b. If a respondent did attend the PS seminar AND did attend a seminar offered by another company, value 4 must appear in the Attended a Seminar Before column for the given row;
c. If a respondent did attend the PS seminar AND did not attend a seminar offered by another company, value 3 must appear in the Attended a Seminar Before column for the given row;
d. If a respondent did not attend the PS seminar AND did attend a seminar offered by another company, enter value 3 in the Attended a Seminar Before column for the given row.
Question: As you review Exhibit 5, why is there a value of 3 in the Attended a Seminar Before column for Survey Respondent #2?
Task 4B. Add results of four additional surveys to Response Worksheet.
Exhibits 6, 7, 8 and 9 provide the results of four additional surveys. Enter the results for EACH survey at the end of the Response worksheet. You will need to code the response data for Questions 1 - 8 using the survey codes in Exhibit 2. (The answers for each survey question on Exhibits 6, 7, 8 and 9 are underlined and are in bold red text). Other information for each of the four surveys is provided below.
|
1) For the survey in Exhibit 6, use the following information for the Response worksheet:
|
|
|
Location:
|
3
|
|
|
Instructor Id:
|
2
|
|
|
Class Size:
|
18
|
|
|
Seminar End Date:
|
09/22/2012
|
|
|
Questions 1 - 8:
|
See codes in Exhibit 2.
|
|
o
|
Attend a Seminar Before:
|
Derive a value as per Task 4A-4
|
|
|
|
(use Excel function)
|
|
2) For the survey in Exhibit 7, use the following information for the Response worksheet:
|
|
|
Location:
|
1
|
|
|
Instructor Id:
|
5
|
|
|
Class Size:
|
15
|
|
|
Seminar End Date:
|
09/22/2012
|
|
|
Questions 1 - 8:
|
See codes in Exhibit 2.
|
|
|
Attend a Seminar Before:
|
Derive a value as per Task 4A-4
|
|
|
|
(use Excel function)
|
|
3) For the survey in Exhibit 8, use the following information for the Response worksheet:
|
|
|
Location:
|
2
|
|
|
Instructor Id:
|
7
|
|
|
Class Size:
|
22
|
|
|
Seminar End Date:
|
09/22/2012
|
|
|
Questions 1 - 8:
|
See codes in Exhibit 2.
|
|
|
Attend a Seminar Before:
|
Derive a value as per Task 4A-4
|
|
|
|
(use Excel function)
|
Task 5. Create a new Summary worksheet in the workbook.
1) Create a new worksheet (titled Summary) in the workbook (myWorkbook.xlsx) created in Task 3. The purpose of this worksheet is to summarize data contained in the Response worksheet. For each question, the Summary worksheet should provide a count for each possible response. The count should reflect the number of times a particular response for the question was given.
For instance, the Summary worksheet should show how many "1" entries, "2" entries, and "3" entries appear in the Location column for the Response worksheet. For questions 3 - 8, Dr. Pryor wants to see their averages, modes, and medians displayed in the Summary worksheet. The Summary worksheet should also provide the average, mode, and median class size.
The content and format of the Summary worksheet must be based on the Summary Worksheet Layout in Exhibit 10.
IMPORTANT NOTE: Use Excel functions to derive appropriate values for each row/column of the Summary worksheet. Excel functions MUST be used to derive every value in the sheet to receive full credit (Note: the function used, not the value, must appear in the function bar when the cell is selected). No credit will be given to assignments in which Excel functions are not used to derive values in the cells of the Summary worksheet.
Task 6. Create several new worksheets (in the workbook) for data analysis.
4 Note: Depending on the version of Excel that you are using, you may or may not have the referenced chart/style/formatting (i.e. stacked column, etc), or specific graphs - select any from the ones available in your version of Excel.
Dr. Pryor wants to analyze the survey data at varying levels of detail. So far, you have entered the survey data into a Response worksheet and computed summarized results in the Summary worksheet. Dr. Pryor also wants to view data based on multiple conditions (such as satisfaction ratings for instructors by course). You have suggested to him that the PivotTable and PivotChart reports are very good tools for this type of analysis. He likes your suggestion and requested that you use these tools to provide answers to the following questions:
1) Which instructor was the most knowledgeable about the subject matter being taught? That is, which instructor had the highest percentage of strongly agreed responses (for Question 6) with respect to the total number of these responses given? Filter your PivotTable by Course No.
• Create a PivotTable report in the worksheet titled Q1InstrKnow.
2) What is the popularity of Pryor Seminar Courses? (Of the total number of students who have taken classes, give the percentages of the popularity of each course (in terms of the number of enrolled students) in comparison to all courses combined. Create a pie chart showing the percentage of offerings for each course as compared to the total courses offered.
• Create a PivotTable report AND a PivotChart report (Exploded Pie Chart) in the worksheet titled Q2CrsPopularity. Make sure to include BOTH, the PivotTable and PivotChart, on the same worksheet. Add a descriptive title to the PivotChart and format it with appropriate labels for a user-friendly presentation style. Be creative in formatting the chart.
3) How many respondents who have taken a seminar course with another company agreed or strongly agreed that they would take another course with Pryor Seminars?
• Create a PivotTable report in the worksheet titled Q3TakeAnother.
4) How did the students rate their instructor on the instructor's ability to provide timely content? For each instructor, provide a percentage for each response. (Each instructor's total percentage should equal 100%)
• Create a PivotTable report AND a PivotChart report (100% Stacked Column) in the worksheet titled Q4TimelyContent. Make sure to include BOTH, the PivotTable and PivotChart, on the same worksheet. Add a descriptive title to the PivotChart and format it with appropriate labels for a user-friendly presentation style. Be creative in formatting the chart.
5) For each course, how many students agreed or disagreed that the course was useful? Filter the report by class size, so that Dr. Pryor could analyze for correlations (if any) between the class size and the perception of the course usefulness. For example, Dr.
Pryor wonders if students in smaller classes perceive the information presented in the course as more useful to their job than students enrolled in larger classes.
• Create a PivotTable report AND a PivotChart report (clustered 3-D bar) in the worksheet titled Q5CrsUsefulness. Make sure to include BOTH, the PivotTable and PivotChart, on the same worksheet. Format the PivotChart report using the Layout and Design tabs as follows:
i. Add a descriptive chart title
ii. Use Chart Style 2 for the chart's design
iii. Show data table with legend keys
iv. Show primary vertical gridlines, and within that, show major and minor gridlines.
6) Create a PivotTable and PivotChart reports of your choice.
1) As a consultant to Dr. Pryor, it is one of your tasks to think above and beyond of what Dr. Pryor had asked you to do. What other reports and charts would help Dr. Pryor perform additional analysis of survey data? You must first come up with a problem statement. Your problem statement can be about anything related to the analysis of Fred Pryor's Seminar workbook data.
However, to receive full credit for #6, your problem statement must not be identical or very similar to the problem statements in questions #1 - #5. Enter the problem statement in cell A1 of worksheet titled Q6MyReport. Furthermore, make sure that the chosen chart type is relevant to the analyses of the problem statement. Next...
• Create a PivotTable report AND a PivotChart report (of your choice) in the worksheet titled Q6MyReport (include both reports in the worksheet). The reports must address your problem statements. Add a descriptive chart title and format it with appropriate labels for a user-friendly presentation style. Be creative in formatting the chart.