RETURN ON INVESTMENT (ROI) OF PUBLIC
LIBRARY CALCULATOR:
A H OW -T O G UIDE
Distinguished Research Professor
Donald W. King
Ph.D. Student
Songphan Choemprayong
School of Information and Library Science
University of North Carolina at Chapel Hill
Last Updated: April 17, 2008
1
ACKNOWLEDGEMENT
This Return on Investment (ROI) of Public Library Calculator is developed to follow the
study of Taxpayer Return on Investment in Florida Public Libraries (Co-Project
Investigator: Jose-Marie Griffiths and Donald W. King) under the commission of the State
Library and Archives of Florida.
The development of the Calculator has received the cooperation to test the calculator from
the following libraries:
• Carnegie Library of Pittsburgh, PA
• Philadelphia Free Library, PA
• Allentown Public Library, PA
• Central Brevard Library and Reference Center, FL
• North Palm Beach Public Library, FL
Sarah Aerni tested the Calculator at the early stage and developed the early version of this
manual.
Kathleen McClatchy provided the original template of the survey instrument.
INTRODUCTION
The Return on Investment (ROI) of Public Library Calculator has been developed to help
individual public libraries to conduct their own analysis. It is designed to work after
finishing data collection using paper-based questionnaires collected from in-library visit
survey. The calculation is based on contingent valuation economic analysis. Note that it is
recommended that all libraries to obtain a minimum of 400 responses to the library survey
in order to make this analysis work.
For more information regarding the calculation model, please consult “A Procedure for
Determining a Public Library Taxpayer Return on Investment (ROI)” document”. This manual
has been created intentionally for the project coordinator and others working on the
project. If you have any question or experience problem using the Calculator, please contact
the developer (songphan [at] email [dot] unc [dot] edu).
2
INSTALLATION
Prior to installing the calculator, there are few points you may need to know.
1. The minimum 512 MB memory is recommended. You may experience crash if you
do not have enough memory.
2. Microsoft Access 2003 or higher version (Note: The Calculator was originally
developed using Microsoft Access 2003 on Microsoft Windows XP SP2 platform.
However, it is suggested to run this program with Microsoft Access 2007 on
Microsoft Vista platform.
Note:
Word Processor and Spreadsheet are recommended if you want to customize
your report.
•
For best display, you should set your monitor resolution to a minimum of 1024
X 768 pixels. This optimizes the forms and formatting of the various tools and
•
forms.
The installation is quick and easy with three steps.
1. Download the Calculator file, if you haven’t already.
2. Place the Calculator file in the desired location on your computer.
3. It is recommended to rename the file in version, for example, roi-calculator-
master.mdb or roi-calculator-[date].mdb.
3
OVERVIEW OF THE CALCULATOR
After launching the Calculator, Microsoft Access will open directly into a main menu. This is
your primary spot for working on this project.
TIP:
When you open the file, you may encounter a Security Warning which says that the
file may not be safe and that the code may be harmful to your computer. In this
case, please indicate that it is fine to open the file.
For Microsoft Access 2007, the Security Warning may prompt on the top of the form.
The Calculator will not work until you enable the program.
Click “Options…” to open a security alert window. Choose to “enable this content”
and click “OK”.
Here is the main menu of the program.
4
The menu has three sections ordered by the consecutive step: 1) entering and updating
library constants and survey data, 2) adjusting survey data and 3) generating reports. Each
section contains buttons to perform specific tasks.
1. The first section, Enter/Update Data, includes four buttons that are used for
entering and updating data. It is a data management center. The buttons lead you
to tables and forms where you can input, edit or delete data that was collected in the
survey.
2. The second section, Adjust Data, is to be used for automatic data manipulation,
including replacing outlier data with maximum value allowed, recoding data fields
and properties to align with the calculation. There is only one button in this section.
The greater detailed explanation will be discussed later in this manual.
3. The three buttons in the third section, Report, will generate reports using the data
that have been entered and adjusted in the previous two sections. This creates final
reports which you can give to others or use for other reports.
At the bottom right of the menu, there are three buttons that you may need to know.
• “Outliers” button leads you to a list of preset maximum values of scale responses.
“Averages” shows the preset calculated values which will be used to replace screwed
averages from small number of response.
•
NOTE:
You do not need to change data in any of the above two sections. It benefits for
advance researchers which will not cover in this manual.
“Quit Program” is the main button to close down the calculator AND Microsoft
Access.
•
Note that you can also use the control button on the top right of the window to close
the application.
To quit the whole program
To close down the main menu, NOT the program
5
WORKING WITH THE ROI CALCULATOR
This section will guide you through how to use the calculator step-by-step. There are six
steps to work with the Calculator.
READING THIS MANUAL:
If you are familiar with Microsoft Access, you can just follow the headings and
bullets.
•
The tip boxes explain essential concepts and provide guidelines on how to deal
particular issues in detail.
•
1. ENTERING LIBRARY CONSTANTS
The first step of working with the Calculator is to define the scope of your project
and provide your information about your library.
• Click button on the main menu
The Library Constants form consists of 12 text boxes to collect information about
your project and data about your public library.
6
• Create Session ID
Firstly you need to create one Session ID for your project. The Session ID can be
generated using any combination of alpha-numeric characters. It can be up to 10
characters long.
You will be asked for Session ID every time you enter survey data and generate
reports. It is recommended to write down the Session ID.
TIP:
“Session” is referred to an analysis of ROI of a single public library or a single service
unit/branch or a library system or a library network for an operating year.
It is suggested to use the combination of the abbreviation of your library and the
year in which the survey was conducted for Session ID, for instance, PHLMain07 for
ROI of Philadelphia Free Library – Main Library in 2007, and 2009BrevLib for ROI of
Central Brevard Library and Reference Center in 2009.
If there is a possibility to merge the session later on (for example, main library with
branch libraries), it is recommended to set up a session for a smallest unit first.
Note that it is easier to merge than to split the sessions.
• Enter Your Library Name
• Enter Year in Which Survey Was Conducted.
Beside numbers, you can enter text in this field to provide the context of the year
(e.g. “FY 2003” or “Survey: Summer 2003/Annual Data: 2002”).
• Enter Annual adult (18 and over) in-person person visits.
TIP:
In case you do not differentiate visitor group by age, you may use the “Adult in-
person visits calculation” by click on “Adult in-person visits calculation” button.
Based on annual total number of in-person visits, you need to provide the
proportion of visits by children under 18 years old. The result shows on the third
7
textbox. Note that the form will NOT automatically update the annual adult in-
person visit in the Library Constant form. You need to write it down or copy and
paste the number in the Library Constant form.
• Enter Annual adult remote visits.
If your library has online presence or service but does not collect the
data on remote visits, enter -1 (minus one) in this field. In this case,
o
the program will calculate the number of annual adult remote visit
automatically.
If your library does not have online presence or service, enter 0
(zero) in this field.
o
• Enter the Remaining Data about Your Library.
These numbers are very important in the calculation. If you do not have
information in any field, enter 0 (zero). DO NOT leave them blank.
TIP:
You can add a new session by clicking on “Add New Constant Set”. The form will
save your previous record and prompt you to a new blank form. You can navigate
through “>” buttons.
In addition, you can also use Record Navigator at the bottom left of the form.
Go to First Record Add New Record
Go to Previous Record Go to Last Record
Go to Next Record
• Close the form.
To close the form and go back to the main menu, click Close button on the bottom
right of the form. In addition, you can use the lower X button on the top right of the
window to go back to the main menu.
TIP:
If you want to edit your Session ID on this form, it will automatically update Session
ID in other data entries (step 2 and 3).
8
2. ENTERING SURVEY DATA
After you receive completed in-library survey questionnaires, you now can begin
entering the survey data into the calculator.
There are two ways to enter/update survey data: table and form formats. For table,
you can see the whole dataset. It would take some time to learn the ergonomics of
the interface. After you are familiar with the interface, this approach would be quite
effective. However, it is easier to familiar with the survey questions by using form.
The form interface may be more intuitive than table. You can begin entering data
with form. After you are familiar with the survey structure, you can move to table
format. It is your choice.
ENTERING SURVEY DATA WITH TABLE
• Click button on the main menu
The survey table will be opened.
The headings are on the top of each column. Use scroll bars to navigate through the
whole form. Each survey questionnaire will be entered on one row of this table.
9
TIP:
The headings beginning from the third column, Q1, are representing question
number. The parenthesis indicates the unit of response. The alphabet after the
question number represents the order of item in a particular question. For example,
“Q3b (minutes)” refers to item b from question 3, drove, asking how many miles to
drive to public library.
• Skip Survey ID and begin with Session ID
The first column, Survey ID, is automatically generated for you, and it should be
unique for each row of data. The Survey ID will be generated after your first typing
in a record set. In addition, there will be a new blank row generated, prompting for
the next record.
TIPS:
Survey ID is not used for calculation purpose, but rather survey management
purpose. It is recommended to write down the Survey ID on each questionnaire.
Thus, you will be able to recall for future reference, if needed.
Although, by any chance, you have to delete an observation, the deleted survey ID
will NOT be recovered.
Starting in the second column, with Session ID, you will begin to enter the data from
the in-library surveys. Enter the same Session ID created in Library Constants form
for each row. If you do not enter Session ID or enter non-existing Session ID, the
Calculator will prompt you to change it.
• Entering survey data for each question
After entering the Session ID, you will begin to enter the data from each survey in
the appropriate column.
TIPS:
You can use Tab key on your keyboard to switch from column to column. This may
make data entry easier for you.
You can see the full question at the status bar located on the bottom left of the
window. When you move the cursor to any column, the status bar will show the full
text of the question asked.
If the respondent did NOT answer the question, please leave it blank. In addition,
for Question 27a and 27b, when the respondent chose “prefer not to say”, you have
to leave them blank also.
10
For multiple choice questions (e.g. Question 3, 6, and 10), there will be drop down
menu provided. You can use pointer or type the first few letter of the answers to
select from the available choices.
However, if the question asks respondent to check all that apply, the available
choices will be listed as headings. For instance, the heading Q7a refers to whether
or not a respondent visit a coffee shop. The available choices will be No, Yes, and
Not Applicable. If the box is checked, enter “Yes”. If the box is unchecked, enter
“No”.
For open-ended numeric questions (e.g. Question 1, 2, 4, and 5), if the respondent
entered zero (0) in the questionnaire, please insert zero (0) in the table. If the
respondent entered DK (Do Not Know), please leave it blank.
For questions dealing with time (e.g. Question 1, 5, 12a), you need to convert all
answers into minute.
For dollar values, the cents will be entered after the decimal point.
• Close the form.
To close the form and go back to the main menu, you have to click on the lower X
button on the top right of the window to go back to the main menu.
ENTERING SURVEY DATA WITH FORM
• Click button on the main menu
The survey form will be opened.
11
This one-page form contains all 27 questions. You can use the vertical scrollbar on
the right of the form to browse all questions. This form shows a single response at a
time.
• Skip Survey ID and begin with Session ID
Similar to entering data with table, you do not need to worry about Survey ID.
However, it is recommend writing down the Survey ID on the actual survey for
reference purpose.
TIP:
After entering Session ID in the form, the information about that particular session
ID, including your library name, year, and number of adult in-person visit, will show
up on the right. This is to check whether or not you enter the right Session ID.
• Entering survey data for each question
After entering the Session ID, you will begin to enter the data from each survey in
the appropriate field.
12
TIPS:
You can use the tab key to switch from field to field. This may make data entry
easier for you.
This form is structured like the paper version. Thus, you do not need to worry
about the headings unlike table version. However, a number of data entry rules are
still applied as follows.
If the respondent did NOT answer the question, please leave it blank. In addition,
for Question 27a and 27b, when the respondent “chose prefer not to say”, you have
to leave them blank also.
For multiple choice questions (e.g. Question 3, 6, and 10), there will be drop down
menu provided. You can use pointer or type the first few letter of the answers to
select from the available choices.
However, if the question asks respondent to check all that apply, the available
choices will be listed as headings. For instance, the heading Q7a refers to whether
or not a respondent visit a coffee shop. The available choices will be No, Yes, and
Not Applicable. If the box is checked, enter “Yes”. If the box is unchecked, enter
“No”.
For open-ended numeric questions (e.g. Question 1, 2, 4, and 5), if the respondent
entered zero (0) in the questionnaire, please insert zero (0) in the table. If the
respondent entered DK (Do Not Know), please leave it blank.
For questions dealing with time (e.g. Question 1, 5, 12a), you need to convert all
answers into minute.
For dollar values, the cents will be entered after the decimal point.
• Close the form.
To close the form and go back to the main menu, click Close button on the bottom
right of the form. In addition, you can use the lower X button on the top right of the
window to go back to the main menu.
13
3. BACKING UP DATA
Before moving on to the next step, it is highly recommended that you should back
up your data. After you have a complete set of data and after you update your data,
you should also do so. The back up can be done by copying and pasting the working
Microsoft Access file in Windows explorer and rename the file in versioning pattern
as described in the Installation section of this manual.
14
4. ADJUSTING DATA
This step will not produce anything life-changing. However, it is absolutely
necessary to complete all of these steps in order to achieve useful and accurate
results. Please follow these instructions carefully so that useful output can be
created for your library.
• Click ‘Adjust outliers’ button on the main menu
After you click the button, there will be a number of pop-up windows asking if you
are sure to update/change data. The number of pop-up windows is dependable
upon the number of questions that contain outliers.
If you are annoyed by these pop-up windows, you can disable the prompt windows
as following (for Microsoft Access 2003):
1) Go to “Tools” on the menu bar.
2) Choose “Options” which will open a new window displaying a
number of tabs.
3) Go to “Edit/Find “ tab
4) Go to “Confirm” box on the top right of the window.
5) Uncheck the “action queries” box
6) Click “OK” button
By this means, after you clicking “All-in-one Adjustment”, there will be no
notification whether the records have been updated or not.
These actions will not require any other action on your part, but it is an important
step to carry out in order to get the statistical analysis correct. The adjustment
includes recoding and changing numerous values in the survey data. It is essential
to note that this function could NOT undo. Thus, backing up your data is required in
the previous step.
In addition, every time you change, add, delete or do anything else to the data entry
in section 1, as described above, you MUST do this adjusting!!!!!
At the end of your project, before generating final reports, you should revisit
the “Adjust data” section again, just to be sure that all your data has been
adjusted properly.
15
5. CREATING REPORTS
In the Report section on the main menu, there are two sets of information required
to generate a report.
The first information required is the number of survey response. The calculation of
return-on-investment has a different approach for a survey that contains less than
350 responses. However, it is highly recommend collecting at least 400 responses
for more reliable results.
• Choose “Number of response”
The second information needed is report type. There are four types of report
available, including:
1) Detail Summary: It lists the calculation in item format and provides
detail of the calculation,
2) Short Summary: It shows the calculation with only main headings,
3) Executive Summary: It is a narrative version providing the great
detail and the meanings of the calculation, and
4) Detail in Item: each calculation element is listed separately. This is
for advance users or researchers only. Also it is available for a
survey that has 350 or more responses.
• Choose “Report type”
• Click “Submit” button
• Enter Session ID when it prompts
All reports require the Session ID to process. Enter the Session ID and then hit
enter.
Please be patient as it may take a little bit of time to generate the report.
16
A report shows all the calculations that have been done for your library based on the
survey data. You can directly print this report. It can be also exported to a word
document by choosing the “Export” command on the “File” menu. You can export to
Excel, HTML, etc. If you want to export to a Word document, you must use the “rich
text format” on the export menu.
17
MANAGING DATABASE
It is easiest and safest to make a copy of the master file that you received. Please
enter any data, constants, etc. into the copy and save the master in case there are
•
any problems with your project, or for future use.
When you want to create a new project, you can either create a new session in the
old Calculator file that you have already working on or begin with a new blank
•
Calculator.
If you want to merge two survey datasets from two Calculator files together, here is
the instruction.
•
1) Open the two files that you want to merge.
2) Select the file that you want to be the main file.
3) Go to the file that you want to move and click “Survey Data (Table)”
4) Select the starting records that you want to move by clicking on the record
selector (the button in front of the Survey ID data).
5) Press “Shift” button on your keyboard and click on the record selectors of
the ending record that you want to move.
6) You can hold pressing the “Shift” button and RIGHT click on any area of the
highlight.
7) Select “Copy” or “Cut”
8) Go back to the main file
9) Open “Survey Data (Table)”
10) RIGHT click on any column of a blank record.
11) Select “Paste”
12) The two survey datasets are now merged.
It is recommended to back up both files prior to and after merge.
After you have been working on the Calculator for a while, Microsoft Access keep all
catch within the file which makes the file unnecessarily large. For Microsoft Access
•
2003, you can clean and compress the data by going to “Tools” on the menu bar.
Then select “Database Utility” and “Compress and Repair Database”. For Microsoft
18
Access 2007, click the Microsoft Office icon located on the top left of the
window. Then select “Manage/Compact and Repair Database”. Note that it will
restart the Calculator.
19