Question

315: Homework 4

1. A logistics company must distribute cases of parts from 3 factories to 3 assembly plants. The monthly supplies and demands, along with the per-case transportation costs are:

                                                               Assembly Plant

                                                1                      2                      3                      Supply

             __________________________________________________________________

                                    A         10                    14                    18                    300

            Factory            B          6                      6                      10                    500

                                    C          6                      12                    11                    300

             __________________________________________________________________

            Demand                       320                  420                  300     

(a) Formulate a linear programming problem to minimize total cost for this transportation problem by defining the decision variables, objective function, and all the constraints.

(b) Solve the linear programming formulation from part (a) by using either Excel or QM for Windows. Find and interpret the optimal solution and optimal value. Please also include the computer output with your submission.

The following questions are mathematical modeling questions. Please answer by defining the decision variables, objective function, and all the constraints. Write all details of the formulation. Please do NOT solve the problems after formulating.

2. The Charming City Vacuum Company wants to assign three salespersons to three sales regions. Given their experiences, the salespersons are able to cover the regions in different amounts of time. The amount of time (days) required by each salesperson to cover each region is shown in the following table:

                                           Region (days)

Salesperson                  I            II             III               

________________________________________

     A                             9           17           11                                     

      B                             9           14           13                    

      C                           10           13           15        

However, because of his health reason, salesperson B does not want to be assigned to region III.

The Company wants to assign either salesperson B or salesperson C to region II. The objective is to minimize total time of covering the three sales regions.

Formulate a linear programming model for the above situation by determining

(a) The decision variables

(b) Determine the objective function. What does it represent?

(c) Determine all the constraints. Briefly describe what each constraint represents.

Note: Do NOT solve the problem after formulating.

3. A congressman’s district has recently been allocated $46 million for projects. The congressman has decided to allocate the money to five ongoing projects. However, the congressman wants to allocate the money in a way that will gain him the most votes in the upcoming election. The details of the five projects and votes per dollar for each project are given below.

Project              Votes/dollar

________________________

Parks                      0.09

Education               0.07

Roads                     0.10

Health Care            0.11

Family Welfare      0.08

In order to also satisfy some local influential citizens, he must meet the following guidelines.

- None of the projects can receive more than 25% of the total allocation.

- The amount allocated to education cannot exceed the amount allocated to health care.

- The amount allocated to family welfare cannot be more than the amount spent on roads.

- All of the money must be allocated.

Formulate a linear programming model for the above situation by determining

(a) The decision variables       

(b) Determine the objective function. What does it represent?

(c) Determine all the constraints. Briefly describe what each constraint represents.

Note: Do NOT solve the problem after formulating.

4. A builder is developing a lakeside community. After considering possible advertising media and the market to be covered, the builder has decided to advertise in four media. He collected data on the number of potential customers reached, the cost per advertisement, the maximum number of advertisements available, and the exposure quality rating for each of the four media. These data are given in the following table.

Number of                                Maximum

Potential                                   Number of        Exposure

Customers        Cost ($) per      Advertisements Quality

Advertising Media                    Reached           Advertisement Available         Units

____________________________________________________________________________

Daytime TV (1 min ad)             3500                4000                8                     80

Evening TV (30 sec ad)             5000                6000                6                     85

Daily newspaper (full page ad) 2000                1300                10                    55

Sunday newspaper magazine     3000                1900                10                    66

(1/2 page color ad)

The builder has an advertising budget of $100,000 for the campaign. In addition, he wants the following restrictions: At least 10 television commercials must be used, at least 50,000 potential customers must be reached, and no more than $15,000 may be spent on Sunday newspaper magazine advertisements. What advertising media selection plan should be recommended to maximize the total exposure quality units?

Formulate a linear programming model for the above situation by determining

(a) The decision variables       

(b) Determine the objective function. What does it represent?

(c) Determine all the constraints. Briefly describe what each constraint represents.

Note: Do NOT solve the problem after formulating.

Answer 1

1)

Now the LP is solved using the excel solver by following these steps,

Step 1: Write the decision variable with value zero. The screenshot is shown below,

Step 2: Write the objective function equation while taking the decision variable value. The screenshot is shown below,

Step 3: Write the constraints equation while taking the decision variable value and write the right side value of the constraint. The screenshot for constraint 1 is shown below,

Similarly, write down the equation for all the constrints as shown above.

Step 4: (If you have not install the solver excel follow, FILE > Options > Add-ins > Manage: select Excel Add-ins > Go then tick Solver Add-in > OK)

DATA > Solver > OK. The screenshot is shown below,

Step 5:

Set Objective: Select objective value,

To: select Min

Subject to the Constraints > Add > in Cell Reference select constraint value and in Constraint: select right hand side value of constraint and select the >= inequality.

Tick Make Unconstraint Variables Non-Negative

Select a Solving Method: Simplex LP

then click Solve. The screenshot is shown below,

Step 6: Select Reports > Ansawer . then Ok

The result is obtained. The screenshots are shown below,

The Answer Report