Abdul Khoury is an assistant sales manager at Wang Marketing, a full$-$service agency in New York City that provides advertising, public relations, and marketing for clients around the world. Abdul is preparing a workbook to summarize the agency's revenue and expenses in the past month. He will submit similar workbooks to the agency's bankers once a month as part of their loan agreement with the bank. Abdul asks for your help in importing data and adding other content to the workbook.

Go to the Advertising worksheet. Insert the picture Support$\_$EX$365\_2021\_$EOM$71\_$Mac$\_$Logo.png to add a picture of the agency's logo to the worksheet. Move and resize the picture so its upper$-$left corner is in cell A$1$ and its lower$-$right corner is in cell G$7.$ Add the alt text Wang Marketing logo for people using screen readers.

Abdul needs to import data from the New York, Chicago, and Los Angeles offices. The New York office provided its monthly data in a text file. Import the data from text from the file Support$\_$EX$365\_2021\_$EOM$7-1\_$Mac$\_$NewYork.csv in the Advertising worksheet beginning in cell A$10.$ Separate the data using a comma delimiter.

The Los Angeles office provided its monthly data as a webpage file. Import the data from the file Support$\_$EX$365\_2021\_$EOM$7-1\_$Mac$\_$LosAngeles.htmI, specifying a path such as c:$\backslash$users$\backslash$username$\backslash$documents$\backslash$projects$\backslash$Support$\_$EX$365\_2021\_$EOM$71\_$Mac$\_$LosAngeles.html$.$ Select all the data except the first column $($Hint: Select data in range B$3$:D$7),$ and then copy and paste the values in the Advertising worksheet beginning in cell A$22.$

Format the ranges as tables with headers and then apply Dark Red, Table Style Light $9$ to each of the three tables to coordinate with the rest of the workbook. Remove the filter buttons from the header rows. Format the $ Revenue and $ Expense data in the bottom two tables using the Comma number style that has no decimal places.

Shelly Cashman Excel $365/2021|$ Module $7$: End of Module Project $1$

$5.$ Abdul needs to calculate the profit from type of ad at the three office locations. Insert a column in each table to the right of the $ Expense column $($Hint: To complete this, just enter the column headings in cells D$10,$ D$16,$ & D$22),$ Use $ Profit as the name of each new column. In cell D$11,$ enter a formula without using a function that uses cell references to subtract the expense amount in cell C$11$ from the revenue amount in cell B$11.$ Copy the formula in cell D$11$ to the range D$12$:D$14($if necessary$),$ the range D$17$:D$20,$ and the range D$23$:D$26.$Abdul needs to calculate the profit from type of ad at the three office locations. Insert a column in each table to the right of the $ Expense column $($Hint: To complete this, just enter the column headings in cells D$10,$ D$16,$ & D$22).$ Use $ Profit as the name of each new column. In cell D$11,$ enter a formula without using a function that uses cell references to subtract the expense amount in cell C$11$ from the revenue amount in cell B$11.$ Copy the formula in cell D$11$ to the range D$12$:D$14($if necessary$),$ the range D$17$:D$20,$ and the range D$23$:D$26.$

Abdul wants to display the profit from each office for each type of ad$.$ In cell G$10,$ enter a formula using the SUM function that totals the Print profit values $($cells D$11,$ D$17,$ and D$23).$ Fill the range G$11$:G$13$ with the formula in the cell G$10.$

Abdul also wants to display the total profit from the four types of ads the agency created in the past month. In cell G$14,$ create a formula using the SUM function to insert the total project amount from the range G$10$:G$13.$

Abdul wants to include a bar chart that compares the profit totals for the four types of ads the agency creates. Insert a Clustered Bar chart based on the data in the range F$9$:G$13.$ Move and resize the bar chart so it covers the range A$27$:G$42.$

Modify the bar chart to make it stand out. Format the bars by applying the Colored Fill Dark Red, Accent $1$ shape style and the Offset: Bottom shape effect from the Outer section of the Shadow gallery.

Abdul wants to call attention to the revenue from Web ads. Insert a Text Box from the Basic Shapes section of the Shapes gallery. Type Revenue from Web ads is increasing in the text box. Resize the text box to a height of ${0\mathrm{.}5}^{\text{'}\text{'}}$ and a width of $1\mathrm{.}75",$ and then move the text box so its upper$-$left corner is in cell G$26$ and its lowerright corner is in cell H$28.$ Apply the Colored Outline $-$ Dark Red, Accent $1$ shape style to the text box to highlight it on the worksheet.

Go to the Organization Chart worksheet, which includes a worksheet title formatted as WordArt. Change the text fill color of the WordArt to Dark Red, Accent $1$ to use one of the colors from the agency logo$.$

The SmartArt on the Organization Chart worksheet shows the managers in the agency and their relationship to one another. Abdul does not have photos of all the managers, so he wants to change the SmartArt layout to one that does not use pictures. C