Part 1

Section 1: Lookup + Text functions (match, index or V/H lookup functions, etc.), IfError, Validation etc.

Section2 - Pivot tables

Section3 - Chart (Frequency functions, etc.)

Describe the functions used.

If there is an alternative approach to get the same outputs, if yes, then the reason/s for choosing the function that you have used.

Interpretation of summary statistics.

Interpretation of histogram.

A brief interpretation of the descriptive stat that you have obtained using Pivot Table and the frequency distribution.

Problem: Extract the data items of rows 81 - 136 (summary statistics) in the light blue raw data tables for the NBFI sample shown in the yellow 'Sample' sheet for the year ending 2004. These data should be shown as a table with NBFIs in rows and data items as column headings.

Required:

Use suitable lookup functions to create this table in a sheet named 'Summary' dynamically linked to the data source. An example layout is shown in the table to the right. Dynamically linked means, for instance, the data for 2003

Part 2

Build Hypothesis.

Organise data (Remove outliers, treat missing variables etc).

Calculate relevant ratios, convert variables to log values, create categorical variables.

Run multivariate regression.

Interpreting regression with dummy variable.

Describe hypothesis.

Treatment of data.

Description of your data (i.e. the ratios calculated, reasons of using log values etc.)

A literature review about interpreting dummy variables.

Interpretation of regression with dummy variable (Please read materials provided).

Problem: Determine for full sample and each lending category (see sheet 'Lending category') sum, mean, standard deviation, minimum, maximum, number of observations for the 5 data item to the right. Only calculate those statistics which make sense, i.e. it is not meaningful to calculate the sum of a ratio.

Report

The Report should be divided in Part 1 and 2 and it should comprise at least the followings:-
- Introduction
- Literature review (only for Part 2)
- Description of Analysis
- Results
- Conclusions and Recommendations
- Appendices
- References

To learn how to create dummy variables and interpret their effects in multiple regression analysis.

1. Perform a multiple regression analysis including a set of dummy variables built from a multi­category nominal variable.

2. You can begin by using the syntax from the above example working with the CRIC 2003 data set.

3. Identify a multi­category nominal variable in the data set to "dummy up" and use it as a control in your analysis. Potential variables include: Q27 (marital status) q28 (work status) q37 (community size).

4. Include your dummies in the multiple regression equation.

5. Examine your output to determine the influence of your dummy variables.

DISCUSSION

When we change the reference category, we must interpret the results as indicating differences from the new reference category. Your substantive conclusions and explained variance should be essentially the same. With ordinal variables you still enter a dummy variable for all categories but one into the regression. Any you still interpret your results in terms of its difference from the reference category.

Dummy variables can be useful in exploring the non­linear effects of some independent variables in regression analysis. For example, if you have a theory that middle age individuals tend to be more knowledgeable about politics than their younger or older counterparts, then an independent variable that simply measures respondents' ages in years, from youngest to oldest, is not very helpful in a linear regression.

Such a variable will likely produce modest and insignificant age effects, even if middle aged individuals are significantly different from others. Instead, you can create dummy variables for several different age categories and enter these in your regression model to see whether the middle aged are more knowledgeable than others.

Instructions

1. For this exercise you will perform a multiple regression analysis with an interaction term

2. Create an interaction term: As always, it is best to work with an explicit hypothesis in mind.

3. Multiply two independent variables together to create the interaction term.

4. Create a new variable (with a new variable name) to measure the interaction. You can name it "inter" or something more descriptive.

compute inter=IndependentVar*ZVar.

5. Once you have made all necessary recodes, declared missing values and created an interaction term, enter all variables (including the variables used to create the interaction) into a multiple regression equation with two steps, or blocks.

regression variables= DVar IVar1 IVar2 Inter

/statistics coeff outs r tol

/dependent=DVar/method=enter IVar1 IVar2 /method=enter Inter.

6. Based on the output, determine whether the dummy variables and the interaction term are significant. If neither are, repeat the process until you find an appropriate set.

DISCUSSION

As we have already mentioned, the two methods for analyzing interaction effects in a regression simply express the interaction (or specifrication) effect in a slightly different way. But the two methods have different advantages and disadvantages:

When you have only one interaction (specification) of interest, the regression with a subset of cases approach is often easier to use and interpret. An example of one such an interaction (specification) is geographic region: many of the relationships between independent and dependent variables differ in different parts of the world (e.g. Francophone Quebec vs. the rest of Canada; Africa versus other parts of the world).

Regression with a subset of cases doesn't readily allow you to examine several different kinds of interaction effects simultaneously: you may, for example, want to test not only whether gender affects the relationship between age and income, but also whether education affects the relationship between region and income. These complex models cannot readily be analyzed using the regression with a subset of cases method.

Moreover, this approach is only appropriate when the control variable has very few categories.

The approach of including an interaction term can often be difficult to set up and to interpret. However, the interaction term approach enables you to examine a variety of different interactions simultaneously. Moreover, using this method you can directly compare the R­squares for the additive and interactive models to see whether the interaction explains more variation on y.

Attachment:- Assignment.rar