This quarter, I had the opportunity to intern at the Colorado Department of Transportation as a Budget Analyst. This public organization is one of the twenty-eight departments under the Government of Colorado, and its primary purpose is to execute state transportation responsibilities in creating a safe and multi-modal transportation system for Colorado. It is a billion-dollar institution that incorporates engineering, maintenance, and financial aspects to achieve its goals. Its core values are safety, integrity, excellence, people, customer service, and respect. The Colorado Department of Transportation has more than 3,300 employees that are located throughout the state amongst five different regions. Region One is located in central Colorado around the Denver area, Region Two encompasses most of the counties in the southeast area of Colorado, Region Three covers the northwestern portion of Colorado, Region Four is comprised of the northeastern area of Colorado, and Region Five includes all of Colorado’s other counties in southwestern Colorado. Most of the Colorado Department of Transportation’s employees work at the headquarters location, which is in downtown Denver. Headquarters is considered to be in Region 1, and this is where I worked and will continue to work for the duration of my internship.
When looking at the Colorado Department of Transportation’s numbers, it can be noted that the organization manages 3,447 bridges, 9,146 miles of highways, and 27 billion vehicle miles of travel across the state. There is also an Aeronautics Division under the Colorado Department of Transportation that oversees most of the smaller airports across the state, as well as a Transit and Rail division that plans, develops, and finances rail services in Colorado. The Colorado Department of Transportation solely manages highways and interstates that stretch across long ranges of Colorado; therefore, smaller roads in the cities and in the counties are ultimately managed by the local governments in those areas. As a result, the Colorado Department of Transportation ends up managing only about 914 miles, or 10% of the roads in Colorado. This may not seem like much, but those 914 miles of interstates and state highways are host to 40% of all travel that takes place in Colorado.
There are thirty divisions and two enterprises within the Colorado Department of Transportation, and the one that I worked in during my internship was the Division of Accounting and Finance. This division’s main goal is to protect and manage the flow of the taxpayer’s money through the whole Department of Transportation by making competent economic decisions while adhering to state and federal financial provisions. The Division of Accounting and Finance is then divided up further into five different entities: Accounting, The Business Office, Business Processes, The Office of Financial Management and Budget, and Procurement.
As a Budget Analyst Intern, I resided in The Office of Financial Management and Budget. This office’s specific goals entail forecasting revenue streams, controlling the Statewide Transportation Improvement Program, and developing each division and enterprise’s budget. A lot of employees’ job duties in this sector of the Division of Accounting and Finance include reviewing contracts, common policies, and budgetary procedures to provide financial services and federal aid to construction projects. There is also a substantial amount of analysis involved in creating the budget for each fiscal year (July 1st- June 30th), including looking at previous expenditures in each division, new and developing construction projects, as well as current inflation rates in the economy. At the end of each fiscal year, The Office of Financial Management and Budget provides several fiscal notes and revenue forecasts as well as the final budget for each division for the next fiscal year. These reports get sent to both internal stakeholders and external stakeholders, with the Transportation Commission being the largest of those entities. The Transportation Commission is a commission of eleven people who are appointed by the Governor and whose responsibilities include forming policies on management and construction as well as endorsing the budget created by The Office of Financial Management and Budget. The Office of Financial Management and Budget as well as almost all of the divisions in the Colorado Department of Transportation make frequent contact with the Transportation Commission.
There were several goals and duties I had to fill as an intern in The Office of Financial Management and Budget at the Colorado Department of Transportation. 70% of my work entailed budget analysis and reporting, meaning I had to analyze each division’s budget request according to their expenditures in previous years as well as their budget requests in past years. I frequently pulled this information from SAP, which is software created by a B2B company to help businesses manage their operations and customer relations. This portion of my work also included reviewing and updating The Office of Financial Management and Budget’s revenue models in Excel. Their main revenue model was created by Peter Mills, a professional engineer and economist from the company Perrin, Thorau & Associates LTD. It inputs several different transportation factors, like registration fees, fuel taxes, and GDP factors, to predict the Colorado Department of Transportation’s revenue in the coming years. It has been my responsibility to update some of the numbers located in this model as well as make sure the worksheets are linking to each other correctly.
After performing work in the budget analysis and reporting section, the next 20% of my work came encompassed the preparation of materials for the Executive Management Team as well as other division managers. The Executive Management Team is comprised of the Chief Financial Officer, the Chief Engineer, and the Director of Policy and Government Relations. Together, they are responsible for most of the decisions at the Colorado Department of Transportation regarding finance and production. It was my duty to assist the Budget Director in preparing memos, PowerPoints, and other important documentation for the Executive Management Team and other managers to help inform them of key budget information. I also oversaw document version control in the division’s public drive, as there were several Word documents, Excel workbooks, Google documents, and Google sheets being used to manage information for The Office of Financial Management and Budget. I would often insert comments and notes into these documents to facilitate communication between the budget office and other divisions. Lastly, the preparation of materials included assisting The Office of Financial Management and Budget in creating a monthly packet for the Transportation Commission. The Transportation Commission requires documentation from the budget office each month regarding revenue projections and any recent budget modifications, like passed bills or roll-forward amounts from the previous fiscal year. This monthly packet aids the Transportation Commission in making important decisions regarding the Colorado Department of Transportation’s funding for coming years, which ultimately affects all taxpayers in Colorado through gas taxes and vehicle registration fees. Documentation is very important in business and is essential to making critical business decisions.
The final 10% of my job duties included miscellaneous office tasks, such as attending staff meetings and events, performing policy research, and reading organizational material. Each week, The Office of Financial Management and Budget had a staff meeting to discuss projects that each team member was working on, current news regarding transportation issues in Colorado, and any other new information the Budget Director had for the team. In addition, the whole Division of Accounting and Finance had a quarterly meeting directed by the Chief Financial Officer. He presented information on current news regarding transportation as well, introduced new hires on the team, and gave attention to large-scale projects being conducted in the division. It was important for me to attend these meetings so as to further my education and be a part of the budget team. The division staff also had me conduct policy research on occasion, which entailed researching monetary policies regarding federal construction projects. This process was simply to double-check other employees’ work in their funding allocations, and it was also helpful in the completion of their job duties. Furthermore, my intern advisors had me surf the Colorado Department of Transportation intranet website during my free time and read through information regarding the organization’s efforts and procedures. This was not necessarily required in fulfilling my analytical duties for the internship, but rather it informed me more about the company I was working for as well as about an entity that is critical to the movement of people in Colorado.
My INFO classes were very helpful in preparing me for this internship, as the majority of the internship required me to analyze and present information related to the Colorado Department of Transportation’s budget. My INFO classes have taught me numerous ways to analyze quantitative data, including using different Excel functions and add-ins, multiple queries in SQL Server, and various analytical software, like Visual Studio, Power BI, and JMP. Specifically for this internship, I used Excel add-ins to analyze the budget information, and then I used Power BI to present the information.
The Colorado Department of Transportation stores its monetary information in SAP, and in order to analyze this data fluently, I had to pull the information out of that system. I did this using the Excel plug-in “Analysis for Microsoft Excel.” This is a great Excel tool that allows information to be extracted from all different kinds of databases, including SAP. Once the information is gathered in Excel, the data is then formatted similarly to a Pivot Table. I could essentially look at all of the different cost centers and budget pools used for each division and filter the table to compare different budget levels, expenditures, and encumbrances according to the dates I specified. This was a great tool to be able to analyze the Colorado Department of Transportation’s budget information over time, and I do not think I would have been able to do this smoothly without my consistent Excel training in all of my INFO classes.
The other way that my INFO classes prepared me for this internship was through the use of Power BI. I just recently learned how to use this software in my INFO 3340 class, as I had to transform my project data into several different data visualizations. The Division of Accounting and Finance at my workplace had recently hired a new Chief Financial Officer when I began my internship, and he insisted that the look and organization of the budget be changed. He wanted to include some data visualizations on how different divisions were performing based on a percentage of budget consumed versus what period it was in the fiscal year. I was actually able to work closely with the Chief Financial Officer on this project because I had volunteered myself and knew from my INFO classes how to create meaningful data visualizations. Throughout the course of my internship, I met frequently with the Chief Financial Officer and presented to him different dashboards I had created using the budget information. I created line and stacked column charts, tree maps, and funnel charts to present relevant information on the Colorado Department of Transportation’s budget that will now be included in the formal budget look. My INFO classes prepared me well for a meaningful internship that has overall provided me with a great experience for my future career in data analytics.