Wk2 Lab Transcripts and Steps

docx

School

University of Louisiana, Lafayette *

*We aren’t endorsed by this school

Course

380

Subject

Management

Date

Jan 9, 2024

Type

docx

Pages

Uploaded by emarielaver

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 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%

Category Points % 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."

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Week 2 Lab Part 1 SPEAKER: In this series of videos, we'll see how to use models in Excel to do production planning for Adventure Works Cycles, a fictional bicycle manufacturer. In this segment, we'll set up a model for Adventure Works' production of its touring bike subcategory. We'll assume that since this is an established product line for the company, the various parameters of the model are reasonably well-known, so this is a case of decision-making under certainty. We know the conditions and the question is under those conditions, how many of each type of bike should the company produce? Launch Excel 2016. You can do this either on your own computer from the Start menu or in the virtual lab Citrix environment. In the virtual lab, use the link under the Introduction & Resources area under Modules, Course Resources, and Lab Resources. And log in with your DeVry student ID number and your usual portal password. If you've already added Excel 2016 to your Favorites, launch it from here. If not, go to the All Apps tab, use the search box at the upper right to find it. Click Details, and add it to your Favorites. And then launch. Create a new blank workbook. Rename the first worksheet to Touring Bike Model Mix. Enter Touring Bike Production Mix as a title in the upper left cell, and then enter column headings for each of the three touring bike models, the Touring 1000, Touring 2000, and Touring 3000. Also add a Total column. Adjust column widths as necessary. Set up a row for the quantity of each model to produce. And we'll enter placeholder numbers for each model here, because this is what we want the model to actually calculate for us. Then use the sum function, or just click the AutoSum button, in order to create a formula in the total column to add these up. Under this, set up rows for the parameters of the model, the gross profit per unit, labor hours per unit, material cost per unit, and required minimum production quantities. The values for these will come from the information in the scenario summary section of the lab instructions. For the minimum production quantities, look underneath the table where that information is in the paragraph below. Format the gross profit and material cost in either the accounting or the currency formats. Now we can do some calculations. Add rows for total gross profit, total labor hours, and total material cost, with appropriate formulas. Total gross profit is the quantity produced times the gross profit per unit. Total labor hours is the quantity produced times the labor hours per unit. And total material cost is the quantity produced times the total labor hours. After entering the formulas in one column, highlight them and drag the fill handle across to fill the other columns. Then add sums in the total column for each of these. We want to find the solution that makes AdventureWorks as much total gross profit as possible. So, enter Maximize beside total gross profit, total labor hours, and material costs are limited by constraints, so enter that total labor hours must be less than or equal to the value that's given in the scenario summary of the lab instructions. Total material costs must also be less than or equal to the value that's given for the total material cost budget in the lab instructions. Format those numbers appropriately.

We'll do one more thing, planning ahead a little. We'll be generating a few different scenarios based on the model, similar to the scenarios in a what-if analysis, and our scenario summary report will be easier to read if we assign names to some of the key cells. So click on the quantity to produce for the Touring 1000, and in the name box at the top left corner of the worksheet, type QTY_T1000, using underscores instead of spaces. Then assign similar names to the other two models. And to the total. Click on the total gross profit, assign the name total_GP. Now click the disk icon at the upper left to save the workbook. If you are working in the virtual lab, for now, save it to a folder on your virtual home drive. That's the F colon drive, labeled with your DeVry student ID number. And be sure to permit all access if you're asked. If you're working on a local computer, you can save it to a folder on your desktop or under My Documents. Now we have our model set up, in the next video segment, we'll use the Solver add to find the ideal production quantities based on this model. Week 2 Lab Part 1 SPEAKER: In this series of videos, we'll see how to use models in Excel to do production planning for Adventure Works Cycles, a fictional bicycle manufacturer. In this segment, we'll set up a model for Adventure Works' production of its touring bike subcategory. We'll assume that since this is an established product line for the company, the various parameters of the model are reasonably well-known, so this is a case of decision-making under certainty. We know the conditions and the question is under those conditions, how many of each type of bike should the company produce? Launch Excel 2016. You can do this either on your own computer from the Start menu or in the virtual lab Citrix environment. In the virtual lab, use the link under the Introduction & Resources area under Modules, Course Resources, and Lab Resources. And log in with your DeVry student ID number and your usual portal password. If you've already added Excel 2016 to your Favorites, launch it from here. If not, go to the All Apps tab, use the search box at the upper right to find it. Click Details, and add it to your Favorites. And then launch. Create a new blank workbook. Rename the first worksheet to Touring Bike Model Mix. Enter Touring Bike Production Mix as a title in the upper left cell, and then enter column headings for each of the three touring bike models, the Touring 1000, Touring 2000, and Touring 3000. Also add a Total column. Adjust column widths as necessary. Set up a row for the quantity of each model to produce. And we'll enter placeholder numbers for each model here, because this is what we want the model to actually calculate for us. Then use the sum function, or just click the AutoSum button, in order to create a formula in the total column to add these up. Under this, set up rows for the

