Management homework help

 

Lab 2: Modeling and Analysis

Lab Overview

Scenario/Summary

Adventure Works Cycles is planning the company’s production for the coming year. In their product subcategory of Touring Bikes, the company manufactures three different models: the Touring-1000, Touring-2000, and Touring-3000. All three are produced at the same factory by the same group of workers, and the company’s overall business plan limits the budget and labor hours available for the Touring Bikes subcategory as a whole. So, making more of one model in the subcategory means making fewer of the others. The production manager for Touring Bikes has asked you to determine what production mix will generate the most gross profit for the company while satisfying all given constraints.

The cost accounting department has provided the following parameters to be used for production planning.

Model	Touring-1000	Touring-2000	Touring-3000
Gross profit per unit	$900	$460	$280
Labor hours per unit	18.5	9.5	11.5
Material cost per unit	$560	$189	$115

The Adventure Works business plan for the coming year allocates a maximum of 2,000 labor hours and $40,000 in material costs for production of all Touring Bike models combined. Contractual commitments with distributors require that the company produce at least 50 Touring-1000 models, 10 Touring-2000 models, and 10 Touring-3000 models.

In addition to these three standard models, Adventure Works plans to introduce a new experimental model for bicycle motocross competition, the BMX-9000. As a new product, this is not subject to the same constraints as the Touring Bike models. However, demand for this new product is uncertain. The marketing department’s best projection for full-price sales is 100 units, with a standard deviation of 30 units, following a normal distribution. Units sold at full price have a gross profit per unit of $500. Any units produced that do not sell at full price will be sold at a loss of -$100 per unit. The production manager has asked you to recommend how many units of the BMX-9000 should be produced based on a simulation.

The tasks for this lab are listed below.

• Create a spreadsheet with formulas and constraints for the Touring Bike model mix.
• Use Solver to find the optimal solution to the problem.
• Perform a sensitivity analysis of the solution.
• Create a Monte Carlo simulation for the BMX-9000.
• Interpret the results and make recommendations to management.

After you are done, submit your completed lab work.

Deliverables

You will submit two files for this lab.

• An Excel workbook titled xlsxcontaining the following worksheets: (1) Touring Bike Model Mix, (2) Answer Report, (3) Sensitivity Report, (4) Limits Report, (5) GP Sensitivity Analysis, (6) and BMX-9000 Simulation
• A Word document titled docxcontaining a one-page summary of your findings and recommendations for Adventure Works

When submitting the workbook, provide a comment explaining what you learned from completing this lab activity.

Category	Points	%
Step 1: Creation of Formulas with Constraints Touring Bike Model Mix sheet has correct set up of problem with all parameter values, constraints, and formulas.	15	21.4%
Step 2: Use Solver to find an optimal solution. Objectives, variables, constraints, and solution method are correctly set in the Solver dialog, and the correct optimal production quantities are shown on the Touring Bike Model Mix sheet. Answer, sensitivity, and limits reports for the solution were generated correctly.	15	21.4%
Step 3: Perform Sensitivity Analysis Perform sensitivity analysis showing how changing parameters by plus or minus 10% affects the solution. A scenario summary sheet was created showing how the optimal product mix is affected by changing the gross profit per unit of each model by plus or minus 10% compared to the original solution.	15	21.4%
Step 4: Create Monte Carlo Simulation Monte Carlo simulation calculates and charts the average gross profit over 100 simulations for the specified range of production quantities.	15	21.4%
Step 5: Opinion Paper Write a one-page paper explaining your findings and making recommendations. Paper is in APA format, free of typographical, spelling, and grammar errors, and clearly states appropriate findings and recommendations from the analysis.	10	21.4%
Total	70	14.4%

Lab Resources

Microsoft Excel 2016

You may use Microsoft Excel on your local PC or from the Virtual Lab-Citrix environment. The link is accessible from the Course Resources page in the Introduction and Resources module. View the Lab Resources section.

Lab Steps

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.

1. 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).
2. Rename the first worksheet to Touring Bike Model Mix.
3. Enter the title “Touring Bike Production Mix” in the top left cell.
4. 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.
5. 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.
6. 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.
7. 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.
8. 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.
9. In column F or the total gross profit row, enter “Maximize” to indicate the goal is to maximize the company’s total gross profit.
10. 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.
11. 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.
12. 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.
13. Save the workbook using the file name 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.

1. If necessary, enable the Solver add-in.
2. 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.
3. 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.
4. 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.

1. 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%.”
2. 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.
3. 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.
4. On your own:Use yellow highlighting to identify any scenarios in the sensitivity analysis where the solution was significantly different from the original solution.
5. 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.

1. Add a new worksheet and name it “BMX-9000 Simulation.” Drag the sheet to the last (rightmost) position in the workbook.
2. 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 Mean	Enter the marketing department’s full-price sales projection as given in the scenario/summary section.
Sales Projection Standard Deviation	Enter the standard deviation of the marketing department’s full-price 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 Price	Enter a formula for the minimum of the projected demand and the quantity produced.
Quantity Sold at a Loss	Enter a formula for the maximum of zero and the difference between the quantity produced and the quantity sold at full price.
Gross Profit per Unit (Full Price)	Enter the gross profit per unit for full-price sales given in the scenario/summary section.
Gross Profit per Unit (Loss)	Enter the gross profit per unit for sales at a loss given in the scenario/summary (should be a negative number) section.
Gross Profit on Full Price Sales	Multiply the quantity sold at full price times the gorss profit per unit (full price).
Gross Profit on Loss Sales	Multiply the quantity sold at loss times the gross profit per unit (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.

1. Change the calculation option to Automatic Except for Data Tables.
2. 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.
3. Trigger a manual recalculation to fill in the data table.
4. Add formulas to calculate the average gross profit over all 100 trials for each production quantity.
5. 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.
6. Save the workbook.

NOTE: Save your work before continuing on to Step 5!

Step 5: Opinion Paper and Submission

Review all the analyses performed for this lab activity, and write a one-page opinion paper summarizing the key findings and recommended actions for management. Be sure to clearly state the recommended production quanities for each model, and also consider and discuss any related findings from the Solver reports, your sensitivity analysis, and the Monte Carlo simulation. Your paper should be in APA format, professionally written, and should be free of grammatical errors, typos, and misspellings. Save your paper in a Microsoft Word file named Lab2_yourlastname_Paper.docx.

Submit both your Excel workbook and your Opinion Paper document.