Global Top 100 Brands by Value: an Excel 2007 case study
Introduction
This page is based on the table of the values of the world’s top 100 brands by value. The aim of the page is to introduce that table and for you to apply some of Excel 2007’s techniques and functions to it. This page is essentially an Excel 2007 Table case study for you to work through.
You should work through this case study alongside the section in chapter 13 in the book entitle Excel 2007 Tables.
The Table
Here is a screenshot of part of the table of the global top 100 brands by value: this is the table as it should be after you have worked your way through this page.
When we found that table from its PDF file source it looked like this:
http://media.ft.com/cms/e5a01ad8-30d5-11de-bc38-00144feabdc0.pdf
We used Windows copy and paste utilities to copy this table over from the PDF file to Excel 2007 and this is what it gave us:
The Excel 2007 that accompanies this page provides you with the above table, in full. Of course, this is not very useful until we use the Text to Columns utility to separate out the various columns from what you see here: where every row of the table has been condensed into one cell in Excel 2007. This is the result of having used Text to Columns:
Again, not the finished product: we felt that delimiting by space was the best option in the Text to Columns utility to get us here. We then used the concatenate utility to provide use with:
Finally we converted the range to a table and removed the filters that are automatically set in the column headings: that gives us the finished table we began this page with:
This is what you have to do ... transform the initial range of data to this polished table. Feel free to use different colours and different row and column bandings. The aim, is to transform the raw data into useful information.
Adding a Calculation Column to a Table
One of the benefits of transforming the range of data into a table is that it allows us to prepare calculations columns. Try this: in cell 5I, enter this, =D5/E5-1 and press [Enter]. See what happens?
You will probably have to change the column heading that Excel 2007 has chosen for you to give you this, with the final column formatted as percentages:
Take a look in cell I5 and you will see this structured reference:
=Table1[[#This Row],[Brand value 2009 ($m)]]/Table1[[#This Row],[Brand value 2008 ($m)]]-1
Scroll down to cell I35 and you will see the error #VALUE!: to tidy that up we used the IFERROR(...) function, new to Excel 2007 as you know, in cell I5 and when we pressed [Enter] Excel 2007 updated every cell in that column for us.
You should create a new column, J, for the changes in Brand Value from 2007 to 2008 and rename the column accordingly. Use the IFERROR(...) function to eliminate any #VALUE! error you will find. What do you find in cell J5?
How about this? Enter =D18/E18-1 in cell K18 and press [Enter]. What happens and what should the column heading be? Do you need to use the IFERROR(...) function? If no, OK; if yes, do it!
Definitions (for Marketing people!)
The definitions used in the table being discussed here are:
Branded Earnings What proportion of a company’s earnings is generated “under the banner of the brand”?
Brand Contribution How much of these branded earnings are generated due to the brand’s close bond with its customers?
Brand Multiple What is the growth potential of the branddriven earnings?
Brand Momentum The Brand Momentum™ indicator that indicates each brand’s growth is based on this evaluation. It is presented as an indexed figure that ranges from 1 to 10 (10 being high). You can find these definitions in this fascinating document:
http://www.millwardbrown.com/Sites/Optimor/Media/Pdfs/en/BrandZ/BrandZ-2009-Report.pdf
The following definition expands on that rather elusive definition of brand momentum and it contradicts it: Millward Brown’s definition (above) shows momentum ranging from 1 to 10 whilst the FT (below) shows it ranging from 0 to 10.
Brand Momentum is an index of a brand’s short-term growth rate relative to the average short-term growth rate of our pool of brands
- A brand with a growth rate in the top 10% gets a momentum of 10
- Below 10, the index is linear to the short term growth rate, so that a brand with an average growth rate would get a momentum of 5
- Brands with above-average growth rates get a momentum ranging between 5 and 10, while brands with below-average growth rates get a momentum ranging between 0 and 5.
http://www.ft.com/cms/48fe984a-c0a7-11da-9419-0000779e2340.pdf
© Duncan Williamson
August 2009
Download this page in PDF Format
Download the Excel 2007 file for this page
In the final step, the growth potential of these branded earnings is taken into account. Both financial projections and consumer data is analyzed. This provides an earnings multiple aligned with the methods used by the analyst community. It also takes into account brand specific growth opportunities and barriers. To capture the weaker economic outlook, all projections have been validated using IMF economic growth forecasts.
The portion of these earnings driven by brand equity is called “Brand Contribution”: The degree to which brand plays a role in generating earnings. This is established through analysis of country-, market-, and brand-specific consumer research from the BrandZ database. This guarantees that the Brand Contribution is rooted in real-life customer perceptions and behaviour, not spurious ‘expert opinion’: in some categories, brand is important — luxury, cars, or beer, for instance. In categories like motor fuel, on the other hand, price and location play a very strong role. Furthermore, as markets develop, consumer priorities and the role of brand may change. And even in strongly branded categories, some successful brands that compete heavily on price.
First, the branded earnings are identified. For example, in the case of Coca-Cola some earnings are not branded Coca-Cola, but come from Fanta, Sprite or Minute Maid. Once identified capital charges are subtracted. This ensures only value above and beyond what investors would require any investment in the brand to earn is captured: The value the brand adds to the business. This provides a bottom-up view of the earnings of the branded business.
