Projects with clients have sometimes required us to create examples of pricing to assist in competitor analysis, and pro forma forecasts.  We have created an approach that we will share with you that generates pricing from one to one million units, with little effort.

We base our Sales Price (SP) on assumed Gross Margin (GM) targets, and also assume that cost is constant.  To cover a range of pricing from one to one million, we use a six-cycle, semi-log format. 

For this example with a software client, we chose 85% as our GM at one unit , and 15% as our GM at one million units.  We apply, and transform the equation for a line; y = -mx+b. (To be compatible with a logarithmic trendline in Excel, we also apply a change of base to Natural Logarithms).  Our equation is now, y = -0.0507 Ln(x) + 0.85.  All that remains is to calculate SP, and since we now have GM, we know that SP = Cost/(1-GM).  Graph 1 shows the semi-log GM plot.  Graph 2 shows the SP vs GM on a linear plot, which enables you to see SP declining as a function of quantity, which was the original goal.  Table 1 shows the calculated values for GM, and SP.

Graph 1  

Graph 2

Table 1

Final pricing calculations seldom result in a single smooth curve.  Usually there are several 'breaks', or segments in the curve.  But the process to accommodate them would be the same as shown here.

This is another of the on-site, half-days workshops we offer for $495.  This workshops includes the Excel template used in the TechTIP, and an example of regressing a competitors pricing.