parameters of the model, the gross profit per unit, labor hours per unit, material cost per unit, and required minimum production quantities. The values for these will come from the information in the scenario summary section of the lab instructions. For the minimum production quantities, look underneath the table where that information is in the paragraph below. Format the gross profit and material cost in either the accounting or the currency formats. Now we can do some calculations. Add rows for total gross profit, total labor hours, and total material cost, with appropriate formulas. Total gross profit is the quantity produced times the gross profit per unit. Total labor hours is the quantity produced times the labor hours per unit. And total material cost is the quantity produced times the total labor hours. After entering the formulas in one column, highlight them and drag the fill handle across to fill the other columns. Then add sums in the total column for each of these. We want to find the solution that makes AdventureWorks as much total gross profit as possible. So, enter Maximize beside total gross profit, total labor hours, and material costs are limited by constraints, so enter that total labor hours must be less than or equal to the value that's given in the scenario summary of the lab instructions. Total material costs must also be less than or equal to the value that's given for the total material cost budget in the lab instructions. Format those numbers appropriately. We'll do one more thing, planning ahead a little. We'll be generating a few different scenarios based on the model, similar to the scenarios in a what-if analysis, and our scenario summary report will be easier to read if we assign names to some of the key cells. So click on the quantity to produce for the Touring 1000, and in the name box at the top left corner of the worksheet, type QTY_T1000, using underscores instead of spaces. Then assign similar names to the other two models. And to the total. Click on the total gross profit, assign the name total_GP. Now click the disk icon at the upper left to save the workbook. If you are working in the virtual lab, for now, save it to a folder on your virtual home drive. That's the F colon drive, labeled with your DeVry student ID number. And be sure to permit all access if you're asked. If you're working on a local computer, you can save it to a folder on your desktop or under My Documents. Now we have our model set up, in the next video segment, we'll use the Solver add to find the ideal production quantities based on this model. Week 2 Lab Part 3 SPEAKER: In a sensitivity analysis, we tweak some of our numbers slightly to see how this affects our results. Ideally, we would like our solution to be approximately the same, even if the model parameters change a little. That's not always the case. And if so, we at least need to know about it.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Let's start by changing the gross profit for the Touring 1000, decreasing it by 10%. Now use Solver. Without changing anything else, solve again. In this case, nothing changes. Click Save Scenario. Enter the name, T1000 GP minus 10%, click OK. And OK again. Now on your own, repeat this process to see what happens when the Touring 1000 gross profit is increased 10 %. And similarly, when gross profit for the other touring bikes is adjusted up or down by 10%. Give an appropriate name to each scenario. When you apply the 10% increase or decrease to one bike, be sure to reset the gross profit for the other bikes back to their original values so you're only changing one bike at a time. When done, restore all three gross profit per unit cells to their original values and solve one more time so that your final spreadsheet reflects the original solution. To compare the results of your sensitivity tests, click on What If Analysis on the Data tab of the ribbon, select Scenario Manager. You'll see the scenarios created from the Solver iterations listed in the Scenario Manager dialog. Click the Summary button to generate a scenario summary report for the result cells. Choose the total quantity produced. Hold down the Control key. Also choose the total gross profit. Click OK. After a short delay, a scenario summary worksheet will be created. Drag it into the last position in the workbook. Review the results of your sensitivity analysis. On your own, highlight the column for any scenarios where the production mix changed. Why do you think in these cases, relatively small changes in the gross profit per unit produced fairly large changes in the product mix? This completes our work with Solver and decision-making under certainty. In the next video segment, we'll use a different modeling technique, Monte Carlo simulation for decision making under risk. Week 2 Lab Part 4 SPEAKER: Touring bikes make up an established product line for AdventureWorks. So we could go to our Cost Accounting Department and get numbers for our model with a fair degree of certainty. In other situations, like launching a brand new product line, that may not be the case. A different modeling approach, Monte Carlo Simulation can help when we only know probabilities and not certainties, that is, when we're doing decision making under risk. We'll model a new product for AdventureWorks called the BMX-9000. Add a new sheet to the workbook and rename it BMX-9000 Simulation. Make sure that it's the last rightmost sheet in the workbook. Enter the row labels for the BMX-9000 model as shown here and given in the lab instructions. Adjust the column

