Question

DONE IN EXCEL WITH SOLVER - SHOW FORMULAS! ILL LIKE (:

USES LP MODEL/SUPPLY CHAIN MODEL

Byrds Bird Supply from Ozark, Missouri, blends together three bird food products (code named Cardinal, Finch and Chickadee) using three types of seeds – Sunflower, Mixed Bird Nuts and Miniflax – as well as a seed ‘Filler’.

Each lb. of Cardinal sells for $3.5/lb. Each lb. of Finch sells for $2.0/lb. Each lb. of Chickadee sells for $1.75/lb.

Each lb. of Sunflowers costs $1.3/lb, each lb. of Mixed Bird Nuts cost $1.6/lb, each lb. of Miniflax costs $2.1/lb. and each lb. of Filler costs $0.40/lb.

You can use up to 360lbs. of Sunflowers, 180 lbs. of Mixed Bird Nuts, 125 lbs. of Miniflax, and 100 lbs. of Filler.

Requirements for blending:

- Each lb. of Cardinal food must be made up of at least 60% Sunflower seeds and no more than 10% Miniflax seeds.

- Each lb. of Finch food must be made up of no more than 40% of any one type of the three seeds.

- Each lb. of Chickadee food must be made up of at least 50% Sunflower seeds and at least 25% Mixed Bird Nuts.

- Each food (all three) cannot have more than 5% of its total production be made from Filler.

- You cannot spend more than $1000 total on the seeds/filler.

- Each food (Cardinal, Finch, Chickadee) cannot make up more than 47.5% of the total combined amount of the three products, and each food must be at least 22% of the total combined blended products. (Ex: Thus, if 1000 total pounds combined of the three foods are made, each food product cannot be more than 475 lbs. but has to be at least 220 lbs.).

- Nutrition: The component seeds/filler have a nutrient ranking – higher is better than lower. Sunflower = 4, Mixed Bird Nuts = 5, Miniflax = 8, Filler =0. Each of the three foods (Cardinal, Finch, Chickadee) must have an average nutrition value of at least 4.7.

- Only whole number of lbs. are allowed for blending.

Find the maximum profit (sales – costs) way of blending together the 4 component seeds/fillers into 3 foods (Cardinal, Finch, Chickadee) such that all requirements above are satisfied.

Answer 1

Let

Cs be the quantity (lb) of Sunflower seeds to be used in Cardinal food

Cb be the quantity (lb) of Mixed Bird Nuts seeds to be used in Cardinal food

Cs be the quantity (lb) of Miniflax seeds to be used in Cardinal food

Cf be the quantity (lb) of Filler to be used in Cardinal food

Fs be the quantity (lb) of Sunflower seeds to be used in Finch food

Fb be the quantity (lb) of Mixed Bird Nuts seeds to be used in Finch food

Fs be the quantity (lb) of Miniflax seeds to be used in Finch food

Ff be the quantity (lb) of Filler to be used in Finch food

Ks be the quantity (lb) of Sunflower seeds to be used in Chickadee food

Kb be the quantity (lb) of Mixed Bird Nuts seeds to be used in Chickadee food

Ks be the quantity (lb) of Miniflax seeds to be used in Chickadee food

Kf be the quantity (lb) of Filler to be used in Chickadee food

--------------------------------

Create Excel model as follows:

EXCEL FORMULA:

Enter Solver Parameters

Click Solve to generate optimal solution. After pressing Click button , values appear automatically in yellow cells (B27:M27)

Click OK