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

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!

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.

How long have you been using Excel?

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

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

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.