Have to solve it with Excel (Solver) Thanks for the help his case deals with str
ID: 3129749 • Letter: H
Question
Have to solve it with Excel (Solver)
Thanks for the help
his case deals with strategic planning issues for a large company. The main issue is planning the company's production capacity for the coming year At issue is the overall level of capacity and the type of capacity-for example, the degree of flexibility in the manufacturing system. The main tool used to aid the company's planning process is a mixed integer linear programming (MILP) model. A mixed integer represent a major expense for the company. The retooled plants would have significantly increased production capacities. Although having greater fixed costs, the retooled plants would be more efficient and have lower marginal production costs-that is, higher marginal profit contributions. In addition, the retooled plants would be flexible they would have the capability of producing more than one line of cars. has both integer and continuous variables. The characteristics of the current plants and the retooled plants are given in Table 6.16. The retooled Lyra and Libra plants are prefaced by the word new The fixed costs and capacities in on an annual basis. A dash in the profit margin sec- tion indicates that the plant cannot manufacture that line of car. For example, the new Lyra plant would be capable of producing both Lyras and Libras but not Hydras. The new Libra plant would be capable of producing any of the three lines of cars. Note, how- ever, that the new Libra plant has a slightly lower profit margin for producing Hydras than the Hydra plant. The flexible new Libra plant is capable of pro- ducing the luxury Hydra model but is not as efficient as the current Hydra plant that is dedicated to Hydra production. Problem Statement lines of cars for the domestic (U.S.) market: Lyras, afohe Dealsit asa Libras, and Hydras. The Lyra is a relatively inexpen- sive subcompact car that appeals mainly to first-time car owners and to households using it as a second car for commuting. The Libra is a sporty compact car that is sleeker, faster, and roomier than the Lyra. Without any options, the Libra costs slightly more than the Lyra; additional options increase the price. The Hydra is the luxury car of the GMC line. It is significantly more expensive than the Lyra and Libra, and it has the highest profit margin of the three cars. Motor Company (GMC) produces three able 6.16 are given The fixed costs are annual costs incurred by GMC, independent of the number of cars produced by the plant. For the current plant configurations, the fixed costs include property taxes, insurance, pay ments on the loan that was taken out to construct the plant, and so on. If a plant is retooled, the fixed costs will include the previous fixed costs plus the additional cost of the renovation. The additional Retooling Options for Capacity Expansion Currently GMC has three manufacturing plants in the United States. Each plant is dedicated to producing a single line of cars. In its planning for the coming year, GMC is considering the retooling of its Lyra and/or Libra plants. Retooling either plant would Table 616 Plant Characteristics Libra Hydra New Lvra New Libra ra Capacity (in 1000s) Fixed cost (in $millions) 1000 2000 800 2000 900 2600 1600 3400 1800 3700 Profit Margin by Car Line (in $1000s) Libra Hydra 2.5 3.0 2.3 3.5 4.8Explanation / Answer
SUBJECT TO
Definitional constraints: Profit and Cost
At the Lyra plant, the profit margin for a Lyra is $2,000 per car. Given the production P1 units (in 1000s of cars), the total profit margin at the Lyra plant is 2P1 (million dollars.)
2) PROFIT - 2P1 – 3P2 - 5P3 – 2.5P4A - 3P4B – 2.3P5A – 3.5P5B – 4.8P5C = 0
If the Lyra plant is in production (LY = 1), the fixed cost is 2000LY million dollars.
If not (LY = 0), the fixed cost is 2000LY = 0 dollars.
3) - 2000LY - 2000LI - 2600HY - 3400NLY - 3700NLI + COST = 0
Logical constraints:
Should capacity at the Lyra and Libra plants be expanded
Either the Lyra plant is expanded or not.
4) LY + NLY = 1
Either the Libra plant is expanded or not.
5) LI + NLI = 1
Capacity constraints:
! If LY = 1 (i.e., the Lyra plant is in production), then the production (P1) cannot exceed 1000 cars.
! If LY = 0, then the production (P1) cannot exceed 0 cars since the plant is not in production.
6) – 1000 LY + P1 <= 0
7) – 800 LI + P2 <= 0
8) – 900 HY + P3 <= 0
9) – 1600 NLY + P4A + P4B <= 0
10) – 1800 NLI + P5A + P5B + P5C <= 0
Solution: The company should expand the capacity of the Lyra and Libra
plants. The production plan is to produce 1.4 and 0.1 million Lyras and Libras,
respectively, at the New Lyra plant and 1.0 million Libras at the New Libra
plant. A total of 0.8 million Hydras should be produced at the New Libra plant.
The resulting profit is 4.04 billion dollars.]
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.