_dfp-module-2-appendix.pdf

7/21/2019 _DFP-Module-2-Appendix.pdf

http://slidepdf.com/reader/full/dfp-module-2-appendixpdf 1/93

MONARCH INSTITUTE PTY LTD

DFP Module 2 - AppendixInvestments



D : M 2: I I : DF 1203

I 1

A ENDI 1 HA DOE A A ING AGENC DO?



D : M 2: I I : DF 1203



D : M 2: I I : DF 1203

HA DOE A A ING AGENC DO?

M . .

M '

' .

M ' :

1.

?

2. H ?

M ' .

D

M ' .

. A ,

'

. O

MOOD A ING OCE

I , M ' :

• G

,

• D ,

• M

,

• I M ' .



D : M 2: I I : DF 1203

,

M ' . O , M '

,

.

I M ' ,

. M

,

. O ,

M ' .

1)

F , '

, . D

, . M '

,

M '

• B /

• I /

• N

• M , , ,

• M

• B

• C



D : M 2: I I : DF 1203

• D ,

• F ,

o

,

o ,

o .

F , M ' ,

. , M '

M ' .

2) '

A , , , .

M '

. F ,

, ,

, .

F ,

. I /

. F

,

, . A

. M '

, .



D : M 2: I I : DF 1203

,

. M '

, M ' .

3)

M ' ,

, 60 90 . H , M '

' ,

.

4)

"O ,

M ' . F ,

.

M ' . . ,

M ' ."

5) '

M '

, ,

, M '

. C

M ' .



D : M 2: I I : DF 1203

6)

M ' '

. C

, , , .

7)

F , M '

,

. M '

,

' . F

, M '

. F ,

.

.

MOOD A ING ME HODOLG

M '

. , :

1) M '

' ,

, ,

.

.

2) O M '

, . M '

'

.



D : M 2: I I : DF 1203

. D

M .

HE BENEFI OF A MODD A ING

, ,

. F ,

. ,

,

.

1)

M ' ,

A , '

. I ' ,

" "

.



D : M 2: I I : DF 1203

2)

,

. M '

.

3)

M '

, . F ,

' , ' . M '

" ".



D : M 2: I I : DF 1203



D : M 2: I I : DF 1203

I 1

A ENDI 2 AM CO E O E F ND IN E O FL E



D : M 2: I I : DF 1203



AMP CapitalCore Property Fund



Why invest in property?

Property is a large and diverse asset class that includes a broad range of sub-sectors includingresidential buildings, ofce buildings, business parks, hotels, industrial, shopping centres, retirementvillages, self storage, laboratories and hospitals. These investments may be accessed directly throughownership vehicles such as trusts or syndicates and indirectly through property portfolios listed onpublic markets. Investing in property can provide a number of benets for investors:

Stable, reliable income streamsOver the long term, the majority of property returns comefrom rental income which is generally less volatile than capitalreturns, as rents are contracted under a lease agreement.To further reduce risk, a property portfolio is ideally comprisedof properties with good tenant diversication and staggeredlease maturities.

Ination hedgingSome real estate leases contain provisions for rental increasesto be indexed to ination, while in other cases there is anopportunity to increase rental rates whenever a lease termexpires and the tenant is renewed. Either way, real estateincome should keep pace with ination, giving an investorthe potential to maintain real returns.

Tangible assetsInvesting in property provides exposure to tangible assetssuch as shopping centres and ofce buildings, and the valueof the land on which they are built, which generally performsin line with key economic drivers such as consumer spendingand employment.

DiversicationAs different property sectors have different risk and returncharacteristics, investing in a strategic mix of listed anddirect property may provide investors with a better riskadjusted return.

Listed property has the potential for higher returns andis more liquid but has a higher correlation to equities, whiledirect property provides excellent diversication benetsdue to its stable incomes and relatively low correlation withother asset classes (traditional investment vehicles suchas stocks and bonds), helping to reduce volatility and riskin an investment portfolio.

Global investment opportunitiesFrom a global perspective, as more countries introduce realestate investment trust (REIT) structures, there are evengreater opportunities for global investment and portfoliodiversication. Since 1990 new entrants to the market haveincluded the UK, Germany, Singapore, Canada, Malaysia andJapan and countries currently under consideration includeemerging markets Brazil, China and India.

Improving investment conditionsAs with many other asset classes, property was adverselyaffected by the global nancial crisis. The increased cost ofborrowing affected companies’ ability to purchase propertyand renance existing assets, rising unemployment decreaseddemand for commercial property and affected rms’ abilityto lease space and there was negative investor sentiment.All these factors led to a fall in valuations.

In AMP Capital’s view we are now moving into a new phasein the investment cycle which is expected to present somesignicant opportunities in the coming years.

In the listed property markets, companies have movedtheir focus from balance sheet issues to underlying realestate fundamentals (demand, supply and pricing) and newacquisitions to provide earnings growth. More equity issuanceand initial public offering (IPO) activity is expected in 2010,generating new investment opportunities.

In direct property markets, valuations appear to be at orclose to a bottom as space demand and supply fundamentalsimprove, selling pressure abates and investor demandincreases. In recent months some quality direct propertieshave seen upward revaluations. In the Australian ofce sector,vacancies are expected to peak in 2010/11.



Introducing the AMP CapitalCore Property FundThe Fund offers investors access to a well-diversied property portfolio through a single fund.It invests in direct and listed assets across local and global markets in key property sectors includingofce, retail, industrial and residential.

A diversied property portfolioAs an open-ended hybrid property fund, the Fund investsin a strategic mix of property assets, with a target assetallocation of 50% in direct property and 50% in Australianand global listed property.

Direct propertyThe portfolio invests in premium, established blue-chipproperty assets that would be unattainable for most retailinvestors due to the amount of capital required. These assetsinclude some of the busiest shopping centres in Australiasuch as Warringah Mall, as well as premium ofce buildingssuch as NAB House and Angel Place in Sydney, and Collins Placein Melbourne. The Fund also has a small exposure to globalproperty assets through the Global Direct Property Fund.This underlying fund currently provides access to investmentsin the North American and European property markets.

Australian listed propertyThe listed portion of the Fund provides investors with accessto liquid, high-yielding Australian property securities. TheBlackrock Wholesale Indexed Australian Listed Property Fundoffers a cost efcient way to access the Australian property

market through its passive index management strategy.

Global listed propertyMore than three quarters of the Australian listed propertymarket is concentrated in the top ve stocks. Investingin global listed property provides more diverse geographicalinvestment opportunities and access to a broader rangeof property sectors, such as hotels and residential propertywhich are difcult to access in Australia. The AMP CapitalGlobal Property Securities Fund invests in real estateinvestment trusts (REITs) and property securities listed onstock exchanges in North America, Europe, and Asia Pacic.

AMP Capital Core Property Fund

Blackrock Wholesale

Indexed Australian

Listed Property Fund

AMP Capital

Global Property

Securities Fund

Direct property opportunities

Targetrange

UnderlyingFunds

Propertyheld

directly

AMP Capital Wholesale Office Fund

AMP Capital Shopping Centre Fund

AMP Capital Hedged Global Direct Property Fund

Direct proper ty 30−70% Australian and global listed property 30−70%



Key Fund benets

0.4

0.6

0.8

1.0

1.2

1.4

1.6

1.8Return on $1 since 1 July 2005 (inception)

Core Property Fund

Mercer AustralianUnlisted PropertyFund Index

UBS Global RealEstate Investors Index

ASX 200 ListedProperty Trust Index

2005 2006 2007 2008 2009

Blending direct and listed propertyhelps smooth returnsThe Fund is designed as a full cycle investment with the aimof providing investors with smoother, more stable performanceby diversifying between global and Australian listed propertyand direct property. As direct and listed markets performdifferently at different times, this blend provides the potentialfor more stable income and capital growth than investingin just one type of property investment. This is demonstratedin the below graph, which shows the performance of the

Fund compared to the Australian and global listed propertyand Australian direct property sectors from 1 July 2005 to31 December 2009.

Access to quality direct propertiesThe Fund’s direct property investments are in quality, welllocated properties with lengthy and secure income streams.For example, as at 31 December 2009 the average asset sizein the AMP Capital Shopping Centre Fund was $349 millionand the average vacancy rate was just 0.34%. In the AMP CapitalWholesale Ofce Fund, 12 out of 14 properties are classiedas premium or grade A. This means properties must offer

good views, onsite undercover parking, access to an attractivestreet setting, a lobby and high quality presentation andmaintenance.

Growth potential and liquidity frominvestment in Australian and global REITsInvesting in listed property securities provides greater portfolioliquidity than investing in direct property alone.

This allows the Fund to make tactical movements to takeadvantage of market opportunities, both in Australia andglobally. It also provides the potential for capital growthas the value of the properties in the portfolio (and theirshare prices) rise.

Regular revaluationsDirect assets in the Fund are regularly revalued by a panelof independent external valuers to more accurately reectthe current value of the portfolio. This has enabled us to takea more effective ‘mark to market’ approach and provide

