Question

 

Micromedia offers computer training seminars on a variety of topics. In the seminars each student works at a personal computer, practicing the particular activity that the instructor is presenting. Micromedia is currently planning a two-day seminar on the use of Microsoft Excel in statistical analysis. The projected fee for the seminar is $600 per student. The cost for the conference room, instructor compensation, lab assistants, and promotion is $9600. Micromedia rents computers for its seminars at a cost of $60 per computer per day, which must be reserved and paid for before the seminar. There is no refund for unused computers. The demand for the seminar varies as follows:

Demand    Probability
10    0.15
20    0.40
30    0.30
40    0.10
50    0.05

Build a simulation model to find out what would be a good number of computers reserved now. Then run it 10,000 times (with 2-way data-table with "computers to reserve now" as the row input). Note that Micromedia cannot admit students any more if they run out of the reserved computers. That is, if there are more students who like to attend the seminar than the number of computers reserved, the excess students cannot attend the seminar.

Question 1 (2 points)

If they reserve 15 computers now, the average profit is

Your Answer:

Question 2 (2 points)

If they reserve 30 computers now, the average profit is

Your Answer:

Question 3 (2 points)

If they reserve 45 computers now, the average profit is

Your Answer:

Question 4 (2 points)

If they reserve 35 computers now, the probability that the profit is positive is

Your Answer:

Answer 1

Simulation using excel

Using the demand table, we calcaultate the cumulative probability and set the random number ranges to simulate the demand

Demand	Probability	Cumulative probability	Interval of random numbers
10	0.15	0.15	0 to less than 0.15
20	0.4	0.55	0.15 to less than 0.55
30	0.3	0.85	0.55 to less than 0.85
40	0.1	0.95	0.85 to less than 0.95
50	0.05	1	0.95 to less than 1

Following are the steps to simulate a demand.

• Generate a random number from uniform distribution in the interval 0,1
• Check the interval it falls into in the table above and pick the corresponding demand
  • For example if the random number generated is 0.65 then the demand would be 30

Let R be the computers reserved and D be the demand.

The profit is (Revenue from the fees @$600/student) - (Fices cost of $9600) - (Variable cost of renting at $60 per computer per day, or $120 per computer for 2 days)

if R>=D

• R*600 - 96000 - R*120

if R

• D*600- 9600 - R*120

This can be combined into one formala in excel as

profit = min(R,D)*600 - 9600 - R*120

Now the following excel showing 2 simulations

A sample data for first few rows is

Now we need to genearte 10,000 rows. Random number can be generated using =RAND() or we use

data-->data analysis-->random number geneartion as below

Get the following

We get the average profits for differet reservations as below

Get the following values

	Reservation
	15	30	45
Average profit	-2853.3	637.8	-107.1

Q1 If they reserve 15 computers now, the average profit is -$2,853.30

Q2 ) If they reserve 30 computers now, the average profit is $637.80

Q3) If they reserve 45 computers now, the average profit is -$107.1

Q4) If they reserve 35 computers now, the count of number of times the profit is greater than 0 is calculated as

We get the count of number of times out of 10,000 the profit is postive, when 35 computers are reserved is

If they reserve 35 computers now, the probability that the profit is positive is 4574/10000 = 0.46