How to use Goal Seek for product pricing in Excel

Excel is a versatile analytical tool widely used by businesses engaged in sales, including retail stores, warehouses, manufacturers, and resellers. One of the primary tasks in such enterprises is determining the retail price, which can depend on various factors and change over time.

In this guide, we will learn how to quickly calculate price increases, find average prices, and determine retail prices while simultaneously estimating expected profits using Excel.



How to Calculate Price Increases with Excel Formulas

Let's start with a simple example. Imagine we have a table with product prices. At some point, our suppliers (a dairy farm and a bakery) increased their prices, which means we need to adjust our selling prices accordingly. We've determined that the price increase for dairy products is 8%, and for bakery products, it's 5%.

We'll create two additional columns: one to input the specified percentage increase, and the other to calculate the new price. Enter the percentages along with the % symbol.

Now, let's calculate the new prices using a simple formula. In cell E3, input the formula =C3+C3*D3. To break it down, to find the new price for milk, you add an 8% increase to the old price. Repeat this formula for the entire column, and you'll get the results.

For readability, you can round the prices to one decimal place. Select the relevant columns, go to the HOME tab, and click on "Decrease Decimal."

Calculating Average Prices with Excel Formulas

Using the same example, let's slightly modify the data. Now, we have price information for multiple branches of our stores, and we want to calculate the average price for each product, essentially finding the arithmetic mean.

We'll use the AVERAGE function. Enter it in cell G3 and select the previous four prices. The software will automatically calculate the average. Copy the formula down the column and round the results as in the previous example.

Determining Product Prices Using Goal Seek

Product prices aren't arbitrarily set; they depend on several factors. One critical factor is the target price, which is determined through data analysis, competition monitoring, and other means. Let's explore this concept with an example.

We have a table showing the costs of a product, totaling $10,000. Naturally, the product's price must exceed this amount; otherwise, the business would operate at a loss. We've also set an expected profit of $2,000. Therefore, the intermediate price for this product is $12,000.

However, taxes apply (e.g., an 18% value-added tax). This means the product must be priced at $14,160 to cover the cost, profit, and tax.

But based on analytical data, the ideal price is determined to be $14,000. This means there's a $160 discrepancy. To adjust these values (while keeping them interdependent), we use the Goal Seek feature.

Place the cursor in cell C9, where the discrepancy value is calculated. Go to the DATA tab and select "What-If Analysis" and then "Goal Seek."

The Goal Seek dialog box appears. Each field is explained, making it easy to understand the steps we're taking. We want to achieve a zero value in cell C8, and the software will determine what to do with cell C3 (expected profit). Click OK.

Download

Now, cell DIFFERENCE displays 0, meaning the final price is indeed $14,000, as intended. Additionally, the software calculates that the expected profit under this scenario would be $1,864.4.