Question

The University of Cincinnati Center for Business Analytics is an outreach center that collaborates with industry partners on applied research and continuing education in business analytics. One of the programs offered by the center is a quarterly Business Intelligence Symposium. Each symposium features three speakers on the real-world use of analytics. Each of the corporate members of the center (there are currently 10) receives eight free seats to each symposium. Nonmembers wishing to attend must pay $75 per person. Each attendee receives breakfast, lunch, and free parking. The following are the costs incurred for putting on this event:

Rental cost for the auditorium:	$150
Registration Processing:	$8.50	per person
Speaker Costs: 3@$800	$2,400
Continental Breakfast:	$4.00	per person
Lunch:	$7.00	per person
Parking:	$5.00	per person

(a)

The Center for Business Analytics is considering a refund policy for no-shows. No refund would be given for members who do not attend, but for nonmembers who do not attend, 50% of the price will be refunded. Build a spreadsheet model in Excel that calculates a profit or loss based on the number of nonmember registrants. Extend the model you developed for the Business Intelligence Symposium to account for the fact that historically, 25% of members who registered do not show and 10% of registered nonmembers do not attend. The center pays the caterer for breakfast and lunch based on the number of registrants (not the number of attendees). However, the center only pays for parking for those who attend. What is the profit if each corporate member registers their full allotment of tickets and 127 nonmembers register?

If required, round your answers to two decimal places. B) Use a two-way data table to show how profit changes as a function of number of registered nonmembers and the no-show percentage of nonmembers. Vary number of nonmember registrants from 80 to 160 in increments of 5 and the percentage of nonmember no-shows from 10% to 30% in increments of 2%. In which interval of nonmember registrants does breakeven occur if the percentage of nonmember no-shows is 22%? Breakeven appears in the interval of   to   number of registered nonmembers.

Answer 1

Total Corporate Member (A)	10
Seats reserved for each member (B)	8
Total tickets allotted for members (C=A*B)	80
Total Registration received of Non-Members	127
			(Amt in $)
	Members	Non-Members	Total
Total Seats allotted/registered	80	127	207
Seats registered but not occupied	25% of above	10% of above
Seats Occupied	60	114	174
Non-attendants	20	13	33
Registration receipts (D)	-	9525	9525
Cost incurred by Centre
Refund to Non-attendants	-	487.5	487.5
Registration Processing Charges	680	1079.5	1759.5
Continental Breakfast Charges	320	508	828
Lunch Charges	560	889	1449
Parking Charges	300	570	870
Rental cost of auditorium	-	-	150
Speaker Cost	-	-	2400
Total Cost incurred by Centre (E)			7944
Profit earned by Centre (D-E)			1581

Profit earned by the Centre from each non-member (before deducting Charges of Auditorium Rental $150 & Speakers Charges of $2400) in following two scenarios
	If registerd non-member attend the Symposium	If registerd non-member does not attend the Symposium
Registration Receipts	75	75
Refundable receipts (50%)	0	-37.5
Registration Proceesing Charges	-8.5	-8.5
Breakfast	-4	-4
Lunch	-7	-7
Parking	-5	0
	50.5	18
Out of the above calculated profit, centre has to pay rental charges of $150 and speaker cost of $ 2400 irrespective of number of attendies

% of No-shows non-members	Number of registration of non-members
80	85	90	95	100	105	110	115	120	125	130	135	140	145	150	155	160
10	1230	1450	1702.5	1922.5	2175	2395	2647.5	2867.5	3120	3340	3592.5	3812.5	4065	4285	4537.5	4757.5	5010
12	1165	1417.5	1637.5	1890	2110	2330	2582.5	2802.5	3055	3275	3495	3747.5	3967.5	4220	4440	4660	4912.5
14	1132.5	1352.5	1572.5	1825	2045	2265	2517.5	2737.5	2957.5	3177.5	3430	3650	3870	4122.5	4342.5	4562.5	4815
16	1067.5	1287.5	1540	1760	1980	2200	2420	2672.5	2892.5	3112.5	3332.5	3552.5	3805	4025	4245	4465	4685
18	1035	1255	1475	1630	1915	2135	2355	2575	2795	3015	3267.5	3487.5	3707.5	3927.5	4147.5	4367.5	4587.5
20	970	1190	1410	1630	1850	2070	2290	2510	2730	2950	3170	3390	3610	3830	4050	4270	4490
22	905	1125	1345	1565	1785	2005	2225	2445	2665	2852.5	3072.5	3292.5	3512.5	3732.5	3952.5	4172.5	4392.5
24	872.5	1092.5	1280	1500	1720	1907.5	2160	2347.5	2567.5	2787.5	3007.5	3227.5	3415	3635	3855	4075	4295
26	807.5	1027.5	1247.5	1435	1655	1875	2062.5	2282.5	2502.5	2690	2910	3130	3350	3537.5	3757.5	3977.5	4165
28	775	962.5	1182.5	1370	1590	1810	1997.5	2217.5	2405	2625	2845	3032.5	3252.5	3440	3660	3880	4067.5
30	710	897.5	1117.5	1305	1525	1712.5	1932.5	2120	2340	2527.5	2747.5	2935	3155	3342.5	3562.5	3750	3970

If % of non members noshows is 22%, breakeven will occur when 59 non-members will register,

In case of 59 REGISTRATIONS of non-members, 22% will not attend i.e. 13 non members will not attend and 46 non members will attend. Thus center will earn $18 from 13 non members and $50.5 from 46 non members, total amounting to $ 2557 and further will pay rental and speaker's charge of $150+$2400=$2550, thus net profit of $7, which is equivalent to a position of NO PROFIT, NO LOSS.