Executive Summary
Lindon City is located just north of Orem, and has a
population size of roughly 10,500 residents. Lindon is a unique city in that it
is a full-service city, despite its small size. A full-service city is a city
which provides all of the services needed to operate a city, including fire and
police services. Lindon has a significant commercial tax base which allows it
to provide more services than other cities of similar size.
To help measure their service satisfaction levels, the city
put out a service satisfaction survey to those who interact regularly with
members of the city staff. The survey was placed on the front desks of various
departments in the City, and as visitors converse with a member of the city
staff, they are asked if they would provide some feedback about their
experience in the survey.
As these survey responses are collected, the City
Administrator asked me if I would come up with a way to analyze our collected
responses. To do this, I created an Excel model where survey responses can be entered
into Excel through help of a form. The user can then run statistics on the data
by clicking a button which will automatically recode the data and output some
statistics.
Since most of the surveys taken are paper surveys, I tried
to create an interface that would make input into the Excel spreadsheet as easy
as possible. When the user clicks on the “Input Form” button, a user form pops
up. The user form allows users to simply click the responses reported on the
survey, and once the user clicks “Enter,” the appropriate fields are
automatically populated in the spreadsheet. Once the user is finished inputting
survey responses, the user can close the form by clicking “Close.”
Once the user is ready to analyze the data, the user can
click the button “Statistics.” This button runs a macro that recodes the data
into a format that can be analyzed, and then a statistical analysis is
displayed in the spreadsheet. The macro works by looking at each of the values
that were input into the spreadsheet and assigns each value a specified number.
These numbers correlated to a typical Lickert Scale. Once each response is
recoded, the recoded numbers are populated in a separate spreadsheet. The macro
then opens the statistical analysis package included in the Excel software, and
selects the appropriate ranges to be analyzed. The macro then runs the summary
statistics package for each selected range in the recoded worksheet and places
the outputs in a specified worksheet to be displayed to the user.
No comments:
Post a Comment