Download Aggregate Planning - Introduction to Operations Management - Lab Manual and more Study notes Business Management and Analysis in PDF only on Docsity!
Aggregate Planning
Aggregate Planning
Agenda:
- Setting up the model
- Solver
- SolverTable
- Additional poblems
- 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 w would like maximize our revenue. Since Solver does not allow you to have multipl 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:
- Multiply the current production plan by the associated PCI in order to get a single value for our index:
- Calculate a similar value for revenue:
- Create a column that will calculate the same values under our 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 revenu 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? Assum 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? Wha
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
- 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.
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 increas 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” shee 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 w
options menu you change the tolerance to be 0.05% - this will change th integer tolerance.
- Run the solver to maximize revenue and compare the result to the origin 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 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 leas 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 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 produce 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 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 decid 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.
