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

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

- 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**. - Click on cell
**C15**to specify the “Monthly Demand” discrete custom input distribution. Select**DiscoverSim**>**Input Distribution**: - Select
**Discrete**>**Custom Discrete Distribution**. - 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 . - Click the
**Data Range**cell reference and specify cells**F15:F24**as the data range for the custom discrete distribution. - 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**. - Click
**Update Chart**to view the custom discrete distribution as shown: - Click
**OK**. Hover the cursor on cell**C15**to view the DiscoverSim graphical comment showing the distribution and parameter values: - 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.
- Click on cell
**C17**. Select**Control**:

- Click input
**Name**cell reference and specify cell**B17**containing the Input Control name “Magazines Produced”. - 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: - Click
**OK**. Hover the cursor on cell**C17**to view the comment displaying the Input Control settings: - 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). - Select
**DiscoverSim**>**Output Response**: - 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. - Hover the cursor on cell
**C28**to view the DiscoverSim Output information. - Select
**DiscoverSim**>**Run Simulation**: - Select
**Seed Value**and enter “12” as shown, in order to replicate the simulation results given below. - The DiscoverSim Output Report displays a histogram, descriptive statistics and process capability indices:
- Now we are ready to perform the discrete optimization to maximize the mean of net profit. Select
**DiscoverSim**>**Run Optimization**: - 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: - Click
**Run**. This optimization will take approximately 30 seconds. - The final optimal parameter value is given as:
- You are prompted to paste the optimal value into the spreadsheet:
- Select
**DiscoverSim**>**Run Simulation**: - Select
**Seed Value**and enter “12” as shown, in order to replicate the simulation results given below. - The DiscoverSim Output Report displays a histogram, descriptive statistics and process capability indices for the optimized setting:
- 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.

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

Click** Run**.

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.

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.

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

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

Click **Run**.

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

# 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)

**Support: **Support@SigmaXL.com

**Sales: **Sales@SigmaXL.com

**Information: **Information@SigmaXL.com