GETTING STARTED

$$ Save the file IL$\_$EX$365\_2021\_$EOM$10-1\_$FirstLastName$\_1.$xlsx as IL$\_$EX$365\_2021\_$EOM$10-1\_$FirstLastName$\_2.$xlsx

o Edit the file name by changing $1$ to $2.$

o If you do not see the $.$xlsx file extension, do not type it$.$ The file extension will be added for you automatically.

$$ To complete this Project, you will also need the following files:

o Support$\_$EX$365\_2021\_$EOM$10-1\_$AprClients.xml

o Support$\_$EX$365\_2021\_$EOM$10-1\_$Leads.html

o Support$\_$EX$365\_2021\_$EOM$10-1\_$MayClients.xml

o Support$\_$EX$365\_2021\_$EOM$10-1\_$MayClients.xsd

$$ With the file IL$\_$EX$365\_2021\_$EOM$10-1\_$FirstLastName$\_2.$xlsx open, ensure that your first and last name is displayed in cell B$6$ of the Documentation worksheet.

o If cell B$6$ does not display your name, delete the file and download a new copy.

PROJECT STEPS

$1.$ Silvia Villacruz is the head of marketing and sales for Malik Construction, a full$-$service construction firm in Boulder, Colorado. She has been tracking sales leads in an Excel workbook, which has been reviewed by Gurman Malik, the owner of the firm, and Natalia Martinez, an account manager. Silvia asks for your help in responding to comments and including missing data.

Go to the Sales Leads worksheet and show all comments in the workbook. Respond to the comment in cell C$8$ by entering Trade show in the cell and then deleting the comment from Gurman Malik.

$2.$ Go to the next comment in the worksheet, which is from Natalia Martinez in cell F$8.$ Reply to the comment by including the text Okay, thank you as response, and then post your reply. Reply to the comment in cell E$12$ by including the text Yes, the percentage is correct. as the response, and then post your reply.

$3.$ Silvia notices that an entry in the Potential Opportunity column might be incorrect and wants to ask Natalia to verify it$.$ In cell D$8,$ insert and post a new comment that contains the following text:

Is this estimate correct? Please let me know.

$4.$ Silvia wants to add some data from an internal company webpage to the worksheet.

Open the file Support$\_$EX$365\_2021\_$EOM$10-1\_$Leads.html in your browser, select the entire table, and then copy the data. In the Sales Leads worksheet, select the range A$13$:G$16,$ and then paste the copied data to match the destination formatting. Close the webpage.

$5.$ Silvia is also tracking data about sales to new clients. She has two XML files containing client data for May and April, and asks you to import them.

Go to the New Clients worksheet. Open the XML Source pane and add the XML schema Support$\_$EX$365\_2021\_$EOM$10-1\_$MayClients.xsd to the workbook.

$6.$ Map the XML elements as shown in Table $1.$

Table $1$: XML Mappings for the New Clients worksheet

XML Element Cell

COMPANY B$2$

BID C$2$

CONTACT D$2$

PROJECT E$2$

$7.$ Use the XML Map Properties dialog box to set the XML map properties to validate imported and exported data against the schema and append new data to existing XML tables.

First, import the XML data from the file Support$\_$EX$365\_2021\_$EOM$10-1\_$MayClients.xml into the worksheet at cell B$2.$ Next, import the XML data from the file Support$\_$EX$365\_2021\_$EOM$10-1\_$AprClients.xml into the worksheet at cell B$7.$

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.