Assessment of Spreadsheet and Database Skills in the Undergraduate Student

Jeanne M Baugh
Computer and Information Systems Department
Robert Morris University
Moon Township, Pennsylvania 15108

Abstract

What makes a student computer literate?  Since spreadsheets and databases are used in many applications, across many platforms, minimal skills in these areas are often seen as components of computer literacy.  This paper describes the process of the evaluation of undergraduate students in the area of spreadsheet and database topics. The students were tested at the onset of an Information Systems Applications course, a requirement in the University’s core curriculum.  The students were expected to have attained a certain skill set prior to this course.  The results of the assessment are discussed and changes to the curriculum are presented based upon the exam results.

Keywords: computer literacy, database, spreadsheet, assessment



1.  Introduction

A computer literate student should not only know how to use computer application software, but should also understand how computers work and how to manipulate them (Goldweber, 1994).  Topics taught under the title of computer literacy differ from school to school and have evolved over time with changing technologies. (Hoffman 2003)  Students currently entering college are much more experienced with computers than those who entered just a few years ago (Marquis 2002).  They are often very good at using a computer to surf the net, download music, use email, use word processing and play games.  In their minds, this makes them computer literate.  But using computers for decision support is for the most part beyond many students’ abilities.

There are approximately 5,000 undergraduate students enrolled at Robert Morris University, a private school in Pittsburgh Pennsylvania.  Of these, approximately 900 are Computer Information Systems Majors.  In the Spring of 2002, a course from the University core, CI310 - Information Systems Applications, came under fire by some in the University as being unnecessary.  They thought that students had already mastered the topics taught in the course.  This criticism came from various students, faculty and some administration. The course covered many information systems concepts, as well as decision support topics using spreadsheets and databases.  The prerequisite for this course was CI212 Principles of Information and Computing, a low-level application course, in which Excel and Access were taught, along with other Microsoft Office skills.  This was also a course in the University core.

The CI310 course description is as follows:
Information Systems Applications provides the student with an integrated perspective of technology and information systems used to support the operation of an organization. The course involves both an overview of technological concepts and practice using application software to enhance decision-making. Concepts addressed include: computer hardware and software, telecommunications and networks, the Internet and intranets, data management, and decision support systems. The student learns to support decision-making necessary to the modern workplace by completing a series of individual and group projects. The projects include case studies requiring the use of spreadsheet and database management software.

In relating CI310 to the IS2002 Model Curriculum, (IS2002, 2002) various topics from at least the following four courses were included in the course, though some were taught on a limited basis:

* IS 2002.P0 – Personal Productivity with IS Technology 
* “advanced software functionality to support personal and group productivity”
* “organization and management of data (sorting, filtering) via spreadsheets and database tools”
* “effective presentation design and delivery”
* “accessing organizational and external data”

* IS 2002.1 – Fundamentals of Information Systems  
* “application versus system software; package software solutions”
* “information security, crime, and ethics”
* “networks and telecommunication systems and applications”
* “application versus system software; package software solutions”

* IS 2002.3 – Information Systems Theory and Practice
* “information systems and organizational system; decision support”
* “roles of information and information technology”
* “human-computer interface”

* IS 2002.4 – Information Technology Hardware and System Software
* “Hardware, hard disks and other storage devices, video display monitors, device controllers, input/output” 
* “operating systems functions and types”
* “memory and file system management”

The discussion for IS2002.P0, from the Model Curriculum states “The course has both a theoretical problem solving component and an equivalent component of structured supervised laboratory experience.”  This is very close to the instructional approach taken with CI310.  Projects using Access and Excel comprised a great deal of work in this course and often the students worked in the computer lab during class time.

Students were encouraged to take both CI212 and CI310 during their first or second year of school.  But often students put off taking CI310 until their third or forth year.  Because of the CI212 prerequisite, when the students entered CI310, they were expected to be proficient in basic skills using both Excel and Access.  The University also often allowed students to transfer in, or substitute various courses to satisfy the CI212 requirement. Therefore, each semester, those teaching CI310 had a group of students with a wide variety of skill levels.

