Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Make Price Optimization Models in Excel (with Easy Steps)

In this tutorial, I am going to show you step-by-step procedures on how to make price optimization models in Excel. You can use these steps for any type of dataset and product to quickly find out the most profitable price for your business. Throughout this tutorial, you will also learn some important Excel tools and techniques which will be very useful in any excel related task.


Download Practice Workbook

You can download the practice workbook from here.


Step-by-Step Procedures to Make Price Optimization Models in Excel

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 6 rows and 2 columns. Initially, we are keeping all the cells in General format and the monetary values in Accounting format. For all the datasets, we have 2 unique columns which are Product Price, and Quantity Sold. Although we may vary the number of columns later on if that is needed.

price optimization models excel


Step-1 Creating Base Dataset in Excel

In this first step, we will be creating the necessary dataset in Excel considering a single product price. Follow the steps below to do this.

  • First, create a dataset with two columns with the names Product Price and Quantity Sold.
  • Next, select the cells under the Product Price column and go to the Home tab at the top of the screen.
  • Under the Number, section click on Accounting Number Format.

creating dataset to make price optimization models in Excel

  • As a result, this will convert the price values into Dollar format as in the image below.

formatted data to make price optimization models in Excel

Read More: Excel Optimization with Constraints (3 Case Scenarios)


Step-2 Generating Trendline Equation

After we have created a dataset for our optimization model now is the time to generate a trendline chart which will be the core of our mathematical model. Let us see how we can do this.

  • To begin with, select the whole dataset and go to the Insert tab at the top of the screen.
  • Then, click on the Scatter chart icon.

inserting chart to make price optimization models in Excel

  • Consequently, this will plot the data points in a scatter chart.

  • Next, click on any of the data points and select Add Trendline.

adding trendline to make price optimization models in Excel

  • Now, click on Linear under the Format Trendline pane on the right side.

linear trendline to make price optimization models in Excel

  • Then, check the Display Equation on chart box to show the trendline equation.

  • Immediately, this will draw a trendline through the data points with the equation.

Read More: How to Solve Linear Optimization Model in Excel


Step-3 Making Price Optimization Models Table in Excel

In this third step, we will insert a price optimization table where we will get the final results. See the steps below to achieve this.

  • For this step, create a data table with the Parameter and Value
  • Then, insert the necessary parameters such as Minimum Price (x), Quantity (y), Cost Per Unit, Profit Per Unit, and Total Profit.

  • Now, insert the minimum price value and type in the following formula in cell C6:
=-(43.143*C5) + 538.57

  • After that, press Enter and this will give the value of the quantity or y variable in this case.
  • Then, enter the cost per unit value and type in the below formula in cell C8:
=C5-C7

  • Again, press the Enter key and you should get the profit per unit value.

  • Then, insert the following formula in cell C9:
=C5*C6

  • Finally, this will calculate the total profit value in cell C9.

Read More: How to Do Portfolio Optimization Using Excel Solver


Step-4 Creating Optimization Model

Now, we need to set up the solver in Excel to properly find out the optimal price for our product. Follow the steps below for this.

  • To start with this final step, go to the Data tab and click on Solver. If you want to know how to add the Solver option to Excel, you can check out this link.

  • Now, in the Solver Parameters, insert $C$9 as the Set Objective field value.
  • Next, set the To value to Max.
  • Then, in the By Changing Variable Cells field, enter $C$5.
  • Now, click on Add to add some constraints.

  • Here, type $C$5 in the Cell Reference field and =$C$5 in the Contraint field as values.
  • After that, click on OK.

  • Now, click on Ok again on the Solver Parameters window as in the image below.

  • As a result, this solver model will optimize the minimum price value according to the set constraint to maximize the total profit.


Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to make price optimization models in Excel. As you can see, it involves quite a few steps to achieve this. So carefully follow these while applying them to your own dataset. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

Nazmul Hossain Shovon

Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo