14.4Information Organization

Having addressed best practices, another important point relates to how the information should be structured and grouped together in the model. Proper information separation sheet division aids in better model development and understanding.

The objective of the control panel is to show the relevant appraisal and profitability indexes, as well as to allow for the selection of the most important parameters per the following breakdown:

Most Important Parameters

  • Use of data validation options in order to limit choices/values to predetermined or feasible ones:

    • Bus-type selection;
    • Financing-type selection;
    • Profitability “goals.”

  • Sensitive consumption/operation parameters:

    • Bus acquisition value information;
    • Fuel, lubricant, tire, and other consumption rates;
    • Driver sizing parameter information.

Relevant Financial Results

  • System appraisal;
  • IRR for system entities;
  • EBITDA margins: profitability (consists of earnings before interest, tax, depreciation, and amortization, divided by total revenue).

Goal-Seeking Functions and Automation

  • Implement macros to target certain profitability values or specific revenue values;
  • The goal-seeking parameter may be expressed as an index of payment per kilometer, payment per customer (technical tariff), or even a hybrid kilometer per customer payment;
  • In essence, this goal-seeking approach targets a total payment amount for the system entity, which may then be broken down into whichever index is desired.

As mentioned above, it is convenient to separate information by its temporal characteristic. Both time-based and non-time-based sheets should cover the following topics, segregating the information accordingly:

Operational Plan

  • Number of kilometers per fleet type;
  • Number of vehicles per fleet type;

  • Ticket revenue:

    • Number of customers/ridership;
    • Tariff scheme per ride.

It is important to recalculate these numbers in the financial model for two reasons:

  • So the model contains all relevant information of the system/scenarios and is “self-sufficient”;
  • As a quality check toward the values calculated in the transportation model.

Fleet

  • Age profile per type of fleet per year:

    • Place example;
    • Indication of new acquisitions and sales (the change between two years).

Operational Expenditures (Opex)

  • Opex per vehicles;
  • Opex per personnel.

Capital Expenditures (Capex)

  • Indication of acquisitions and renewals;
  • Indication of acquisition values per depreciation type;
  • Indication of acquisition values to be financed by financing alternative.

Fixed Assets

  • Used for depreciation calculation;
  • Must separate each item by depreciation category and rate.

Financing Schemes

  • Interest rates;
  • Tenor of loan;
  • Grace period;
  • Percent financed;
  • Finance indexes, used by ECAs.

Financing Schedule

  • Used to keep track of interest and principal repayment;
  • Must separate and keep track of each loan individually (loan drawdown year and conditions).

Lastly, there is the financial sheet, which contains the necessary information for the financial appraisal of the system entity.

Income Statement and Cash-Flow Statement

  • The income statement is used to calculate the yearly earnings, profit, accumulated profit, and taxes;
  • The cash-flow statement is used in order to calculate the yearly free cash flow;

  • For the cash-flow statement, it is recommended that one segregate the different cash-flow origins to properly calculate the cash requirements/overdraft or surpluses for the company:

    • Cash flow from operations;
    • Cash flow from investing;
    • Cash flow from financing;
    • Net cash flow.

Balance Sheet

  • Used primarily to calculate increase/decrease in working capital;
  • Can be used to calculate debt/equity ratios and other indexes to attest to company’s health;
  • Aids in validating the free cash flow and system appraisal, once all information is categorized and interrelated in the balance sheet;
  • Discussion of how the balance sheet may be “omitted,” as long as these calculations are performed elsewhere.