Solver add-in linear programming problem

I've been given a problem where a company produces a product in 4 quarters, demand varying each quarter. Production is limited each quarter and so an inventory must be built up to supply to greater demand in the later quarters for which production cannot meet demand. If demand is not met instead the company can pay a penalty fee, but if inventory is greater than demand the company must pay a holding fee. These fees vary per quarter and the company wants to know by how much they should under/overproduced each quarter in order to minismise costs. The problem is the assignment requires me to solve this linearly and the only way I can think to model the costs is using an IF function (ie If production - demand > 0 then pay this holding fee, if production - demand <0 then pay this penalty fee) and this means the model is nonlinear. How can I avoid using an IF function and model the problem linearly?
Thank you

I've been given a problem where a company produces a product in 4 quarters, demand varying each quarter.

Typical homework I would say and forum rules says

In this forum we cannot allow direct help with homework or assignment questions, if we tell you how to do it you will never learn how to do it! We can answer specific questions and point you in the right direction, if you really don't understand your work/assignment then don't be afraid to contact your tutor as he/she will be pleased you came forward for help rather than struggle. One other thing, your tutor(s) may also frequent this and many other forums like it, either to help the community or sourcing training aids for their pupils so be mindful of this when seeking help!

Still we are allowed to "point you in the right direction" so if you upload the file with the problem I think some forum members may be able to help you a bit.

Alf

this is the exact problem if someone could give me some guidance as I'm at a complete dead end on this one!

I am thinking you don't need a solver for this one. MRP systems are backward scheduling, and that principle might be useful in this situation as well.

Im sure youre right that it can be done without a solver but the assignment specifically states to be solved using the Solver add-in on microsoft excel with linear programming model selected.

So I would have columns to represent all of your variables so that the formula in the dollars column will change based on the quantity column. That way you can tell the solver to calculate your lowest value by only changing the qty produced. In other, words make it linear by making enough columns to capture bothe the variable of having too much or too little.

Or to think of it differently, you get dinged if your ending inventory is not equal to zero. How does one calculate ending inventory?

Also the formulas you create are where the if statement should be if they are created correctly only units produced will the solver have to calculate.

Thank you for your input, I think I have already done what you have said, but any use of an IF function means that the model is no longer linear. I need a way of avoiding an IF function altogether. I have posted my current model along with this message if that helps as I'm still stuck!

Also this appears to give a fairly good answer if nonlinear model is selected but I need to solve this with that linear model ticked as that is the point of the assignment, in which case I need a way to replace the IF function?

Why does an if statement imply non-linear?

I'm not sure as I'm fairly new to this but my lecturer tried to describe it as the function needs to be 'constant' and here the IF function allows 2 different potential 'paths' for the function to take so is not constant. I just know that he said that IF functions won't work with a linear programming model and that it is possible to formulate a model without using them.

Well he is asking a non-linear question then. Because he is asking what is the optimal units to produce which implies that there are two paths what happens when you under or over produce... Or an if statement... With all due respect.

That's why I'm so confused as it is a LINEAR programming module and the question seems not to be linear. Is there a way of introducing new variables to make it linear or solve it in two steps or something?

To me it is still linear because you start off with producing up to full demand in each period as capacity allows. So you are only producing in other periods to satisfy the demand in the final quarter.

Hi Stewart

As you have made an effort trying to solve the problem I think forum rules will allow me to upload the model I set up for solving this problem. By the way your forum profile says "Excel 2003" but your uploaded file is an ".xlsx" file i.e. "Excel 2007" or "Excel 2010" but my model is based on your forum profile.

Since penalty for not producing is higher than stock holding the production for each quarter + stock must at least match demand and stock holding should of course be kept to a minimum. Assuming production is "units" I've set an "Integer" constraint on production.

As for calculating stock holding costs I would have used an average of "opening" and "closing" stock but adding these costs and dividing by two will make the model non linear so I've used opining stock for cost calculation.

Alf

Hi Alf, thanks for this, but there is a problem in that the 16 units produced in quarter 2 incur holding cost twice, both in quarter 2 and 3 so it is actually cheaper to not meet demand in the final quarter and incur costs of £7 rather than the £12 per unit that it is costing to hold the 16 units produced in quarter 2. I have uploaded a new spreadsheet where i think the total cost is less than on your spreadsheet.

I have uploaded a new spreadsheet where i think the total cost is less than on your spreadsheet.

Yes you are right about that.

Alf