Description
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 Lab2_yourlastname.xlsx containing 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 Lab2_yourlastname_Paper.docx containing 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
Lab Videos
Please watch the videos below for guidance on completing the lab steps.
Important: The videos will demonstrate how to perform the operations required for the lab, but you will not receive full credit for the lab if you only recreate what is shown in the videos. You will learn to do each operation by following along with the video; then you will perform a similar operation on your own. Be sure to complete all steps in the written lab instructions below, using the videos as a guide; do not just follow along with the videos. Parts in the written instructions that go beyond what is shown in the vidoes will be marked with “On your own.”