greater transparency to our investors.

Daily pricing with monthly withdrawalsIn 2009 the Fund changed from quarterly to monthlywithdrawals to provide greater certainty and allow clientsmore ready access to their funds. To the extent that availablefunds are insufcient to meet the month’s withdrawalrequests, each request will be scaled back on a pro-rata basis.

Active managementKey to AMP Capital’s success as a leading property manager

is our active management process. At Fund level we combinelong-term strategic asset allocation with short-term tacticalallocation between sectors and geographies. At direct propertylevel we not only operate physical assets, but also manageresponses to macro and micro drivers which involves tenantmanagement, leasing, efciency and environmental upgrades.With our afliate AMP Capital Brookeld, we also apply activemanagement to our global listed property portfolio in a processthat covers screening and idea generation through to portfolioconstruction, allowing timely responses to changes in marketconditions and optimisation of investment opportunities.

Relative returns since inception as at 31 December 2009

Source: AMP Capital. Past performance is not a reliable indicator of future performance.



Why AMP Capital? The offer at a glance

Investment objectiveTo provide regular income and capital growth anda total return (income and capital growth) on a rollingthree-year basis, after costs and before tax, above theFund’s performance benchmark while accepting amedium level of volatility over the long term.

BenchmarkThe weighted return of the indices listed below(under weightings indicated):

• Mercer/IPD Australian Pooled Property Fund Index – 50%• S&P/ ASX 300 Property Accumulation Index – 25%• UBS Global Real Estate Investors Index – 25%

Suggested minimum investment timeframe5 years

DistributionsAims to pay quarterly

Buy/Sell spreadBuy spread: 0.17%

Sell spread: 0.17%

Management fee1.1% plus a 20% performance fee for Class A clients and1.4% plus a 20% performance fee for Class H clients.*

* The management costs include any management fee charged by theunderlying investment managers.

Contribution/withdrawal feeNil

Hedging policyThe Fund’s exposure to global listed property and globaldirect property will be hedged back to Australian dollars.

AMP’s involvement in property dates back to 1962 whenit completed Sydney’s rst sky-scraper, now home to theAMP head ofce. From this time, AMP Capital has hadextensive experience managing, developing and leasingproperty investments on behalf of superannuation fundsand wholesale investors, and today manages over$20 billion in assets.

AMP Capital has a team of sector specic propertymanagers who are supported by experts from all areasof property management. This wider team includes

specialists in debt advisory, investment strategy andresearch and transactions, among others. We have over80 property investment professionals based in Sydney.

Exposure to global listed property securities is via theAMP Capital Global Property Securities Fund which ismanaged by AMP Capital Investors and AMP CapitalBrookeld. Together they represent AMP Capital’sspecialist global listed real estate and infrastructuresecurities capabilities. We use ‘on the ground’ investmentspecialists based in Sydney, Chicago, London and HongKong to identify the key opportunities in these regions.


http://slidepdf.com/reader/full/dfp-module-2-appendixpdf 20/93 N S 5 4

6 0

0 3 / 1

0

Important note

Investors should consider the Product Disclosure Statement (PDS) availablefrom AMP Capital Investors Limited (ABN 59 001 777 591) (AFSL 232497) for

the AMP Capital Core Property Fund before making any decision regardingthese funds. The PDS contains important information about investing inthe Fund and it is important that investors read the PDS before making anydecision whether to acquire, or continue to hold, or dispose of any units inthe Fund. Permanent Investment Management Limited (ABN 45 003 278 831)(AFSL 235150) being the responsible entity of the Fund and the issuer ofunits in the Fund. Permanent has not prepared this information and makesno representation or warranty as to the accuracy or completeness of anystatement in it. To invest in the Fund, you and you clients will need theFund’s current Product Disclosure Statement (PDS) issued by Permanentand available from AMP Capital on its website. The PDS contains importantinformation about investing in the Fund and it’s important you and yourclients read the PDS before making a decision about whether to acquire,continue to hold or dispose of units in the Fund. This information hasbeen prepared for the purpose of providing general information, withouttaking account of any particular investor’s objectives, nancial situation orneeds. You and your client should, before making any investment decisions,consider the appropriateness of the information in this document, and seekprofessional advice, having regard to the investor’s objectives, nancialsituation and needs.

The Lonsec Limited (“Lonsec”) ABN 56 061 751 102 rating (assigned February2009) presented in this document is limited to “General Advice” and basedsolely on consideration of the investment merits of the nancial product(s).It is not a recommendation to purchase, sell or hold the relevant product(s),and you should seek independent nancial advice before investing in thisproduct(s). The rating is subject to change without notice and Lonsec assumesno obligation to update this document following publication. Lonsec receivesa fee from the fund manager for rating the product(s) using comprehensiveand objective criteria.

To the extent that any ratings, opinions or other information of Standard& Poor’s Information Services (Australia) Pty Ltd (ABN: 17 096 167 556,Australian Financial Services Licence Number: 258896) (“Standard & Poor’s)constitutes general advice, this advice has been prepared by Standard& Poor’s without taking into account any particular person’s nancial orinvestment objectives, nancial situation or needs. Before acting on anyadvice, any person using the advice should consider its appropriatenesshaving regard to their own or their clients’ objectives, nancial situationand needs. You should obtain a Product Disclosure Statement relating tothe product and consider the statement before making any decision orrecommendation about whether to acquire the product. Past performanceis not a reliable indicator of future performance. Ratings can change orcease at any time and should not be relied upon without referring to themeaning of the rating. For more information regarding ratings please callS&P Customer Service on 1300 792 553 and also refer to Standard & Poor’sFinancial Services Guide at www.fundsinsights.com. Each analytic productor service of Standard & Poor’s is based on information received by theanalytic group responsible for such product or service. “S&P” and “Standard& Poor’s” are trademarks of The McGraw-Hill Companies, Inc. © 2009

Standard & Poor’s Information Services (Australia) Pty Limited

Contact us

AdvisersTo nd out more about the AMP CapitalCore Property Fund, or how your clients canaccess this investment opportunity, pleasecontact your AMP Capital Key AccountManager or call our Adviser Services teamon 1300 139 267

Personal Investors

To nd out more about the AMP CapitalCore Property Fund, please talk to youradviser or call us on 1800 188 013 .

Or visit our websitewww.ampcapital.com.au



D : M 2: I I : DF 1203

I 1

A ENDI 3 BALANCED F ND FAC HEE



D : M 2: I I : DF 1203



www.bt.com.au

June 2011

BT Multi-managerBalanced Fund

Fund objectiveThe Fund aims to maximise returns aboveinflation with a medium risk of fluctuations incapital values in the short term and provide anoverall return which exceeds its benchmark overfive years or more.

Investment approachThe Multi-manager funds allow you to select asingle investment option that diversifies acrossasset classes, investment managers andinvestment management styles. Thisdiversification helps reduce overall risk and aimsto improve consistency of returns by minimisingthe impact on overall performance resulting fromany one style, asset class or manager.

Manager selectionThe BT Multi-manager funds are built, monitored

and rebalanced by Advance InvestmentSolutions – who research, select and blendinvestment managers from around the world andactively manage the strategy, manager selectionand performance of the funds. AdvanceInvestment Solutions regularly meets with theinvestment managers and conducts an intenseinvestigation of everything, from their investmentphilosophy and current market views, to how wellthe investment manager’s team is workingtogether or how they would react in periods ofhigh volatility.

Fund factsMinimum suggested investment period 3 yearsDate of inception March 2003Risk profile BalancedProduct size ($m) $196.9Management fee 1.80% paAPIR code BTA0077AU

Performance vs benchmark (% pa)

Investment returns are retail – net of fees. The benchmark for the Fund is created from a blend of indices based on the

Fund’s exposure to different asset classes. Details of the particular marketindices used for the Fund’s benchmark can be found on BT Online.

Asset allocationLatest portfolio update

Growth assets posted modest falls over the Junequarter as investor risk aversion stemming fromsocial unrest, political instability and economicfragility led investors to realign their portfolios infavour of defensive assets. The Fund continued torun a slight overweight position to growth assetsduring the quarter, with the holdings of equitiesand commodities above the neutral benchmarkallocation. The performance of the Fund was inline with the benchmark return with sectorallocation variance having little impact on overallperformance whilst manager selection decisions

detracted slightly from the Fund’s return. Thelargest positive contribution by asset class frommanager selection came from within active globalequities and diversified property.

-1.60

10.60

0.91 0.08

5.44

-1.19

10.81

2.22 2.43

7.49

-10.00

-5.00

0.00

5.00

10.00

15.00

20.00

25.00

30.00

35.00

40.00

3 months 1 year 3 years 5 years Since inception

Fund Benchmark

Australian Equities 32.80%

International Equities 21.70%

Asian Equities 2.98%

Defensive Alternative Assets 2.07%

Growth Alternative Assets 9.85%

Listed Property 6.13%

Fixed Interest 4.89%

Int Fixed Interest 9.89%

Cash 9.68%



