New Perspectives Excel 365 | Module 8: End of Module Project 2 Donnelly Communications

GETTING STARTED

1. Save the file NP_EX365_EOM8-2_FirstLastName_1.xlsx as NP_EX365_EOM8-2_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file NP_EX365_EOM8-2_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Charlie Mendoza is a financial analyst for Donnelly Communications, a full-service business and communications workshop facilitator based in Cleveland, Ohio. Charlie created a workbook containing details about workshops scheduled for June this year. He asks you to help analyze the data to determine how the company can increase profits. Switch to the Leadership worksheet. Charlie wants you to calculate the revenue, expenses, and profit based on the number of people attending the Leadership Skills Workshop. He 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 students (cell C4) as the Column input cell.

2. In cell E14, apply a custom format to display the text Students/Fee in place of the cell value.

3. Switch to the Accounting worksheet. Create a Scatter with Straight Lines chart based on the range E4:G14 in the data table titled Accounting Fundamentals Workshop - Break-Even Analysis. Resize and move the chart so that it covers the range E15:H29. Remove the chart title. Add Sales and Expenses as the vertical axis title and Students as the horizontal axis title.

4. Charlie wants you to change the bounds of the chart to better determine the break-even point. Change the Minimum Bounds of the vertical axis to 2000. 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 10.

5. One way to increase profits is to run the Accounting workshop for a second day. Another way is to run the workshop online, which will reduce Fixed Costs by removing venue rental and food costs. Charlie has already created a scenario named Extra Day and wants you to create another for running the workshop online. In the Scenario Manager, add a scenario using the data shown in bold in Table 1. The changing cells are the nonadjacent cells C5, C10, and C13.

Table 1: Accounting Scenario Values

Scenario Name | Online Registration_fee (C5) | 175 Variable_cost_per_student (C10) | 15 Total_fixed_cost (C13) | 1800

6. One of the values in the Extra Day scenario is incorrect. Edit the Extra Day scenario to use 5,500 as the Total_fixed_cost value. Show the Extra Day scenario, and then close the Scenario Manager.

7. Switch to the Marketing worksheet. Charlie wants you to clarify what each line means in the Scatter with Straight Lines chart in the range E15:J32. 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 Co-Hosting Companies worksheet. Charlie wants you to determine whether partnering with other communications providers to co-host the workshops would reduce overall expenses. 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 104, the total hours of workshop services provided. · F10 is less than or equal to 3,300, the maximum amount Charlie can pay to the other workshop providers. · C4:E4 is less than or equal to 39, the maximum hours provided by a single workshop provider. · 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. Charlie wants you 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 Co-Hosting Answer Report as the name of the worksheet containing the Answer report.

10. Switch to the June worksheet, which compares the data for the three workshops the company is handling in June. Charlie 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 co-hosts. He asks you to use the Scenario Manager to create a Scenario Summary report that summarizes the effect of the Current, Increase Fee, and Co-Hosts scenarios. Use the range C16:E16 as the result cells.

11. Switch back to the June 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_Student_Leadership, Profit_Student_Accounting, and Profit_Student_Marketing) 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:C26.

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 website to submit your completed project.

Final Figure 1: Leadership Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 2: Accounting Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 3: Marketing Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 4: Co-Hosting Companies Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 5: Co-Hosting Answer Report Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 6: Scenario Summary Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 7: Scenario PivotTable Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 8: June Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.