Management Reception

Management Reception

The timely submission of assignments is your responsibility, and excuses — such as finding a long queue in the Computer Centre to print out — will not be accepted. You will receive an electronic submission receipt ID via email. It is your responsibility to keep this safe as proof of submission. You are also strongly recommended to keep a copy of all submitted assignments.

Your school district wants to evaluate teachers based on their students’ scores on standardized tests. In thiscase, you will use Microsoft Access and Microsoft Excel to create the evaluation system.
Your state’s education officials complain that virtually all the teachers in your school district are rated asgood or excellent by their school principals. There are surely bad teachers, state officials say, but they arenot identified and the problem is not addressed.
Therefore, the public school district has been instructed by state education officials to devise anaccountability system to identify good teachers and bad teachers. Good teachers would be paid more. Badteachers would be retrained so that they improve, or they would be fired. Fired teachers then would bereplaced by good teachers. In this way, officials believe that teaching would improve and students wouldlearn more.
In the new system, subjective evaluations by school principals will not be the only measure of teachersuccess. In addition to principal evaluations, an objective method is needed to measure teacher performance.Your state’s standardized tests are considered a reliable measure of student learning, which means that thetest scores could be used to help measure teacher performance objectively. According to some state officials,teachers whose students do well on the tests are doing a good job; poor test scores must be a sign of badteaching. The concept of matching teacher performance with student performance is highly controversial.
In your state, public school begins with kindergarten. Starting in first grade, elementary students take astandardized test at the end of each school year. The test has a reading section and a math section; thesummary score ranges from 0 to 1000. These standardized tests are given for grades 1 through 8; the testbecomes more difficult and comprehensive each school year.
Your district superintendent thinks that parental involvement is a key factor in a student’s motivationand learning. The superintendent wants parents to supervise their children’s homework, meet regularly withteachers, and be involved with the school. The superintendent thinks that children of “involved” parentsusually do better in school. In your district, K-8 teachers are required to note the extent of parentalinvolvement, and to report this information as a rating to the principal and the superintendent. Teachersare required to document these ratings by recording missed meetings, missed homework assignments,and so on. District officials think the ratings are reliable because they are documented and are rarelydebated by parents.
You have been asked to develop an accountability system that incorporates standardized test scores andparental involvement ratings. Your model will be a prototype. The model will use test score data for allsecond-grade students in the district. To develop a sample group for comparison, you have identified threesecond-grade classes in a district school. You have two years of data for students in these classes.
An Access database file namedTeacherEval.accdb contains your data.Use Windows Explorer to copy the TeacherEval.accdbdatabase, which is in the MN1505 subfolder within the Management folder on the pclabs (\\ourdata\teaching) (R:) drive, to your Y: drive.[If your Y: drive is full you may need to delete some of its contents to be able to do this].
The tables in the file are discussed next. Figure 1 shows the first few records of the Year1Students table.
Figure 1 Year1Students table records

The table shows each student’s ID number and indicates whether parents are involved in the student’seducation. Each second-grader in the district is assigned a unique student number. A lack of parentalinvolvement is indicated by the text value “YES” in the LackOflnvolvement? field. If the parents areinvolved, the entry is “NO.”
The district had 548 second-grade students in Year 1 of your model. A companion table calledYear2Students holds data for second-grade students in Year 2 of your model. The two tables contain twodifferent sets of students. By sheer coincidence, Years 1 and 2 had the same number of second-gradestudents, 548.
Your model will also include two years of student records for three second-grade teachers in a particulardistrict school. The teachers are named Smith, Jones, and Casey. Figure 2 shows the first few records ofthe Year1ClassAssignments table.
Figure 2 Year1ClassAssignments table records

The three teachers each had 23 students in their second-grade class each year. Each teacher’s class has aunique number. Here, teacher Smith’s Year 1 class was number 1. The table shows how individual studentswere assigned to each class. Each student is given a unique number, which is the table’s key field.
Figure 2 shows that teacher Smith had students 1, 2, and 10 among the 23 assigned to her. TeacherCasey’s class of 23 included student 5.
In your district, most students attend the school in their neighbourhood. Occasionally, parents requestthat their child attend a school other than their neighbourhood school, but such requests are not alwayshonoured. Within a school, the principal assigns students to a teacher; the teachers are not allowed to requestparticular students.
A companion table called Year2ClassAssignments shows how students in Year 2 were assigned to thesame second-grade classes used for Year 1. No students were “kept back” at the end of Year 1, so no Year 1students reappear in a teacher’s class in Year 2.
Each second-grade student in the district took standardized tests at the end of first grade, and again atthe end of second grade. Scores for Year 1 students are summarized in the table Year1StudentTestScores.
The first few records of the table are shown in Figure 3.
Figure 3 Year1StudentTestScores table records

