Step 1: Creation of Formulas and Constraints
In this step, you will set up the optimization problem worksheet for determining the best production mix for the Touring Bike models.
Launch Excel and create a new blank workbook. For this lab, you may use your own copy of Microsoft Excel, or you may use Excel in the Azure virtual lab environment. You are recommended to use the virtual lab to become familiar with it, as it will be required for some future labs. You should definitely use the virtual lab if your own copy of Excel is not the 2016 version, or if you are not using the Microsoft Windows operating system (the version of Excel for the Mac OS is significantly different from the version for Windows).
Rename the first worksheet to Touring Bike Model Mix.
Enter the title Touring Bike Production Mix in the top left cell.
In the next row, enter the row label Model in column A; the model names Touring-1000, Touring-2000, and Touring-3000 in columns B, C, and D respectively; and the column heading Total in column E.
In the next row, enter the row label Quantity to Produce in column A. As placeholders, enter last year’s production numbers under each model: 79 for the Touring-1000, 24 for the Touring-2000, and 27 for the Touring-3000. Enter a formula to calculate the total number of units produced for all three models in column E.
In the next rows, enter the parameters for gross profit per unit, labor hours per unit, and material cost per unit as provided by the cost accounting department and given in the scenario/summary section above.
In the next row, enter the label Minimum Production in column A, and the minimum production quantities for each model in columns B, C, and D respectively, as given in the scenario/summary section above.
In the next three rows, enter the labels Total Gross Profit, Total Labor Hours, and Total Material Cost in column A. In the column under each model, enter formulas to calculate these values using the corresponding production quantities and parameter values entered earlier. In column E, enter formulas to calculate the totals for all three models in each row.
In column F or the total gross profit row, enter Maximize to indicate the goal is to maximize the company’s total gross profit.
In the total labor hours row, enter “<=" (less than or equal to) in column F and the maximum allocated labor hours for Touring Bike production (from the scenario/summary section above) in column G.
In the total material cost row, enter "<=" (less than or equal to) in column F and the maximum allocated budget for material costs (from the scenario/summary section above) in column G.
Assign descriptive cell names to the cells for each model and the total in the quantity to produce row, and to the cell containing the total gross profit for all models combined.
Save the workbook using the file name Lab2_yourlastname.xlsx. If you are using the virtual lab environment, you should save it to a folder on your virtual home drive.
Note: Save your work before continuing on to Step 2!
Step 2: Use Solver to Find Optimal Solution
In the section, you will use the Excel Solver Add-In to find the optimum production mix for Adventure Works Cycles Touring Bike models.
If necessary, enable the Solver Add-In.
Start the Solver Add-In. In the Solver dialog, set the objective to maximize the total gross profit from all Touring Bike models combined, using the quantities to produce for each model as the variables. Add all constraints previously stated for the problem, using only cell references (do not hard-code any numbers in your Solver constraints). Select Simplex LP as the solving method.
Use the Solver Add-In to solve for the optimal Touring Bike production mix. Keep the Solver solution. Generate all reports (answer, sensitivity, and limits). Save under the scenario name Original Solution. Drag the report sheets so they appear to the right of the Touring Bike Model Mix sheet in the workbook.
Save the workbook.
Note: Save your work before continuing on to Step 3!
Step 3: Perform Sensitivity Analysis
In this section, you will perform a sensitivity analysis to explore how changing the gross profit per unit parameter of each model by +/- 10% affects the optimal product mix.
Change the gross profit per unit of the Touring-1000 by -10% and solve again using Solver. Do not generate any reports. Save under the scenario name T1000 GP -10%.
On your own: Create additional scenarios in which, compared to the original solution, the gross profit for the Touring-1000 is increased 10%; the gross profit for the Touring-2000 is decreased or increased 10%; and the gross profit for the Touring-3000 is decreased and increased 10%. Give each scenario a descriptive name that follows the pattern established in the previous step. Change the gross profit for only one model at a time; in each case, use the gross profit values from the original solution for the other two models.
Create a scenario summary report sheet showing the quantities to produce for each model, the total quantity produced for all models combined, and the total gross profit for all models combined. The summary report should include seven scenarios: the original solution plus the gross profit +/-10% scenarios for each of the three models. Rename the scenario summary sheet to GP Sensitivity Analysis and drag it to the last sheet position in the workbook.
On your own: Use yellow highlighting to identify any scenarios in the sensitivity analysis where the solution was significantly different from the original solution.
Save the workbook.