Top administration issued a directive to the Computer Information Systems (CIS) Department to reduce the University core from two CIS courses to one. This directive was also supported by University-wide core curriculum committee.  The enrollment in CI212 had steadily declined in recent years, so the logical choice for elimination was CI212.  Actually the CIS department was not against taking CI212 out of the core.  But what was to be done with CI310?  Many of the students professed that they were very experienced with spreadsheets and databases, and for that reason, should not have been required to take the course.  However, this view was not held by those who have taught this course, members of the CIS faculty.  Some students also argued that they should not have had to learn the Information Systems concepts, because these topics were not meaningful to them in their field of study.  Some faculty members in the CIS department proposed eliminating the software component of CI310 and just teaching the concepts, because they felt the concepts were the heart of the course.  This idea sparked many long hours of debate among the CIS faculty.  If CI212 was to be eliminated and the software was to be taken out of CI310, where were the students to gain their Excel and Access skills?
Other disciplines on Campus, besides Computer Information Systems, expected their students to have the spreadsheet and database skills as prerequisites for some of their courses (Marquis, 2002; Goldweber, 1994). For example, the Accounting Department expected their students to be extremely proficient in Spreadsheet work and the Education department expected their students to understand how to set up a simple Access student-grading database. Even though the software skills were seen as important across the curriculum, many in the University community felt that the students had already mastered them.  Some outside of the CIS department did not even understand that the course was more than just Excel and Access.  Perhaps the fact that the course is housed in the CIS Department and not in a Business Department accounts for the misunderstanding of the course content.  This should be investigated further, but is beyond the scope of this paper.  It is interesting to note that in the past, the Business Department has not incorporated much technology into their course content.

This paper deals only with an investigation concerning the software component of the course, Microsoft Excel and Microsoft Access.  Did the students have the database and spreadsheet skills? (Marquis 2002)  Were they as proficient with the software as they professed to be? (Veal 2001) A discussion concerning the concepts portion of the course and what the University community, outside of the CIS department, understands about the CIS courses in the University core is as area for further study.

Before deciding what should be done with CI310, it was agreed upon by the CIS faculty that some kind of assessment should be done to determine what initial Excel and Access skills students bring to CI310.

2. Spreadsheet and Database Literacy Pre-Exam

At the beginning of the Spring 2002 semester, a skills exam was given to students enrolled in CI310.  This was considered a pre-exam to assess their skill levels coming into the course. (Veal, 2001).  The exam was to be administered to all of the approximately 350 students enrolled in the course. This included many course sections being taught by part time instructors.  In most cases, the students were given class time to complete the exam. But, the design of the exam also allowed for the student to take it on his own time, outside of class.

The exam consisted of two parts, a spreadsheet section and a database section.  A copy of the Exam is as follows:

CIS 310 Pre-test
A.  Excel Section
* Open the file called “expenses.xls” 
* Save the file onto your home drive
* Make the following changes:
1. Enter four sources of expenses in column A (for example; rent, food, gas etc...)
2. Make column A wider to fit all of the text
3. Enter numbers under each month for each expense
4. Put in a formula to add each months expenses 
5. Format all numeric data to be currency with 2 decimal places
6. Insert a row above the month titles and enter your name in that row
7. Center your name and the title “Semester Finances” 
* Save the spreadsheet
* Close the spreadsheet

B.  Access Section
* Drag the database “college.mdb”  to your home drive 
* Open the database from your home drive
* Add the following two fields to the table “students”
1. age (integer)
2. gender (text – Male or Female)
* Add age and gender values for the existing records 
* Add five new records to this table
* Create the following two queries and save them
1. List all information for those students who are Female 
2. List the first and last name of the students who are CIS majors
* Use the Report Wizard to create a report containing all of the information in the “students” table
* Use the Form Wizard to create a form for the “students” table 
* Close the database

C.  Complete Test
* Create a new email message to: baugh@rmu.edu

Semester
Finances
 
 
 
 
 
Sept.
Oct.
Nov.
Dec.
Expense
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Total Expenses
 
 
 
 
 
 
 
 
 











Figure 1 Excel Spreadsheet


* In the subject line, write “ASSESSMENT TEST”
* Attach the Excel file “expenses.xls” from your home drive
* Attach the Access file “college.mdb” from your  home drive, Send the email

The spreadsheet section asked the student to modify an existing spreadsheet using Microsoft Excel.  The student was to carry out elementary skills including basic data entry and formatting (Summons 1996). A copy of the exam spreadsheet is shown in figure 1

The database section asked the student to modify an existing Microsoft Access database, which consisted of one table of student information.  The schema for the table called “students” is as follows:

Table – students
social_security_number 	text 9
	(primary key)
last_name			text 20
first_name			text 20
major				text 10
GPA				currency

The students were to perform what were considered elementary Access skills, such as simple queries and wizard use.

Once all required tasks on both the spreadsheet and database were completed, they were to email the modified spreadsheet and database as attachments for evaluation.  All spreadsheet and database tasks on the exam were considered to be simple in nature and ones that should be mastered by someone with basic competency in these areas (Goldsworthy, 1993).

As an incentive to completing the exam, each student was given an assignment grade of 100%, which was to be averaged into his final course grade.  Students taking the exam were to complete it during the first two weeks of their CI310 course.  However, many sections were being taught by part time instructors, and it was difficult to get this compliance from their students. Research has shown that part-timers are often disconnected from the "mission and spirit of the institution." (Gappa 1993). Perhaps they did not appreciate the importance of the exam to the Computer and Information Systems Department.  Perhaps there was a communication break down.  This bears investigating, and is a topic for further research, but beyond the discussion in this paper.  What ever the problem was, in some cases the students did not finish the exam until the third or fourth week of class.  It is important to note that spreadsheet topics were the first to be covered in the CI310 course.  Therefore, many of the students were using spreadsheets in their daily course work at the time of their exam completion.

