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)
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
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!
These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.LAB2 RESULTS SUMMARY
Based on the scenario provided, a comprehensive analysis in MS Excel was conducted in regards to the Touring Bike Production Mix. In first part, best (optimal) production mix for the Touring Bike models is determined. Second, sensitivity analysis was conducted with the aim to explore how changing the gross profit per unit parameter of each model (by +/- 10%) affects the optimal product mix. Finally, in third part, a Monte Carlo simulation was conducted and the best production quantity for the new BMX-9000 model is determined.
Based on the conducted analysis, next key findings could be highlighted:...
By purchasing this solution you'll be able to access the following files:
Solution1.docx and Solution2.xlsx.