Attachment # 00007177 - Project_1_(Part_1).xlsx
Project_1_(Part_1).xlsx (541.85 KB)
Raw Preview of Attachment:
(refer to the detailed question and attachment below)
Assignment RequirementsBack to OverviewPart 1: Extract raw into table format (20 marks)Part 2: Prepare summary statistics (15 marks)Data items for which summary statistics is requiredAsset size (NZD Thousand)Total operating income (annualized)Impaired asset expense as % of average loan assetsNet interest margin (net interest income / average tot. assets)Loan asset growth/yrPart 3: Histogram (15 marks)SampleNameTypeAllied Farmers Finance LtdFinance CompanyAll Purpose Finance LtdAshburton Finance LtdAsset Finance LtdBelgrave Finance LtdBeneficial Finance LtdBridgecorp LtdBroadlands Finance LtdBusiness Finance LtdCapital+Merchant Finance LtdChallenge Finance LtdClegg & Co Finance LtdCityWide Capital LtdDorchester Finance LtdElders Finance LtdEquitable Mortgage Income TrustEvia Finance LtdFAI Finance LtdFairfield Finance LtdFinance Direct LtdFisher&Paykel Finance LtdFive Star Consumer Finance LtdFarmers' Mutual Finance LtdGeneral Finance LtdGeneva Finance LtdGold Band Finance LtdInstant Finance LtdLDC Finance LtdLombard Finance & Investment LtdMascot Finance LtdMarac Finance LtdMedical Securities LtdMFS Pacific Finance LtdMutual Finance LtdNathans Finance NZ LtdNational Finance 2000 LtdNationwide Finance LtdNumeria Finance LtdOrange Finance LtdOxford Finance CorporationPacific Retail Finance LtdPGG Finance LtdPrime Finance LtdPropertyFinance Securities LtdRockforte Finance LtdSt Laurence Property & Finance LtdSaving and Loans LtdSouthland Finance LtdSouth Canterbury Finance LtdSpeirs Finance LtdStrategic Finance LtdUDCUnited Finance LtdWestern Bay Finance LtdWestgold Finance LtdWilliams & Kettle LtdWrightson Finance LtdABS Canterbury CooperativeNelson Building SocietySouthland Building SocietyWairarapa Building SocietyPSIS LtdNBFIs from A-GBack to Overview$000Avon Inestment LtdBIL financial LtdBMW Financial Service New Zealand LtdCanon Finance New Zealand LtdCapital+Merchant Finance LTdCenturion Finance LtdCitywide Capital LtdDealer Finance LtdDominon Finance Group LtdEagle Finance LtdFirst Eastern Finance LtdFletcher Building Finance LtdFlexirent Capital New Zealand LtdFocus Capital GroupFuji Xerox Finance LtdGeneral Motors Acceptance Corporation (NZ) LtdGE Finance and InsuranceGlobal Corporate Credit LtdGoodman Finance LtdGPG Finance LtdYearYear endMarchMarchJuneJuneJuneMarchDecMayAprilAugustDecemberSeptemberOctoberData ID Statm of Financial Performance (P&L)Interest income Interest expenseNet interest incomeBad debt recoveredCommissions and FeesOther operating incomeTotal operating(non-interest) income Add. prov. for doubtful debts Bad debts written off Other operating expensesTotal operating(non-interest) expenseNet surplus before taxationTax expenseNet surplusBalance SheetAssetsFinance receivables / loans& advancesOther assetsTotal assetsEquity&liabilitiesCurrent debentures and depositsN/current debentures and depositsOther liabilitiesEquityTotalAsset QualityImpaired assets Past due assetsTotal past due & impairedProvisions specificProvisions general & otherProvisions totalStarting provisionsCharge/credit to income statementOther transaction (recoveries/ written off)Closing provisionsRelated party transactions Income from related companies (interest/ni)Expense to related companies (interest/ni)Loan from related companies21281. 079Loan to related companiesOther transaction to related companies Concentration of credit riskExposure to indivdual counter party No of 10- 20% of equityn/aN/a No of more than 20% of equityLoan and advance industry No 1 ranked (%)Commericial loanMortgageCommerical and BusinessAgriculture, Forestry, fishing and MiningPersonal loanProperty related financial serviceResidential InvestmentRetail, Hire purchaseLoan to parent companyHire Purchase, finance dealer vehicle stocksCommercialProperty loanTansport, storage and aviationLeasingPropertycredit card, personalRetail sectorResidential developmentLeased equipment primarily in small to medium enterprise marketMotor vehicle hire purchasePersonal Finance Consumer and Business loanCommerical/Residential property developmentMortgage residential and CommcerialAgricultureRetail and consumerLoggingsmall and medium size business in Nelson and MarlboroughRural sectorPersonal loan (car, boat)Building Retail customersconsumer durable goodsLoan to Parent companyfunding for the purchase of predominantly IT equipmentrenting and leasing finance Leasing and renting equipmentfirst and second mortgage providerProperty and motor vehiclesBusiness financeInvest in two series of Credit linked notesPersonal financeFor taking over Goodman FielderLoan to other companies under GPG Holding Ltd? No 2 ranked (%)Farming and RuralManufacturingBusiness loan Residential DevelopmentMotor vechicle loanMachinery contractorsLand/sub divisionResidential lendingsubdivisions Urban Property Finance and insuranceHealthServiceCommercial CustomersCommerical financeGeographical concentration of loans Taranaki ResidentsAuckland North of North IslandAucklandCanterburyNelsonRest of North IslandParent CompanySouth Caterbury finance LTdBILBMW New Zealand LtdBridgecorp Holdings Ltd (AU)Access Capital Ltd (parent)South Pacific Credit card LtdClegg & Co Havnor Group Holding LtdHanover Holding LtdAF investment LTdFlexirent Capital Pty Ltd (AU)Origination Network Systems Pty. LtdFuji Xerox NZ LtdCairns lockie LtdGneral Motors Acceptance Co. GE Capital Global Finance Holdings Inc. (US)Forsyth Barr Group LtdBurns Philp & Companyparent info 2Belgrave Finance TrustBMW AGAmerican Express Co.GE Co. (US)parent info 3Serge Investment LtdEmployee number (full time equivalent)SUMMARY STATISTICSProfitablility / EfficiencyReturn on assets (net surplus / average total assets)Return on equity (net surplus / average equity)Net interest margin (net interest income / average tot. assets)Interest income as % of average loan assetsInterest expense as % of average debentures&depositsNon-interest income as % total operating incomeCost income ratios:Operating expense / (net interest income + other income)Operating expense / (net interest income + other income - impaired asset exp.)Capital ratio (not risk weighted) (equity / total assets)Loan assets as % of total assetsRelated Party InformationLoans to related party as % of loan assetsIncome from related party as % of total incomeLevel impaired assets as % of total assetsLevel total provisions for doubtful debts as % of loan assetsLevel general provisions for doubtful debts as % of loan assetsNet debt write-offs as % of average loan assetsImpaired asset expense as % of net interest incomeP&L Raw DataNet interest income (annualized)Net surplus (annualized)Impaired asset expense (annualized) Net debt write off (annualized)Tax expense (annualized)Balance Sheet Raw DataAverage assetsAverage loan assetsLoan assets (end balance)Average equityEquity (end balance)Average debentures/depositsDebentures (end balance)General provisions (level)Total provisions (level)Movement in general provision (annualized)Movement in total provisions (annualized)Bottom of lookup/match rangeEnd PointMid PointYear End PointIntervalRelated Party informationInfo1Allied Farmers Ltd (parent)Lease, expense, purchase of debtors porfoliio of the parent companyExecutive Trustees Ltd controlled by Mr A S Radisich.Lease premisesNegotiable Enterprises Ltd (shareholder)DividendDorechester Pacific Ltd (parent)Mangement fee, DividendHanover Group Holding Ltd (grand Grand parent)various Info2Timbertion Investment Ltd and Swan Insurance Ltd controlled by Mr A S RadisichLoan Whitby Willage Holding LtdSubention paymentSenate Finance Ltd (subsidiary)Hanover Group Ltd (grand parent)Info3Penrose Enterprises Ltd controlled by Mr AS Radisich Purchase of fleet vehiclesHanover Financial service Ltd (parent)NBFIs from H-NLiberty Financial LtdLoan SocietyMacquarie Finance (NZ) LtdMotor Trade Finance LtdNew Zealand Finance LtdNorth South Finance LtdMaarchn/ana/aCar, household, and mortgagefinance to finance companyMortgage LoanMortgage Commercial PropertyConsumer Property development and investmentHealth care professionResidential loanCommerical property industrymotor vehiclepersonal loanVending personal loan (vehicles)Commercial & residential property developmentResidentialResidential housingother finance companylending to business and personalFarmingResidential property developmentCommerical BuildingaucklandChristchurch, wellingtonLiberty Financial Pty Ltd (Au)Solomon Nominees LtdPyne Gould Corporation LtdMutual Group LtdVTL GroupHanover Group Holding LtdNew Zealand Finance Holding LtdCentral Enterprises LtdNumeria Co LtdThe Bay Trust(Ult) Evan ChristianInterest income as % of average loan assetsNBFIs from O-TOrigin Energy Contact Finance LtdORIX New Zealand LtdPrimus Financial Service LtdPrometheus Finance LtdPropertyFinance securities LtdProvincial Finance LtdRural Portfolio Investment LtdSapphire Securities LtdSapphire Securities LtdScottish Pacific Business Finance LtdSMC Buidling SocietySMC Building SocietySouthern Cross Building SocietySouthPac Finance Trust LtdStrata Finance LtdTCNZ Finance LtdTechnology Holding LtdToyota Finance LtdJulyTotal operating(non-interest) incomesubscrible for redeemable preference shares in Mission energy pacific holdings, the major sharehold in Contact Energy Ltdbusiness finance, leasingHire purchase agreement, personal loanPurchase hire purchase from parent company (Retail and consumer)Motor vehicleAutomative financeSocially Responsible Investment Commerical property lendingHousehold lending and consumerMotor purchasingRural Factoring Personal propertypersonal service and housingvehicles financecommerical property lendingparents companyTechnology, EftposHouseHoldbusiness equipmentResidential property lendingruralfinance and insurancerural sectorCar dealershipSouth IslandGisborneMECI BVElectra LtdPacific Retail services LtdPyne Gould Guinness LtdPrometheus FoundationAvon Investment LtdBluestone Group Pty LtdScottish Pacific Management service Pty Ltd (AU)Southbury Group LtdSt Laurence LtdToyota Financial services Co (JPN)Pacific Retail Group Ltd(Ultimate) St. George Bank (AU)`Toyota Motor Co. (ultimate) Cullen Group LtdNBFIs from U-X$000Vision Senior Living LtdVision Securities LtdVTL Group Finance LtdVTL Group LtdBad debt recoveredCommissions and FeesCommercial propertyretirement village developersTransport, storage and avaiationvendingPersonalMotor Vehicle industryArrow international LtdWringhtson LtdMessrs Anderson and R. Foster (two directors of VSL)ANZ\Main lending categories of NBFIs# of companiesProperty(Investment/Development)Business/ManufacturingRural/FarmingPersonal/Consumer/RetailOthersGlobal Corporate Credit LimitedBIL financial Ltd (loan to parent company)Goodman Finance Ltd (for taking over Goodman)Fletcher Building Finance Limited (loan to parent company)Origin Energy Contact Finance LimitedFuji Xerox Finance LimitedPrometheus Finance (Socially Responsible Investment )Canon Finance New Zealand LimitedTCNZ Finance LTdORIX New Zealand LimitedVTL Group Finance Ltd Sapphire SecuritiesToyota Finance LimitedBMW Financial Service New Zealand LimitedRetail Financial Services LimitedPrimus Financial Service LimitedSouth Canterbury Finance Ltd Eagle Finance LimitedBIL financial Ltd (loan to parent company)SUMMARYYEARCompany NameReturn on assets (net surplus / average total assets)Return on equity (net surplus / average equity)Net interest margin (net interest income / average tot. assets)Interest income as % of average loan assetsInterest expense as % of average debentures&depositsNon-interest income as % total operating incomeOperating expense / (net interest income + other income)Operating expense / (net interest income + other income - impaired asset exp.)Asset size (NZD Thousand)Loan asset growth/yrCapital ratio (not risk weighted) (equity / total assets)Loan assets as % of total assetsLevel impaired assets as % of total assetsLevel total provisions for doubtful debts as % of loan assetsLevel general provisions for doubtful debts as % of loan assetsImpaired asset expense as % of average loan assetsNet debt write-offs as % of average loan assetsImpaired asset expense as % of net interest incomeNet interest income (annualized)Total operating income (annualized)Net surplus (annualized)Impaired asset expense (annualized) Net debt write off (annualized)Tax expense (annualized)Average assetsAverage loan assetsLoan assets (end balance)Average equityEquity (end balance)Average debentures/depositsDebentures (end balance)General provisions (level)Total provisions (level)Movement in general provision (annualized)Movement in total provisions (annualized)Allied Farmers Finance LtdGroup Member NameID
FIN415 project1

FIN415 project1

Question # 00101493 Posted By: lyn689 Updated on: 09/08/2015 02:13 AM Due on: 09/15/2015
Subject Economics Topic Financial Markets Tutorials:
Question
Dot Image
You need to analyse given financial data using various Excel functions.

the PPTX in files will tell you the functions you should use in every section.  if you are good at excel, this will be easy for you. 
Dot Image
Attachments
Project_1_(Part_1).xlsx (541.85 KB)
Project_1_(Part_2).xlsx (338.54 KB)
Project_1.pptx (40.56 KB)
Tutorials for this Question
  1. neil2103
    Tutorial # 00095833 Posted By: neil2103 Posted on: 09/08/2015 02:40 AM
    Puchased By: 3
    Tutorial Preview
    The solution of FIN415 project1...
      Get the Solution
    Attachments
    1-Project-1--Part-2-_(1).xlsx (1121.51 KB)
    _Part-2_(1).docx (57.09 KB)
    part_1_updated.xlsx (592.21 KB)
Whatsapp Lisa