## How to use Proc Optmodel to solve for a Multiplier to minimize an objective?

Hi -

I currently have two datasets - forecast and guarantees (see attached). I have a portfolio of products each with a distinct product_id. In the forecast dataset I have projected sales and the current discount being realized by a client. In the guarantee dataset is the respective guarantee offered per product. You will notice that this particular client is getting a better deal on some products (discount > guarantee) and worse deals (discount < guarantee) on others.

My goal is to solve for a  common Factor that would be used to multiply by the discount of all the products so that the good deals and bad deals zero out (or common as close to 0 as possible).

See below what I've tried, which obviously is not working. Not sure where to go from here. Any help would be much appreciated! Thanks.

proc optmodel;
set <str> FORECAST;
set <str> GUARANTEES;
num discount {FORECAST};
num proj_sales {FORECAST};
num Guarantee {GUARANTEES};
read data work.forecast into FORECAST=[product_id] discount proj_sales;
read data work.guarantees into GUARANTEES=[product_id] Guarantee;

var factor {FORECAST};

impvar new_rate {fac in FORECAST} = discount[fac] * factor[fac];
impvar variance {fac in FORECAST} = new_rate[fac] - Guarantee[gtee]; /*ERROR: gtee is unknown*/
impvar sales_performance {fac in FORECAST} = variance[fac] * proj_sales[fac];

/* I'm not sure how to set a minimum objective with the constraint that it has to be >= 0 */
min x = sum {fac in FORECAST} sales_performance[fac];

con c1:
x >= 0;

solve;
print factor;

quit;

1 ACCEPTED SOLUTION

Accepted Solutions

## Re: How to use Proc Optmodel to solve for a Multiplier to minimize an objective?

If I understand correctly, you want a common value of factor across all products, so your problem has only one decision variable (not one per product), as follows:

``````proc optmodel;
set <str> PRODUCTS;
num discount {PRODUCTS};
num proj_sales {PRODUCTS};
num Guarantee {PRODUCTS};
read data work.indata into PRODUCTS=[product_id] discount proj_sales Guarantee;

var factor;

impvar new_rate {p in PRODUCTS} = discount[p] * factor;
impvar variance {p in PRODUCTS} = new_rate[p] - Guarantee[p];
impvar sales_performance {p in PRODUCTS} = variance[p] * proj_sales[p];

con c1: sum {p in PRODUCTS} sales_performance[p] = 0;

solve;
print factor;
quit;
``````

The resulting value for factor is:

 1.14663

But that approach is overkill.  Your constraint reduces to the following equation, where factor is the only variable:

sum {p in PRODUCTS} (discount[p] * factor - Guarantee[p]) * proj_sales[p] = 0

Solving this linear equation algebraically for factor yields the explicit formula:

factor = (sum {p in PRODUCTS} Guarantee[p] * proj_sales[p]) / (sum {p in PRODUCTS} discount[p] * proj_sales[p])

As you can check, this formula also yields:

 1.14663
4 REPLIES 4

## Re: How to use Proc Optmodel to solve for a Multiplier to minimize an objective?

If I understand correctly, you want a common value of factor across all products, so your problem has only one decision variable (not one per product), as follows:

``````proc optmodel;
set <str> PRODUCTS;
num discount {PRODUCTS};
num proj_sales {PRODUCTS};
num Guarantee {PRODUCTS};
read data work.indata into PRODUCTS=[product_id] discount proj_sales Guarantee;

var factor;

impvar new_rate {p in PRODUCTS} = discount[p] * factor;
impvar variance {p in PRODUCTS} = new_rate[p] - Guarantee[p];
impvar sales_performance {p in PRODUCTS} = variance[p] * proj_sales[p];

con c1: sum {p in PRODUCTS} sales_performance[p] = 0;

solve;
print factor;
quit;
``````

The resulting value for factor is:

 1.14663

But that approach is overkill.  Your constraint reduces to the following equation, where factor is the only variable:

sum {p in PRODUCTS} (discount[p] * factor - Guarantee[p]) * proj_sales[p] = 0

Solving this linear equation algebraically for factor yields the explicit formula:

factor = (sum {p in PRODUCTS} Guarantee[p] * proj_sales[p]) / (sum {p in PRODUCTS} discount[p] * proj_sales[p])

As you can check, this formula also yields:

 1.14663

## Re: How to use Proc Optmodel to solve for a Multiplier to minimize an objective?

This worked!

For the linear equation would you just replace the solver call with the formula you mention?

## Re: How to use Proc Optmodel to solve for a Multiplier to minimize an objective?

You can replace everything after READ DATA with this one line:

``````   print ((sum {p in PRODUCTS} Guarantee[p] * proj_sales[p]) / (sum {p in PRODUCTS} discount[p] * proj_sales[p])) best32.;
``````

## Re: How to use Proc Optmodel to solve for a Multiplier to minimize an objective?

Wow, that simplifies things. Can't express how thankful I am!

Discussion stats
• 4 replies
• 831 views
• 0 likes
• 2 in conversation