Not Just Numbers

Do you deny how much your team uses Excel?

2012-04-24T11:46:00.001+01:00

"A prudent question is one-half of wisdom."

In too many years of experience helping organisations to make better use of Excel, one truth has become evident:

The higher you move up an organisation, the less they think the organisation uses Excel.

I am currently talking to many accountancy practices and this seems even more true in that industry.

Causes of Excel Blindness
There are a number of reasons why this should be true but it can be incredibly damaging for reasons I will go into later in this post.

I believe that the main causes are as follows:

The day-to-day experience of those involved - The higher you go in any organisation, the more your role involves relationships and meetings, rather than hands on number-crunching. Naturally those producing the numbers and analysing them use Excel far more than those discussing them;
Belief in computer systems - Having invested in accounting and/or ERP packages (or in the case of accountancy practices, accounts preparation software), and been sold the omnipotent nature of these packages by the software companies, it can be difficult to believe (or face) that there are still large amounts of work done on spreadsheets.
Over-simplification of what processes involve - A manager, for example, may understand that a member of the team is emailed a particular piece of information from a customer,a supplier, another branch or another department, and that they enter this into the system. The member of the team involved will know that they receive this information as an Excel file and they apply numerous sorts and filters, delete columns and rows, calculate totals, etc. before they enter it (or even import it) into the system. All of this work is done in Excel - and the manager is completely unaware.

Why does it matter?

It matters because it can be incredibly damaging to an organisation in terms of both financial risk and inefficiency

Financial Risk

Not understanding the systems that your business relies upon can lead to errors in systems management don't even realise are being used. The controls in the ERP system or accounting software may be perfectly adequate, but if the information entered is coming from a spreadsheet with an inbuilt error, they won't be of much use.

Inefficiency

In most organisations, this is by far the greater cost. Many staff are using Excel for a great deal of their time and have very little training or understanding of how best to use it. Many hours can be cut from most employees' working weeks, with a little bit of focus on this area.

Let is use the situation described earlier to demonstrate both the efficiency and financial risk elements:

"The member of the team involved will know that they receive this information as an Excel file and they apply numerous sorts and filters, delete columns and rows, calculate totals, etc. before they enter it (or even import it) into the system."

This whole process could be automated in Excel so that a couple of hours of messing about, could be reduced to a couple of clicks. This not only saves those couple of hours every month, week, or even day, but ensures that the same conversion is applied to this spreadsheet each time, significantly reducing the risk of error.

What can be done?

The first step is to acknowledge how much your organisation uses Excel. No matter how sophisticated your systems are, there are many jobs around the edges of the system for which Excel is not only the best tool, it is often the only tool capable of the flexibility required. Acknowledging this can lead to some very quick improvements to efficiency.

Ensure that you have access to someone (either inside the organisation or from outside) who can look at this Excel use and suggest and/or implement improvements and train staff. I offer this type of service to UK accountancy practices via my Excellent Accountancy business, but please feel free to email me if you wish to discuss how to go about it for any other type of organisation.

Excel is not just Word for numbers, it does form parts of your key processes - whether you like it or not.

Imagine if you took the same lax approach to any other key processes in your business.

A little time and/or money invested in improving how you use Excel will go a long way, as it is such an untapped area in most businesses.

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.

Use Excel to generate invoices and report on sales

2012-04-11T19:16:00.000+01:00

I come across many small businesses who use Excel (or Word) to produce an invoice template that they can email or print and post to their customers.

Usually the sale detail is entered on the face of the template and this is saved as a separate file for each invoice. The problem here is that to be able to report on this information for accounting purposes, or sales analysis, the information needs to be recorded somewhere else in one file (either another spreadsheet, or an accounting software package).

What is really needed is to be able to enter the sale details once and for this to be used to populate both the invoice template and the sales reports.

If an accounting package is being used, the best answer would usually be to use an invoicing module in the accounting software and ditch the external template, however if this is not an option, or an accounting system isn't used, then we need the invoice template and the sales history to be linked.

Here is my approach to this problem, in the simple scenario where each invoice has one line of detail. If more lines are required a variation of this approach can be used, but this is a little too complicated to go into in this blog post.

General Approach
This approach involves three main worksheets within the one spreadsheet, Customer Data, Sales Data (what accountants often call a Sales Day Book) and the Invoice Template.

Customer Data
This worksheet, as the name suggests, is where all of the customer data is held. The sheet should follow the rules for laying out data that I outlined in an earlier post (How to lay out data in Excel). It should have column headings for name, address 1, address 2, etc. and one row for each customer, new customers are just added to the bottom of the list as needed.

The first column should be a unique identifier for that customer, it may be possible to use the name for this but you would need to add a new customer on a change of name rather than editing the existing one. Often better is to use the approach used by most accounting systems and allocate each customer a unique Account Code. I often use Conditional Formatting on this column to identify any duplicates.

The spreadsheet will use this sheet to provide the customer address details for the invoice.

Sales Data (or Sales Day Book)
This sheet should again follow the rules mentioned above. Here the business will record all sales, one line per sale. Headings should include (at least) Invoice No (in the first column), Date, Amount, Customer Account Code (the unique identifier mentioned in Customer Data above) and Description. Depending on needs the amount may be a calculated column, multiplying entries in columns for price and quantity. The business may also need a column for VAT or any other sales tax. The Customer Account Code column should use Data Validation to restrict entries to those codes in the first column of the Customer Data sheet.

The VLOOKUP function can then be used to pull in the customer name and address details in columns to the right of the entered data, using the customer account code to look that information up from the Customer sheet.

Invoice Template
If the business is already using an Excel template then this can be used as the basis of this sheet, otherwise some work will need to be done to produce a satisfactory layout for the invoice.

Ultimately there should only be one editable cell on these sheet - the Invoice Number which should be a dropdown based on the Invoice Number column of the Sales Data sheet.

