Apply: Case Problem 1 Data File needed for this Case Problem: Seminar.xlsx Paget Integrated Solutions Constance Paget

Fantastic news! We've Found the answer you've been seeking!

Question:

Apply: Case Problem 1

Data File needed for this Case Problem: Seminar.xlsx

Paget Integrated Solutions Constance Paget is the owner of Paget Integrated Solutions, a Web technology consulting firm based in Seattle, Washington. Paget is planning a seminar on Web applications with a popular guest speaker to take place in Houston, Texas, next spring. She wants your help in generating a budget for the seminar that will incorporate the fixed, variable, and mixed costs to determine under what conditions the seminar will be profitable for her company. Complete the following:

1
Open the Seminar workbook located in the Excel10 > Case1 folder included with your Data Files, and then save the workbook as Seminar Budget in the location specified by your instructor.
2
In the Documentation worksheet, enter your name and the date.
3
Constance hopes to attract 100 people to the seminar at a cost of $120 per person. In the Budget worksheet, enter these values in the range B5:B6. Calculate the total revenue in cell B7.
4
Each participant will receive training materials worth $35 and a goodie bag worth $15. Enter these values into the range B10:B11. Calculate the total variable costs in cell B12 based on the number of attendees entered in cell B5.
5
There are several fixed costs associated with the seminar. Providing an Internet hotspot for the attendees will cost $50. The seminar speaker’s fee is $900 plus a $500 travel allowance and a $140 lodging stipend. Constance estimates $350 in miscellaneous expenses. Enter these values in the range B15:B19. Calculate the total fixed costs in cell B20.
6
The company must rent a conference room large enough to accommodate the number of attendees. The lookup table in the range D5:E10 contains the room charges for seminars of 0 to 50 people, 50 to 100 people, 100 to 150 people, 150 to 200 people, and beyond 200 people. For example, a room that fits 0 to 50 people will cost the company $400. In cell B23, calculate the room rental cost by looking up the room rental fee based on the number of attendees to the seminar. (Hint: Use the VLOOKUP function with an approximate match lookup.)
7
The more people attending the seminar, the less the hotel will charge per person to cater the seminar meals. In cell B24, calculate the total catering charge by using the lookup table in range D13:E16 to determine the cost per person, and then multiply that value by the number of attendees.
8
The company also needs to pay for the hotel support staff. The larger the seminar, the higher the support staff fee. The lookup table in the range D19:E23 contains the staff fees for groups of different sizes. For example, a seminar of 0 to 50 people will incur a $60 staff fee. In cell B25, calculate the support staff charge for the number of attendees to the seminar.
9
In cell B26, calculate the total mixed costs from the room rental, meal catering, and support staff. In cell B28, calculate the balance from the conference by subtracting the sum of the variable, fixed, and mixed costs from the total revenue.
10
Use Goal Seek to determine what number of attendees will result in a balance of $0. Enter your conclusion in the merged cell A30, indicating how many attendees are needed to break even for 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. In cell G6, display the value of cell B5. In cell H6, display the value of B7. In cell I6, display the sum of cells B12, B20, and B26. In cell J6, display the value of cell B28. In the range G7:G21, enter the number of possible attendees ranging from 20 to 300 in increments of 20. Complete the data table, showing the total revenue, total costs, and balance for each seminar size.