Illustrated Excel 2019 | Module 7: SAM Project 1b Aloft Recruiting

GETTING STARTED

1. Open the file IL_EX19_7b_FirstLastName_1.xlsx, available for download from the SAM website.

2. Save the file as IL_EX19_7b_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_7b_Payback.docx

Support_EX19_7b_Phones.jpg

Support_EX19_7b_Prepaid.png

Support_EX19_7b_Prepaid2.xlsx

Support_EX19_7b_Specs.pptx

Support_EX19_7b_Totals.csv

4. With the file IL_EX19_7b_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.

5. If a security warning appears, click Enable Content to begin the project. If a dialog box about links pops up, click Don't Update to continue.

PROJECT STEPS

1. Jermaine Harris is a recruiter at Aloft Recruiting, which encourages employees to use their own cell phones while visiting clients. To help his colleagues purchase a new phone, Jermaine created an Excel workbook to compare phones and plans. He asks for your help to improve the appearance of the workbook and include data stored in other files. Go to the Plan Options worksheet. In cell C1, insert the picture Support_EX19_7b_Phones.jpg.

2. Adjust the phones picture as follows to make it more attractive:

a. Apply the Brightness +20% Contrast +40% correction to the picture.

b. Sharpen the picture by 25% to remove the blurriness.

c. Add the Tight Reflection: Touching effect from the Reflection gallery.

3. Jermaine needs to add another shape and picture to the SmartArt on the worksheet to show four cell phone plans instead of three. He also wants to indicate that one plan includes a group discount. Add a shape and picture to the SmartArt as follows:

a. Add a shape after the Prepaid 1 shape.

b. Type Prepaid 2 in the new shape.

c. Use the picture placeholder to add the picture Support_EX19_7b_Prepaid.png to the SmartArt.

d. Add another shape after the Contract 2 shape.

e. Type Group Discount in the new shape, and then demote the new shape.

4. Format the SmartArt as follows so that it coordinates more closely with the rest of the worksheet:

a. Change the SmartArt colors to Colored Fill – Accent 2.

b. Apply the Subtle Effect SmartArt style.

5. Jermaine wants to include an image of a PowerPoint slide he created listing phone specs. Include the image as follows:

a. Use PowerPoint to open the presentation Support_EX19_7b_Specs.pptx.

b. In Excel, use the Screen Clipping tool to paste a screenshot of Slide 1: Phone Specs into the Plan Options worksheet.

c. Position the upper-left corner of the screenshot image in cell I1, and close the support file.

6. Go to the Phone Comparison worksheet. Add a Light Blue, Background 2, Darker 50% border to the Aloft picture to match the same picture on the Plan Options worksheet.

7. Position and format the picture of the two phones as follows to improve the appearance of the worksheet:

a. Copy the formatting of the Aloft picture to the picture of the two phones in cell G4.

b. Without moving the phones picture to the left or right, align the top of the phones picture with the top of the Aloft picture.

8. Jermaine has a Word document with a table that lists the reimbursement amounts Aloft provides for employees who purchase cell phones and use them at work. Embed the Word table in the Phone Comparison worksheet as follows:

a. Use Word to open the document Support_EX19_7b_Payback.docx.

b. Copy the table and then paste it into the Phone Comparison worksheet starting in cell B21. Close the support file.

9. The embedded data includes an incorrect amount. In cell C25, edit the embedded data to use $20 as the payback amount for monthly payments over $80.

10. Jermaine has a text file that lists the totals for each plan. Import the text file as follows:

a. Get data from the Text/CSV file Support_EX19_7b_Totals.csv.

b. If the first row ("Summary Total") does not appear as column headers, edit the text file before loading it to use the first row as headers.

c. Choose to load the data to a location in the worksheet.

d. View the imported data as a table and insert the data in cell I5 of the existing worksheet.

e. Apply the Aqua, Table Style Light 11 table style to the imported table to coordinate with the rest of the worksheet contents, and then format the range J6:J9 in the Accounting number format with 2 decimal places and $ as the symbol.

11. Jermaine originally inserted the Prepaid 2 plan data as a link to another Excel workbook. He asks you to update the data and then remove the link. Update and remove the link as follows:

a. Display the links to external files, and then update the link to the workbook Support_EX19_7b_Prepaid2.xlsx.

b. Break the link to the Support_EX19_7b_Prepaid2.xlsx file

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: Plan Options Worksheet

Final Figure 2: Phone Comparison Worksheet