Managing Data Analysis with Multiple Conditions

SUM & IF ARRAY as an ALTERNATIVE TO SUMIFS

In Excel, when you need to retrieve data off a table based on a criterion, you can easily use a VLOOKUP or HLOOKUP formula. If you ever want to sum the entire data which meets that single criterio, you use SUMIF in place of VLOOKUP.

Now say you have a great big table of downloaded report (called "SourceData" tabsheet) detailing both sales and expenses information by Cost Centre and Department for the whole year of 2011. On another worksheet that you call "Summary", $A10 is the first criteria (say Cost Centre of your organisation) that is listed down from cell A10 to A30. B$3 is the second criteria (say Department of your organisation) that you placed horizontally from cell B3 to B8.

And so it goes that you need to sum up the data based on multiple criteria. Yes, you can use SUMIFS that unfolds like =SUMIFS(SourceData!$K$2:$K$1000,SourceData!$E$2:$E$1000,$A10,SourceData!$D$2:$D$1000,B$3). But what if you're still using the older versions of Excel (prior to 2007) such as Office 2003, 2002 (XP), or even 2000? You don't have the luxury of SUMIFS function yet. The solution is to utilise the "array flavored" combination of SUM and IF.

Using the same logic of working as SUMIFS, this array formula will generate the same results. It goes like this {=SUM(IF((SourceData!$E$2:$E$1000=$A10)*(SourceData!$D$2:$D$1000=B$3),SourceData!$K$2:$K$1000))}. The uniqueness (or perhaps eccentricity if you want to call it that way) of this formula is as follows:
1. The two criteria are combined by using the multiplication operator (which is "*") which functions like the "AND" condition where you tell Excel that both arguments MUST be met to return the result.

2. It is the IF function that actually enables this formula to have the 'array' flavor because what it basically does is to compare individually IF each cell on range E2:E1000 is the same as cell A10, AND AT THE SAME TIME comparing each cell on range D2:D1000 is the same as cell B3. If both IFs match, please sum all the data up. That's what you want Excel to do.

3. Beware, though, that you have to press CSE combo button (Control+Shift+Enter) for this array formula to work so that the curly bracket {} shows automatically which marks it as an array. Hence, please do NOT type the curly bracket yourself in the Formula Bar.

4. The shortcoming of using an array formula is that you can't copy paste downwards easily like you're used to doing very quickly. Excel will display an error message saying "You cannot change part of an array" - seemingly due to the fact that Excel doesn't even vary the semiabsolute cell addresses that we intentionally use in the formula so they could be carried forward when doing copy & paste. This means you'll have to paste (Ctrl+V) it one at a time throughout the working range. Luckily, you can still copy by row and paste it downwards rather than one for each cell!

In any case, this array combination formula nicely does the job of SUMIFS equally well although you have to put up with its few weirdos. Not bad, really not bad for an alternative as well as a good chance learning about array. (EJ - 19/5/11)

Calculating the proposed Mining 40% Tax

What is actually the resource super profit tax?  What real issue is at hand?

The Resource Super Profits Tax - what is it?

Glenda Kwek

May 11, 2010

The Resource Super Profits Tax is a 40 per cent tax on mining profits, which is in addition to the usual company income tax. It is planned to start on July 1, 2012.

How does it work?

Mining companies are allowed to subtract a tax-free allowance of 6 per cent from their existing earnings - called the RSPT allowance.

Advertisement: Story continues below

In the first five years of the scheme, they can also subtract an accelerated rate of depreciation.

The remaining amount is taxed at 40 per cent - the "super tax". The remaining amount is taxed again, at 28 per cent.

For example

Revenue = $100 and

Expenses = $50

Your profit = $50

You can then subtract depreciation, with the allowance - set at 6 per cent.

So if RSPT allowance = $3

You are left with $50 - $3 = $47

The $47 is taxed at 40 per cent (the super tax).

So $47 x 0.4 = $18.80 and you are left with $ 28.20

The $28.20 is taxed again at 28 per cent (the company tax).

So $21.20 x 0.28 = $7.90 and you are left with $20.30.

That adds up to about a total tax of about 57 per cent.

