• Originally published: 06/08/2020 12:24
Last version published: 10/10/2020 10:43
Publication number: ELQ-67447-2
View all versions & Certificate

Product Pricing Model (e-commerce)

A model analysing two scenarios used to find the profit-maximising sale price of a product to be released.

Description
This model uses Excel Solver to allow the tool find the best price for a new product to maximise the revenue within the first 18 months of the product release.

The optimum price is based on recommended figures from the client as well as on the assumptions gathered from the company website, industry statistics and publicly available resources (EU commission).

The model produces a detailed statement of comprehensive income (profit and loss) for two categories of products:

- The basic version of the product

The purpose of the model is to find the profit-maximising price for the premium product line within the first 18 months of the product launch date.

The model takes into account seasonality of product sales in the industry (obtained from the manufacturer's website) and industry-specific marketing expenses such as price protection expense to retailers. Fulfilment and general administrative expenses have been excluded however they are ready to be added in the model if required.

Note that using Excel Solver allows model user to find the optimal value (here selling price) within the specific constraints. Here, the constraint was the selling price of the basic product version (for \$59.99) and the ratio of premium / basic product category price which should be maintained at all times (please go to Data -> Solver in this workbook to check the assumptions used to find the profit-maximising price).

This Best Practice includes
1 Excel file with the model and supporting schedules.

Discuss

Further information

Objectives

The objective of the tool is demonstrating how Excel Solver can be used to find the best alternative based on the accepted assumptions and constraints.

Use it if

the model can be best used for price setting exercises or wherever using Excel Solver is required to find the optimal value (here retail price) for a product.

Don't use it if

This is not a three-statement model - the model is a profit projection and contains a statement of profit and loss statement.

See all

Discussion feed for Product Pricing Model (e-commerce)

The user community and author are here to help. Go ahead!

• Hi, I am interested to know more about the pricing model. Can you please elaborate on the specific constraints considered while using Solver. I am not able to find it in the excel file.
• Hi Bharath,

Thank you for your question and for letting me explain the constraints used in this exercise in more detail.
The end goal of the model is to find the price which helps maximize the forecasted net income.
If you go to Data -> Solver you will note that the Solver objective was set up to maximize the value in cell E109 (which is the Net Income cell).
This is done by Solver by Changing Variable Cell, i.e. the price for the standard type of the product in row 12 (here you can see that the originally suggested price for the base product was \$59 however after using Solver the new optimal price is \$21.69).
If you have any further questions please let me know and thanks once again for your comment.