As part of a consulting project, I once had data from well over 100 law firms on each of their average partner billing rates as well as their number of lawyers. Put into Excel, the data created a scatter-gram that showed a data point for each firm’s rate (on one axis) matched to its corresponding size (on the other axis). Sometimes the eye readily spots clusters, open spaces or patterns. With the firms sorted by increasing numbers of lawyers, however, a trend line placed by Excel on the data points showed a quite apparent steady rise: more lawyers, higher rates.
The trend line alone helps you understand the correspondence of the data. More usefully, the software also produces a formula. That formula let you put in any size of a firm and find out its projected average partner rate. From the formula you could see that each additional 100 lawyers raised the average partner rate $13.
A trend line calculates what is known as a least-squares line, which is the straight line that passes as closely as possible to all the data points. It minimizes the sum of all the distances of the points from the line. The slope of that line is the formula that lets you do calculations for any data point.
When large numbers are involved, such as revenue, you can use logarithmic trend lines instead of linear trend lines. Relatively straight trend lines on log scales indicate smooth increases over larger scales.
Whenever you have a set consisting of two pieces of data that you can match, a scatter-gram helps you see relationships and a trend line both confirms it and quantifies it. That eyeball analysis works even better when you sort from high to low by one of the data elements.
Opportunities for law departments to understand data with scatter-plots and trend lines abound. Years out of law school could match up against base salary; elapsed months of law suits against total fees paid; patents filed in countries to annuities paid; and market cap of acquired firms compared to M&A costs are all candidates for graphs and formulas. It is a way to show and describe correlations.
Try it yourself. Collect pairs of data such as blended rates and firm size, put the paired data in Excel by rows, create a scatter-gram and super-impose a trend line. You will also spot outliers quickly, which may indicate a problem with the data or an unusual situation.
With Excel, all you have to do is click on the data points to create a trend line on sorted data that will confirm the pattern your eyes pick out or confirm the randomness of the data. For example, a scatter gram that shows the damages claimed by plaintiffs against your total defense costs should show a pattern and yield a formula.