Pricing is perhaps one of the hardest aspects of marketing to get right. And even though it’s impossible to keep control over and measure all of the variables involved, using analytics can help you track and predict enough of those variables so you can set the price where it needs to be. Here, we will outline an example of how to set up a pricing model for a service – model business using a simple tool, Microsoft Excel.
We’ll take a generic example to describe the process. Let’s say you own a network of service providers, each that are able to perform a few different types of services to your clients. When getting a new client, you need to know how much to charge for a yearly contract of these services. The problem is that each of the service providers are independent and will charge you a different amount for each service, and each of these prices may change with time. This makes it a logistical nightmare to know your costs so you can set an appropriate price.
Marketing Analytics in Excel
Here is where marketing analytics can help us. First, we can perform a historical analysis to set up a method for discovering how much a specific client contract might cost us. To do this, let’s go to Excel. What we’ll need to do is set up a spread sheet with the specific services across the top, and the various service providers down the rows. Then we need to input past data, showing how much specific services have cost for specific providers in the past. It will look something like this.
As can be seen, the historical prices that each service cost according to provider are input into the body of the sheet. Now we need to break this data down into something that we can use. What we want to know is how much, on average, each service costs according to the provider. If you have many rows of data, it’s easiest to use excel’s new ‘averageif’ function, as shown:
This will average the data only for the provider specified, so you can get the averages for each service according to provider. As the formula is written, all you’ll need to do is drag it down, change the provider name according to the row, and then drag them all across.
The final thing we must do in setting up our historical analysis is to find out if the averages we’ve calculated are a good measure of what our actual cost will be. For this, we calculate the standard deviation. Again, we are going to use an ‘if’ function to make it easier to deal with large spreadsheets. The formula should look like the following:
Remember two important things about this formula: One, after typing it in you must hit ctrl+shift+enter to turn it into an array, or it won’t work. Two, make sure any blank cells in the range to be calculated by the formula are not blank (you can fill them in with a negative sign, as shown, and it will work just fine).
Now that we have found averages and standard deviations using historical data, we are ready to begin our analysis.
BUT, before you continue on to tomorrow’s post for the analysis, check out our e-book: Using Excel to Solve Business Problems: Simple Predictive Analytics, on Amazon. While the book is older, most of the content is still relevant today.