Mathematical Optimization, Discrete-Event Simulation, and OR

Operations Research topics: SAS/OR,
SAS Optimization, and SAS Simulation Studio
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dsklein12
Calcite | Level 5

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
RobPratt
SAS Super FREQ

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

View solution in original post

4 REPLIES 4
RobPratt
SAS Super FREQ

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
Calcite | Level 5

This worked!

 

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

RobPratt
SAS Super FREQ

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
Calcite | Level 5

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

sas-innovate-white.png

Join us for our biggest event of the year!

Four days of inspiring keynotes, product reveals, hands-on learning opportunities, deep-dive demos, and peer-led breakouts. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

Register now!

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