CODEWITHSUNDEEP
excelsolver
Pete
Pete

Reputation: 47

Set stable prices of product, while breaking even (Excel)

The case:

Let us say a company is a monopolist in the market, and the supply of products is 100% met by demand every week. All (variable and fixed) costs are set. Based on estimations I have weekly units produced. In addition, the average price throughout the year has to hit approximately X. It is preferrable that the monthly results don't vary too much from break even. How can I quickly set the weekly price for the company’s product so that the company breaks even (Result = approx. 0), and at the same time keep the weekly price at the most stable price possible (as close to average as possible every week).

I have tried to use solver to minimize STD.DEV of prices (also tried skewness), with constraints that the average price is X and estimated result = 0, by changing the weekly prices throughoutthe year. However, this results in a few weeks of extreme price differences, which is the opposite of what I need.

Do somebody have a possible solution to the problem?

Upvotes: 0

Views: 21

Answers (1)

Erwin Kalvelagen
Erwin Kalvelagen

Reputation: 16762

You could try the following:

minimize maxp - minp
maxp >= p(w)   for all w
minp <= p(w)   for all w

This will try to minimize the bandwidth (and additionally is completely linear)

Upvotes: 1

Related Questions