Real life applications for a cash flow worksheet vary extensively, from stock investigation to project funding, even the government! This is a real life case study of a financial analyst utilizing a cash flow worksheet at a civil engineering firm to predict the success of a bridge project. It shows several universal parts of such a project, and one non-typical way to calculate economic value.
Steven is a financial expert at a civil engineering firm in the City of Metropolis. The job is to work with the civil engineers and project managers to evaluate the income and costs of multiple civil bridge and road projects to support the proposal and contracting process.
The company has received an RFP from the municipality to kick off a new highway connection over the North Harbor. The firm’s engineers have estimated the required materials along with the manpower to build the bridge. At this point it’s the analyst’s task to estimate the profitability of the project so the company can submit its proposal.
The analyst breaks out a reliable cash flow worksheet template, that is already built for civil engineering projects. This tool is the starting point to be tweaked for this project. First, it projects the quantities and types of steel, cable, cement, and other materials approximated by the engineers and multiplies those by forward asset market rates to get the approximate cost. The analysts researches into the company’s previous projects to estimate a potential range of variation for the base materials, given that input prices can move significantly between proposal and building period.
Next, the analyst inputs his labor costs into the cash flow worksheet broken down by design, iron workers, sea services, etc. He or she includes an estimate of hiring workers or subcontracting the work, along with benefits, insurance, and other costs. These expenses are usually spaced out according to the project phase. Other expenses, permits, training, research, delays, etc. are included in the plan.
On the income side, the analyst’s previous exposure to the customer and similar projects reveal payment will come in three large chunks, then the revenue share from the bridge tolls for a decade. The large installments are what the firm is actually competing for, and they need to cover the expenses of each project step. The analyst adds a variable profit margin on top of costs. Using this profit margin, his goal is to estimate the lowest fee to charge above their costs to ensure the NPV meets the company’s profit minimum expectation of 8 %.
The spreadsheet times the initial payment in order to coincide with the venture start date, which will cover supplies, site excavation, and drilling costs. The other two payments are usually timed half a year after the completion of phases 2 and 3, which are the framework building, and construction of the highway and toll booths.
Regarding the share of future toll fees, the analyst gets creative. The company has rarely seen this type of project and both the toll fees and amount of traffic are unfamiliar. In a separate tab in the cash flow worksheet, the analyst creates two grids. The first includes a range of car traffic numbers from 10,000 to 30,000 per day. A growth rate multiplier is applied, compounding the initial estimate. The analyst then creates an identical grid of toll fees and growth rates and arrays them to the right for 10 years. Finally, the analyst uses an Excel statistical analysis tool to build a probability distribution of toll profits per year. This generates 5 scenarios with revenues varying up and down one and two standard deviations to get multiple simulated cash flow pathways.
In the last step the cash flow worksheet sums the costs and revenues per year and all five scenarios. The spreadsheet displays these in chart format with a horizontal bar for minimum satisfactory return. The company has usage of public project funding at a 6% interest rate, to which the firm adds 5% for additional risk factors to arrive at a discount rate. Lastly, it discounts the net returns per year under all scenarios, and calculates an NPV for each one. This gives five project values. At least three or more of these NPVs must go over the company’s hurdle return as demonstrated in in the cash flow worksheet in order to bid on the project.
