We are now ready, having performed a historical analysis in part 1 of this post, to create a useful system for pricing. Depending on how comfortable you are with statistics and mathematical operations with standard deviations, you may or may not understand why the excel formulas are set up the way that they are. A full explanation of the mathematics is not our purpose here, however; we just want to get a useful result. So if you find yourself scratching your head at the math, just copy the formulas.
What we are going to do is set up three new spreadsheets. The first one will list all of the averages and standard deviations that we found from our historical analysis in part 1, and will look like this:
The upper portion of the second sheet will contain the number of specific services according to provider you are going to offer in the contract. In the lower portion, it will contain a formula to calculate standard deviation according to the number you input in the upper sheet. It will look like the following:
The final sheet, where we get our pricing, will have formulas mapped back to the first and second sheets.
We also need to add a few cells to the last sheet in order to calculate the total contract cost and standard deviation:
We now have an idea of how much the contract will cost the company. We also have an idea of how accurate that number is. According to a normal statistical distribution, you are about 65% sure your true cost will be within 1 standard deviation, and 95% sure it will be within 2. For this reason the standard deviation multiplied by 2 is also calculated above.
This means, from the example above, you are 95% sure your cost will be between $2,428 and $2,564. That knowledge will help you price accordingly. Granted, this measure is not exact, and there are other more advanced statistical measures that could be employed to improve this number. However, this measure is sufficient to give us a good idea about pricing.
What’s more, you can reuse or change this model whenever you need to. Just go back in and change the number of services needed per each provider (spreadsheet 2) and the formulas will instantly calculate a new total contract cost and standard deviation for you.