A benchmark is like a line in the sand against which we measure ourselves. But comparing actual performance with a benchmark is a static measurement. Unless we've reached the goal, we have no idea whether we're moving closer to it or further away. A benchmark can help us find out where we are, but no single measurement, no matter how sophisticated, can tell us where we are going.

For that, we need trend analysis. A trend line is dynamic; it indicates which direction we have been moving and predicts which direction we will move in the future. You may have not yet reached a benchmark of, say, 30% gross profit, but it's good to know that your company's gross profit trend is upward. Even better would be knowing that the current trend shows you will reach that 30% benchmark in five months.

Best Laid Plans For remodelers too busy to monitor overall company health, trend lines for a few critical company numbers will help you focus on the big picture. A good place to start is by tracking produced volume and gross profit every month (see “Budget vs. Actual,” below). The annualized “actual” numbers (row 2) become more trustworthy as the year goes on. In January and February, for example, these numbers represent only about 16% of the total year. At the end of June, however, 50% of the year is accounted for and the predicted annualized volume gains reliability. (More sophisticated trend line analysis, called a “trailing 12-month chart,” removes the seasonality by including data from the prior 12 months.)

Data vs. Information This raw data is pretty uninteresting and, to visually-oriented remodelers, not very useful. By creating a graph, you transform the raw data into useful information. Now you can clearly see that volume is “trending” a bit higher than anticipated, while gross profit, although on the upswing now, has been trending lower.

Sharing the graph with the rest of your company gives everyone a picture of where things are headed. And when the trend is in the wrong direction, the information gets everyone thinking about ways to turn it around. —Judith Miller is a Bay Area construction business consultant and trainer specializing in accounting and finance, business planning, and computerization.

Budget vs. Actual

In a spreadsheet, enter monthly volume goal and actual volume into the appropriate yellow columns; do the same for monthly gross profit goals and actual amounts (blue columns). Use the following formulas in row 2:
Column B (Volume Goal): = SUM (B3:B14)
Column C (Volume Actual): = AVERAGE (C3:C14) * 12 (multiplying by 12 "annualizes" the monthly amounts)
Column D (Gross Profit Margin Goal): = SUM (D3:D14) / B2
Column E (GPM Actual): = (AVERAGE (E3:E14) * 12) / C2 (again, annualized)

Budget vs. Actual

In a spreadsheet, enter monthly volume goal and actual volume into the appropriate yellow columns; do the same for monthly gross profit goals and actual amounts (blue columns). Use the following formulas in row 2:

Column B (Volume Goal): = SUM (B3:B14)

Column C (Volume Actual): = AVERAGE (C3:C14) * 12  (multiplying by 12 "annualizes" the monthly amounts)

Column D (Gross Profit Margin Goal): = SUM (D3:D14) / B2 

Column E (GPM Actual): = (AVERAGE (E3:E14) * 12) / C2 (again, annualized)
Budget vs. Actual In a spreadsheet, enter monthly volume goal and actual volume into the appropriate yellow columns; do the same for monthly gross profit goals and actual amounts (blue columns). Use the following formulas in row 2: Column B (Volume Goal): = SUM (B3:B14) Column C (Volume Actual): = AVERAGE (C3:C14) * 12 (multiplying by 12 "annualizes" the monthly amounts) Column D (Gross Profit Margin Goal): = SUM (D3:D14) / B2 Column E (GPM Actual): = (AVERAGE (E3:E14) * 12) / C2 (again, annualized)

Sample Form Click here to download the Excel form. Remember to save the file to your hard drive.