I need help with part 13. i keep messing up on it and dont think im doing it corrctly. i am using excel 2016

by looking 30 people nnar,tint u by looking 30 people wille .,to 200 people, and beyond 2,calculate sarmeing the seminar, the less the hotel will charge per person n table in rteds will cost the company $400. t up the room rental fee based on the nmbup) more people attending of attendees to the meals. In cell 82 an approximate match lookup. 4, calculate the total cater per person to cater the charge by using of a 8. The table in range higher company also needs to pay for the hotel support staff. The larger the e the cost per person, and then multiply that value by the the support staff fee. The the lookup erent sizes. For example, a seminar of O to 50 people will incur a s lookup table in the range D19:E23 conta the calculate the support staff charge for the number of attendees to the semina, and sup 9. In cell B26, calculate the total mixed costs from the room rental, meal e range D19:E23 contains the staff fees for go p $60 staff fee. In cell B25, staff. In cell B28, calculate the balance from the conference by subtracting variable, fixed, and mixed costs from the total revenue. the sum of the of $O. E actinging, and conclusion in the merged cell A30, indicating how many attendees are nter your a registration fee of $120. Change the value in cell B5 back to 100 11. Create a one-variable data table of different seminar budgets. need to break even for cell B5. In cell H6, display the value of B7. In cell 16, display the sumer the numbering the B26. In cell 16, display the value of cell B28. In the range G attendees ranging from 20 to 300 in increments of 20. Complete the data table, showie In cell G6, display the value of cells B12, B20, and G7:G21, enter the number of total revenue, total costs, and balance for each seminar size. 12. Create a CVP chart of the Total Revenue and Total Costs values in the on the table so that it is easy to read, and resize it to cover the range G23:)34 13. Constance wants to investigate the impact of different registration fees and nu on the seminar balance. In cell L6, display the value of cell B28, formatted Attendees". In the range L7:L21, enter attendee values ranging from 20 to 20. In the range M6:P6, enter registration fees of $75 to $150 in increm L6:P21, complete the two-variable data table using the number of attendees di splay the text 5. In the range egist istration fee as the input values. 14. Crea te a chart of the balances versus the number of attendees with each registration te displayed as a separate line. Format the chart so that it is easy to interpret, and resize it to cover the range L23:P34 of the worksheet. 15. Create scenarios for the other possible values for the input cells listed in Figure 10-48. 0-48 Seminar what-if scenarios Changing Cell Seminar 1 100 120 $35 Seminar 2 Seminar 3 $85 Registration Fee Training Material Cost Goodle Bag Cost Speaker Fee Speaker Travel Speaker Lodging $140 $40 $20 $950 $500 $120 900 500 140 $750 $420 $125 File Home Insert Draw Page Layout Formula Review View From Access From Web b From Text Sources Connections Query o Recent Sources AEst Links Show Queries ll From Table Resesh Flash Fill From Other Existing New Fiter-, Reapply Sort & Filter All. 'Ledt Links Sort Text to Columesggota widation. l Remove Dupkcates " Relationslip ent Sources Advanced Maage Data Model What-if Farecast Analyss. Get External Data Get & Transform Connections Sheet Break-Even Analysis Balance Analys Attendees Total Revenue Total Costs Balance ration Fee ation Fee Attendan 100 20 40 60 $12,000 $9,490 $3,900 $5,400 $6,820 8,220 $9,490 $10,830 $12,170 $13,660 $15,000 $16,460 $17,800 $19,140 $20,480 $21.820 $23,160 $2.510$75$100$125$150 2,400 $4,800 $7,200 $9,600 $12.000 $14,400 16,800 $19,200 $21,600 $24,000 $26,400 $28,800 $31,200 $33,600 $36,000 $2.510 1500) ($600 $380 $1,380 S2,510 $3,570 $4.630 $5,540 $6,600 $7,540 20 ($2.400 ($1,900 ($1,400 $25 40 ($2,400 (S1400)($400)$600 60 ($2.32 80 (S2.220 $680 | S2.180 100 120 140 $2201 $1,780 $3,780 100 ($1,90) $510 $3,010 $5,510 $1,170 $4,170 $7,170 140 ($1,670 $1,830 $5,330 $8.830 12 160 ($1.66$2.340 $6,340 $10.340 $1,500! S3,000 S7.500 | $12.000 200 (1.460 $3.540 $8,540 $13.540 20 (S1.300 $4.200 $9.700 $15.200 40$4,860 $10.860 $16,860 5,520 $12020 $18.520 180 $13,180 $20,180 $6,840 $14.340 $21.840 180 ( 180 200 220 240 15 $9,660 $10,720 $11,780 $12,840 20 300 O Type here to search From Access From Web From Text Show aeries El From Table From Othes Exicting New Sources Connections Query.o Recent Sources All Edlt Links Flash F Properties Relresh Sot Fiter Get External Data Get & Transform Sort & Fiter A. evenue Number of Attendees Registration Fee Total Revenue Room Rental 100 Persons $120 Break-Even Analysis Attendees Total Revenue Total C S400 $600 $750 $900 $1,000 100 2.510 200 %0 52020 262053 0 $2,400 $4,800 7,200 9,600 12,000 $14400 $3.900 $5,400 Variable Costs Training Material Cost (per person) Goodie Bag Cost (per person) Total Variable Costs 150 200 60 $1,380 $8,220 59,490 $35 $15 $5,000 100 3570 54,630 Meal Catering Persons Charge (per person) 140$1600$12.170 $46 $19.200 13,660 Fixed Costs Internet Hotspot Speaker Fee Speaker Travel Speaker Lodging Miscellaneous Total Fixed Costs 20 $17 $50 $900 $500 $140 $350 $1,940 516,460 17,800 $19,140 $20.480 521.820 $23.160 $24,000 200 220 240 100 $28,800 $31.200 $33,600 59,660 $10.720 511.780 Support Staff Persons Charge Type here to search om Access Show Queries From Table Connections Flash Fill B- Remove Duplicates R& Relationships om Web Properties From Other Existing New Refresh Sources Connections Query- Co Recent SourcesAld Links Colttosce Data Widation What-if For Advanced Manage Dea Model Away . Get External Data Get & Transform Connections Sort & Filter f. Revenue-SUM(Total-Mixed Costs,Total-Fixed-Costs,Total-Variable Costs) =Total C| D $140 350 Persons $28,800 $31,200 $33,600 $36,000$23,160 $9,660 $10,720 $11,780 $12,840 Speaker Lodging Miscellaneous Support Staff 240 260 19,140 O Total Fixed Costs $1,940 300 100 200 $80 $100 $120 $25000 2 Mixed Costs 23 Room Rental 24 Meal Catering 25 Support Staff 26 Total Mixed Costs 27 28 Balance 29 30 52.4 attendies to get a balance of O. $750 $1,700 $2,550 $2,510 $20000 $15,000 $10.000 $5,000 31 32 50 $$,000 s10,000 515,000 520 000 525,000 53 000 533 00 540000 35 DocumentationBudget Type here to search Uraw Page Layout Formulas bubget Excel Data Review View Tell me what you want to do 9 Connections L IA Show Queries Existing Connections New lEE From Table Query-LO Recent Sources Tet Sources. Refresh Sot Fiter Remove Duplicates @ Relationships Get External Data All.-EditLinks Text to Advanced Columns | Get & Transform Connections What if Forecast Sort & Fiter Date Teols Break-Even Analysis Attendees Total Revenue Total Costs Balance Analysis Balance Registration Fee 100 $12,000 $9,490 $3,900 $5,400 6.820 $2,510 $2,510 gistration Fee Attendan 100$125 $150 20$2400 $2.4 ($1.900)| ($1.400 $4,800 $7,200 $ $9,600 $12,000 $14,400 $16,800 $600) 40S1400 ($40 $600 680 $2.180 $1,780 $3,780 ssi S3.010 | S5510 60 $380 $1,380 2.510 $3,570 $4,630 160$19,200$13,660$5,540 $6,600 $7,540 $8,600 $9,660 10,720 60 ($2.320) $100 $125 100 $9,490 100IS1.990 $1,830$1,170$4,17o $7,170 40 1.670)$1.830 5,330 $8.830 $2,340$6,340 $10.340 180| :$1.50 | S3.000 | S750 $12.000 $3,540 $8.540 $13,540 $42001 s9, 7001 $15.200 140 $12,170 160 ($1.660) $21,600 $24,000 $15,000 $16,460 220$26,400$17,800 $19,140 200 (S1,460) 2201