Reference no: EM13961456
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.
(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.
|
Compounding and a prevailing interest rate
: Suppose you know that the amount a wine-drinker is willing to pay for a bottle of wine t years from now is $P(1+20(sqr(t))). Assuming continuous compounding and a prevailing interest rate of 5% per year, when is the best time to sell your wine?
|
|
Compute the feasible space for each independent constraints
: Determine the feasible space for each of the following independent constraints, given that xl, x2 ≥ 0. Identify the direction of increase in z in each of the following cases:
|
|
Quality management plan-borrowing responsibly system
: Please come up with a quality management plan, quality baseline, and sponsor acceptance for a mobile application with an innovative approach to inform student’s how to borrow and manage student loans responsibly. The creation of a Borrowing Responsib..
|
|
Sure that you cover the point given below
: you have had a bad fall and have been advised to stay in bed.However,you do not wish to miss too much school work. Write a brief email to your best friend to ask help.Make sure that you cover the point given below. what happened to you Ask for new..
|
|
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.
|
|
Food would represent which component of expectancy theory
: Providing food fulfills which level of Alderfer's needs? If employees value free food as a reward for working hard, the food would represent which component of expectancy theory? In trying to help employees balance their personal lives with their wor..
|
|
Identify any ethical legal or diversity considerations
: Identify any ethical, legal, or diversity considerations that may be involved with the research study. Suggest guidelines for conducting ethical research involving people in work settings
|
|
At what point the force on a test charge of 1 c be zero
: Sketch the E-field and equipotentials associated with this charge system.
|
|
Thesis statement-organized structure-transitions
: Write 3 pages STORY that describes the scenario and include as much detail as you can recall. For example: What happened? Who was there? Where were you? When did the event occur? Why did such an event occur?
|