Need help with the following excel functions.... Thank you.

Complete the data table with cell B5 as the column input cell, showing the total revenue, total costs, and balance under different numbers of attendees.

Create a CVP chart of the Total Revenue and Total Costs values using the data from the range G5:I16, the one-variable table, and then format the chart as follows:

a.Move and resize the chart to cover the range G18:J29

.b.Change the chart title to CVP Analysis.

c.Change the scale of the horizontal axis to go from 0 to 500 in 100-unit increments.

Brenda wants to investigate the impact of different registration fees and number of attendees on the seminar balance. In cell L6, display the value of cell B29 formatted to display the text Attendees. In the range L7:L16, enter attendee values ranging from 50 to 500 in increments of 50. In the range M6:P6, enter registration fees of $200, $300, $400, and $500.

Create a two-variable data table in the range L6:P16, using cell B6 as the row input cell and cell B5 as the column input cell.16.Create a scatter chart with straight lines of the data in the range L7:P16, and then make the fol-lowing changes to the chart:

a).Move and resize the chart to cover the range L18:P29.

b).Change the chart title to Balance Analysis.

c).Change the name of the four data series to match the registration fee values in cells M6, N6, O6, and P6.

d).Change the scale of the horizontal axis to go from 0 to 500 in 100-unit increments.

Create scenarios for the other possible values for the input cells listed in Figure 848.

Create a scenario summary report of the Seminar 1, Seminar 2, and Seminar 3 scenarios, showing the cost per person and balance from each seminar as the result. Move the sheet to the end of the workbook.

Show the results of Scenario 3 in the Budget worksheet.

Experience has taught Brenda that as the registration fee for the seminar increases, the number of attendees willing to pay decreases. Based data from other seminars, Brenda has defined a relationship between attendance and registration fee, shown in the range R4:X21 on the Budget worksheet. In cell B5 of the Budget worksheet, change the number of attendees from a constant value to the following formula that projects the number of attendees for a given registration fee based on the value in cell B6. (Hint: Look at the formulas in the range S6:S21 to learn how to translate this equation into an Excel formula.)

Attendees = 1000 e(fee/75)

Use Solver to determine the registration fee in cell B6 that will maximize the balance value in cell B29 with the constraint that the registration fee should be an integer. Run Solver with an initial registration fee of $1,000.22.Save the workbook, and then close it.

