Question 1

Exhibit 3.1
The following questions are based on this problem and accompanying Excel windows.
Jones Furniture Company produces beds and desks for college students. The production process requires carpentry and varnishing. Each bed requires 6 hours of carpentry and 4 hour of varnishing. Each desk requires 4 hours of carpentry and 8 hours of varnishing. There are 36 hours of carpentry time and 40 hours of varnishing time available. Beds generate $30 of profit and desks generate $40 of profit. Demand for desks is limited, so at most 8 will be produced.
 Let
$$\begin{array} { l } 
\mathrm { X } _ { 1 } = \text { Number of Beds to produce } \
\mathrm { X } _ { 2 } = \text { Number of Desks to produce }
\end{array}$$ The LP model for the problem is
 $$\begin{array} { l l } 
\text { MAX: } & 30 X _ { 1 } + 40 X _ { 2 } \
\text { Subject to: } & 6 X _ { 1 } + 4 X _ { 2 } \leq 36 \text { (carpentry) } \
& 4 X _ { 1 } + 8 X _ { 2 } \leq 40 \text { (varnishing) } \
& X _ { 2 } \leq 8 \text { (demand for desks) } \
& X _ { 1 } , X _ { 2 } \geq 0
\end{array}$$ 
-Refer to Exhibit 3.1. What formula should be entered in cell E5 in the accompanying Excel spreadsheet to compute total profit?

Accepted Answer

A)  =B4*B5+C4*C5 
B)  =SUMPRODUCT(B8:C8,$B$4:$C$4) 
C)  =SUM(B5:C5) 
D)  =SUM(E8:E10)

Question 2

A grain store has six types of grain, each varying in cost, quality, and nutritional content. Periodically, excess inventory of these grains are consolidated into two local products, Feed-M-All and Supreme-Feed. Feed-M-All sells for $6.50 for a 10-pound bag while Supreme-Feed sells for $8.50 for a 10-pound bag. These feeds are advertised as having the following nutritional content:
 $\begin{array}{lccc}
\text { Grain } & \text { Minimum Proten } &\text { Minimum Fat }&\text { Maximum Carbohydrates }\
\hline\text { Feed-M-All } & 16 \% & 18 \% & 10 \%\
 \text {Supreme-Feed} &18 \%&18 \% &9 \% \
\end{array}$
 The component grains have the following content characteristics:
 $$\begin{array} { c c c c c c c } 
\text { Grain } &\text { Cost/10lbs }& \text { Quality } & \text { Praten } & \text { Fat } & \text { Carbohydrates } & \text { Paunds Ayail. } \
\hline \mathrm { A } & \$ 4.75 & 4 & 15 \% & 10 \% & 10 \% & 90 \
\mathrm {~B} & \$ 4.00 & 2 & 20 \% & 20 \% & 8 \% & 120 \
\mathrm { C } & \$ 3.75 & 1 & 10 \% & 25 \% & 5 \% & 150 \
\mathrm { D } & \$ 4.25 & 3 & 15 \% & 20 \% & 10 \% & 125 \
\mathrm { E } & \$ 4.50 & 3 & 20 \% & 20 \% & 10 \% & 85 \
\mathrm {~F} & \$ 5.00 & 4 & 25 \% & 15 \% & 12 \% & 165
\end{array}$$ Targets for Feed-M-All are a cost of $ 4.35 per 10-pound bag, a quality rating of 2.25, along with the minimum percentages of protein and fat, and the maximum percentage of carbohydrates. Similar targets are set for Supreme-Feed with cost set at $ 4.60 and quality at 2.45. There must be at least a 70%-30% mix among these two local feeds.
Formulate an LP model for this product mix problem.

Accepted Answer

The answer of A grain store has six types of...

Question 3

Exhibit 3.4
The following questions are based on this problem and accompanying Excel windows.
A financial planner wants to design a portfolio of investments for a client. The client has $300,000 to invest and the planner has identified four investment options for the money. The following requirements have been placed on the planner. No more than 25% of the money in any one investment, at least one third should be invested in long-term bonds which mature in seven or more years, and no more than 25% of the total money should be invested in C or D since they are riskier investments. The planner has developed the following LP model based on the data in this table and the requirements of the client. The objective is to maximize the total return of the portfolio.
 $$\begin{array} { c c c l } 
 \text { Investment } & \text { Return } & \text { Years to Maturity } & \text { Rating } \ 
