LunaLove is a pet store that sells three different types of dog food: Premium, S

Question

LunaLove is a pet store that sells three different types of dog food: Premium, Silver, and Standard. The demand and cost information for these three types of dog food are given below:

Dog Food Type

Demand

Fixed Order Cost

Per bag cost

Per bag holding cost

A.) Find the number of bags that should be ordered for each type of dog food that minimizes the total cost? Assume that at least one bag should be ordered of each. Solve using Excel solver.

B.) Let’s say that Ms. Love can store up to 1000 lbs of dog food and the weights of the bags for Premium, Silver, and Standard are 6,3, and 10 lbs, respectively. How do the order sizes change? Solve using excel solver.

Dog Food Type

Premium Silver Standard

Demand

200 300 500

Fixed Order Cost

$300 $450 $200

Per bag cost

$10 $7 $5

Per bag holding cost

$3 $2 $1

Explanation / Answer

A.) Objective function

Min : 313a+459b+256c

Constraints

a,b,c 1

a 200

b 300

c 500

A is for premium, b for silver and c for standard dog type food

Solution:

B.) There is one more constraint

6a+3b+10c 1000

Solution

Objective Cell (Min) Cell Name Original Value Final Value $F$8 total cost Sum total 0 978 Variable Cells Cell Name Original Value Final Value Integer $C$3 Required premium 0 1 Contin $D$3 Required silver 0 1 Contin $E$3 Required standard 0 1 Contin Constraints Cell Name Cell Value Formula Status Slack $C$3 Required premium 1 $C$3<=200 Not Binding 199 $C$3 Required premium 1 $C$3>=1 Binding 0 $D$3 Required silver 1 $D$3<=300 Not Binding 299 $D$3 Required silver 1 $D$3>=1 Binding 0 $E$3 Required standard 1 $E$3<=500 Not Binding 499 $E$3 Required standard 1 $E$3>=1 Binding 0

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.