STEP ONE: Provide estimates of what the new prices should be.

The first step involved providing estimates of where the company wanted the new prices to be. These estimates were placed on an Excel worksheet and graphed so show a new pricing curve. At this stage, rough estimates are fine. However, later, these numbers need to be tweaked to produce a smooth curve.

After making a first pass at new prices, I refined the estimates to produce as smooth a curve as I could. At one point, I changed the axes to focus on improving my estimates at the lower end of the curve. You can see this at about 0:11.

STEP TWO: Add a trendline to fit the pricing curve.

Once the price estimates were finished, I used the chart to find the best fitting trendline. In this case, the trendline based on a fifth-degree polynomial was the best fit. I use that fifth degree in a LINEST formula, step three.

STEP THREE: Create a LINEST function to get the coefficients of a fifth-degree polynomial.

Using LINEST, I retrieved the coefficients of the polynomial which best fit my pricing guesses. With these coefficients, I can create a formula that provides pricing for all the user counts (licenses) not represented on the worksheet (steps four and five).

(Note: For the LINEST function, you can use SEQUENCE( 1, 5 ) in place of “{1,2,3,4,5}” if you prefer.)

STEP FOUR: Create a formula the calculates prices based on user counts.

With the coefficients returned by LINEST, I was able to calculate any price (“derived y value,” dependent variable) from a given count of users (“known x value,” independent variable). In the video below, I validate the formula by comparing to the price estimates entered above.

STEP FIVE: The last step involves setting up a simple formula that permits input of a count of users (or licenses) and returns the calculated price.

STEP SIX: To finish the project, I created a dashboard to allow a user to enter a count of licenses and other related values and calculate a final price or price adjustment.