You are the owner of a small business that manages the program, food, and merchandise sales...

Transcribed Image Text:

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 events a year. Your company handles all sales made inside the arena. There are three major products 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 "Past Events" worksheet. You will analyze this data in order to obtain appropriate values to develop the model. Complete the tasks to finish the model. Whenever possible, reference cells rather than hard-coding values in the cells of your worksheet. Product Type Cost of Goods Sold (COGS) Commission Fixed Costs Employees (% of Expected Profit) Salary (per employee) Arena Fee Model Inputs Programs 40% 20% $5,000.00 0.05% $50.00 $100,000.00 Food 60% 10% $10,000.00 0.20% E Merchandise 70% 5% $20,000.00 0.03% TI B Product Type Sales Cost of Goods Sold (COGS) Gross Profit Product Type Salary Expenses Commissions Fixed Costs Total Operating Expenses Profit before Arena Fee Number of Employees Product Type Sales Gross Profit Total Operating Expenses Profit before Arena Fee Arena Fee Net Profit C Event Profitability Analysis Gross Profit Programs Operating Expenses Programs Profit Summary Total D Food Food E Merchandise Merchandise F O # of Events H Statistic Average Standard Deviation 95% Confidence Interval Attendance Product Type Correlation with Attendance Product Type Sales Forecast Upper Limit for Sales Lower Limit for Sales Arena Fee 100 Attendance Programs Programs 18000 Programs $50,000.00 $75,000.00 What-if Analysis $100,000.00 $125,000.00 $150,000.00 $175,000.00 $200,000.00 Food Merchandise Food Food 15000 Merchandise Merchandise Attendance Forecast 18000 21000 Questions Question 1: The sales for which product type are most highly correlated with attendance? 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? 66 Instruction Task Submit View Sheet Guide Report Show Instructions Automatic Scoring A1 4 6 Event 1123 1125 1214 1217 1322 1416 1419 1621 1713 13 1716 14 1723 15 1919 16 2119 Fair 17 2317 Good 18. 2321 Poor 19 2322 Fair 20 2416 Fair 21. 2418 Poor 22 2419 Excellent 23 2523 Fair 24 2524 Excellent 2527 Fair 25 26 2615 Excellent 27 2620 Excellent 28 2621 29 2919 9 10 11 A Ready 4 B Crowd Size Attendance 11055 19689 Fair Good Good Fair Good Fair Excellent Fair Good Good Good Good fx Poor Excellent Event Profit Randomly Selected Past Events 16657 11383 15334 12081 20082 10643 18421 18485 17012 16884 14350 15901 8789 13629 13331 8569 21016 13281 21538 12465 20611 20145 8800 20999 Randomly Selected Past Events Past Events Type here to search. Programs $28,190 $168,538 $74.457 $32,100 $102,584 $70,191 Food $76,722 $113,409 $239,861 $181,217 $98,598 $68,620 $106,836 $246,808 $39,486 $139,210 $128,947 $223,078 $63,219 $187,992 $35,045 $204,484 $29,040 $128,318 $35,014 $72,1813 $34,187 $221,183 $63,544 $131,659 EMBRE $59,968 $211,250 $95,583 $161,172 $35,047 $106,084 $56,323 $305,362 $23,109 $113,951 $119,536 $129,228 $79,153 $142,101 $176,430- $166,125 $58,018 $234,689 $30,096 $87,032 $97,645 $223,639 Pivot Table Questions 0 8 F Merchandise $146,037 $499,116 $351,629 $271,826 $618,267 $407,975 $561,292 $448,070 $615,814 $251,396 $137,967 Type Sporting Event Sporting Event Sporting Event Sporting Event Concert Sporting Event Sporting Event Concert $695,621 Concert $569,552 Concert $244,557 Sporting Event $103,798 Other Event $457,253 Sporting Event Sporting Event Other Event Sporting Event $419,527 Sporting Event $117,310. Sporting Event $774,860 Concert $454,210 Sporting Event Concert $987,087 $121,160 Other Event Bakery $581,642 Sporting Event $283,843 Sporting Event $292,336 Sporting Event $893,927 Concert C + W File A1 2 Instruction Task Submit View Sheet Guide Report Show Instructions Automatic Scoring A 29 2919 2921 2924 Fair 3121 SEN-06 51 30 31 32 33 34 35 36 4122 Fair 37 4416 38 4422 39 4520 40 4719 Fair 41 4819 Fair 42 4820 Good 43 4919 Fair 44 5114 Good 45 5119 Fair 46 5125 Fair 47 5218 Fair 48 5223 Good 49 5317 Fair 50 5323 Excellent 5425 Good 5520 Good 5619 Good 5620 Good 5714 Fair 5720 Poor 5821 Fair 52 53 54 Home 55 56 57 Ready 115 35 Insert Page Layout Excellent Good O Good 3216 Good 3920 Poor 3922 Excellent Excellent Fair Good Event Profit ✓fx 20999 15443 10848 19953 18903 9074 20498 11573 21249. 11257 19235 13275 13378 18449 13725 15710 11382 12437 14828 18468 13952 20051 19744 19205 16309 15234 14435 9173 14906 Past Events Type here to search Formulas Data Randomly Selected Past Events $97,645 $223,639 $123,390 $67,149 $91,983 $147,632 $34,390 $53,705 $87,608 $170,417 $99,624 $25,006 $19,382 $13,512 $44,093 $80,291 $41,946 $61,235 $18,656 $46,856 Review View $237,822 $172,483 $265,774 $157,840 $123,769 $304,190 $63,420 $320,010 $150,394 $181,194 $96,775 $206,423 $216,407 $71,233 $217,426 $127,934 $125,241 $84,520 $71,102 $117,641 $120,406 $113,011 $120,707 $201,312 $138,405 $154,990 $129,826 $273,863 $62,953 $137,159 $116,845 $117,149. $80,980 $200,502 $41,737 $63,477 $71,698 $86,008 PivotTable Questions 0 $893,927 $177,749 $72,356 $906,265 $504,710 BI $288,281 $565,335 $213,175 Help Bakery Concert Sporting Event Sporting Event Sporting Event Sporting Event $127,919 Other Event $413,132 $483,760 $300,679 $526,424 $126,007 $199,562 $238,321 $195,087 $126,236 $661,625 Concert Assignmen O G Concert Other Event Other Event $378,963 Sporting Event $602,308 Concert $555,012 Sporting Event $560,927 Sporting Event $701,367 Sporting Event $492,043 Sporting Event Concert Sporting Event Sporting Event Sporting Event Concert Other Event Sporting Event Sporting Event Sporting Event Other Event $609,512 $467,550 Sporting Event $309,680 Sporting Event $498,606 Sporting Event W E File 57 58 Home E 60 Instruction Task Submit View Sheet Guide Report Show Instructions Automatic Scoring A1 85 8319 Ready AND 3 A 5821 Fair 5916 Good 59 5927 Fair 60 6216 Poor 61 6423 Fair 62 6522 Fair 63 6619 Poor 64 6625 Excellent 65 6627 Fair 66 6712 Good 67 6722 Fair 68 6820 Poor Fair Fair Fair Good Good SE Insert Page Layout 69 6821 70 6917 71 7120 72 7215 73 7216 74 7320 Fair 75 7618 Good 76 7714 Good 77 7821 Fair 78 7822 Good 7919 Fair 8213 Fair O B 79 80 81 8216 Good 82 8220 Good 83 8221 Poor 84 8223 Good Fair Event Profit X M 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 12817 Past Events Type here to search Formulas Data Review View Randomly Selected Past Events $71,698 $77,741 $27,551 $37,476 $48,709 $64,105 $31,347 $86,008 $198,932 $498,606 $812,678 $285,276 $339,193 $72,949 Other Event $77,785 Other Event $175,276 $196,529 $133,578 $911,138 $95,270 $90,874 $149,913 $152,655 $137,232 $817,062 Concert $10,899 $84,339 $176,931 Sporting Event $25,191 $99,987 $352,499 Concert $88,467 $112,571 $48,718 Concert $54,617 $207,804 $81,145 Other Event $57,176 $157,721 $538,037 Concert $144,707 $288,479 $364,198 Sporting Event $18,462 $124,325 $364,675 Sporting Event $25,920 $188,371 $426,503 Sporting Event $20,225 $102,398 $162,913 Other Event $30,051 $247,735 $389,727 $60,646 $109,213 $462,878 $106,235 $121,695 $696,277 Sporting Event $69,169 $215,445 $84,335, Other Event $48,593 $75,193 $70,357 Other Event $129,830 $136,289 $620,669 Concert $100,564 $176,309 $488,798 Sporting Event $57,761 $114,291 $181,994 Sporting Event $56,092 $135,997 $543,458 Sporting Event $420,526 Sporting Event $21,789 $122,787 PivotTable Questions $82,871 $131,947 $74,321 $62,296 $87,770 0 Bakery II (+) Help Assignment Sporting Event Concert Sporting Event Concert Sporting Event Concert Other Event Sporting Event Concert W N H File A1 Home struction Task Submit View Sheet Guide Report Show Instructions Automatic Scoring Ready H A 8319 Fair 8418 8523 Fair 8524 8815 Insert Page Layout 5 6 7 8 Excellent 9 Fair 0 8823 Fair 1 8914 Excellent 2 8916 Good 3 8921 Good 4 9127 Poor 5 9220 Good 6 9421 Fair 7 9423 Good 8 9515 Good 9 9520 Good 00 9717 Poor 01 9720 Poor 02 9818 Fair 03 9922 Poor 04 OS 06 07 08 09 10 11 12 13 Good B fr С 12817 17723 13497 21799 13596 11375 21663 18469 16811 8414 15448 13303 17784 19791 18932 9893 8276 11471 8100 Event Profit Past Events Type here to search Formulas Data Randomly Selected Past Events D $21,789 $60,967 $91,375 $66,051 $268,346 $52,073 $183,002 $43,111 $81,900 $158,140 $197,567 $83,665 $197,249 $125,746 $96,495 $53,261 $105,973 $113,673 $189,701 $64,520 $89,396 $157,744 $252,355 $48,290 $252,929 $67,587 $286,820 $40,363 $77,363 $23,504 $59,835 $71,694 $163,462 $51,516 $123,444 Review PivotTable Questions $122,787 $420,526 $97,122 $97,831 $70,859 $491,291 $152,811 $525,078 $282,783 $872,152 $472,437 Sporting Event $383,795 Sporting Event Sporting Event Other Event Other Event $72,832 Concert $390,872 Sporting Event $112,077 Other Event $387,113 Concert $279,067 Sporting Event $166,674 Sporting Event $360,774 Concert 0 View Bakery 6: $162,306 $194,645 $112,942 (+) Help Assignment W Sporting Event Other Event Sporting Event Other Event Concert Sporting Event Concert 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 events a year. Your company handles all sales made inside the arena. There are three major products 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 "Past Events" worksheet. You will analyze this data in order to obtain appropriate values to develop the model. Complete the tasks to finish the model. Whenever possible, reference cells rather than hard-coding values in the cells of your worksheet. Product Type Cost of Goods Sold (COGS) Commission Fixed Costs Employees (% of Expected Profit) Salary (per employee) Arena Fee Model Inputs Programs 40% 20% $5,000.00 0.05% $50.00 $100,000.00 Food 60% 10% $10,000.00 0.20% E Merchandise 70% 5% $20,000.00 0.03% TI B Product Type Sales Cost of Goods Sold (COGS) Gross Profit Product Type Salary Expenses Commissions Fixed Costs Total Operating Expenses Profit before Arena Fee Number of Employees Product Type Sales Gross Profit Total Operating Expenses Profit before Arena Fee Arena Fee Net Profit C Event Profitability Analysis Gross Profit Programs Operating Expenses Programs Profit Summary Total D Food Food E Merchandise Merchandise F O # of Events H Statistic Average Standard Deviation 95% Confidence Interval Attendance Product Type Correlation with Attendance Product Type Sales Forecast Upper Limit for Sales Lower Limit for Sales Arena Fee 100 Attendance Programs Programs 18000 Programs $50,000.00 $75,000.00 What-if Analysis $100,000.00 $125,000.00 $150,000.00 $175,000.00 $200,000.00 Food Merchandise Food Food 15000 Merchandise Merchandise Attendance Forecast 18000 21000 Questions Question 1: The sales for which product type are most highly correlated with attendance? 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? 66 Instruction Task Submit View Sheet Guide Report Show Instructions Automatic Scoring A1 4 6 Event 1123 1125 1214 1217 1322 1416 1419 1621 1713 13 1716 14 1723 15 1919 16 2119 Fair 17 2317 Good 18. 2321 Poor 19 2322 Fair 20 2416 Fair 21. 2418 Poor 22 2419 Excellent 23 2523 Fair 24 2524 Excellent 2527 Fair 25 26 2615 Excellent 27 2620 Excellent 28 2621 29 2919 9 10 11 A Ready 4 B Crowd Size Attendance 11055 19689 Fair Good Good Fair Good Fair Excellent Fair Good Good Good Good fx Poor Excellent Event Profit Randomly Selected Past Events 16657 11383 15334 12081 20082 10643 18421 18485 17012 16884 14350 15901 8789 13629 13331 8569 21016 13281 21538 12465 20611 20145 8800 20999 Randomly Selected Past Events Past Events Type here to search. Programs $28,190 $168,538 $74.457 $32,100 $102,584 $70,191 Food $76,722 $113,409 $239,861 $181,217 $98,598 $68,620 $106,836 $246,808 $39,486 $139,210 $128,947 $223,078 $63,219 $187,992 $35,045 $204,484 $29,040 $128,318 $35,014 $72,1813 $34,187 $221,183 $63,544 $131,659 EMBRE $59,968 $211,250 $95,583 $161,172 $35,047 $106,084 $56,323 $305,362 $23,109 $113,951 $119,536 $129,228 $79,153 $142,101 $176,430- $166,125 $58,018 $234,689 $30,096 $87,032 $97,645 $223,639 Pivot Table Questions 0 8 F Merchandise $146,037 $499,116 $351,629 $271,826 $618,267 $407,975 $561,292 $448,070 $615,814 $251,396 $137,967 Type Sporting Event Sporting Event Sporting Event Sporting Event Concert Sporting Event Sporting Event Concert $695,621 Concert $569,552 Concert $244,557 Sporting Event $103,798 Other Event $457,253 Sporting Event Sporting Event Other Event Sporting Event $419,527 Sporting Event $117,310. Sporting Event $774,860 Concert $454,210 Sporting Event Concert $987,087 $121,160 Other Event Bakery $581,642 Sporting Event $283,843 Sporting Event $292,336 Sporting Event $893,927 Concert C + W File A1 2 Instruction Task Submit View Sheet Guide Report Show Instructions Automatic Scoring A 29 2919 2921 2924 Fair 3121 SEN-06 51 30 31 32 33 34 35 36 4122 Fair 37 4416 38 4422 39 4520 40 4719 Fair 41 4819 Fair 42 4820 Good 43 4919 Fair 44 5114 Good 45 5119 Fair 46 5125 Fair 47 5218 Fair 48 5223 Good 49 5317 Fair 50 5323 Excellent 5425 Good 5520 Good 5619 Good 5620 Good 5714 Fair 5720 Poor 5821 Fair 52 53 54 Home 55 56 57 Ready 115 35 Insert Page Layout Excellent Good O Good 3216 Good 3920 Poor 3922 Excellent Excellent Fair Good Event Profit ✓fx 20999 15443 10848 19953 18903 9074 20498 11573 21249. 11257 19235 13275 13378 18449 13725 15710 11382 12437 14828 18468 13952 20051 19744 19205 16309 15234 14435 9173 14906 Past Events Type here to search Formulas Data Randomly Selected Past Events $97,645 $223,639 $123,390 $67,149 $91,983 $147,632 $34,390 $53,705 $87,608 $170,417 $99,624 $25,006 $19,382 $13,512 $44,093 $80,291 $41,946 $61,235 $18,656 $46,856 Review View $237,822 $172,483 $265,774 $157,840 $123,769 $304,190 $63,420 $320,010 $150,394 $181,194 $96,775 $206,423 $216,407 $71,233 $217,426 $127,934 $125,241 $84,520 $71,102 $117,641 $120,406 $113,011 $120,707 $201,312 $138,405 $154,990 $129,826 $273,863 $62,953 $137,159 $116,845 $117,149. $80,980 $200,502 $41,737 $63,477 $71,698 $86,008 PivotTable Questions 0 $893,927 $177,749 $72,356 $906,265 $504,710 BI $288,281 $565,335 $213,175 Help Bakery Concert Sporting Event Sporting Event Sporting Event Sporting Event $127,919 Other Event $413,132 $483,760 $300,679 $526,424 $126,007 $199,562 $238,321 $195,087 $126,236 $661,625 Concert Assignmen O G Concert Other Event Other Event $378,963 Sporting Event $602,308 Concert $555,012 Sporting Event $560,927 Sporting Event $701,367 Sporting Event $492,043 Sporting Event Concert Sporting Event Sporting Event Sporting Event Concert Other Event Sporting Event Sporting Event Sporting Event Other Event $609,512 $467,550 Sporting Event $309,680 Sporting Event $498,606 Sporting Event W E File 57 58 Home E 60 Instruction Task Submit View Sheet Guide Report Show Instructions Automatic Scoring A1 85 8319 Ready AND 3 A 5821 Fair 5916 Good 59 5927 Fair 60 6216 Poor 61 6423 Fair 62 6522 Fair 63 6619 Poor 64 6625 Excellent 65 6627 Fair 66 6712 Good 67 6722 Fair 68 6820 Poor Fair Fair Fair Good Good SE Insert Page Layout 69 6821 70 6917 71 7120 72 7215 73 7216 74 7320 Fair 75 7618 Good 76 7714 Good 77 7821 Fair 78 7822 Good 7919 Fair 8213 Fair O B 79 80 81 8216 Good 82 8220 Good 83 8221 Poor 84 8223 Good Fair Event Profit X M 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 12817 Past Events Type here to search Formulas Data Review View Randomly Selected Past Events $71,698 $77,741 $27,551 $37,476 $48,709 $64,105 $31,347 $86,008 $198,932 $498,606 $812,678 $285,276 $339,193 $72,949 Other Event $77,785 Other Event $175,276 $196,529 $133,578 $911,138 $95,270 $90,874 $149,913 $152,655 $137,232 $817,062 Concert $10,899 $84,339 $176,931 Sporting Event $25,191 $99,987 $352,499 Concert $88,467 $112,571 $48,718 Concert $54,617 $207,804 $81,145 Other Event $57,176 $157,721 $538,037 Concert $144,707 $288,479 $364,198 Sporting Event $18,462 $124,325 $364,675 Sporting Event $25,920 $188,371 $426,503 Sporting Event $20,225 $102,398 $162,913 Other Event $30,051 $247,735 $389,727 $60,646 $109,213 $462,878 $106,235 $121,695 $696,277 Sporting Event $69,169 $215,445 $84,335, Other Event $48,593 $75,193 $70,357 Other Event $129,830 $136,289 $620,669 Concert $100,564 $176,309 $488,798 Sporting Event $57,761 $114,291 $181,994 Sporting Event $56,092 $135,997 $543,458 Sporting Event $420,526 Sporting Event $21,789 $122,787 PivotTable Questions $82,871 $131,947 $74,321 $62,296 $87,770 0 Bakery II (+) Help Assignment Sporting Event Concert Sporting Event Concert Sporting Event Concert Other Event Sporting Event Concert W N H File A1 Home struction Task Submit View Sheet Guide Report Show Instructions Automatic Scoring Ready H A 8319 Fair 8418 8523 Fair 8524 8815 Insert Page Layout 5 6 7 8 Excellent 9 Fair 0 8823 Fair 1 8914 Excellent 2 8916 Good 3 8921 Good 4 9127 Poor 5 9220 Good 6 9421 Fair 7 9423 Good 8 9515 Good 9 9520 Good 00 9717 Poor 01 9720 Poor 02 9818 Fair 03 9922 Poor 04 OS 06 07 08 09 10 11 12 13 Good B fr С 12817 17723 13497 21799 13596 11375 21663 18469 16811 8414 15448 13303 17784 19791 18932 9893 8276 11471 8100 Event Profit Past Events Type here to search Formulas Data Randomly Selected Past Events D $21,789 $60,967 $91,375 $66,051 $268,346 $52,073 $183,002 $43,111 $81,900 $158,140 $197,567 $83,665 $197,249 $125,746 $96,495 $53,261 $105,973 $113,673 $189,701 $64,520 $89,396 $157,744 $252,355 $48,290 $252,929 $67,587 $286,820 $40,363 $77,363 $23,504 $59,835 $71,694 $163,462 $51,516 $123,444 Review PivotTable Questions $122,787 $420,526 $97,122 $97,831 $70,859 $491,291 $152,811 $525,078 $282,783 $872,152 $472,437 Sporting Event $383,795 Sporting Event Sporting Event Other Event Other Event $72,832 Concert $390,872 Sporting Event $112,077 Other Event $387,113 Concert $279,067 Sporting Event $166,674 Sporting Event $360,774 Concert 0 View Bakery 6: $162,306 $194,645 $112,942 (+) Help Assignment W Sporting Event Other Event Sporting Event Other Event Concert Sporting Event Concert

Answer rating: 100% (QA)

Answer 1 Attendance Type 15334 10643 16884 14350 21016 21538 20999 19953 13378 14828 13952 15234 19484 8675 21934 19774 8627 13029 12217 12453 16560 13596 21663 17784 9893 8100 Concert Concert ... View the full answer