Question

1. The wedding date for a couple is quickly approaching, and the wedding planner must provide the caterer an estimate of how many people will attend the reception so that the appropriate quantity of food is prepared for the buffet. The following table contains information on the number of RSVP guests for the 150 invitations. Unfortunately, the number of guests does not always correspond to the number of RSVPed guests.

   Based on her experience, the wedding planner knows it is extremely rare for guests to attend a wedding if they notified that they will not be attending. Therefore, the wedding planner will assume that no one from these 55 invitations will attend. The wedding planner estimates that the each of the 20 guests planning to come solo has a 75% chance of attending alone, a 20% chance of not attending, and a 5% chance of bringing a companion. For each of the 65 RSVPs who plan to bring a companion, there is a 90% chance that she or he will attend with a companion, a 5% chance of attending solo, and a 5% chance of not attending at all. For the 10 people who have not responded, the wedding planner assumes that there is an 80% chance that each will not attend, a 15% chance each will attend alone, and a 5% chance each will attend with a companion.

   RSVPed Guests	Number of Invitations
   0	55
   1	20
   2	65
   No response	10

   1. Assist the wedding planner by constructing a spreadsheet simulation model to determine the expected number of guests who will attend the reception. Round your answer to 2 decimal places.
      
      guests
      
   2. To be accommodating hosts, the couple has instructed the wedding planner to use the Monte Carlo simulation model to determine X, the minimum number of guests for which the caterer should prepare the meal, so that there is at least a 90% chance that the actual attendance is less than or equal to X. What is the best estimate for the value of X? Round your answer to the nearest whole number.
      
      guests

Answer 1

Solution:-

Refer the screenshot of the excel calculations for 1000 run Monte Carlo simulation.

Following are the formulae for probabilistic calculation model:

Prepare the prbability tables as shown on the right side.

For Guests who have RSVPed 1 person attending, RSVP 1 table is with probabilties whether 25 people will come alone, or none of them will come or all will come with companion making count 50 is given with probabilties as mentioned in problem statement. The probability is mentioned next to number of guests. and cummulative probability is calculated last as: Cell I3 = zero, Cell I4 =I3+K3, Cell I5 =I4+K4.

Same way prepare the cummulative probability tables for RSVP 2 & No RSVP

Now to the simulation of 1000 runs of calculations:

Column A is for RSVP 1, Cell A1000 =VLOOKUP(RAND(),$I$3:$J$5,2)

Column B is for RSVP 2, Cell B1000 =VLOOKUP(RAND(),$I$7:$J$9,2)

Column C is for No RSVP, Cell C1000 =VLOOKUP(RAND(),$I$11:$J$13,2)

Column D is total of the 3 probable guest numbers D1000 =A1000+B1000+C1000

(a) Calulate this for for first row & drag to 1001 rows for getting 1000 runs of the same calculation. Now the Average of the guests calculated in column D is the average possible guest coming: 135 guests

(b) calculate average & standard deviation of the 1000 totals in column D then use normal distribution formula to get 90% probability guest count

Average = Cell D1002 =AVERAGE(D2:D1001)

Stadard Deviation = Cell D1003 =STDEV.S(D2:D1001)

Number of guests with 90% possibility of serving them = Cell D1004 =NORMINV(0.9,D1002,D1003)

Cateres should prepare to serve 175 guests for minimum 90% service

Please give a Like, if it Helps You.

0.75 OOOU WN 120 ༠ 0.05 A1000་ X J& =VLOOKUP(RAND),$$3:$J$5,2) A B C D E F G H K ། ། 1 RSVP 1 RSVP 2 No RSVP Total guests Cummulative probability Number of guests Probability 120 0 145 RSVP 1 120 145 25 120 145 0.75 0.2 120 095 500.05 25 120 RSVP 2 120 0.9 25 120 0.9 0.05 25120 0,95 60 10 No RSVP 25 120 120 0.8 0.15 13 25 120 200.05 g97 10 993 120 999 25 120 145 _2560 85 120 10 130 134.855 Mean 1003 31.43 Standard Deviation 1004 175.15 Normal distribution, guests for 90% probability 11 0.8 145 0.95 120 155 145 1OOD 1001 1002 1005