Did you hear that one about the accountant...?

Why is it that poor old accountants are the butt of so many bad jokes? They get so much flack for doing such a thankless task. My personal favourite accountant joke....What does an accountant use for contraception? His personality.

Now, now, before I get loads of complaints, I'm an accountant too...and if we can't laugh at ourselves, 'tis a sad life. And why I am bringing out the  bad accountant jokes? Because I am about to head into serious accounting territory, so wanted to get you all loosened up first!

Ok, this is not as serious as having to re-do your tax returns, but I have been asked a specific accounting type question, so will answer it with an accounting based solution. However, please note that while I have recreated a P&L and Trial Balance, you will note that they are a "little" basic and only cover sales and cost of sales. This is because it still conveys the answer required, and doesn't take up heaps of space.

So, the question I was asked was....how do you create a rolling P&L, with each months' figures next to each other, without having to manually key in the figures from separate spreadsheets? I'll show you my solution, and there lots of ways of doing this, but this is the one I like for simplicity:

Lets assume you download a Trial Balance each month and save it somewhere. Lets also assume that your Trial Balance is governed by account numbers. So it looks something like this:

(Before all the accountants start yelling that a Trial Balance should net to zero, like I said, this is fictional and incomplete!)

You also want to have a P&L that looks something like this:

Please note the column headings - they jump from D to O because I have hidden the other columns, which contain March to December, until needed.

So, instead of having to type in each month's figures, or set up a fresh lookup formula and copy it all the way down, you can set this all up using an INDIRECT formula embedded in a VLOOKUP. I briefly touched on this in a prior blog, but I will take you through it step by step now.

First thing is in the set up. Obviously you want to set up your P&L to look just right the first time round. So do it, but only do all the formulas etc for the first month. Once you have it perfected, drag all the formulas across the rest of the columns.

Second thing is, it is better if you paste each month's trial balance into a new sheet within the workbook. This is because an INDIRECT formula will bring back an #REF! error if the workbook it is looking up to is closed.

Right, I have pasted my Trial Balances into my workbook for January and February, and my tabs look like so:

To keep this as simple as possible, I have just shown one formula (for Sales for Widget A) and some of the other more basic formulae:

Now, the step by step walk through (its not nearly as scary as it looks):

IFERROR - this formula is saying "if the results of the following formula comeback as an error, then return 0 instead". That is what the 0 at the end of each line is for. This will also make sure your YTD total doesn't show errors when you drag the formula across for future months.

VLOOKUP - this is your basic VLOOKUP formula as discussed in prior blogs (click here for a refresher). The first brings back your Debit totals, and the second brings back your Credit totals, and these are added together for a total for each account number. This example is based on account numbers, but you can use anything as long as each line is unique. So descriptions are fine too, just make sure the spelling is identical in both sheets.

INDIRECT - instead of  having to set up the formulas each month once you have your TB file, you simply point your lookup to the month name in row 3, and if your sheet is named the same, it will perform the lookup in that month's sheet. So when you add in your February figures to the sheet named Feb, it will automatically lookup the numbers. Just be sure to check the TB data is always in the same columns.

Once you have this setup, it really is as simple as pasting a TB into a sheet in your workbook to update your YTD P&L - obviously unhide each month as you need it.

One word of warning - it is always a good idea to have some kind of automatic sum check to compare your P&L totals to the sum of the P&L accounts in your TB. I haven't put a solution in here as everyone's TB will look different, but just bear it in mind. Sometimes automating things too much can make you miss some crucial points (or new TB accounts!).

If you would like a friendly reminder of all new blogposts, please sign up here.

Happy Spreadsheeting!

Failing to plan is planning to fail....

Who hasn't had that old nugget thrown at them by a parent or teacher or any other busy body? And who hasn't inwardly (and maybe outwardly - depending on bravery levels) rolled their eyes heavenwards in exasperation? Why are we exasperated to hear that? Because we are normally hearing it too late. When to go back and re-plan and re-do and make it right would take just as long as fixing the problem would.