BT Funds Management Limited ABN 63 002 916 458 is the responsible entity of the Multi-manager Balanced Fund (Fund) and is the issuer of units in the Fund.A Product Disclosure Statement (PDS) is available for the Fund and can be obtained by calling 1800 813 886 or online at www.bt.com.au . You should obtainand consider the PDS before deciding whether to acquire, or continue to hold, units in the Fund. This fact sheet has been prepared without taking account ofyour objectives, financial situation or needs. Because of this you should, before acting on this information, consider its appropriateness, having regard to yourobjectives, financial situation and needs. Total returns for the Fund are calculated to the last day of each month using exit prices. Total return figures assumedistributions are reinvested and issuer fee and expenses are deducted but contribution fees (where payable) and taxes are not . Returns are historical and pastperformance is not a reliable indicator of future performance. This fact sheet is updated quarterly and is accurate at the time of publishing. We may change thenvestment characteristics of the Fund at any time.

The second quarter of 2011 delivered lacklustre returns withglobal industrial production being disrupted due to the naturaldisasters suffered by Japan. Also affecting investorconfidence was the deteriorating social, political andeconomic conditions of southern Europe, Northern Africa andthe Middle East. This led to the rotation in favour of defensiveassets at the expense of growth assets.

Global equity markets registered modest falls in the secondquarter returning -2.8% as measured by the MSCI World exAustralia Index (in Australian dollar terms). The Australiandollar moved 3.8% higher against the US dollar, whilst on atrade-weighted basis the Australian dollar appreciated 2.0%.

The Australian equity market performed poorly as the strongAustralian dollar, heightened market volatility caused byglobal natural disasters and global political and social unrestled to investors reducing their risk appetites. The S&P/ASX200 Accumulation Index declined 4.0% over the quarter,underperforming both the local currency and the unhedgedglobal equities indices.

Global property markets did manage to hold onto some of thegains from the first quarter, although struggled during thequarter, making up losses in the last week of June andposting a return of 2.9% (hedged in Australian dollars). Inaddition, the macro uncertainty may have increased thelikelihood of continued low interest rates in the developedeconomies, providing further support for real assets withstable income, such as real estate.

Strong cash flows in the first part of the quarter, and mergerand acquisition activity in the listed real estate marketprovided further support for global REIT share prices.Australian LPT Index underperformed global property stockhaving recorded a fall of 0.5% over the quarter.

The Australian bond market posted a slight gain over thequarter as bond yields moved lower across the entire curve.The RBA kept the official cash rate unchanged at 4.75% withfurther rate hikes anticipated sometime over the remainingmonths of 2011. The UBS Australian All Maturities Indexposted a gain of 2.3%.

Global sovereign bond yields moved lower over the quarter aseconomic data out of the US and leading European nationsshowed signs of sluggish economic conditions. This led toinvestor rotation, into the relative safety of global bonds andout of risky growth assets. The Barclays Global AggregateBond Index (Hedged) returned 2.9% over the quarter.

Managers

Australian EquitiesBTNorthcapeAusbilSchroders

AlleronCelesteContangoTribeca

International Equities

AQRMFSTrilogyTradewindsLion

SchrodersLazardMellonState Street

PropertyPerennialEuropeanInvestorsCBRE

Alternative AssetsAdvanceAlternativeStrategies

Credit SuisseGrosvenor

Australian FixedInterest

CFS Perennial

International FixedInterest

Standish MellonFranklinTempleton

Wellington

Cash IMSKapstream



Fund facts Fund benefits

Benchmark: Balanced Growth Index

Buy / Sell spread:Investment style: Active, fundamental, disciplined, valueSuggested minimum investment period: Five years or longer

Total returns % (after fees) as at 31 August 20111 mth 3 mths 6 mths 1 yr 3 yrs pa 5 yrs pa 7 yrs pa 10 yrs pa

Perp. WealthFocus Investments -1.6 -4.3 -4.1 2.9 -1.5 -0.3 3.8 3.9

Perp. WealthFocus Investment Advantage -1.5 -4.2 -4.1 2.8 -1.5 -0.5 3.7 3.7

Perp. WealthFocus Super -1.4 -3.8 -3.6 2.9 -0.9 0.0 3.9 3.9

Perp. WealthFocus Pensions -1.5 -4.1 -3.9 3.2 -1.0 0.1 4.3 4.3

Perp. WealthFocus Term Allocated Pension -1.5 -4.1 -3.9 3.2 -1.0 0.1 4.3 4.3

Balanced Growth Index -1.7 -4.1 -4.1 2.7 0.2 1.1 5.1 4.6

Growth of $10,000 since inception (WFI Fund) Portfolio sectors¹

Top 10 stock holdings¹Commonwealth Bank of Australia

BHP Billiton Limited

Westpac Banking Corporation Asset allocation ranges¹Telstra Corporation Limited Min. Target Max.

ANZ Banking Group Limited Australian shares 10 28 50

Coal & Allied Industries Limited International shares 10 28 50

Orica Limited Property 0 5 15Fletcher Building Limited Fixed income 5 10 35

Alumina Limited Enhanced cash 0 13 30

Rio Tinto Limited Other investments 0 16 30

Past performance is not indicative of future performance. Returns may differ due to different tax treatments.

PER0017AU

PER0036AU

1.95%

PER0100AU

0.34% / 0.00%

APIR

August 2011

Investment objective: To provide investors with long term capital growth and incomethrough investment in a diversified portfolio with an emphasis on Australian andinternational share investments.

Mgmt cost:

Provides investors with access to a diverserange of growth and income producing assets.Active management and asset allocationtechniques are employed in order to further

enhance the fund's return and manage risk.

PER0334AU

PER0015AU

Australian shares, 31.5%

International shares, 28.1%

Property, 5.8%

Fixed Income, 9.9%

Cash & Enhanced cash, 7.8%

Other investments, 16.8%

$8,000

$12,000

$16,000

$20,000

$24,000

May-99 May-01 May-03 May-05 May-07 May-09 May-11

Fund Benchmark



What are...?

Further informationAdviser Services 1800 062 725Investor Services 1800 022 033Email [email protected]

This publication has been prepared by Perpetual Investment Management Limited (PIML) ABN 18 000 866 53 5, AFSL No 234426. It is general information only and is not intended toprovide you with financial advice or take into account your objectives, financial situation or needs. You should consider, with a financial adviser, whether the information is suitable for yourcircumstances. To the extent permitted by law, no liability is accepted for any loss or damage as a result of any reliance on this information.The PDS for the relevant fund, issued by PIML,should be considered before deciding whether to acquire or hold units in that fund. The PDS can be obtained by calling 1800 022 033 or visiting our website www.perpetual.com.au. Nocompany in the Perpetual Group* guarantees the performance of any fund or the return of a ny investor's capital. Total return shown for the fund(s) have been calculated using exit pricesafter taking into account all of Perpetual's ongoing fees a nd assuming reinvestment of distributions. No allowance has been made for e ntry or exit fees or taxation (except in the case ofsuperannuation funds). Past performance is not indicative of future performance.The Balanced Growth Fund gains its exposure to Australian Shares by investing in an underlying AustralianShare Fund/s which primarily invests in Australian listed or soon to be listed shares but may have up to 20% exposure to stocks outside Australia. The investment guidelines showing theFund's maximum investment in international shares do not include this potential additional exposure. Short positions may be part of the underlying Australian Share Fund's strategy.Currency hedges may be used from time to time.* Perpetual Group means Perpetual Limited ABN 86 0 00 431 82 7 and its subsidiaries.The following funds are not open to new investment: Perpetual's Balanced Growth Fund Nil Entry Fee Option.¹ Fund information in this document is relevant to the W holesale option unless stated.

When making asset allocation decisions, weconsider three key indicators being cycle, value

and technical. This process evaluates the impactof the earnings cycle on Australian equities, thebusiness cycle on domestic bonds, and analysevaluations, while also incorporating marketsentiment through technical analysis. Theseindicators combine to either increase exposure toan asset class when market conditions becomefavourable or reduce exposure if marketconditions are becoming adverse.

Did you know?

Perpetual’s investment philosophy for investingin balanced portfolios is to focus on the

fundamental drivers of returns from qualityinvestments rather than on asset classesthemselves. We believe investment in internalcapability where we have the demonstratedability to consistently add value is a lower riskand more reliable approach to meeting investors’objectives. We also believe that our focused,active approach to asset allocation and ourproven disciplined investment style has theability to add value to investors over time.

Active asset allocation is a portfolio managementstrategy that changes a portfolio’s assetallocation to take advantage of market conditionsin the short term. Using a disciplined active assetallocation process offers the potential for bothenhanced returns and reduced levels of risk.

Why PerpetualPerpetual is one of Australia’s most experiencedinvestment services groups, with an enduringpassion for protecting and growing our clients’wealth.

Founded in Sydney in 1886 as PerpetualTrustees, we’ve helped generations ofAustralians invest and manage their wealththrough all market conditions.

Perpetual process