Student l’s scores on the two year-end tests were 453 and 456. Each year’s test emphasizes maths andreading, and each succeeding test is more comprehensive and more difficult. Thus, Student 1 did not appear to progress greatly in second grade. Student 2’s second-grade score appears to represent a decline in ability.Student 3’s second-grade test score shows improvement over Year I , although the scores are not high ineither year.
The YearlStudentTestScores table has 548 records, one for each Year 1 student. A companion table,Year2StudentTestScores, contains records in the same format for second-grade students in Year 2 ofthe model.
Your model will use two criteria for assessing teacher performance.
1. Criterion 1: The average test score is computed for a second-grade class taught by one of the sample group teachers. The average test score is also computed for all second-grade students in the district. If the sample teacher’s class average is higher than the district average, the teacher is rated “good” for the year; otherwise, the teacher receives a “poor” rating for the year.
2. Criterion 2: Each student has a first-grade test score and a second-grade test score. The average test score improvement is computed for the sample teacher’s class and for all second-grade students in the district. If the teacher’s class has a better average improvement than the district’s, the teacher is rated “good” for the year; otherwise, the teacher receives a “poor” rating for the year. For example, if a teacher’s class had an average first-grade test score of 460 and an average second-grade test score of 474, the average improvement of the class is 3% (1.03 * 460 = 474). If the average district student showed a 2.5% improvement from the first-grade test to the second-grade test, the teacher would receive a “good” rating for the year.
You want to see if the two criteria produce the same ratings for a teacher in a year and if a criterionproduces the same rating for a teacher two years in a row.
In this task, you will design and run four queries in Access.
Year 1 Test Scores for All District Students
Create a query whose output shows Year 1 test scores for all second-grade students in the district. Youroutput should look like that in Figure 4. The query should generate 548 output records, although only thefirst few are shown. Name the query Year1ScoresForDistrict.

Figure 4Year 1 scores for all second-grade students in the district

You should also create a query for Year 2 test scores of all second-grade students in the district. Theoutput format is the same.
Year 1 Test Scores for Students in Sample School
Create a query whose output shows Year 1 test scores for all second-grade students in the sample school. Thequery should generate 69 records for the three teachers in your model. Your output should look like that inFigure 5; only the first few output records are shown. Name the query Year1TestScoresForSchool.

Figure 5 Year 1 scores for second-grade students in sample school

You should also create a query for Year 2 test scores of all second-grade students in the sample school.The output format is the same.
When you finish the queries, save and close the TeacherEval.accdb file.
In this task, you will import the output of the four Access queries into Excel worksheets and thendevelop information needed to rank the three teachers.
Importing Query Data
Open a new file in Excel and save it as TeacherEval.xlsx.
Import the YearlScoresForDistrict query output into Excel. Click the Data tab and then click FromAccess in the Get External Data group. Specify the Access filename, the query name, and where to place the data in Excel (cell A1 is recommended).
The data will be imported into Excel as an Excel data table, which is the format you want. If cell A1 is notalready selected, click it. In the Table Style Options group, select Total Row to add a Totals row to the bottomof the table. Rename the worksheet YearlScoresForDistrict. The first few rows of your worksheet should looklike Figure 6.
Figure 6 Rows in the Year1ScoresForDistrict worksheet

Import the Year2ScoresForDistrict query output into another worksheet. Add a Totals row and name theworksheet Year2ScoresForDistrict.
Import the YearlTestScoresForSchool query output into a third worksheet. Add a Totals row and namethe worksheet YearlScoresForSchool. The first few rows of your worksheet should look like Figure 7.
Figure 7 Rows in the Year1ScoresForSchool worksheet
Finally, import the Year2TestScoresForSchool query output into a fourth worksheet. Add a Totals rowand name the worksheet Year2ScoresForSchool.
Using Data Tables to Gather Data
You will use the data tables to gather data needed to evaluate teacher performance for the two years. Youcould also use pivot tables to gather the data; your instructor may require them instead. However, data tablesare probably more convenient in this case.
Before gathering data, you need a worksheet in which to manually enter data that you develop. Create anew worksheet named Summary. You can use any format that lets you compare one value to another. Forexample, the format shown in Figure 8 is acceptable.

