Step 4: Monte Carlo Simulation
In this step, you will create a Monte Carlo simulation to determine the best production quantity for the new BMX-9000 model using MS Excel.
Open a new worksheet and name it BMX-9000 Simulation. Name the Tab: BMX-9000.
Starting in the upper right (A1) cell, set up the following model to calculate BMX-9000 gross profits. Cell A1 will have the label: BMX-9000 Production Model
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 gross 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: 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.
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.
Add formulas to calculate the average gross profit over all 100 trials for each production quantity.
Calculate the Average Gross Profit for each column.
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.
Save the workbook.
