Linear Programming_Solver_Statistics

Individual Assignment 3

1) Virginia Electronics Company (VEC), is contemplating a research and development program encompassing eight major projects. The company is constrained from embarking on all of the projects by the number of available scientists (40) and the budget available for the projects ($300,000). In the following are the resource requirements and the estimated profit for each project.

Project	Expense ($000)	Scientists Required	Profit ($000)
1	60	7	36
2	110	9	82
3	53	8	29
4	47	4	16
5	92	7	56
6	85	6	61
7	73	8	48
8	65	5	41

a. Using binary variables write down an algebraic formulation of an integer (linear) programming formulation for this problem. Document your algebraic model so that it is clear what the variables, constraints, and objective function mean.

b. Implement your model in Solver. Document your model appropriately so it is possible to identify the decision variables, objective function, and constraints without going into Solver. Make sure to paste the Solver dialog box into your spreadsheet.

c. Based on the solution to your model, what is the maximum profit? Which projects should be selected to achieve this profit?

d. Suppose projects 2 and 5 are mutually exclusive. That is, VEC should not undertake both. Write down a linear constraint that implements this condition.

e. Add the constraint from part d into your model and reoptimize your model. Do this in a separate worksheet. (You can right click the name of the original sheet of your model and select move or copy. Then make a copy of your original sheet, rename it and then make changes to the solver model therein).

f. What is the revised project portfolio and the revised maximum profit?

g. In addition to the previous restrictions, suppose that projects 5-8 involve consumer products and that management decides to undertake at least two of those. Write down a single linear constraint that implements this restriction.

h. Add the constraint from part g into your model from part e and reoptimize your model. Do this in a separate worksheet.

i. What is the revised project portfolio and revised profit?

Your submission to this question should be a word or pdf file (or a text box within your excel submission)

with all of the answers for the parts a through I (except b, e, and h). Please also submit the spreadsheet containing the solver model for parts b, e, and h.

2) College Park Motors is a car dealership that specializes in the sales of sport utility vehicles and station wagons. Due to its reputation for quality and service, College Park Motors has a strong position in the regional market but demand is somewhat sensitive to price. After examining the new models Covington’s marketing consultant has come up with the following demand curves:

SUV demand = 400 – 0.014(SUV price)

Wagon demand = 425 – 0.018(wagon price)

The dealership’s unit costs are $17,000 for SUVs and $14,000 for wagons. Each SUV requires 2 hours of prep labor, and each wagon requires 3 hours of prep labor. The current staff can supply 320 hours of labor.

College Park Motors would like to determine profit maximizing prices for SUVs and wagons. (It is okay for prices and/or demand to be fractional in this problem. Do not add integer restrictions.)

a. Write down an algebraic formulation of a nonlinear optimization model that maximizes College Park Motors profit. Document your algebraic model so that it is clear what the variables, constraints, and objective function mean. In the event you prefer to work in excel directly to build a Solver Model, that is fine. After you build your Solver model make sure to write out the algebraic equivalent of your model.

b. Implement your model from part a in Solver. Document your model appropriately so it is possible to identify the decision variables, objective function, and constraints without going into Solver. Make sure to paste the Solver dialog box into your spreadsheet.

c. What is the maximum profit? What should the SUV price and Wagon price be set at? And what is the resulting demand?

d. Using the sensitivity report obtained from solving your model, determine the marginal value of dealer prep labor.

Your submission to this question should be a word or pdf file (or a text box within your excel submission) with all of the answers for the parts a through d (except b). Please also submit the spreadsheet containing the solver model for parts b and the sensitivity report used for part d.