budget committee for formal Valentines Day Ball

You are on the budget committee for  formal Valentines Day Ball at your university. The ball includes dinner and dancing. Your committee prepared a tentative budget outlining income and expenses in the attached Dance.xlsxfile.

The primary sources of income are contributions from student organizations and ticket prices. Expenses include the actual cost of the dinner, facilities, parking, and other costs at a luxurious hotel in the city. Your goal is to balance the income and expenses, decide on the most appropriate ticket price per student, and ensure your budget falls within the limitations you must work with.

a)      Currently, the estimated budget has a deficit. Use Goal Seek to achieve a $0 balance by changing the ticket price per person. Answer question 1 on the Q&A worksheet.

b)     You believe that between 200 and 500 students will attend. Because the ticket revenue, chair setup, catering cost, and valet parking expenses are dependent on the number of students, you decide to create a one-variable data table to compare the budget effects based on different number of students attending.

Complete the one-variable data table. There are four columns: # Attend, Total Revenue, Total Expenses, and Balance. In the first column, enter the series of substitution values ranging from 200 to 500 at increments of 20 students. Enter references to the total revenue, total expenses, and balance formulas in the correct location for the one-variable data table. Complete the one-variable data table and answer questions 2 through 4 on the Q&A worksheet.

c)      Now you want to compare the balance of different combinations of attendees and ticket prices per person using a two-variable data table. Using the same series of substitution values for # Attend from the one-variable data table.

Enter the series of substitution values for ticket price from $50 to $100 at $10 increments. Enter the reference to the total income formula in the correct location for the two variable data table. Complete the two-variable data table and answer questions 5 and 6 on the Q&A worksheet. Question 6 requires three combinations to list.

d)    You negotiated different cost per meal and ballroom rental rates based on 500, 400, 300, or 200 attendees. You estimated tentative ticket prices per attendee. You use Scenario Manager to help you decide the target number of attendees. Create a first scenario named 500 Attend, using the number of attendees, meal cost per person, ticket price per person, and ballroom rental variables as the changing cells.

Enter these values for the first scenario: 500, 15.95, 75 and 12500. Create a second scenario named 400 Attend, using the same changing cells, entering these values for the second scenario: 400, 17.95, 85, and 12500.

Create a third scenario named 300 Attend, suing the same changing cells, entering these values for the third scenario: 300, 19.95, 90, and 11995. Create a fourth scenario named 200 Attend, using the same changing cells, entering these values for the fourth scenario: 200, 22.95, 95, and 11995. Generate a scenario summary report using the total revenue, total expenses, and balance as the results, and then answer question 7 through 9 on the Q&A worksheet.

e)      Save the workbook.

Valentine’s Day Ball

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

400

50%

2.00

19.95

20.95

75.00

 

Limitations

Maximum Attendees

Maximum Parking Stalls

Minimum Ticket Price

Maximum Ticket Price

$500

240

50.00

100.00

Income

Student Club Contributions

$8,500

Ticket Revenue

Total Income

Expenses

Advertising

Ballroom Rental

Chairs/Table Setup

Valet Parking

Decorations

DJ Cost

Cleanup Costs

Meal Cost

Contingency

Total Expenses

Balance

30,000

$38,500

$ 44,090

$(5,590)

3,345

12,500

800

3,990

4,575

3,000

2,500

8,380

 

5,000

One-Variable Data Table: Attendees

# Attend Two-Variable Data Table: Attendees and Price Per Ticket

Question

1) What is the ticket price per person to balance the initial budget using Goal Seek?

2) For the one-variable data table, how many attendees creates the largest deficit?

3) For the one-variable data table, what is the largest deficit?

4) For the one-variable data table, how many attendees creates a break-even point?

5) For the two-variable data table, what ticket prices do not produce a break-even point?

6) For the two-variable data table, list the combination of ticket prices and attendees?

7) For the scenario summary, which scenario provides the highest positive balance and by what

amount?

8) For the scenario summary, which scenario provides a negative balance? What is the balance?

9) Is this balance close enough that you might achieve break even? How is this possible?

Get accounting assignment help

Assignment Writing Pro
Order NOW to get 15% Discount!
Pages (550 words)
Approximate price: -

Why Choose Us

Premium Quality Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

Professional Assignment Writers

We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.

Free Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.

Timely Delivery and 100% Money-Back-Guarantee

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.

Customer Support 24/7

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

We'll send you the first draft for approval by at
Total price:
$0.00

How it works?

Fill in the order form and provide all details of your assignment:

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Assignment Writing Help Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.

Essays

Essay Writing Services

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

Admissions

Admission and Business Papers

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

Editing

Editing and Proofreading

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

Coursework

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.