One key component of implementing Cycle-based Budgeting (CBB) is a technology system that allows the investments to be documented, tracked, and analyzed. Ideally, an online system that connects an institution’s financial data, outcome data, and its strategic plan should be used. Undoubtedly, it is a big and complex task to build, deploy and use such a system, which is probably why no vendor products are currently available in the market that serve the purpose. In JCPS, we developed an online system using the Microsoft SharePoint platform to implement CBB. This system has many nice features that render a good user experience (e.g., no login is required while on the district network, automatic notifications for conformation and updates), enhance data quality (e.g., front-end validation for errors and inconsistency), and reduce the amount of work for end users (e.g, pre-fill or auto-populate information). In CBB Technical Manual, I shared the process of how one simplified version of such system could be developed and deployed. However, that approach could be challenging to adopt and might not be the best option for many districts.

To address this issue, in another blog post, I introduced an Investment Tracking Form developed using Microsoft Word, a tool widely available in most school systems. However, that was only half of the solution because data collected from the form need to be exported for tracking and analysis purposes. In this post, I present how to use that form to collect what data and how the collected data can be exported into a CSV file using open-source software R for investment tracking and analysis.

INVESTMENT TRACKING FORM

The Investment Tracking Form can be deployed by: 1) putting it online on the district’s web site, 2) saving it on a share drive which people with authorization can access, or 3) sending it out to the intended audience through email. Because the form is a template file, a new Word document will be created anytime when it is opened.

The Investment Tracking Form has four sections: Glance, Budget, Target, and Goals (See Figure 1). Once the owner of an investment completes the form, he or she can send it back to the person who is providing the data and tech support for implementing CBB and hereafter referred to as “data strategist“. Please note that if the investment item is submitted as a budget request, the highlighted three fields will be completed by the data strategist after a decision is made on the request. If this Investment Tracking Form is used after budget decisions are made, these three fields can be filled out by either the owner or the data strategist.

Figure 1 Investment Tracking Form

After receiving a filled-out form, the data strategist should save it under the folder “C:\CBB\Investment Tracking”. If a different folder will be used, the data strategist needs to change the path accordingly in the R code presented below. For example, Figure 2 shows that three completed Investment Tracking Forms have been saved under the folder.

Figure 2 Completed Investment Tracking Forms Saved under the Specified Folder

Eventually, all completed Investment Tracking Forms should be saved under this folder. It would help to develop a naming convention so that forms from different departments in different years can be easily differentiated. For example, all files can be named following the convention of “FY_Cost Center_Owner.docx” such as “2018-19_Data Strategy_Bo Yan.docx”.

R Code

The R code (See Figure 3) is designed to export data from all Investment Tracking Forms saved under the folder “C:\CBB\Investment Tracking”. However, the code can be easily modified to export data for the desired forms only, such as forms from certain departments or years. Before running the code, you should have R and three R packages (“XML”, “data.table”, and “magrittr”) installed on your computer. This blog post Install R, RStudio, and R Commander in Windows and OS X will be helpful if you need a step-by-step guide on how to do it.

Figure 3 R Code

After running the code, a CSV file called “exp.csv” will be generated under the folder where all of the submitted Investment Tracking Forms are stored. There are four columns in this CSV file (See Figure 4):

1. 1. Column A “var”: variable name

1. 1. Column B “value”: variable value

1. 1. Column C “id”: unique ID number for an Investment Tracking Form

1. Column D “file”: name of the Investment Tracking Form file

Figure 4 Exported CSV File

In this CSV file, the exported data are stored in the long format where each row is a data point from a completed Investment Tracking Form. Depending on which fields are filled out, the total number of rows for each completed form can vary because empty fields are not exported. This data can be easily transformed into the wide format where all data points from an Investment Tracking Form are represented in one row.