1. Fernanda Oliveira is a financial analyst for Expo Events, a full-service trade show and exhibition company headquartered in Orlando, Florida. Fernanda created a workbook containing details about events scheduled for March this year. She asks you to help analyze the data to determine how the company can increase profits. Switch to the Builders worksheet. Fernanda wants to calculate the revenue, expenses, and profit based on the number of people attending the Builders Trade Show. She has already set up a structure for a one-variable data table. In the range E5:H10, insert a one-variable data table, using the number of attendees (cell C4) as the Column input cell. 2. In cell E14, apply a custom format to display the text Attendees/Fee in place of the cell value. 3. Switch to the GymWear worksheet. Create a Scatter with Straight Lines chart based on the range E4:G14 in the data table titled GymWear - Break-Even Analysis. Resize and move the chart so that it covers the range E15:H27. Remove the chart title. Add Sales and Expenses as the vertical axis title and Attendees as the horizontal axis title. 4. Fernanda wants to change the bounds of the chart to better determine the break-even point. Change the Minimum Bounds of the vertical axis to 120,000 and let the Maximum Bounds change automatically to 210,000. Change the Number format of the vertical axis to Currency with 0 decimal places and $ as the symbol. Change the Minimum Bounds of the horizontal axis to 1,500 and let the Maximum Bounds change automatically to 2,500. 5. One way to increase profits is to run the GymWear trade show for an extra day. Another way is to add exhibits. Fernanda has already created a scenario named Extra Day and wants to create another for adding exhibits. In the Scenario Manager, add a scenario named using the data shown in bold in Table 1. The changing cells are the nonadjacent cells C4, C10, and C13. Table 1: GymWear Scenario Values Scenario Name Add Exhibits Attendees (C4) 2200 Variable_cost_per_attendee (C10) 53 Total_fixed_cost (C13) 52,400 6. One of the values in the Extra Day scenario is incorrect. Edit the Extra Day scenario to use 55,000 as the Total_fixed_cost value. Show the Extra Day scenario, and then close the Scenario Manager. 7. Switch to the Logistics worksheet. Fernanda wants to clarify what each line means in the Scatter with Straight Lines chart in the range E15:J28. Edit the chart series names by using cell F5 as the series name for Series 1. For Series 2, use cell G5 as the series name. For Series 3, use cell H5 as the series name. For Series 4, use cell I5 as the series name. For Series 5, use cell J5 as the series name. 8. Switch to the Service Providers worksheet. Fernanda wants to determine whether partnering with other event services companies would reduce overall expenses for a trade show. Run Solver and set the objective as minimizing the total costs (cell F10). Use the hours provided for each company (range C4:E4) as the changing variable cells. Adjust the hours provided by each company using the following constraints: F4 is equal to 192, the total hours of event services provided. F10 is less than or equal to 48,000, the maximum amount Fernanda can pay to the other event services companies. C4:E4 is less than or equal to 68, the maximum hours provided by a single event services company. C4:E4 should be an Integer. Run Solver, keep the solution, and then return to the Solver Parameters dialog box. Save the model to the range B14:B21. 9. Fernanda wants to document the answer Solver found, including the constraints and a list of the values Solver changed to solve the problem. Solve the model again, this time choosing to produce an Answer report. Use Services Answer Report as the name of the worksheet containing the Answer report. 10. Switch to the March worksheet, which compares the data for the three events the company is handling in March. Fernanda has created three scenarios on this worksheet to compare the current data with one that would increase registration fees and another that would increase expenses by using contractors. Use the Scenario Manager to create a Scenario Summary report that summarizes the effect of the Current, Increase Fee, and Contractors scenarios. Use the range C16:E16 as the result cells. 11. Switch back to the March worksheet. Use the Scenario Manager to create a Scenario PivotTable report for result cells C16:E16. Change the Number format of the value fields (Profit_Attendee_Builders, Profit_Attendee_GymWear, and Profit_Attendee_Logistics) to Currency with 2 decimal places and $ as the symbol. 12. Add a Clustered Column PivotChart to the Scenario PivotTable worksheet, based on the Scenario PivotTable report. Resize and reposition the chart so that it covers the range A8:C21. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

