Budget Day in the UK

Alistair Darling issues his most important budget today.

I would love to think he addresses the anomaly of an acute housing shortage and decimated construction firms with no work.

The UK has had a massive drop off in demand for the purchase of houses due to the credit crunch in the mortgage market, but still has a massive demand for housing (unlike the US, which has an oversupply of housing).

Surely the answer is to pay the construction companies to build council houses/social housing!

Good luck Alistair - you.re going to need it!

What does everyone else want to see in the budget?

Free Excel 2003 Pivot Tables Video

This video is now available here.

Excel Tip: Using GETPIVOTDATA (part 2)

This post continues from where we left in Excel Tip: Using GETPIVOTDATA (part 1) . We had explained the construction of the GETPIVOTDATA formula and how to use it to report a particular figure from the pivot table.

In this post we will look at the flexibility that can be achieved by using formulae to populate some of the arguments in the GETPIVOTDATA function.

To recap, the GETPIVOTDATA function has the following format:

=GETPIVOTDATA("Name of data field to return",location of pivot table,"field1","item1","field2","item2")

but each of the text fields (in inverted commas) can be replaced by formulae which, used intelligently, allow you to produce management accounts and reports in any format.

Examples:

You could replace "Name of data field to return" with a reference to the cell at the top of the column in your final report, which can hold the name of the data field you wish to return. By using [F4] to insert a '$' in front of the row but not the column in this reference, you can copy the formula across multiple columns using different data fields from the pivot table in each column on your final report. This is useful for reporting, say, month and year-to-date figures in management accounts.

Where the field item refers to the name of the raw data column containing a code that determines where the values go in your final report (e.g. a management accounts code) - if you replace the corresponding "item" field with a formula referring to the first column of the spreadsheet, this can be used to populate the rows of your final report. By using [F4] to insert a '$' before the column and not the row, you can now copy the same formula into every cell in your management accounts. Each row will show the value for that row, based on the code in the first column and each column will include the data field to use for the value, based on the first row.

How you get theses management accounts codes into the raw data is covered in an earlier post:

Do your management accounts take weeks, days, hours, minutes…or seconds to prepare?

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Golden Cells: The ExcelZone online video awards

It is always nice to get recognition for what you are doing and I was pleased to see our Introduction to Pivot Tables video course featured in Accountingweb's (a respected UK Accounting website) Excelzone online video awards. The video was described thus:

"Many of the Excel video pioneers are American, but a new generation of UK
producers has emerged in recent months led by Carlisle-based Emily Coltman. Her
four-minute Introduction to pivot table course video for
Feechan Consulting truly is a state-of-the art production, featuring shiny pink
introductory graphics, slick page-folding transitions and the now obligatory
enlarged yellow spotlight cursor technique. The content and pacing are well
planned and the narration crisp and authoritative.

You can see the free video here.

Ashton Kutcher and my 10 year-old: Why we should worry!

Two events this morning got me thinking about the future and what it will look like:

1. Ashton Kutcher reached 1 million followers on Twitter, ahead of CNN;
2. My 10 year-old son showed me the website he's built this week on his school holidays.

For those of you who don't know about Twitter, take a look at my earlier blog post, Twitter – What’s all the fuss about?

