Docstoc

Spreadsheet Use in Research Data Collection

Document Sample
Spreadsheet Use in Research Data Collection Powered By Docstoc
					 “Mission Critical” Spreadsheets in
     a Large Urban University

                        Janet Wagner,
                       Associate Dean
                       Miriam Crandall,
                Undergraduate Honors Program


INFORMS
Atlanta, GA 10/03
                    Background
• SPRIG Inspired Project
• Coming full circle with InfORMS




INFORMS
Atlanta, GA 10/03
                    Introduction
• Previous research into spreadsheets can
  be characterized as looking at various
  issues “in-depth”
• In contrast, this study is seeking an idea of
  the “breadth” of spreadsheet use
• Issue: across an institution, how are
  spreadsheets used and managed?


INFORMS
Atlanta, GA 10/03
                    Why a University?
• UMASS Boston is a comprehensive urban
  university with about 13,000 students and
  over 80 degree programs
• A complex managerial environment
• Lead author had recently been involved in
  the implementation of a revision of the
  entire undergraduate curriculum, which
  had been “fast tracked” into being
  implemented in one year instead of three
INFORMS
Atlanta, GA 10/03
       Basic Research Questions
• Who uses spreadsheets (looking
  comprehensively across the institution)?
• Where are there “mission critical”
  spreadsheets?
• Where do these spreadsheets come from?
  Who maintains them?
• What quality control exists for “mission
  critical” spreadsheet use?
INFORMS
Atlanta, GA 10/03
   Mission Critical Spreadsheets
• Spreadsheets that are an integral part of
  how functional area or department meets
  its goals
• Most likely used multiple times
• Often by multiple users (and/or data and
  analyses from them are used by multiple
  users)


INFORMS
Atlanta, GA 10/03
                    Literature Review
• There is some existing literature on use of
  spreadsheets
    – Chan and Srorey (1996)
    – Cox et. al., Waikato University
    – Wilkins (1992)
• Also some on process of using
  spreadsheets
    – See “Questionnaire and Interview” section in
      panko.cba.hawaii.edu/ssr/Survey.htm

INFORMS
Atlanta, GA 10/03
                    Literature Review
• More on spreadsheet errors
    – Information Integrity Research Centre,
      University of Greenwich (1996)
    – Business Week (1994)
    – Panko and Halverson (1995)
• See EuSpRIG and SPRIG websites for
  more


INFORMS
Atlanta, GA 10/03
                    Literature Review
• Software Engineering Techniques
    – For example: Pfleeger (2001) & Read and Batson
      (1999)
• Lifecycle Model
    –   Scope
    –   Specify
    –   Design
    –   Build
    –   Test
    –   Use
INFORMS
Atlanta, GA 10/03
                    Data Collection
• “Snowball” sample methodology
    – E-mail solicitation (using lead author’s
      contacts)
    – Focus on executive and top professional staff
• Initial E-mail solicitation
    – Are you a user/creator of “mission critical
      spreadsheets” and will you answer a few
      questions?
    – Who else should we talk to?
INFORMS
Atlanta, GA 10/03
                    Sample
• Contacted about 40 people total
    – via e-mail
    – via phone
    – via “walking around”
• 21 final respondents
    – 11 phone interviews
    – 1 face-to-face
    – 9 e-mail responses
INFORMS
Atlanta, GA 10/03
                    Round 1 Questions
•     What do you consider to be your mission critical
      spreadsheets. What do they do?
•     Where does the information in the spreadsheets go to?
      Up to someone? Down to someone?
•     Who created these spreadsheets?
•     If any changes were to be made to these
      spreadsheets, who would make them?
•     Are the same models used over and over again, or are
      they created new each time? If the same ones are
      used, how old are the models?



INFORMS
Atlanta, GA 10/03
                    Methodology
• Wrote up notes from phone and face-to-
  face interviews and had e-mail responses
• Coded responses
    – Was respondent from an administrative or
      academic unit?
    – What level?
    – Did the respondent create the MCS?
    – Etc.

INFORMS
Atlanta, GA 10/03
                                         Results
                                 Who Uses MCS?
                                                      Associate Vice Chancellor,
        Director of Admissions                        Administration and Finance
                                                      Associate Vice Chancellor, Enrollment
        Assistant Provost, Personnel                  Services
        Information Management Coordinator,
        College of Nursing                            Assistant, Computing Services
        Assistant Provost, Budget                     Assistant, Registrar
        Associate Dean, College of Science and
        Mathematics                                   Associate, Human Resources
        Assistant Dean, College of Liberal Arts       Director, Computing Services
        Dean of Science                               Vice Chancellor, Accounting and Finance
        Director of Auxiliary Services                Associate Vice Provost
        Associate Dean, College of Nursing            Assistant, Graduate College of Education
        Assistant Dean, Graduate College of
        Education                                     Associate Dean, College of Management
        Associate Vice Chancellor, Administration &
        Finance

INFORMS
Atlanta, GA 10/03
                                     Who?
                            Respondent Responsibility

                    14
                    12
    # Respondents




                    10
                     8
                     6
                     4
                     2
                     0
                         Academic Unit            Administrative Unit



• There are only 6 Academic Units (Colleges) so that may
  explain why academic unit usage seems small