Figure 8 Possible summary worksheet format

Most data for the summary worksheet will come from the data tables. Data in the Improvement columnwould be determined by an Excel formula in which one cell value is divided by another. The Criteria cellsshould include an explanatory comment; to enter one, right-click in the cell and click Insert Comment. The bottom section, “Lacks Involvement-Yes”, records counts of students whose parents lacked involvement ineach teacher’s classes during the two years. For example, Smith might have had 8 uninvolved parents in Year 1 and 12 in Year 2.
You should now gather data for the sample teachers’ classes and for the district, using the four datatables. As data is gathered, enter the values manually into your summary sheet. Keep the following points in mind:
• Click the arrow in the Totals row to select summary values for a column’s data, such as average, or counts.
• Column headings also have arrows. You can stratify data in a column by clicking a value on or off. For example, suppose that your worksheet showed people’s heights and gender. You could click to hide information for males, leaving just female data. Then you could use the Totals row to determine the average height for females. To restore the male data, you would click the heading arrow and then click Select All.
Fill in the Criteria cells after inspecting the data. Then consider the following eight questions:
1. What is the impact of parental involvement? Is it true that children of involved parents do better academically in this school district?
2. Using Criterion 1, are any teachers rated as good in one year but poor in the other? If so, does seem reasonable that a teacher’s ability changes from year to year? How can these rating changes be explained?
3. Using Criterion 1, does it appear that any teachers are classified incorrectly? In other words, are any teachers considered good when they actually are poor, or vice versa?
4. Is Criterion 1 a reasonable way to assess teacher ability? Why or why not?
5. Using Criterion 2, are any teachers rated as good in one year but poor in the other? If so, does seem reasonable that a teacher’s ability changes from year to year? How can these rating changes be explained?
6. Using Criterion 2, does it appear that any teachers are classified incorrectly? In other words, are any teachers considered good when they actually are poor, or vice versa?
7. Is Criterion 2 a reasonable way to assess teacher ability? Why or why not?
8. Based on the data, how would you describe the abilities of each teacher?
In this task, you write a memorandum in Microsoft Word that documents your findings. Your memoneeds tables that show teacher ratings by the two criteria for each year. The format of the tables is shown in Figure 9; you will also need a table for Year 2.

Teacher Year1 Rating by Criterion 1 Year 1 Rating by Criterion 2
Figure 9 Format of table to include in memo

In your memo, observe the following requirements:
• Your memo should have proper headings, such as Date, To, From, and Subject. You can address the memo to the district superintendent.
• Briefly outline the situation. However, you need not provide much background — you can assume that readers are generally familiar with your task — but you need to show that you have understood the situation and what is required of you.
• In the body of the memo, briefly describe the model you created. Be sure to state the two accountability criteria. List the ratings given to the three teachers, and refer to the tables that summarize the ratings.
• List the answers to the eight questions using appropriate data to support them. Then state your opinion of measuring teacher accountability by standardized test scores. If the method has problems, how do you think the problems can be minimized?
• The memorandum should be between 1000 and 1500 words in length.
The submitted and assessed part of this coursework is a business-style memorandum, rather than an academic essay. Thus, the marking criteria are different from those usually required for an academic essay. Your assignment will be assessed on the following criteria:
• Fulfilling the requirements of the brief
• Use of data/charts to support statements made
• Writing style
• Quality of presentation

I do not expect you to use in-text references nor to provide a bibliography/reference list at the end of the memorandum.
1. Your memorandum (printed copy and electronic submission via Moodle). It is this memorandum on which your mark will be based.
2. CD or USB stick which should contain your Excel spreadsheet file, and your Access database file (single copy only, do not submit these via JISC). These are submitted to prove that you have carried out the Access and Excel work yourself. They will only be reviewed if it is necessary to rule out plagiarism.

Please ensure that your disk/stick is clearly labelled with your Student ID number and that it and your printed report are placed in an envelope or folder so that they stay together and can be returned to you.