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.
a. Launch Excel 2016 and create a new blank workbook. For this lab, you may use your own copy of Microsoft Excel 2016, or you may use Excel 2016 in the Citrix 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).
b. Rename the first worksheet to Touring Bike Model Mix.
c. Enter the title "Touring Bike Production Mix" in the top left cell.
d. In the next row, enter the row label "Model" in column A; the model names Touring1000, Touring-2000, and Touring-3000 in columns B, C, and D respectively; and the column heading Total in column E.
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.
f. 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.
g. 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.
h. 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.
i. In column F or the total gross profit row, enter "Maximize" to indicate the goal is to maximize the company's total gross profit.
j. 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.
k. 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.
l. 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.
m. 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.
a. If necessary, enable the Solver add-in.
b. 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.
c. Use the Solver add-in to solve for the optimal Touring Bike production mix. Keep the Solver solution. Generate all reports (answer, senstivity, 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.
d. 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.
a. 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%."
b. 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 Touring2000 is decreased or increased 10%; and the gross profit for the Touring-300 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.
c. 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.
d. On your own: Use yellow highlighting to identify any scenarios in the sensitivity analysis where the solution was significantly different from the original solution.
e. Save the workbook.
NOTE: Save your work before continuing on to Step 4!

Step 4: Create Monte Carlo Simulation
In this section, you will create a Monte Carlo simulation to determine the best production quantity for the new BMX-9000 model.
a. Add a new worksheet and name it "BMX-9000 Simulation." Drag the sheet to the last (rightmost) position in the workbook.
b. Starting in the upper right (A1) cell, set up the following model to calculate BMX 9000 gross profits.

BMX-9000 Production Model
Trial Number                   Leave this cell blank.
Quantity Produced          100 (this is an initial trial value)
Sales Projection             Enter the marketing department's full-price sales
Mean                               projection as given in the scenario/summary section.
Sales Projection             Enter the standard deviation of the marketing
Standard Deviation          department's fullprice sales project
                                       as given in the scenario/summary section
Projected Demand          Enter a formula to calculate a random
                                       normally distributed value with the
                                       mean and standard deviation of the sales projection
Quantity Sold at Full       Enter a formula for the minimum of the projected
Price                               demand and the quantity produced
Quantity Sold at a             Enter a formula for the maximum of zero and the difference
Loss                                  between the quantity produced and the quantity
                                       sold at full price.
Gross Profit per Unit       Enter the gross profit per unit for full-price sales given
(Full Price)                         in the scenario/summary section.
Gross Profit per Unit       Enter the gross profit per unit for sales at a loss given
(Loss)                               in the scenario/summary (should be a negative number)
                                       section.
Gross Profit on Full          Multiply the quantity sold at full price times the gross
Price Sales                      profit per unit (full price).
Gross Profit on Loss       Multiply the quantity sold at loss times the gross per unit
Sales                               loss
Total Gross Profit             Add the gross profit on full price sales and the gross profit
                                       on loss sales

Note that each time you enter a new value or formula, the number in your projected demand cell, and all other cells calculated from it, will change. This is because Excel recalculates the spreadsheet and generates a new random number each time.
c. Change the calculation option to Automatic Except for Data Tables.
d. In a blank area of the same sheet, create a two-variable data table in which the row variable is the quantity produced (values of 20 to 200 in increments of 20), the column variable is the trial number (1 to 100), and the result is the total gross profit.
e. Trigger a manual recalculation to fill in the data table.
f. Add formulas to calculate the average gross profit over all 100 trials for each production quantity.
g. On the same sheet, add a column chart showing the average gross profit for each production quantity. Give the chart a descriptive title and axis labels.
h. Save the workbook.
NOTE: Save your work before continuing on to Step 5!

Step 1: Creation of Formulas and Constraints In this step, you wil...