I’ve recently been working on a new service and was having a tough time putting a price tag on it. I had a few variables that I needed to solve for in different scenarios. I needed to plan product pricing with Google Sheets to find answers to:
- How much profit can I expect to make?
- What should I charge?
- How many customers would I need?
My head was spinning.
I wanted to test prices to see how many customers I’d need in order to make the profit I wanted. Obviously, if I charge more I’d need fewer customers, or if I charged less I’d need more customers. But what were the numbers?
What I needed was a way to test my service’s pricing when I changed certain variables. At the same time, I needed to keep other variables the same (such as my profit amount).
Plan Product Pricing With Google Sheets
What I’m going to demonstrate can easily be done with Microsoft Excel’s Goal Seek feature. But you don’t need to go out and spend money on Excel if you don’t already have it. Google Sheets is free and available to anyone with a Google account.
First, we need to start a new spreadsheet in Google Sheets. The Blank template is fine for this project.
Next, we need to install the free Solver Add-On. Click on the Add-ons menu, and select Get add-ons…. Search for the word “Solver”, then click on the blue “Free” button to the right next to the Add-On titled “Solver” by Frontline Systems, Inc.
Follow the prompts in the window that opens and agree to allow the add-on to work on your files.
Now, let’s add some data to the spreadsheet. You can follow along in this simple example spreadsheet.
In that example, I’m outlining that I want to make $50,000 in profit on this new service (i.e. total income less total expenses should equal $50,000 profit). I know my cost per unit on this service will be $30 and I’m thinking of selling it for $100. How many customers do I need at $100 each to hit $50,000 in profit?
Yes, I am your nightmare from high school math class!
The math isn’t all that difficult, but you might want to try out a number of different scenarios with different prices, or a different profit so it’s best to automate as much of this as possible.
Let’s get solving!
Now that we have our data in the spreadsheet, let’s get back to the Solver Add-On.
Click the Add-ons menu, select Solver then Start.
On the right-hand side of your screen, you should see the Solver window. This is where we will set our objective, and tell Solver what to change in order to reach that objective.
In the screenshot, I’m telling Solver to set my objective (cell B8), which is the Profit line to the value of 50000, by changing the value in cell B4, which is my number of customers.
To run the scenario, you would click the Solve button towards the bottom.
The spreadsheet then gets updated with the total number of customers I’d need with the pricing I have set to hit $50,000 in profit, which is about 715 customers.
Now, I can do a sanity check:
- Are there 715 customers out there who would purchase my service?
- Do I have the capacity to handle 715 customers for this service?
- Would I need to bring on additional staff in order to handle 715 customers, and therefore increase costs?
If 715 seems like too many customers, I can try a new scenario with increased prices. Maybe I increase the customer’s price to $150 and run the scenario again. That brings the total number of customers needed down to about 417. If that number is more manageable for my service I’d be wise to use $150 as a price for it instead of $100.
Of course, this doesn’t answer whether or not a customer will be willing to pay that price for the product or service, but it gives you an idea of how many customers you’ll need to support in order to reach your goal.
If you know that you have a fixed capacity for the number of customers you can handle each year, you can enter that number into the “Number of Customers” section on the spreadsheet and have Solver change the price needed to charge in order to get the profit you’re looking for. For example, if I know I can handle a max of 500 customers per year, I would enter 500 in cell B4 (number of customers), and have Solver change cell B1 (customer price per unit). This would give me a $130 price in order to hit $50,000 profit. In this scenario, if I know that a customer won’t spend more than $75 for the service I may need to find a way to increase my capacity and reduce expenses, or perhaps it just doesn’t make sense to offer this particular product or service.
This was a very simple example, but you certainly aren’t limited to a one product scenario. If you have multiple products you can do something similar to see what the prices would need to be in order to hit a total profit goal.
On Solver’s window, you can click the Insert Example button to have it insert a more complex example with multiple products. In that example, they try to maximize the profit by changing the number of products to build, while making sure they build at least one of each and they don’t use more inventory than they have available.