The goal of this exercise was to demonstrate how one can convert the existing static budget document into an automated budget book solution. After conversion is done, the Budget Department will be able to generate budget books faster and save the taxpayers money by optimizing the existing challenging budget formulation process. We built Budget in Brief application to be adaptive and transparent with the focus on ensuring completeness and accuracy of you final budget book.
We looked over many budget books. Among them we were searching for the clear budgets that can be easily readable by majority of citizen in different communities. As you know, budgets could get complicated in a hurry, to level, that the seasonal auditor would require help of a few budget analysts to find the way through the book.
Between many worthy budget we had chosen the City of Nanaimo budget.
Besides being the outstanding and beautiful community, the City of Nanaimo has put together well thought budget, which contains all important components, and as a result they have created very readable and transparent budget book.
What is most important is that the City of Nanaimo has also provided the very convenient way for its citizens to ask questions about the budget.
How to Prepare Budget Project?
One of the first important steps is to read the budget and grasp at the visible components. There are many things that book can tell. Many components you will see in tables and charts and a lot of helpful information are delivered by narratives. If you have questions, while you reviewing the budget, it is time to write them down, when you will finish the book many of them will be answered.
The original book contains the data for the prior fiscal year, the current fiscal year and for the next four years until 2015. There are exhibits, charts, tables, and many informative narratives with budget numbers.
After finishing with the book review we started to organize data.
The Funds Crosswalk
First we have creates the "Funds.xslt" spreadsheet, or we call them crosswalks, based on the available funds and reserve information in the book. The Funds spreadsheet has several dimensions:
Fund Type: the General Fund, the Sewer Fund and the Water Fund
Reserve Class: Non-Statutory Reserves and the Statutory Reserves
Reserve: the list of available reserves
Each dimension in the Funds crosswalk is spread by a particular budget year, delineating balances, additions, and withdrawals.
In the Total lines we have used standard Excel SUM function. After Funds spreadsheet was created we were ready to create automatic exhibit which generated Reserve Projection table.
The Main Crosswalk
Next in line was the main crosswalk “Crosswalk.xlsx”.
Staying on the side of the average citizen and with the belief, that the budget book is designed to have the explanation for the majority of numbers, we have moved along with an attempt to connect the numbers and their totals. Grasping the city budget from the highest City level, down to the Department level, and deeper to the Programs level, proved to be fun and challenging. The knowledge of the City of Nanaimo internal business process and help of the city budget analysts would be beneficial at this step, but the effort that city have put in to the book allowed us to get through this step with the minimal questions.
To add an extra clarity to the crosswalk we have separated the Expenditures from the Revenues, created Summary sheet, and included separate sheet for Charts.
The main crosswalk has several dimensions: Operating/Capital, Revenue Type, Department, and Program dimensions. Funds in the main crosswalk are spread by a particular budget year.
Once we got all the numbers connected in the crosswalk, we continued with the exhibit development. Exhibits were created pretty quickly and reused for each separate department. You will find exhibits code under “City of Nanaimo\Exhibits\XSLTs” folder.
After populating crosswalks with data, creating the exhibits, preparing charts it was ready for consumption by the book project.
Numbers and Narratives
The narratives bring the life in to the budget, they glue the number together and explain why or why not certain part of budget works, they providing explanation for programs, expenses and educating reader where funds are allocated to bring benefits to the community.
When all data was ready in the budget project, we have added narratives, table of contents, applied styling and a little bit of polishing.
Click! - and the book draft was ready.
There are many numbers in the book, some of them we left untouched (to give you the opportunity for an improvements), some numbers were auto referenced to the data in crosswalk. All referenced numbers will automatically adjust every time the crosswalk changes. Here are a few numbers:
Section 1 Last paragraph - Capital Expenditure Program.
Section 4 Last paragraph - Consolidated Revenues by Type, the Title of the corresponding Chart.
Section 6 Second Paragraph - 2011 - 2015 Capital Plan section as well as Capital Expenditures Program by Department Chart title has automatic reference.
Organizing the Book Project
The budget project were organized in has following folders:
Data – the folder where we store crosswalk.
Draft – the folder where you will find the generated book.
Exhibits - the folder for the XSLT style sheets that extract data for automatically generated exhibits.
Extract – the folder that will store the updated charts extracted from the crosswalks.
Narratives – the folder for the narrative documents.
Original – the folder that has the original budget document downloaded from City of Nanaimo web site.
Temp – temporary working folder where Budget in Brief application stores files while processing the book. In thus book project we have stored here the exhibits artifacts.
Templates – the folder with the style, headers, footer and templates Table of Content etc.
Let us mention that you might organize project in many different ways.
Why some charts were not automated?
For the some charts we could not find the data in the original budget book. And, since we have plenty of examples on how to work with charts we decided to leave some charts as static images. On the other hand any chart that can be produced outside of the Budget in Brief, can be simply included as an image in the book.
Why some data is different from Original?
This is the sample project that allows you to experiment with data on your own. Making the book project to look identical to the original and automating all the numbers calculation would take a little or no time. Please let us know what numbers and calculation you are interested in.
It is easy and cheap to convert any budget book into the Budget In Brief book project. In fact, it is easier and less resource intensive than with any other product available on the market today. After conversion is complete the book project can be emailed, copied, stored in content management system, carried on a memory stick. When the book project is ready, budget analyst could change the numbers, update the narratives and regenerate the new version of the book in seconds.