AutoSave On NP_EX_8_Seminar - Saved - Search (Alt+Q) Jay File Home Insert Draw Page Layout Formulas Data Review View Help Comments Share Queries & Connections ? Solver 16 A Y WA ra A1 A2 Sort EE Outline Stocks Clear Ye Reapply Filter Advanced Sort & Filter Get Data e Get & Transform Data Currencies Properties Refresh All Edit Links Queries & Connections Text to Columns Es G What If Forecast Analysis Sheet Forecast Data Types Data Tools Analyze G . K L M N O P Q R S T U W Balance Analysis Fee Fee Balance ($48.950) Break-Even Analysis Attendees Total Revenue Total Costs 200 $100,000 $51,050 50 100 150 200 250 300 350 400 4501 500 G4 Xv fx Break-Even Analysis A B C D E F F 3 4 Revenue . Room Fees 5 Attendees 200 Attendee Charge 6 Registration Fee per Attendee $500 $ 0 $1,500 7 Total Revenue $100,000 100 $1,750 8 200 $1,800 9 Variable Costs 300 $1,850 $ 10 Training Materials $150 400 $1,9001 11 Supplemental Materials $75 500 $2,000 12 Total Variable Costs $11,250 13 Meal Catering 14 Fixed Costs Attendee: Charge (per person) 15 Computing Costs $1,400 0 $150 16 Speaker Fees $2,400 100 $140 17 Speakers Travel $2,000 200 $130 18 Speakers Lodging $950 300 $1201 19 Miscellaneous $5,000 400 $110 20 Total Fixed Costs $11,750 500 $100 21 22 Mixed Costs Seminar Support 23 Room Costs $1,800 Attendee Charge 24 Meal Costs $26,000 0 $150 25 Support Costs $250 100 $200 26 Total Mixed Costs $28,050 2001 $250 27 300 $300 28 Cost per Attendee $255 400 $350 29 Balance ($48,950) 500 $500 30 31 32 33 34 Documentation Budget Effect of the Registration Fee on Attendance Attendance $0) 1.000 $100 819 1,200 $200 670 1,000 $300 549 $400 449 SDD $500 368 $600 301 500 $700 247 $800 202 400 $900 165 $1,000 135 200 $1,100 111 $1.200 91 $1,300 74 $0 $5DD $1,000 $1,400 61 Registration Fee $1,500 50 Ready 3 70% Type here to search | 0 0 a X 6:14 PM 11/22/2021 38F F 69 Figure 8-48 Seminar what-if scenarios Changing Cell Attendees Registration Fee Training Materials Supplemental Materials Computing Costs Speaker Fees Speakers Travel Speakers Lodging Miscellaneous Seminar 1 200 $500 $150 $75 $1,400 $2,400 $2,000 $950 $5,000 Seminar 2 300 $400 $175 $100 $1,200 $2,800 $2,200 $1,200 $4,500 Seminar 3 150 $600 $135 $55 $1,600 $2,600 $1,600 $1,000 $4,800 AutoSave On NP_EX_8_Seminar - Saved - Search (Alt+Q) Jay File Home Insert Draw Page Layout Formulas Data Review View Help Comments Share Queries & Connections ? Solver 16 A Y WA ra A1 A2 Sort EE Outline Stocks Clear Ye Reapply Filter Advanced Sort & Filter Get Data e Get & Transform Data Currencies Properties Refresh All Edit Links Queries & Connections Text to Columns Es G What If Forecast Analysis Sheet Forecast Data Types Data Tools Analyze G . K L M N O P Q R S T U W Balance Analysis Fee Fee Balance ($48.950) Break-Even Analysis Attendees Total Revenue Total Costs 200 $100,000 $51,050 50 100 150 200 250 300 350 400 4501 500 G4 Xv fx Break-Even Analysis A B C D E F F 3 4 Revenue . Room Fees 5 Attendees 200 Attendee Charge 6 Registration Fee per Attendee $500 $ 0 $1,500 7 Total Revenue $100,000 100 $1,750 8 200 $1,800 9 Variable Costs 300 $1,850 $ 10 Training Materials $150 400 $1,9001 11 Supplemental Materials $75 500 $2,000 12 Total Variable Costs $11,250 13 Meal Catering 14 Fixed Costs Attendee: Charge (per person) 15 Computing Costs $1,400 0 $150 16 Speaker Fees $2,400 100 $140 17 Speakers Travel $2,000 200 $130 18 Speakers Lodging $950 300 $1201 19 Miscellaneous $5,000 400 $110 20 Total Fixed Costs $11,750 500 $100 21 22 Mixed Costs Seminar Support 23 Room Costs $1,800 Attendee Charge 24 Meal Costs $26,000 0 $150 25 Support Costs $250 100 $200 26 Total Mixed Costs $28,050 2001 $250 27 300 $300 28 Cost per Attendee $255 400 $350 29 Balance ($48,950) 500 $500 30 31 32 33 34 Documentation Budget Effect of the Registration Fee on Attendance Attendance $0) 1.000 $100 819 1,200 $200 670 1,000 $300 549 $400 449 SDD $500 368 $600 301 500 $700 247 $800 202 400 $900 165 $1,000 135 200 $1,100 111 $1.200 91 $1,300 74 $0 $5DD $1,000 $1,400 61 Registration Fee $1,500 50 Ready 3 70% Type here to search | 0 0 a X 6:14 PM 11/22/2021 38F F 69 Figure 8-48 Seminar what-if scenarios Changing Cell Attendees Registration Fee Training Materials Supplemental Materials Computing Costs Speaker Fees Speakers Travel Speakers Lodging Miscellaneous Seminar 1 200 $500 $150 $75 $1,400 $2,400 $2,000 $950 $5,000 Seminar 2 300 $400 $175 $100 $1,200 $2,800 $2,200 $1,200 $4,500 Seminar 3 150 $600 $135 $55 $1,600 $2,600 $1,600 $1,000 $4,800