⇦ Back to Modules

INFRASTRUCTURE MODELING AND ANALYSIS
By Rafael Nicolas Fermin Cota

↳ Download Railway Module

I am very excited to share a fully functioning framework for an infrastructure project as well as its technical report. The goal of this module is to provide HBA students with dynamic modelling knowledge to be “desk ready” and be able to apply infrastructure/railway modelling skills immediately in the workplace.

Consistent with my approach of “learning by doing”, this module focus on the practical, hands-on spreadsheet application of three segments and a consolidated portion: “Freight”, “Passenger”, “Infrastructure” and “Consolidated”. Each segment has its own assumptions, calculations, financial statements and scenario analysis. Furthermore, the consolidated section aggregates the data from all three segments and delivers fully combined financial statements and scenario analysis.

The framework is exceptionally detailed allowing for separate macroeconomic, operational, capital asset and financial assumptions for each segment. After all calculations have been run, a summary section outputs results, ratios and charts for the key data required by decision makers. This data can be viewed as an aggregated total under the consolidated section or as individual segments as required by the user. It is one of the most detailed and unique framework available for infrastructure entities.

One of the unique aspects of the framework is the amount of sensitivity analysis that can be run. The scenario analysis first contains a control panel to regulate the length of the analysis and what will be displayed. After the initial scenario is set out, there are several variables including multipliers and growth rates that the user can input. Finally the user can quickly analyse the results under summary data which will display key metrics and charts for that scenario. This makes the model perfect for running various feasibility tests for any infrastructure entity in a quick and efficient manner. In class, we will cover different array formulas and infrastructure modeling fundamentals with reinforcement through Excel, Python and VBA.

Another unique aspect is how user friendly and intuitive it is to use. It follows a simple top down approach where students can go through and input the various assumptions for each section as they have the information available. It also allows students to select the units that will be entered such as "Ton" or “Ton-Km" with all the conversions calculated within the model. The technical report first gives an explanation of how each index fits into the model as a whole and then gives detailed explanations of all formulas in plain English. With its intuitive nature and the technical report as guidance, even novice HBA students will be able to take full advantage of the module.

Keep in mind that my class it’s not just about training and classroom teaching, but about cultivating the next generation of experienced investment manager professionals. The target audience includes railway professionals in public and private entities, investors, investment firms, government agencies and international organizations.

I would like to thank Tanuj Dutta, Sam Archibald, Corey Obermayer, and Kevin Good for their excellent research assistance.