**Microsoft Excel **is a powerful software. We can perform numerous tasks on datasets using Excel tools and features. There are many default **Excel functions** that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Often, we have to estimate a quantity for producing a product. We estimate such figures based on market demands. However, the demands don’t stay consistent. Thus, we take the probability into account for each quantity. We can simulate the probability easily in excel. This article will show you step-by-step procedures to get **Simulation Probability **in **Excel**.

**Table of Contents**hide

## Download Practice Workbook

Download the following workbook to practice by yourself.

## Step-by-Step Procedures to Get Simulation Probability in Excel

We’ll consider a case in this example. Suppose we have to estimate a quantity to manufacture a product. In this regard, we set up the **Demand **and **Cut-offs **values. To illustrate, we’ll use the following dataset. For instance, the dataset considers random values from **0 **to less than **.10 **to fall under the demand of **15000**, from** .10** to less than** .45** to fall under **25000**, from **.45** to less than **.75** to fall under **40000**, and random values equal to** .75** and above fall under **50000**. Now, we’ll perform a simulation probability for picking a quantity to produce so that we earn maximum profit and have the minimum amount of unsold products simultaneously.

### STEP 1: Input Estimation

- First, we’ll estimate a quantity for production.
- Type the quantity in cell
**C10**.

### STEP 2: Generate Random Value

- Then, select cell
**C11**. - Here, input the formula:

`=RAND()`

- Subsequently, press
**Enter**. **The RAND function**generates random values.

### STEP 3: Simulate Demand

- Now, we’ll extract the demand against the random number in cell
**C11**. - For this purpose, choose cell
**C12**. - Insert the formula:

`=VLOOKUP(C11,B5:C8,2)`

- Hit
**Enter**. **The VLOOKUP function**searches for the**C11**cell value in the range**B5:C8**and returns the value from the**2nd**column i.e.**Demand**.

### STEP 4: Insert Necessary Info

- Afterward, we’ll input necessary info like
**Unit Production Cost**,**Price**, and**Disposal Cost**in the range**C13:C15**.

**Similar Readings**

**How to Use Continuous Probability Distribution in Excel****Create Joint Probability Table in Excel (with Easy Steps)****How to Find Discrete Probability Distribution in Excel****Create Option Probability Calculator in Excel****How to Model Uniform Probability Distribution in Excel (3 Ways)**

### STEP 5: Create Required Formulas

- Firstly, we’ll determine the
**Revenue**. - So, click cell
**C17**. - Next, type the formula:

`=MIN(C10,C12)*C14`

- Press
**Enter**. **The MIN function**gives out the minimum quantity between cells**C10**and**C12**.

- Now, to get the
**total production cost**, select cell**C18**. - Insert the formula:

`=C10*C13`

- Click
**Enter**.

- For calculating the
**total disposal cost**, choose cell**C19**. - Input the formula:

`=C15*IF(C10>C12,C10-C12,0)`

- After that, press
**Enter**. **The IF function**tests whether the estimated quantity is greater than the demand.- Only then, there will be a disposal cost.
- Otherwise, the disposal cost will be
**0**.

- Finally, to get profit, click cell
**C20**. - Type the formula:

`=C17-C18-C19`

- Subsequently, press
**Enter**.

### STEP 6: Set up Dataset

In this step, we will set up the worksheet for performing the simulation.

- First of all, set up the dataset in the range
**E4:I20**. - Input each of the quantities in the range
**F4:I4**. - Look at the picture below for a better understanding.

- Then, select cell
**E4**. - Here, we’ll input the profit amount.
- Hence, type the formula:

`=C20`

- Press
**Enter**.

### STEP 7: Perform Probability Simulation

In this step, we’ll perform the iteration. For any kind of simulation probability, a larger number of iterations are always recommended. However, in this example, we will conduct **16 **iterations for demonstration convenience. Therefore, follow the process.

- After that, select the range
**E4:I20**. - Next, go to
**Data > Forecast > What-If Analysis > Data Table**.

- As a result, the
**Data Table**dialog box will pop out. - Select cell
**C10**as the**Row input cell**and any blank cell, for example,**K4**as the**Column input cell**. - Afterward, press
**OK**.

- Accordingly, it’ll return the calculated profit amounts for each of the product quantities.
- See the following figure to clearly understand.

**Read More:** **How to Calculate Probability in Excel (3 Practical Examples)**

### STEP 8: Simulate Best Probable Result

Now, we have to pick the best probable quantity to produce, considering the profits and disposal costs too. So, learn the process.

- In the beginning, select cell
**F22**. - Type the formula:

`=AVERAGE(F5:F20)`

- Press
**Enter**. - Subsequently, use
**AutoFill**to the right. **The AVERAGE function**computes the average of the profits for each quantity.

- Moreover, to get the
**Standard Deviation**, click cell**F23**. - Insert the formula:

`=STDEV(F5:F20)`

- Hit
**Enter**and apply**AutoFill**. **The STDEV function**computes the standard deviation.- In this way, we’ll get the deviation.

- Notice, there is no standard deviation for
**15000**. That means, there will be no leftovers. - Consequently, if you do a higher number of iterations, you’ll get one quantity which will always yield the maximum profit.
- Again, to avoid any risks, consider picking the amounts which have lower risks.
- In such a process, you can calculate the simulation probability.

**Read More:** **How to Calculate Probability Distribution in Excel (with Quick Steps)**

## Conclusion

Henceforth, you will be able to get **Simulation Probability **in **Excel **by following the above-described steps. Keep using them and let us know if you have more ways to do the task. Follow **the ExcelDemy** website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

**Related Articles**

**How to Find Marginal Probability in Excel (2 Simple Ways)****How to Calculate Empirical Probability with Excel Formula****Calculating Probability in Excel with Mean and Standard Deviation****How to Make a Probability Tree Diagram in Excel (3 Easy Methods)****How to Calculate Probability of Exceedance in Excel****Poisson Probability Distribution in Excel (4 Practical Examples)**