After the exam was completed and evaluated, an email was sent back to the student with his exam results.  This email was presented by the student to his instructor, in order to receive credit for an assignment.  The instructors were not to penalize the student if the results of his exam proved to be unsatisfactory.  Each student was to be given a 100% for an assignment regardless of the outcome of the exam.

3.  Results

There were 322 students who took the exam, a 92% participation.  The CIS faculty agreed upon what minimal skills they would like to see from the students coming into CI310.


Figure 2  Pre-exam Summary Results


The rubric used for the Excel portion of the exam, was to issue either satisfactory or unsatisfactory in each of the following four areas:

1. Enter data into cells
2. Write formulas
3. Insert new rows
4. Format the data

The student must have scored satisfactory on all four of the above areas to have been satisfactory on the entire Spreadsheet section of the exam.  He must have made no major mistakes while completing any of the required tasks as detailed in the exam.

The rubric used for the Access portion of the exam, was to issue either satisfactory or unsatisfactory in each of the following five areas:

1. Add fields
2. Input data
3. Create query 
4. Create form (using wizard)
5. Create report (using wizard)

Again, to receive a satisfactory in each of the above, there must have been no major mistakes. To attain a satisfactory result with the entire Access section, the student must have had satisfactory on the first three items above.  He could also have an unsatisfactory on either the fourth of the fifth item, but not both.

Each spreadsheet and database was opened to evaluate the required tasks.  This proved to be very time consuming, but necessary, to be fair in the grading. The only feedback the student got was to communicate to him in a return email if each section was satisfactory or not.  Fifty-six percent of the students were satisfactory with Excel, but only thirty-two percent were satisfactory with Access.  The results are summarized in Figure 2.

The detailed results of the spreadsheet section are presented in Figure 3.  Most students were able to enter data, write simple formulas and insert a new row.  But 44% of the students could not format the data, a very elementary skill.  As stated earlier, these students were using Excel in their current course work at the time of their exam completion, which makes this result even more troublesome.

The detailed results of the Access section are presented in Figure 4.  The students performed much worse on the database section versus the spreadsheet section.  This result


Figure 3 Excel Detailed Exam Results


Figure 4 Access Detailed Exam Results


was not unexpected (Marquis 2002).  Even though databases are pervasive in the student’s everyday life, formal exposure to database software functionality is clearly lacking in the student’s own experience.
4. Conclusions and Curriculum Changes

Students were not as competent with spreadsheets and databases as they and some non-CIS faculty members thought.  The required Excel tasks on the exam were extremely elementary, and the fact that so many students could not successfully perform them is an indication that they needed more instruction and/or more practice with the software.  This is exactly what the course, CI310 provided.  Spreadsheets are used in many areas of study and the student will benefit from mastering this application software.

The results of the Access section was not a surprise to any of the instructors who taught CI310.  The results clearly point to the fact that the students had not mastered database tasks.  As with spreadsheets, databases are everywhere in the student’s life.  A better grasp of these topics will allow the student to apply this technology to their respective area of study.

Competency can be defined as “The ability to perform in the workplace.” (Goldsworthy 1993) To attain a basic level of competency with Excel and Access for the workplace, the students do need CI310 to strengthen or even introduce the spreadsheet and database skills.  The faculty in the CIS department realized that these skills must be taught in one of the their courses or another department would take over the task.

Some in the CIS department felt that because CI212 was being taken out of the core, we might end up with even more students who are deficient in spreadsheet and database work coming into CI310.  Those of us who have taught CI310 realized that trying to teach all of the concepts as well as the software to non-CIS majors became an almost impossible task for one course.  With more possible students not up to par with the software skills, the job could be even more difficult.  Again, we in the CIS department realize that the IS concepts are extremely important, but not all in the University community see these topics belonging in the core.  This difference of opinion should be investigated further.

Based on the results of the exam and the sensitivity of the Computer Information Systems Department to the needs and interests of the other majors, the following changes were made:

1. CI212  Principles of Information and Computing is no longer a required course in the core curriculum.  Database and spreadsheet topics do not compromise a majority of the course and many of the other topics are already mastered by students before entering College. However, if students are found to lack a minimal computer background when entering their first year of College or transferring in at a higher academic level, they will be advised to enroll in this course. (note – There was a numbering overhaul of all course offerings in the University)  The number on this course is now INFS1010, but the course name, description and content are the same.

