Reference no: EM131729607

COMPLTETE THE FOLLOWING:

- Write the linear program, in standard form, that is needed to solve the problem.
- Put the linear program from (a) into Excel to find the optimal solution and optimal value.

Note: It is OK for some variables to have a value of zero.

PROBLEMS:
#1) A toy store is opening a new location and needs to advertise. They want to reach as many customers as possible per month. The total budget is $20,000. Internet advertising is to be emphasized; at least 15 ads need to be online, but no more than $12,000 can be spent on internet advertising. (I = internet ad). The maximum number of times an ad can be run is in the table. How many of each ad should be run per month to maximize customers reached and meet all constraints?

Type of Ad	Price ($)	Maximum Available	Customers Reached
Newspaper	1000	7	4000
Radio	600	8	2000
TV	1400	10	3000
Google (I)	800	15	1500
Facebook (I)	200	10	1000
Youtube (I)	1800	6	6000

#2) A company makes four types of cat food. The cat food is made of chicken, fish, and grain. The contract minimum and demand is for number of bags per month. The contract minimum should always be met, and more can be made, but what is made should not exceed the demand. There is a limited supply of ingredients. How many bags of each should be made to maximize profit?

Ingredient	Cost per pound	Maximum pounds available
Chicken	$4	800
Fish	$5	600
Grain	$2	700

Product	Revenue / bag	Contract minimum	Demand	Mass of bag (pounds)	Ratio
All chicken	$18.00	110	190	4	100% Chicken
All fish	$24.00	160	220	2	100% Fish
Chicken/grain mix	$26.00	140	170	5	40% Chicken / 60% Grain
Fish/grain mix	$15.00	80	180	4	25% Fish/ 75% Grain

#3) A new car dealership has full time sales people and part time salespeople that work on Sundays. "Working" is considered when the salespeople are with customers. On Sundays, the full-time sales people work six hours and also have a one hour lunch break and attend a one hour meeting. Part time people work four consecutive hours with customers, with no lunch and no meetings to attend. Full time workers must work at least 60% of total hours demanded on Sunday. No part time workers can work the first hour.
Full time workers make $200 a day and part time workers make $64 dollars a day.

30% of full time employees go to lunch from 1:00 to 2:00 and 70% go from 2:00 to 3:00.
50% of full time employees go to the meeting from 10:00 to 11:00 and 50% go from 3:00 to 4:00.

Find the minimum cost of the car dealership to staff Sundays that meets or exceeds demand and satisfies all constraints.

Time                      Demand

10:00 to 11:00    4

11:00 to 12:00    8

12:00 to 1:00       13

1:00 to 2:00         15

2:00 to 3:00         10

3:00 to 4:00         9

4:00 to 5:00         14

5:00 to 6:00         10

#4) Don't use the QM program to solve this, please use regular Excel Solver, as show in the lesson.

A company ships cases of baseballs from its factories to its warehouses, then to its retail outlets. The cost for a case to be shipped on each route is given below. How many cases should be shipped on each route to minimize cost, exactly meet demand, and satisfy all constraints?

Cost to Ship ($ / case):

                                                                        Warehouses

Factories          Dallas 3           Kansas City 4 

Seattle              5                      3

Reno                2                      2

                                                Retail Outlets                           Retail Outlets

Warehouses                 Atlanta                         Tampa                         Cleveland        Pittsburgh

Dallas                          8                      5                      10                    9

Kanas City                   6                      7                      12                    14

Supply / Demand (cases)

Factories          Supply                                     Retail Outlets   Demand

Seattle              1600                                        Atlanta                         1500

Reno                2000                                        Tampa                         600

                                                                        Cleveland        700

                                                                        Pittsburgh         800     

#5) Don't use the QM program to solve this, please use regular Excel Solver, as show in the lesson.
Four programmers are hired to fix the bugs in four Microsoft products. Each programmer gives an estimate, in hours, of how long it will take to fix the bugs in each product. Assign one programmer to one product that will minimize the total time to fix all the programs.

Programmers:                   Products:

                                     Word     Excel      Outlook           Power Point

Donatello                          8              9              10              11

Michelangelo                     2              3              4                5

Leonardo                          6              7              8                9

Raphael                            11           12           13                 14

#6) Below is a network for a shortest-route problem with origin Node 1 and destination Node 9. The numbers with the arcs represents the cost to use the arcs. Find the route from Node 1 to Node 9 that minimizes cost. Remember that some routes go two ways.

#7) A manufacturing plant makes ceramics for dishes and cups. The production of each item requires a worker to labor on it and some automatic machines to create it. The table below gives the revenue of each item and the hours of labor and machine time needed to make the item. For a production week, there is a maximum of each resource available. All units that are produced will be sold. Find the number of each item to produce for the production week that maximizes revenue and meets all constraints.

Item	Revenue ($)	Labor	Bake Machine	Glaze Machine
Mug	5	5	6	2
Cup	3	1	4	2
Serving Plate	8	5	3	5
Dinner Plate	4	3	3	3
Saucer	2	2	2	1
Max Available	--	200	400	300

#8) A construction company needs to hire workers. The workers can either be in a union or not be in a union. A union worker earns $1000 a week and a non-union worker earns $800 a week (they are not paid by the hour). Union workers take more training and can do certain tasks faster, but non-union workers are sometimes more motived to work faster on other tasks. The number of tasks that each type of worker can complete per week is given, along with the minimum number of each task that needs to be completed per week. (It is OK to complete more than the minimum number of tasks.) Decide how many union workers and how many non-union workers should be hired to minimize salary costs but complete all tasks.

                                                            Completion rate (tasks per week)

Type of worker                                    Pavement         Structure          Drywall            Paint

Union                                      59                    19                    8                      15                               

Non-union                               11                    114                  19                    12

Minimum tasks needed                        140                  180                  40                    60

#9) A shirt company produces t-shirts with graphics of professional sports teams. A retail store wants to order exactly 100 shirts, with the following requests. The revenue, per shirt, for football is $20, baseball is $18, basketball is $17, and hockey is $12. How many of each shirt should be sold to the store to maximize revenue and meet the requests?

At most 20 should be football.

At most 25 should be baseball.

At most 50 should be basketball.

At most 30 should be hockey.

At least 10 should be football.

At least 20 should be baseball.

At least 15 should be basketball.

At least 25 should be hockey.

#10) A Hollywood action movie production is being planned. A study was conducted to analyze the appeal of movies, and a new unit was created, "Audience Satisfaction Point" (ASP). The studio wants to maximize total ASP for the movie by including certain amounts of the following factors while staying under (or at) budget: vehicles, star actors, and special effects. There needs to be a least 12 vehicles, 4-star actors, and 20 special effects. The total budget is $2,100,000. How many of each factor should be planned to maximize ASP and meet all constraints?

Factor:                         Cost ($)                        ASP     Budget ($)

Vehicles:                                                          600,000

Cars                             40,000             40

Motorcycles                 10,000             10

Helicopters                  60,000             100

Star Actors:                                      1,800,000

A-List Actor                 500,000           200

B-List Actor                 100,000           30

Special Effects:                                 70,000

Explosions                   4,000               42

Stunts                           2,000               20

Full Body Makeup       1,000               10