\hline  \text { A } & 6.45 \% & 9 & \text { 1-Excellent } \
\text { B } & 7.10 \% & 8 & 2 \text { -Very Good } \
\text { C } & 8.20 \% & 5 & 4 \text {-Fair } \
\text { D } & 9.00 \% & 8 & 3 \text {-Good }
\end{array}$$ Let
$\begin{array} { l } 
X _ { 1 } = \text { Dollars invested in } A \
X _ { 2 } = \text { Dollars invested in } B \
X _ { 3 } = \text { Dollars invested in } C \
X _ { 4 } = \text { Dollars invested in } D
\end{array}$

MAX: $\quad .0645 \mathrm { X } _ { 1 } + .071 \mathrm { X } _ { 2 } + .082 \mathrm { X } _ { 3 } + .09 \mathrm { X } _ { 4 }$
Subject to:
$\begin{array} { l } 
\mathrm { X } _ { 1 } + \mathrm { X } _ { 2 } + \mathrm { X } _ { 3 } + \mathrm { X } _ { 4 } \leq 300000 \
\mathrm { X } _ { 1 } \leq 75000 \
\mathrm { X } _ { 2 } \leq 75000 \
\mathrm { X } _ { 3 } \leq 75000 \
\mathrm { X } _ { 4 } \leq 75000 \
\mathrm { X } _ { 1 } + \mathrm { X } _ { 2 } + \mathrm { X } _ { 4 } \geq 100000 \
\mathrm { X } _ { 3 } + \mathrm { X } _ { 4 } \leq 75000 \
\mathrm { X } _ { 1 } , \mathrm { X } _ { 2 } , \mathrm { X } _ { 3 } \mathrm { X } _ { 4 } \geq 0
\end{array}$  
-Refer to Exhibit 3.4. Which cells are changing cells in the accompanying Excel spreadsheet?

Accepted Answer

A)  B3:B6 
B)  B7:I7 
C)  C7 
D)  E7

Question 4

How many constraints are there in a transportation problem which has 5 supply points and 4 demand points? (ignore the non-negativity constraints)

Accepted Answer

A)  4 
B)  5 
C)  9 
D)  20

Question 5

You have been given the following linear programming model and Excel spreadsheet to solve this problem. What formulas should be entered into cells E5 and D8:D10 to implement this model?
 MIN: $\quad 8 X _ { 1 } + 3 X _ { 2 }$
Subject to: $\quad X _ { 2 } \geq 8$
$\begin{array} { l } 
8 \mathrm { X } _ { 1 } + 5 \mathrm { X } _ { 2 } \geq 80 \
3 \mathrm { X } _ { 1 } + 5 \mathrm { X } _ { 2 } \geq 60 \
\mathrm { X } _ { 1 } , \mathrm { X } _ { 2 } \geq 0
\end{array}$ $$\begin{array} { | c | c | c | c | c | c | } 
\hline & \text { A } & \text { B } & \text { C } & \text { D } & \text { E } \
\hline 1 & & & & & \
\hline 2 & & & & & \
\hline 3 & & \mathrm { X } _ { 1 } & \mathrm { X } _ { 2 } & & \
\hline 4 & \text { Number to make: } & & & & \text { OBJ. FN. VALUE } \
\hline 5 & \text { Unit profit: } & 8 & 3 & & \
\hline 6 & & & & & \
\hline 7 & \text { Constraints: } & & & \text { Used } & \text { Available } \
\hline 8 & 1 & & 1 & & 8 \
\hline 9 & 2 & 8 & 5 & & 80 \
\hline 10 & 3 & 3 & 5 & & 60 \
\hline
\end{array}$$

Accepted Answer

The answer of You have been given the following linear...

Question 6

What command is used to add the contents of cells A1, A2 and A3?

Accepted Answer

A)  =A1+A2+A3 
B)  =ADD(A1:A3) 
C)  =TOTAL(A1:A3) 
D)  =PRODUCT(A1:A3)

