Class Notes
(806,814)

Canada
(492,451)

York University
(33,494)

Marketing
(184)

MKTG 2030
(72)

Ben Kelly
(12)

Lecture 3

# Lecture 3.pdf

Unlock Document

York University

Marketing

MKTG 2030

Ben Kelly

Winter

Description

OMIS2000 Lecture 3 Jessica Gahtan
Lecture 3: Common Business Applications and Excel Solver
Common Business Applications
Linear
Programming
(LP)
can
be
used
for
many
managerial
decisions :
- Product mix
- Media selection
- Marketing research
- Portfolio selection
- Shipping & transportation
- Multi-period scheduling
For
a
particular
application
we
begin
with
the
problem
scenario
and
data,
then:
1. Define the decision variables
2. Formulate the LP model using the decision variables
- Write the objective function equation
- Write each of the constraint equations
3. Implement the model in Excel Solver
4. Solve
Common
Business
Applications
Product Mix
- Usually involve maximizing profit subject to:
- Production resource constraints
- Material Availability constraints
- Standing orders
- Quotas
- Maximum or minimum proportions
Example
1
–
Product
Mix
Imagine that you are managing a factory that is building three products: TV sets, stereos and
speakers. Each product is assembled from parts in inventory, and there are five types of parts:
Chassis, picture tubes, speaker cones, power supp lies and electronics units. Your goal is to
produce the mix of products which will maximize profits, given the inventory of products on hand.
Assume that you can sell TV sets for a gross profit of $75 each, stereos for a profit of $50 each,
and speaker for $35 each.
To assemble a TV set, you need 1 chassis, 1 picture tube, 2 speaker cones, 1 power supply and
2 sets of electronics. To make a stereo, you need 1 chassis, 2 speaker cones, 1 power supply
and 1 set of electronics. And to build a speaker, all you need is 1 speaker cone and 1 set of
electronics. The parts you have on hand are 450 chassis, 250 picture tubes, 800 speaker cones,
450 power supplies and 600 sets of electronics. You can build only a limited number of products
from the parts on hand.
a) Formulate the LP model to Maximize the profit.
b) Solve using Solver
Page 1 of 17 OMIS2000 Lecture 3 Jessica Gahtan
Example
1
-‐
Solution
Step 1: Define the objective
- Maximize the profit
Step 2: Define the decision variables
x1= number of TV sets assembled
x2= the number of stereos assembled
x3= the number of speakers assembled
Step 3: Write the mathematical objective function
Maximize Z = 75 x + 50 1 + 35 x 2 3
Step 4: Formulate the constraints
1 x 1 1 x 2 ≤ 450 (Chassis)
1 x ≤ 250 (Picture tubes)
1
2 x 1 2 x +21 x 3 ≤ 800 (Speaker cones)
1 x 1 1 x 2 ≤ 450 (Power supplies)
2 x 1 1 x +21 x 3 ≤ 600 (Electronics)
x1, x 2 x 3 ≥ 0
Step 5: Final Formulation
Maximize Z = 75 x + 50 1 + 35 x 2 3
S.t:
1 x 1 1 x 2 ≤ 450 (Chassis)
1 x ≤ 250 (Picture tubes)
1
2 x 1 2 x +21 x 3 ≤ 800 (Speaker cones)
1 x 1 1 x 2 ≤ 450 (Power supplies)
2 x 1 1 x +21 x 3 ≤ 600 (Electronics)
x1, x 2 x 3 ≥ 0
Some
helpful
notation
xi= # of units of product i produced
p=iprofit per unit of product i
r = amount of resource j needed to produce one unit of product i
ij
A j amount of resource j available
i= {1,2,3} and j={1,…,5}
Solver
Solution
Page 2 of 17 OMIS2000 Lecture 3 Jessica Gahtan
Investment Portfolio
– Usually involve maximizing return subject to
• Maximum risk constraints
• Maximum or minimum prop ortions in various asset classes
– OR
– Minimizing risk subject to
• Minimum return constraints
• Maximum or minimum proportions in various asset classes
Example
2a
–
An
Investment
Example
Welte Mutual Funds, located in New York City, just obtained $100,000 by co nverting industrial
bonds to cash and is now looking for other investment opportunities for these funds. Based on
Welte’s current investments, the firm’s top financial analyst recommended that all new
investments be made in the oil industry, steel industry or in government bonds. Specifically, the
analyst indentified five investment opportunities and projected their annual rates of return. The
investments and rates of return are listed below. Management of Welte imposed the following
investment guidelines:
1. Neither industry (oil or steel) should receive more than $50,000
2. Government bonds should be at least 25% of the steel industry
investment.
3. The investment in Pacific Oil, the high -return
but high risk investment, cannot be more
than 60% of the total oil industry investment.
What portfolio recommendations - investments
and amounts, should be made with the
available $100,000?
Example
2a
–
Solution
Step 1: Define the objective
• Maximize the return
Step 2: Define the decision variables
A - Dollars invested in Atlantic Oil
P - Dollars invested in Pacific Oil
M - Dollars invested in Midwest Steel
H - Dollars invested in Huber Steel
G - Dollars invested in Government Bonds
Step 3: Write the mathematical objective function
Maximize Z = 0.073A+ 0.103P+0.064M+0.075H+0.045G
Step 4: Formulate the constraints
1. Welte just obtained $ 100,000 by converting industrial
bonds to cash and is now looking for other investment opportunities for these funds.
A+P+M+H+G=100,000
2. Neither industry (oil or steel) should receive more than $50,000
A + P ≤ 50,000
M + H ≤ 50,000
3. Government bonds should be at least 25% of the steel industry investment.
4. The investment in Pacific Oil, the high return but high-risk investment, cannot be more
than 60% of the total oil industry investment.
Page 3 of 17 OMIS2000 Lecture 3 Jessica Gahtan
Step 5: Final Formulation
Solver
Solution
Diet Problems
– Usually involve minimizing cost of diet subject to
• Minimum and maximum nutritional requirements
Example
3
–
Diet
Problem
Lifegym, a health and fitness cente r, operates a morning fitness program for senior citizens. The
program includes aerobic exercise, either swimming or step exercise, followed by a health
breakfast in the dining room. Lifegym’ dietitian wants to develop a breakfast that will be high in
calories , calcium, protein and fiber, which are especially important to seniors, but low in fat and
cholesterol. She also wants to minimize cost. She has selected the following possible food items,
whose individual nutrient contributions and cost from which to develop a standard breakfast menu
are shown in the slide.
Page 4 of 17 OMIS2000 Lecture 3 Jessica Gahtan
Diet Problem – Decision Variables
x1= cups of bran cereal
x2= cups of dry cereal
x3= cups of oatmeal
x4= cups of oat bran
x5= eggs
x6= slices of bacon
x7= oranges
x8= cups of milk
x9= cups of orange juice
x = slices of wheat toast
10
Diet Problem – Formulation
Formulation - Excel
Page 5 of 17 OMIS2000 Lecture 3 Jessica Gahtan
Diet Problem – Solution
Blending Problems
– May be similar to diet problems in that we may minimize the cost of formu lating
subject to
• Minimum and maximum component requirements
– Alternatively we could be maximizing margin or profit earned
Example
4a
-‐
A
Blend
Example
Formulation
•

More
Less
Related notes for MKTG 2030