Project: Part $3$

In this lab, you will be applying the techniques learned in class to the data for the chosen company. The

goal in part $3$ is to focus on sampling, create a few more visualizations, and run a regression. Regression

analysis is a key part of statistics.

Complete the steps below:

$1.$ Filter the Age variable in the Board Data tab and delete the rows of the six individuals who do

not have an age recorded $($normally$,$ you would never delete data like this but for the sake of

this lab we don$$t want blank ages$)$

$2.$ In this step, calculate the probability that a random sample of $50$ board members will be $59$

years old or less. Listed below are some hints to complete this step:

a$.$ You do NOT need to pull a sample for this question! Just do the math in Excel

b$.$ Review chapter $7$

c$.$ You will need to calculate:

$$ Standard deviation of the population

$$ Standard error of the mean

$$ Z$-$score $($then look up in z$-$score table$)$

d$.$ Find the probability and record your answer in a new Word document.

e$.$ Is it likely to pull a sample with an average age of $59$ or less? Write your answer in a

Word document.

$3.$ Repeat the process from step $2,$ but with a sample size of $500.$

a$.$ You do NOT need to pull a sample for this question! Just do the math in Excel

b$.$ Are you more or less likely to pull a sample with an average age of $59$ or less when the

sample size is larger? Write your answer in a Word document.

c$.$ Think critically about the answers you found. Why did the probability change? Be

specific. Write your answer in a Word document.

$4.$ From the Board Data, draw a sample of $500$ from the Age column:

a$.$ Highlight the Age column, navigate to the Data Analysis button, then find $$Sampling$$ in

the list of options. Make sure you highlight the Age column as the input range, include

labels, choose random as the sampling method, the input $500$ for the number of

samples. Make sure the output options are in a new worksheet.

b$.$ Calculate the average age of the sample.

c$.$ Find the probability that the sample had the average age you calculated above in part b

d$.$ How does the average age of the sample and the probability of collecting that sample,

compare to the answers found in steps $2$ and $3?$ Write your answer in a Word

document.

e$.$ Why do you think your sample had an average age closer to the population age? Write

your answer in a Word document.

$5.$ Construct a confidence interval:

a$.$ Review section $8\mathrm{.}2$

b$.$ Construct a $95\%$ confidence interval using data from your sample in step $4$

c$.$ Write the confidence interval in a Word document and interpret it accurately.

$6.$ Create a pie chart for the Ethnicity variable and label everything correctly.

a$.$ Hint: pivot tables are useful for grouping data.

$7.$ Create a pie chart for the $$Female$?$ variable and label everything correctly.

a$.$ Hint: pivot tables are useful for grouping data, but this time you will have to count the

number of blanks as $$male$.$

$8.$ Running a Simple Regression using Data Analysis

a$.)$ Copy the Physical Risk$-$Drought, Physical Risk$-$Tropical Cyclone, Physical Risk$-$Heat Stress,

Physical Risk$-$Wildfire, Physical Risk$-$Flood and Physical Risk$-$Most Likely Total columns from

the climate data sheet into a new sheet.

b$.)$ For this regression, the Physical Risk$-$Most Likely Total variable will be the dependent

variable, with the rest being independent variables.

c$.)$ Clean the data by deleting any column with empty values in the new sheet. One way to do

this is by sorting smallest to largest per column $($While expanding the selection so all of the

values stay linked$)$ and scrolling down to the bottom to see if any blanks were sorted out.

d$.)$ On the data analysis dropdown, click regression to begin running the regression function on

excel. The Y input should be your dependent variable $($the total risk column$),$ with the x

input being the rest. Make sure to check the $$labels$$ box and when inputting the data, click

on the label cell and hold control shift and down to highlight all of the cells. If you select the

entire column by clicking the column label instead, the function will not work. Check off

confidence level $(95\%)$ and create the regression in a new sheet. PASTE RESULTS INTO

YOUR WORD DOCUMENT.

e$.)$ ANSWER: What do the results show? What does the R squared mean? Are the variables

significant? What are we testing for here? Please answer in a paragraph or two.

$9.$ Save your Word document with your name and part as the title $($example: Name$\_$Part $3).$

Deliverables:

$$ Ensure your submissions are professional, neatly organized, and well written

$$ Submit the Word document with each step clearly labeled to Canvas