Include Top

Introduction: Simulation and Optimization to Determine Optimal Magazine Production Quantity to Maximize Profit

Back to Table of Contents

This is an example of DiscoverSim simulation and optimization to determine optimal magazine production quantity to maximize net profit.

Monthly Demand is modeled using a discrete custom distribution.

A DiscoverSim Input Control is used to set the number of magazines produced (x 10,000).

Production and disposal costs are variable costs per unit. (Note: content and fixed printing costs are covered by advertising and subscriptions).

Revenue = MIN(Monthly_Demand,Monthly_Demand,Magazines_Produced)*Selling_Price_to_Retailers

Total Production Cost = Magazines_Produced*Production_Cost

Total Disposal Cost = IF((Magazines_Produced-Monthly_Demand)>0,Magazines_Produced-Monthly_Demand,0)*Disposal_Cost

Net Profit = Revenue - (Total_Production_Cost + Total_Disposal_Cost)

Summary of DiscoverSim Features Demonstrated in Case Study 2:

  • Create Input Distributions with Custom Discrete
  • Create Discrete Input Controls
  • Run Simulation
  • Run Optimization

  1. Open the workbook Magazine Production – Optimization. A custom discrete input distribution will simulate the variability in monthly demand and is specified in cell C15. The demand quantity data for this custom distribution is given in cells F15: F24 with weights specified in cells G15:G24. The weights are converted to calculated probabilities in cells H15:H24. A DiscoverSim Input Control (Discrete) will be used to specify the magazine production quantity (1 to 10) at cell C17. This value is then multiplied by 10,000 at cell C18. The output, Net Profit will be specified at cell C28.

  2. Click on cell C15 to specify the “Monthly Demand” discrete custom input distribution. Select DiscoverSim > Input Distribution:

  3. Select Discrete > Custom Discrete Distribution.

  4. Click the input name cell reference and specify cell B15 containing the input Name “Monthly Demand” (Note: spaces are replaced with the underscore “_” character for all input names). After specifying a cell reference, the dropdown symbol changes from to .

  5. Click the Data Range cell reference and specify cells F15:F24 as the data range for the custom discrete distribution.

  6. Click the Weight Range cell reference and specify cells G15:G24 as the weight range for the custom discrete distribution. These weights are used to determine the distribution probabilities as displayed in cells H15:H24.

  7. Click Update Chart to view the custom discrete distribution as shown:

  8. Click OK. Hover the cursor on cell C15 to view the DiscoverSim graphical comment showing the distribution and parameter values:

  9. Now we will add the Discrete Input Control (also referred to as a “Decision Variable”). This will be used to set the magazine production quantity (multiplied by 10,000). DiscoverSim will use Stochastic Global Optimization to determine what quantity will maximize the mean net profit.

  10. Click on cell C17. Select Control:

  11. Click input Name cell reference and specify cell B17 containing the Input Control name “Magazines Produced”.

  12. Select Discrete (this Input Control setting will consider only integer values). Set the Start value to 10, Min value to 1 and Max value to 10 as shown:

  13. These values are multiplied by 10,000 in cell C18. Our staring value is the current production volume of 100,000 magazines per month.”

  14. Click OK. Hover the cursor on cell C17 to view the comment displaying the Input Control settings:

  15. Now we will specify the output. Click on cell C28. The cell contains the Excel formula for the Net Profit: = _Revenue - (_Total_Production_Cost + _Total_Disposal_Cost).

  16. Select DiscoverSim > Output Response:

  17. Click output Name cell reference and specify cell B28 containing the Output name “Net Profit”. Enter the Lower Specification Limit (LSL) as 0. The Weight and Output Goal settings are used only for multiple response optimization, so do not need to be modified in this example.

  18. Hover the cursor on cell C28 to view the DiscoverSim Output information.

  19. Select DiscoverSim > Run Simulation:

  20. Select Seed Value and enter “12” as shown, in order to replicate the simulation results given below.

  21. Click Run.

  22. The DiscoverSim Output Report displays a histogram, descriptive statistics and process capability indices:

  23. The Mean Net Profit is approximately $42,300, which is good, but we also see from the Actual Performance report, we have a 22.9% chance of monthly loss. The standard deviation is also quite high at $47,800.

  24. Now we are ready to perform the discrete optimization to maximize the mean of net profit. Select DiscoverSim > Run Optimization:

  25. Select “Maximize” for Optimization Goal, “Weighted Sum” for Multiple Output Metric and “Mean” for Statistic. Select Genetic Algorithm. Select Seed Value and enter “12”, in order to replicate the optimization results given below. All other settings will be default as shown:

  26. Here we are using the Genetic Algorithm to speed up the optimization. Hybrid optimization method requires more time to compute, but is very powerful to solve complex optimization problems. This particular problem is not complex, given that there is only one input control variable with 10 integer levels. In fact, one could simply try all 10 values, run simulation for each, and select the winner. However, we will let DiscoverSim do the work for us.

    Note: Intermediate calculations of integer optimization use continuous values.

  27. Click Run. This optimization will take approximately 30 seconds.

  28. The final optimal parameter value is given as:

  29. The optimum number of magazines to produce per month is 6 (x 10,000), resulting in an expected mean profit of $59,077.

  30. You are prompted to paste the optimal value into the spreadsheet:

  31. Click Yes. This sets the input control value to the optimum value of 6 as shown:

  32. Select DiscoverSim > Run Simulation:

  33. Select Seed Value and enter “12” as shown, in order to replicate the simulation results given below.

  34. Click Run.

  35. The DiscoverSim Output Report displays a histogram, descriptive statistics and process capability indices for the optimized setting:

  36. From the histogram and capability report we see that the expected mean net profit has increased from $42,300 to $59,300. The standard deviation has decreased from $47,800 to $33,500. Looking at the Actual Performance report we see a decrease in the likelihood of a profit loss from 22.9% to 5.2%.

  37. Obviously the quality of this prediction will depend on the validity of the custom distribution and does not take into account hidden soft costs like customer dissatisfaction due to lack of availability. If necessary, the model can be refined. Nevertheless, the tool is very useful to help us to determine what our production rate should be in order to maximize net profit.

Back to Table of Contents

Define, Measure, Analyze, Improve, Control

Lean Six Sigma Software Excel Add-in

Simulate, Optimize,

Lean Six Sigma Software Excel Add-in

Web Demos

Our CTO and Co-Founder, John Noguera, regularly hosts free Web Demos featuring SigmaXL and DiscoverSim
Click here to view some now!

Contact Us

Ph: 1.888.SigmaXL (744.6295)