back

Knowing what I'd done with the time sheets and the PRS reporting workbook, one of my agencies came to me and asked if we could develop something in Excel to track and report Creative Curriculum assessments for infants, toddlers, and twos in the various day care centers for their United Way funding. While Creative Curriculum already had such a tool (developed with Visual Basic that took advantage of Excel and Access features) for the older children, the older version did not accommodate the infants, toddlers, and twos, and the newer version was prohibitively costly. So my agency wanted to keep using their current copy for the older children and develop something in house for the infants, toddlers, and twos.

I met with the Education Director. Although in the back of my head I was thinking database, I replied that we could try modeling a solution in excel (with a normalizing approach) and see how viable it would be. (And while I would have loved to try Visual Basic, I thought that would have been too long a learning curve.) I reasoned that since I had been doing more modeling in Excel recently and since the Agency was really familiar and comfortable with the Excel interface, doing an initial model in excel would be quick and less complex. I also suggested that we visit Head Start and look into the suitablity of ORS. Head Start used ORS to track and report on various goals and outcomes including Creative Curriculum assessments for older children - and our agency had a very old version of that software that had fallen out of use and that would need to be updated.

So we pursued both angles and looked for other partners in the agency who could benefit from ORS.

Modeling the project in Excel proved to be an ambitious project, one that pushed Excel and me to our respective limits in trying to achieve accuracy and ease of use. Here's how.

At the day care centers, teachers assess children 3 times a year - fall, winter, and spring - using Creative Curriculum's Assessment Tool - a paper-based booklet consisting of five goals and twenty-three objectives for each child. Through observation, the child is placed at one of five stages for each objective. For the older children, teachers enter children's scores into an excel spreadsheet and email the it to the Education Director who compiles and imports them into the Creative Curriculum software. We thought a similar process would work for infants, toddlers, and twos - however we would have to develop our own back end engine in Excel - a workbook that would pull in and summarize the data in the teacher workbooks.

I wanted to keep it as simple as possible and make it as accurate as possible, and as useful as possible - and yet as comprehensible and maintainable as possible for an agency with limited IT resources should I not be there to support the package.

I learned some things very quickly - relatively speaking. I wanted to keep the work down to a minimum - no duplicate data entry or cutting and pasting, and my thinking was informed but also colored about models I had recently developed for other applications and by the Creative Curriculum Tool in use for the older children. We started out naming the teacher workbooks after the teachers. This had some major design implications. First it meant that we would never know what a workbook would be named. Secondly, we would never know how many workbooks we would be dealing with, although it should have been pretty obvious. So I designed a solution where the Summary Workbook automatically built its own links. All the Director had to do was enter the teacher's name into the teacher list and the Summary Workbook would pull all the assessment data, demographic data, and class room information from that teacher's workbook for each checkpoint.

Pretty slick actually, except for two things ... to pull this off I had to use Excel's indirect function to build the links. Excel's indirect function requires all the workbooks to be open at the same time in order to pull in the data. The second thing was that I was having the Summary workbook summarizing most of the raw data for each checkpoint. With all the workbooks open and with even our small subset of test data (remember - 5 goals, 23 objectives, 5 levels per student), the Summary workbook slowed down to an absolute crawl as Excel performed millions of calculations each time I tweaked a cell.

So now I was faced with having to streamline the application. At this point (and various others throughout the experience) I could hear the voices of my IT instructors from college days asking me (sadly), "Did we teach you nothing?"

You see, I started my degree work when computer resources were scarce and one of the main objectives was to do the pre-planning and analytical work so that your program would run correctly the very first time. Don't get me wrong, top down analysis and design, and pre-planning is best practice for many reasons - including the boat load of time and money it can save you later. But it was singularly important to college students doing computer projects because you could only have an hour session before the system bumped you off and the next person in line could have their turn. And there was a line. On top of that during busy times like end of the semester, the system slowed down to a crawl - it might take an hour for it to compile and run your program. I know I'm talking about the good old days here - and fortunately, PC's had just entered the scene and cpu power rapidly became more available to us in the computer labs. But as I've said - top down analysis and design is always a best practice; while modeling, which is a very inductive process by contrast, is perhaps just now coming into its own. From my experience with this project, I have concluded that a mixed approach is desirable.

In my next iteration through this project, I offloaded a lot of the summarizing to the individual teacher workbooks. This had the advantage of also providing the teachers with summary information about their children and their classroom as a whole - which I reasoned would be more of a motivation, along with ease of use, for transfering the scores from the paper to the computer.

Essentially the teacher workbooks consisted of a class roster sheet with the students names, the number of checkpoints completed, and other information identifying the class. The teacher also created a tab for each child where she entered the assessment scores for each checkpoint. That work sheet also summarized scores so the teacher could see an individual child's development progress. Borrowing on my experience with the CDBG report and the Therapist Productivity workbooks, I had a summary sheet which counted and summed scores and demographic data across the student tabs to arrive at totals for the entire class room.

The Director's Summary workbook pulled in the summary information from each of the teacher workbooks and summarized and charted scores for the entire organization for each checkpoint. A Word Document hot-linked to the Director's workbook, and voila, the agency had their United Way report.

All well and good except for one thing. Excel was still very slow. I had made the suggestion to the Director that it might be a good idea to name the teacher workbooks after the center and the classroom they worked in so we wouldn't have to worry about teacher name changes mid-year. And it dawned on me. Number of classrooms was for the most part pretty set - it would fluctuate a lot less than number of teachers - and we could always pull in the teacher name. So there was no reason I could not create a workbook for each classroom and pre-enter those as direct links into the Summary Workbook. So, I did - and Excel went from being a snail to back to being the old Excel we know and love. Plus with the hard links there was no need to have all the workbooks open at the same time.

So that's how we ended up with an application that was fast, simpler, and fairly maintainable, after a few wrong turns, and I learned a little more about how to approach design projects! (Ouch)

We also spent time looking into ORS as well. We found another area in the agency who was interested in that product and we visited the Head Start Facility and talked to the vendor. However, ORS came from different roots. I believe it originated in the housing not-for-profit area (although I'm not sure about that). ORS had since expanded to accommodate measuring outcomes for all kinds of not-for-profits - but for our particular need it was not clear how easily it could accommodate the five stage Outcome structure used by Creative Curriculum for Infants, Toddlers, and Twos. For the price the Education Director was used to a product more specifically tailored to child development that offered a lot of satellite products and benefits as well. And while the inhouse solution didn't offer those, it had a look and feel that the Director liked and more importantly, the price was right.

I will comment on one feature in the classroom/teacher workbooks. While all infants, toddlers, and twos are assessed in a year's time, only the data of children who have completed all the checkpoints are included in the final report. The Creative Curriculum software for the older children offered this feature and the Education Director wanted it for the younger children as well.

The way I approached this was to have a beginning tab and ending tab in each teacher or classroom workbook. The workbook's summary sheet only counted and summarized the data on worksheets between those tabs. So if a child had entered (or left) the program mid-year, their tab could simply be moved outside that range.

So without further ado, here's a peek at the model:

While I don't recommend actually using the indirect linking version - because it is slow and because this version has not been thoroughly tested or corrected, I include it below as an example of indirect linking and how to automatically generate links.