With all the uncertainty about the economy, now is the perfect time to play “What if” with your business. In the sample spreadsheet below, numbers in the yellow cells in the “Current” column set the baseline for the formulas in the remaining cells. The four “What if” columns show how changes to margin, average job size, close ratio, or all three affect break-even sales volume goals. For example, the “Current” column shows that to cover $300,000 in costs with a gross profit margin of 35%, an average job size of about $65,000, and a close ratio of 0.25, break-even volume is $857,143 (cell B10).

Here's where “What if” comes in. If gross profit margin (D8) drops to 26%, you'll need almost $300,000 more in sales to cover costs. Similarly, if jobs are smaller than expected, Column E shows how many more jobs you'll need to sell and how many more leads you'll need to generate. Column F shows that if you can't close as many sales, you'll need more leads just to tread water. And the last column shows what happens if everything changes at once — a doomsday scenario.

The good news is that “What if” works in both directions. If things change for the better, the picture gets rosier. — Judith Miller is a Bay Area construction business consultant and trainer specializing in accounting, finance, and computerization.

Sample Form Click here to download the sample Excel form. Remember to save the file to your hard drive. To calculate Break-Even Volume — the minimum amount of total sales needed to cover costs — use this formula:
Total Annual Need ($) ÷ Gross Profit Margin (%)
For example: $300,000 ÷ 0.35 = $857,143

NOTE: Because this is a break-even exercise, Net Profit is not included in Total Annual Need.

Making the Spreadsheet Work In the Current column, enter the following data into the yellow cells:

Personal Living Expenses (B3) — total annual budget for rent, food, utilities, etc.

Business Overhead (B4) — include only expenses associated with running the company (e.g., office salaries and rent, but not materials or subs). In the final two lines (B5, B6), enter cash needed to repay debt or to increase liquidity. (Add lines for other business expenses — not shown.)

Jobs Required (B12) — number of jobs sold this year.

Lead-to-Close Ratio (B13) — total number of sales divided by total leads for the year. The remaining “Current” column cells hold the following formulas:

Total Annual Need (B7): =SUM(B3:B6)

Break-Even Volume (B10): =B7/B8

Leads Required (B14): =B12/B13

Leads Per Month (B15): =B14/12
Copy all four of the above formulas across into columns D through G (green cells).

Average Job Size (B11): =B10/B12
The formula in the blue cells D11 and F11 is the same: =B11. Other blue cell formulas: E8 and F8: =B8; D13 and E13: =B13.
The brown cell D12 contains a formula to calculate Jobs Required: =D10/D11. This can be copied into the other three cells to the right in the same row (E11, F11, G11).