Market commentary Global equities fell 4.8% in August. A s lowdown in global growth and concerns oversovereign debt in Europe sent stocks lower. The Australian market fell by 10.0% early inthe month, before rallying to end the month down 2.0%. This was the fifth consecutivemonth of negative performance. The August domestic reporting season delivered mixedresults with resources experiencing the largest increases in profit growth, followed by thebanks.

Credit markets endured one of the most volatile months in August since the fallout fromLehman Brothers. Amid heightened volatility and extreme risk aversion, August observedcredit spreads reflective of a broad-based global recession.

Asset allocation Asset allocation added value over the month. Allocation to equities was initially heldslightly underweight. However, the equity market sell off early in the month causedequities to become more attractively valued. This resulted in the portfolio moving to aneutral equity position. Momentum remained negative throughout most of the period.

We started the month with a small overweight to fixed income, but remained close toneutral for the remaining period. Over the month Australian bond prices rallied sharplyfollowing the downgrade of the US Government’s credit rating. The resulting fall in yieldsmeant that bonds became expensive relative to inflation expectations.

Stock selection Stock selection was positive for equities. One of the reasons the Australian equitiesportfolio outperformed was its holding in Coal & Allied. During the month Coal & Alliedreceived a joint takeover offer from Rio Tinto and Mitsubishi Corporation at $122 pershare. The offer was later increased to $125 per share, valuing the company at $10.8

billion.

The global equities portfolio also outperformed. This was due to its more defensivepositioning. Contributors to performance included Johnson & Johnson, Nestle, TescoPLC, and Philip Morris. Johnson and Johnson delivered better sales growth in itsPharmaceutical and Consumer segments and is one of the best-managed large-caphealthcare companies.

The fixed income portfolio underperformed. An underweight position to supranationalsand semi-government securities contributed to performance during the month. Theportfolio’s overweight to credit detracted from performance. This was due to a broadbased widening of credit and swap spreads. This was partially offset by strong runningyields.

Outlook We are mindful that global equities face some challenges in the short to medium term.The US Federal Reserve has the ability to implement monetary easing in the form of QE3to appease the market, but Europe is likely to remain the focus of uncertainty as thegrowth picture deteriorates perhaps to the point of t ipping back into a mini-recession. Asiaand China look more favourable in this context and we expect that when reflation doesreturn, emerging markets and commodities will bounce back meaningfully.

Domestically, companies continue to be impacted by the strength of the Australian dollar,a frugal consumer and rising input costs . We continue to research and invest incompanies with recurring earnings, low debt and sound management which we believeare well placed to add value over the medium to long-term.

The outlook for the credit markets remains neutral. Valuations are attractive relative tohistoric levels. Credit fundamentals remain stronger than current spreads suggest.Domestic banks and corporates are far less exposed to the troubles facing a number ofoffshore counterparts. Despite having high capital levels and stronger funding profiles,subdued risk appetite has resulted in a broad based sell off among credits. These positivefactors are countered by the heightened market volatility, which has been caused by anincrease in risk aversion.



02 04 06 08 0 100

10-35%5-30%

0-20%

0-15%

0-15%0-20%

25-45%

United Capital Balanced Fund

Investment objective

To provide capital growth of your investment over themedium to long term by investing in a diversied portfolioof growth and defensive assets, and to achieve a total returnafter fees in excess of the fund’s benchmark 1 over a rolling5 year period.

Investment strategy The fund generally gains its exposure to a diversiedportfolio of investments through a mix of investmentmanagers. The balanced orientation of the fund means ithas a similar exposure to growth assets (such as Australianand international property and equities and alternative –

growth), and defensive assets (such as cash, xed interestand alternative – defensive).

The fund is authorised to utilise approved derivativeinstruments for risk management purposes subject to thespecic restriction that the derivative instruments cannotbe used to gear port folio exposure.

The underlying investment managers may utilise strategiesfor the management of currency exposure. The level ofcurrency hedging used for the fund will vary from time totime. The fund has the capacity to change the level andnature of the currency overlay to manage the currency risk.

Manager informationUnited believes that no single investment manager canprovide superior investment performance across all assetclasses. Because of this, United adopts the multi-manager,sector-specialist investment approach by outsourcingthe security selection function to a range of high qualityinvestment managers who United believes have specialisedskills and expertise at managing investments within a specicasset class. The sector specialist investment approach isfocussed on harnessing an investment manager’s strengthsand avoiding their weaknesses.

Notes1 The fund’s benchmark incorporates the applicable indices for each asset class

weighted against the fund’s neutral asset allocation. Refer to the ‘Asset classbenchmark indices’ table for more information.

2 Estimated management cost before performance fee. This is an estimate thatincludes the responsible entity fee, the investment manager fee and es timatesof the underlying investment management fee and other fund expenses. Theamount payable may be more or less than the estimate.

3 This is an estimate. The amount payable may be more or less than the estimate.4 Represents the full margin between the buy and sell spread.5 Past performance are net of investment manager fees and gross of tax.

If you are a superannuation fund member, you may be investing in the fundvia a poole d superannuation trust (PST), therefore performance relating to yourinvestment may vary due to the effect of fees and income tax applied at the PSTlevel. Please refer to the relevant fund PDS to check if this applies to you and forinformation about the fund’s investment process.

6 Property asset sector may include exposure to Australian direct property andAustralian and international property securities.7 The MSCI World ex-Australia Index in AUD Hedged may be used from time to

time, depending on the strategic hedging ratio applied to the internationalshares portfolio. The benchmark for international shares may change in future tothe MSCI All Countries World ex-Australia Index in AUD.

8 This benchmark is an asset weighted average return predominately made up ofthe Commonwealth Property Fund and the AMP (Capital Investors) InvestmentLinked Superannuation - Australian Core Property Portfolio.

Investment Prole

Fund facts

Fund start date 12 April 2002

Distribution frequency Quarterly

Investment timeframe 3 years +

Estimated management cost 0.77%2

Performance Fee 0.06%3

Buy/Sell spread 0.45%4

Performance % 5

1 year return as at 30 June of each nancial year

2011 2010 2009 2008 2007

8.97 10.95 -11.78 -9.93 13.64

Investment guidelines

Range Strategic (neutral) allocation22% Australian shares18% International shares10% Property6

35% Diversied xed interest5% Cash & short term securities5% Alternative – growth5% Alternative – defensive

Asset class benchmark indices

Asset class Benchmark

Australianshares S&P/ASX 300 Accumulation Index

Internationalshares MSCI World ex-Australia Index in AUD7

Directproperty InTech Weighted Direct Property Index

8

Australianlistedproperty

S&P/ASX 300 Property Accumulation Index

Internationallistedproperty

UBS Global Real Estate Investors ex-AustraliaIndex in AUD Hedged TR

Diversiedxed interest& Alternative– defensive

50% – UBSA Composite Bond Index

50% – Barclays Capital Global Aggregate BondIndex (hedged in AUD)

Cash &short term

securities

UBSA Bank Bill Index

Alternative –growth MSCI World ex-Australia Index in AUD

Last updated | June 2011



United Funds Management Ltd Investor Services 1800 333 700 Website www.united.com.au

Important note: This Investment Prole has been prepared on behalf of the IOOF group, which consists of IOOF Holdings Ltd ABN 49 100 103 722 (IOOF) and itsrelated bodies including IOOF Investment Management Limited ABN 53 006 695 021 AFSL 230524 and Australian Executor Trustees Limited ABN 84 007 869 794AFSL 240023. It is based on general information contained in the underlying fund Product Disclosure Statement (PDS) issued by United Funds Management Ltd,which is available at www.united.com.au.

The Investment Prole is not intended to represent investment or professional advice as it does not take into account your individual objectives, nancial circumstances or needs.You should carefully review the PDS and consider consulting a nancial adviser before making a decision about whether this par ticular investment option is appropriate for you. IOOFand its related bodies corporate are not liable for any loss or damages arising as a result of reliance placed upon the contents of this Investment Prole. The information is given ingood faith and believed to be accurate at the time.

Last updated | June 2011

United Capital Balanced FundInvestment Prole

Underlying sector managers 9 – as at 30 June 2011

Sector Manager %

Australianequities

AllianceBernstein Australia Ltd 4.15BlackRock Investment Management(Australia) Ltd

3.63

Integrity Investment Management 4.21Legg Mason Australia AssetManagement Ltd

1.38

Merlon Capital Partners 1.79

Perennial Investment Partners Ltd 4.24

Solaris Investment Management Ltd 4.83

24.23Internationalequities

Axiom International Investors LLC 3.61

LSV Asset Management 4.17

Perennial Investment Partners Ltd 0.77

State Street Global Advisors, Aust, Ltd 8.89

Wellington Management Company, LLP 2.20

19.64

Fixedinterest

Loomis, Sayles & Company LP 3.18

Perennial Investment Par tners Ltd 10.82

PIMCO Australia Pty Ltd 10.50

24.50

Property Challenger Managed Investments Ltd 1.54Colonial First State Global AssetManagement

0.42

1.96

Internationalproperty

Cohen & Steers Capital Management Inc 0.91

