Hi, I need help with my homework about Computer Modelling in Finance. This homework included a project and a report. The report is based on project and this project need to use Excel to make.

Assignment

&"Arial,Bold"&12FINA 515

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.

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.

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.

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).

Sample

Data preparation NBFI Financial DATA.xls &"Arial,Bold" Page &P of &N

A-G