one

Required Course Textbook(s)

· Management Science, 14th Edition
By: Anderson/Cochran/Fry/Ohlmann
ISBN: 978-1-337-03485-2

Chapter 1, 2 and 4
Format: Answer these questions using an Excel workbook and include a problem per sheet. Name the sheets with the corresponding problem number in the textbook, for instance 1-14 or 4-19. Many problems may include a solver solution. Follow the format in the excel document in Course Resources and used in class. Some problems in the textbook have additional questions that require a change some conditions in your solver set up, just copy the original problem to a new worksheet and solve the new conditions. Do not change the conditions in the initial solver set up, so the instructor can see all your work.

· Week 1
· Assignments:
· Introduction to Management Science (Ch 1) -Problems #13, #15
· Introduction to Linear Programming (Ch 2) – Problems #15, #23
· L-P Applications (Ch 4) – Problems #5, #10

PLEASE USE THESE TEMPLATES BELOW

Chapter one; problem 13 – Macromedia offers computer training seminars on a variety of topics. In the seminars each student works at a personal computer, practicing the particular activity that the instructor is presenting. Macromedia is currently planning a two-day seminar on the use of Microsoft Excel in statistical analysis. The projected fee for the seminar is $600 per student. The cost for the conference room, instructor compensation, lab assistants, and promotion is $9600. Macromedia rents computers for its seminars at a cost of $120 per computer per day.
a. Develop a model for the total cost to put on the seminar. Let x represent the number of students who enroll in the seminar
b. Develop a model for the total profit if x students enroll in the seminar.
c. Micromedia has forecasted an enrollment of 30 students for the seminar. How much profit will be earned if their forecast is accurate?
d. Compute the breakeven point.
Chapter one; problem 15 – Preliminary plans are under way for the construction of a new stadium for a major league baseball team. City officials have questioned the number and profitability of the luxury corporate boxes planned for the upper deck of the stadium. Corporations and selected individuals may the boxes for $300,000 each. The fixed construction cost for the upper-deck area is estimated to be $4,500,000, with a variable cost of $150,000 for each box constructed.
a. What is the breakeven point for the number of luxury boxes in the new stadium?
b. Preliminary drawings for the stadium show that space is available for the construction of up to 50 luxury boxes. Promoters indicate that ers are available and that all 50 could be sold if constructed. What is your recommendation concerning the construction of luxury boxes? What profit is anticipated?
Chapter Two; problem 15 – Refer to the Par, Inc., problem described in Section 2.1. Suppose that Par, Inc., management encounters the following situations:
a. The accounting department revises its estimate of the profit contribution for the deluxe bag to $18 per bag.
b. A new low-cost material is available for the standard bag, and the profit contribution per standard bag can be increased to $20 per bag. (Assume that the profit contribution of the deluxe bag is the original $9 value.)
c. New sewing equipment is available that would increase the sewing operation capacity to 750 hours. (Assume that 10A 1 9B is the appropriate objective function.) If each of these situations is encountered separately, what is the optimal solution and the total profit contribution?
Chapter Two; problem 23 – Embassy Motorcycles (EM) manufactures two lightweight motorcycles designed for easy handling and safety. The EZ-Rider model has a new engine and a low profile that make it easy to balance. The Lady-Sport model is slightly larger, uses a more traditional engine, and is specifically designed to appeal to women riders. Embassy produces the engines for both models at its Des Moines, Iowa, plant. Each EZ-Rider engine requires 6 hours of manufacturing time, and each Lady-Sport engine requires 3 hours of manufacturing time. The Des Moines plant has 2100 hours of engine manufacturing time available for the next production period. Embassy’s motorcycle frame supplier can supply as many EZ-Rider frames as needed. However, the Lady-Sport frame is more complex, and the supplier can only provide up to 280 Lady-Sport frames for the next production period. Final assembly and testing require 2 hours for each EZ-Rider model and 2.5 hours for each Lady-Sport model. A maximum of 1000 hours of assembly and testing time are available for the next production period. The company’s accounting department projects a profit contribution of $2400 for each EZ-Rider produced and $1800 for each Lady-Sport produced.
a. Formulate a linear programming model that can be used to determine the number of units of each model that should be produced in to maximize the total contribution to profit.
b. Solve the problem graphically. What is the optimal solution?
c. Which constraints are binding?
Chapter Four; problem 5 – Kilgore’s Deli is a small delicatessen located near a major university. Kilgore’s does a large walk-in carry-out lunch business. The deli offers two luncheon chili specials, Wimpy and Dial 911. At the beginning of the day, Kilgore needs to decide how much of each special to make (he always sells out of whatever he makes). The profit on one serving of Wimpy is $.45, on one serving of Dial 911, $.58. Each serving of Wimpy requires .25 pound of beef, .25 cup of onions, and 5 ounces of Kilgore’s special sauce. Each serving of Dial 911 requires .25 pound of beef, .4 cup of onions, 2 ounces of Kilgore’s special sauce, and 5 ounces of hot sauce. Today, Kilgore has 20 pounds of beef, 15 cups of onions, 88 ounces of Kilgore’s special sauce, and 60 ounces of hot sauce on hand.
a. Develop an LP model that will tell Kilgore how many servings of Wimpy and Dial 911 to make in to maximize his profit today.
b. Find an optimal solution.
c. What is the dual value for special sauce? Interpret the dual value.
d. Increase the amount of special sauce available by 1 ounce and re-solve. Does the solution confirm the answer to part (c)? Give the new solution.
Chapter Four; problem 10 – An investment advisor at Shore Financial Services wants to develop a model that can be used to allocate investment funds among four alternatives: stocks, bonds, mutual funds, and cash. For the coming investment period, the company developed estimates of the annual rate of return and the associated risk for each alternative. Risk is measured using an index between 0 and 1, with higher risk values denoting more volatility and thus more uncertainty.

