A number of readers ask that I provide a sample sales forecasting excel sheet for companies who sell to original equipment manufacturers, or better known as OEMs. This excel sheet is from on the post Using the PERT Calculation & Analysis to Improve Sales Forecasting Accuracy. However, in that post, I didn’t include the excel sheet. Instead of merely adding it to the post, I thought I would provide it in this one and include the steps needed to make it work. Its premise is based on thoroughly understanding your customers business, identifying the total volumes your company could sell and using a formula to come up with a more accurate sales forecast.
The Value of PERT in Forecasting
This excel sheet is based on using the Project Evaluation and Review Technique (PERT) calculation and analysis. PERT is a methodology initiated by the United States Navy in its design of its Polaris submarine. It allowed them to narrow down the most likely outcome among three possible variables. It’s an approach adopted by project and product managers in multiple industries and forms the basis of Gantt charts.
Identify the three most possible outcomes, develop variables for each, and use a simple and straightforward calculation to come up with the most likely outcome. These variables include identifying the “Best Case Scenario”, the “Most Likely Scenario” and the “Worst Case Scenario”.
Now, it’s important to note that no sales forecast is 100% accurate. However, this excel sheet’s benefits is that it forces salespeople to answer the most important questions with regards to their customers. It does this by converting these variables above, into questions salespeople should ask themselves when reviewing their business at an Original Equipment Manufacturer. What’s the most I can sell? What am I likely to sell? What is the minimum amount I can sell?
Best Case Scenario = What is the most I can sell = A
Most Likely Scenario = What am I likely to sell = B
Worst Case Scenario = What is the minimum amount I can sell = C
PERT Calculation = {1(A) + 4(B) + 1(C)} / 6
Step 1: Determine Gross Profit Per-Unit Sold
Gross profit is the ultimate measurement of sales. If there’s no gross profit, then it simply doesn’t matter what the sales total is. Therefore, the first step is to use your company’s gross profit per-unit sold within the excel sheet. You can input this value into the yellow highlighted area entitled “Gross Profit Per-Unit Sold”. In our example, we’ll assume the gross profit per-unit sold to the original equipment manufacturer is $10.00.
Step 2: Determine Usage for Each of the Customer's Lines
Your company must have a thorough understanding of your product’s usage within the manufacturer's product lines. In this case, it’s about knowing how many of your products go into each and every one of your customer’s own products. In our example, for every line your customer sells, they’ll need two of your company’s products. Determine how much your customers would need of your products for each of their own lines and input this value into the “Usage Per-OEM Line” in the yellow section.
Step 3: Determine the Number of Lines Sold by the Equipment Manufacturer
Understanding your customer’s business is an essential aspect of proper sales forecasting. In this third step, you want to have a solid understanding of the total number of sales the equipment manufacturer makes to their own customer base. If you know how many of your own products go into each equipment line, and know how many lines your customer sells to their own customers, then you can accurately depict the total sales in gross profit for the account. In our example, we’ve assumed the equipment manufacturer has three product lines that it sells to its market. Its totals are 100 for line 1, 150 for line 2 and 300 for line 3. Input these values into the yellow portion entitled “# of Lines sold by original equipment manufacturer in their market”.
The Benefits of Selling to Original Equipment Manufacturers (OEM)
Applying Percentages to the Value in Pert Calculations
For the remaining portion of the excel sheet, (all areas with an asterisk *), the sales person would ask themselves the three aforementioned questions of “What’s the most I can sell?” “What am I likely to sell?” and “What is the minimum amount I can sell?”. Their answers would be put into the yellow shaded areas. In this example, I’ve decided to use percentages instead of volumes, but you could change the excel sheet to use volumes instead. The salesperson asks themselves this question for each of the customer's lines.
Once the sales person inputs their values for the three variables, the excel sheet will use the PERT calculation and provide an estimate in gross profit on the potential sales to each individual line. This is found in the green areas under “Answer”. A total is then provided for the account under “Estimated GP for Account”.
The above video provides further insight into using PERT to improve sales forecast accuracy. It is taken from the post: How Do I Improve Our Sales Forecast Accuracy?
When selling to equipment manufacturers, it’s essential to thoroughly understand their business. First, determine where your products go and how many can be sold for each of the equipment manufacturer's product lines. Second, determine the number of equipment lines they sell to their own customers. Third, apply percentages to the three PERT variables.
My customers often ask me how they can determine the number of lines sold by their customers within their own market. Well, it might seem rather involved, but it really is pretty simple. This information comes from solid market research and includes using the internet, trade publications, the equipment manufacturer's own website, its product information releases and in most cases, it simply involves asking them.
Here is the excel sheet: Download Sales Forecasting Excel Sheet For Companies Selling to OEMs
If you want to read more about selling to original equipment manufacturers, then you can read the following posts.
Performing a Sales Gap Analysis on an OEM Account
The Benefits of Selling to Original Equipment Manufacturers (OEM)
Strategic Sales Planning: Using a SWOT Analysis on OEM Customers
Comments