Microsoft Excel Applications
As with Access, using Excel can be very straightforward or very complex. I have used it for things as simple as keeping track of donated items and creating bid sheets and lists for a fundraising auction. I have also used it to build complex models and perform sophisticated analyses. Below are some of the more complicated applications I have built.
Alaska-Canada Rail Link
In 2005, the Governments of Alaska and the Yukon Territory commissioned a joint feasibility study to formally assess the benefits of building a 2,400 kilometer rail connection between the Alaska Railroad and the rest of the North American rail system. 
Working in partnership with a Rail Infrastructure and Operations expert, I constructed a spreadsheet model that pulled together physical parameters supplied by other team members, applied cost and operating relationships, and ultimately produced financial statements evaluating the overall economics of the proposal. The project had two distinct phases. In Phase I - "Which Route?" - our role was to calculate the operating and capital recovery costs of moving the forecasted freight volumes over alternative routes to help identify the preferred route. In Phase II - "How should we run it?" - our role was to analyze the profitability of the preferred route under a variety of traffic and management assumptions. For each scenario, the model calculated profitability for different corporate structures, by route segment, and by traffic type. The different questions being asked in Phase I and Phase II, and the requirement to evaluate profitability by slicing the pie in multiple ways, resulted in an exceptionally complex Excel model.
Railcar Forecasting
Working with a Rail Operations expert, I constructed a model to forecast the supply and demand of various railcar types. Investment firms use this model to assess the profitability of investing in railcars. The model draws on several different data sources having similar but not identical data definitions. This means I must use various approximation methods to put the data into common buckets. I also developed a method to forecast car retirements using regressions that estimate the percentage of cars of each age (at beginning of year) retiring by the end of the year. This proved to be more reliable than the usual practice of estimating the percentage of cars built in year 0 that would retire in year 1, year 2, etc.
When building a model, it is important to calibrate and sanity-check the results. One method is to list a priori the expected effect of changing input variables. In the screen shot below, you can see how I set up a comparison between the expected and actual impacts of parameter changes. If they move in the same direction, it is an indication the model is built correctly. If they move in opposite directions, then I would look for the cause and then decide whether it was the model structure or my assumption that was incorrect.
Other Applications
- Railroad Revenue Per Revenue Ton-Mile Analysis. This supported various analyses used to make investment decisions in the railroad industry.
- Truck vs Rail Industry Competitiveness by Railroad and Commodity
- Port Study. This analysis evaluated potential transportation markets in support of a $2.3 billion acquisition of major port facilities in North America.
- Unit Train Management. Set of linked spreadsheets used by CSX Transportation to manage unit Grain, Bulk and Rock trains. The spreadsheets automatically create a variety of reports, update an intranet site, and broadcast emails. When these spreadsheets were implemented, train routing errors dropped from an average of one per day (at a cost of over $7,000 per incident) to just a few per year.