# Optimization Problems

Untimed Optimization Assessment, Due 04/17/10 11PM (no extra time given by Blackboard)

You have to do two separate things to complete this assessment:

1) Enter answers to numbered questions using online Assessment form (untimed one).

2) Upload the spreadsheets you used in solving the questions on this assessment (no spreadsheet needed for questions 38-44) to the online Assignment form that is supposed to go with this. If you cannot upload the spreadsheets, email them to me. Much preferred for you to just upload them.

ABSOLUTELY NO DISCUSSION OF PROBLEMS WITH OTHERS AND NO SHARING WITH ANYONE OF INFORMATION RELATING TO THIS ASSESSMENT

CONTACT PROF IF YOU HAVE QUESTIONS

Given the following Solver output what range of values can the objective function coefficient for variable X1 assume without changing the optimal solution?

Changing Cells

Final Reduced Objective Allowable Allowable

Cell Name Value Cost Coefficient Increase Decrease

$B$4 Number to make: X1 9.49 0 5 1.54 1

$C$4 Number to make: X2 1.74 0 6 1.5 1.47

Constraints

Final Shadow Constraint Allowable Allowable

Cell Name Value Price R.H. Side Increase Decrease

$D$8 Used 42 0 48 1E+30 6

$D$9 Used 132 0.24 132 12 12

$D$10 Used 24 1.24 24 1.33 2

Give answers with two digits to the right of the decimal point, e.g. 1.00

Q1. Bottom of range

Q2. Top of range

Consider the following linear programming model and Solver output. What is the optimal objective function value if the RHS of the first constraint increases to 18? (Give answer with one digit to right of decimal point, e.g., 1.0)

MAX: 7 X1 + 4 X2

Subject to: 2 X1 + X2 16

X1 + X2 10

2 X1 + 5 X2 40

X1, X2 0

Changing Cells

Final Reduced Objective Allowable Allowable

Cell Name Value Cost Coefficient Increase Decrease

$B$4 Number to make: X1 6 0 7 1 3

$C$4 Number to make: X2 4 0 4 3 0.5

Constraints

Final Shadow Constraint Allowable Allowable

Cell Name Value Price R.H. Side Increase Decrease

$D$8 Used 16 3 16 4 2.67

$D$9 Used 10 1 10 1 2

$D$10 Used 32 0 40 1E+30 8

Q3. Optimal objective function value =

The Happy Pet pet food company produces dog and cat food. Each food is comprised of meat, soybeans and fillers. The company earns a profit on each product but there is a limited demand for them. The pounds of ingredients required and available, profits and demand are summarized in the following table. The company wants to plan their product mix, in terms of the number of bags produced, in order to maximize profit .

Product

Profit per Bag ($)

Demand for product

Pounds of Meat per bag Pounds of Soybeans per bag Pounds of Filler per bag

Dog food 4 40 4 6 4

Cat food 5 30 5 3 10

Material available (pounds) 100 120 160

Formulate the LP model for this problem, assuming decision variables are:

X1 = bags of Dog food to produce

X2 = bags of Cat food to produce

Give WHOLE NUMBERS for answers to the questions relating to this LP (e.g.2)

What values are in the constraint for meat (___x1 +___x2 <= ___)?

Q4. LHS coefficient for x1

Q5. LHS coefficient for x2

Q6 RHS value

What is the constraint for soybeans?

Q7. LHS coefficient for x1

Q8. LHS coefficient for x2

Q9 RHS value

What is the constraint for Filler?

Q10. LHS coefficient for x1

Q11. LHS coefficient for x2

Q12 RHS value

What is the constraint for Dog food demand?

Q13. LHS coefficient for x1

Q14 LHS coefficient for x2

Q15 RHS value

What is the constraint for Cat food demand?

Q16. LHS coefficient for x1

Q17. LHS coefficient for x2

Q18RHS value

What is are the objective function coefficients

Q19 For x1

Q20 For x2

Q21 What is the optimal objective function value

https://brainmass.com/math/optimization/un-timed-optimization-assessment-311052

#### Solution Preview

Untimed Optimization Assessment, Due 04/17/10 11PM (no extra time given by Blackboard)

You have to do two separate things to complete this assessment:

1) Enter answers to numbered questions using online Assessment form (untimed one).

2) Upload the spreadsheets you used in solving the questions on this assessment (no spreadsheet needed for questions 38-44) to the online Assignment form that is supposed to go with this. If you cannot upload the spreadsheets, email them to me. Much preferred for you to just upload them.

ABSOLUTELY NO DISCUSSION OF PROBLEMS WITH OTHERS AND NO SHARING WITH ANYONE OF INFORMATION RELATING TO THIS ASSESSMENT

CONTACT PROF IF YOU HAVE QUESTIONS

Given the following Solver output what range of values can the objective function coefficient for variable X1 assume without changing the optimal solution?

Changing ...

#### Solution Summary

The expert examines un-timed optimization assessment for two separate things. A complete, neat and step-by-step solution is provided in the attached file.

Portfolio Optimization Problem

Create an Excel optimization file with good modeling techniques. Using Solver and Solver Table.

Not sure how to get started on this problem any help would be appreciated.

Thank you.

Here is the problemt:

Portfolio Optimization Problem

The annual returns for three companies over the last 12 years are given below, where the return for year n is defined as:

(closing price,n) - (closing price,n-1) + (dividends,n) / (closing price,n-1)

Year IBC NMC NBS

1 11.2% 8.0% 10.9%

2 10.8% 9.2% 22.0%

3 11.6% 6.6% 37.9%

4 -1.6% 18.5% -11.8%

5 -4.1% 7.4% 12.9%

6 8.6% 13.0% -7.5%

7 6.8% 22.0% 9.3%

8 11.9% 14.0% 48.7%

9 12.0% 20.5% -1.9%

10 8.3% 14.0% 19.1%

11 6.0% 19.0% -3.4%

12 10.2% 9.0% 43.0%

Part a: Using Excel, determine for each company the estimated mean return and standard deviation. Also, calculate the estimated correlations and covariances between the companies' returns.

Hint: Use the Excel functions AVERAGE, STDEV, and CORREL to determine the means, standard deviations and correlations. The covariances can then be calculated from the correlations (as in the class example). Click on fx in the Formula Bar for help with using these Excel functions.

Part b: Using the three stocks, a financial planner would like to create the least risky portfolio with minimum of a 12% expected return. Implement a spreadsheet model to determine the desired portfolio. Discuss the solution and highlight exactly how risk is minimized in the optimal portfolio, while achieving the desired return.

Part c: Either by repeatedly re-running the model, or by using SolverTable, create the chart for the efficient frontier for this portfolio optimization problem. Explain the usefulness of such a chart.