# POL242 LAB MANUAL: EXERCISE * Regression with Dummy Variables

August 30, 2017

Question
Assignment Requirements
Assume these are financial data that have been extracted by your research assistant from prospectuses of New Zealand Non-bank Financial Institutions (NBFIs). The data are organized by alphabet in the blue sheet tabs A-G etc

Part 1: Extract raw into table format (20 marks)
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 are shown if we change the input year.

Maximum marks for tidy, dynamically linked smart solutions.
Manual solutions (without link) will obtain half of the maximum mark

Part 2: Prepare summary statistics (15 marks)
Insert another worksheet into the workbook and use it to prepare the following summary statistics of the sample.

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.

Hint: Excel’s Pivot table features provides quick results.
Comment: If you duplicate data for this analysis, there is no requirement for it to be dynamically linked to the source data.
Full marks for appropriate formatting

Part 3: Histogram (15 marks)
Attempt to create a histogram similar to the one on the right for the NBFIs in sheet ‘Sample’.
It counts the total number of companies in each size class (left bars) and shows the total amount of asset in this size class (right bars). Size is measured by assets (item asset size).
Full points for the use of FREQUENCY or comparable functions, good formatting (and of course a correct answer).

Get a 30 % discount on an order above \$ 5
Use the following coupon code:
CHRISTMAS
Positive SSL