Perennial Investments Partners Ltd 2.05

2.96

Cash Perennial Investment Partners Ltd 4.05

United Funds Management Ltd 17.13

IOOF Investment Management Ltd 1.1122.29

Alternativeequities

Pantheon Ventures (Guernsey) Ltd 0.08

Lombard Par tners International LLC 0.20

Crown Global Secondaries II PLC 0.08

Mount Kellett Capital Partners LP 0.08

Sector Manager %

Alternativeequities(continued)

Newbury Partners LLC 0.02

Performance Equity Management LLC 0.04

BNY Mellon Asset Management 1.56

Other 0.51

2.57

Alternativedebt

Credit Suisse Asset Management(Australia) Ltd 0.44

International Acceptance Ptd Ltd 0.11Gresham Property Funds Management

Ltd0.08

AMP Capital Investors Ltd 0.80

RARE Infrastructure 0.41

EIG Global Energy Partners 0.02

1.86

Notes9 Underlying manager allocations and asset allocations reect the combined

indirect exposure of the Fund’s holding in other IOOF Group unit trusts and/ordirect investments.



D : M 2: I I : DF 1203

I 1

A ENDI 4 IME AL E OF MONE



D : M 2: I I : DF 1203



D : M 2: I I : DF 1203

IME AL E OF MONE

I ,

/

.

L $1,000

.

8%

. . $1,000 8%

. . $1,360.49 4 .

, $1,000

.

I = $1,000.00

I 1 = 8% $1,000 = $ 80.00I 2 = 8% ($1,000 + $80) = $ 86.40

I 3 = 8% ($1,000 + $80 + $86.40) = $ 93.31

I 4 = 8% ($1,000 + $80 + $86.40 + $93.31) = $ 100.78

$1,360.49

,

:

= ($1,000)

= (8%)

= (4)



D : M 2: I I : DF 1203

I ( ) ,

8% $1,360.49 '

.

. :

= (1 + )

:

= ($1,000)

= (8%)

= (4)

= F

$1,000 (1 + 0.08) 4

= $1,360.49



D : M 2: I I : DF 1203

B , :

.

. C

, . .

.

. I O A O

B .

E .1 E .2 E .3 E .4 E .5

N N $27,000 $8,000 N

E .1 E .2 E .3 E .4 E .5

N N N N $40,000

I

. I

5, , O A O B,

.



D : M 2: I I : DF 1203

,

:

= ($27,000)

= (8%)

= (2)

I ( ) ,

8%, $27,000 '

$31,492.80 ' .

= ($8,000)

= (8%)

= (1)

I ( ) ,

8%, $8,000 '

$8,640 ' .

, $27,000 3 ' $8,000

4 ' $40,132.80 5

' ($31,492.80 + $8,640).

$40,000 $40,000. O

,

O A



D : M 2: I I : DF 1203

? F, :

. $8,000. H

7

?

A ,

$12,000 6 4 . I 6.5%

. .

?

? H ?

,

O A O B.

I O A O B

,

.

.

.

= ($27,000)

= (8%)

= (3)

I ( ) ,

8%, $27,000

$21,433.47 .



D : M 2: I I : DF 1203

= ($8,000)

= (8%)

= (4)

I ( ) ,

8%, $8,000

$5,880.24 .

, $27,000 3

$8,000 4 $27,313.71

($21,433.47 + $5,880.24).

= ($40,000)

= (8%)

= (5)

I ( ) ,

8%, $40,000

$27,223.33 .

A

, O A. I

.

A ,

, $27,000 3 ' $8,000 4 '

$27,313.71 . F O B,

$40,000 $27,223.33

.



D : M 2: I I : DF 1203

.

.

=

=

=

=

A , .

= $27,000 + $8,000

(1 + 0.08) 3 (1 + 0.08) 4

= $27,313.71

= $40,000

(1 + 0.08) 5

= $27,223.33

O ,

.



D : M 2: I I : DF 1203

. A /

. A

.

C 7%:

E E E E E

1 2 3 4 5$500 $500 $500 $500 $500

I ( )

,

:

= ($500)

= (7%)

= (5)

I ( ) ,

7% $2,875.37

$500 5 $2,875.37

.

A , . :



D : M 2: I I : DF 1203

= Mr

nr ]1)1[( −+

=07.0

]15)07.01[(500$ −+

= $2,875.37

.

.

= ($500)

= (7%)

= (5)

I ( ) , 7% $2,050.10

$500 5 .

,

= r

nr ])1(1[ −+−

= $500 [ ( . ) ]

.

1 1 0 075

0 07

− +−

= $2,050.10



D : M 2: I I : DF 1203

?

1 ( 1).

A

) . A

( ) .

C 7%:

E E E E 0 1 2 3 4

$500 $500 $500 $500 $500

L , 5 ( ) $500.

, ( )

( ) 4. C ,

= 5

1 ( 1).

F 2 . F = 4

1 ,2, 3

4 ( ) $500

:

.

1: C 4 1, 2, 3 4.

= ($500)

= (7%)

= (4)



D : M 2: I I : DF 1203

I ( ) ,

7% $1,693,61

$500 4 .

2: A $500 ( 0) 1.

$1,693.61 + $500 = $2,193.61

,

1:

= r

nr ])1(1[

−+−

= $50007.0

]4)07.01(1[ −+−

= $1,693.61

2: $1,693.61 + $500 = $2,193.61

2 ($2,193.61)

1 ($2,050.10) ( )

$500 5 ( )?

( ) . C ,

.



D : M 2: I I : DF 1203

.

C .

A 40 . H

. A 65 $1.5 65

. H $12,000

( 15 ) . A 55

$40,000 , ,

. I A 11%

, , A

( 55 65 )

$1.5 ?

I

. I .

,

( ).

40 41 42 43 53 54 55 56 57 58 59 60 61 62 63 64 65

0 1 2 3 12 13 14 15 16 17 18 19 20 21 22 23 24 25

($,000 )

12 12 12 12 12 12

(40)

? ? ? ? ? ? ? ? ? ?

1500



D : M 2: I I : DF 1203

A , 3

• 1 D A 55

.

• 2 D 55

A 65 .

• 3 I A

55 65 ,

?

55 .

:

= ($12,000)

= (11%)

= (15)

I ( )

, A 15

55 $412,864.

O ($40,000)

$372,864.



D : M 2: I I : DF 1203

$372,864

65 .

:

= ($372,864)

= (11%)

= (10)

I ( ) , $372,864 $1,058,718.

I A $1.5 ,

$441,282.

$441,282

55 65 , ( )

.

:

= ($441,282)

= (11%)

= (10)

I ( ) ,

A ( ) $26,389

55 , $1.5

65 .



D : M 2: I I : DF 1203

: ?

G J , , B M . 31

D , . G J

. $8,000 ( )

. .

J 12 , . .

9 ( 10 ).

G J $120,000

. 25

$12,000. A , , , G J

$10,500 . ,

. F

. G J

.

9

.

.

:

1. G

J ?

2. A

. B

.



D : M 2: I I : DF 1203

:

1. A G J

,

$10,500 . I

.

2.

4.5% ,

8% .

O G J

.

9 , 4.5% . . 8% . .

:

:

D

. 12 , 10 .

,

. I , G J

10

9.

11 10 . I

6

15 ( 14).

10 ( 9)

4.5%,

:



D : M 2: I I : DF 1203

1: C 5

10, 11, 12, 13 14. (

9 $24,000

)

= ($24,000)

= (4.5%)

= (5)

I ( ) ,

( ) 4.5%, $24,000

10, 11, 12, 13, 14 $105,359

9 ( 10)

2: A $24,000 9 1.

$105,359 + $24,000 = $129.359

8 $24,000 = $192,000

6 ,

( ) $129,359 9 ( 10).

F

1 9 $129,359

9. , :

= ($129,359)

= (4.5%)

= (9)



D : M 2: I I : DF 1203

I ( ) ,

G J $11,975.34 9 ,

$129,359 9 ,

.

G J

$10,500 . M

8% . .

.



D : M 2: I I : DF 1203

E A

A

9

4.5%

A

9

8%

0 3

1 4 $11,975.34 $10,359.03

2 5 $11,975.34 $10,359.03

3 6 $11,975.34 $10,359.03

4 7 $11,975.34 $10,359.03

5 8 $11,975.34 $10,359.03

6 9 $11,975.34 $10,359.03

7 10 $11,975.34 $10,359.03

8 11 $11,975.34 $10,359.03

9 12 $24,000 $129,359 $11,975.34 $10,359.03

10 13 $24,000

11 14 $24,000

12 15 $24,000

13 16 $24,000

14 17 $24,000

15 18



D : M 2: I I : DF 1203

:

) C

)

) G J

)

G J

,

.

G J 9 ,

8% .

I G J

/

. ,

.

. .



D : M 2: I I : DF 1203

.

K J $680,000. $200,000

$50,000

7.20

. L

.

30 . $11,000

.

A

. A

35%.

) A

H : ,

,

. (7.2% /12 =

0.6%)