I am on the fence on planning, personally. I tend to go to extremes...either going with the flow and dealing with whatever comes my way, or following that other well-loved bit of advice - "hope for the best, plan for the worst", and getting myself into knots with too many plans.

However, I am fairly adamant on planning in Excel. Proper planning can make your spreadsheets, and work, so much more efficient. Also being able to see the woods for the trees, and selecting the right approach to a problem, helps immensely.

Take this example. My sister is in IT and was recently battling through a  merger of a couple of systems. She emailed me asking for some help on a VLOOKUP formula. Basically, they were running tests on various areas. Lets say that one test result would look like this:

She was getting each set of test results back and saving them as a new sheet into one summary workbook. Each sheet would have a name of Test1, Test 2 etc... She now wanted to create a summary that would look up all the results on one page. However, if she used a normal VLOOKUP, she would need to change each new column's formula to a different sheet name (Test1, Test 2 etc). Not the end of the world, but time consuming. So I suggested she use this instead:

As you can see, I have inserted an INDIRECT formula into the VLOOKUP. By doing this, when she drags the formula across the columns, the VLOOKUP will automatically pick up the new sheet name, which is also in the header row 2, saving her having to replace the sheet name for each column of results.

Side note on INDIRECT formulas - these are very handy little formulas, but pretty tricky to explain. Basically, an INDIRECT formula inserts the results of the cell that you reference in your INDIRECT formula. This is very useful if you are going to be changing things, like in the example above. So we reference the cell C2, to bring back 'Test1' into our VLOOKUP formula.

Anyway, back to the problem. Well, it was no longer a problem, it was solved. And I did exactly what she asked, I adjusted her VLOOKUP formula to change the sheet names.

However, with hindsight, a possibly better solution would have been to use a consolidation. It would have required more work at the set up, and I suspect she was beyond that stage, so I wasn't brave enough to throw the quote at her!

How I would have used a consolidation for this testing is as follows:
1) I would have created all the test sheets before hand.
2) Instead of Pass or Fail, I would assign a number to each status. So 100 for a Pass, 50 for a fail and 0 for not tested. This will make consolidating easier at a later stage.
3) I would then have created my consolidation summary sheet, using the Excel consolidation tool.

How?
Here goes...
1) Go to a blank sheet.
2) Go to Data - Data Tools - Consolidate
3) A pop up box will appear. I have already filled this one in, and will talk you through it below:

 

Function: - You can choose a function like Sum, Count etc. I have chosen Average for this example...you'll see why later.

Reference: - Click into this box and then go the sheet you want to include in your consolidation. It is better to include the entire column or row if you are going to be adding in more data.

Click Add and it it will add the referenced cells into the box below, which contains all the references you want consolidated.

Use labels in - I have ticked the Left Column, as this is what Excel will use to consolidate the data. This is very handy because you don't have to have all your sheets identically laid out, and you don't need to have an identical list of items. Excel just matches what it can.
It works the same for labels in the top row.

Create links to source data - this is very important as it brings back an actual formula showing all the sheets linked. It also ensures your data will be updated when the source sheets are changed.

Click OK, your pc will whirr for a second, and then hey presto, you get this:

 

These are the results of four different test sheets, averaged out. To see the full detail, click on the little 2 on the top left hand corner, and you get the works:

Now, if you did all this before you started testing, you could send out the links to the individual sheets to each tester. As they completed their own tests, you would get a running update on how your tests are looking. And you can keep on kicking butt until the averages are all 100!

So that is a quick overview on the consolidation tool. Some notes:

1) If your worksheets are all laid out identically, you don't need to use labels in the consolidation.
2) Try to get your consolidation perfect the first time. Making adjustments afterwards will probably require you to restart your consolidation.
3) Plan, plan, plan.

