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

[dsklein12]

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;

[RobPratt · Accepted solution]

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.14663218567975

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.14663218567975

[dsklein12]

This worked!

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

[RobPratt]

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.;

[dsklein12]

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