A poet has inherited 90 ha (or acres) of woods from her late grandmother. The poet finds that talks in the woods makes her happy, although she has to rely on income from the woods to support her during times of writers' block. In all she has 90 ha, 40 ha of which are covered in red pine forest, the other 50 ha are covered in mixed hardwood forest. The poet wants to figure out how much land to manage under each forest type in order to maximize her revenue. She has calculated that she needs to spend 2 days/ha/year to manage the red pine and 3d/ha/year to manage the mixed hardwood. She makes $90/ha/year on the red pine and $120/ha/year on the hardwoods. In order to have enough time for her writing she wants to spend no more than 180/year working on the land.
A. Formulate this problem using algebraic notation
B. Graph the feasible region and find the optimal solution
C. User Solver in Excel to solve the problem and provide a printout of your spreadsheet. Does the optimal solution match your graphical solution?
D. Write out the dual to this problem, solve it in Excel and provide your spreadsheet.
E. What are the shadow prices on the constraints (of the primal)?
F. Say there is some uncertainty with respect to returns on hardwood management and instead of expecting $120/ha/year, the poet could expect up to $150/ha/year. How does this change the objective function value and optimal solution? Is this what you would expect? Why?
G. Assume instead that the poet is considering working 182 days in the woods instead of 180. How does this change the objective function? Explain this change in relation the shadow prices.© BrainMass Inc. brainmass.com October 25, 2018, 3:55 am ad1c9bdddf
Please find attached word document and excel file for detailed ...
The spreadsheet contains primal problem formulation and solution, Dual problem formulation and solution and senstivitiy analysis report.
Linear Programming : Algabraic Formulation and Spreadsheet Modeling using Excel and Solver
Optimization Problem using Excel add-in Solver
This problem requires the use of Excel and the add-in, called Solver. The course is Excel-based and Solver is the optimization application used for all problems.
The problem appears in this text box and is also attached as a MS Word file, so it is sure to transmit legibly. The MS Word version will likely need to be the document from which to work, as the text box does not allow information to be transmitted well (see attached MS Word doc below).
Problem: 1a, 1b, and 1c (LP Algebraic and Spreadsheet Modeling)
During the next four quarters, Dorian Auto must meet (on time) the following demands for cars:
4000 in quarter 1;
2000 in quarter 2;
5000 in quarter 3;
1000 in quarter 4.
At the beginning of quarter 1, there are 300 cars in stock. The company currently has the capacity to produce 3000 cars per quarter.
At the beginning of each quarter (including the 1st quarter), the company can change production capacity. It costs $100 to increase quarterly production capacity by one car. For example, it would cost $10,000 to increase capacity from 3000 to 3100. It also costs $200 per quarter to maintain each unit of production capacity (even if it is unused during the current quarter). For example, the cost to maintain a 3000-car production capacity is 3000*200 or $600,000 per quarter. The production capacity can be decreased at the beginning of each quarter at a cost of $100 per unit as well.
The variable cost of producing a car is $2000. A holding cost of $150 per car is assessed against each quarter's ending inventory. It is required, that at the end of quarter 4, plant capacity must be at least 3000 cars.
(1a) Develop the algebraic formulation for the linear program for the problem described. Use the variable Ci to denote the capacity for quarter i, the variable Pi to denote the production level in quarter i and the variable Ii to denote the ending inventory at the end of quarter i. Define the other variables as needed along with the constraints and the objective function.
(1b) Develop an Excel spreadsheet for the problem and solve it. Try to follow good practices in implementing the spreadsheet (separation of data from formulae, clear headings, and so on). Describe the optimal solution. THE OBJECTIVE IS TO MINIMIZE COST.
(1c) Rather than following the policy of having a minimum production capacity of 3000 cars at the end of the planning horizon, the company is contemplating reducing the requirement to 2000 cars in production capacity and 1000 cars in inventory. Would you recommend such a change in policy? Please give a brief written explanation to support how you arrived at your conclusion.View Full Posting Details