Because cash is held in a money market fund, the annual return is lower, but it carries essentially no risk. The objective is to determine the portion of funds allocated to each investment alternative in to maximize the total annual return for the portfolio subject to the risk level the client is willing to tolerate. Total risk is the sum of the risk for all investment alternatives. For instance, if 40% of a client’s funds are invested in stocks, 30% in bonds, 20% in mutual funds, and 10% in cash, the total risk for the portfolio would be 0.40(0.8) 1 0.30(0.2) 1 0.20(0.3) 1 0.10(0.0) 50.44. An investment advisor will meet with each client to discuss the client’s investment objectives and to determine a maximum total risk value for the client. A maximum total risk value of less than 0.3 would be assigned to a conservative investor; a maximum total risk value of between 0.3 and 0.5 would be assigned to a moderate tolerance to risk; and a maximum total risk value greater than 0.5 would be assigned to a more aggressive investor. Shore Financial Services specified additional guidelines that must be applied to all clients. The guidelines are as follows:
· No more than 75% of the total investment may be in stocks
· The amount invested in mutual funds must be at least as much as invested in bonds
· The amount of cash must be at least 10%, but no more than 30% of the total investment funds.
a. Suppose the maximum risk value for a particular client is 0.4. What is the optimal allocation of investment funds among stocks, bonds, mutual funds, and cash? What is the annual rate of return and the total risk for the optimal portfolio?
b. Suppose the maximum risk value for a more conservative client is 0.18. What is the optimal allocation of investment funds for this client? What is the annual rate of return and the total risk for the optimal portfolio?
c. Another more aggressive client has a maximum risk value of 0.7. What is the optimal allocation of investment funds for this client? What is the annual rate of return and the total risk for the optimal portfolio?
d. Refer to the solution for the more aggressive client in part (c). Would this client be interested in having the investment advisor increase the maximum percentage allowed in stocks or decrease the requirement that the amount of cash must be at least 10% of the funds invested? Explain.
e. What is the advantage of defining the decision variables as is done in this model rather than stating the amount to be invested and expressing the decision variables directly in dollar amounts?

Week 1 – Ch01 –
Breakeven TEMPLATE.xlsx

Linear Break Even Analysis

Fixed Costs: $2,000

