View the step-by-step solution to:

# Valentine's Day Ball One-Variable Data Table: Attendees # Attend Input Section No. of Attendees % Attendees Using Valet Cost per Chair Setup Valet...

On the Budget worksheet, perform a goal seek analysis to determine what the ticket price in cell B9 would need to be if you wanted the balance in cell C34 to be \$0. Accept the solution.

Beginning in cell E3, complete the series of substitution values ranging from 300 to 500 in increments of 20 students vertically down column E.

In cell F2, enter a reference to the total income formula from column C. In cell G2, enter a reference to the total expenses formula from column C. And in cell H2, enter a reference to the balance formula from column C.

Create a one-variable data table for the range E2:H13 using the number of attendees as the Column input cell. Format the results with Accounting Number Format with two decimal places.

Apply custom number formats to make the formula references in the range F2:H2 appear as Revenue, Expenses, and Balance, respectively.

Create a two-variable data table in the range E17:I28, using the Ticket Price per Person as the Row input cell and the No. of Attendees as the Column input cell. Format the results with Accounting Number Format with two decimal places.

Apply a Red, Accent 2, Lighter 80% fill color to the three cells closest to break-even without creating a deficit.

Create a scenario named 500 Attend using the number of attendees, the caterer's meal cost per person, ticket price per person, and ballroom rental variables as the changing cells. Uncheck Prevent changes to change cell values. Enter these values for the scenario: 500, 15.95, 75, and 12500, respectively. Do not show the results.

Create a second scenario named 300 Attend and using the same changing cells. Uncheck Prevent changes to change cell values. Enter these values for the scenario: 300, 19.95, 90, and 11995, respectively. Do not show the results.

Create a third scenario named 200 Attend and using the same changing cells. Uncheck Prevent changes to change cell values. Enter these values for the scenario: 200, 22.95, 95, and 11995, respectively. Do not show the results.

Create a scenario summary report using the total revenue, total expenses, and balance as the results.
Load the Solver add-in if it is not already loaded. In cell C34 of the Budget worksheet, use Solver to find the highest balance possible by changing the number of attendees and the ticket price per person. Using the Limitations section of the spreadsheet model, add the following constraints:
Number of attendees
Ticket price per person (this will require two constraints based on the minimum and maximum ticket prices)

Keep the Solver dialog box open.

Add the following constraints to the Solver Parameters:

Valet parking expense (less than or equal to the product of the number of parking stalls and the valet price per vehicle)
Use the number of attendees as the integer constraint

Solve the problem, but keep the original values in the Budget worksheet. Create an Answer report.
Ensure that the worksheets are correctly named and placed in the following order in the workbook: Scenario Summary, Answer Report 1, Budget. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
1 page

Valentine's Day Ball One-Variable Data Table: Attendees
# Attend Input Section
No. of Attendees
% Attendees Using Valet
Cost per Chair Setup
Valet Parking per Car
Caterer's Meal Cost per Person
Ticket Price per Person
Limitations
Maximum Attendees
Maximum Parking Stalls
Minimum Ticket Price
Maximum Ticket Price 400
50%
\$2.00
\$19.95
\$20.95
\$88.98
500
240
\$50.00
\$100.00 Income
Student Club Contributions
Ticket Revenue
Total Income \$8,500
35,590 Expenses
Ballroom Rental
Chairs/Table Setup
Valet Parking
Decorations
DJ Cost
Cleanup Costs
Meal Cost
Contingency
Total Expenses \$3,345
12,500
800
3,990
4,575
3,000
2,500
8,380
5,000 Balance \$44,090 \$44,090
\$- Two-Variable Data Table: Attendees and Price Per Ticket
# Attend
\$70
\$80
\$90
\$100
300
320
340
360
380
400
420
440
460
480
500

## This question was asked on Mar 10, 2013.

### Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors and customizable flashcards—available anywhere, anytime.

### -

Educational Resources
• ### -

Study Documents

Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access or to earn money with our Marketplace.

Browse Documents