Homework answers / question archive / Illustrated Excel 2019 | Module 6: End of Module Project 1 Quinn Retail Services MANAGE WORKBOOK DATA GETTING STARTED Open the file IL_EX19_EOM6-1_FirstLastName_1

Illustrated Excel 2019 | Module 6: End of Module Project 1 Quinn Retail Services MANAGE WORKBOOK DATA GETTING STARTED Open the file IL_EX19_EOM6-1_FirstLastName_1

MS Excel

Share With

Illustrated Excel 2019 | Module 6: End of Module Project 1

Quinn Retail Services

MANAGE WORKBOOK DATA

GETTING STARTED

• Open the file IL_EX19_EOM6-1_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as IL_EX19_EOM6-1_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • 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.
• 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-1_Jan-May.xlsx
  • Support_EX19_EOM6-1_Logo.png
• With the file IL_EX19_EOM6-1_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

• PROJECT STEPS

1. Ladonna Raymond is an account manager for Quinn Retail Services, a national merchandising company headquartered in Cincinnati, Ohio. Ladonna is using an Excel workbook to analyze sales for June and July of this year. She asks you to update the workbook and make the data easier to interpret so that she can share it with other Quinn employees.
   Go to the Links worksheet. Ladonna wants to add a picture to the background of the Links worksheet to add visual interest. Insert the picture Support_EX19_EOM6-1_Logo.png as the worksheet background.
2. Ladonna wants to add hyperlinks to the Links worksheet to make it easier to navigate and find information. Format cell C4 as a hyperlink that links to the file Support_EX19_EOM6-1_Jan-May.xlsx. Add Workbook with semiannual data as the ScreenTip text. Format cell C5 as a hyperlink that links to the cell A1 of the June Sales worksheet. Format cell C7 as a hyperlink that links to the www.quinn.cengage.com website. Format cell C8 as a hyperlink that links to the info@quinn.cengage.com email address.
3. Ladonna decides she does not want to provide her email address in the workbook. Remove the hyperlink from cell C10, while keeping the "Ladonna Raymond" text.
4. Go to the June Sales worksheet. Ladonna occasionally wants to focus on the billing data for the first full week of June. Set up a custom view by hiding rows 12–50. Select cell G1 and then create a custom view using Week1 as the name. Display the June Sales worksheet using the custom view Weeks1-4, which Ladonna already created, to redisplay all of the data.
5. The June Sales worksheet has the same structure as the July Sales worksheet. Ladonna wants to apply the same formatting to both worksheets. Group the June Sales and July Sales worksheets. Fill the merged cell B2 with the Green, Accent 3 fill color. Ungroup the worksheets.
6. On the June Sales worksheet, freeze the rows so that rows 1–3 always appear at the top of the worksheets when scrolling.
7. For the June billings, Ladonna wants to display subtotals for each region. Sort the June Billings data in ascending order by Region. Add subtotals to the Billed column at each change in the Region. Use the Sum function and display the summary below the data.
8. Ladonna wants to display the subtotals directly to the right of the subtotal heading. Hide column E, which does not contain information Ladonna needs to display with the subtotals.
9. Go to the July Sales worksheet. Ladonna still has to verify the Paid amounts for the July billings. She asks you to secure the rest of the July Sales worksheet data so that no one can change it. Unlock the Paid data (the range G4:G52) so that Ladonna can change it later. Protect the July Sales worksheet so that users can unprotect it without a password. Also allow users to select locked and unlocked cells.
10. To help Ladonna find the workbook after she saves and closes it, add the text June and July Sales to the Title document property.
11. Prepare the workbook for distribution by marking it as final.

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.