width as needed for the row labels. Leave the trial number cell blank. For the quantity produced, enter 100 as a placeholder for now. This is what we want to determine. The Marketing Department can't give us a single precise sales forecast, but they have given us a range with a mean or most likely forecast of 100 and a standard deviation of 30. We'll assume a normal or bell curve distribution. For the projected demand, we want to randomly pick a value from that distribution. Click the fx or Insert Function button. Search for the NORM.INV function. Select it and click OK. For probability, enter RAND and open and close parentheses to generate a random number. For the mean, select the cell with the sales projection mean. For the standard deviation, select the cell with the sales projection standard deviation. Click OK. A randomly generated value for the projected demand will be shown. This will change as you continue to make entries since Excel recalculates the sheet every time, but that's OK. The quantity we sell at full price will be the smaller of the projected demand and the quantity produced. We can't sell more than the demand at full price, but we also can't sell more than we make, even if the demand is higher. So for this cell, enter equals min parenthesis the quantity produced comma the projected demand. Close the parentheses. Now, any excess that we produce above projected demand will be sold at a loss. If we produce less than the demand, then the quantity sold at a loss will be 0. For this, we can use the formula equals max open parentheses quantity produced minus the quantity sold at full price comma 0. Close parentheses. The numbers for gross profit per unit at full price and gross profit per unit on loss sales are given to us by our friendly cost accountants and are listed in the scenario summary. At full price, we have a gross profit per unit of 500. And at less than full price, we have a loss of minus 100. Notice that the gross profit when selling at a loss is, of course, negative. Our gross profit on full price sales is the quantity sold at full price multiplied by the gross profit per unit on full price sales. And our gross profit on loss sales is, similarly, the quantity sold at a loss times the gross profit per unit on those lost sales, again, 0 or negative number. We get the total gross profit by simply adding those last two values. Format the gross profit values using the accounting or the currency format. This model randomly simulates different sales outcomes, but it only does it one trial at a time. In the next video segment to get results for many trials, we'll combine this with Excel's what-if analysis capability using a two-variable data table. Week 2 Lab Part 5

SPEAKER: In this segment, we'll run multiple trials with our random model and summarize the results. To make this work more efficiently, we'll turn off Excel's automatic recalculation inside the table. Go to the Formulas tab, click Calculation options, and choose Automatic Except for Data Tables. Now we're good to go. Skip a few rows in the spreadsheet. And starting in column B, enter some different possibilities for the quantity of bikes to produce, ranging from 20 to 200 in increments of 20. We can do this easily by entering the first two values in the series, 20 and 40, then selecting them, dragging the fill handle to complete the series. In column A, starting in the following row, enter the trial number from 1 to 100. Here's an easy way to do this. Click in the cell where you want the trial numbers to start, and enter the first trial number of 1. Then select the cell again on the Home tab of the ribbon, click Fill and select Series. Select Columns, enter a step value of 1 and a stop value of 100, and click OK. Excel will fill in the rest of the series. The upper left cell of a two-variable data table links to the results we want, which in this case is total gross profit, so click in this corner cell, enter an equals sign, and then click on the cell with the number for total gross profit. Now highlight the complete data table, and on the Data tab, click What If Analysis and Data Table. For the row input cell, click on the cell containing the quantity produced number since the values 20 to 200 across the top represent the quantities produced. And for the column input cell, click on the blank cell next to trial number, since the numbers 1 to 100 down the left column are the trial numbers. Click OK. At first, you'll see all of the same values since we turned off automatic recalculation inside the table, so go to the Formulas tab, click Calculate now. Excel simulates 100 random trials with each of the production quantities, and fills in the resulting gross profit from each trial in the table. To summarize these results, add a row label just above the data table, labeled Average Gross Profit. Enter a formula to summarize or to average the total gross profit for all 100 trials. And then fill this across. And format these as accounting or currency. Visualize these results by creating a chart. This will be easier if the quantities produced are above, rather than below, the average gross profit numbers. So in the row above average gross profit, add the label Quantity Produced. Copy paste the quantities from the top row of the data table. Now highlight the quantity produced and the average gross profit rows. Go to the Insert tab, select the clustered column chart, choose the 2D clustered column option. Position the chart in a blank area near the top of the sheet. Give the chart a descriptive title. On the Chart Tools Design tab, choose Select Data, uncheck the data series for quantity produced. Click the Edit button above the horizontal axis labels. Select the quantity produced values so that they will be used as the category labels. Click OK. And then use Add Chart Element to give your chart a vertical axis title of Average Gross Profit and a primary horizontal axis title of Quantity Produced. Save the workbook.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

If you've been working in the virtual lab environment, you will now need to save to your local computer so that you will be able to submit your work. Go to File, Save As, and click Browse. Select Local Disk, C colon under Computer, and then open in succession the folders' users, your login on the computer you're using, which will be your D number if it's a DeVry computer only, and the location on your computer, such as Desktop or Downloads or My Documents, where you want to save your work. Create a new folder in this location if you'd like and save. Your last step in this lab is to write an opinion paper based on your results with your findings and recommendations for AdventureWorks management. Be sure to include your recommended production quantities for the three touring bikes, as well as for the BMX 9000. Submit your Excel workbook and the Word document with your opinion paper. You should now have a good understanding of how to use Solver for models involving decision-making under certainty and Monte Carlo simulations for modeling decision under risk. 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 Touring- 1000, 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 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. 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 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.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

BMX-9000 Production Model 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. 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 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 .

Previous Next Top

Wk2 Lab Transcripts and Steps

Related Documents