Illustrated Excel 2019 | Module 6: End of Module Project 2 Flores & Falk Law Offices

GETTING STARTED

1. Open the file IL_EX19_EOM6-2_FirstLastName_1.xlsx, available for download from the SAM website.

2. Save the file as IL_EX19_EOM6-2_FirstLastName_2.xlsx by changing the “1” to a “2”.

a. If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

3. To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

Support_EX19_EOM6-2_August.xlsx

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

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

PROJECT STEPS

1. Oscar Sanchez manages the main office of the Flores & Falk law firm in Las Vegas, Nevada. The firm has branch offices in three other cities in the state. Staff members have entered invoice data in an Excel workbook, which Oscar is now analyzing. He asks for your help in making the data more secure and easier to understand. Go to the Summary worksheet and then unprotect the worksheet so that you can update it.

2. Oscar hid one worksheet to keep the data private but wants to make a formatting change to that worksheet and one other in the workbook. Unhide the August Billings worksheet, and then group the August Billings and September Billings worksheets. Fill merged cell B2 with the Aqua, Accent 5 shading color to match the formatting of the Summary worksheet. Ungroup the worksheets.

3. Return to the Summary worksheet. Oscar wants to focus on the revenue from entertainment law cases (an area the law firm wants to expand) and be able to display only the totals for each category. Hide rows 3:8 and rows 15:37 to display data from only the Entertainment category of cases. Select cell G1 and then create a custom view using Entertainment as the name. Display the Summary worksheet using the All_Categories custom view.

4. Group rows 4:6 so that you can hide and display Corporate revenue details. Also group rows 10:12, rows 16:18, rows 22:24, rows 28:30, and rows 34:36. Collapse the outline to display only the name and totals for the Corporate, Entertainment, Int. Property, Real Estate, and Tax categories. Leave the Family category expanded. Unlock the Family data (range B16:D18) so that it can be changed later.

5. To prepare for printing, insert a page break so that row 27 prints on a new page.

6. Unhide the August Billings worksheet to display it. Oscar will verify the data on this worksheet by comparing it to the original, unformatted data in another workbook. He wants to use a hyperlink to access the other workbook quickly. In cell J3, add a hyperlink that links to the Support_EX19_EOM6-2_August.xlsx workbook. Hide the August Billings worksheet to keep the data private.

7. Go to the September Billings worksheet, which lists September invoices sorted by invoice number. Oscar wants to display the billed amounts by client type. Sort the September billings data in ascending order by Client Type values. Add subtotals to the worksheet at each change in the Client Type. Use the Sum function to total Billed values and display the summary below the data.

8. Protect the September Billings worksheet so that users can unprotect it without a password. Allow users to select locked and unlocked cells on the protected worksheet.

9. Add Billings in the Categories document property so that Oscar can find the workbook easily when he is searching for files.

10. Oscar plans to share the workbook with members of the law firm advisors, though some of them use earlier versions of Excel. Check the compatibility of the workbook with earlier versions of Excel, and then copy the Compatibility Report to a new worksheet.

11. Because the workbook contains sensitive data, protect the workbook as follows:

a. Protect the workbook structure using Oscar as the password.

b. Mark the workbook as final. (Hint: Click OK when prompted to confirm.)

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.

Final Figure 1: Summary Worksheet

Final Figure 2: September Billings Worksheet

Final Figure 3: Compatibility Report Worksheet