Models are a great way to simulate scenarios. When planning a business, there are lots of “what if’s” that need to be considered. Modeling these “what if’s” with reproducible, systematic models makes justifying your projections easier. Sales projections are particularly prone to skepticism since the art of sales depends on convincing people to part with their hard earned money. That is why building a sales model for your new venture allows for a constructive discussion. In this post, we will discuss how to model sales via a spreadsheet using this sample model.

Table of Contents

## Step 1: Calculate Average Selling Price

Pricing is as much art as science. Art in that it’s based on what someone will pay while the science is your costs to make the product. For our discussions, we will focus on the two dominate pricing models: **Cost Plus** and **Value**.

Both of these models cover a wide array of products and are important to understand before heading into more complex modeling.

### Cost Plus Pricing

Cost plus pricing is just that. You figure your cost and then add some percent margin on top. This type of pricing is usually done with well established, commodity products. Industry norms usually dictate the margin. Volume discount pricing is usually done this way since the more you build, the cheaper it gets. Products that go through distribution or are resold typically follow the cost plus pricing model. It really depends on the industry.

### Value Pricing

Value pricing looks at the value to the customer. This value could be the worth of each feature or how much the customer saves by using your product. The trick here is to figure out what the customer values and how much it’s worth to them. Understanding your customer pain as well as their business makes reading their mind a lot easier. Value pricing is usually done on cutting edge products that solve major customer pain. The harder the pain to solve, the more value the product adds. Value pricing still requires meeting a certain margin level but that is rarely an issue.

## Step 2: Determine Price Decline

Most products price decline over time. Usually, this is volume dependent. Competitors also affect ASP decline as new products get introduced or competitors show up. Your best bet is to start out by doing some research on similar products. Check out how their price eroded as they matured or the sales volume went up. Some general guidelines to consider include:

**Volume Reduces Costs:**As your volume increases, your cost to manufacture declines. A good rule of thumb is that for every 10x increase in volume, the cost reduces by 15%. So the individual cost of 10 will be 15% less than the cost of 1. Talk to your manufacturing guys and see what they can do.**Cost Reductions**: Often, the 2nd generation of the same product is cheaper than the first. There are various reasons for this but most likely, the design team figured out how to do the same with less. Planning for a cost reduce is always a good idea since nothing is stagnate.**Customer Mandated:**Some customers will demand that the price decline some set amount. This could be volume or time dependent. At first, this does seem strange since how can they dictate that the price go down. Well, if the deal is big enough, the customer can pretty much do whatever you let them do.**Reduced Feature Set Model:**Your ASP may go down if you add another product to the mix that accounts for a higher volume of sales. Usually, cost reductions take this into account but you may choose to capture more of the market by allowing customers to buy a cheaper, reduced feature set version.

## Step 3: Figure Out Your Sales Cycle

Your sales cycle is how long it takes from first contact to a sale. Knowing this exactly is almost impossible — so just guess. Make your best estimate. It can be refined later. Think about how complex the your product is and the costs associated with it. Some questions to ask are:

**Customer Approvals:**If you have an expensive product, you will likely need executives to sign off on the purchase. Installation and lead times for site preparation also add time to the cycle.**Success Rate:**The hit rate is how many initial contacts will turn into a sale. Again, this is a hard number to get exact but make a guess. A good starting point is 10%. Hit rates like direct mail hover around 1%, so the range varies a lot.**Sales Staff Efficiency:**Determine how many customer visits each sales staff can do. You might have to get a little creative on travel time and meeting time. Make an educated guess if you don’t know. You will have plenty of chances to refine as you study your model. Also consider how many visits it might take to close a sale. Knowing this will determine how many sales staff you need.

## Step 4: Product Ramp

Once a sale has been made, there is usually a ramp to fill the order. This is the product ramp. This applies to products where a win is multiple units or long lead time products. A single unit can have a product ramp if it needs to be installed. In that case, the ramp would be from shipment to installed and functional.

## Step 5: Determine Wins Over Time

The most complicated, subjective and black magic part of the whole model is estimating sales. Your investors will give you grief about whatever numbers you put in. So, the key to this is to figure out how much revenue you want to achieve when you are full up selling. Compare this number to what an investor might want as a return. Refine until you can justify your sales ramp by either giving competitor examples or customer interest. For example, if it takes 60% of the total market to make your sales numbers, that will not fly. 15% maybe. Top 20 customers even better. Investors will worry about this ramp or adoption curve since it translates into exit potential and the amount of capital they need to invest.

## Step 6: Refine As Needed

Modeling is an iterative process. As you gather more data, you adjust your model accordingly. Reviewing it with your staff is also a good idea. The more eyes looking at it, the better. You can also track your success rate in modeling by comparing to actuals once sales start. This model validation is a great way to adjust future projections to more closely align to reality.

## Using The Sample Sales Model Spreadsheet

Download the sample spreadsheet and open via Excel or OpenOffice (I used OpenOffice). There are three sheets within the workbook: Summary, Unit_ASP_Model and Sales_Staff_Model.

### Summary Sheet

As the name implies, it summarizes the sales forecasts and sales staff required for 5 years. You don’t change anything on this sheet — it’s just for displaying and cut and pasting into your pitch. None of the cells in these sheets are protected, so be warned that you are flying solo.

### Unit ASP Model

This sheet models the sales and price of one product. The green indicates where you will fill data in. Sales and ASP are done by quarter for a total of 20 quarters (5 years). These quarters are then rolled up into the a summary below the quarter data.

### Sales Staff Model

Models the number of sales staff required for the desired wins or sales. It includes an additional 4 quarters before revenue because sales staff take time to sell products (e.g. Sales ramp). Calculations are done to figure out the total number of sales staff, by quarter based on the hit rate and the wins required. Remember that the hit rate is your sales staff efficiency in closing a sale. This is based on the total number of customers they meet with.

## Good Luck And Happy Sales Modeling

At first, this might seem overwhelming. That is totally normal. Just take it one step at a time. Modify one green box and see how it effects the rest. Don’t worry about messing it up — you can always start over. Armed with a solid sales model, your business will be primed for success.

jarie says

Dave- Try the link now. It got messed up and it should work now. The direct link is https://www.thedailymba.com/wp-content/uploads/2009/08/sample_sales_model.xls

Thanks for reading

Jarie

Dave says

Link to Excel model results in “Sorry, no posts matched your criteria.” using Safari. Is there another location for the spreadsheet download? Thanks.