Excel Spreadsheets

1. Build a spreadsheet that presents the unit manufacturing costs and the production upper bound for product i (i = 1,, P).

2. Build a spreadsheet that presents the fixed set-up costs and production capacity for machine j (j = 1,,n).

3. Build a spreadsheet that presents the amount of raw material used to produce one unit of product i (i = 1,, P).

4. Build a spreadsheet that presents the total amount of processing time product i requires on machine j(i=1,..,P; j=1,,n).

User Interface

Build a welcome form.

Build a data entry form. The following suggestions help to design this form. Insert a frame titled Problem Data. The frame includes two option buttons. The user has the choice to select whether to read the data from a file or manually enter the data. Include a command button that, when clicked on, performs these actions:

If the user chose to read the data from a file, a text box should appear where the user types in the name of the file.

If the user chose to enter the data manually, two text boxes should appear where the user types in the total number of products and the total number of machines. After this information is typed in, four tables appear. In the first table (with dimensions 2 by P) the user types in the unit manufacturing cost and the upper bound of production quantity for each product; in the second table (2 by n) the user types in the fixed set-up costs and production capacity for each machine; in the third table (1 by P) the user types in the amount of raw material used to produce one unit of each product; in the fourth table (n by P) the user types in the total amount of processing time each product needs on each machine.

Insert a text box where the user can type in the total amount of raw material available. Insert a command button that, when clicked on, submits the data for this problem, solves the corresponding linear programming problem, and opens Form 3, described below.

Build a form titled Problem Analysis. Insert two frames in this form. The first frame, titled Reports, has a number of option buttons that enable the user to choose to open one of the summary reports described below. The second frame, titled Sensitivity Analysis, has four combo boxes, one for each of these parameters: unit manufacturing cost, machine set-up cost, machine processing time for each product, and machine capacity. These combo boxes enable the user to choose a particular parameter to perform a sensitivity analysis. For example, the user may want to know what would happen to the total production costs and production quantity if more machines were purchased. Include a command button that, when clicked on, performs the sensitivity analysis with respect to the selected parameter.

Design a logo for this project. Insert this logo in the forms created above. Pick a background color and a font color for the forms created. Include the following in the forms created: record navigation command buttons, record operations command buttons, and form operations command buttons as needed.

Reports

1. Report the optimal amount that should be produced for each product.

2. Report the total number of machine hours required to produce each product.

3. Report the total production costs.

4. Report the utilization of each machine.

5. Report the results from the simulation analysis.