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.