Goal Seek, One Variable, Two Variable Data Table & Scenarios You are the event manager for Graduation Dinner at the campus and, you created a list of all the spenses & income from ticket sale and donations. You found out that college makes a loss of $2,800. You have been asked to arrange the event in such a way that college breaks even (no profit & no loss). Go to Graduation sheet and use an Excel function to find out how much should the Ticket Price be if the result is to show a zero balance for Profit/Loss (Break Even). 1. /1 2 You know that the profit depends very much on the number of attendees. Create a one variable data table based on the number of attendees between 300 and 600. Start in cell DS and complete the series of substitution values ranging from 300 to 600 at increments (or steps) of 23 attendees vertically down column D (Series of numbers will not stop at 600 exactly, but that is okay). Enter the references to the "Total Income". "Total Expenses" and "Profit/Loss" in the one-variable data table in their appropriate cells within the one-variable data table. 3. 13 Complete the one-variable data table and then format the results with Accounting Number Format with two decimal places (If losses show up in brackets or a negative number, that's okay). In cell D4, Type # of Attendees and make sure the reference cells display as following: In E4, Income in F4, Expenses, in G4, Profit/Loss Bold & center all the column headings. /3 Copy the number of attendees from the one-variable data table (ie the variable), and paste them in the two-variable data table starting in cell 15. You have decided to create a two-variable data table as well and, you will use the Ticket Price as your second variable. You have been told that the maximum amount that can be charged for the tickets is $65. Complete the series of substitution values for ticket price from $50 to $65 at $3 increments and enter the reference to Profit/Loss for the two-variable data table 5. Complete the two-variable data table and format the results with Accounting Number Format with two decimal places. Apply a custom number format to make the formula reference appear as a descriptive column heading "Attendees/Price", bold and center the heading. 12 Goal Seek, One Variable, Two Variable Data Table & Scenarios You are the event manager for Graduation Dinner at the campus and, you created a list of all the spenses & income from ticket sale and donations. You found out that college makes a loss of $2,800. You have been asked to arrange the event in such a way that college breaks even (no profit & no loss). Go to Graduation sheet and use an Excel function to find out how much should the Ticket Price be if the result is to show a zero balance for Profit/Loss (Break Even). 1. /1 2 You know that the profit depends very much on the number of attendees. Create a one variable data table based on the number of attendees between 300 and 600. Start in cell DS and complete the series of substitution values ranging from 300 to 600 at increments (or steps) of 23 attendees vertically down column D (Series of numbers will not stop at 600 exactly, but that is okay). Enter the references to the "Total Income". "Total Expenses" and "Profit/Loss" in the one-variable data table in their appropriate cells within the one-variable data table. 3. 13 Complete the one-variable data table and then format the results with Accounting Number Format with two decimal places (If losses show up in brackets or a negative number, that's okay). In cell D4, Type # of Attendees and make sure the reference cells display as following: In E4, Income in F4, Expenses, in G4, Profit/Loss Bold & center all the column headings. /3 Copy the number of attendees from the one-variable data table (ie the variable), and paste them in the two-variable data table starting in cell 15. You have decided to create a two-variable data table as well and, you will use the Ticket Price as your second variable. You have been told that the maximum amount that can be charged for the tickets is $65. Complete the series of substitution values for ticket price from $50 to $65 at $3 increments and enter the reference to Profit/Loss for the two-variable data table 5. Complete the two-variable data table and format the results with Accounting Number Format with two decimal places. Apply a custom number format to make the formula reference appear as a descriptive column heading "Attendees/Price", bold and center the heading. 12
