CBA and Cost of Capital

The CBA reports today that :

"There is no doubt when we look at the current cost of funding that rates are going to increase"

http://www.news.com.au/money/interest-rates/commonwealth-bank-flags-rate-hikes-separate-to-reserve-banks/story-e6frfmn0-1225938726546

Obviously the overseas roadshow then was a bit of a flop ..

The CBA has been hitting the overseas circuit over the last few weeks trying to drum up institutional support for Australia's mortgage market. Thanks to Kris Sayce at Moneymorning.com.au, it turned out that CBA was using dodgy figures to convince overseas lenders to continue to prop up Australia's housing market (http://www.moneymorning.com.au/20100910/has-commonwealth-bank-deliberately-misled-investors.html).

I think today's statement from the CBA really just sums it up.

What is an auction with no result ?

Reading the REIV's auction clearance figures, one cant help wondering what is an 'auction with no result' and how does it differ from an auction that is passed in ?

Well the REIV does not actually define on its website what it actually means.. so I really dont know. But this is what I think it means :

If an auction is passed in or passed in on a vendors bid then it implies that there was a bid made, and that bid did not reach the vendors reserve. So that is a result of sorts. A failure.

If the auction had no result, then I assume that there was no bids made, therefore the REIV conveniently omits these figures from its clearance rates.

So we need to adjust the REIV clearance rates to reflect the fact that a quantity of auctions didnt manage to generate a single bid.

The REIV results from yesterday are below :

TOTAL AUCTIONS

This week: 669
Last weekend: 492
This time last year: 577

S Sold at Auction: 370
SB Sold before Auction: 82
SA Sold after Auction: 2

Passed in: 215
Passed in on vendor’s bid: 135

Clearance rate: 68%

Postponed: 0
Withdrawn: 0
Auctions with no result: 127

http://www.reiv.com.au/home/inside.asp?ID=142

So, to calculate the real clearance rate we need to adjust the failed auctions to include those that didnt attract any bids (127 + 215 = 342) and similarly adjust the total auctions (669 + 127 = 796). Our adjusted clearance rate is now 57% not 68%.

Draw your own conclusions on the ethics of the method used to calculate clearance rates. Actually there is a very good quote on the APM website which would probably apply just as well to the REIV :

APM's attention to detail and a willingness to push the boundaries of mathematical possibility has given them the edge in Australian property value estimation.

http://www.homepriceguide.com.au/

I would certainly agree that the reported clearance rates are pushing the boundaries of mathematical possibility !!

Australian House Prices

This weeks post is a little off topic, however, the subject of Australian house prices is getting quite a bit of press, and I thought I would post some of my own analysis.

Considering that a significant proportion of home lending in Australia is financed from overseas lenders (this article suggests 30% http://www.smh.com.au/business/banks-pave-the-way-for-surplus-rate-rises-20101001-1612p.html), I thought I would approach the analysis by taking a look at Australia's current account deficit (CAD).

Treasury defines the CAD as :

The CAD measures the extent to which Australia draws on foreign savings to fund that portion of national investment that is not funded by domestic national savings.

http://www.treasury.gov.au/documents/1087/PDF/02_ABE_Keynote.pdf

CAD is influenced by exports and imports, particularly the price and volume of resources that Australia exports relative to the price and volume of goods and services that are imported. The surplus of imports over exports essentially has to be funded by overseas lending or equity investments - capital inflows.

This analysis hypothesises that this overseas lending is a key source of funds for home lending, and that the CAD might have some predictive value in determining future house prices.

You can download the CAD history from the Australian Bureau of Statistics

(http://www.abs.gov.au/AUSSTATS/abs@.nsf/DetailsPage/5302.0Jun%202010?OpenDocument).

You can download the Australian historical house price index (HPI) from the same site

(http://www.abs.gov.au/AUSSTATS/abs@.nsf/DetailsPage/6416.0Jun%202010?OpenDocument).

So, taking these two data sets (and setting to a common base) for the period June 1986 to June 2010 I have used excel to do a basic linear regression analysis.

So we have a pretty good match with an R squared of 73.6%. So there seems to be at least some sort of relationship between the two data sets.

Now if we exclude the outlier in the bottom right hand corner of the chart and run the analysis again we get 80.6%.

So what is the significance of the outlier - we cant just exclude data from our analysis because it doesn't fit our expectations. Well the significance of the outlier is that it is the June 2010 result, so all we have really done by excluding the outlier is run the analysis as at March 2010.

So lets have a look at the data from another perspective. The chart below looks at the two data sets over the timescale (I have reset both index's as at 1986) :

So the chart above certainly suggests there is some relationship between the CAD and the HPI over the period of analysis - and in fact we have shown with our regression that R squared is 73.6% (or 80.6% if we exclude the last quarter's data).

Treasury itself in 2005 even alludes to this relationship :

Over the next several years, however, the share of Australian GDP devoted to dwelling investment may decline gradually as the house-price boom continues to wane, reducing this contribution to the CAD.

http://www.treasury.gov.au/documents/1087/PDF/02_ABE_Keynote.pdf

The Treasury paper, however, considers house prices as a determinant of CAD not CAD as a determinant of house prices. Our analysis, however, does not indicate causation and hypothesises that CAD may be partially at least, determinant of HPI.

Interesting also, that Alan Greenspan also makes reference to the strong correlation between home mortgages in the US, and the size of the US CAD :

Interestingly, the change in U.S. home mortgage debt over the past half-century correlates significantly with our current account deficit.⁶

http://www.federalreserve.gov/boardDocs/speeches/2005/20050204/

So if we continue this line of argument, the next question is where is the CAD heading ? If we know whether the CAD is increasing or decreasing, we can hypothesise the effect on the HPI.

Fortunately, Treasury gives us some guidance in the 2010/11 budget :

The current account deficit (CAD) is expected to narrow from 4¾ per cent of GDP in 2009‑10 to 3¾ per cent in 2010‑11, with the trade balance moving temporarily into surplus. Export incomes are expected to rise substantially in 2010‑11, largely driven by increased commodity prices, but also by higher commodity export volumes. A wider net income deficit is expected to offset the improved trade balance somewhat, with a significant proportion of increased mining profits flowing to overseas investors. The CAD is expected to widen to 5 per cent of GDP in 2011‑12, a0arial, helvetica, cleank into deficit, as commodity prices fall slightly and import volumes continue to grow strongly.

http://www.budget.gov.au/2010-11/content/bp1/html/bp1_bst2-03.htm

So we can expect a 20% decrease in CAD during 2010/11 according to Treasury's budget paper. The June 2010 actual CAD indicates that Treasury's estimates might be on the conservative side. In any event, utilising the estimate of 20% decrease in CAD, what does this equate to in terms of expected HPI.

Referring back to our linear regressions above the gradient in each of the regression calculations is around 0.8 - in other words Δ CPI = 0.8 Δ HPI

Rearranging the formula, Δ HPI = 1.25 Δ CAD

So a 20% decline in CAD, according to our hypothesis, results in a 25% decline in HPI.

So with the last quarter's CAD declining by 60% as shown on our chart above, and current analysis of the news suggesting that this trend might be maintained, this blog hypothesises that we may soon see some correction in the HPI. We will watch and wait for the September quarter results of both the CAD and the HPI and post a follow up.

Australian Linehaul

I thought I would write a topic today on Australian Linehaul. Ive been doing a lot of analysis of the topic recently, and have been really struggling to find a good concise summary online.

By linehaul I mean interstate transport. Semi Trailers. Whilst I am writing in an Australian context there might still be some value in this analysis for overseas readers.

I am going to focus on a couple of key areas in this analysis :

1. The Industry
2. Equipment
3. Rates
4. OHS
5. Service levels
6. Perth


1. The Industry

As I write, the frieight industry is characterised by overcapacity, and consolidation. There are more or less 3 types of players; the big players, the medium sized often family owned players, and the owner operators and subcontractors.

The big players are currently buying up medium sized players picking out the crown jewels from their contracts. The big players tend to use a mix of their own equipment and subcontractors, and tend to have national coverage including rail and coastal freight.

The medium sized players more or less tend to own their own equipment and occupy niches in the marketplace.

The owner operators and subcontractors operate a loose network communicating often by radio, and operate mostly based on personal relationships and a network of subcontract arrangements. Sometimes the equipment will be owned and maintained by an investor, and the driver operates for the investor under some sort of contract arrangement.



Applying Porters 5 forces we can summarise the industry as follows :

Barriers to Entry
- Low, all that one really needs is a drivers license and some equipment.

Power of Suppliers
- Key direct input is diesel fuel, price is set at the pump, and power of suppliers is high. Overhead costs such as registration and vehicle maintenance are very high, and non negotiable.

Power of Buyers
- Currently very high. There is little incentive for a buyer to commit to a contract in current circumstances. Very difficult to differentiate ones service in order to obtain a premium.

Threat of Substitutes
- On this element, linehaul freight is fairly well protected. There really isnt a credible substitute (with the exception of Perth which we will detail later). There is some rail and coastal freight availability, but across the eastern seaboard there is little benefit in these options due to handling constraints affecting lead time.

Level of Competition
- Currently very high. The bigger players are trying to saturate the market with low pricing to gain share.


On balance, one has to concede that currently the linehaul industry in Australia is not attractive from a profitability standpoint. Above market rents are uncommon except where one can occupy a niche. Power in the market comes from scale as the large players are demonstrating.



2. Equipment

Whilst I am no expert on equipment, there are a couple of key things to know about trucks. I'll start with some terminology.

The truck part at the front where the driver sits is called the Prime Mover. Some prime movers are flat at the front, and some of them have a hood. The flat nosed prime movers are often purchased in order to put a longer trailer on the back as there are legal constraints applying to the total length of a vehicle. Newer prime movers have better fuel efficiency enabling operators to reduce their fuel cost relative to operators running older equipment.

The part at the back of the prime mover where the trailer connects is called the turntable. The trailer rests on the turntable providing considerable stability to the truck.

Lets look at trailers next. In Australia you can more or less have a single trailer, or a B double combination. Single trailers are just that - a single trailer towed behind the prime mover. A B double combination consists of a smaller 'A' trailer behind the prime mover, the 'A' trailer has its own turntable at the back, and the second, or 'B', trailer sits on this turntable.

A standard single trailer holds 22 pallets - 2 across, and 11 down. A standard pallet is 1.2 x 1.2M, so the trailers are 2400mm across, and about 13.2M long (internal dimensions). It is possible to have a 24 pallet trailer, but these are not so common.

A standard 'A' trailer on a B double holds 12 pallets - 2 across, and 6 down. So a total B double combination holds 36 pallets. I dont think you can legally put a 24 pallet 'B' trailer on a B double combination, although I could be wrong on this.

Many trailers have a flat deck, although you might have noticed some trailers that have 2 deck levels. These are called drop deck trailers, and one is shown below.

A drop deck trailer increases the cubic capacity of the trailer by lowering the height of the deck. Drop deck trailers are good for freight which is high cubic, or overheight. The maximum height of a trailer is 4.3M without special permits, so a lower deck enables the internal height of the trailer to be maximised.

A flatbed trailer is good especially in rear loading situations (which suits a lot of loading docks). A drop deck has to be loaded from the side (because of the step) and so requires a different sort of loading dock arrangement.

On any sort of trailer, the deck height is determined by the height of the turntable on the prime mover. There is no standard dimension for deck height. On a drop deck trailer, the step can range anywhere from 300mm to 420mm or more, so again there is no standardisation across equipment.

On a drop deck trailer, the part of the trailer that sits above the turntable is called the gooseneck. This is probably due to its shape. Most trailers tend to have about 2850mm clearance from the floor to the roof, though ones ability to utilise all this space can be constrained by a metal bar and rubber flaps that run down the length of the trailer, as well as ribbing across the roof.

There are also other sorts of trailers such as refrigerated trailers which I am not so familiar with, so I will avoid going into them in this analysis as I will probably write something incorrect.

3. Rates

This is the good stuff (and very hard to find anywhere online). I'll start with a disclaimer - these are my own opinions only of what are market rates, and I am sure there is a diversity of opinion.

Overall, nobody charges more for a drop deck trailer than a flatbed. If your operator is trying to make you pay more for the extra cubic, find another operator.

Before talking about rates one has to understand the dynamics of the market. If you understand the supply and demand imbalances across major routes you can understand the rate structures. These dynamics probably change over time as well, so please keep in mind that I am writing as of todays date. All rates include fuel levy but not GST.

Melbourne

A lot of freight comes out of Melbourne relative to what goes into Melbourne. This means pricing out of Melbourne is higher than pricing into Melbourne. Sydney - Melbourne is considered a backhaul, and most operators cant wait to get to Melbourne to grab another load.

For a single trailer Sydney to Melbourne you should be paying no more than $1200 per load, and for a B double you should be paying no more than $1600.

Coming back, prices are about 20-25% higher, so you can be paying up to $1500 Melbourne to Sydney for a single trailer, and $2000 for a B double.

Sydney

A lot of freight comes out of Sydney as well, and tends to go north to Brisbane, and south to Melbourne. Rates into Brisbane are much higher than rates into Melbourne (despite there not being that much difference in distance) for the reason stated above. Sydney - Brisbane is a premium route with lots of freight going up north.

Sydney - Brisbane you should be paying about $2250 for a single trailer, and up to $3200 for a B double.

Brisbane

Hardly any freight comes out of Brisbane. In fact I would estimate that the majority of trucks coming south from Brisbane to Sydney are coming back empty. Brisbane - Sydney is definately considered a backhaul, and some operators dont even bother to look for freight back down to Sydney. It is more profitable for them to get the truck back quickly and send it up again.

Brisbane - Sydney you should be paying no more than $800 for a single trailer, and $1100 for a B double.

Adelaide

I am not so familiar with the idiosynchracies surrounding Adelaide, but suffice to say, you should not be paying more than $3000 for a B double into Adelaide, and $2200 for a single trailer.

Ive devoted a whole section to Perth so I wont get into that here. There are also routes up further north into Townsville and Darwin which I am not so familiar with. I know that when its fruit picking season, anything into the far north is a backhaul so you should be getting good rates. Remember though, Townsville is further away from Brisbane than Brisbane is from Sydney, so you are looking at at least a couple of days to get your freight up there. There are operators in Brisbane who specialise in that route.

Actually there is a great article here which I just recently found that talks about what subcontractors are getting paid for various loads :

http://www.ownerdriver.com.au/news/articleid/41760.aspx

Heres another article that gives a pretty reasonable breakdown of an operators costs - although I think the fuel costs in the report are based on city driving. On the Volvo website it gave a fuel economy of 40.1 liters per 100km's on one of their prime movers :

http://www.business.vic.gov.au/busvicwr/_assets/main/lib60070/owner%20drivers%20rc%20transport%20semi-trailer%202008%20update.pdf

4. OHS

This is a really, really, super important section. I'll start by saying that there are far more authoritative sources available on this super important stuff than my little blog. So for that reason I wont go into too much specific detail other than to say this :

If you are not aware of your responsibilities under the legislation it is not a valid defence.

You could be opening yourself up to criminal liability and you can go to jail if you ignore your responsibilities.

I have included a link below for further information :

http://www.ntc.gov.au/filemedia/Publications/HVDF_ChainResponsibility_July08.pdf

Download it. Read it. Understand it. Its important.

5. Service levels

I'm going to briefly cover standard service levels in this section. By service level I am referring to lead time and deliveries. I dont deal so much with the Aldi's and Coles and Woolies of this world so I dont know much about delivery windows and that sort of thing.

Sydney - Melbourne, Sydney - Brisbane, and vice versa are overnight services. Brisbane is 11 hours away, Melbourne is 9.

Remember that a driver can only work 14 hours out of any 24 hour period.

This means that when he or she gets to Brisbane they dont have a lot of time to do deliveries. Some operators will swap drivers and put in a jockey, but this will cost you money. Most operators will do 1 drop and then a depot for free, any additional drops you will be looking at at least $100 per delivery and possibly a minimum hire or demurrage cost. Traffic in Brisbane is terrible. You should look at the option of crossdocking onto smaller rigid vehicles if you have multiple deliveries.

Melbourne is much the same as Brisbane, although, because it is 9 hours away the driver has a little more time. Melbourne, however, is a big city. The extra 2 hours that your driver has will be lost if he has to travel across town to make deliveries. You will also be looking at at least $100 per delivery, and possibly minimum hires or demurrage costs. Again, look at the option of crossdocking onto smaller rigid vehicles.

Some operators will go through a Distribution Centre (DC) to break the load down into smaller deliveries. You can add an extra day onto your leadtime for this but it tends to be fairly cost effective.

In my experience what works very well is to have a crossdocking facility set up with a 3PL operator. You can take advantage of the 1 drop then depot arrangement from your operator, but using the 3PL facility as a depot you can have semi permanent rigid trucks on hire to receive the linehaul trailer. If you go with the right 3PL partner you can negotiate to have the rigid driver unload your freight onto the rigid truck, and you can have your deliveries out same day at very reasonable cost.

6. Perth

The key thing to bear in mind about Perth is that its far away. Perth is further away from Sydney than New Zealand.

Because it is far away, the freight dynamics are a little bit different to the eastern seaboard. Whilst road transport is important, it is not the primary method for getting freight over there. Road transport is normally handled by husband and wife teams who rotate driving responsibilities to get there in a continuous trip. Road is probably the fastest and most flexible way of getting there, but it is also the most expensive.

The primary corridor for freight into Perth is rail. The delays caused by loading containers onto the trains are offset by the speed at which the trains travel. There are a number of trains per week - I think they leave Sydney on monday, wednesday, friday, and saturday. The monday train is the least popular so you might be able to negotiate better rates on this service.

Containers are destuffed in Perth and deliveries are done either by rigid trucks, or the deliveries can be done straight out of the container on a semi trailer. You will need to compare the costs of each of the two options. If you have a couple of large deliveries you are better off doing it out of the container, if you have a large number of small deliveries you are better to do it on rigid trucks.

Lead time for rail freight is about a week door to door. The wednesday service is faster and you can load the container on a tuesday and have it delivered in Perth on the friday. Pricing on a 40' container Sydney - Perth excluding delivery is around $3800 and for a 20' container it is about $2400.

Some operators can offer you 48' containers. These can be really good particularly if your freight is cubic. 48' containers also tend to have more internal height so you end up with about 30% more cubic meters. Cost on a 48' container is about $4200.

Another corridor for Perth freight is coastal. A lot of ships come from New Zealand and stop at Sydney, Melbourne, and Perth before going somewhere else. Lead times on coastal freight are longer, allow 2 weeks door to door, but the costs are lower than rail freight.

Recent changes to the legislation have made it unattractive for coastal operators to provide this service. In a nutshell, if a ship stops at more than 1 Australian port, it is now subject to Australian legislation (OHS and Worplace Relations) for the period between stops.

A lot of shipping lines are thus taking capacity off the coastal route, and this is pushing up prices reducing the cost difference between coastal and rail. Current costs on a 40' container Sydney to Perth by coastal freight is about $3300. 48' containers are not available on coastal.

Excel Tips and Tricks III

Goal Seek

Maybe you have seen the Goal Seek option on the Tools Menu and wondered what it is. Or maybe you have been in the situation where you are trying to solve an Excel problem by populating a cell with different values to see which one works the best.

Goal Seek is a very slick and easy to use Excel feature that will basically determine a cell value for you by trial and error. Its found on the Tools menu as shown below :

I think Goal Seek is best explained using a very simple example. I have set up a simple formula below to calculate profit where margin per unit, and total overhead are known.

I want to calculate the breakeven volume based on these parameters. I could do this a number of ways, but since this is an example of Goal Seek, I will use Goal Seek to calculate my breakeven volume.

Select Goal Seek from the Tools menu, and the following screen pops up :

All I need to do now is set cell $c$8 (the profit) to value zero (breakeven) by changing the value in cell $c$6 (the volume). Hit OK.

In about half a second goal seek finds the solution which is that at a volume of 133,333, profit is zero, and therefore 133,333 is your breakeven volume.

Obviously this is a very simple example, but you get the picture. Its a very powerful tool that can be used in quite complex spreadsheet models.

Excel Tips and Tricks II

I thought I would write another instalment of Excel Tips and Tricks. This time I will look at Excels capability for data analysis.

If you've used Minitab or another statistical package you might have been frustrated that you cant easily export results to Excel, or vice versa Minitab tends to be pretty clunky when you want to drop an Excel list into it for analysis. You might be wondering why Excel doesnt include some statistical analysis functionality.



Well, wonder no longer, Excel does in fact come pre-installed with some pretty powerful statistical analysis capability, it just happens to be hidden away in the Tools-Add Ins menu.


The first time you want to use the Data Analysis functionality you will need to activate it through the Tools - Add Ins menu option see the image below :

From the Add-Ins menu you will need to check the box for Analysis Toolpak and hit OK. There is no messy CD instal required, it is all preinstalled you just have to activate it this way.

Once you have activated the Analysis Toolpak, you will see that under your Tools menu bar, the Data Analysis option now appears. Once installed it will reside in your Excel Application forever, or until you uninstall it I guess.

Why Microsoft chose to hide away such great functionality is beyond me. Since I discovered this little nugget I would say I use it at least once a week to perform any basic statistical task.

Features accessed through the data analysis function include histograms, regressions, basic statistical data, ANOVA, covariance and correlation p tests and f tests .... the list goes on.

Unfortunately for Minitab, since discovering this great feature in Excel, I have never had the need to use their software again.

Easy Linear and Quadratic Regression in Excel

OK so you can use the Data Analysis add in for linear regression, but it can be a little bit technical, and sometimes you just quickly want to see the line of best fit and know the gradient, intercepts, and R squared.

This is where the Excel chart trendline function simply cannot be beaten.

In the chart below, I have a scatterplot of time based data values. The actual data I've used below is monthly demand of a material. I used the linear regression to help calculate a forecast for future demand.

First, right click one of the data points to get the context menu. From the context menu select "Add Trendline" as shown.

The following window pops up. Simply select the type of regression you want. In this case I've selected linear regression but you could just as easily select logarithmic or polynomial for non linear regressions. You can also select moving average but just remember that Excel wont give you a formula for moving average and it wont have any predictive value.

Once you've selected the type of regression you want, click on the options tab to jump to the next tab.


On the options tab you just want to click on the two checkboxes shown below - the first one to display the equation on the chart, and the second one to show R squared. Hit OK.

Voila ! Excel automatically calculates the linear or non linear regression, illustrates it on the chart with a trendline, and even gives you the regression equation.
This is an enormously powerful Excel statistical application and it is just so easy to do.

For the uber-nerds you can easily compare a linear regression to a logarithmic, quadratic, or even higher order regression (by selecting polynomial and adjusting the order value in the trendline popup window) to determine the highest R squared value, and consequently the most appropriate regression model.

Excel Must Knows

I have been using Excel myself for near on 15 years and would consider myself a power user. Despite this I can only do the most basic of macros. To be honest I never even use macros. In my view macros are the most overrated feature of Excel I have ever come across.

There are 3 Excel must knows that will get you through almost every business analysis problem - these are the pivot tables, the vlookups, and the sumifs. In addition, to make ones life a bit easier there are a number of short cuts and basic features that are imperative to know in order to make your life more convenient.

I'll give a brief overview below, but intend to add more detailed training notes in future blogs :

Pivot Tables

Without a doubt the pivot table is a fundamental feature of Excel that bridges part of the gap between a what is a flat file and a database application.

Pivot table will summarise your data in a format that is easy to read, and is also easy to change should you wish to view your data slighly differently. Things like summarising sales by region or by product, or summarising deliveries by state or by date, are typical applications of the pivot table.

I tend to use pivot table also when I want to condense a list to its components as its the fastest way I have found of doing this. For example if I have a list of every single customer order over the month, and I just want a list of products that the customer has purchased, or I just want to know the list of purchase order numbers (without all the detail) I would use pivot table to quickly return that information.

Another way of doing the same thing would be to use the subtotal function, but I am not in favour of using subtotals and rarely use them for the sole reason that they mess up what was originally a perfectly good data file by inserting rows and data which more or less renders the data file useless for any future pivot tables.

The pivot table wizard is accessed through Data menu. I am using a Mac in this instance, but on a PC it is pretty much the same.

You will need a data file to practice the pivot table function.

The Pivot Table wizard is fairly self explanatory and with a bit of practice you can work out pivot tables without needing to go into detailed training notes. Just remember to click on the 'Layout' button to get into the meat of the process. The Pivot Table wizard will default to creating the Pivot Table on a new data sheet. I would always recommend this option as data sheets are free, and if you insert the Pivot Table in an existing data sheet it will overwrite any pre-existing cells.

One tip is always to double click the row attributes to get rid of subtotals as they tend to clutter up your pivot table with fairly meaningless aggregations of data. Get rid of the subtotals by double clicking the row attribute within the layout function of the wizard and changing the radio button from 'automatic' to 'none' for each of the row attributes. You can always add them in again later if you decide that you do want them by right clicking the pivot table and selecting the wizard.

90% of the time also you are using Pivot Table to sum data. The wizard (for some reason known only to Microsoft) will default to a count of data option. In the layout function of the wizard you need to double click on the Count Of .. button in the data section and change it to Sum Of .. in order to correct this.

One great feature of the pivot table is that you can easily drill down on any calculated cell to take a closer look at the data. Just try it. Double click on the data, and Excel will create a new sheet with a filtered list from the original data file containing the relevant data. This is a very fast alternative to Filtering the data. To avoid cluttering up your file always remember to delete the sheet afterwards (unless obviously you want to keep it).

One thing to remember about Pivot Tables is that they take a snapshot of the data as it existed when the Pivot Table was originally created. If you change any of the data in the original data file you will have to right click the Pivot Table and choose 'Refresh Data' for the Pivot Table to update.

Vlookups

If Pivot Tables are the bread then Vlookups are the butter.

Vlookups enable you to cross populate 2 or more different data files. For example if you have one data file containing sales by customer, but you have another data file containing customer by state, and you want to create a file containing sales by state, then you are looking at using Vlookups.

The critical thing to remember about Vlookups is that they are only good for 1 to 1 relationships. In the above example, if a customer has more than 1 state, then Vlookups will not work and you need to be looking at Sumifs (next section).

To practice Vlookups you will need 2 different data files with one thing in common, and it has to be a 1 to 1 relationship. Type in '=vlookup(' and hit enter for the wizard to pop up.

I am not going to go into the detail here but there is another critical thing to remember about Vlookups... The 4th attribute is a false/true, or 0/1 attribute. In my 15 years of excel use I have never used a '1' as the 4th attribute. Unfortunately, in its wisdom, Microsoft defaults the 4th attribute to a '1'.

What this means is as follows. If the 4th attribute is a '1' and the vlookup cannot find an exact match, then it will find the next closest match (which defeats the purpose of using a Vlookup). It is imperative that you make sure the 4th attribute is a '0' so that if the Vlookup cannot find an exact match it will return a 'NA#' value. This will tell you that there is something wrong with your formula, or that there is some data missing from one of your files.

There will be times when you want your formula to return a zero instead of 'NA#'. For example if you are looking up a sales volume from another data file, and if there is no sales volume for the product you are looking up, the Vlookup will return a 'NA#' Whilst this is a logical outcome, you might have another sum formula adding up all the sales volumes and the 'NA#' will prevent the sum formula in the secondary process from adding up the values.

In this instance you need to use an =if(iserror(), , ) or =if(isna(), , ) to convert the 'NA#' value to a zero to facilitate the secondary sum process.

Sumifs

If Pivot Tables are the bread, and Vlookups are the butter, then Sumifs are the mayonnaise that you might need when the Vlookups just might not cut the mustard.

As we know Vlookups are good for 1 to 1 relationships, but what about when there is a 1 to many relationship ?

For example you might have a file containing every customer sales order, and you have a list of specific customers, but you want to return a list of total sales for your specific list of customers.

A Pivot Table is not the best option because you are not interested in every customer, and a Vlookup will not work because there are multiple sales per customer.

One solution is to do a Pivot Table on the sales by customer, and then do a Vlookup on the total sales for each customer on your list of specific customers. A Vlookup on a Pivot Table will certainly work, but the limitation of a Pivot Table is that it takes a snapshot of the data, and it does not return live information. The Vlookup on a Pivot Table solution is therefore very clunky and there is a much more elegant solution.

The elegant solution is the Sumif formula.

The beautiful thing about Sumif is that it only relies on 3 attributes; the data field you are looking up, the reference field that you are looking up on, and the data field for which you want to return the summed information. Unlike the Vlookup it will never return a 'NA#' value. If it cant find anything it will just return a zero.

For example if I want to add up the total sales by customer or by material on a list of customer purchase orders; and I have a list of the specific customers or materials that I am interested in, the Sumif formula is the perfect solution.

Honestly I spent years in the wilderness doing Vlookups on Pivot Tables until someone showed me the Sumif solution and I have never done a Vlookup on a Pivot Table since then.

Short Cuts and Basic Features

Copying Down by Double Clicking the bottom right hand corner of a cell

Text to Columns to convert text fields into numeric fields

Find and Replace "." with "/" to convert SAP dates into Excel dates

Window - Freeze Panes to lock column and row headings in for analysis

=trim() to remove superfluous blank spaces

'Page Setup - Rows to Repeat at Top' to format large printouts

Tools - Options - Gridlines to remove excel gridlines for printing/presentation

Dead Stocks

We are probably all familiar with aged stocks. Those items that appear way down the right hand side of the aged inventory register are generally there for a reason .. slow moving, obsolete, or simply forgotten about in the pace of day to day business.

So what then is a dead stock ? It is best explained with a diagram.

In this simple example assuming constant demand and periodic purchasing, the dead stock component is that part of stock that is never consumed. It represents waste.

I only have metrics from my own experience but in my view it would not be unusual for 1/3 of inventory to be classed as 'dead stock' over a typical 3 month period.

Dead Stock vs Aged Stock

Most if not all aged stock is dead stock, however, not all dead stock is aged. This distinction is important. In the above diagram for example it is clear that the stock is not aged, in fact it turns over approximately once per month. Therefore it will never show up on an aged stock report. The fact that it is turning over quickly means that it can be fixed relatively quickly.

Dead stock management is therefore a very effective way to reduce inventory in the short term.

Identifying Dead Stock

Aged stocks by their nature are relatively easy to identify (using an aged stock report or aged inventory trial balance), but typically can be challenging to fix. If the material is slow moving, then it will take some time to run it out. If the material is obsolete, then the activity of reworking, writing off, or otherwise disposing of it can take some time and generate cost.

Dead stocks on the other hand can be difficult to identify, but the fast moving dead stocks by their nature can be fixed relatively quickly.

For SAP users there is a transaction called MC50 "Dead Stock Report" that quickly identifies dead stocks. Run at a plant level, or even for a list of materials, the report will rank dead stock balances over the specified time period. Over longer time periods expect the dead stock balance to be smaller than over short time periods because over a longer time period there is a greater probability that the stock will have run lower than over a short period.

Ive included a snapshot of the SAP MC50 transaction below. A tip for optimising the performance of the report is to run it from values 1 to 99999999, rather than unbounded.

Reducing Dead Stock Balances

Reducing the dead stock balance comes down to planning and process control. If your purchasing and or planning processes are highly automated, the remedies tend to revolve around forecasts, safety stocks, lead times, re-order points, and re-order quantities which can be easily maintained.

The MC50 Dead Stock report will automatically rank your dead stocks from highest to lowest. You can export the data into an excel spreadsheet to create a Pareto Chart similar to the one below.

In this example, there are a total of 61 materials in inventory for this product group. In fact, when I compared the dead stock balance to the inventory balance, 62% of the total inventory was dead over the last 3 months !

Looking at the above chart, 50% of total dead stocks are made up of just 10 materials. What this means is that I can reduce total inventory for the product group by 1/3 if I just fix the dead stock balances on 10 items. When the problem is broken down like that it doesnt seem so difficult any more.

I can break the problem down into 10 material level strategies, and solve them all one by one or simultaneously.

Viewing the inventory history

SAP has a neat functionality built into the Dead Stock report whereby you can view a graphical summary of the dead stocks over time.

Run the dead stock report, and place the cursor on one of the material numbers. From the top menu buttons, click on 'Detailed Display'. You will see a pop up window with a number of options, and you can jump from these options to a number of screens. Click on 'Stock Level' and hit the green tick.

This is a great tool for getting a quick feel for a material inventory. In the above example we can see that back in 2007 we had an overzealous purchasing officer who brought in way too much of this material, and in the years since then we have fine tuned our purchasing and forecasting in order to manage it a bit better. Over most of 2010, however, most of the stock has fallen dead yet we have still brought more in. In this instance I would be looking at the forecast accuracy on this material, with a view toward reducing the safety stock. As you can see though, the material turns fairly regularly, and therefore is unlikely to appear on our ageing reports.