1 Expo Events - Builders Trade Show 2 3 4 2,800 Revenue Attendees Registration fee Total revenue Builders Trade Show - Profit Analysis Attendees Total Revenue Total Expenses 2,800 $ 182.000 $ 172,750 $ 2,200 Gross Profit 9,250 5 $ 65.00 182,000.00 6 $ 2,500 7 8 $ 9 10 2,800 3,100 3,400 55,150.00 24.00 122,350.00 $ $ 11 12 Variable Expenses Materials Variable cost per attendee Total variable costs Fixed Expenses Total fixed cost Total expenses Total expense per attendee 13 $ 14 $ 50,400.00 172.750.00 61.70 $ 75.00 $ $ 85.00 15 $ 9,250 $ 2,200 2,500 2,800 Builders Trade Show - Gross Profit Analysis 60.00 $ 65.00 $ 70.00 $ (26,350.00) (15,350.00) (4,350.00) (15,550.00) (3,050.00) 9,450.00 (4,750.00) 9,250.00 23,250.00 6,050.00 21,550.00 37,050.00 16,850.00 33,850.00 50,850.00 55.00 $ (37,350.00) (28,050.00) (18,750.00) (9,450.00) (150.00) 6,650.00 21,950.00 37,250.00 80.00 17,650.00 34,450.00 16 17 18 19 51,250.00 28,650.00 46,950.00 65,250.00 83,550.00 101,850.00 Profit Total sales Total expenses Gross profit $ 182,000.00 172.750.00 50.00 3,100 3,400 52,550.00 67,850.00 68,050.00 84,850.00 $ 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Documentation Builders GymWear Logistics Service Providers March + 1 G2 X f A B D E F G H 1 Expo Events - GymWear Revenue 2 3 4 5 Attendees Gross Profit Attendees Registration fee Total revenue 2,100 85.00 $ $ 6 178,500.00 7 8 9 GymWear - Break-Even Analysis Total Sales Total Expenses 178,500 167,395 204,000 175,795 195,500 172,995 187,000 170,195 178,500 167,395 170,000 164,595 161,500 161.795 153,000 158,995 144.500 156,195 136,000 153,395 $ 56,995.00 28.00 2,100 2,400 2,300 2,200 2,100 2,000 1,900 1,800 1,700 1,600 11,105 28,205 22,505 16,805 11,105 5,405 (295) (5,995) (11,695) (17,395) 10 $ Variable Expenses Materials Variable cost per attendee Total variable costs Fixed Expenses Total fixed cost Total expenses Total expense per attendee $ 115,795.00 IT 12 13 14 $ $ 51,600.00 167,395.00 79.71 15 $ 16 17 18 $ Profit Total sales Total expenses Gross profit 178,500.00 167,395.00 19 $ $ 20 11,105.00 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Documentation Builders GymWear Logistics Service Providers March + G2 X fo A B C D E F G H 1 Expo Events - Atlantic Logistics 2 3 4 1,500 Revenue Attendees Registration fee Total revenue 5 $ 95.00 $ 6 $ 142,500.00 Attendees 1,800 1,700 1,600 7 100.00 $ 38,100.00 30,600.00 23,100.00 15,600.00 8,100.00 8 Atlantic Logistics - Net Income Analysis Registration Fees 85.00 $ 90.00 $ 95.00 $ 11,100.00 20,100.00 29,100.00 5,100.00 13,600.00 22,100.00 (900.00) 7,100.00 15,100.00 (6,900.00) 600.00 8,100.00 (12.900.00) (5,900.00) 1,100.00 (18,900.00) (12,400.00) (5,900.00) (24,900.00) (18,900.00) (12,900.00) (30,900.00) (25,400.00) (19,900.00) (36,900.00) (31,900.00) (26,900.00) 9 $ 1,500 49,150.00 25.00 105.00 47,100.00 39,100.00 31,100.00 23,100.00 15,100.00 7,100.00 (900.00) (8,900.00) (16,900.00) 10 $ 11 Variable Expenses Materials Variable cost per attendee Total variable costs Fixed Expenses Total fixed cost Total expenses Total expense per attendee $ 86,650.00 1,400 1,300 1,200 12 600.00 (6,900.00) (14,400.00) (21,900.00) 13 $ 1,100 1,000 $ 47,750.00 134,400.00 89.60 14 15 $ $50.000 16 $40.000 17 $30,000 18 Profit Total sales Total expenses Gross profit $ $ $20,000 19 20 142,500.00 134,400.00 8,100.00 $10,000 $ Net Income $0 21 22 ($10,000) 23 24 (520.000) (530.000) (540,000) 1.000 25 1,100 1.200 1,300 1.600 1.700 1,800 1.900 1,400 1,500 Attendees 26 27 Series! Series2 Series 3 Series 4 Series 5 28 29 30 31 32 33 34 Documentation Builders GymWear Logistics Service Providers March + G1 fox A B E F G Expo Events - Event Service Providers 1 2. 3 2 3 4 TradeStar General Leary Total Hours Provided 68 60 64 192 5 $ Fixed Costs Fixed Cost Per Hour Variable Costs Variable Cost Per Hour Total Costs 8,500.00 $ 125.00 6,460.00 95.00 14,960 $ 10,000.00 $ 166.67 5,400.00 90.00 15,400 $ 11,500.00 179.69 5,440.00 85.00 16,940 $ 9 47,300 Minimum Total Cost Model 6 7 8 9 10 IL 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Documentation Builders GymWear Logistics Service Providers March G1 A B D E B Expo Events - March Trade Shows 1 2 3 2 3 Builders GymWear Logistics 4 5 6 Revenues Attendees Registration fee Total sales 6 7 AA 2,800 65.00 $ 182,000 $ 2,100 85.00 $ 178,500 $ 1,500 95.00 142,500 7 or 9 9 Cost of Goods Sold Fixed costs Variable cost per attendee Variable costs Total costs $ $ $ $ 50,400.00 $ 24.00 $ 122,350.00 $ 172,750.00 $ 51,600.00 $ 28.00 $ 115,795.00 $ 167,395.00 $ 47,750.00 25.00 86,650.00 134,400.00 u $ 10 IL 12 13 14 15 16 17 18 19 20 21 Total profit Profit per attendee 9,250 $ $3.30 11,105 $ $5.29 8,100 $5.40 7 23 24 25 26 27 28 29 30 31 32 33 34 Documentation Builders GymWear Logistics Service Providers March 1 Expo Events - Builders Trade Show 2 3 4 2,800 Revenue Attendees Registration fee Total revenue Builders Trade Show - Profit Analysis Attendees Total Revenue Total Expenses 2,800 $ 182.000 $ 172,750 $ 2,200 Gross Profit 9,250 5 $ 65.00 182,000.00 6 $ 2,500 7 8 $ 9 10 2,800 3,100 3,400 55,150.00 24.00 122,350.00 $ $ 11 12 Variable Expenses Materials Variable cost per attendee Total variable costs Fixed Expenses Total fixed cost Total expenses Total expense per attendee 13 $ 14 $ 50,400.00 172.750.00 61.70 $ 75.00 $ $ 85.00 15 $ 9,250 $ 2,200 2,500 2,800 Builders Trade Show - Gross Profit Analysis 60.00 $ 65.00 $ 70.00 $ (26,350.00) (15,350.00) (4,350.00) (15,550.00) (3,050.00) 9,450.00 (4,750.00) 9,250.00 23,250.00 6,050.00 21,550.00 37,050.00 16,850.00 33,850.00 50,850.00 55.00 $ (37,350.00) (28,050.00) (18,750.00) (9,450.00) (150.00) 6,650.00 21,950.00 37,250.00 80.00 17,650.00 34,450.00 16 17 18 19 51,250.00 28,650.00 46,950.00 65,250.00 83,550.00 101,850.00 Profit Total sales Total expenses Gross profit $ 182,000.00 172.750.00 50.00 3,100 3,400 52,550.00 67,850.00 68,050.00 84,850.00 $ 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Documentation Builders GymWear Logistics Service Providers March + 1 G2 X f A B D E F G H 1 Expo Events - GymWear Revenue 2 3 4 5 Attendees Gross Profit Attendees Registration fee Total revenue 2,100 85.00 $ $ 6 178,500.00 7 8 9 GymWear - Break-Even Analysis Total Sales Total Expenses 178,500 167,395 204,000 175,795 195,500 172,995 187,000 170,195 178,500 167,395 170,000 164,595 161,500 161.795 153,000 158,995 144.500 156,195 136,000 153,395 $ 56,995.00 28.00 2,100 2,400 2,300 2,200 2,100 2,000 1,900 1,800 1,700 1,600 11,105 28,205 22,505 16,805 11,105 5,405 (295) (5,995) (11,695) (17,395) 10 $ Variable Expenses Materials Variable cost per attendee Total variable costs Fixed Expenses Total fixed cost Total expenses Total expense per attendee $ 115,795.00 IT 12 13 14 $ $ 51,600.00 167,395.00 79.71 15 $ 16 17 18 $ Profit Total sales Total expenses Gross profit 178,500.00 167,395.00 19 $ $ 20 11,105.00 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Documentation Builders GymWear Logistics Service Providers March + G2 X fo A B C D E F G H 1 Expo Events - Atlantic Logistics 2 3 4 1,500 Revenue Attendees Registration fee Total revenue 5 $ 95.00 $ 6 $ 142,500.00 Attendees 1,800 1,700 1,600 7 100.00 $ 38,100.00 30,600.00 23,100.00 15,600.00 8,100.00 8 Atlantic Logistics - Net Income Analysis Registration Fees 85.00 $ 90.00 $ 95.00 $ 11,100.00 20,100.00 29,100.00 5,100.00 13,600.00 22,100.00 (900.00) 7,100.00 15,100.00 (6,900.00) 600.00 8,100.00 (12.900.00) (5,900.00) 1,100.00 (18,900.00) (12,400.00) (5,900.00) (24,900.00) (18,900.00) (12,900.00) (30,900.00) (25,400.00) (19,900.00) (36,900.00) (31,900.00) (26,900.00) 9 $ 1,500 49,150.00 25.00 105.00 47,100.00 39,100.00 31,100.00 23,100.00 15,100.00 7,100.00 (900.00) (8,900.00) (16,900.00) 10 $ 11 Variable Expenses Materials Variable cost per attendee Total variable costs Fixed Expenses Total fixed cost Total expenses Total expense per attendee $ 86,650.00 1,400 1,300 1,200 12 600.00 (6,900.00) (14,400.00) (21,900.00) 13 $ 1,100 1,000 $ 47,750.00 134,400.00 89.60 14 15 $ $50.000 16 $40.000 17 $30,000 18 Profit Total sales Total expenses Gross profit $ $ $20,000 19 20 142,500.00 134,400.00 8,100.00 $10,000 $ Net Income $0 21 22 ($10,000) 23 24 (520.000) (530.000) (540,000) 1.000 25 1,100 1.200 1,300 1.600 1.700 1,800 1.900 1,400 1,500 Attendees 26 27 Series! Series2 Series 3 Series 4 Series 5 28 29 30 31 32 33 34 Documentation Builders GymWear Logistics Service Providers March + G1 fox A B E F G Expo Events - Event Service Providers 1 2. 3 2 3 4 TradeStar General Leary Total Hours Provided 68 60 64 192 5 $ Fixed Costs Fixed Cost Per Hour Variable Costs Variable Cost Per Hour Total Costs 8,500.00 $ 125.00 6,460.00 95.00 14,960 $ 10,000.00 $ 166.67 5,400.00 90.00 15,400 $ 11,500.00 179.69 5,440.00 85.00 16,940 $ 9 47,300 Minimum Total Cost Model 6 7 8 9 10 IL 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Documentation Builders GymWear Logistics Service Providers March G1 A B D E B Expo Events - March Trade Shows 1 2 3 2 3 Builders GymWear Logistics 4 5 6 Revenues Attendees Registration fee Total sales 6 7 AA 2,800 65.00 $ 182,000 $ 2,100 85.00 $ 178,500 $ 1,500 95.00 142,500 7 or 9 9 Cost of Goods Sold Fixed costs Variable cost per attendee Variable costs Total costs $ $ $ $ 50,400.00 $ 24.00 $ 122,350.00 $ 172,750.00 $ 51,600.00 $ 28.00 $ 115,795.00 $ 167,395.00 $ 47,750.00 25.00 86,650.00 134,400.00 u $ 10 IL 12 13 14 15 16 17 18 19 20 21 Total profit Profit per attendee 9,250 $ $3.30 11,105 $ $5.29 8,100 $5.40 7 23 24 25 26 27 28 29 30 31 32 33 34 Documentation Builders GymWear Logistics Service Providers March