Posted: April 5th, 2015

Excel Take home exam with written report (Statistic)

Excel Take home exam with written report (Statistic)

Order Description

EVALUATION CRITERIA
Written Report
? Completeness and Correctness
? Presentation: including the clarity of writing, interpretation on the empirical results, clarity and
appropriateness of graphs and tables, the use of notation, introduction/motivation and
conclusion/discussion
? Creativity
?
? Excel file correctness of results produced by the Excel program plus other aspects: generality, documentation and readability
Problem Description
The financial company Giacometti Holding wants to study which are the factors affecting the monthly home mortgage payment or rent payment of her potential customers. In particular, the financial company Giacometti Holding wants to understand the characteristics of the customers, which monthly pay more.
The purpose of the Giacometti’s management is to offer a new financial product: an insurance to hedge against the risk related to the inability to pay a high monthly home mortgage payment or rent payment, in case of a temporary job loss.
As a consultant of the company you are required to suggest a strategy (on which customers do I have to concentrate my attention) to attach the potential market.
The dataset file
The file Data_exam.xlsx contains data from a survey of 500 randomly selected potential customers/householders.
Data Description:
1. Customers/householder: Sequential number.
2. Family size: Number of persons in the family.
3. Location: Location of residence within the given city: 1=SW sector, 2=NW sector, 3=NE sector, and
4=SE sector.
4. Ownership: 0= rent home (house or apartment) or 1=own home.
5. First Income: Annual income of first household wage earner.
6. Second Income: Annual income of second household wage earner (if applicable).
7. Monthly Payment: monthly home mortgage payment or rent payment.
8. Utilities bill: Average monthly expenditure on utilities.
9. Debt: Total indebtedness (excluding home mortgage) in dollars.
Your tasks :
STEP 1 (30 points) Create a sample.
????
Create a sample of 150 customers using the simple random sampling method . Then, all your elaborations must be performed on the sample you create.
Report the main descriptive statistics and draw histograms for the following variables :
1. Family size
2. First Income
3. Second Income
4. Monthly Payment
5. Utilities bill
6. Debt
-Estimate the 99% confidence intervals for the Average Monthly Payment and average debt.
-What proportion of the householders contains a single person. Estimate 99% confidence intervals and test the hypothesis that the population proportion is 20%. Determine the sample dimension to reduce the margin error of the proportion by 50%
-Use Pivot tables to answer the following questions, considering your sample: what are the average monthly payment, average monthly utilities bill, average debt (excluding the home mortgage) of the householders residing in each of the four sectors of the city. Discuss the results
STEP 2 (30 points)
Focus now on the monthly payment as your dependent variable.
Use scatter plots to understand the relevant relationships between the dependent variable and the independent ones, such as the ones reported in “The dataset file” paragraph.
Run a multivariate regression, using as dependent variable the total amount spent and as independent ones independent the ones reported in the bullet points 1-9 above in “The dataset file” paragraph.
Critically comment the significance tests on the beta coefficients, the R^2 and the F-test. Develop a strategy to select only the relevant variables.
Using the regression output, determine with of the explanatory variables should be excluded from the regression equation.
What are the main insights from your analysis?
?
STEP 4 (30 points)
The Consumer Confidence Index (CCI) attempts to measure people’s feelings about general business conditions, employment opportunity and their own income prospects. The file contains the monthly average of CCI.
Use the forecasting method that you consider more appropriate to forecast the next 6 months. Provide motivations for your choice. Can you use this additional information to add some comments for Giacometti Holding?

Expert paper writers are just a few clicks away

Place an order in 3 easy steps. Takes less than 5 mins.

Calculate the price of your order

You will get a personal manager and a discount.
We'll send you the first draft for approval by at
Total price:
$0.00
Live Chat+1-631-333-0101EmailWhatsApp