Shelly Cashman Excel 365 | Module 7: SAM Project B Park & Yang Event Planning

GETTING STARTED

1. Save the file SC_EX365_7B_FirstLastName_1.xlsx as SC_EX365_7B_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

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

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

Support_EX365_7B_Conferences.png

Support_EX365_7B_Events.docx

Support_EX365_7B_Seminars.png

Support_EX365_7B_Shows.png

Support_EX365_7B_Team.html

3. With the file SC_EX365_7B_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

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

PROJECT STEPS

1. Hana Park is a partner at Park & Yang Event Planning, a firm that coordinates events for businesses. She is preparing a workbook that will be given to prospective clients to describe the company's services and performance. She asks for your help in importing data and adding other content to the workbook. Go to the Planning Team worksheet. Add Company logo as the alt text for the picture in the range B2:B3.

2. In cell B2, apply the Fill: Aqua, Accent Color 1; Shadow WordArt Style (2nd column, 1st row in the WordArt Styles gallery) to the shape containing the worksheet title "Park & Yang" to coordinate it with the other worksheet title in the workbook.

3. The worksheet should list information about the event planning team, which is contained in a webpage. Import data from the webpage as follows:

a. Open the webpage Support_EX365_7B_Team.html in a browser.

b. In the Planning Team worksheet, import the data from the Park_&_Yang_Event_Planning_Team part of the Support_EX365_7B_Team webpage as a table in cell H13 of the existing worksheet. [Mac Hint: Import the data from HTML. Open the file Support_EX365_7B_Team.html, and then copy and paste the data from the range A3:G9 to the range beginning in cell H13.]

c. Clear the formats from the imported data in the range H13:N19 to remove the webpage formatting.

d. Apply the Aqua, Table Style Light 9 style to the imported table.

e. Rename the table using PlanTeam as the name.

4. Hana wants you to list the team information in the range B6:F11. The webpage table separates the first and last names, but Hana wants to list the full name on the Planning Team worksheet. List the first and last names of each team member in a single cell as follows:

a. In cell B6, enter a formula using the CONCAT function that displays the first name shown in cell I14 followed by a space (" ") and then the last name shown in cell J14.

b. Fill the range B7:B11 with the formula in cell B6 to list the full names of the remaining team members.

5. Incorporate the imported data in the range C6:F11 as follows:

a. Copy the Title data from the range K14:K19 and paste only the values in the range C6:C11.

b. In cell D6, enter a formula using the PROPER function to capitalize the first letter in each word in the Responsibility text in cell L14.

c. Fill the range D7:D11 with the formula in cell D6 to list the responsibilities of the remaining team members.

d. In cell E6, enter a formula using the LEFT function to insert the first 2 characters on the left of cell M14. Copy the formula in cell E6 to the range E7:E11.

e. In cell F6, enter a formula using the RIGHT function to insert the last 2 characters on the right of cell N14. Copy the formula in cell F6 to the range F7:F11.

f. Hide rows 13 to 19 so that the worksheet does not display duplicated data.

6. On the Events worksheet, Hana already imported data summarizing the most successful events the company coordinated in recent years. She wants to display the data in the range H6:I11 of the Planning Team worksheet. She asks you to switch the rows and columns when you insert the data to fit in the range H6:I11. Insert the imported data as follows:

a. Go to the Events worksheet. Copy the data in the range B2:G3, and then transpose the rows and columns as you paste the data on the Planning Team worksheet starting in cell H6.

b. Resize column H to its best fit.

7. On the Planning Team worksheet, Hana asks you to provide one diagram with an organization chart to show the hierarchy of the event planning team and another diagram illustrating the signature events. Insert an organization chart as follows:

a. Insert the Organization Chart SmartArt from the Hierarchy section of the SmartArt gallery.

b. Move and resize the SmartArt so that the upper-left corner is in cell B20 and the lower-right corner is in cell E32.

8. Add text to the SmartArt as follows, using Figure 1 as a guide:

a. Enter Moreno in the top shape.

b. Enter Powell in the second shape.

c. Add a shape after the "Powell" shape so that it appears to the right of the "Powell" shape.

d. Enter Byrd in the new shape.

e. In the bottom row, enter Chen in the left shape.

f. Enter Moss in the middle shape.

g. Enter Barba in the right shape.

Figure 1: SmartArt Text

9. Insert a diagram to illustrate the top three event types as follows:

a. Insert the Bending Picture Caption SmartArt from the Picture section of the SmartArt gallery.

b. Add a third shape to the SmartArt.

c. Move and resize the SmartArt so that the upper-left corner is in cell F20 and the lower-right corner is in cell J32.

10. Add text and pictures to the SmartArt as follows, using Figure 2 as a guide:

a. Enter Conferences in the upper-left shape.

b. Enter Seminars in the upper-right shape.

c. Enter Trade Shows in the bottom shape.

d. Insert the picture Support_EX365_7B_Conferences.png in the upper-left shape.

e. Insert the picture Support_EX365_7B_Seminars.png in the upper-right shape.

f. Insert the picture Support_EX365_7B_Shows.png in the bottom shape.

Figure 2: SmartArt Text and Pictures

11. Go to the Cost Benefit worksheet, which Hana asks you to finish. The worksheet shows sample data for events to illustrate the benefits clients typically receive when working with Park & Yang Event Planning. The data for the range B21:D28 is stored as text in a Word document. Include the text in the Cost Benefit worksheet as follows:

a. Open the Word document Support_EX365_7B_Events.docx.

b. Copy all the paragraphs.

c. Paste the text in cell B21 of the Cost Benefit worksheet to match destination formatting.

d. Convert the text in the range B21:B28 to columns using a comma delimiter. Use the General column data format and replace the data already in the range.

12. Hana asks you to help clients visualize the data in the range B21:G28. Create a conditional formatting rule that adds Gradient Fill Blue Data Bars to the range G22:G28.

13. Insert a chart as follows to compare the expenses with the sales for each event:

a. Based on the nonadjacent data in the Event (range B21:B28), Expenses (range D21:D28), and Sales (range E21:E28) columns, insert a 2-D Clustered Bar chart.

b. Move and resize the chart so that its upper-left corner is in cell B6 and its lower-right corner is in cell G19.

14. Modify the bar chart as follows to make it easier to interpret:

a. Remove the chart title because the legend already identifies the data.

b. For the Sales data series only, add data labels to the outside end of each bar.

15. Hana wants to make sure that clients reviewing the worksheet understand it displays sample data. Add a shape to the worksheet as follows to provide this information:

a. In cell G4, insert an Arrow: Left shape from the Block Arrows section of the Shapes gallery.

b. Resize the arrow to a width of 1.2" without changing the height.

c. Move the arrow so that its tip is on the right border of the range F4:F5.

d. Type Sample data in the arrow.

e. Apply the Subtle Effect – Aqua, Accent 1 shape style to the arrow.

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.

Final Figure 1: Planning Team Worksheet

Final Figure 2: Events Worksheet

Final Figure 3: Cost Benefit Worksheet