Case Study: Using a Cash Flow Worksheet

Real life applications for a cash flow worksheet vary widely, from stock analysis to project finance, even the government! This is a real life case study of a financial analyst using a cash flow worksheet at a civil engineering firm to forecast the profitability of a bridge project. It demonstrates some universal components of such a project, as well as one unique way to estimate long term value.

Dave is a financial analyst at Extra Smart Partners (ESP), a civil engineering firm in the City of Metropolis. Dave’s job is to work with the civil engineers and project managers to analyze the income and costs of various government-funded infrastructure projects to support the proposal and bidding process.

Extra Smart Partners has received an RFP from the City of Metropolis to kick off a new bridge over the Eastern River. The ESP engineers and project managers have estimated the necessary materials and manpower to build the bridge. Now it’s Dave’s job to estimate the profitability of the project so ESP can submit it’s proposal to Metropolis.

Dave breaks out his trusty cash flow worksheet project template, which is already set up for civil engineering projects. This is his starting point which he’ll tweak for this project. First, he uses the quantities and types of steel, cable, cement, and other materials estimated by the engineers and multiplies those by current forward commodity prices in the market to get the approximate cost. He looks into the company’s past projects to estimate a potential range of variation for the raw materials, since input prices can move substantially between proposal and building stages.

In the last step of his cash flow worksheet, Dave sums the expenses and revenues for each year and all 5 scenarios. He charts these out in graphs with a horizontal line for minimum acceptable return. ESP has access to public project funding at a 6% interest rate, to which he adds 3% for additional risk factors, for a 9% discount rate. Finally, he discounts the net returns per year under all scenarios at 9%, and calculates an NPV for each revenue path. This gives him 5 project values at 5 different revenue assumptions based on his variable markup. At least 3 of these NPVs must exceed the company’s 12% hurdle rate in his cash flow worksheet for the bid to viable.

Related video: How To Write Effective Proposals and Cover Letters for Upwork Contracts

Leave a Reply