INFORMS
Atlanta, GA 10/03
                                             Who?
                                        Respondent Role

                   12
   # Respondents




                   10
                    8
                    6
                    4
                    2
                    0
                        University   Unit Exec   Assoc Unit   Professional   Clerical
                          Exec                     Exec



• Time after time, we were referred to “one level down”
  from executive staff as the person with the “mission
  critical spreadsheets”.
• We were often told by executive staff something like
  “that information is very important but … gives it all to
  me”
 INFORMS
 Atlanta, GA 10/03
    What Is Done with the MCS?
                                Spreadsheet Usage




                                    Hiring, 2   Scheduling, 4
                      Student
                    Enrollment, 5




                       Faculty                       Budgets, 12
                      Reviews, 6




INFORMS
Atlanta, GA 10/03
                         Information Goes?
                                      MCS Info Use

                  12
                  10
  # Respondents




                   8
                   6
                   4
                   2
                   0
                       Info Used Up         Down?    Both?



INFORMS
Atlanta, GA 10/03
                    Who Created?
• Often didn’t get a very clear answer on this point
• Roughly the majority are created and used
  solely by the person we interviewed
• Sometimes we were told something like “I
  inherited it, I didn’t create it, I just use it”
• Only one instance where the spreadsheets were
  specified as having been handed down from
  above
    – From the one “Mission Critical” using Dean to the
      Associate Dean

INFORMS
Atlanta, GA 10/03
                           Who Changes?

                  8
                  7
                  6
  # Respondents




                  5
                  4
                  3
                  2
                  1
                  0
                      Changed by owner only   Changed by others




INFORMS
Atlanta, GA 10/03
                                      How Old?
                             14
                             12
             # Respondents




                             10
                              8
                              6
                              4
                              2
                              0
                                  New Each Year?   Reused?




• About half the reused ones were said to be 10
  years old or older
 INFORMS
 Atlanta, GA 10/03
             Who Didn’t Use MCS?
• One college (College of Public and
  Community Service). We’ll check again.
  (But they were the College formed in the
  ’60’s!)
• Facilities
    – Paraphrasing: “The University Peoplesoft
      implementation is intended to eliminate all
      spreadsheet use, and I am formally reporting
      to you that we use NO spreadsheets in
      Facilities”
INFORMS
Atlanta, GA 10/03
                     Peoplesoft
• The University is in the process of converting its
  entire administrative computer system to
  Peoplesoft --- an integrated information
  management system that includes:
    –   Finance (first to go, and currently live)
    –   Human resources (currently live)
    –   Prospect and admissions management (currently live)
    –   Student records (going live)
    –   Advising (still in planning)
    –   Alumni relations (still in planning)
INFORMS
Atlanta, GA 10/03
                    Peoplesoft
• One stated goal of Peoplesoft:
    – To eliminate all “shadow systems”
    – It says that on posters that are on the walls of
      all the Peoplesoft project rooms and
      conference rooms!




INFORMS
Atlanta, GA 10/03
                    Peoplesoft
• Sample (paraphrased) comments about
  Peoplesoft
    – We aren’t fully comfortable with Peoplesoft, so we
      keep the parallel systems to check it.
    – I download data from Peoplesoft, but do the analysis
      in my own spreadsheets because Peoplesoft lacks
      some reports I need.
    – “Yes I confess to using spreadsheets”. My staff
      doesn’t trust Peoplesoft and they prefer their own
      (old) spreadsheets. We enter the data into
      Peoplesoft later.
INFORMS
Atlanta, GA 10/03
           Round 2 (this semester)
• We are gathering a set of 6 “power users”
    – Academic and Administrative
    – All but one at the “Associate” level
• Intend to do a “focus group” with these 6
    – Repeat (with sharing) round 1 questions for more in-
      depth understanding
    – Discuss quality control practices
         • to understand
         • to push for more use of Software Engineering concepts
    – Consider on-going “spreadsheet users” group

INFORMS
Atlanta, GA 10/03
                    Conclusions
• Spreadsheets widely used for “mission critical”
  tasks
• Information from spreadsheets even more
  widely disseminated (and used)
• More operational use than we would have
  expected (as opposed to budgeting)




INFORMS
Atlanta, GA 10/03
                    Conclusions
• Spreadsheet “owners” concentrated at the
  “Associate” level
• Considerable use of spreadsheet generated
  analyses at executive staff level
• Same spreadsheets often used by multiple
  people
• Some spreadsheets have an amazingly long life
• Peoplesoft hasn’t (isn’t going to) eliminate all
  spreadsheet use

INFORMS
Atlanta, GA 10/03
                    Major Conclusion
Obviously this is still an anecdotal, pilot
 study, but that being said . . .

            Little evidence of systematic
            management of these critical
                 information resources

      (Should I be working to change that?)
INFORMS
Atlanta, GA 10/03
                    Future Work
• A College of Management Honors Student
  is planning to do his honors project by
  reproducing this study at a major asset
  management firm in Boston where he is a
  co-op student.




INFORMS
Atlanta, GA 10/03

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:60
posted:7/19/2010
language:English
pages:30
Description: Spreadsheet Use in Research Data Collection document sample