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

GETTING STARTED

1. Save the file SC_EX365_CS1-3A_FirstLastName_1.xlsx as SC_EX365_CS1-3A_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_CS1-3A_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. Unfreeze the first column since it does not display information that applies to the rest of the worksheet.

2. In cell K13, enter a formula using the NOW function to display today's date. Apply the Short Date number format to display only the date in the cell.

3. Format the worksheet title as follows to use a consistent design throughout the workbook:

a. Fill cell B1 with the Brown, Accent 6, Lighter 40% shading color.

b. Change the font color to White, Background 1.

c. Merge and center the contents of cell B1 across the range B1:H1.

d. Use AutoFit to resize row 2 to its best fit.

4. Format the billing rate data as follows to suit the design of the worksheet and make the data easier to understand:

a. Italicize the contents of cell J14 to match the formatting in cell J13.

b. Apply the Currency number format to cell K14 to clarify that it contains a dollar amount.

5. Format the data in cell A3 as follows to display all of the text:

a. Merge the cells in the range A3:A12.

b. Rotate the text up in the merged cell so that the text reads from bottom to top.

c. Middle-align and center the text.

d. Remove the border from the merged cell.

e. Resize column A to a width of 3.00.

6. Format the data in row 3 as follows to show that it contains column headings:

a. Change "Description" to use Service Description as the complete column heading.

b. Apply the Ice Blue, 20% - Accent 2 cell style to the range B3:H3.

c. Use AutoFit to resize column D to its best fit.

7. Gemma wants you to include the actual dollar amount of the services performed in column E. Enter this information as follows:

a. In cell E4, enter a formula without using a function that multiplies the actual hours (cell D4) by the billing rate (cell K14) to determine the actual dollar amount charged for general administrative services. Include an absolute reference to cell K14 in the formula.

b. Use the Fill Handle to fill the range E5:E11 with the formula in cell E4 to include the charges for the other services.

c. Format the range E4:E11 using the Comma number format and no decimal places to match the formatting in column F.

8. Gemma needs you to show how much of the estimate remains after the services performed. Provide this information as follows:

a. In cell G4, enter a formula without using a function that subtracts the actual dollars billed (cell E4) from the estimated amount (cell F4) to determine the remaining amount of the estimate for general administrative services.

b. Use the Fill Handle to fill the range G5:G11 with the formula in cell G4 to include the remaining amount for the other services.

c. Format the range G4:G11 using the Comma number format and no decimal places to match the formatting in column F.

9. Gemma also wants you to show the remaining amount as a percentage of the actual amount. Enter this information as follows:

a. In cell H4, enter a formula that divides the remaining dollar amount (cell G4) by the estimated dollar amount (cell F4).

b. Copy the formula in cell H4 to the range H5:H12, pasting only the formula and number formatting to display the remaining amount as a percentage of the actual amount for the other services and the total.

10. Calculate the project status totals as follows:

a. In cell D12, enter a formula using the SUM function to total the actual hours (range D4:D11).

b. Use the Fill Handle to fill the range E12:G12 with the formula in cell D12.

c. Apply the Accounting number format with no decimal places to the range E12:G12.

11. Gemma also wants you to identify the services for which A.W. Jones has billed more than the full estimate amount. In the range H4:H11, use Conditional Formatting Highlight Cells Rules to format values less than 1% (0.01) in 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 wants you to include the data in the Project Status worksheet. Copy and paste the data as follows:

a. Go to the Consultants worksheet and copy the data in the range A1:F9.

b. Return to the Project Status worksheet. Paste the data in cell J2, keeping the source formatting when you paste it.

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. List this information as follows:

a. In cell N4 on the Project Status worksheet, enter a formula that uses the IF function to test whether the number of years of experience (cell M4) is greater than or equal to 5.

b. If the consultant has five or more years of experience, display "Data Engineer" in cell N4.

c. If the consultant has less than five years of experience, display "Testing Lead" in cell N4.

d. Copy the formula in cell N4 to the range N5:N10, pasting the formula only.

e. Use AutoFit to resize column N to its best fit.

14. Gemma wants you to include summary statistics about the project and the consultants. In cell D14, enter a formula that uses the AVERAGE function to average the number of years of experience (range M4:M10).

15. Make the 3-D Clustered Column chart in the range B16:H30 easier to interpret as follows:

a. Change the chart type to a Clustered Bar chart.

b. Use Actual Project Hours as the chart title.

c. Add a primary horizontal axis title to the chart, using Hours as the axis title text.

d. 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 to Project Schedule to use a more descriptive name.

18. Each service starts on a different date because the services depend on each other. Enter the starting dates for the remaining services as follows:

a. In cell C5, enter a formula without using a function that adds 5 days to the value in cell B5.

b. In cell D5, enter a formula without using a function that subtracts 4 days from the value in cell C5.

c. In cell E5, enter a formula without using a function that adds 2 days to the value in cell D5.

d. In cell F5, enter a formula without using a function that adds 2 days to the value in cell E5.

19. Copy the formulas in Phase 3 to the rest of the schedule as follows:

a. Copy the formula in cell C5 to the range C6:C8.

b. Copy the formula in cell D5 to the range D6:D8.

c. Copy the formula in cell E5 to the range E6:E8.

d. Copy the formula in cell F5 to the range F6:F8.

20. In cell B10, enter a formula that uses the MIN function to find the earliest date in the project schedule (range B5:F8).

21. In cell B11, enter a formula that uses the MAX function to find the latest date in the project schedule (range B5:F8).

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.