VLOOKUP is then used on the rest of the invoice to pull the Customer Name and Address, Date, Description, Amount, etc. into the appropriate cells from the Sales Data sheet, based upon the Invoice Number selected.

I would recommend that to email the invoice to a customer, that it is printed to pdf first. This not only gets round the problem that all of your other sales data is in the same spreadsheet, but looks far more professional than sending a spreadsheet to the customer.

Reporting
As the sales data is all correctly laid out and, depending on the size of the business, at least a year's worth of sales can be stored in the one spreadsheet (usually many more) then Pivot Tables can be used to report on these sales in any way that is required.

Just don't forget to make sure you take regular backups and there is no need to record your sales anywhere else.

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.

Adding categories to data in Excel - how to save you time later

2012-03-02T17:49:00.000Z

In my previous post, I stated that one of the major problems with how most Excel users lay out their data, is using a column for each category.

In the feedback I have had from that post, it was felt that this point needed further explanation and/or an example, so I thought I would provide both here.

First of all, here is the point as it appeared in the original post (it was point number 3):

Don't group data by putting it in different columns (THIS IS THE ONE THAT ALMOST EVERYONE GETS WRONG)

Don't split out financial or numerical data into separate columns to categorise the data into months, expense categories, customers, agents, etc.
Do have one column for the financial or numerical data and create a column for month, expense category, customer or agent, to categorise each row;
You can use data validation drop-down lists to select the appropriate category for each row;
This one is counter-intuitive because in any report, you will almost certainly will want a column (or row) for each of these categories - but if you do this in the data you will massively restrict what you can do with it.

Let's look at some sales data laid out the wrong way:

I have left out any extra data (other than the date) to keep it simple. With the data laid out like this, you could use the SUM function to calculate monthly totals, but you can't do a lot more than that. If you were to use the data in a pivot table, you would have to add the data as 12 data fields, making it very cumbersome and inflexible.

Also, if you wanted to do any calculations on this data, such as calculating VAT, or any other Sales Tax for that matter, you would need another 12 calculated columns!

You then get into further problems if you want to analyse the data from another perspective - by salesperson for example.

A better approach

Now let's see a better way to lay this data out:

You can also automate the month column using the following formula in cell C2:

=CHOOSE(MONTH($A2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Laid out like this you can use the month as a way of analysing the amount in a pivot table for example:

Its real power however comes when you wish to add additional analysis and calculations, so for example you could add additional analysis columns for Salesperson and Country, and a VAT column (being 20% of amount):

You can then produce all manner of pivot tables, here are just a few examples:

It would be just as easy to show the months as columns - the only reason I haven't is to make best use of the space.

And remember, you can at any stage easily add further analysis or calculation columns as your reporting needs change.

I hope this has explained this point in more detail and even more importantly, highlighted the value of getting it right!

99% of Excel users get this wrong - How do you lay out your data?

2012-02-23T16:52:00.002Z

"Learn the fundamentals of the game and stick to them. Band-Aid remedies never last."

Jack Nicklaus (Champion US golfer)

When someone comes to me with a problem in an existing spreadsheet, the problem is invariably in the layout of the data. The spreadsheet is built for one purpose and works OK for that until something slightly different is required and it proves almost impossible to get the report that's needed.

If a few simple rules are followed when laying out your data, then producing additional reports from that data, and using it for different purposes, becomes simple, instead of the nightmare it is for many users.

These rules apply to any lists of data, be it monthly financial information, transactional data (such as lists of sales, purchases, payments or receipts), customer or supplier lists. If you are going to store data in your spreadsheet to produce reports from, you need to follow these rules.

At the heart of these rules is the approach - you are not laying out your final report here, you are laying out the data in a format that can be reported from! These are two very different things (see my OAP approach to reporting in Excel).

The rules to follow:

Columns with headings and no gaps

Every column should have its own UNIQUE heading, in the first row;
There should be no empty columns;
These columns represent the fields of a database, e.g. Customer Code, Customer Name, Telephone Number, Email Address, etc.

One row per record and no gaps

Every record should have all of its data on one row. E.g. in the above example, one row per customer;
There should be no empty rows;

Don't group data by putting it in different columns (THIS IS THE ONE THAT ALMOST EVERYONE GETS WRONG)

Don't split out financial or numerical data into separate columns to categorise the data into months, expense categories, customers, agents, etc.
Do have one column for the financial or numerical data and create a column for month, expense category, customer or agent, to categorise each row;
You can use data validation drop-down lists to select the appropriate category for each row;
This one is counter-intuitive because in any report, you will almost certainly will want a column (or row) for each of these categories - but if you do this in the data you will massively restrict what you can do with it.

The benefits:

Data following the rules above is perfectly prepared to be analysed using countless tools within Excel, for example: pivot tables, autofilter, SUMIF, COUNTIF, etc.
Most changes to the data don't require a change to the data layout. New categories, e.g. expense categories, customers, agents, etc. can just be added to the drop-down lists. Any new entries in these columns will be automatically picked up by pivot-tables, autofilter, etc. with no work involved.If you had to create a new column each time, you would also need to edit every report that used the data.
You can choose to analyse the data by any category you want. It takes seconds to edit a pivot table that has a column for each month and change it to a column for each expense category. This is almost impossible if the data was laid out in those columns.
You can add additional category columns to the data if needed and these can even be calculated from the data. You might, for example, introduce departments - simply add a department column to the raw data, and your pivot tables can analyse the data by this category as well, or instead of existing categories.

As you can see, if you lay out your data according to these rules, you can do pretty much anything you want with it. The spreadsheet can grow with your business, and with any additional reporting requirements you want to add.

It can take a little bit of time to get your head around point 3, but believe me, you'll be pleased you decided to be among the 1% that get this right.

If you'd prefer me to redesign your spreadsheet for you, just visit www.needaspreadsheet.com and let me know what you need and I will send you a fixed price quote.

Connecting with those we're trying to help

2012-02-13T18:52:00.000Z

"The more you explain it, the more I don't understand it."

Mark Twain

Constantly, as accountants, we need to communicate important messages to non-accountants and, in doing so, we risk coming across as detached number-crunchers, resulting in our message not really connecting.

No matter what our technical ability, if we do not have this power to connect, we don't get the chance to add the value we know we can!

I experienced this last week at the breakfast networking group that I attend every Thursday morning.

I have been going for around 3 months, with a 60 second talk every week to explain what I do (develop spreadsheets to help businesses streamline their admin). I had so far had a few pieces of business come from it, but not a great deal.

This week, I had a 10-minute slot and chose, rather than wax lyrical about what I do, to demonstrate what I had done the previous week for one of the other members. This was a relatively simple spreadsheet to record customer and sales information, with various reports from the data.

I demonstrated how the business owner could now record each piece of information once and use that same information to (at the click of a mouse) produce his invoices and sales reports, and track the success of his marketing efforts, as well as manage his callbacks.

I could almost hear the pennies dropping around the room.

I picked up three new opportunities straight away, and pretty much everyone (including a visitor who had never been before) said how every business they know would benefit from what I had shown.

The lesson I took from this is that we have to put ourselves in the other person's shoes and (and I think this is the key) demonstrate what the result means to them. Without them seeing this, anything we say will fall on deaf ears.

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 fiddling with Excel a good or a bad use of time?

2012-01-27T10:56:00.000Z

I'd really like to know what everyone thinks about this question - because I am not sure myself.

I have always fiddled with Excel until I got it to do what I wanted. For me, personally, it has worked out very well as I turned the skills I developed as a result into a successful business! But was it good for my employers at the time?

Granted, they got some good spreadsheet solutions in the end, but would it have been more effective to bring someone in who already knew how to do it, rather than use my time to get there by trial and error!

I'm sure I'm not alone as someone who likes to make sure they find a way, but it can be all too easy to spend far more time than could be justified in financial terms. Once the problem has been solved, the skills are there for next time, but is it the most efficient approach?

There are a few alternatives to fiddling with it until you get there using Google and Excel's help facility, they all have a financial cost but can considerably reduce the time spent:

Excel training - this can obviously be useful but is often too generic to then apply to your real problems when you get back to the office. I have found a one-to-one approach is often more effective, working with the client's own spreadsheets and problems. Another approach is to have training tailored to your business or industry (the service I offer to Accountants in Practice at Excellent Accountancy works along these lines)
Subscribe to a service where you have someone to ask - my Excel Advice by Email subscribers get this kind of service by email for just £75 per year
Get someone else to do it - I have my own service for this at needaspreadsheet.com

My suspicion is that any one of these could be right, depending on the relative value of your time vs your business cash, and whether you ultimately want the skills in-house.

If you have plenty of time and no cash (especially if you want to develop the skills yourself), then keeping fiddling is probably the best route (it worked for me!). At the other end of the scale, your time is usually more valuable than the cost of getting the job done outside, and this for many is a no-brainer if the primary purpose is not to build your own Excel skillset.

I'd love to hear what you do now, and what you think is best as they may not be the same!

It ain't what you do, it's the way that you do it!

2012-01-17T17:47:00.001Z

One of the most common misconceptions I come across when helping others to get the most out of Excel is the belief that it is all about learning new functions and capabilities. This misconception is compounded as most Excel training will teach you new functions and capabilities! My Pivot Table Training videos are no exception to this!

Where learning new functions is most certainly useful, it is rarely what holds people back. We can generally learn new functions from a quick Google search or just by using the fx button.

What really transforms what you can achieve using Excel is your approach. If you get the thinking right, you can always find the functions you need to achieve what you want.

My OAP Approach to Excel
One useful method I adopt to help clients change their approach is to teach my 'OAP method'.

This helps to separate the different tasks a spreadsheet needs to do, so that it does them all well. Trying to address all of these steps together is where most people come a cropper!

O is for Obtain
The O in my OAP approach is for Obtaining Data. This is key to getting your spreadsheet right and will make everything else easier.

Whether the data to be used is to be entered directly into the spreadsheet, or imported from another database or system, there are two factors to take into account:

Does the layout make it easy to input the information?
Is it laid out in a way that makes the other steps easier?

What should be completely ignored at this stage, is the layout of the final output! This is important, and is the most common reason people get bogged down with cumbersome and inflexible spreadsheets.

Any data to be entered should be in one place, using tools such as drop-down lists to make input as easy as possible.

Where there are multiple transactions or records, these should be held in a list with one row per transaction or record, with column headings and no blank column headings. Formatting is very much secondary here.

For example, a list of invoices should have columns for Date, Invoice Number, Amount, etc.

Where multiple lists of transactions or records are to be used, these should ideally have their own sheets, with no other information on them.

A is for Analyse

For my readers abroad, that is how we spell it in the UK!

This is where the calculations are done.

If the data has been collected in the right format (see O for Obtain above), we can add any calculations to the lists by adding extra columns alongside. Because the format is right, these calculations can just be copied down so that they are applied to every row.

This is also true for looking up additional data from the other lists in the spreadsheet. For example, we can use the VLOOKUP function to add address columns to an invoice list, by pulling the information from a customer list held on a separate sheet.

The objective in this step is to ensure that on one sheet we have columns for all of the items we will need in the final output. These will either have been populated via data entry (or import), or have been calculated or looked up.

P is for Present

Finally we start to address the final presentation, but this is now a lot easier as the all of the data we need is now accessible in a format that makes it easy to report on and use.

We can now use Pivot Tables to present the information in many different ways or functions such as VLOOKUP and SUMIF or COUNTIF to pull the data into specific cells if a pivot table does not do the job.

We can also use a combination of pivot tables and the GETPIVOTDATA function to give us the most flexibility.

If you work in a UK accountancy practice, I offer a service specifically for you that will really help you change how you use Excel at Excellent Accountancy.

For everyone else, please let me know if I can help with anything, or alternatively why not get me to do it for you at needaspreadsheet.com.

Merry Christmas - Take some time to stop and think!

2011-12-22T17:24:00.000Z

Just a quick blog post to wish everyone a very Merry Christmas and a Happy, Healthy and Prosperous New Year.

And, before you go, here is a Christmas-themed video from BMW that also, I think, illustrates quite nicely what happens if you fly around at break-neck speed without stopping to think about whether you might be able to do things in a better way:

I hope you all have a great break and get the chance to stop and think. See you in 2012.

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.

Accountants - A new way to improve your Excel use

2011-12-19T16:10:00.000Z

In my last post I mentioned a new service that I was about ready to launch that would help accountants in practice to significantly improve their use of Excel, and therefore their profitability.

Well, Excellent Accountancy is now live with an amazing offer for those who act before Christmas.

If you're a UK-based accountant in practice, you really need to look at this as it could seriously improve your profitability in the New Year.

Excellent Accountancy Christmas Offer

Sorry for the unusually 'salesy' post, but this is a new service (and offer) that you really need to know about. For those of you not in practice, or in the UK, normal service will be resumed in my next 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.

Apologies and Missed Opportunities

2011-12-09T18:20:00.001Z

First of all an apology for such a long delay without a post. I have been working on a new service for accountancy practices to help them get the best out of Excel which has been taking up a big chunk of my time.

I can't say a lot about it at the moment, but for those of you who run accountancy practices in the UK, keep your eye out for the launch in the next few days as there is an introductory offer you won't want to miss.

Speaking of missed opportunities, here is a video I thought you might like. It charts the early stages of the internet through the eyes of a man who missed the opportunity to be at the heart of it:

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.

Spreadsheet help and assistance

2011-08-09T15:16:00.000+01:00

I know many of you read this blog for Excel tips and will usually build your spreadsheets yourself, but I realised recently that you might not be fully aware of the other ways I can help with your spreadsheet requirements.

I have therefore added an additional page to the blog, see the new tab above - Need some help with your spreadsheet?

Whether you are after ongoing email advice when you're stuck or someone to build your spreadsheet for you, I can help.

If you have more involved requirements, such as automating your complete management accounts pack from your accounting system, I can come and work on site with you to achieve this.

Just drop me an email (glen@feechan.co.uk) if there is anything you need help with.

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.

EXCEL TIP: Eliminating #DIV/0! and other errors automatically using ISERROR

2011-07-27T14:03:00.039+01:00

Excel is great for being able to apply the same calculation consistently by simply copying the formula to all of the relevant cells, however often there are times where some of the data isn't how we would like it and the formula throws up an error.

The most common of these is the #DIV/0! error that Excel returns when trying to divide a number by zero. As Excel struggles with the concept of infinity it returns this error. A common situation where this occurs is in a Gross Margin Percentage calculation. We may have multiple products with sales and profit figures and a formula to calculate the margin as a percentage, i.e. profit/sales formatted as a percentage.

This will work fine until you come to a product with no sales where the formula will return #DIV/0!.

This can be addressed using a combination of the IF statement and ISERROR (note that in Excel 2007 and 2010 there is a combined function called IFERROR, however the solution proposed here will also work in earlier versions of Excel). If you need a refresher on how the IF statement works take a look at :

EXCEL TIP: The IF Statement made simple

If cell A1 contains the Sales figure and B1, the Profit, then to show the Margin Percentage in C1, we would format it as a percentage and use the following formula:

=B1/A1

This will return the #DIV/0! if cell A1 is empty or zero.

The ISERROR function returns TRUE if its argument returns an error and FALSE if not, therefore

=ISERROR(B1/A1)

will return TRUE when this #DIV/0! would occur.

We also need to decide what we want to appear instead of the error. In this scenario, I usually show 0% which is what I have assumed for the example.

Using the ISERROR formula above as the condition for the IF statement, we can return zero if it is an error, or the original calculation otherwise. So C1 becomes:

=IF(ISERROR(B1/A1),0,B1/A1)

No more #DIV/0! errors!

A similar approach can be used for any other common errors, such as when a looked up value is not in the list.

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.

EXCEL TIP: Using the SUMIF function to interrogate lists

2011-07-18T18:05:00.000+01:00

How many times do we need to pull out a particular category of numbers from a list and add them up. Well, you might not be aware that Excel has a pretty simple function designed to do just that.

The SUMIF function can be used to pull out departmental information from a trial balance, sales information from a transaction list for a particular salesman, department or division (as in the example we will use), or for any other situation where you have a list and need to selectively sum a subset of that list based on criteria.

It is actually a pretty simple function when you know how it works, but is very powerful in the types of situations outlined above.

See below the example situation we will be using to illustrate this function:

This is a simplified situation where we have a list of sales transactions in columns A to C (maybe imported from your accounting software) and want to report total Retail and total Wholesale sales.

We will use SUMIF to return the values in cells F3 and F4.

The format of the SUMIF function is as follows:

=SUMIF(CriteriaRange,Criteria,SumRange)

where CriteriaRange and SumRange are one column wide and the same number of rows as each other in height.

CriteriaRange is used for the column that includes that data that you wish to filter your sum by (in our example, C2:17).
Criteria identifies which item in the criteria range column you want to sum (in our example, "Retail" or "Wholsale" for cells F3 and F4 respectively).
SumRange is used for the column that holds the data you actually want to add up (in our example, B2:B17). This argument is optional and if not entered CriteriaRange is used.

So for our example, cell F3 contains:

=SUMIF($C$2:$C$17,E3,$B$2:$B$17)

Notice I have used the dollar signs to fix the ranges and used the reference to cell E3 rather than the word "Retail". This can then be copied down to cell F4 to become:

=SUMIF($C$2:$C$17,E4,$B$2:$B$17)

The criteria does not need to be a simple matching as in our example. A condition such as ">0" could be used to sum only positive values (this is an example where you might not enter the separate SumRange as the criteria is applied to the data being summed.

With our example data, the formula:

=SUMIF($B$2:$B$17,">1000")

returns £1,431.

Have a play with it and see what you can use it for.

SUMIF is great if you know the exact format of the output that you want and you require a fixed layout. If you want to summarise all of the data in your list dynamically, a pivot table might be more what you need.

If you want this kind of help on hand when you need it, have a look at my Excel Advice by Email service which provides email advice when you get stuck for a small subscription.

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.

Need a Spreadsheet?

2011-07-06T16:46:00.000+01:00

Many readers have used my Spreadsheets by Email service when they have wanted a spreadsheet built to their requirements, so I thought I had better ensure that readers were aware of some significant changes to the presentation of the service (no changes to the quality of the work, or who carries it out).

As the service is very useful both to those who know Excel well and need a little help (I suspect this is the case for most readers of this blog), and people who have never opened a spreadsheet in their lives - I was concerned that the website might have intimidated some of this second group if it came across too "geeky".

So, I've gone for a complete change of name and presentation, which I think will appear more accessible to all. And now you know where I've disappeared to for the last few weeks.

Welcome to needaspreadsheet.com.

Please take a look and let me know what you think, and if you have a spreadsheet you need building, please fill in the form on the site.

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.

A business plan from my 12 year old son!

2011-06-03T20:58:00.000+01:00

I received the following email from my 12 year old son this morning. I was at work, and he was home, obviously putting his half-term holiday to good use!

Subject: I know how to make over £30.00 at school in 6 weeks

"Everybody is interested in tech decks at school and they all say the cheapest place to get them is crazy clearance. They are apparently £3.00 each at crazy clearance. I have found a pack of 10 tech decks for £14.43 which is about £1.44 each. If I sold 10 per week at £2.00 I would earn £20.00 and make a profit of £5.57 per week. If I could do that for 6 weeks I would have made a profit of £33.42 towards my TV. Is this a good idea. Also do you think offering a free lollipop from the shop would be a good idea if I couldn't sell any in my first week because I would still make a profit on each tech deck. In east lee stores the lollipops are 20p and were popular at the beginning of the year, being sold at around 30p. If I charged £2.00 for a tech deck and a free lolli It would cost me £1.64 per tech deck and I would make a profit of 36p per sale. I would not do this straight away however because without the the lolli I would make a profit of 56p per sale. That is a 28% profit margin which is quite decent. Please tell me if this is a good idea. The only thing is allot of people are selling used tech decks for £1.50 but with mine they would be paying for the quality. I think it is decent because it means more than doubling my starting budget which would have to be £15.00. Tell me if this is a good idea and if so could you please build me a spreadsheet when you have the time just to keep you practicing your spreadsheet skills. And don't charge me for the spreadsheet because I can do it myself."

Putting aside the typos and lack of paragraphs (and the cheeky jibe about Spreadsheets by Email at the bottom), I realised I had come across so many business plans from grown-ups that weren't as considered.

He has sourced a supplier, researched the market (even establishing that he can still cover his costs at the current rate for second hand product) and knows his margins. He has even given thought to a promotion!

Part of me feels incredibly proud, and part of me worries I've created a monster!

By the way, I had to ask what a Tech Deck was, and this was his reply:

"It is a mini skateboard that you can use your fingers to ride and do tricks with. You can also get mini skate park equipment to use it on. Tech deck is the brand name though. you can get cheaper ones but they break alot easier."

What's more, this second email had a link to his blog in the signature!

It sounds like he'll do his bit for all of our pensions!

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.

Working over the weekend?

2011-05-27T18:45:00.000+01:00

We're about to finish for the Whitsun bank holiday weekend here in the UK, and I'm curious how many of you are going to be working over the weekend. Or what would be more interesting is how many of you will actually get a weekend completely free of work (for those of you not in the UK, will you get Saturday and Sunday?). That means not checking work emails - completely switching off from work.

I am aiming to spend some time with the family, maybe get out for a long walk in the middle of nowhere, but I would struggle to not check emails on my phone when I have a signal, and have some work I definitely need to fit in over the weekend.

Please let me know what you are up to in the comments. For those of you who are not even checking emails, maybe you can answer on Tuesday!

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.

EXCEL TIP: The dollar sign ($) in a formula - Fixing cell references

2011-05-20T13:07:00.002+01:00

I want to cover something today that I use all of the time but seems to be understood in varying degrees by clients I work with.

I am talking about use of the dollar sign ($) in an Excel formula.

Relative cell references
When you copy and paste an Excel formula from one cell to another, the cell references change, relative to the new position:

EXAMPLE:

If we have the very simple formula "=A1" in cell B1 it will change as follows when copied and pasted:

Pasted to B2, it becomes "=A2"

Pasted to C2, it becomes "=B2"

Pasted to A2, it returns an error!

In each case it is changing the reference to refer to the cell one to the left on the same row as the cell that the formula is in, i.e. the same relative position that A1 was to the original formula.

The reason an error is returned when it is pasted into column A, is because there are no columns to the left of column A.

This behaviour is very useful and is what allows a sum to be copied across or down the page and automatically refer to the new column or row that it finds itself in.

But in some situations, you want some or all of the references to remain fixed when they are copied elsewhere.

The dollar sign ($)
This is where the dollar sign is used.

EXAMPLE:

Take an example where you have a column of Sales values in Pounds Sterling in column A and a formula to convert these into US Dollars in column B. You could enter the actual exchange rate into the formula but it would be more sensible to refer to a cell where the exchange rate is held, so that it can be updated whenever it is needed.

The simple formula for cell B2, would be "=A2*E1", however if you copy this down, then the formula in cell B3, would read "=A3*E2" as both references would move down a row as described above.

This is where the dollar sign ($) is used. The dollar sign allows you to fix either the row, the column or both on any cell reference, by preceding the column or row with the dollar sign. In our example if we replace the formula in cell B2 with "=A2*$E$1", then both the "E" and the "1" will remain fixed when the formula is copied. i.e. in cell B3, the formula will read "=A3*$E$1", still referring to the cell with the exchange rate in it.

In this example we have fixed both the row and the column, but in other situations, you may just want to fix one or the other, for example:

Above we have a spreadsheet calculating the times tables where we want to every cell in the white area to be the product of its row and column heading. This is easy using the dollar symbol. In cell B2, the formula without dollars would be "=A2*B1", but for this formula to work when copied to each column, we need it to always look at column A for the first reference and to work for each row, we need to always look at row 1 for the second. Using the dollar sign to do this, it becomes "=$A2*B$1". This can then be copied to every cell in the white area.

Quick Tip
You can speed up entering the dollar signs by using the function key F4 when editing the formula, if the cursor is on a cell reference in the formula, repeatedly hitting the F4 key, toggles between no dollar signs, both dollar signs, just the row and just the column.

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.

A New Era for Not Just Numbers

2011-05-20T10:34:00.000+01:00

Sunrise over Seaham

Hello again to regular readers who might be wondering where I've been. No blog post for over two months! Well, I've been busy reviewing how all the various parts of my business fit together - including this blog.

Not Just Numbers was originally intended to generate traffic for my other businesses, however it is difficult to justify the effort required purely on this basis - hence the deliberations over the last couple of months.

I have been wrestling with this because I enjoy doing the blog and would hate to give it up, and there are many intangible benefits which are difficult to measure for both the business (additional credibility for clients brought in through other routes for example) and for me personally (communicating with many interesting readers that I would not have otherwise met).

Well, the deliberation is over and I have decided not only to continue, but step up the blog activity, while looking for additional ways in which the blog can contribute financially (I still have a business to run!). The most immediate change you will see is the donation button in the right hand column, but I will also be looking for additional advertising where appropriate.

So, onwards and upwards.

PS: Not Just Numbers posts are like buses, none for two months then you get two in one day - there will be another post later today about using the dollar sign in Excel formula to fix cell references.

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.

Automating regular procedures (e.g. month-ends) - the real benefits

2011-03-11T15:11:00.002Z

Ah, month-ends, those joyous occasions where we get to reflect on the successes of the last month's results and toast the even better ones to come....

What! Not your experience of month-ends?

How about a desperate panic of late nights trawling through those familiar journals and spreadsheets to hopefully pull together a set of management accounts that makes some kind of sense?

Does that sound more like it?

When we finish, we pause briefly, dribble a bit, think we must be able to automate some of this, and then start gearing up for the next one.

If this is you, you might want to consider what the real costs are of not finding the time (or money) to automate some of this.

What are the costs to the business?

Missed business opportunities - A finance department focused on producing the numbers instead of interpreting them can cost a business dear. If the finance department spent the time lost to month-end procedures on reviewing the month-end numbers and understanding their implications, the business could benefit greatly;
Missed business problems - For the same reasons as above, this pre-occupation with getting the numbers out can lead to future problems being missed until it is too late;
Increased staff turnover - The extra workload and pressures (and late nights) can mean it is hard to hang on to good employees in the finance department;
Less accurate accounting - Accounts produced in this environment rarely have time to be properly reviewed, allowing errors to slip through the net.

But what about the cost to you?

Stress - It goes without saying that this working environment is not conducive to low stress levels;
Lack of career progression - Spending all of this time working on what has to be done, stops you working on those areas where you could add real value to the business, and demonstrate your worth. The pay rises come a lot easier when the boss sees you as generating profit rather than as a necessary cost;
Time - The biggest cost of this type of approach is time. A chunk of that time put into repetitive month-end procedures could be directed into adding value to the business (as discussed above), however there should also be a significant chunk available for you, particularly if the job currently involves a lot of work outside of office hours. You could start going home at 5pm and not coming in at the weekend. Just think what you could do with that time:

Spend more time with the family;
Play more golf;
Take up a hobby;
Learn to fly;
Become an internet millionaire (I keep reading emails saying I can do this in 4 hours a week!).

I hope this has made some of you spend a little more time this month on the "we must be able to automate some of this" part of your month-end.

If you want to discuss how you might go about it, drop me an email (glen@feechan.co.uk).

Here's to making month-ends fun!

How long have you been using Excel?

2011-01-26T17:20:00.001Z

I came across this short video celebrating the first 25 years of Microsoft Excel and it got me to reminiscing about my early days with spreadsheets.

I didn't start using Excel myself until 1998 when the company I was working for went through a management buyout and I decided to do the forecasts for the post-buyout company in Excel rather than Lotus-123, and bring the rest of the accounts department across afterwards. This put ridiculous pressure on me to convert the management accounts into Excel immediately after we completed the buyout. This is when I came up with the basis of the approach I outlined in my first ever post on this blog, back in September 2008 (read it here).

I first used Lotus-123 during my 3 years training in an accountancy practice (1992-1995) on an ancient 'portable computer' that looked like a sewing machine and was shared by all of the accountants in the practice. I remember it had orange text on a black background and we only really used it for Fixed Asset Registers!

Would love to hear any stories of early Excel (or Lotus-123) use from any of you. Please share in the comments below.

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.

Just for fun - How I created my Excel Christmas Quiz

2011-01-18T12:29:00.001Z

Before Christmas you may have seen my Excel Christmas Quiz which you can still download here (although the questions might no longer be appropriate for the season, the prize is still valuable until the end of January).

I thought it might be useful in this post to tell you how it was made.

This post will make more sense if you download the spreadsheet first.

The Quiz

The quiz element is pretty straight forward. There are 3 hidden columns alongside the spaces for the answers. One column contains the correct answers (column T), another column (column S) contains a nested IF statement as follows:

(for the first question - row 5) =IF(R5="","",IF(R5=T5,"Ho Ho Ho","Grinch!"))

which returns a blank cell if no answer is entered, returns "Ho Ho Ho" if the answer entered is equal to the correct answer and "Grinch" if not. Cheesy I know, but it was Christmas.

The third column (column H) contains another IF statement:

(again for the same row) =IF(R5=T5,1,0)

which returns a 1 if the answer is correct or a zero if not.

This third column is summed for the score as follows:

=SUM($H$5:$H$14)&"/10"

This takes the sum of these 1s and 0s and adds the text "/10" after it. See my post on manipulating text in Excel for more detail on this.

The revealing of the Christmas picture

This was done using the background facility in Excel and a series of IF statements combined with offset formulae and conditional formatting.

I first set the background image to be the Christmas picture using Format -> Sheet -> Background. As this tiles the picture in the background and gives no facility to re-size I needed to use some trial and error to get the size I wanted and had to have the image in the top-left to ensure it stayed in the right place.

I then needed to choose a background colour for the rest of the sheet so that the picture was not repeated across the whole sheet - for this I simply formatted the cells with that fill colour (in my case green). It is easier to apply this for the whole sheet and then remove the colour from the cells that hide the picture.

I then created on a separate sheet (hidden) a grid of the same number of cells as the picture, containing random whole numbers between 0 and 9 in each of the cells, representing the 10 questions. This will be used to determine which cells are revealed for each question. I actually used the random number generator to create this grid using the following formula:

=ROUNDDOWN(RAND()*10,0)

RAND() returns a random number between 0 and 1, multiplying this by 10 therefore returns a random number between 0 and 10 (but not necessarily a whole number). The ROUNDDOWN formula rounds down to the number of decimal places specified in the second argument (in this case 0). The result of the whole formula, therefore, is a random whole number between 0 and 9.

Because the RAND formula recalculates on each calculation the spreadsheet does, I needed to fix these numbers as I wanted the cells revealed by a question to stay revealed, not be re-scattered across different cells every time an answer is entered. The simplest way to do this was to copy the grid of numbers and re-paste it over the top of itself using 'paste as values'.

Going back to the main sheet I entered the following formula in the top-left cell of the picture (A1):

=IF(SUM($H$5:$H$14)=10,"",IF(OFFSET($H$5,Sheet4!A1,0)=1,"",1))

The objective of this formula is to return an empty cell if the corresponding question is correct and a 1 if not. I'll explain shortly what we do with this result.

The first IF statement returns an empty cell if the sum of the scores is 10, so all cells are empty if all answers are right. If the total score does not equal 10, the second IF statement checks whether the score of the relevant question is correct (i.e. 1 in column H) and an empty cell if not. The offset function is used to determine which question to look at. $H$5 is the first score (with dollars to fix this reference) but the row number is offset by the number in the corresponding cell in our number grid (on Sheet4). The zero is the number of columns to offset. For example, if the number in the grid is zero, the offset formula will look at cell H5, i.e. the score of question 1, if it is 9 it will look at cell H14 (5+9) and return the score for question 10.

This formula is copied to all cells on the picture, leaving the dollars off the Sheet4 reference so that in cell A2, it looks at cell A2 on Sheet4, etc.

We now have empty cells corresponding to correct questions, which is right as we want an empty cell with no fill as the picture is revealed. For incorrect, or not yet completed, answers we have a 1 in the cell. We use conditional formatting to use this result to hide these cells.

First of all, set the font colour of these cells to the background colour of the rest of the sheet (green in this case). We now have a green "1" in front of the picture for incorrect answers.

We use conditional formatting to choose a green background for these cells if they contain the number 1.

In Excel 2003, select Format -> Conditional Formatting, in Excel 2007/2010 you need to add a new rule. In either case select < Cell value is > < equal to > < 1 > for the three boxes and click the format button to choose the green fill.

Now the picture has a green background until correct answers are entered to gradually reveal the picture.

Revealing the prize

The final step is a much simpler version of revealing the picture, that is only dependent on the total score.

The background of the prize box is set to yellow using the normal cell format and the font colour to whatever you want it to be when it is revealed.

We then use conditional formatting again but enter the following for all of the cells in the prize area:

< Formula is > < =$R$16<>"10/10" >

setting the format to a yellow font colour. This switches the font colour to yellow if the score in cell R16 does not equal 10/10, making the text invisible on the yellow background.

Protecting it all

Finally, we need to protect the sheet so that none of this information can be seen or edited.

Cells are formatted as "Locked" by default, so we need to select the cells that the user needs to be able to use (just the 10 answer boxes), select Format->Cells and on the Protection tab, un-tick the "Locked" box.

We then protect the sheet as follows:

Select Tools->Protection->Protect Sheet

Enter a password so that users cannot switch off the protection and un-tick all boxes in the list except the one that says "Select unlocked cells". Once you have clicked OK, the user cannot even click in any of the cells apart from the 10 answer boxes.

There you have it! Your very own Excel Quiz.

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.

JK Rowling on the benefits of failure

2011-01-11T16:40:00.000Z

Happy New Year to everyone.

Sorry for being a bad blogger! It's 11th January and I am just getting round to my first post of the year. Even then I'm still catching up so it is a short one just to show you a video I came across today. I certainly found it both entertaining and inspiring - some of you may have seen it before.

In the current economic climate, I am sure that many of us might feel that we are failing in some way. In this video of her Harvard Commencement address from 2008, Harry Potter author, JK Rowling talks about how important failure was to her success.

I hope you enjoy.

J.K. Rowling Speaks at Harvard Commencement from Harvard Magazine on Vimeo.

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.

Excel Christmas Quiz 2010

2010-12-23T15:36:00.001Z

A Merry Christmas to all of our readers.

Last year I created a Christmas Quiz in Excel just for fun (last year’s quiz), but it proved very popular so I thought I should do it again this year.

This time though I thought it might be nice to add a little Christmas present if you get them all right. I won’t tell you what it is and spoil the surprise, but you will find it very useful if you need a spreadsheet. Even if you don’t, I hope you have a bit of fun with the quiz.

Maybe you could even do it without Google!

Download your copy of the Christmas Quiz

Good Luck and have a great Christmas break and a fantastic 2011!

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.

Lessons I didn't learn until I ran my own business - Lesson 2

2010-11-30T17:44:00.000Z

Welcome to the second lesson in my occasional series, 5 Lessons I didn't learn until I ran my own business.

Lesson 2: Flexibility is far more important than you think

In the early days of my business, I wanted to make sure I had all of the building blocks in place for the business I wanted in the future. In many cases I made long term commitments to keep the costs down so that I would have a greater share of the income that would eventually come in. This is really faulty logic!

When sales weren't as anticipated, I still had these monthly overheads to cover, whether or not there were any sales - each month leaving me less and less room for manoeuvre. Instead of being able to focus on growing the business, you find yourself focussed on short term sales to feed the overheads and survive until next month.

If I was starting out now, I would focus on trying to make as many costs as possible variable rather than fixed (even if this means that the rates you actually pay are higher). Once you have a proven regular level of sales, you can revisit these and maybe make some longer term commitment to keep the cost down. But if you do, make sure this works for the minimum level of sales you can expect, keeping the flexibility for sales above this.

Ways to stay flexible:

Employees - Where possible use temporary staff, paid by the hour, or outsource to experts by the day as needed. Employ sales people on a commission-only basis or at least with a high commission element to their package.

Stocks - Look at sale or return, or consignment stock arrangements so that you only have to pay for stock you sell. Operate a Just-in-Time approach with suppliers to minimise stock holdings.

Premises - Look for easy-in, easy-out options on offices or workshop space, ideally with a one to three month notice period, so you can flex for the space you need, rather than be tied to the space you've committed to.

Capital purchases - Do you really need that capital item? If you do need it, could it be bought second-hand? Can you pay for it out of cash and avoid a monthly lease commitment? If this is not feasible, try to ensure that there is an exit route, e.g. that the outstanding finance will remain below the resale value, allowing you to get rid of it by stopping the payments.

Vehicles - Although these should be covered by Capital purchases above, I think they deserve a mention on their own. The question, "Do you really need it?" should be asked at least ten times before committing to a vehicle lease in the early days of your business. This is probably the easiest way to take on large monthly commitments that bring little benefit to the business. Trust me, I learned the hard way. You're only kidding yourself when you say the flash car is necessary to bring the business in. Clients/customers are unlikely to be impressed and will more likely drive a hard bargain as they think your margins can stand it.You are buying it because you want to - there is no business case. You need a reasonable car to get you around. When you are making the money, you can get the car you want.

Remember, if you have a 20% profit margin, for every £100 monthly overhead you commit to, you now need to sell an extra £500 to break even! And remember break-even just means earning nothing!

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.

Off-the-shelf or bespoke software - How to decide

2010-11-18T13:30:00.001Z

I have been approached by a client to build a project planning spreadsheet, but given the client's very general requirements, I have pointed him towards an off-the-shelf, on-line solution that will be a much more cost-effective solution for him. This got me to thinking about that choice.

My previous post on how to decide between a spreadsheet or a database made the assumption that you had already decided that you needed a custom solution, but how do you come to that conclusion.

I will take a similar approach to that previous post and list what I think are the key points to consider:

1. How unique is what I need?
Is your requirement something very specific to you or your business or is it quite generic, even if that is just in your industry? The more generic the requirement, the more likely an excellent solution already exists.

2. Do I have some very exacting requirements about how I want the need met?
In a similar vein to point 1, even if you have what appears to be a generic requirement, you may want it implemented in a particular way. We recently developed an on-line time-sheet system for a client because for their business had some very specific ways they wanted to keep time-sheets.

3. Is it possible, or practical, to change the process?
To get the best from an off-the-shelf product, it is better if you can adapt your processes to suit the way the software works. In many cases, this leads to an improvement in the process anyway - but sometimes the current process is key to the business, or a change would cause too much disruption. A bespoke solution can be tailored to the process.

4. How complex is my requirement?
This one really comes down to comparative cost and development time. For a simple requirement, a bespoke solution can often cost little more than an off-the shelf package and be implemented as quickly, but it can be tailored to your exact needs. The more complex the requirement, the more incentive there is to tap into (possibly) years of development that have already gone into an off-the-shelf package where the development costs are shared with the other users, than to pay to re-invent the wheel yourself.

5. What is my budget?
As ever, this is always going to be a factor. In most cases the off-the-shelf package will be the cheapest option, however do bear in mind any costs of tailoring your processes to fit (see point 3).

6. Ultimately, does the package I want exist?
If your answers to the other questions suggest that an off-the-shelf package would be best for you. This is only of any use if an off-the-shelf package exists. Alternatively, you may have just identified a business opportunity!

If you do feel that you need a bespoke solution (either spreadsheet or on-line database), then visit us at Spreadsheets by Email where we can provide you with a cost-effective, fixed price solution.

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.