A 1 , K J $524,821

.

) H K J

?

) O , ?

) H 1?

) I 7.20 8.40 ,

K J

?



D : M 2: I I : DF 1203

H : . A , 29

( ).



D : M 2: I I : DF 1203

:

) $3,597.58

I $3,597.58 , ($3,597.58/$11,000)= 32.7%

35% K J

.

) $43,170.96 (12 $3,597.58)

) G 1 $524,821,

$5,179 ($530,000 $524,821)

) $43,170.96

$5,179

$37,991.96

e) If interest rates increase to 8.4% per annum, the regular monthly payment

would now be $4,029.36

I $4,029.36 , ($4,029.36/$11,000) = 36.6%

35% K J

.



F .

. A ,

, . .

( ) , .

C .

, . C .

C 1 D F .

. .

. . $50,000 , $100,000 .

A .

1 D F .

$60,000 . A .

3, .

. 8 .

. .

8

.

. , .



G $60,000 , $60,000 8 . .

8 ( 3.5%). H

=$60,000, =8 =.035 F . $79,009

8 ! $79,009

. A $60,000 .

$79,009 . .

B .

. ( . . ) .

( ). . , (

). 10 .

8 , . ( F ) .

32 $250,000 32

.

, , ( ) $79,009 8 . ( ) 32 ,

