Lutz Brewery brews three brands of beers: Lutz Lager, Lutz Light, Lutz Ultralight. Lager sells for $12 per barrel, Light sells for $10 and Ultralight sells for $14 per barrel. Producing a barrel of Lager requires 8 pounds of corn and 4 pounds of hops. Producing a barrel of Light requires 2 pounds of corn, 6 pounds of rice, and 3 pounds of hops. Producing  a barrel of Ultralight requires 1 pounds of corn, 8 pounds of rice, and 2 pounds of hops. The brewery has 5000 pounds of corn, 3000 pounds of rice, and 4000 pounds of hops. To supply demand from established customers, Lutz brewery should produce at least 200 barrels of each product.

a) Assuming a linear relationship, use Excel Solver to determine the optimal mix of Lager, Light and Ultralight that maximizes Lutz Brewery”s revenue.

b) Conduct sensitivity analysis and interpret results.

Problems: Binary Integer Models

 

Production planning department of Graystar Prefabricated Homes Company has to decide which types of homes to produce for the next period. They have four prefabricated homes (A,B,C and D). Each home uses three different materials, lumber, particleboard and drywall. The table given below shows the expected profits for each house, the amount of materials used in house and the available quantities of these materials.

 

	A	B	C	D	Available
Profit	20,000	17,000	21,000	24,000
Lumber	10	7	11	10	25
Particle Board	5	4	7	6	20
Drywall	2	1.5	3	2.5	6

 

Formulate the problem, to determine which house(s) should be produced to maximize the profit.

 

SOLVER 4

Problems: Transportation Analysis

 

Sweetland Sugar  &Co. produces cane sugar in its four plants in Charlotte, Kansas City, Indianapolis and Flagstaff. These plants serve five major markets, New York, Chicago, St. Louis, Las Vegas and Seattle. The capacities of each plant, demands of each market and the transportation costs of sending a ton sugar from a plant to a market are given in the table below. The company seeks to develop a plan to minimize the transportation costs. Your task is to find the total transportation cost and the amounts of sugar sent from each plant to distribution centers and from each distribution center to markets using Excel Solver.

 

 

 

From Plants	To Markets					Supply (Tons)
	New York	Chicago	St. Louis	Las Vegas	Seattle
Charlotte	$16	$13	$12	$22	$25	16
Kansas City	$20	$11	$6	$15	$18	17
Indianapolis	$10	$8	$8	$17	$19	13
Flagstaff	$25	$18	$15	$8	$15	19
Demand (tons)	15,000	12,000	9,000	14,000	13,000

 

 

NOTE: The numbers shown here as SUPPLY and DEMAND are CORRECT ” Please solve with THESE inputs.

 

SOLVER 5

Problems: Binary Integer Models

 

Production planning department of Graystar Prefabricated Homes Company has to decide which types of homes to produce for the next period. They have four prefabricated homes (A,B,C and D). Each home uses three different materials, lumber, particleboard and drywall. The table given below shows the expected profits for each house, the amount of materials used in house and the available quantities of these materials.

 

	A	B	C	D	Available
Profit	20,000	17,000	21,000	24,000
Lumber	10	7	11	10	25
Particle Board	5	4	7	6	20
Drywall	2	1.5	3	2.5	6

 

Formulate the problem, to determine which house(s) should be produced to maximize the profit.

 

SOLVER 6

Problems: Network Models

100 Points

1) Formulate and solve a spreadsheet model for the maximum flow problem, shown below, where node A is the source, node F is the sink, and arc capacities are numbers in parentheses shown next to arcs.

2) Formulate and solve a spreadsheet model for the shortest path problem, shown below, starting from ORIGIN and ending in DESTINATION. Distances between points in miles in parentheses are shown next to arcs.

 

 

 

 

SOLVER 7

Problems: Nonlinear Problems

 

Frigid Air company produces three different types of industrial refrigerators; A, B and C. The production capacity of the plant is 1000 units per week. Sales department calculates the unit prices for these products as follows, A= $1000, B= $1200, C=$1500. The manufacturing costs for these products are calculated by the following formulas:

For A= 200A + A²

For B = 250B + B²

For C = 350C + C²

How should the production be allocated in order to maximize profit?

(Hint: Production numbers should be integers)

 

SOLVER 8

Problems: Queuing Problems

Bank of Ohio in Zanesville has a single drive-in teller window. Customers arrive at the window about every 10 minutes on average with an exponential distribution or the hourly arrival rate

is λ = 6. It take an average of five minutes (exponentially distributed) to complete each customer order

or the hourly service rate is μ = 12. You are asked following tasks:

Determine

• Expected number of customers in the system, including those being served.
• Expected number of customers in the queue, excluding those being served.
• Expected waiting time in the system, including service time, for an individual customer.
• Expected waiting time in the queue, excluding service time, for an individual customer.
• Utilization factor
• The probability that waiting time in the system exceeds 0.25 hours (15 minutes)
•  The probability that waiting time in the queue exceeds 0.25 hours (15 minutes)
• The probability that the system has 5 customers in the system

 

SOLVER 9

Problem: Computer Simulation

 

Jack Williams operates a small mechanics shop in his hometown, Lima, Ohio. He works six days a week, Monday thru Friday, 10:00 AM to 6:00 PM and Saturdays from 9:00 AM to 12:00 PM. On a regular weekday, customers” arrival time is exponentially distributed with a mean of 45 minutes and service time is also exponentially distributed with a mean of 35 minutes. Simulate Jack Williams” shop for 100 customer arrivals to estimate Average Time in Line and Average Time in System.