Don't want to miss out on the weekly tips? Why not sign up here? We'll remind you...in the nicest possible way!

Happy spreadsheeting!

Who's afraid of the big bad vlookup?

Little Red Riding Hood is lost. And it's not in a dark, scary wood...oh no, it's in the lot of a Hollywood Studio. You see, what with all the rehashing of classic fairytales at the moment, the casting directors are getting confused and sending the actors to the wrong studios. What they need is a good vlookup formula to get themselves on track (yes, seriously)......

So imagine these were there current projects:

What this table shows, besides the fact that handsome princes are in demand and fairies and stepmothers have got a bad name, is that there is a fair amount of opportunity to get confused with all the various details.

If each casting director were sent a lovely little lookup tool as shown below, they could select their fairytale from the dropdown list, and the answers would automatically flow into place, along with the correct studio number.

Now before you fall into a 100 year sleep, let me explain VLOOKUPs as simply as I can. The official syntax of a vlookup is:

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup).
Yup. Hardly surprising it sends most people running for their fairy godmothers. Instead of fleeing, if you are using a VLOOKUP formula, try saying this in your head as you type it out. It may help you make sense of the formula:

=VLOOKUP(look for this, in here, bring back stuff from that column, false). So much easier than the official set of syntax, no?

Here is what the formula means, in plain english:

lookup_value - this is the cell reference of the item you want to search for in the data table. So for our example above, the lookup_value is the Fairytale name.

table_array - this is the table of data that you are using to search for all your answers.
NOTE: for vlookups to work, the first column in the table of data needs to be the column containing your lookup_values. In our example, that means the 'Fairytale' column must come first.

col_index_number - this will depend on what data you want to bring back. In our example, to bring back the Leading Lady, you insert a 2, as this info is contained in the 2nd column of data if you start counting columns (left to right) from the column that contains your lookup_values.  For Good Guys, use 3, for Bad Guys, use 4 etc.

range_lookup - This is always either TRUE, FALSE or you can leave it out, which is the same as typing in TRUE. To get an exact match on your lookup_value, type in FALSE. If it can't find an exact replica of the item you are searching for, you will get an #N/A. If you type in TRUE, it will look for the nearest match and bring back that data. This has its uses (see below) but in our example, you would always use FALSE. In fact, whenever you want 'exact match or nothing', use FALSE.

Some points on VLOOKUPS:
1) If you have duplicates in your first column, the vlookup will always bring back the data from the first instance. So check for duplicates if you're not getting the answer you expect.
2) If you are going to be copying the formula into a number of cells, make sure your table data is 'locked' into the formula. So select your table co-ordinates and press F4 to get the little $ signs.
3) Spaces matter in vlookups. If you download or import data from a reporting system, it is quite likely there may be a space at the end of your data. This will prevent your vlookup from working. Delete the space, et voila.
4) If you are not quite comfortable typing in the formula freehand, use the formula function box to help you the first couple of times. After that, you will probably find it much easier just to type it yourself.

So, back to the issue of TRUE or FALSE. Using TRUE is really handy if you want to look up a number that falls within a range of numbers. So if you were grading papers and wanted to give students an A-F grading based on their % marks, this is a handy tool to use.

Similarly, if we wanted to give our characters a 'goodness' rating, we could do the following (I've added the formula in at the bottom again for explanation):

This formula works by first checking for an exact match. If it can't find it, it will look for the next LOWEST number. So your table always needs to be in ascending order. In our example, Mother Gothel has not yet reformed her ways enough to even be classified as Misguided (40%), so she goes straight into the Evil & Twisted basket.

So, that is your basic VLOOKUP. These formulas are normally used for extracting data from one table into another, but can be used for just about anything. I've kept it simple for ease of explanation, but you can add in all sorts of other functions to make these formulas truly perform magic.

In our newsletter this week, I will be explaining how an HLOOKUP works...why not subscribe?

Happy Spreadsheeting!


--