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 five free tickets to each symposium that they can either use themselves or distribute to colleagues or friends. Non-members wishing to attend must pay $75 for a ticket per person to register. The ticket for each registrant (member or non-member) includes breakfast, lunch, and free parking. The following are the costs incurred on the center for putting on this event:

Rental cost for the auditorium:	$1000	for up to 150 "total (planned) attendees", but $4000 for more than 150 "total (planned) attendees"
Registration Processing:	$8.50	per person "registered"
Speaker Costs: 3@$500	$1,500
Continental Breakfast:	$4.00	per person "registered" if catered for 120 registered people or less, $480 total (not per person any more) for more than 120 registered people
Lunch:	$7.00	per person "registered" if catered for 120 people registered or less, $6.00 per person registered if catered for more than 120 people registered
Parking:	$5.00	per person "attended"

First read through all questions and then build a spreadsheet model. Make sure that you distinguish between registered individuals and attended individuals. To start with the analysis, have separate cells in Excel to show the number of "registered members", "registered non-members", and "total registered". Then create cells in your spreadsheet showing the percentage of registered member that would attend and percentage of registered non-members that would attend (based on info in parts a or c). Then, calculate the number of "member attendees", "non-member attendees" and "total attendees". For each cost component specified in the problem, make sure that you have separate cells that calculate the "TOTAL COST" of each component, and then just add up all the costs to calculate the total cost. In your calculations, make sure that you are referring to the above 6 cells as needed. Hint: Some of the calculations of total cost components require using IF function.

(a)	Assume that all individuals that have a ticket in hand show up for the event. What would be the total profit of setting up this event if the center sells 50 tickets to non-members? (Round your answer to 2 decimal places) $ What would be the total profit if the center sells 127 tickets to non-members instead? (Round your answer to 2 decimal places) $
(b)	If the center could sell any number of non-member registration tickets between 50 and 150 (i.e. in increments of 1), selling how many non-member tickets would maximize the centers profit? (Hint: You can use a data table to answer this question and you have to make sure that your formulas in part a are correctly linked to the cells containing numbers such as the number of registered members and non-members) What would be the profit if the above best number of non-member tickets were sold? $
(c)	The center is considering a refund policy for no-shows. No refund would be given for registered members who do not attend, but for registered non-members who do not attend, 50% of the price will be refunded. Extend the model you developed in part (a) to account for the fact that historically, 25% of registered members do not show and 10% of registered non-members do not attend. From the above mentioned cost structure, note that 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. The cost of auditorium rental is also based on planned attendees (not those registered). What is the profit if each corporate member registers their full allotment of tickets and 127 nonmembers register? In your calculation, it is OK if the number of attendees becomes fractional, these numbers represent average number of people attended.
	If required, round your answer to two decimal places.
	$
(d)	If the center could sell any number of non-member registration tickets between 50 and 150 (i.e. in increments of 1), selling how many non-member tickets would maximize the centers profit based on part (c)? What would be the profit if the above best number of non-member tickets were sold?