How does it differ from the old tax regime?

*The royalties that resource companies have to pay to states now will be refunded by the federal government. Miners could argue that the royalties introduced uncertainty into the market as they were subject to change without notice.

*Company tax will be lowered from 30 per cent to 28 per cent.

*Resource companies can claim accelerated depreciationbefore the super tax kicks in, on prior investments for the first five years.

All new investment will be subject to the normal depreciation. The 6 per cent rate is applied here.

So do other industries have a similar tax regime?

Yes. The petroleum industry is one example. It has been operating under such a regime since the 1990s.

The difference is that it has a higher allowance - 11 per cent.

So why are there points of contention with the RSPT?

*It's not a super profits tax

Miners argue that the government is not just taxing more when it's boom time, but taxing more all the time.

"At the moment, it doesn't appear that there is this premium that comes in and out depending on whether these companies are making super or normal profits," UBS chief economist Scott Haslem says.

"That's the issue. It just appears like we have a higher marginal tax rate on every dollar forever."

*6 per cent allowance is too low

Back to our earlier use of numbers. What's at issue is the $3 (6 per cent of $50).

This 6 per cent is too low, miners say. It's how much return you get on an ultra-safe investment. But their investments cost a lot and are highly risky. They say they should have a higher allowance (which means a lower amount of money is taxed after the allowance is subtracted).

Petroleum companies, as mentioned above, have an allowance of 11 per cent. The resources companies are not arguing for such a high rate - but they do want something between 6 and 11 per cent.

(Note: The 6 per cent is calculated from the government's long-term bond rate.)

*$9 billion tax take a year

The resource companies dispute the government's figures that it will get $9 billion a year in super tax on miners.

$9 billion "is not a steady state long-term growth estimate", Haslem says. It's a lower tax take calculated when accelerated depreciation is taken into account. That means that if today's profits are replicated in five years after the regime kicks in, mining companies would be playing even more tax.

**Background on the RSPT was complied with the help of Scott Haslem, chief economist at UBS.

Source: SMH, 11 May 2010

If you want to read more:

The first hurdle: define a super profit

Who's next to cop a super tax  

New mining tax will cut long-term growth, UBS economists predict

Full federal budget coverage

 

Valuing A Property

To all property lovers,

highly relevant for you in or about to go into the market :-)

How do valuers value?

By Peter Boehm

May 24 1  May 24, 2010 

Peter Boehm

How much do you really know about property valuation? Peter Boehm takes us through the detailed process, and dishes some tips on what adds value to a property and what doesn't.

Whenever you borrow to buy a residential property your lender will instruct a professional valuer to get independent confirmation that the property represents adequate security for its loan and that the amount advanced will not breach the agreed loan to value ratio (LVR). The LVR is a function of how much is borrowed and how much the property is deemed to be worth. So a $300,000 home loan on a property valued at $400,000 would result in a loan to value ratio of 75% (300,000/400,000).

Valuations provide the lender with a price that could be realised if the property had to be sold within ninety days of purchase, given reasonable marketing and an arms-length transaction between a willing buyer and a willing seller. Lenders are looking at a worst case scenario where the property has to be sold reasonably quickly either through repossession or mutual agreement.

Valuations are not the same as a price estimate

It's important to understand that an estate agent's price estimate is not the same as a professional valuation. A price estimate merely acts as a guide to assist in the marketing and selling of the property - it is not a proper valuation and has no formal standing e.g. it's not recognised by the courts or by lenders when assessing a home loan application. A recognised property valuation can only be carried out by a fully licensed valuer.

The valuation process

A full and comprehensive valuation starts with an internal and external inspection of the property. It normally takes around 48 hours for a standard three page report to be produced. Valuation reports vary in price depending on the property type and the report format requested but for an average sized property costs start at around $300 plus GST.

Some of the key attributes the valuer will consider include:

• The land size, property aspect, topography and layout of the block.
• The size and layout of the residence.
• The location of the property in relation to schools, public transport, shops and amenities.
• The architectural style of the dwelling.
• How well the property has been maintained.
• What potential there might be e.g. renovation or redevelopment.
• Number of rooms, including bedrooms, bathrooms and the size of the kitchen.