Unit Variable Cost: $60.00

Unit Price: $80.00

Break Even Units: 100 $8,000.00

Plot Points

$2,000 0

$14,000 200

$0 0

$16,000 200

Breakeven Chart
Cost 0 200 2000 14000 Revenue 0 200 0 16000 Units

Week 1 – Ch2&4 –
LP TEMPLATES.xlsx

2 by 2 Template

LP Template for 2 by 2 Problem

DV 1

Bud: Step 2: List the decision variables.
DV 2 type RHS

Min/Max

Bud: Step 1: State the objrctive.

Bud: Step 2: List the decision variables.
Objective

Bud: Step 3: Quantify the relationship between the objective and the decision variavbles. Objective 0

Bud: Optimum objective value

Constraint 1

Bud: Step 4: List the names of the constraints.

Bud: Step 7: Quantify the relationship between each decision variable and each constraint.

Bud: Step 5: Define the type of each constraint. <=, =, >=

Bud: Step 6: Quantify the limiting vakue of each constraint.

Bud: Optimum objective value Constraint 1 0

Constraint 2 Constraint 2 0

Allocations

PROCESS >>> USE SOLVER

3 x 2 Template

LP Template for 3 by 2 Problem

DV 1

Bud: Step 2: List the decision variables.
DV 2 type RHS

Min/Max

Bud: Step 1: State the objrctive.

Bud: Step 2: List the decision variables.
Objective

Bud: Step 3: Quantify the relationship between the objective and the decision variavbles. Objective 0

Bud: Enter the SUMPRODUCT formulas.

Constraint 1

Bud: Step 4: List the names of the constraints.

Bud: Step 7: Quantify the relationship between each decision variable and each constraint.

Bud: Step 5: Define the type of each constraint. <=, =, >=

Bud: Step 6: Quantify the limiting vakue of each constraint.

Bud: Enter the SUMPRODUCT formulas. Constraint 1 0

Constraint 2 Constraint 2 0

Constraint 3 Constraint 3 0

Allocations

PROCESS >>> USE SOLVER

3 by 3 Template

LP Template for 3 by 3 Problem

DV 1

Bud: Step 2: List the decision variables.
DV 2 DV 3 type RHS

Min/Max

Bud: Step 1: State the objrctive.

Bud: Step 2: List the decision variables.
Objective

Bud: Step 3: Quantify the relationship between the objective and the decision variavbles. Objective 0

Bud: Optimum objective value

Constraint 1

Bud: Step 4: List the names of the constraints.

Bud: Step 7: Quantify the relationship between each decision variable and each constraint.

Bud: Step 5: Define the type of each constraint. <=, =, >=

Bud: Step 6: Quantify the limiting vakue of each constraint.

Bud: Optimum objective value Constraint 1 0

Constraint 2 Constraint 2 0

Constraint 3 Constraint 3 0

Allocations

PROCESS >>> USE SOLVER

4 x 2 Template

LP Template for 4 by2 Problem

DV 1

Bud: Step 2: List the decision variables.
DV 2 type RHS

Min/Max

Bud: Step 1: State the objrctive.

Bud: Step 2: List the decision variables.
Objective

Bud: Step 3: Quantify the relationship between the objective and the decision variavbles. Objective 0

Bud: Optimum objective value

Constraint 1

Bud: Step 4: List the names of the constraints.

Bud: Step 7: Quantify the relationship between each decision variable and each constraint.

Bud: Step 5: Define the type of each constraint. <=, =, >=

Bud: Step 6: Quantify the limiting vakue of each constraint.

Bud: Optimum objective value Constraint 1 0

Constraint 2 Constraint 2 0

Constraint 3 Constraint 3 0

Constraint 4 Constraint 4 0

Allocations

PROCESS >>> USE SOLVER

4 by 4 Template

LP Template for 4 by 4 Problem

DV 1

Bud: Step 2: List the decision variables.
DV 2 DV 3 DV 4 type RHS

Min/Max

Bud: Step 1: State the objrctive.

Bud: Step 2: List the decision variables.
Objective

