HOW DO YOU DO THIS ON EXCEL?? Please put the formulas!!! People Boxes, Inc. is a

Question

HOW DO YOU DO THIS ON EXCEL?? Please put the formulas!!!

People Boxes, Inc. is a consortium of real estate owners who seek out growing real estate demand among young, affluent, highly-educated workers entering the workforce or relocating to new cities. They specialize in condominium-style new construction, saving on costs by reusing blueprints in different cities and working with national contracting firms. Their revenue growth depends on identifying new markets to expand into, hlling a niche in high-demand, high-income cities They have collected data on demographics and income in a number of metropolitan statistical areas (MSAs) in the United States and would like assistance in analyzing the data to provide some background information and some conclusions on the underlying relationships of income and age, education levels, and rental prevalence, as well as the determinants and effects of the supply of housing stock.

Explanation / Answer

Create a new variable City type:- 1. Young, 2. Older

Use this formula- =IF(13<CityAge<25,”Young”,”Older”)

Q1.

Calculate the median HH Income of the median income of the Whole Dataset-

=Median(HH median Income Range)

Calculate the average median HH income in Young city only

=AVERAGEIF(Select Range of City Type, Put Condition as “Young” or use cell referencing, Select Data range of median HH income)

Check whether Young city average median income is higher than median of HH median income

Q2.

=AVERAGEIF(Select Range of City Type, Put Condition as “Older” or use cell referencing, Select Data range of median HH income)

= STDEV.P(Select Data range of median HH income)/SQRT(No. of observation)

= =AVERAGE(Select Data range of median HH income)

Hypothesis

H0- Mean of the median of older cities = Population mean

H1- Mean of the median of older cities <> Population mean

Calculating C.I. for the dataset

=NORMSDIST((A-C/B))

Hypothesis: One Tail Test

H0- Mean of no. of rental unit in young cities >= Population mean

H1- Mean of no. of rental unit in young cities < Population mean

Calculating Z value for the dataset or you can use Z test function directly

= (A-B)/C

Q5.

A.       Average No. of persons working from home in the rental in the USA-

=AVERAGE(Select Data range of no. of persons working from home)

B.       Average No. of persons working from home in the rental in Young City-

=AVERAGEIF(Select Range of City Type, Put Condition as “Young” or use cell referencing, Select Data range of no. of persons working from home)

C.       Calculate Standard error of population-

= STDEV.P(Select Data range of no. of persons working from home)/SQRT(No. of observation)

Hypothesis: Two-Tailed Test

H0- Mean of no. of the rental unit in young cities <> Population mean

H1- Mean of no. of the rental unit in young cities < Population mean

Calculating Z test & CI for the dataset

=NORMSDIST((A-C/B))

Q3.

=AVERAGE(Select Data range of no. of the rental unit in a city)

=AVERAGEIF(Select Range of City Type, Put Condition as “Young” or use cell referencing, Select Data range of no. of a rental unit in a city)

= STDEV.P(Select Data range of no. of a rental unit in a city)/SQRT(No. of observation)

Hypothesis

H0- Mean of no. of the rental unit in young cities >= Population mean

H1- Mean of no. of the rental unit in young cities < Population mean

Calculating Z test for the dataset

= (A-B)/C

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.