The University of Cincinnati Center for Eusiness Analytics is an cuitrecth center that colisborates wath industry partners of apelied research and continuing educotion in buscess analytics: One of the orograms cffered by the center is a quortertiv musiness inteligence Symposim. Each symposium teatures three speakers on the real-yocid use of analytics. Each of the corperate members of the center (there are currenthy 10 ) receives five free tickets to each symposium that they can either ose themselves or distribute to colleoguet or friends. Non-members wishing to attend must pay $75 for a tirutt per perwen ta fegistet. The ticket for each registrant (member or non-member) iricludes beeaktast, lunch, and free parking The follewing are the costs incurred on the center for putting on this event. Rental cost for the auditorium: $1000 for up to 150 "total reestered", but 54000 for more than 150 "total registered" Aeglstration Processing: 58.50 per person "registered" Speaker Costs: 38$500$1,500 Continental Ereakfast: $4.00 per person "registered" if catered for 120 regstered people or less, 3480 total (not per person any more) for more than 120 registored neepele Lunch: 57.00 per person "registered" if catered foe 120 people registered of less, 16.00 per person registered if catered for more than. 120 people repotered Borking: 55.00 per person "attended". First read through all questions and then boild a spreadiheet model. Make sure that you disonguish between registered indivisuais and attended individuats, To Mart wath the analysis, have separate cols in Excel to show. the number of "registered members", "reqistered nen members"; and "totai regivtered". Then create cells in yeur sereadsheet. showing the percentage of registered members that would attend and percentage of registered non-members that would attend (based on info in oarts a or 6 . Then, catculate. the number of "member attendees", "non-member attendees" and "total attendees". For each cost component specified in the probiem, make isre that you have sebarate cets that calculate the ToTAL cosT" of each component, and then just add up ail the coits to calculate the total cost. In your calciations, make sure that you are referring to the above 6 cells as needed, bint: Some of the calculations of total cost components require using If function. (a) Assume that all indivieuals that have a ticket in hand shaw up for the event. What would be the total profit of setting up this everst if the center selis so tickits to non: membens? (Round your answer to 2 decimat places) (5) What would be the total profit if the center selis 127 tidkets to non-members insteadt (pound your answer to 2 decimat places) (b) If the center could seil any number of non-member registration tickets between 50 and 155 . (i.e. in increments of 1 ), seling how many non-member bckets nould maximize the profit of the center? (Hint: You can use a data table to answer this questan 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) (c) Toe center is considering a refund policy for no-showa. No refund would be given for registered members who do not attent, but far registered non-members who do nek attend, 50% of the price will be refunded. Extend the model you eveveloped in part (a) to account for the fact that histonically, 25% of registered members do not show and 10%. of registered non-members do not attend. From the obove mentioned cost atructure, note that the center poys the caterer for breakast and lunch, and for aucitaniun tased an the number of rogistrants (not the number af attendees). However, the center enly pays for parking for those whe attend. What is the profit if each corporate inember refisters their full allotment of tickets and 50 non-members register? In your calculation, it is OK it the inumber of attendees becomes tractional, these numbers represent average number of people attended. If reauired, round your answer to two decimal places. s. What would be the total profit if the center selis 127 tickets to non-members instead? (Round your answer to 2 dedimal placel) \$. (d) If the center could sell any number of non-member registration tickets between 50 and 155 (i.e. in inerements of 1 ), selling how many nan-member ticknts would maximizo the center 5 profit based an part (c)? What would be the profit if the above best number of non-member tickets were sold? The University of Cincinnati Center for Eusiness Analytics is an cuitrecth center that colisborates wath industry partners of apelied research and continuing educotion in buscess analytics: One of the orograms cffered by the center is a quortertiv musiness inteligence Symposim. Each symposium teatures three speakers on the real-yocid use of analytics. Each of the corperate members of the center (there are currenthy 10 ) receives five free tickets to each symposium that they can either ose themselves or distribute to colleoguet or friends. Non-members wishing to attend must pay $75 for a tirutt per perwen ta fegistet. The ticket for each registrant (member or non-member) iricludes beeaktast, lunch, and free parking The follewing are the costs incurred on the center for putting on this event. Rental cost for the auditorium: $1000 for up to 150 "total reestered", but 54000 for more than 150 "total registered" Aeglstration Processing: 58.50 per person "registered" Speaker Costs: 38$500$1,500 Continental Ereakfast: $4.00 per person "registered" if catered for 120 regstered people or less, 3480 total (not per person any more) for more than 120 registored neepele Lunch: 57.00 per person "registered" if catered foe 120 people registered of less, 16.00 per person registered if catered for more than. 120 people repotered Borking: 55.00 per person "attended". First read through all questions and then boild a spreadiheet model. Make sure that you disonguish between registered indivisuais and attended individuats, To Mart wath the analysis, have separate cols in Excel to show. the number of "registered members", "reqistered nen members"; and "totai regivtered". Then create cells in yeur sereadsheet. showing the percentage of registered members that would attend and percentage of registered non-members that would attend (based on info in oarts a or 6 . Then, catculate. the number of "member attendees", "non-member attendees" and "total attendees". For each cost component specified in the probiem, make isre that you have sebarate cets that calculate the ToTAL cosT" of each component, and then just add up ail the coits to calculate the total cost. In your calciations, make sure that you are referring to the above 6 cells as needed, bint: Some of the calculations of total cost components require using If function. (a) Assume that all indivieuals that have a ticket in hand shaw up for the event. What would be the total profit of setting up this everst if the center selis so tickits to non: membens? (Round your answer to 2 decimat places) (5) What would be the total profit if the center selis 127 tidkets to non-members insteadt (pound your answer to 2 decimat places) (b) If the center could seil any number of non-member registration tickets between 50 and 155 . (i.e. in increments of 1 ), seling how many non-member bckets nould maximize the profit of the center? (Hint: You can use a data table to answer this questan 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) (c) Toe center is considering a refund policy for no-showa. No refund would be given for registered members who do not attent, but far registered non-members who do nek attend, 50% of the price will be refunded. Extend the model you eveveloped in part (a) to account for the fact that histonically, 25% of registered members do not show and 10%. of registered non-members do not attend. From the obove mentioned cost atructure, note that the center poys the caterer for breakast and lunch, and for aucitaniun tased an the number of rogistrants (not the number af attendees). However, the center enly pays for parking for those whe attend. What is the profit if each corporate inember refisters their full allotment of tickets and 50 non-members register? In your calculation, it is OK it the inumber of attendees becomes tractional, these numbers represent average number of people attended. If reauired, round your answer to two decimal places. s. What would be the total profit if the center selis 127 tickets to non-members instead? (Round your answer to 2 dedimal placel) \$. (d) If the center could sell any number of non-member registration tickets between 50 and 155 (i.e. in inerements of 1 ), selling how many nan-member ticknts would maximizo the center 5 profit based an part (c)? What would be the profit if the above best number of non-member tickets were sold