You are the owner of a small business that manages the program, food, and merchandise sales at the Excel Center, where the local professional basketball team plays. In addition to basketball games, the Excel Center hosts dozens of event a year. Your company handles all sales made inside the arena. There are three major products that you sell: programs, food, and merchandise. You have decided to build a spreadsheet model to help you analyze and project the profitability of future events. The 'Event Profit' worksheet contains this model. You have randomly selected sales and attendance data from a number of past events to help build your model. This data can be found on the on the 'Past Events' worksheet. You will complete statistics on this model to obtain appropriate values for the inputs for the model. Complete the tasks to finish the model. Whenever possible, reference cells rather than 'hard-coding' values in the cells of your spreadsheet.

Input Analysis 100 # of Events Attendan Programs Food Merchandise Model Inputs Product Type Programs Food Merchandise Cost of Goods Sold (COGS) 40% 60% 70% Commission 207 10% 5% Fixed Costs $5,000.00 $10,000.00 $20,000.00 Employees (% of Expected Profil 0.05% 0.20% 0.03% Salary (per employee) $50.00 Arena Fee $100,000.00 Statistic Average Standard Deviation 95% Confidence Interval Programs Food Merchandise Product Type Correlation with Attendance Event Profitabilit Anal sis Attendance 18000 Gross Profit Product Type Programs Food Merchandise Sales Cost of Goods Sold (COGS) Gross Profit Programs Food Merchandise Product Type Sales Forecast Upper Limit for Sales Lower Limit for Sales Operating Expenses Product Type Programs Food Merchandise Salary Expenses Commissions Fixed Costs Total Operating Expenses Yhat-if Anal sis Attendance Forecast 15000 18000 21000 Profit before Arena Fee Arena Fee Number of Employees Profit Summary Total Question 1: The sales for which product type are most highly correlated with attendance? Item Sales Gross Profit Total Operating Expenses Profit before Arena Fee Arena Fee Net Profit Question 2: How likely is it that food sales would exceed $220,000 if attendance is 18000? Question 3: Which product generates the largest gross profit? Question 4: Which product will require the fewest employees? Question 5: Would the event be profitable with a 150,000 arena fee if 15000 people were in attendance? Randomly Selected Past Events Event Crowd Siz ittendanc Programs Food Merchandis Type 1123 Fair 11055 $28,190 $76,722 $146,037 Sporting Event 1125 Good 19689 $168,538 $113,409 $499,116 Sporting Event 1214 Good 16657 $74,457 $239,861 $351,629 Sporting Event 1217 Fair 11383 $32,100 $181.217 $271,826 Sporting Event 1322 Good 15334 $102,584 $98,598 $618,267 Concert 1416 Fair 12081 $70,191 $68,620 $407,975 Sporting Event 1419 Excellent 20082 $106,836 $246.808 $561,292 Sporting Event 1621 Fair 10643 $39.486 $139,210 $448,070 Concert 1713 Good 18421 $ 128,947 $223,078 $615,814 Sporting Event 1716 Good 18485 $63,219 $187,992 $251,396 Sporting Event 1723 Good 17012 $35,045 $204.484 $137.967 Other Event 1919 Good 16884 $29,040 $128,318 $695,621 Concert 2119 Fair 14350 $35,014 $72,181 $569,552 Concert 2317 Good 15901 $34.187 $221,183 $244,557 Sporting Event 2321 Poor 8789 $63,544 $131,659 $103,798 Other Event 2322 Fair 13629 $59,968 $211,250 $457,253 Sporting Event 2416 Fair 13331 $95,583 $161,172 $419,527 Sporting Event 2418 Poor 8569 $35,047 $106,084 $117,310 Sporting Event 2419 Excellent 21016 $56,323 $305,362 $774,860 Concert 2523 Fair 13281 $23,109 $113,951 $454,210 Sporting Event 2524 Excellent 21538 $119,536 $129,228 $987,087 Concert 2527 Fair 12465 $79,153 $142,101 $121,160 Other Event 2615 Excellent 20611 $176,430 $166,125 $581,642 Sporting Event 2620 Excellent 20145 $58,018 $234,689 $283,843 Sporting Event 2621 Poor 8800 $30,096 $87,032 $292,336 Sporting Event 2919 Excellent 20999 $97,645 $223,639 $893,927 Concert 2921 Good 15443 $123,390 $237,822 $177,749 Other Event 2924 Fair 10848 $67,149 $172,483 $72,356 Other Event 3121 Good 19953 $91,983 $265,774 $906,265 Concert 3216 Good 18903 $147,632 $157,840 $504,710 Sporting Event 3920 Poor 9074 $34,390 $123,769 $288,281 Sporting Event 3922 Excellent 20498 $53,705 $304,190 $565,335 Sporting Event 4122 Fair 11573 $87,608 $63,420 $213.175 Sporting Event 4416 Excellent 21249 $170.417 $320,010 $127,919 Other Event 4422 Fair 11257 $99.624 $150,394 $413,132 Sporting Event 4520 Good 19235 $25,006 $181.194 $483,760 Sporting Event 4719 Fair 13275 $19,382 $96.775 $300,679 Sporting Event 4819 Fair 13378 $13,512 $206,423 $526,424 Concert 4820 Good 18449 $44,093 $216.407 $126,007 Other Event 4919 Fair 13725 $80,291 $71.233 $199,562 Sporting Event 5114 Good 15710 $41.946 $217,426 $238,321 Sporting Event 5119 Fair 11382 $61.235 $127,934 $195,087 Sporting Event 5125 Fair 12437 $18,656 $125,241 $126,236 Other Event 5218 Fair 14828 $46,856 $84,520 $661,625 Concert 5223 Good 18468 $71,102 $117.641 $378,963 Sporting Event 5317 Fair 13952 $120,406 $113,011 $602,308 Concert 5323 Excellent 20051 $120,707 $201,312 $555,012 Sporting Event 5425 Good 19744 $138,405 $154,990 $560,927 Sporting Event 5520 Good 19205 $129,826 $273,863 $701,367 Sporting Event 5619 Good 16309 $62,953 $137,159 $492,043 Sporting Event 5620 Good 15234 $116,845 $117,149 $609,512 Concert 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 63 TO 11 72 73 74 75 76 TT 78 79 80 81 82 83 84 85 86 87 88 89 30 31 32 33 34 35 36 37 38 33 100 101 102 103 5620 Good 5714 Fair 5720 Poor 5821 Fair 5916 Good 5927 Fair 6216 Poor 6423 Fair 6522 Fair 6619 Poor 6625 Excellent 6627 Fair 6712 Good 6722 Fair 6820 Poor 6821 Fair 6917 Fair 7120 Fair 7215 Good 7216 Good 7320 Fair 7618 Good 7714 Good 7821 Fair 7822 Good 7919 Fair 8213 Fair 8216 Good 8220 Good 8221 Poor 8223 Good 8319 Fair 8418 Good 8523 Fair 8524 Excellent 8815 Fair 8823 Fair 8914 Excellent 8916 Good 8921 Good 9127 Poor 9220 Good 9421 Fair 9423 Good 9515 Good 9520 Good 9717 Poor 9720 Poor 9818 Fair 9922 Poor 15234 14435 9173 14906 19484 10847 8675 11434 11306 8830 21934 11879 19774 10582 8627 13029 13004 12217 18696 16938 13861 15925 18782 12453 19820 14174 11515 16560 16116 9469 17639 12917 17723 13497 21799 13596 11375 21663 18469 16811 8414 15448 13303 17784 19791 18932 9893 8276 11471 8100 $116,845 $80,980 $41,737 $71,698 $77,741 $27,551 $37.476 $48,709 $64,105 $31,347 $196,529 $95,270 $152,655 $10.899 $25,191 $88,467 $54,617 $57,176 $144,707 $18.462 $25,920 $20,225 $30,051 $60,646 $106,235 $69,169 $48,593 $129,830 $100,564 $57,761 $56,092 $21,789 $60,967 $91,375 $66,051 $52,073 $43.111 $158.140 $83,665 $125,746 $53,261 $105,973 $64,520 $157,744 $48,290 $67,587 $40,363 $23,504 $71,694 $51,516 $117.149 $200,502 $63,477 $86,008 $198.932 $82,871 $131.947 $74,321 $62,296 $87.770 $133,578 $90,874 $137,232 $84,339 $99,987 $112,571 $207,804 $157,721 $288,479 $124,325 $188,371 $102,398 $247,735 $109,213 $121,695 $215,445 $75,193 $136,289 $176,309 $114,291 $135,997 $122,787 $97,122 $70,859 $268.346 $183,002 $81,900 $ 197,567 $197 249 $96.495 $113,673 $189.701 $89,396 $252,355 $252,929 $286,820 $77,363 $59,835 $ 163,462 $123,444 $609,512 Concert $467,550 Sporting Event $309,680 Sporting Event $498,606 Sporting Event $812,678 Concert $285,276 Sporting Event $339,193 Concert $72.949 Other Event $77,785 Other Event $175,276 Sporting Event $911,138 Concert $149,913 Other Event $817,062 Concert $176,931 Sporting Event $352,499 Concert $488,718 Concert $81,145 Other Event $538,037 Concert $364,198 Sporting Event $364,675 Sporting Event $426,503 Sporting Event $162,913 Other Event $389,727 Sporting Event $462,878 Concert $696,277 Sporting Event $84,335 Other Event $70,357 Other Event $620,669 Concert $488,798 Sporting Event $181,994 Sporting Event $543,458 Sporting Event $420,526 Sporting Event $97,831 Other Event $491,291 Sporting Event $152,811 Other Event $525.078 Concert $282,783 Sporting Event $872,152 Concert $472,437 Sporting Event $383,795 Sporting Event $162,306 Sporting Event $194,645 Other Event $112,942 Other Event $726,832 Concert $390,872 Sporting Event $112,077 Other Event $387,113 Concert $279,067 Sporting Event $166,674 Sporting Event $360,774 Concert 104 Input Analysis 100 # of Events Attendan Programs Food Merchandise Model Inputs Product Type Programs Food Merchandise Cost of Goods Sold (COGS) 40% 60% 70% Commission 207 10% 5% Fixed Costs $5,000.00 $10,000.00 $20,000.00 Employees (% of Expected Profil 0.05% 0.20% 0.03% Salary (per employee) $50.00 Arena Fee $100,000.00 Statistic Average Standard Deviation 95% Confidence Interval Programs Food Merchandise Product Type Correlation with Attendance Event Profitabilit Anal sis Attendance 18000 Gross Profit Product Type Programs Food Merchandise Sales Cost of Goods Sold (COGS) Gross Profit Programs Food Merchandise Product Type Sales Forecast Upper Limit for Sales Lower Limit for Sales Operating Expenses Product Type Programs Food Merchandise Salary Expenses Commissions Fixed Costs Total Operating Expenses Yhat-if Anal sis Attendance Forecast 15000 18000 21000 Profit before Arena Fee Arena Fee Number of Employees Profit Summary Total Question 1: The sales for which product type are most highly correlated with attendance? Item Sales Gross Profit Total Operating Expenses Profit before Arena Fee Arena Fee Net Profit Question 2: How likely is it that food sales would exceed $220,000 if attendance is 18000? Question 3: Which product generates the largest gross profit? Question 4: Which product will require the fewest employees? Question 5: Would the event be profitable with a 150,000 arena fee if 15000 people were in attendance? Randomly Selected Past Events Event Crowd Siz ittendanc Programs Food Merchandis Type 1123 Fair 11055 $28,190 $76,722 $146,037 Sporting Event 1125 Good 19689 $168,538 $113,409 $499,116 Sporting Event 1214 Good 16657 $74,457 $239,861 $351,629 Sporting Event 1217 Fair 11383 $32,100 $181.217 $271,826 Sporting Event 1322 Good 15334 $102,584 $98,598 $618,267 Concert 1416 Fair 12081 $70,191 $68,620 $407,975 Sporting Event 1419 Excellent 20082 $106,836 $246.808 $561,292 Sporting Event 1621 Fair 10643 $39.486 $139,210 $448,070 Concert 1713 Good 18421 $ 128,947 $223,078 $615,814 Sporting Event 1716 Good 18485 $63,219 $187,992 $251,396 Sporting Event 1723 Good 17012 $35,045 $204.484 $137.967 Other Event 1919 Good 16884 $29,040 $128,318 $695,621 Concert 2119 Fair 14350 $35,014 $72,181 $569,552 Concert 2317 Good 15901 $34.187 $221,183 $244,557 Sporting Event 2321 Poor 8789 $63,544 $131,659 $103,798 Other Event 2322 Fair 13629 $59,968 $211,250 $457,253 Sporting Event 2416 Fair 13331 $95,583 $161,172 $419,527 Sporting Event 2418 Poor 8569 $35,047 $106,084 $117,310 Sporting Event 2419 Excellent 21016 $56,323 $305,362 $774,860 Concert 2523 Fair 13281 $23,109 $113,951 $454,210 Sporting Event 2524 Excellent 21538 $119,536 $129,228 $987,087 Concert 2527 Fair 12465 $79,153 $142,101 $121,160 Other Event 2615 Excellent 20611 $176,430 $166,125 $581,642 Sporting Event 2620 Excellent 20145 $58,018 $234,689 $283,843 Sporting Event 2621 Poor 8800 $30,096 $87,032 $292,336 Sporting Event 2919 Excellent 20999 $97,645 $223,639 $893,927 Concert 2921 Good 15443 $123,390 $237,822 $177,749 Other Event 2924 Fair 10848 $67,149 $172,483 $72,356 Other Event 3121 Good 19953 $91,983 $265,774 $906,265 Concert 3216 Good 18903 $147,632 $157,840 $504,710 Sporting Event 3920 Poor 9074 $34,390 $123,769 $288,281 Sporting Event 3922 Excellent 20498 $53,705 $304,190 $565,335 Sporting Event 4122 Fair 11573 $87,608 $63,420 $213.175 Sporting Event 4416 Excellent 21249 $170.417 $320,010 $127,919 Other Event 4422 Fair 11257 $99.624 $150,394 $413,132 Sporting Event 4520 Good 19235 $25,006 $181.194 $483,760 Sporting Event 4719 Fair 13275 $19,382 $96.775 $300,679 Sporting Event 4819 Fair 13378 $13,512 $206,423 $526,424 Concert 4820 Good 18449 $44,093 $216.407 $126,007 Other Event 4919 Fair 13725 $80,291 $71.233 $199,562 Sporting Event 5114 Good 15710 $41.946 $217,426 $238,321 Sporting Event 5119 Fair 11382 $61.235 $127,934 $195,087 Sporting Event 5125 Fair 12437 $18,656 $125,241 $126,236 Other Event 5218 Fair 14828 $46,856 $84,520 $661,625 Concert 5223 Good 18468 $71,102 $117.641 $378,963 Sporting Event 5317 Fair 13952 $120,406 $113,011 $602,308 Concert 5323 Excellent 20051 $120,707 $201,312 $555,012 Sporting Event 5425 Good 19744 $138,405 $154,990 $560,927 Sporting Event 5520 Good 19205 $129,826 $273,863 $701,367 Sporting Event 5619 Good 16309 $62,953 $137,159 $492,043 Sporting Event 5620 Good 15234 $116,845 $117,149 $609,512 Concert 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 63 TO 11 72 73 74 75 76 TT 78 79 80 81 82 83 84 85 86 87 88 89 30 31 32 33 34 35 36 37 38 33 100 101 102 103 5620 Good 5714 Fair 5720 Poor 5821 Fair 5916 Good 5927 Fair 6216 Poor 6423 Fair 6522 Fair 6619 Poor 6625 Excellent 6627 Fair 6712 Good 6722 Fair 6820 Poor 6821 Fair 6917 Fair 7120 Fair 7215 Good 7216 Good 7320 Fair 7618 Good 7714 Good 7821 Fair 7822 Good 7919 Fair 8213 Fair 8216 Good 8220 Good 8221 Poor 8223 Good 8319 Fair 8418 Good 8523 Fair 8524 Excellent 8815 Fair 8823 Fair 8914 Excellent 8916 Good 8921 Good 9127 Poor 9220 Good 9421 Fair 9423 Good 9515 Good 9520 Good 9717 Poor 9720 Poor 9818 Fair 9922 Poor 15234 14435 9173 14906 19484 10847 8675 11434 11306 8830 21934 11879 19774 10582 8627 13029 13004 12217 18696 16938 13861 15925 18782 12453 19820 14174 11515 16560 16116 9469 17639 12917 17723 13497 21799 13596 11375 21663 18469 16811 8414 15448 13303 17784 19791 18932 9893 8276 11471 8100 $116,845 $80,980 $41,737 $71,698 $77,741 $27,551 $37.476 $48,709 $64,105 $31,347 $196,529 $95,270 $152,655 $10.899 $25,191 $88,467 $54,617 $57,176 $144,707 $18.462 $25,920 $20,225 $30,051 $60,646 $106,235 $69,169 $48,593 $129,830 $100,564 $57,761 $56,092 $21,789 $60,967 $91,375 $66,051 $52,073 $43.111 $158.140 $83,665 $125,746 $53,261 $105,973 $64,520 $157,744 $48,290 $67,587 $40,363 $23,504 $71,694 $51,516 $117.149 $200,502 $63,477 $86,008 $198.932 $82,871 $131.947 $74,321 $62,296 $87.770 $133,578 $90,874 $137,232 $84,339 $99,987 $112,571 $207,804 $157,721 $288,479 $124,325 $188,371 $102,398 $247,735 $109,213 $121,695 $215,445 $75,193 $136,289 $176,309 $114,291 $135,997 $122,787 $97,122 $70,859 $268.346 $183,002 $81,900 $ 197,567 $197 249 $96.495 $113,673 $189.701 $89,396 $252,355 $252,929 $286,820 $77,363 $59,835 $ 163,462 $123,444 $609,512 Concert $467,550 Sporting Event $309,680 Sporting Event $498,606 Sporting Event $812,678 Concert $285,276 Sporting Event $339,193 Concert $72.949 Other Event $77,785 Other Event $175,276 Sporting Event $911,138 Concert $149,913 Other Event $817,062 Concert $176,931 Sporting Event $352,499 Concert $488,718 Concert $81,145 Other Event $538,037 Concert $364,198 Sporting Event $364,675 Sporting Event $426,503 Sporting Event $162,913 Other Event $389,727 Sporting Event $462,878 Concert $696,277 Sporting Event $84,335 Other Event $70,357 Other Event $620,669 Concert $488,798 Sporting Event $181,994 Sporting Event $543,458 Sporting Event $420,526 Sporting Event $97,831 Other Event $491,291 Sporting Event $152,811 Other Event $525.078 Concert $282,783 Sporting Event $872,152 Concert $472,437 Sporting Event $383,795 Sporting Event $162,306 Sporting Event $194,645 Other Event $112,942 Other Event $726,832 Concert $390,872 Sporting Event $112,077 Other Event $387,113 Concert $279,067 Sporting Event $166,674 Sporting Event $360,774 Concert 104