Bud: Step 3: Quantify the relationship between the objective and the decision variavbles. Objective 0

Bud: Optimum objective value

Constraint 1

Bud: Step 4: List the names of the constraints.

Bud: Step 7: Quantify the relationship between each decision variable and each constraint.

Bud: Step 5: Define the type of each constraint. <=, =, >=

Bud: Step 6: Quantify the limiting vakue of each constraint.

Bud: Optimum objective value Constraint 1 0

Constraint 2 Constraint 2 0

Constraint 3 Constraint 3 0

Constraint 4 Constraint 4 0

Allocations

PROCESS >>> USE SOLVER

6 x 4 Template

LP Template for 6 by 4 Problem

DV 1

Bud: Step 2: List the decision variables.
DV 2 DV 3 DV 4 type RHS

Min/Max

Bud: Step 1: State the objrctive.

Bud: Step 2: List the decision variables.
Objective

Bud: Step 3: Quantify the relationship between the objective and the decision variavbles. Objective 0.00

Bud: Optimum objective value

Constraint 1

Bud: Step 4: List the names of the constraints.

Bud: Step 7: Quantify the relationship between each decision variable and each constraint.

Bud: Step 5: Define the type of each constraint. <=, =, >=

Bud: Step 6: Quantify the limiting vakue of each constraint.

Bud: Optimum objective value Constraint 1 0

Constraint 2 Constraint 2 0

Constraint 3 Constraint 3 0

Constraint 4 Constraint 4 0

Constraint 5 Constraint 5 0

Constraint 6 Constraint 6 0

Allocations

PROCESS >>> USE SOLVER

20 x 20 Template

LP Template for 20 by 20 Problem

DV 1

Bud: Step 2: List the decision variables.
DV 2 DV 3 DV 4 DV 5 DV 6 DV 7 DV 8 DV 9 DV 10 DV 11 DV 12 DV 13 DV 14 DV 15 DV 16 DV 17 DV 18 DV 19 DV 20 type RHS

Maximize

Bud: Step 1: State the objective.

Bud: Step 2: List the decision variables.
Objective

Bud: Step 3: Quantify the relationship between the objective and the decision variavbles. Objective 0

Bud: Optimum objective value

Constraint 1

Bud: Step 2: List the decision variables.

Bud: Step 7: Quantify the relationship between each decision variable and each constraint. ≤

Bud: Step 5: Define the type of each constraint. <=, =, >= 0

Bud: Step 6: Quantify the limiting vakue of each constraint. Constraint 1 0.0

Constraint 2

Bud: Step 2: List the decision variables.
≤ 0 Constraint 2 0.0

Constraint 3

Bud: Step 2: List the decision variables.
≤ 0 Constraint 3 0.0

Constraint 4

Bud: Step 2: List the decision variables.
≤ 0 Constraint 4 0.0

Constraint 5

Bud: Step 2: List the decision variables.
≤ 0 Constraint 5 0.0

Constraint 6

Bud: Step 2: List the decision variables.

Bud: Step 5: Define the type of each constraint. <=, =, >=

Bud: Step 6: Quantify the limiting vakue of each constraint.

Bud: Optimum objective value ≤ 0 Constraint 6 0.0

Constraint 7 ≤ 0 Constraint 7 0.0

Constraint 8 ≤ 0 Constraint 8 0.0

Constraint 9 ≤ 0 Constraint 9 0.0

Constraint 10 ≤ 0 Constraint 10 0.0

Constraint 11 ≤ 0 Constraint 11 0.0

Constraint 12 ≤ 0 Constraint 12 0.0

Constraint 13 ≤ 0 Constraint 13 0.0

Constraint 14 ≤ 0 Constraint 14 0.0

Constraint 15 ≤ 0 Constraint 15 0.0

Constraint 16 ≤ 0 Constraint 16 0.0

Constraint 17 ≤ 0 Constraint 17 0.0

Constraint 18 ≤ 0 Constraint 18 0.0

Constraint 19 ≤ 0 Constraint 19 0.0

Constraint 20 ≤ 0 Constraint 20 0.0

Allocations