Shelly Cashman Excel 365 | Modules 1-3: SAM Critical Thinking Capstone Project C A.W. Jones Finance Consultants

GETTING STARTED

1. Save the file SC_EX365_CT_CS1-3C_FirstLastName_1.xlsx as SC_EX365_CT_CS1-3C_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 SC_EX365_CT_CS1-3C_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. Gemma Oyuela is a senior account manager at A.W. Jones Finance Consultants, a consulting firm that works with financial companies around the world. Gemma has created a workbook summarizing the status of the consulting project for Asset Plus Solutions. She asks for your help in completing the workbook. Go to the Project Status worksheet. The data is incomplete and the formatting needs to be finished. Unfreeze the first column since it does not display information that applies to the rest of the worksheet.

2. Enter a function to the right of the cell marked "Date:" to display the current date and time, then use the Short Date number format to display only the date in the cell.

3. The worksheet title should use a consistent design throughout the workbook. Change the fill color and the font color of the cell containing the text "Asset Plus System Upgrade" to match the cell below. Merge and center the contents through the end of the table data. Use AutoFit to resize row 2 to its best fit.

4. Format the billing rate data to suit the design of the worksheet and make the data easier to understand. Italicize the label "Billing Rate:" to match the formatting in the cell labeled "Date:". Then apply the Currency number format to the rate to display it as a dollar amount.

5. The text in the first column needs to be displayed in a better way. Format the data in the first column as follows: Merge the cells from the text "Asset Plus Solutions" down to the bottom of the Service ID column, including the total row. Rotate the text up in the merged cell so that the text reads from bottom to top. Middle-align and center the text. Remove the border from the merged cell, and then resize the column width to 3.00.

6. Format the row of data under the cell containing "Project Status Report" to show that it contains column headings. Change "Description" to Service Description as the complete column heading. Apply the Ice Blue, 20% - Accent 2 cell style to the row of headings, and then resize the Actual Hours column to its best fit.

7. Gemma wants you to include the actual dollar amount of the services performed in the Actual $ column. Starting with the actual dollar amount for SOX compliance, enter a formula without using a function that multiplies the actual hours by the billing rate to determine the actual dollar amount charged for SOX compliance. Be sure that the formula always references the billing rate. Copy the formula down to fill the remaining amounts, and then match the number formats of the column to the Estimated $ column.

8. Gemma needs you to show how much of the estimate remains after the services are performed. Starting with SOX compliance, enter a formula without using a function that subtracts the actual dollars billed from the estimated amount to determine the remaining amount of the estimate. Copy the formula down to fill the remaining amounts, and then match the number formats to the Estimated $ column.

9. Gemma also wants you to show the remaining amount as a percentage of the actual amount. Starting with SOX compliance, enter a formula without using a function that divides the remaining dollar amount by the estimated dollar amount. Copy the formula down to fill the remaining percentages. Copy the formula (but not the formatting) to the total row as well.

10. Now, Gemma needs you to calculate the project status totals. In the appropriate cell, use a function to total the actual hours. Copy the formula across to fill the rest of the totals, and then apply the Accounting number format with no decimals to the filled in cells.

11. Gemma also wants you to identify the services for which A.W. Jones has billed more than the full estimate amount. Use Conditional Formatting to highlight remaining percentages with values less than 1% using Yellow Fill with Dark Yellow Text.

12. Gemma imported data about the consultants working on the Asset Plus project and stored the data on a separate worksheet, but she wants to include the data in the Project Status worksheet. Go to the Consultants worksheet and copy the entire table of consultants. Paste the data beginning in cell J2, keeping the source formatting.

13. Gemma needs you to list the role for each consultant. Those with five or more years of experience take the Data Engineer role. Otherwise, they take the Testing Lead role. Starting with Jimmy Searles, use a function to determine the consultant's role, displaying "Data Engineer" or "Testing Lead" as appropriate. Copy the formula down to fill the remaining roles, pasting only the formula. Resize the Role column to its best fit.

14. Gemma wants you to include summary statistics about the project and the consultants. In the appropriate cell under the Project Status Report table, use a function to determine the average the number of years of experience of all consultants.

15. The 3-D clustered column chart is not the clearest format to use to show the actual hours. Reformat the chart to make it easier to interpret. Change the chart type to a Clustered Bar chart. Use Actual Project Hours as the chart title. Add a primary horizontal axis title to the chart, using Hours as the axis title text. Add data labels in the center of each bar.

16. Delete row 31 since Gemma had you reformat the clustered column chart.

17. Go to the Schedule worksheet. Rename the Schedule worksheet tab Project Schedule to use a more descriptive name.

18. Each service starts on a different date because the services depend on each other. Starting with Phase 2, enter a formula without using a function that adds 5 days to the Y-43 service date. Next, use a similar formula that subtracts 4 days from the G-55 service date. For the next date, use a similar formula that adds 2 days to the M-73 service date. Finally, use a similar formula that adds 2 days to the C-09 service date.

19. Copy the formulas in Phase 2 down to fill the rest of the schedule.

20. Next to the cell labeled "Start date", use a function to find the earliest of the project schedule dates.

21. In the appropriate cell, use a function to find the latest of the project schedule dates.

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.

The value in cell K13 of the Project Status worksheet has been intentionally blurred as it will never be constant.

Final Figure 1: Project Status 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: Consultants 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: Project Schedule 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.