How to Create a Components Sales Forecast

A critical part of any sales management job is forecasting, yet according to studies, the best forecasters are only 40% accurate. Setting up and maintaining an organized forecast is the first step to accuracy in forecasting.

Difficulty: Challenging

Instructions

Things You'll Need:

• Your opportunities Excel sheet
• Your PC
• A calculator to double check your work

Setting up Your Sheet and Filling in Opportunities

1. Set up an Excel workbook with two spreadsheets - one entitled Opportunities, and the other, Forecast.

2. Lay out each one with two columns per month - one for units and one for dollars. Add a column in front of every three month quarter for ASP, or average selling price and have the dollar column automatically calculated by adding a formula that multiplies the units by the ASP. If you are computing foreign revenue, add the appropriate converstion factors into the formula.

3. Add a probability column, plus a column for customer name and part number to the far left of this sheet.

4. At the end of every three months, add one quarter end total column each for units and dollars - and one quarter end each for units and dollars weighted by the probability factor you added at the beginning.

5. Fill this in with all your open opportunities, add the probability based on how far down the road the customer is with your product, and anticipate their run rates and start of production. It helps if you have gleaned this information during each sales call.

6. Add a weighted total at the bottom of each quarter column.

Creating the Actual Forecast

7. Copy this worksheet, and paste it into your sales forecast sheet. Delete the weighted columns, and delete every opportunity that is less than 95% probable. Now, delete the probability column.

8. Add in your existing run rate business, and project the growth of each piece of business by reviewing each company's website for indicators of strength or weakness.

9. Add in projections for purchases by any channel partners you have, remembering to remain conservative.

10. Add totals at the end of every quarter column, and a yearly total at the far right side of the sheet.

Tips & Warnings

• You may want to color code your channel partners projections to differentiate them from direct customers.
• Be conservative! You are far better off if you under forecast than if you over forecast.

Resources

• Conduct a Sales Forecast