Search the web 20220715_061611.jpg Templates Browser 11 Q 30% Outcomes and Objectives For this assignment, you are to use Excel applications and present visual information about the top six most visited national parks The specific learning outcomes and objectives for this assignment are as follows: Learning Outcomes #1 Present data in a pie chart. . Objectives: 1. Chart Data with a Pie Chart 2. Format a Pie Chart 3. Edit a Workbook and Update a Chart 4. Use Goal Seek to Perform What-If Analysis Learning Outcomes #1 - Make projections using what-if analysis and present projections in a line chart. Objectives: 1. Design a Worksheet for What-If Analysis 2. Answer What-If Questions by Changing Values in a Worksheet 3. Chart Data with a Line Chart 4. Create a Map Chart and a Funnel Chart 5. Analyze Trends with a Line Chart Type here to search Instructions for Top Six National Parks Visit Assignment: Group 1 - Tasks: Start Excel and open Data File - Week 3 Homework Assignment file located in the Files folder, directly below the Syllabus: 1. Save the workbook as Lastname_Firstname_Week3_Hw_Assignment using your own name. 2. In cell E10, enter a formula to total the visitors for the Current Year. Format cells E3:E10 with commas and no decimals. 3. Create a 3-D Pie chart using the nonadjacent ranges B3:B8 and E3:E8. Move the chart to a new sheet and name the workshee Pie Chart Add the Chart Title: Current Year Vicitors 82F Sunny 9:16 AM 7/16/2022 Search the web 20220715_061721.jpg Templates Browser Type here to search 3. Create a 3-D Pie chart using the nonadjacent ranges B3:B8 and E3:EB. Move the chart to a new sheet and name the worksheet Pie Chart Add the Chart Title: Current Year Visitors with formatting WordArt Style Fill Black, Text color 1; Shadow and font size 24 Delete the legend and add data labels Category Name and Value with position inside end. Format the labels with font size 11 and bold. Explode the Great Smokey Mountains slice to 15% and change the color to Green. Group 2 - Tasks: 4. Change the value in cell E3 on Sheet 1 to 11,338,893 Note the change in the total and on the pie chart. Format cells A12:B12 with bold and font color to dark red. 5. In cell C15, construct a formula to calculate the number of visitors for the next season after the projected increase of 3% is applied. Fill the formula through the range C16:C20. Format the range B15:C20 with commas and no decimals. 6. Copy the range C15:C20 and paste the values and number formatting to the range B23:B28. Change the Projected Increase in cell B12 to 5%. Now copy the range C15:C20 and paste using the values and number formatting to range C23:C28. Group 3 - Tasks: 7. Select cells A2:C8 and insert a Map Chart showing the states and National Parks. Move the Chart to a new sheet named Map Chart Add the Chart Title Top Six National Parks Format with any Word Art Style and font size 24 Remove the legend and add data labels. 8. Make sure that Sheet 1 is still the active sheet. Select the nonadjacent cells B3:B8 and D3:D8. Insert a funnel chart showing the square miles for the National Parks. Move the Chart to a new sheet named Funnel Chart Add the Chart Title Size of National Parks Format the title with Text Outline Red and font size 24 9. Click on Sheet 1 and select cells B3:C8. Insert a Line with Markers Chart showing the year the National Park was established. Move the chart to a new sheet named Line Chart Add the Chart Title Year National Park Established and format with font size 24 and Text Fill dark red. Format the Horizontal (Category) Axis with Gradient Fill-Light Gradient Accent 1. Format the Vertical Value Axis Bold. Group 4 - Tasks: 10. On Sheet 1 use the Goal Seek to determine the number of visitors needed in Zion National Park (cell E5) for the Total Annual Visitors (cell E10) to be 35,000,000. Note the change in the Pie Chart. 11. Rename Sheet 1 to National Parks and make it the first sheet. 12. Change the orientation to Landscape, horizontally center the sheets, and insert a custom footer with the file name in the left H 11 Q 30% 79F C 9:16 AM 82F Sunny 7/16/2022 Search the web 20220715 131815.jpg Type here to search 1 2 3 Tennessee 4 Arizona 5 Utah 6 Colorado 7 California Templates O Browser Q 0 Top Six Most Visted National Parks Visitors State National Park Year Established Square Miles Current Year Great Smokey Mountains 1934 816 11538893 Grand Canyon 1919 1904 6254238 Zion 1919 229 4504812 Rocky Mountain 1915 415 4437215 Yosemite Yellowstone 1890 1200 4336890 1872 3472 4116524 8 Wyoming 9 10 Total Annual Visitors 11 12 Projected Visitor Increase 13 14 National Park 15 Great Smokey Mountains 16 Grand Canyon 17 Zion 18 Rocky Mountain 19 Yosemite 20 Yellowstone Visitors Current 3% Year Projected Number 1138893 6254238 4504812 4437215 4336890 4116524 Ready Sheet1 + Accessibility: Good to go Type here to search 521 11 Q 30% 82F Sunny 9:18 AM 7/16/2022 Search the web 20220715 131837.jpg Type here to search 13 14 National Park 15 Great Smokey Mountains 16 Grand Canyon 17 Zion 18 Rocky Mountain 19 Yosemite 20 Yellowstone 21 22 National Park 23 Great Smokey Mountains 24 Grand Canyon Templates Browser Visitors Current Year Projected Number 25 Zion 26 Rocky Mountain 27 Yosemite 28 Yellowstone 29 30 31 32 33 34 35 36 Sheet1 + Ready Accessibility: Good to go Type here to search 521 1138893 6254238 4504812 4437215 4336890 4116524 3% 5% 11 Q 30% 82F Sunny 9:18 AM 7/16/2022 Dashboard Week 3 Homework Assignment x + File C:/Users/lader/Downloads/Week%203%20Homework%20Assignment%20-Tips.pdf 1 of 3 Group 1 - Tasks #3: Page view A Read aloud Add text V Draw Useful Tips Highlight Erase Explode the Great Smokey Mountains slice to 15% and change the color to Green. 1. With the entire pie chart selected, Click the Great Smokey Mountains slice to select it. Type here to search 2. In the Format Data Point pane, under Series Options, in the Point Explosion box, type 15% and hit enter. The Great Smokey Mountains should explode (move away slightly from the rest slices). 1. Change the Great Smokey Mountains' color to green With the entire pie chart selected, Click the Great Smokey Mountains slice to select it. 2. In the Format Data Point pane, under Series Options, in the Point Explosion box, type 15% and hit enter. The Great Smokey Mountains should explode (move away slightly from the rest slices). 3. Then click Fill to expand its options. Click the Gradient fill option button, scroll down and click the Preset gradients arrow, and then in the fourth row, click the last gradient (green). You may have to scroll down to Gradients Stops and Color to make it green. Group 2 - Tasks #5: In cell C15, construct a formula to calculate the number of visitors for the next season after the projected increase of 3% is applied. Note that 3% is in B12. The correct formula is below" 91F Sunny 11:09 AM 7/19/2022 Dashboard Week 3 Homework Assignment x + File C:/Users/lader/Downloads/Week%203%20Homework%20Assignment%20-Tips.pdf of 3 Page view A Read aloud Add text V Draw Highlight Erase Note that 3% is in B12. The correct formula is below" =B15*(1+$B$12) Fill the formula through the range C16:C20. Format the range B15:C20 with commas and no decimals. With the formula =B15*(1+$B$12), dragging the fill handle works. Note: It won't work with just =B15*(1+B12) without the $ sign with it. Type here to search Group 4 - Tasks #10: On Sheet 1 use the Goal Seek to determine the number of visitors needed in Zion National Park (cell E5) for the Total Annual Visitors (cell E10) to be 35,000,000. Note the change in the Pie Chart. 1) Select cell E10 and click Data on the home menu. 2) On the third to the last item on the right 3) Click What-if Analysis. 4) Select Goal Seek. 5) A little box appears. 6) Leave Set cell at E10. 7) To value: 35,000,000. 8) By changing cell = E5 9) Click ok. 91F Sunny 11:09 AM 7/19/2022 Dashboard of 3 Week 3 Homework Assignment x + File C:/Users/lader/Downloads/Week%203%20Homework%20Assignment%20-Tips.pdf Type here to search Page view A Read aloud Add text V Draw Highlight Erase The tags and subject The tags and subject thing do not add any value to a worksheet. It is for file identification purposes. They don't appear in print preview or when a document is printed. The author included the task to complicate the already complicated tasks. To access Tags and Subject in MS 365 Excel do the following: 1. With an opened Excel workbook, click File, Info 2. To the extreme right is Properties. Under properties in the third row or item is Tags. 3. Clicking on the dropdown arrow to the right of Properties take you to Advanced properties. 4. Clicking on Advanced properties, the second item is subject. More Useful Tips for Week 3 Assignment 1. How do you format a fill color in Excel? Under Drawing Tools, on the Format tab, in the WordArt Styles group, click the arrow next to Text Fill, and then do one of the following. To add or change a fill color, click the color that you want. To choose no color, click No Fill. 91F Sunny 11:09 AM 7/19/2022 Dashboard Week 3 Homework Assignment x + File C:/Users/lader/Downloads/Week%203%20Homework%20Assignment%20-Tips.pdf of 3 Type here to search Page view A Read aloud Add text V Draw Highlight Erase 2. How to add category and percentage data labels in the inside end position in Excel? Add data labels Click the chart, and then click the Chart Design tab. Click Add Chart Element and select Data Labels, and then select a location for the data label option. Note: The options will differ depending on your chart type. If you want to show your data label inside a text bubble shape, click Data Callout. 3. How do you add a category name in Excel? Image result for Add Category Name and Value with position inside end in excel Select your chart in Excel, and then click Design > Select Data. Click on the legend name you want to change in the Select Data Source dialog box, and click Edit.... Type a legend name into the Series name text box, and click OK. 4. Exploding a Single Pie Slice Click your pie chart to select it. To select a pie slice, click it once. Click and drag the selected slice outward. The selected slice appears exploded. 91F Sunny 11:09 AM 7/19/2022