Question Description

You are developing a demographic analysis of Miami-Dade County’s population. Follow the steps to produce the results shown in the figures at the end of this document.
Notes:
This workbook has 2 worksheets: the Documentation worksheet (for the history of this workbook), and the Population worksheet. You will create a 3rd worksheet named Selected Demographics later in the project. Be sure to work on the correct worksheet as indicated in the instructions.
You should use cell references in all formulas – do not re-enter data.
Use the images at the end of this document to validate the correctness of your work.

Project 3 Instructions

Starting File: Project 3 – County Population – Student.xlsx

You are developing a demographic analysis of Miami-Dade County’s population. Follow the steps to produce the results shown in the figures at the end of this document.

Notes:

  • This workbook has 2 worksheets: the Documentation worksheet (for the history of this workbook), and the Population worksheet. You will create a 3rd worksheet named Selected Demographics later in the project. Be sure to work on the correct worksheet as indicated in the instructions.
  • You should use cell references in all formulas – do not re-enter data.
  • Use the images at the end of this document to validate the correctness of your work.
  • Remember:  Project work must be your own individual work – no group work allowed!

Steps:

Download the starting file from the Assignment Dropbox and save it on your computer.  Then open it to work on it.

  1. In the Documentation worksheet, enter your name in cell B3 and the date you created this workbook in cell B4.
  2. In cell B5, enter a short description of the purpose of this workbook.
  3. Format cell A1 with the Title style.
  4. Add all borders around A3:B5. Expand column B to contain your purpose statement.  Wrap the text if needed.          

Switch to the Population worksheet. Use formulas with cell references to complete these steps.

  • Merge and center cell A1 across A1:T1. Format with the Title style. Left justify cell A1.
  • Enter formulas in T4:T15 to calculate the total population for each of the demographic segments.
  • Enter formulas in B16:T16 to calculate the total population for each age group.
  • Format B4:T16 with the Number format and no decimal places.
  • In cell U4, create a line style Sparkline, using the date in row 4.  Set the marker color to be red. Do likewise for the rest of the data rows. Verify your results with Figure 1.
  • Create a Line Chart with Markers showing the total population by age segments, as shown in Figure 2. Position this chart at B19:G35. Format as follows:
    • Change the chart title to Total Population by Age Segments.
    • Rotate the text in the horizontal axis to 270 degrees. (Hint: Use the Text direction dropdown list for this.)
    • Change the line and marker colors to Orange Accent 2 Darker 25%.
    • Format the vertical axis options to have major units set to 50,000 and minor units set to 25,000.
    • Add Primary Major Vertical gridlines and Primary Minor Horizontal gridlines.
    • Format the plot area to have no border. Set the fill to be gradient, with a preset gradient named Light Gradient Accent 2.
    • Add a border to the entire chart with color Orange Access 2 Darker 50%.
  • Set the page orientation to Landscape, and the Margins to Narrow. Open the Custom Margins setting and adjust the settings provided by the Narrow choice so that top and bottom margins are now set to 0.25. Set Col. A to repeat on each page. You should have 3 pages, with data through age 30-34 and chart on the first page, and data from age 35-39 to 65-69 on the second page, and the rest of the data on the last page. There should be no scaling for print.
  • Insert a page footer with your name in the right-hand section, and the date you completed this worksheet in the right section. Use Page Break Preview to ensure that your print options are set up correctly on the Population worksheet.
  • Create a new worksheet named Selected Demographics. Move it to the last position in the tabs.
  1. Return to the Population worksheet to create a Line Chart showing all race and age segments, as shown in Figure 3. Format at follows:
    1. Move this chart to the Selected Demographics worksheet, and position it at B3:M26.
    1. Change the chart title to Population by Race and Age Segments.
    1. Rotate the text in the horizontal axis to -45% using the Custom angle option.
    1. Set the Maximum Bounds on the vertical axis to 60000. Set the Major units to 10,000 and the Minor units to 5,000.
    1. Add Primary Major Vertical gridlines and Primary Minor Horizontal gridlines.
  2. Return to the Population worksheet to create a Clustered Column Chart showing the black population by age segment, as shown in Figure 4. Format this chart as follows:
    1. Move this chart to the Selected Demographics worksheet and position it at B28:M42.
    1. Move the legend to the top.
    1. Change the title to Black Population by Age Segment.
    1. Set the Maximum Bounds on the vertical axis to 25000. Set the Major units to 5,000 and the Minor units to 2,500.
    1. Add Primary Major Vertical gridlines and Primary Minor Horizontal gridlines.
  3. Return to the Population worksheet to create a 3-D pie chart showing senior citizens’ totals by age segment as shown in Figure 5.  Include ages 60 and above. Format this chart as follows:
    1. Move this chart to the Selected Demographics worksheet and position it at O3:U18.
    1. Change the chart title to Senior Citizens by Age Segment.
    1. Change the chart style to Style 4.
    1. Move the legend to the top.
    1. Add data labels on the outside ends.
    1. Format data labels to show percentages and the legend key.
    1. Explode the slice for age 65-69.
  4. On the Selected Demographics worksheet, add a title of Selected Demographics in cell B1. Merge and center B1 across B1:U1, and format with the Title style.
  5. Set the page orientation to Landscape, and the Margins to Narrow.
  6. Use Page Break Preview to remove any page breaks on the Selected Demographics worksheet.
  7. Use File – Print to determine if you need to do custom scaling to make all charts appear on a single page.
  8. Save your file, close Excel, and submit into Canvas.

Do you have a similar assignment and would want someone to complete it for you? Click on the ORDER NOW option to get instant services at essayloop.com