2. Most of the information System concepts have been removed from CI310.  These topics will be incorporated into another Information Systems course for the CIS majors. There will still be a limited discussion of information technology and a general understand of computers included in the course content.(Chang 1999) (2000 Gehl) CI310 is now INFS1050, Fundamentals Of Information Systems.  The new course description is a follows:

Provides the student with an understanding of computers, essential computer hardware and software, and the impact of information technology on organizations and society. The complexity of designing effective information systems is discussed, and the student learns to compare, analyze, and evaluate information from various systems. The course involves both an overview of technological concepts and student practice in solving typical information-related problems. The importance of logical reasoning and solving specific technical problems is addressed by developing structured logic designed to be non-language specific.

3. INFS1050, Fundamentals Of Information Systems (formerly CI310) has been strengthened in the areas of database and spreadsheet work.  The removal of the intense study of CIS concepts will allow more time for decision support requiring advanced reasoning and functionality, using advanced case studies in Excel and Access.

4. INFS1050 will be the only required computer course in the University core.  This new core will be implemented in the Fall of 2003.

5. There will be two other versions of INFS1050 offered to students.  One course will cover specific topics for business majors and the other will cover topics pertinent to the medical field for the Nursing majors. Allowing flexibility with this course is seen in a very positive light among other departments on Campus (Learmonth, 2001).

This process of assessing what students know and what they should know was debated at many Computer Information Systems Department meetings.  Once the results from the assessment exam were presented to the CIS faculty, the task of restructuring the course topics led to a great deal of passionate dialogue.  Most of us in the CIS department did not want to see the concepts moved from the course and will continue to cover IS topics as time allows.  The agreement on the course content for INFS1050, comes from a collaborative effort of the CIS Department faculty members to structure a course we feel will serve the needs of the majority of the students in the University.  The CIS faculty also realizes that the computer and technological information students need to master is a dynamic area and must be reviewed at regular intervals in order to keep the University current with industry demands. (Barker 1994) Further study should be done to assess if the removal of INFS1010 (old CI212) from the core was the correct path to take.  Also, the consequences of taking the information systems concepts out of the required core course INFS1050, (old CI310) should be followed.

5.  References

Barker ,Donald I. (1994). “A Technological Revolution in Higher Education,” Journal of Educational Technology Systems, Volume 23, Number 2 

Chang, Carl., Gerald Engel, Willis King, Eric Roberts, Russ Shackelford, Robert H. Sloan, and Pradip K Srimani (1999). “Curriculum 2001: bringing the future to the classroom,” Annual Joint Conference Integrating Technology into Computer Science Education,Working group reports from ITiCSE on Innovation and technology in computer science education Cracow, Poland

Gappa, J.M., and D.W. Leslie (1993). “The Invisible Faculty,” San Francisco: Jossey-Bass.

Gehl, John (2000). “The future of the IT profession: an interview with Peter Denning,” Ubiquity Volume 1 , Issue 5, ACM Press, New York, NY, USA (March 2000)

Goldsworthy, A.W. (1993). “IT and the Competency Debate- Skills vs. Knowledge A Major Issue,” The Australian Computer Journal. 25(3):p. 113-122 education, Phoenix, Arkansas

Goldweber, Michael, John Barr, and Chuck Leska (1994). “A New Perspective on Teaching Computer Literacy,” Technical Symposium on Computer Science Education Selected papers of the twenty-fifth annual SIGCSE symposium on Computer science 1993

Hoffman, Mark (2003). “Computer literacy: today and tomorrow,” The Journal of Computing in Small Colleges Volume 18 , Issue 5 Pages: 221 - 233

IS2002 (2002). Model Curriculum and Guidelines for Undergraduate Degree Programs in Information Systems, 2002, curriculum effort of Association for Computing Machinery (ACM), Association for Information Systems (AIS), Association of Information Technology Professionals (AITP)

Learmonth, Rod (2001). “Flexible Delivery of Information Systems as a Core MBA Subject” Annual Joint Conference Integrating Technology into Computer Science Education, Proceedings of the 6th annual conference on Innovation and echnology in computer science education, Canterbury, United Kingdom

Marquis, Gerald P. (2002). “What is the Fate of the Computer Literacy Course?” ISECON 2002, 19th Annual Conference on Information Systems Education, San Antonio, TX

Summons, Peter., Jo Coldwell, Christine Bruff, and Frans Henskens (1996). “Automated assessment and marking of spreadsheet concepts,” ACM International Conference Proceedings of the second Australasian conference on Computer Science education, The Univ. of Melbourne, Australia

Technical Symposium on Computer Science Education (2002). Proceedings of the 33rd SIGCSE technical symposium on Computer science education, Cincinnati, Kentucky

Veal, D., S. P. Maj, and Rick Duley (2001). “Assessing hands on skills on CS1 computer and network technology units,” Technical Symposium on Computer Science Education Proceedings of the thirty second SIGCSE technical symposium on Computer Science Education