Valuations are professional opinions based as much on art as on science because they take into consideration both tangible and intangible aspects of a property including its surrounds. According to Greville Pabst of WBP Property Group, there are three methods valuers use to come up with a property value range; direct comparison, summation and capitalisation of net income.

Different valuation methods

The direct comparison method looks at researching recent sales of similar properties (within the last six months) and comparing and contrasting those properties with the subject property. The comparison properties thereby act as a valuation guide enabling the valuer to compare like with like and to make adjustments (up or down) if there are any material differences between the properties.

The summation method comprises adding the value of the land to the value of the improvements on the land, which include things like the house, pool, garage and pergola. Land value takes into account things like its size, shape, location, topography and surrounding infrastructure and amendments. The value of the improvements is determined by taking into account things like age, style, architectural features, room numbers, renovations and overall appearance.

Valuers use a combination of these two methods to determine a valuation range and then use their skills and experience to come up with their valuation figure.

The third method, capitalisation of net income, involves applying an investment yield to the property to work out rental income which is then discounted to determine market value. This approach is more commonly used with investment properties.

What adds value to a property?

Valuers look for attributes that can add or reduce a property's value. It's useful to know which ones are good and which ones are bad as this can help not only in the selection of a property, but also in identifying undervalued properties and aiding in the type and extent of potential renovations. Some of the positive factors include:

• Aspect - things like water views can add significant value to a property.
• Proximity to lifestyle amenities.
• Land size.
• Development potential.
• Architectural style e.g. inner city Edwardian and Victorian styles.
• More than one bathroom - two is good.
• Modern well appointed kitchens.
• The condition and age of bathroom(s). As with kitchens, modern bathrooms are sought after.

What reduces a property's value?

Conversely there are certain attributes that may reduce a property's value. These include:

• Proximity to main road traffic.
• Mixed use property e.g. residential/commercial.
• Environmental risks e.g. overhead power lines.
• Poor proximity to public transport, shops and other amenities.
• Poor room layout and design which reduces openness of internal areas.
• The size and layout of rooms.
• Small land component.
• Poor state of repair of the buildings.

What happens if the valuation comes in under the purchase price?

Normally the valuation will come in around the purchase price - after all, that is what the market is willing to pay. But there could be times when it comes in under or over meaning you may have paid too much or got yourself a bargain. 

If the valuation comes in under the purchase price, you could be in a spot of bother because the lender's LVR restrictions may limit the amount you can borrow meaning you may not be able to afford the property.  Take for example a situation where you are looking to buy a house for $400,000 and have saved up a 10% deposit of $40,000 and your lender is willing to lend 90% of the property's value. So far so good because you'll have enough to buy the property i.e. $40,000 + $360,000 = $400,000. But if the valuation came in at $380,000, the lender would limit its loan to $342,000 ($380,000 x 90%) meaning you would have to find another $18,000 (see the table below) or in the worst case, walk away from the purchase - something you can't do if you've bought by auction.



How do you select a valuer?

Normally your lender will select a valuer from its panel - one that is local to the property you are looking to buy. However, there may be occasions when you'll want to appoint a valuer yourself - for instance, to help you set your upper limit if you're bidding at auction.

Whatever your reasons, if you're looking for a good valuer the first thing you should do is make sure he or she is a full member of the Australian Property Institute, Australia's peak body for property professionals. In addition, you need to check the valuer is appropriately licensed in the State or Territory they operate in. For example, valuers in NSW must be registered under the Valuers Act 2003. Finally, it's a good idea to find out who the valuer's clients are. For example, if they include any of the big banks this can give you some comfort you're dealing with someone decent, as the banks have strict valuation criteria.

Have you ever used a valuer before? Did they do a good job? (Share your views below)

Want to know how much your property is really worth? Check out Top Choice Home Loan's Right Price Report. Thinking about buying a home? Visit Top Choice Home Loans for everything you need to know about home buying, including a free seven week email course.

http://au.pfinance.yahoo.com/b/peterboehm/1194/how-do-valuers-value/