Question

1. Simulation. You are projecting sales for the next year by running simulations of different scenarios. You need to use Excel to complete this portion of the assignment, though you need not turn in your spreadsheet.

(a) The quantity demanded for your company’s products next year can be 100,000, 200,000, or 300,000 with equal probability. Describe your strategy to simulate such scenarios, and write down the Excel functions you would use.

(b) The potential price of your company’s product next year is normally distributed with a mean of $7 and a standard deviation of $2. Describe your strategy to simulate such scenarios, and write down the Excel functions you would use.

(c) The unit cost of your company’s product may be either $3 or $5 with equal probability. Describe your strategy to simulate such scenarios, and write down the Excel functions you would use.

(d) Using 10000 iterations, simulate the average profit you expect your company to make next year.

(e) According to your simulation results, what is the probability that your company will be unprofitable next year?

Answer 1

(a) The quantity demanded for your company’s products next year can be 100,000, 200,000, or 300,000 with equal probability

That means the probability of demand

We create the following cumulative probability and the interval of random numbers

			Random number interval
Demand	Probability	Cumulative Probability	From	to
100,000	0.3333	0.3333	0	0.3333
200,000	0.3333	0.6667	0.3333	0.6667
300,000	0.3333	1.0000	0.6667	1.0000

We use the following strategy to simulate demand

• generate a random number from uniform distribution in the interval 0,1
• Check the random number interval in which this number lies and pick the corresponding demand
  • For example if the random number generated is 0.55, it lies in the interval 0.3333 to 0.6667 and hence the simulated demand is 200,000

Excel functions

• Use =RAND() to generate from uniform (0,1)
• Then use =VLOOKUP() to lookup the value of demand from the table above

b) The potential price of your company’s product next year is normally distributed with a mean of $7 and a standard deviation of $2.

We will use inverse CDF method to generate the price

The following are the steps

• Generate a random number u from uniform distribution in the interval (0,1)
• Then is a drawn from normal distribution where is the  inverse CDF of normal distribution  with mean of $7 and a standard deviation of $2.

Excel functions

• We will use =NORM.INV(RAND(),7,2) to simulate prices

(c) The unit cost of your company’s product may be either $3 or $5 with equal probability.

that means P(3) = P(5) = 0.5

Strategy to simulate

• Generate a random number u from uniform distribution in the interval (0,1)
• If the random number generated is less than 0.5 then the unit cost is $3, else the unit cost is $5

Excel function

• We will use =IF(RAND()<0.5,3,5) to simulate the unit cost

Finally profit = Demand*(Price-Unit cost) assuming that you can produce all that is demanded

d) Create the following sheet

now replicate this into 10,000 rows

Paste the demand,price, unit costs as values to avoid the changes

Get this sheet

Get the average profit using

Get this

ans: The average profit that we expect for the company next year is $606,139.96

e) the probability that your company will be unprofitable next year is

The following does this

get this

The probability that your company will be unprofitable next year is 0.0861