Question 7

Exhibit 3.1
The following questions are based on this problem and accompanying Excel windows.
Jones Furniture Company produces beds and desks for college students. The production process requires carpentry and varnishing. Each bed requires 6 hours of carpentry and 4 hour of varnishing. Each desk requires 4 hours of carpentry and 8 hours of varnishing. There are 36 hours of carpentry time and 40 hours of varnishing time available. Beds generate $30 of profit and desks generate $40 of profit. Demand for desks is limited, so at most 8 will be produced.
 Let
$$\begin{array} { l } 
\mathrm { X } _ { 1 } = \text { Number of Beds to produce } \
\mathrm { X } _ { 2 } = \text { Number of Desks to produce }
\end{array}$$ The LP model for the problem is
 $$\begin{array} { l l } 
\text { MAX: } & 30 X _ { 1 } + 40 X _ { 2 } \
\text { Subject to: } & 6 X _ { 1 } + 4 X _ { 2 } \leq 36 \text { (carpentry) } \
& 4 X _ { 1 } + 8 X _ { 2 } \leq 40 \text { (varnishing) } \
& X _ { 2 } \leq 8 \text { (demand for desks) } \
& X _ { 1 } , X _ { 2 } \geq 0
\end{array}$$ 
-Refer to Exhibit 3.1. Which cells should be changing cells in this problem?

Accepted Answer

A)  B4:C4 
B)  E5 
C)  D8:D10 
D)  E8:E10

Question 8

Exhibit 3.3
The following questions are based on this problem and accompanying Excel windows.
Jack's distillery blends scotches for local bars and saloons. One of his customers has requested a special blend of scotch targeted as a bar scotch. The customer wants the blend to involve two scotch products, call them A and B. Product A is a higher quality scotch while product B is a cheaper brand. The customer wants to make the claim the blend is closer to high quality than the alternative. The customer wants 50 1500 ml bottles of the blend. Each bottle must contain at least 48% of Product A and at least 500 ml of B. The customer also specified that the blend have an alcohol content of at least 85%. Product A contains 95% alcohol while product B contains 78%. The blend is sold for $12.50 per bottle. Product A costs $7 per liter and product B costs $3 per liter. The company wants to determine the blend that will meet the customer's requirements and maximize profit.
 Let $\quad X _ { 1 } =$ Number of liters of product $A _ { 1 }$ in total blend delivered
$\quad\quad\quad\mathrm { X } _ { 2 } =$ Number of liters of product $\mathrm { B }$ in total blend delivered

MIN: $\quad 7 \mathrm { X } _ { 1 } + 3 \mathrm { X } _ { 2 }$
Subject to: $\quad \mathrm { X } _ { 1 } + \mathrm { X } _ { 2 } = 1.5 * 50$ (Total liters of mix)
$\quad\quad\quad\quad\quad\mathrm { X } _ { 1 } \geq 0.48 * 1.5 * 50 \left( \mathrm { X } _ { 1 } \right.$ minimum)
$\quad\quad\quad\quad\quad\mathrm { X } _ { 2 } \geq 0.5 ^ { * } 50 \left( \mathrm { X } _ { 2 } \right.$ minimum)
$\quad\quad\quad\quad.0 .95 \mathrm { X } _ { 1 } + 0.78 \mathrm { X } _ { 2 } \geq 0.85 * 1.5 * 50$ (85\% alcohol minimum)
$\quad\quad\quad\quad\mathrm { X } _ { 1 } , \mathrm { X } _ { 2 } \geq 0$ 
-Refer to Exhibit 3.3. What formula should be entered in cell E5 in the accompanying Excel spreadsheet to compute total cost?

Accepted Answer

A)  =B4*C4+B5*C5 
B)  =SUMPRODUCT(B4:C4,B5:C5) 
C)  =SUM(B5:C5) 
D)  =SUM(E8:E10)

Exam 3: Modeling and Solving Lp Problems in a Spreadsheet

How many constraints are there in a transportation problem which has 5 supply points and 4 demand points? (ignore the non-negativity constraints)

What command is used to add the contents of cells A1, A2 and A3?