# Simulation in Excel using YASAI

1. ## Simulation in Excel using YASAI

I have a problem using YASAI to simulate a problem.
A hardware company sells a lot of low-cost, high-volume products. For one such product, it is equally likely that annual unit sales will be low or high. If sales are low (60,000), the company can sell the product for \$10 per unit. If sales are high (100,000), a competitor will enter and the company will be able to sell the product for only \$8 per unit. The variable cost per unit has a 25% chance of being \$6, a 50% chance of being \$7.50, and a 25% of being \$9. Annual fixed costs are \$30,000.
1. Use simulation to estimate the company's expected annual profit
So, my main problem is that I am supposed to use YASAI, but I can manage this easily in @risk. Can anyone help me by explaining how to set up the formulas in excel?
Or is it possible using excel functions?

Attached is the set-up I got so far and the @RISK model.

-Andreas

2. ## Re: Simulation in Excel using YASAI

Maybe this will help?
http://www.yasai.rutgers.edu/yasai-guide-26.html

3. ## Re: Simulation in Excel using YASAI

Preetty straightforward with just vanilla Excel:

 A B C D E F G H 1 Fixed 2 30,000 3 4 Sales Price Cost \$ 6.00 \$ 7.50 \$ 9.00 5 60,000 \$ 10.00 \$210,000 \$120,000 \$30,000 D5: =\$A5 * (\$B5 - D\$4) - \$A\$2 6 100,000 \$ 8.00 \$170,000 \$20,000 (\$130,000) 7 8 Prob Prob 25% 50% 25% 9 50% 12.5% 25.0% 12.5% D9: =\$B9 * D\$8 10 50% 12.5% 25.0% 12.5% 11 12 Expectation \$70,000 D12: =SUMPRODUCT(D5:F6, D9:F10)

4. Originally Posted by FDibbins
I've been trying that, but when I use the only logical formula (genTable), my solution won't be a fixed number.

5. Originally Posted by shg
Preetty straightforward with just vanilla Excel:

 A B C D E F G H 1 Fixed 2 30,000 3 4 Sales Price Cost \$ 6.00 \$ 7.50 \$ 9.00 5 60,000 \$ 10.00 \$210,000 \$120,000 \$30,000 D5: =\$A5 * (\$B5 - D\$4) - \$A\$2 6 100,000 \$ 8.00 \$170,000 \$20,000 (\$130,000) 7 8 Prob Prob 25% 50% 25% 9 50% 12.5% 25.0% 12.5% D9: =\$B9 * D\$8 10 50% 12.5% 25.0% 12.5% 11 12 Expectation \$70,000 D12: =SUMPRODUCT(D5:F6, D9:F10)
Thanks! I'll see what I can do with this

6. ## Re: Simulation in Excel using YASAI

Okay, so I figured out how to do it in Excel using YASAI.
Thanks

There are currently 1 users browsing this thread. (0 members and 1 guests)