Question

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 RSVPs for the 145 invitations. Unfortunately, the number of guests does not always correspond to the number of RSVPs. Based on her experience, the wedding planner knows that it is extremely rare for guests to attend a wedding if they affirmed that they will not be attending. Therefore, the wedding planner will assume that no one from these 50 invitations will attend The wedding planner estimates that the each of the 25 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 60 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 they will attend alone, and a 5% chance they will attend with a companion. RSVPsNo. of Invitations 50 25 60 10 No response (a) Assist the wedding planner by constructing a spreadsheet simulation model to determine the expected number of guests who will attend the reception. If required, round your answer to the nearest whole number (b) To be accommodating hosts, the couple has instructed the wedding planner to use a 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 ofX Select your answer - herk My

Answer 1

A1000 1 RSVP 1 RSVP 2 No RSVP Total guests 145 145 145 Cummulative probability Number of guests Probability RSVP 1 25 25 0.75 0.2 0.05 0.75 0.95 145 145 145 RSVP 2 0.9 0.05 0.05 25 25 0.9 0.95 10 10 95 145 No RSVP 25 0.8 0.15 0.05 0.8 0.95 13 145 155 145 25 25 10 998 25 25 1000 1001 1002 1003 1004 1005 10 130 134.865 Mean 31.43 Standard Deviation 175.15 Normal distribution, guests for 90% probability

(a) Calulate this for for first row & drag to 1001 rows for getting 1000 runs of the same calculation. Now the Average of the quests calculated in column D is the average possible quest coming: 135 quests (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 ther-Cell D1004-NORM INV(09,D1002,D1003) Cateres should prepare to serve 175 guests for minimum 90% service

formulas

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 13-zero, Cell 14 13+K3, Cell 15-14+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(RANDO,SiS3:SJS5,2) Column B is for RSVP 2, Cell B1000-VLOOKUP(RANDO,SIS7:SJS9,2) Column C is for No RSVP, Cell C1000 VLOOKUP(RAND0,SIS11:SJS13,2) Column D is total of the 3 probable guest numbers D1000-A1000+B1000+C1000