Male and Female Workers

Calculate the ratio of Male to Female workers in the US. Provide the results as a line and a stacked column charts. Use a cell reference or a single formula where appropriate.

Steps to Perform:
1) Start Excel. Download and open the workbook named:
Chapter_2-9_MF_workforce_Start
2) In cell F7, calculate the total employment ratio for the year 1870. Fill cell F7 down the column to cell F21.
3) In cell G7, calculate the number of male workers in 1870. Fill cell G7 down the column to cell G21. Format cells G7:G21 as Number with 3 decimal places.
4) In cell H7, calculate the number of female workers in 1870. Fill cell H7 down the column to cell H21. Format cells H7:H21 as Number with 3 decimal places.
5) In cell I7, calculate the percentage ratio of female to male workers labor force participation rate in 1870. Fill cell I7 down the column to cell I21. Format cells I7:I21 as Number with 3 decimal places.
6) In cell L23, determine if the percentage ratio of female to male workers can be more than 100.
7) In cells U11:AB25, insert a Line Chart to show the number of male and female workers over time. Select range G6:H21, on the Insert tab, click Recommended Charts, then open All Charts tab, then click Line, and choose the Line chart. Use cell references to G6 and H6 for Series names. Select A7:A21 as Horizontal Axis Labels. Apply Style 1 on the Design tab. Add a chart title above the chart axis titles. Replace Chart Title with Labor Force Participation by Gender. Add axis titles. Replace Axis Title for the horizontal axis with Years and Axis Title for the vertical axis with Numbers in Millions. Add legend at the bottom of the chart.
8) In cells U30:AC45, insert a 100% Stacked Column Chart to show the number of male and female workers over time in percentages. Select range G6:H21, on the Insert tab, click Recommended Charts, then open All Charts tab, then click Column, and then click 100% Stacked Column. Use cell references to G6 and H6 for Series names. Select A7:A21 as Horizontal Axis Labels. Apply Style 5 on the Design tab. Add a chart title above the chart axis titles. Replace Chart Title with Civilian Labor Force by Sex 1870 - 2012 (NUMBERS IN MILLIONS). Add axis titles. Replace Axis Title for the horizontal axis with Years. Add legend at the bottom of the chart.

Use a cell reference or a single formula where appropriate.
The spreadsheet shows the part-to-part ratio of Male to Female workers in the US.
a.) The ratio 85 : 15 is part-to-part, male to female workers for the year 1870. Compute the Whole for this ratio by adding the numbers together.
b.) Compute the number of Male and Female workers for each year given using the part-to-whole ratios. Format to three decimal places.
c.) The World Bank computes the ratio of female to male workers labor force participation rate (%) for each country. Compute this statistic by taking the ratio of female to male workers and scale the second quantity to 100. Format to three decimal places.
d.) Is it possible for a country to have the percentage ratio of female to male workers be over 100?
Over 100?
e.) Create a well labeled line graph showing the number of male and female workers over time. You should have one line for male and one line for female using two different colors by years, axes labeled and an appropriate title. A key showing lables for each of the two color lines should be included. Axis labels and title should be chosen from the list:
a. Years
b. Numbers in Millions
c. Labor Force Participation by Gender
f.) Create a 100% stacked column chart showing the number of male and female workers over time similar to the one shown to the left created by the BLS. Label the horizontal axis, include a title and show a key for the two colors used in the stacked columns representing male and female. Colors should align with the colors selected for part e.).
Axis label and title should be chosen from the list:
a. Years
b. Civilian Labor Force by Sex 1870 - 2012 (NUMBERS IN MILLIONS)