Top of Form

Bottom of Form

WINTER 2007 MGTSC 352 LEC B1DOCUMENTSLABS > LAB 6 - AGGREGATE PLANNING (WESTPLAST)

Lab 6 - Aggregate Planning (Westplast)

/ Course Documents
Lab6_Westplast (59 Kb)
Lab 6 - Aggregate Planning (Westplast)
March 2, 2007
Agenda:
1. Setting up the model
2. Solver
3. SolverTable
4. Additional poblems
5. Solutions
Setting up the model
The first thing that you may notice about this case is that there are two objectives. We would like to maximize the Plant Capability Index (PCI) but at the same time we would like maximize our revenue. Since Solver does not allow you to have multiple objectives, we will have to use alternative methods in order to provide a practical solution to this model.
Calculating the Indexes and Revenue for the current plan:
1. Multiply the current production plan by the associated PCI in order to get a single value for our index:

2. Calculate a similar value for revenue:

3. Create a column that will calculate the same values under our plan:

4. Calculate how much of our capacity we are using under each plan.

Solver
Now that we have created our model we need to try and find a better solution than the first plan. One option is to run Solver and make sure that the Total Index amount does not decrease from the current plan, while trying to maximize revenue. The solver settings for this option are as follows:

Constraints were added to ensure:
i) Our plan's total index is greater than or equal to the current plan's total index
ii) Production does not exceed 350,000 tons
iii) Production is not more demand for each product
iv) Production is sufficient to meet the contractual amounts required for each product
Additional Questions for the Problem
Each question should be considered to be individual. Reset your settings to the original solution for each subsequent problem.
a) What is the impact of the contractual obligations on the decision? Could the company come up with a better product mix if the contractual obligations were treated as simple demand (without an obligation to fill them)?
b) How would expectations of changes in currency exchange rates influence the product mix? Specifically, suppose that the executives expect a 5% increase in the value of the US dollar during the year. Would that change the product mix? Assume that the price paid by customers in the US, as measured in US dollars, does not change.
c) The company chose to produce pellet D since they wanted to keep Smitheren Ltd. (the only buyer) a satisfied customer. This is perceived as desirable, in case of a future downturn in the economy. What is the economic cost of this choice to the company?
d) Washington Plastics, Inc. in Seattle may undersell WestPlast in the US market. If the executives of WestPlast notice an attempt in this direction, what is their best counter-strategy?
e) Criticize the company’s production plan considering the marketing strategy of the company “fewer products, greater tons”. Suppose it is possible to increase the production capacity of the mill by 5% by dropping one product from the product mix. Is it possible to improve upon the product mix by dropping one product? What percent-increase in the capacity is necessary to justify the dropping of one product?
Solver Table Time!
Solver Table is an effective tool for sensitivity analysis. To download SolverTable click here.
Instead of maxmizing revenue with a minimum limit on the PCI index, why not combine the two objectives using weighting?


We want to vary the weight for the PCI index and capture the index and revenue outcomes to generate the efficient frontier of solutions. Before setting up the SolverTable we need to solve the problem with Solver. We no longer have the minimum limit on the PCI index, so this constraint is removed. We also want to change the objective cell to the weighted objective.

Now that Solver is set up we can use SolverTable to compute the various weight combinations. SolverTable is similar to a data table. Basically it takes new values, runs Solver each time in the 'background', and returns the outputs for each run.
Go to Data -> SolverTable.
We want to create a oneway table since we are only varying one parameter (the PCI weight).
We want to vary the PCI weight. The minimum value is 0 and the maximum value is 1. The variation will be in increments of 0.1. The output cells are the solution values we want to track. Select all the of the decision cells as well as the Total Index and Total Revenue. The location of the table can be anywhere on your worksheet. Note that it will overwrite anything in it's way so choose your location carefully.

After changing the headings, here is what the outputs should look like.

Solutions
a) To complete this question we must remove the constraint that production must be sufficient to meet the contractual obligations and run Solver again. Compare this to the original solution.

We could increase our revenue by 42 if we did not need to meet contractual obligations.
b) There are two export products. A 5% increase in the value of the US$ will increase our cash flows by 5% on these two products. You cannot use the sensitivity report to answer this question since we are changing more than one number in the problem. You need to increase the revenues of the export products by 5% and resolve.
Rewrite the Total Revenue formula so that you are multiplying by the new revenue.


Rerun Solver. When you do this, you will notice that the solution (product mix) does not change. Of course, the revenue goes up.
c) To evaluate this cost, change the Pellet D contract figure from 0 to 35 and run the solver again. You will find a revenue reduction of 0.68%.
d) The allowable decrease figures for the two exports in the “sensitivity report” sheet tell you how much room you have to reduce your unit revenues without changing the optimal mix--there is some room to counter a price reduction by the competitor. However, if you want to find the impact of reducing both export unit revenues simultaneously, then you have to run the solver again with new revenue figures.
e) To complete this problem we will have to add a new set of variables. These will allow us to determine the amount of products to produce
For the first question:
  • Introduce a new column titled Yes/No. The 0-1 variables in this column determine whether or not to produce a product. Sum up this column to track the number of products we're producing.
  • Introduce another new column, titled “Conditional Demand”, which is the product of the demand with the Yes/No variable for each product. Note we cannot multiply changing cells by changing cells, as this would make our problem non-linear. This is the reason we do not simply multiply our Yes/No variable by the variables listed under "Our Plan"

  • Compute the % increase in capacity as the difference between the number of products produced in the original plan and the new plan multiplied by 5%.
  • Change the capacity to =335*(1+% increase)
  • Add the Yes/No variables to the changing cells.
  • Add a constraint that says the Yes/No variables are “binary”.

  • Add a new constraint: Production Conditional Demand and remove the Production Demand constraint.
  • Add another new constraint that sets the sum of the Yes/No variables the total number produced in the original plan.
  • Change integer options tolerance to be 0.0005 (instead of default of 1%). In Premium solver, you select the integer options button then inside the tolerance input box you change this. At home when you go into the regular options menu you change the tolerance to be 0.05% - this will change the integer tolerance.
  • Run the solver to maximize revenue and compare the result to the original solution
For the second question:
The idea behind this question is that by dropping one less product you gain production capcity. If you were to not increase capcity and just drop one product, you will not be able to achieve the needed revenue and index needed to improve the solutions. You can't do it becuase there is no product mix that will allow you to stay within the capacity constraints while still achieving the CPI index and revenue that you would get with 9 products. In order to have at least the same values for index and revenue while also dropping one product (changing the max number of products allowed to be 8 instead of 9) there has to be an increase in capacity. This question is trying to determine what the minimum percentage that is needed to achieve at least the same index and revenue values while only having 8 products. Because we are mimimizing the percentage increase we know that the number of products that will be produced is 8 and this is because of what was discussed in the previously. For each product that you drop, if you still want to achieve the same revenues and index, production needs to increase and to see the minimum production increase, you will produce the maximum number of products. You'll notice when you drop to 7, the problem is infeasible. Why? Just some things to think about on your own.
How to solve this problem:
  • Keep all the changes from above
  • Target: minimize percentage increase in production
  • Decisions: add your target cell as a decision sell, since we want Solver to decide how much this should be
  • Create two new constraint: that Revenue the revenue from the plan found original solution, and Number of products produced the number found in part 1 of this question.
  • The capacity should now be calculated as follows:

The optimal increase in production is 0.27%. If you have an answer of 0.7318% you have not re-adjusted your tolerance level for the binary integer constraints. See the constraints from the first part of this problem above for an explination on how to do this.