If we dig a bit deeper, these two seemingly unrelated events pose a stark threat (or wake-up call, depending on how you look at it) to those of us of a certain age (I'm 38).

Firstly, Ashton Kutcher, an actor, has become the first Twitter account to reach 1 million followers. CNN's breaking news account was looking like it would be the first to reach the million, and Mr. Kutcher laid down a challenge, among other things offering to buy 10,000 malaria nets for Africa if he got there first. CNN responded encouraging its TV viewers to log on and follow them. Even bringing out the big guns such as Larry King.

CNN has a team of staff updating its Twitter account, providing very valuable content, i.e. real-time updates of breaking news from one of the world's leading news networks. Ashton Kutcher updates his entirely on his own, with no additional resources beyond his PC, a mobile phone and a broadband connection.

Secondly, my son Ben, entirely on his own, has set up a website where he can post whatever he chooses (I'll be checking regularly). He got into Twitter last month.

What does all of this mean?

The world of communication is changing incredibly rapidly. One individual with a PC (granted he's a famous actor, but he's still one individual) can get his message out to the world and inspire a greater following than one of the world's biggest providers of "old-fashioned" communication.

A whole generation is coming through now that see these methods of communication as second-nature. They will be working in your business (and your competitors' businesses) very soon. They will also soon be your customer and suppliers.

Those of us in senior positions now - if we are still planning to be in the workforce in 20, or even 10 or 5 years time, need to sit up and take notice. The rules are changing, and we'll be dead in the water if we don't grasp the nettle now (a bit of a mixed metaphor, but you get the point). Going into an economic recession can only accelerate the process - those organisations, and employees, that understand these technologies will be the ones that thrive, and keep their jobs. Those that don't will be the ones that fall by the wayside.

I don't want to finish on a negative note. We still have a head-start on this next generation - there is a great opportunity if we act now. If you haven't already, set up accounts on Twitter (takes 5 minutes), Facebook, Linkedin and the like. Have a dig around and see what you can learn. Right now, the combination of business experience alongside these skills makes you invaluable, but that won't be a differentiator in a few years' time when this generation gathers experience. See you in there.

The time to act is now!

Good luck in this brave new world.

Advanced Management Reporting in Excel, London,20th May 2009

You know that you have all of the information you need in your accounting and management systems, however every day, week and month you (and your team) spend time pulling it from different sources, tweaking it and presenting it in a format that you (and the rest of the management team) can use.

What if you had the skills to automatically populate your existing spreadsheets and/or create new ones that give you all of the information you need, in a few clicks, when you want it. You can reduce time spent on management accounts preparation, for example, from days to minutes or even seconds.

This one-day workshop is a rare opportunity to learn my unique approach to automating all of your reports (from simple sales reports to complex management accounts). This is possible, no matter what accounting and management software you use in your business.

For just £275 plus VAT per delegate, you will learn how to:

• Interrogate your existing systems
• Analyse the resulting data using lookups and pivot tables
• Present it in any format, including your existing spreadsheets (no matter how complex), automatically

Click here to find out more and/or to book online.

Excel Tip: Using GETPIVOTDATA (part 1)

As regular readers will know, I am a huge fan of pivot tables and find them an incredibly useful and powerful feature of Excel. If you don't know how to create a Pivot Table, take a look at my free video.

For all of the power of Pivot Tables, sometimes the final format is not want you want and you need to use that data elsewhere. Management Accounts are a prime example of this - a pivot table can give you the numbers but you usually want these to then populate an ordinary Excel template, set out in your Management Accounts format. This is where the GETPIVOTDATA formula comes in.

GETPIVOTDATA allows you to specify the data you want to pull from the Pivot Table. The format is as follows:
=GETPIVOTDATA("Name of data field to return",location of pivot table,"field1","item1","field2","item2")
Where:
Name of data field to return is a text field, being the name of the pivot table field as it appears on the pivot table.
location of pivot table is a reference to a cell anywhere within the pivot table, it is often best to use the top left cell of the main body of the pivot table, as this will remain in a constant position no matter how much the data changes.
field1, item1,field2,item2,etc. are pairs of field and item names from the row, column or page (filter) areas of the pivot table. You can use as many pairs as you like. The field name comes from the original column heading in the data, the item name is the entry in that column that creates a unique row, column or page in the pivot table.

I think an example might be useful here:

To return the figure 15,813 for Matthew's sales for the South East, the formula would be:

=GETPIVOTDATA("Sum of Net",A3,"Salesman","Matthew","Region","South East")

This is useful when you are wanting to report one number, however you can really make use of this formula when you realise that any of the text arguments (in inverted commas) can be replaced with references to cells where the text can be found. You can then use this to populate any report layout, but I will leave this for a later post.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Top 10 current concerns of CFOs/Finance Directors

Thank you to Cindy Kraft of CFO-Coach.com for bringing this list to my attention.

The following is the top 10 current concerns of CFOs/Finance Directors according to the Duke-CFO Global Business Outlook Survey, Q1 2009, reproduced from a webcast available for download from www.cfo.com.

1. Ability to forecast results
2. Working capital management
3. Maintaining morale/productivity
4. Balance sheet weakness
5. Attracting and retaining qualified employees
6. Cost of health care
7. Pension obligations
8. Managing IT systems
9. Supply chain risk
10. Protection of intellectual property

Anyone have anything to add or disagree with the priorities?

I was surprised to see the absence of cost-cutting from the list!

Learn VLOOKUP with this short, free video tutorial

Following my earlier post on the Excel VLOOKUP formula, I have produced (again using the excellent services of Emily Coltman of Ask M, who produced the Pivot Table video training package) A short video tutorial on this useful function. I hope you find it useful.

http://www.screencast.com/t/SLlrqpl8

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Is your Easter Weekend covered by these 10?

A long weekend coming up and I bet there are a very limited number of ways in which we all spend it. I can think of ten:

1. Do nothing!
2. Get some jobs done around the house and garden;
3. Spend some rare time with the family;
4. Go away for a weekend break;
5. Visit friends;
6. Drink!
7. Eat!
8. Sleep;
9. Take work home;
10. Go into the office because you can't get any work done at home.

I am planning to do bits of 1,3,5,6,7 and 8. I will desperately try to avoid 2, 9 and 10.

I'd love to hear of anybody whose plans are not covered above. Please share below.

New look and feel - What do you think?

Regular visitors will notice a significant change to the look and feel of the blog - hopefully it looks more professional, that was the intention.

Please comment with your thoughts.

Spreadsheets by email

Those of you who read the Not Just Numbers ezine will be aware that Feechan Consulting has just launched the new Spreadsheets by Email service.

This service allows you to tap into Feechan Consulting's Excel expertise from a distance. Simply enter the details of any standalone spreadsheet you want developed (from anywhere in the world) and you will be provided with a fixed competitive quote. Should you wish to go ahead, simply pay by credit card and the spreadsheet will be emailed to you within the agreed timescales.

The service is already receiving a positive response from other blogs, see The Excel wizard has a new spellbook on Emily Coltman's blog.

Why not give it a try?

Excel Tip: Using VLOOKUP

Second only to pivot tables, the VLOOKUP formula is the one of the features of Excel that I am most often asked about. It is also one of the most useful.

Simply put, this formula allows you to look up an item from a list.

Its format is as follows:

=VLOOKUP(lookup value,range to lookup,column to return,TRUE or FALSE)

lookup value: This is the value you wish to search the first column of your table for (this can be the actual value, but it can be very powerful to refer to a cell containing the value).

range to lookup: This is the range (either named, or referred to directly) that contains the lookup table. It is useful to refer to whole columns as this allows your list to grow.

column to return: This is given as a number, where the first column (the one being searched) is 1.

TRUE or FALSE: This is a rather odd one. If you enter 'TRUE' or omit this argument, Excel will go to the closest value if the actual value being looked up is not there. This requires the first column to be sorted in ascending order. I can think of very few applications for this other than some form of conversion table, but as conversion tables are normally to help approximate a formula, there is not a lot of call for them in Excel where you can easily calculate the actual formula itself. For most financial applications you will need to enter 'FALSE' here as this will return an #NA error if it cannot find the lookup value.

The VLOOKUP formula is very useful for adding additional data to raw imported data in Excel. For one application, see my article on automating management accounts.