I am having trouble creating the formulas for number 11 and 13.
Excel File Edit View Insert Format Tools Data Window Help Seminar Budget.xlsx Q Search Sheet cut AutoSum A | Fill Calibri Light Wrap Text Merge & Center, $ , % , .0.0 Conditional Format Cell , | 11 . A Av 1 . Currency 1 u ,-, d , A, _ Sort & Filter Insert Delete Format Format B I Formatting as Table Styles 121 P Q Paget Integrated Solutions Seminar Budget Projections Room Rental Break-Even Analysis Balance Analysis S Number of Attendees 6 Registration Fee 7 Total Revenue 100 $120 $12,000 Attendees Total Revenue 12,000 $2,400 $4,800 Persans Total Costs Reristration Fee Attendan 100 $5,700 So 525 550 $19,640 100 $750 Variable Costs 150 9,600 12,000 $14,400 16,800 19,200 21,600 24,000 26,400 $28,800 31,200 33,600 36,000 10 Training Material Cost lper person) 11 Goodie Bag Cost [per persan) 12 Total Variable Costs 13 $15 $5,000 100 120 125 $150 $19,640 Meal Catering PersonsChar Fixed Costs 15 Internet Hotspot 16 Speaker Fee 17 Speaker Travel 18 Speaker Lodging 19 Miscel lancous 20 Total Fixrd Costs 21 $200 S50 $250 5275 5300 100 200 $500 $140 220 Support Staff 260 $1,940 60 300 Mixed Costs 100 $100 23 Room Rental 24 Meal Catering 25 Support Staff 26 Total Mixed Costs 27 28 Balance 29 30 31 32 $1,000 $1,700 $10,000 $12,700 200 $5,700 -21.2766 34 Budget + Ready 120% Case Problem 1 Data File needed for this Case Problem: Seminar.xisx Paget Integrated Solutions Constance Paget is the owner of Paget Integrated Solutions, a Web a 4 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 > Casel folder included with your Data Files, and then save the workbook as Seminar Budget in the location specified by your 2. In the Documentation worksheer, 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 atendees entered in cell B5. Copyright 2017 Cenage LearningAl ghts seed May not be copled, scanned, or duplicated, In whole or le part WCN 83-200-203 Excel | Module 10 Performing What-If Analyses 5. There are several fixed costs associated with the seminar. Providing an Intenet hotspot for the attendees will cost $50. The seminar speakers 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 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. (Hin Use the VLOOKUP function with an approximate match lookup.) in the range DS:E10 contains the room d 7. The more people attendding the seminar, the less the hotel will charge per person to cater the seminar meals. In cell 824, 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 atendees 8. The company also needs to pary for the hotel suppot saff. The larger the seminar, the higher the suppont 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 coss from the room rental, meal catering, and suppont staff. In cell 828, 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 attenders will result in a balance of $O. 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 BS 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 16, display the sum of cells 812, B20, and B26. In cell 16, display the value of cell 828. 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 12. Create a CVP chart of the Total Revenue and Total Costs values in the one-variable table. Forma the table so that it is easy to read, and resize it to cover the range G23:134. 13. Constance wants to invesrigate the impact of different registration fees and number of attendees on the seminar balance. In cell L6, display the value of cell 828, formatted to display the text Attendees". In the range L71.21, enter attendee values ranging from 20 to 30n increments of 20. In the range M6:P6, enter registration fees of $75 to $150 in increments of $25. In the range L6:P21, complete the two-variable data table using the number of attendees and the registration fee as the input values. 14. Create a chart of the balances versus the number of attendees with each registration fee displayed as a separate line. Format the chart so that it is easy to interpret, and resize it to cover the range L23:P34 of the worksheet. 15. Create scenarios lor the other possible values for the input cells listed in Figure 10-48. Figure 10-48 Seminar what-if scenarios Changing Cell 100 $120 535 $15 150 $85 $30 $10 $750 75 Registrotion Fee Training Material Cost Goode Bag Cost Speaker Fee Speaker Travel Speaker Lodging $40 $20 $950 $140 $125 $120 Module 10 Performing What-If Analyses | Excel EX 655 16. Create a scenario summary report of the Seminar 1, Seminar 2, and Seminar 3 scenarios, showing the balance from each seminar as the result. Move the sheet to the end of the workbook 17. Constance knows that as the registration fee for the seminar increases, the number of attendees willing to pay decreases, as shown in the range T5:X18 in 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. (Hine: Look at the formulas in the range S6:S18 to leam how to translate this equation into an Excel formula.) ra e atrendees 600 x e 18. Use Solver to determine the registration fee in cell B6 that will maximize the balance value in cell B28 with the constraint that the registration fee should be an integer. Run Solver with arn initial registration fee of $100. 19. Add your condlusion regarding the registration fee that results in the highest ending balance to the company to the merged cell A30. Be sure to include the registration fee, the number of attendees, and the resulting maximum balance in your summary. 20 Save the workbook, and then close it. Excel File Edit View Insert Format Tools Data Window Help Seminar Budget.xlsx Q Search Sheet cut AutoSum A | Fill Calibri Light Wrap Text Merge & Center, $ , % , .0.0 Conditional Format Cell , | 11 . A Av 1 . Currency 1 u ,-, d , A, _ Sort & Filter Insert Delete Format Format B I Formatting as Table Styles 121 P Q Paget Integrated Solutions Seminar Budget Projections Room Rental Break-Even Analysis Balance Analysis S Number of Attendees 6 Registration Fee 7 Total Revenue 100 $120 $12,000 Attendees Total Revenue 12,000 $2,400 $4,800 Persans Total Costs Reristration Fee Attendan 100 $5,700 So 525 550 $19,640 100 $750 Variable Costs 150 9,600 12,000 $14,400 16,800 19,200 21,600 24,000 26,400 $28,800 31,200 33,600 36,000 10 Training Material Cost lper person) 11 Goodie Bag Cost [per persan) 12 Total Variable Costs 13 $15 $5,000 100 120 125 $150 $19,640 Meal Catering PersonsChar Fixed Costs 15 Internet Hotspot 16 Speaker Fee 17 Speaker Travel 18 Speaker Lodging 19 Miscel lancous 20 Total Fixrd Costs 21 $200 S50 $250 5275 5300 100 200 $500 $140 220 Support Staff 260 $1,940 60 300 Mixed Costs 100 $100 23 Room Rental 24 Meal Catering 25 Support Staff 26 Total Mixed Costs 27 28 Balance 29 30 31 32 $1,000 $1,700 $10,000 $12,700 200 $5,700 -21.2766 34 Budget + Ready 120% Case Problem 1 Data File needed for this Case Problem: Seminar.xisx Paget Integrated Solutions Constance Paget is the owner of Paget Integrated Solutions, a Web a 4 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 > Casel folder included with your Data Files, and then save the workbook as Seminar Budget in the location specified by your 2. In the Documentation worksheer, 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 atendees entered in cell B5. Copyright 2017 Cenage LearningAl ghts seed May not be copled, scanned, or duplicated, In whole or le part WCN 83-200-203 Excel | Module 10 Performing What-If Analyses 5. There are several fixed costs associated with the seminar. Providing an Intenet hotspot for the attendees will cost $50. The seminar speakers 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 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. (Hin Use the VLOOKUP function with an approximate match lookup.) in the range DS:E10 contains the room d 7. The more people attendding the seminar, the less the hotel will charge per person to cater the seminar meals. In cell 824, 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 atendees 8. The company also needs to pary for the hotel suppot saff. The larger the seminar, the higher the suppont 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 coss from the room rental, meal catering, and suppont staff. In cell 828, 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 attenders will result in a balance of $O. 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 BS 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 16, display the sum of cells 812, B20, and B26. In cell 16, display the value of cell 828. 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 12. Create a CVP chart of the Total Revenue and Total Costs values in the one-variable table. Forma the table so that it is easy to read, and resize it to cover the range G23:134. 13. Constance wants to invesrigate the impact of different registration fees and number of attendees on the seminar balance. In cell L6, display the value of cell 828, formatted to display the text Attendees". In the range L71.21, enter attendee values ranging from 20 to 30n increments of 20. In the range M6:P6, enter registration fees of $75 to $150 in increments of $25. In the range L6:P21, complete the two-variable data table using the number of attendees and the registration fee as the input values. 14. Create a chart of the balances versus the number of attendees with each registration fee displayed as a separate line. Format the chart so that it is easy to interpret, and resize it to cover the range L23:P34 of the worksheet. 15. Create scenarios lor the other possible values for the input cells listed in Figure 10-48. Figure 10-48 Seminar what-if scenarios Changing Cell 100 $120 535 $15 150 $85 $30 $10 $750 75 Registrotion Fee Training Material Cost Goode Bag Cost Speaker Fee Speaker Travel Speaker Lodging $40 $20 $950 $140 $125 $120 Module 10 Performing What-If Analyses | Excel EX 655 16. Create a scenario summary report of the Seminar 1, Seminar 2, and Seminar 3 scenarios, showing the balance from each seminar as the result. Move the sheet to the end of the workbook 17. Constance knows that as the registration fee for the seminar increases, the number of attendees willing to pay decreases, as shown in the range T5:X18 in 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. (Hine: Look at the formulas in the range S6:S18 to leam how to translate this equation into an Excel formula.) ra e atrendees 600 x e 18. Use Solver to determine the registration fee in cell B6 that will maximize the balance value in cell B28 with the constraint that the registration fee should be an integer. Run Solver with arn initial registration fee of $100. 19. Add your condlusion regarding the registration fee that results in the highest ending balance to the company to the merged cell A30. Be sure to include the registration fee, the number of attendees, and the resulting maximum balance in your summary. 20 Save the workbook, and then close it
