60.1K

Verified Solution

Link Copied!

Outcomes and Objectives For this assignment, you are to use Excel applications and present visual information about the top six most visited national park 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 Instructions for Top Six National Parks Visit Assignment: Group $1-$ Iasks: Start Excel and open Data File $-$ Week $3$ Homework Assignment file located in the Files folder, directly below the Syyllabus: $1.$ Save the workbook as Lastname$\_$Firstname$\_$Week$3\_$Hw$\_$Assignment using your own name. $2.$ In cell E$10,$ enter a formula to total the visitors for the Current Year. Format cells E$3$:E$10$ with commas and no decimals. $3.$ Create a $3-$D Pie chart using the nonadjacent ranges B$3$:B$8$ and E$3$:E$8.$ Move the chart to a new sheet and name the workshe $3.$ Create a $3-$D Pie chart using the nonadjacent ranges B$3$:B$8$ and E$3$: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

and change the color to Green. Group $2-$ Tasks: $4.$ Change the value in cell E$3$ on Sheet $1$ to $11,338,893$ Note the change in the total and on the pie chart. Format cells A$12$:B$12$ with bold and font color to dark red. $5.$ In cell C$15,$ construct a formula to calculate the number of visitors for the next season after the projected increase of

is applied. Fill the formula through the range

$.$ Format the range

with commas and no decimals. $6.$ Copy the range C$15$:C$20$ and paste the values and number formatting to the range B$23$:B$28.$ Change the Projected Increase in cell B$12$ to

$.$ Now copy the range

and paste using the values and number formatting to range

Group $3-$Tasks: $7.$ Select ceils A$2$:C$8$ 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 B$3$:B$8$ and D$3$:D$8.$ 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 $249.$ Click on Sheet $1$ and select cells B$3$:C$8.$ 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 E$5)$ for the Total Annual Visitors $($cell E$10)$ to be

$.$ 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, $-$ Explade the Great Smokey Mountains slice to

and change the color to Green. $1.$ 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

and hit enter. The Great Smokey Mountains should explode $($move away slightly from the rest slices$).-$ Change the Great Smokey Mountains' color to green $1.$ 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

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 C$15,$ construct a formula to calculate the number of visitors for the next season after the projected increase of

is applied. The correct formula is below"

$-$ Fill the formula through the range C$16$:C$20.$ Format the range B$15$:C$20$ with commas and no decimals. With the formula

$,$ dragging the fill handle works. Note: work with just

without the

sign with it$.$ Group $4-$ Tasks #$10$: $-$ On Sheet $1$ use the Goal Seek to determine the number of visitors needed in Zion National Park $($cell E$5)$ for the Total Annual Visitors $($cell E$10)$ to be

$.$ Note the change in the Pie Chart. $1)$ Select cell E$10$ 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 E$10.7)$ To value: $35,000,000,8)$ By changing cell $=$ E$59)$ Click ok$.$ 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$ Assianment $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. $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.

Answer & Explanation Solved by verified expert

See Answer