( F ) $250,000 32 40 . ( ) 8 ( . .

), B . (

. , $70,009 32

$250,000? .

D F .

( ) 7%

$79,009 35 . A 3.5% ( ) ( ) . 3.5%



. , ( ) = $1,589,759

$1,589,759 $60,000 . G 8 ,

.

8 .

. ( F ) $1,589,759 ( ) 8 .

. 7%.

8 $925,254

$925,254 , ! ,

8 , . A

. A ,

, . !



D : M 2: I I : DF 1203



D : M 2: I I : DF 1203

I 1

A ENDI 5 I FO ING E CEL EAD HEE



D : M 2: I I : DF 1203



1

I . Overview of t he Excel Work Space

♦ Titles, Windows, and Worksheets

The title bar at the very top of your Excel screen reminds you that you're in Excel. Ifyour workbook is expanded to take up the maximum amount of screen space, its titlebar is merged with Excel's title bar to look like this: Microsoft Excel - Book1 . If yourworkbook is taking up less than the maximum amount of screen space it's displayed inits own w indow with its own separate title: Book1 .

Book1 is the default name for an Excel workbook until you assign it another name.Book1 is composed of multiple worksheets. Take a look at the bottom of the Book1window to see the tabs labeled Sheet1, Sheet2, Sheet3 , etc. Use the mouse to click anyone of these tabs to move that worksheet to the top of the display.

Right-click any tab to get a “pop-up menu” of options specific to the tab. For example,one of the options is “Rename” . Click the Rename option to put the selected tab’sname in reverse video. Type a different name and hit the enter key to change the tab’sname.



2

Change the order of worksheets by dragging and dropping a tab from one location toanother. Delete a tab by clicking the tab to select it, right clicking, and selecting“ Delete” from the pop-up menu that displays.



3

♦ Menus

Excel's menu appears across the top of the workspace. The menu is a part of Excel'sworkspace and is separate from the workbook in which you create worksheets, charts,and other objects.

The menus are arranged to lead you to the option you need. Select any top-level menuentry by clicking it with the mouse or holding down the ALT key and tapping theunderlined letter for your choice. Excel displays a further set of options using a drop-down menu list. Selections with a diamond to the right of them lead to other drop-down lists. Menu selections with ellipses after them lead to a dialog box that collects

information Excel needs to carry out your command.

There are times when the menu contents may change. For example, if you're workingwith a chart the menus reflect choices relevant to the InfoWindow or the chartingenvironment.

Choose the commands View, Toolbars,Customize to open the “ Customize” dialog.Choose the “Options” tab. On that tab youcan control whether Excel positions the

Standard and Formatting toolbars togetheras well as whether Excel uses its “mostrecently used” option for menu items. Ifyou like seeing the menu items in the sameplace each time you open a menu, toggleoff this option.

TipMove or copy a worksheet with thecommands Edit, Move or Copy Sheet .Indicate what workbook you wantthe sheet to be in.

Click the “Create a copy” box at thebottom of the dialog if you want to



5

♦ Formula Bar

The formula bar appears under the menus and any toolbars you might have open atthe top of the screen. It reflects the contents of whatever cell is the current cell in theworksheet. The left of the formula bar shows the cell reference or the cell name if thecell has been assigned a name. At right is a display of the cell's contents. If your cellcontains a formula, the worksheet cell shows the value of the evaluated formula whilethe formula bar displays the formula’s contents (E.g., the formula itself).

♦ Toolbars

Excel has more than a dozen preconfigured toolbars. The button icons on a toolbar aregenerally shortcuts to commands you might otherwise issue using the menus or withkeystrokes. Toolbars appear as strips or boxes of icons located around the edge of yourworksheet or floating on its surface.

When you start Excel you see the Standard toolbar, with icons for frequently-usedtasks such as file open, print, copy and paste, formatting, functions, charting, etc. Todisplay more toolbars, use the commands View, Toolbars and select the toolbars youwant displayed from the dialog box that appears. Turn off toolbars the same way.

Toolbar Display ShortcutIf you have at least one toolbar displayed, here’s a shortcut todisplaying and hiding toolbars: Right-click anywhere in the graytoolbar area at the top of the Excel window (but not directly on a toolbutton). Excel displays a drop-down list of almost all its toolbars.Click any toolbar name in this list to display it (if hidden) or to hide it(if displayed).



6

Move the mouse pointer to the top edge of an icon on a toolbar to have Excel display a“ tooltip”, or a short description of w hat that icon does.

“ Grab” a toolbar by holding the left-hand mouse button on a space on the toolbar butnot directly on a tool button. Drag to any location in the workspace where you wantthe toolbar to be.

Reshape any toolbar by dragging with themouse on an end or side. For example, youmay prefer to have all the tools in a toolbarin a box, like the one at left.

♦ Status Bar

At the very bottom of the Excel workspace is the status bar. Keep an eye on the statusbar for information about the status of your session or a particular operation. Forexample, if you have the num lock key on and the caps lock key on, Excel displays thewords NUM and CAPS in the status bar.

The status bar is also useful for getting on-the-fly calculations about spreadsheetvalues. In the example below, highlighting the range of numbers in column A displaystheir sum (the default) in the status bar.



7

Right-click the “Sum” report in the status bar to change the status bar calculation toone of the other options: Average, Count, Count Nums, Max, or Min.

♦ Worksheet Referencing Scheme

The worksheet part of the screen is arranged as a grid of rows and columns. Anindividual cell in the grid is named by the intersection of the numbered rows andlettered columns. For example, starting at the upper left-hand corner (the worksheet“ home” position) a cell two co lumns over and four columns down is named cell B4.

If you prefer, you can turn on an alternative “R1C1” style of referencing where bothrows and columns are numbered. To do this, select Tools, Options, General and selectthe “R1C1 reference style” under “ Settings” . If you make this selection, both rows andcolumns have numbers as names. For example, with this scheme, the name for cell A5

becomes R5C1. The R1C1 referencing style is used mostly in macro writing.

♦ For More Information

Click Help on the top-level menu and choose Microsoft Excel Help to display either the Office Assistant or Excel’s onlinehelp guide (depending on your Excel configuration).

If using the Office Assistant, enter “workbooks andworksheets” as the help topic.



8

If using the online help guide, choose the “Contents” tab and select the item Workingwith Workbooks and Worksheets.

I I . Basic Data Ent ry

♦ Entering Text and Numbers

Click with the mouse on the cell in which you want to enter data to make that cell thecurrent cell. Begin typing the entry for that cell. When your entry is complete, hit theenter key.

Excel automatically left justifies text and right justifies numbers, but you can easilyoverride these defaults with formatting commands.

Notice that while you're typing an entry into a cell, the entry also displays in theformula bar. Any time you make that cell the current cell, the cell's contents display in

the formula bar. If you enter a formula in the cell, the formula itself displays in theformula bar while the evaluated result of the formula displays in the worksheet.

♦ Editing an Entry



9

To edit the contents of a cell double-click the cell with the mouse. Excel changes themouse pointer to a vertical edit bar within the cell. Change the cell contents and thenhit the enter key to exit cell edit mode.

Alternatively, make the cell you want to edit the current cell. Then click the mouse in

the formula bar and perform the edit operation in the formula bar instead of in the cellitself.

If your copy of Excel is configured differently and you want to change edit modes,choose Tools, Options to open the “Options” dialog and then select the “ Edit” tab.

♦ Deleting an EntryMake the cell whose entry you want to delete the current cell. Then hit the Delete keyto delete the cell's contents.

Delete the contents of a range in the same way you’d delete the contents of a singlecell.



10

♦ Adding or Deleting a Row or Column

It’s easy to make changes to your spreadsheet layout by adding or deleting rows orcolumns. For example, to add a single column, click the header of the column to theright of where you want the new column to be located. The entire selected columnwill appear in reverse video. Select Insert, Columns from the main menus and Exceladds a new column to your worksheet. To insert more than one column at a timeselect more than one column header before choosing the menu instructions. Insertingan additional row or rows works the same way. Select the row or rows below whereyou want the new row to be located.

Delete a row or column by selecting its header and clicking Edit, Delete or hitting theDelete key.

I I I . Selection

♦ Selecting More Than One (Contiguous) Cell

You might want to select a range of cells to perform the same operation on all of themwith a single command. To do this, click a cell at one corner of the range of cells youwant to select. Make sure the mouse pointer is a wide crosshair shape (not an arrow).Then hold down the left mouse button and move the mouse over the worksheet toinclude the cells you want selected. Reverse-highlighting indicates which cells areselected. The cell you started out with is the only one that doesn't appear in reverse



11

highlighting.

TipTo select a very large range, one that’s not conveniently visible all atonce on the screen, here’s an easier way than dragging with themouse From the menus select Edit, Go to. In the dialog box thatdisplays enter the address of the range you want to select in the“ Reference” box. For example, A1:Q109. Click OK and Excel selects



12

♦ Selecting Non-contiguous Cells

Excel makes it easy to select noncontiguous cells. Select the first cell or range of cellsyou want to include. Then hold down the CTRL key and move the mouse pointer toselect a noncontiguous cell or range. Repeat as many times as necessary, holdingdown the CTRL key the entire time, to select all the cells/ ranges you want.

You can also use the same Edit, Go to option described in the tip above. In this case,enter in the “Reference” box the addresses of all the ranges and cells you want toselect, separated by commas. For example:

Tip: Click the Special button on the “Go To” dialog to select particular classes ofcells or objects.



13

I V. Key Mouse Operat ions

♦ Move a Cell or Range

Select the cell or range you want to move. When selected, it should appear in reversevideo. Position the mouse pointer at the edge of the cell or range so it turns into anarrow. Hold down the left-hand mouse button and drag the selection to its newlocation. Release the mouse button. The cell or range is still selected. Click anywhereout of the cell or range to de-select it.

♦ Copy Data

Similar to moving a cell or range. Select the cell or range you want to copy. Positionthe mouse pointer at the edge of the cell or range so it turns into an arrow. Hold downthe CTRL key and hold down the left-hand mouse button. (Note the small plus signdisplays above the mouse arrow pointer as a visual reminder that this is a copyoperation, not just a move.) Drag the selection to create a copy in a new location.Release the mouse button. Then click anywhere out of the cell or range to de-select it.

♦ Fill & Extend

This section describes two variations on copying: Filling and Extending. Both methodsstart with a source cell or range to copy and copy to contiguous cells.

Filling repeats cell contents. The results are most like the copy operation. To copy byfilling:

1. - Select the source cell or range tocopy.2. - Position the mouse pointer onthe fill box in the lower-right-handcorner of the cell or range. The

The fill box



14

pointer should turn into a thin filled crosshair.3. - Hold down the left-hand mouse button and drag to the right so the range

now extends to the next column (or columns).



15

4. - The contents of the source cell or range are repeated in the adjacentlocation you indicated.

5. - Click any cell to deselect and turn off reverse video.

In this example, the fill box at the lower right corner of cell A3 was

dragged two columns to the right to extend the A3 entry.

Extending is similar to filling. It also starts with the source data but instead of copyingit extends the data in a logical progression. That is:

1. - Select the source cell or range to extend.2. - Position the mouse pointer on the fill box in the lower-right-hand corner of

the cell or range. When positioned over the fill box the pointer willdisplay as a thin filled crosshair.

3. - Hold down the left-hand mouse button and drag to the right 1 so the rangenow extends to the next column (or columns).

The difference between filling and extending is that after you extend the source cell orrange the cells you’ve filled with data don't hold the same thing as the source cells.They’re not just a copy because Excel extends the data in a logical fashion.For example:

If the source cell(s) hold(s): The contiguous extended cells hold:

Qtr1 Qtr2, Qtr3, Qtr4, Qtr1, Qtr2, etc.

Jan Feb, Mar, Apr, May, Jun, Jul, etc.

1 You can also drag to the left or down, depending on what your source data looks like and what you wantto do.



16

1994, 1995 1996, 1997, 1998, 1999, 2000 etc.

In the third example in the table above we needed to give Excel at least two cells

worth of source data so it would know how to extend the data. A variety ofprogressions are possible if you give Excel a sample of how you want it to proceed. Forexample:

If the source cells hold: The contiguous extended cells hold:

2000, 1999 1998, 1997, 1996, 1995, 1994, etc.

.2, .4 .6, .8, 1.0, 1.2, 1.4, 1.6, etc.

V. Moving and Select ing

♦ Moving Around the Worksheet

Use the scroll bars at the right and bottom of the workbook window to move quicklyaround the worksheet.

To go to a specific cell, hit the F5 key to get the Go To dialog box. In the Reference area,enter the reference of the cell you want to go to and click OK. Excel closes the dialogbox and makes the cell you named the current cell.

In the Go To: area of the dialog box, Excel keeps track of the last locations you askedfor, in case you want to return to them.

If you've named cells or ranges in your worksheet you can enter a name instead of acell or range reference.

CTRL+Home puts you in the upper-left-hand corner of your worksheet: cell A1.

♦ Specifying a Range

You may need to specify a range by typing, and not just by selecting the range withthe mouse. This is especially useful, for example, if the range is a large one extending



17

past the borders of your display and so not easy to select by dragging. To specify arange by typing, indicate the cell reference of the cell at the top left corner of therange. Type a colon. Then indicate the cell at the bottom right corner of the range.Here’s an example of typing in a range specification: A1:B5

You can also select a range by using the Go To dialog box (F5) and typing in a rangespecification instead of just a single cell reference.

♦ Selecting a Range for Data Entry

If you need to enter a lot of data in contiguous cells of your worksheet you can speedup operations by selecting the range where the data will be before you begin enteringdata. Then when you enter data in the first cell of the range you can just hit the enterkey to move to the next cell instead of hitting the enter key and having to use an arrowkey or the mouse to move to the next cell. Within the range you specify, Excel bydefault moves down each column and then across.

Change the default setting for your installation of Excel by clicking Tools, Options, Editand selecting an option from “ Move Selection after Enter Direction” .



18

VI. Formulas

♦ Format

Select the cell where you want to enter a formula and type an equal sign to start theformula (and activate the formula bar). Type the formula into the cell and hit the enterkey when you're finished. Use parentheses where necessary to make operations clear.

♦ Naming Cells & Ranges

You can assign any worksheet cell or range a name and then use that name in aformula. This can make your worksheet more readable.

An easy way to assign a name is to select a cell or range and enter the name you wantto assign it in Excel’s “ name box” on the formula bar.

The formula bar

How the formuladisplays in theworksheet itself.

The “ name box”on the formula



19

If you name cells or ranges after you’ve already used their values in formulas, youneed to explicitly tell Excel to use the names in the formulas. You can do this with the

command Insert, Name, Apply. Select the names you want to use from the Apply Names dialog box that displays .

♦ Tips for Avoiding Errors

Naming key cells and using those names in formulas instead of cell references can helpyou avoid errors. For example, the formula =Q55*M92 is probably less immediatelycomprehensible than the formula =Subtotal*Taxrate .

When constructing a formula you can use the mouse to click a cell you want toinclude in the formula instead of typing in the cell’s name or reference. Excel includesthe cell reference (or name, if available) in the formula automatically. Clicking the cell

you want to include can help you avoid typing in the wrong reference by mistake.

Keep formulas short and straightforward. If you need to express a complexrelationship with a formula it's better to build several short formulas and use them asbuilding blocks for intermediate values rather than to build one long, baroque, error-prone, and hard-to-debug formula.



20

Excel displays an error value in a cell when the formula for that cell can't becalculated. If a formula includes a reference to a cell that contains an error value, thatformula also produces an error value.



21

Examples of Excel error values:

#DIV/0! The formula is trying to divide by zero.#N/A No value is available.#NAME? Excel doesn't recognize a name used in the formula.

#NUM! There's a problem with a number in the formula.#REF! The formula refers to an invalid cell.#VALUE! An argument or operand is incorrect.

♦ Using Built-in Functions

Excel has a large library of built-in functions you can use to perform standardworksheet calculations. In many cases you can use these functions instead of writingyour own formulas. Use the built-in functions by themselves or embed them withinyour own formulas.

Functions can be nested within other functions.

All Excel functions have the same basic syntax: =function name(parameters).

If you don't already know the syntax of the function you need, use thefunction button on the Standard toolbar to call up the Function Wizarddialog box.

The Function Wizard dialog box walks you through selecting and completing afunction.



22

♦ Copying Formulas (Relative Referencing)

There will be many occasions when you build a worksheet model with several likeformulas. For example, perhaps your budget model totals sales in six departments foreach of the months of January to December. The formula to sum departmental salesfor January is the same as the formula to sum departmental sales for December, exceptthey refer to different columns of data. If your January to December co lumns arestructured alike, there's no reason to separately enter twelve summing formulas.

Instead, enter the formula for January and then copy it across the worksheet to theother eleven columns.

Excel adjusts cell references in each copied formula across the row so that eachformula refers to its own column's data. That is, if the January sales data is in Column

A, the formula for January refers only to cells in Column A. February’s data is inColumn B. Even though the sum formula for February was copied from January’sformula in Column A, the February formula correctly refers only to cells in Column B.

This formula copying and adjusting arrangement is the default in Excel. It’s termed

relative referencing. Keep in mind that it’s an issue only when you’re copying aformula.

♦ Absolute Addressing



23

There may be times when you copy a formula that you don't want Excel to adjust acell reference in the copy from the master formula. That is, when you copy theformula, you don't want the default of relative addressing for some or all of the copiedformula's cell references.

For any reference in the source formula that you want to “ fix” (e.g., disallow anychanges during the copy) use a dollar sign before the row and column indicator. Thedollar sign is an arbitrary symbol that just instructs Excel not to change the reference.For example, a source formula that allows the D5 reference to change but fixes thereference to A1 would look like this:

=D5*$A$1

If we copy the source formula above across the worksheet to columns E, F, and G, thecopied formulas in those columns would look like this:

=E5*$A$1=F5*$A$1=G5*$A$1

In this example, the formulas in E2, F2, and G2 were copied from the original formulain D2. In the master formula in Cell D2, the reference to cell A1 was absolute (fixed)but the reference to the value in Row 1 (D1) was relative.

As a result, when this formula was copied to E2, F2, and G2, each copied formulareferred to Cell A1 (the absolute reference value) and also to the value in Row 1 of itsown column (the relative reference value).

VII. Charting



24

♦ Chart Basics

Some worksheet data is too complex to interpret at a glance. If this is true of yourdata, and if you want to present the data in a different way, consider using an Excelchart to show your data graphically. Excel has a variety of chart types to choose from,from simple, general column, bar, and pie chart types to specialized types such as XY,radar, bubble, stock, and surface charts. Excel’s online help for charts providesexcellent suggestions on what chart types are especially appropriate to displayparticular data.

♦ Using the Chart Wizard

The easiest way to create an Excel chart is tomake use of Excel’s Chart Wizard. The ChartWizard is a button on Excel’s Standard Toolbar,or invoke it with the menu commands Insert,Chart .

Although not required, it’s customary to selectthe data you want to chart before you open theChart Wizard. At least initially, all the data youplan to chart must be located on the sameworksheet, although it need not be contiguous.

The illustration at right shows the first of the four Wizard steps. Select the type ofchart you want in this step.

The second Wizard step shows a thumbnailview of your chart and confirms the range ofdata you’re charting. If you selected the data tochart before starting the Chart Wizard, the“ Data range” box is automatically completed.

Otherwise, choose the data range to chart inthis step.

Also in this step you can choose to identifyyour data series in rows or in columns. Excelmakes its best guess.



25

The “ Series” tab in Step 2 provides advanced methods for choosing and labeling data.

The third Chart Wizard stepprovides lots of options forformatting your chart. Most ofthese options can be accessed andchanged later, once your chart isbuilt, by right-clicking andchoosing from the pop-up menuthat displays.

The final Wizard step lets youchoose a location: a newworksheet that Excel inserts intoyour workbook for you, or as agraphical object that floats on thesurface of the current worksheet.

Once you’ve gone through the four Chart Wizard steps, you may want to move andor size the chart. Click the chart to select it. Drag to move it. Drag the selection box ata corner to resize it.

As mentioned earlier, remember that right-clicking any chart element providesaccess to customization options for that element. In addition, the ChartingToolbar that displays whenever a chart is selected also contains many of themost commonly-used commands.



26

An Excel chart is dynamic, in the sense that it’s tied to the data on which it’sbased. If the underlying data values change, the chart changes automatically.

VIII. File Management Essentials

♦ Saving a File

To save a workbook use the commands File, Save to get the dialog box that lets youname your file and specify what drive and in what folder it should be saved.

The Save as type : entry at the bottom of the dialog box lets you translate your file intoother formats (123, text, DBase, etc.)

♦ Retrieving a File



27

To retrieve a workbook you've saved on disk use the commands File, Open . Exceldisplays a dialog box similar to the one above where you can name the file to openand specify where it’s located.

IX. Printing

To print the current worksheet using Excel's defaults for printing click the Print buttonon Excel's Standard toolbar.

To control Excel’s print options select File, Page Setup to open the Page Setup dialogbox. Note that this dialog has four tabs: Page, Margins, Header/Footer, and Sheet .



28

Some particularly useful options:

To specify an area to printGo to the Sheet tab, click in the Print Area box and then drag through the worksheet

areas that you want to print. (Nonadjacent ranges print on separate sheets.)

To print row & column headings & gridlinesGo to the Sheet tab and click in the “row and column headings” and “ gridlines” boxesto put check marks in these boxes.

To print headers and footersClick the Header/Footer tab and follow the prompts. Note that a number of standardheader entries (page, name, date, etc.) are provided.

To center the printout on the pageClick the Margins tab and check Center on page horizontally or Center on page vertically .

To select portrait or landscape orientationClick the Page tab and select whether you want Excel to print down an 8 ½ x 11 pageor across.

To fit your printout on one (or x) page(s)

Click the Page tab and click the Fit to option in the “ Scaling” section. If you have aspreadsheet that’s just a row or column or two too large to fit nicely on a single page,this is a lifesaver of an option. It reduces your entire spreadsheet in size just enough tofit in the space you specify with the Fit to option.



D : M 2: I I : DF 1203

A ENDI 6 HE FAI GODMO HE AND HE MAGIC

AIN NOEL HI AKE



F G

, , .

A D BE

I , , . I 22 .

.

, , .

.

? .

, . I , . I

.

. I . I I I ;

. , .

I , . I . I .

.

. .

; , E , . ?

.

, . I .

100 . E

.

, $1,0 $2.73 ; ,

.



, . A I $2.73 . .

, , I . I $2.73

H , , , . , .

. I , .

.

, . H ,

, .

$1,000 .

$1,000. I . , ,

.

A . $140 . I 14%

,

$2.73 .

A . . $300 . ICE

$2,440. I I . . .

. $2.73 .

$926 . . . I

$7,537.

. I H . H . C

?

, . E B . .

$2.73 . I H $7,537 , $2.73 .



H , . H ,

$7,537 .

F .

, .

10. $2,707 $22,045.I 22 I

. H ? I ; $22,045

10 .

A , 21 $16,195, $131,876.

$2.73 .

A 21 I . I ?

. H $131,876? .

I . ?

. H . C , . H

$20,000 60.

. H $21,000 20 $39,000 . H $20,000 39

. $60,000 $780,000.

B . H $2.73

. B 31 HA F A I I D A $62,000

. , . 31 16 . I

37 . I .

$140,000 E I I D A . $2.73 F EE

!



A . I I I I D A 50 , E E I I D A 55 E F

I I D A 60 . A $2.73 37 .

. H . $2.73 ? . I

, C I .

I

, ,

, .

I $1,000

. .

.

I 1990, I

14% 7%. .

( ) 14% I 7%.

A 7% , $24 60 $41 ,

$37,000. 60.

. , I

.

F D E $2. ?



. G HA E F

AIA I

. G H A E F AI1 140 1 140 31 62 747 510 9372 300 2 440 32 71 671 583 6083 482 3 922 33 81 845 666 4534 689 5 611 34 93 443 760 8965 926 7 537 35 106 665 868 5616 1 195 9 732 36 121 739 991 2997 1 502 12 233 37 138 921 1 130 2218 1 853 15 086 38 158 231 1 288 4529 2 252 18 338 39 180 383 1 468 385

10 2 707 22 045 40 205 637 1 674 47211 3 226 26 271 41 234 426 1 908 89812 3 818 31 089 42 267 245 2 176 14313 4 493 36 528 43 304 660 2 480 804

14 5 261 42 843 44 347 312 2 828 11615 6 138 49 981 45 395 936 3 224 05216 7 137 65 255 46 451 367 3 675 42017 9 276 75 530 47 514 558 4 189 97918 10 714 87 244 48 586 597 4 776 57619 12 354 99 598 49 668 720 5 445 29620 14 083 114 681 50 762 341 6 207 63821 16 195 131 876 51 869 069 7 076 70722 18 602 151 478 52 990 738 8 067 44523 21 347 173 825 53 1 129 442 9 196 887

24 24 476 199 301 54 1 287 564 10 484 45125 28 042 228 343 55 1 467 823 11 952 27426 32 108 261 451 56 1 672 318 13 625 59227 36 743 299 194 57 1 907 528 15 533 17428 42 027 342 221 58 2 174 644 17 707 81829 48 051 391 272 59 2 479 094 20 186 91230 54 918 447 190 60 2 826 167 23 013 079

_dfp-module-2-appendix.pdf

Documents