From the Not Just Numbers blog:

Well, this is my last post before Christmas, as I am taking some time off with the family over the Christmas break, so my next post will be in the New Year.

I thought I’d sign off with a simple, but seasonal post.

But, in case you don’t read on, I would first of all like to wish all of my readers a very Merry Christmas and I look forward to working with many of you in the New Year.

If you want to add a bit of seasonal spirit to your spreadsheets, why not add a Christmas background?

You can easily add something like the Christmas Tree picture on this post as a background to any worksheet.

All you need to do is go to the Page Layout ribbon and click “Background”. You can then browse to image you want to use and click OK. That worksheet will then use that picture as it’s background, visible in cells that have “No Color” as their background colour. Note that a fill colour of White is therefore different to “No Color”. A cell with a white background will hide the picture.

The picture will “tile”, i.e. repeat itself indefinitely, and any cell contents will appear on top of it.

When the Christmas period is over and you want to go back to your boring old spreadsheet, just go back to the Page Layout ribbon and click “Delete Background”.

Have a great break and see you in 2015!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.

From the Not Just Numbers blog:

Last week we covered the IRR function, which enabled us to calculate the Internal Rate of Return of a project.

This week I am going to introduce the NPV function which allows us to calculate Net Present Value – a function closely related to IRR.

The Net Present Value of a series of cash flows, gives a value of those cash flows today discounted by a required rate of return.

The required rate of return represents represents the investor’s time value of money. This is often the rate of return achievable in alternative investments.

So, for example, if we have the following series of cash flows (the same example as used for the IRR post):

Initial outlay  £20,000

Year 1 positive cash flow  £500

Year 2 positive cash flow  £5,000

Year 3 positive cash flow  £8.200

Year 4 positive cash flow  £9,000

Year 5 positive cash flow  £9,000

and we require a 10% annual rate of return.

The syntax of the NPV function is:

=NPV(rate,value1,[value2],…..)

where rate is the required rate of return and value1, value2, etc. are a series of cash flows at the same regular interval as the rate, so if the rate is an annual rate, then the cash flows should be a year apart.

value1 is required, whereas [value2] onwards are optional. These can be entered as values or refer to a range. Note that these cash flows are assumed to be at the end of each period.

So we could write the function for our example as follows:

=NPV(0.1, -20000,500,5000,8200,9000,9000)

which returns £2,257.25.

More likely though, we will have the values held in a range of cells as we did in the IRR example:

B2  -20000

B3   500

B4   5000

B5   8200

B6   9000

B7   9000

In this case our function would be:

=NPV(0.1,B2:B7)

Obviously, we would normally also refer to a cell to provide the rate as well, rather than enter it directly into the formula.

NPV relates to IRR  because IRR is the rate that produces zero NPV, so:

=NPV(IRR(B2:B7),B2:B7)

will always return zero.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.

From the Not Just Numbers blog:

Last week I introduced the PMT function for calculating payments on a loan.

This is one of the many functions under the Financial heading in Excel. This week I want to introduce you to another. One that allows you to calculate the internal rate of return (IRR) for a series of cash flows.

The IRR is a method often used to compare alternative projects or investments. and is stated as a percentage return, within a specified period.

As with the PMT function, it is worth starting by stating the assumptions used in the calculation. The IRR function requires the cash flows to be at regular intervals and the return percentage it calculates is  for the length of that interval. So, for example, if the cash flows fed to the function are annual cash flows, then the IRR returned will be a percentage per annum.

Also, the cash flows involved must include both positive and negative cash flows. If you think about this is self-evident, as any return percentage would be infinite if there was no outlay and all cash flows were positive!

Typically this will involve an initial outflow (or investment) followed by a series of projected inflows.

For example, a business investment opportunity might be presented as follows:

Initial outlay  £20,000

Year 1 positive cash flow  £500

Year 2 positive cash flow  £5,000

Year 3 positive cash flow  £8.200

Year 4 positive cash flow  £9,000

Year 5 positive cash flow  £9,000

We might want to compare it to another project and a useful piece of informationin doing this would be to calculate the IRR for each project.

So, how do we do it.

The IRR function syntax is as follows:

=IRR(values,[guess])

Most of the time you won’t need the optional [guess] argument, but I will come to that in a minute.

values is the range of cells that show the cash flows in chronological order.

So, for our example we could enter the cash flows in cells B2 to B7 as follows:

B2  -20000

B3   500

B4   5000

B5   8200

B6   9000

B7   9000

Our function would then be:

=IRR(B2:B6)

which returns an IRR of 14% (rounded to zero decimal places). This means that over this 5 year period, the project has an internal rate of return of 14% per annum.

The [guess] argument is there because Excel uses an iterative process to calculate IRR and will give up if it has not arrived at it after 20 iterations. To do this it must start with an estimate. If the [guess] argument is omitted, an estimate of 10% (or 0.1) is used. For most projects this will work with no problems, however with cash flows that fluctuate wildly and/or have an unusually high or low return, it may not get there in the first 20 iterations, if it starts at 10%.

If this happens it will return a #NUM! error. You can then add a guess to the function so that it starts closer to the true answer and therefore can get there within the 20 iterations.

Next week we will look at calculating Net Present Value, which is the flipside of IRR.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.

From the Not Just Numbers blog:

Excel has a baffling array of functions under the heading of “Financial”, many of which are only likely to be used by financial analysts. However, over the next few weeks, I will tell you about a few that can be very useful to the rest of us.

This week, I want to introduce you to the PMT function, which allows you to calculate payments on a loan.

Before we get into how the PMT function works, it is useful to note what types of loan it will work for (and what types it won’t!).

The function works for loans with a constant interest rate and regular payments of the same amount. The only exception to this is that it does allow for a ‘balloon payment’ at the end of the loan.

If this is the nature of the loan payment you want to calculate, then the PMT function will work for you.

Essentially, you feed the function the loan details, and it returns the regular payment amount.

The syntax of the PMT function is:

=PMT(Interest Rate,No of Periods,Present Value,[Future Value],[Type])

The last two arguments are optional and we’ll come to those later in this post.

The three mandatory arguments are explained below:

Interest Rate: This is the interest rate per payment period, so if you have an annual interest rate of 6% with monthly payments, the Interest Rate entered shound be 0.5% (i.e. 6% divided by 12).

No of Periods: This is the number of periods or payments of the loan. A 5 year loan with monthly payments would be 60, for example.

Present Value: This is the value, today, of the loan and for a new loan will be the loan advance amount.

So, say we have a £10,000 loan over 5 years with monthly payments and an annual interest rate of 12%:

Interest Rate = 12%/12 = 1% or .01

No of Periods = 5 x 12 = 60

Present Value = £10,000

So our function becomes:

=PMT(.01,60,10000)

and returns a monthly payment of £222.44. Note that the function actually returns -222.44 as we entered Present Value as  positive figure. If the receipt of £10,000 is positive, then for consistency, the payment returned is negative. We could enter the £10,000 as a negative number (i.e. a negative payment), which would return a positive payment.

In reality, we wouldn’t normally enter these figures into the function directly, more likely we would hold them in cells so that we could experiment with changing their values. and the function would be more like =PMT(B1,B2,B3), where Interest Rate, No of Periods and Present Value were held in cells B1, B2 and B3 respectively.

The final two (optional) arguments work as follows:

[Future Value]: This is where you would enter a ‘balloon’ payment, or any balance that you wish to remain outstanding at the end of the period you are looking at. You might have a final ‘balloon’ payment of £5,000, and therefore the payment calculated would need to leave exactly £5,000 outstanding when No of Periods comes to an end. Alternatively, you might recalculate payments annually to hit a specific target balance at the end of each year. This should be entered with the same sign as the payments (i.e. the opposite sign to Present Value). This arguments is treated as zero if omitted, i.e. the loan will be completely paid off after all payments are made.

[Type]: This is a 1 or a 0 and indicates when in each period the payments are made. If this is 0, or omitted, the calculation assumes that payments are made at the end of each of the periods in No of Periods. Enter 1 for this argument if payments are made at the start of each period.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.

From the Not Just Numbers blog:

Do you have charts with titles that regularly need to be changed? Or text boxes in diagrams that need to be edited to reflect the numbers shown?

It is really easy to have these update automatically, but it is not obvious how you are supposed to do it in Excel.

Say you have several charts that feature in a monthly pack and each chart refers to the current month in its title. “Cumulative Profit to November 2014” for example.

In a normal cell we can create this sentence in a formula. If A1 contains the period-end date, then we can use the following:

=”Cumulative Profit to “&TEXT(A1,”mmmm yyyy”)

Unfortunately, we cannot just type a formula like that into a chart title or a text box,  but we can do the next best thing.

We could type our function above into, say, cell B1, then go to edit the chart title or text box (so we can see the cursor ready for us to type). We can then click in the formula bar and type =B1. The chart title or text box will then always show the contents of cell B1.

No more editing charts each month!

We can use exactly the same technique in a text box, which might refer to this month’s sales figure (held in cell A2). Our function would be something like:

=”Your sales this month are “&TEXT(A2,”£#,0”)

Once the text in our charts and diagrams is automated like this, they can simply be an output from the numbers, requiring no manual input.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.

From the Not Just Numbers blog:

This week, I just have a short post about a simple tool that is a bit of a hidden gem in Excel.

We know we can add graphics to an Excel spreadsheet by using the Insert ribbon to add images and shapes, as well as the many charts that we can create in Excel.

But what about when, instead of adding a bit of graphics to a spreadsheet, we want to add a bit of a spreadsheet to some graphics?

This might be having a table appear within the area of a chart, or in front of an image, for example.

We can do this easily using the Camera tool.

Before we can do anything, we need to make the camera tool accessible. Microsoft don’t make it easy to find!

To do this we need to add it to a ribbon. We can do this as follows:

• Select File, Options and Customize Ribbon
• From the “Choose commands from:” drop-down, select “All commands”
• Scroll down to find “Camera” (they’re alphabetical)
• Highlight any custom tab on the right hand side (the simplest is Home, Custom Edit)
• Click “Add>>”
• Click “OK”

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.

From the Not Just Numbers blog:

Regular readers of my blog will know that I am not normally a big fan of Macros. I would argue that in the majority of situations that I see them used, they are unnecessary and often result from a lack of understanding of how Excel can address the problem using its standard functionality.

Naturally this is not always the case and one particular situation that cannot easily be addressed using standard functionality is automatically copying a line of data from one row to another.

Before we get into it, I think it is important to point out that there are many times that I speak to a client who thinks this is what they need, when what they really need is a link between the two lists, or one list with a category field that can be used to report two lists from the same data.

I came across an example of when this was genuinely need just last week. In automating an accountancy practice’s job tracking, I had brought all of their data together into three distinct lists – Clients, Recurring Jobs and actual Jobs.

This allowed them to store information at the right level:

Clients – One row per client (e.g. XYZ Ltd), holding all information pertaining to that client.

Recurring Jobs – One row per each recurring job (e.g. XYZ Ltd Annual Accounts), holding the current information relating to that particular recurring job.

Actual Jobs – One row for each actual job, which could be a one-off job or an instance of a recurring job (e.g. XYZ Ltd Annual Accounts 2014). This holds all information for that specific job.

Each Client, Recurring Job and Actual Job then has its own unique reference that is used to link them and access information from the other lists (using lookups and SUMIFs, etc.)

However, on the Actual Jobs list, we need a new instance each time a recurring job comes around. We also want to copy the recurring information over, with the ability to edit it just for this instance. That means copying and pasting a number of columns from Recurring Jobs to Actual Jobs each time.

This can, of course, be done manually, however that can be cumbersome and prone to error – not least in that in this particular instance I wanted to use “paste as values” rather than a full paste, as this could override conditional formatting that I had used on the Actual Jobs sheet.

However we can create a relatively simple macro to do it for us.

To add a macro you will need the Developer Ribbon which you can enable by selecting File, Options, Customize Ribbon and ticking the Developer option on the right hand side.

From the Developer Ribbon, click Macros and enter the name (e.g. CreateJob) that you want to give this Macro and click Create.

Then paste the following code between the lines already there, being Sub CreateJob() (or whatever name you gave the Macro) and End Sub:

If ActiveSheet.Name = “Recurring Job Data” Then

Range(Cells(ActiveCell.Row, “A“), Cells(ActiveCell.Row, “F“)).Select

Selection.Copy

Sheets(“ActualJobs“).Select

If ActiveSheet.AutoFilterMode = True Then

ActiveSheet.AutoFilterMode = False

End If

Range(“A10001“).End(xlUp).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Rows(“1:1”).Select

Selection.AutoFilter

Else

Exit Sub

End If

The yellow items will need to be changed for your individual requirements:

Recurring Job Data is the name of the worksheet that we want to copy from. This part of the code, checks whether we are on this worksheet when the code is run, and exits the code if we are not. That stops the code running when we are on the wrong worksheet.

The A and F are the columns (from and to) that we want to copy over.

ActualJobs is the worksheet that we want to copy to.

A10001 is a cell in the first column of the worksheet that we want to copy to, beyond the bottom of the range of the existing data.

The code works as follows:

• Check the active worksheet is “Recurring Job Data” and exit the Macro if not
• Select columns A to F of the current row (the row that that the currently selected cell is in)
• Copy these cells
• Switch to the “ActualJobs” worksheet
• Switch off Autofilter (as if the list is filtered this may cause the code to incorrectly determine the bottom of the existing list
• Select cell A10001
• Go up column A until it finds some data and selects the cell below this
• Paste as values
• Switch Autofilter back on (assuming that the headings are in row 1)

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.

From the Not Just Numbers blog:

I have previously posted about using the Find and Replace facility to replace parts of text strings or formulae. But what if you want to be able to do this kind of thing automatically?

You’re going to need to do it with formulae. The good news is that Excel has a couple of functions to do this.

Say you need to replace a section of a nominal ledger code (maybe a section the denotes a department or cost-centre).

If this part of the code will always be in the same place then we can use the REPLACE function.

The syntax for the REPLACE function is:

=REPLACE(OldText,StartNumber,NumberOfCharacters,NewText)

where,

OldText is the text we want to amend

StartNumber is the position in OldText at which we want to start replacing

NumberOfCharacters is how many characters of OldText we want to replace

NewText is the text we want to replace them with

So, say we wish to replace characters 4 to 6 of the string in A1 with the letters SAL, then we can use:

=REPLACE(A1,4,3,”SAL”)

We may, however, not be able to rely on the department being in the same place. We can use the SUBSTITUTE function to replace a particular string with another.

The syntax of the SUBSTITUTE function is:

=SUBSTITUTE(Text,OldText,NewText,[Instance])

where,

Text is the text string that we want to amend

OldText is the text string (within Text) that we want to replace

NewText is the text string that we want to replace OldText with

Instance is an optional field that can be used to specify which instance (as a number) of OldText should be replaced. If this is omitted all instances of OldText are replaced with NewText

So, if in our earlier example we know the existing code has a department of ADM, then we can use:

=SUBSTITUTE(A1,”ADM”,”SAL”)

This will replace every instance of ADM with SAL, so be careful.

We can use the Instance argument, if we know that it is always the first instance for example:

=SUBSTITUTE(A1,”ADM”,”SAL”,1)

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.

From the Not Just Numbers blog:

A while back, I wrote a post explaining an approach to eliminating #DIV/0! and other errors in Excel.

Although the information in the original post is still valid, there is a more streamlined approach you can use in more recent versions of Excel (Excel 2007 onwards).

But before we get into that, a quick reminder that Mynda Treacy’s Excel Dashboards course is closing shortly, so get yourself over there if you don’t want to miss out!

Anyway, on with the post…

If you want to read the original post (out of curiosity or to stay compatible with Excel 2003 and earlier), you can do so here,

Where the original post used a combination of IF and ISERROR, the newer approach uses the IFERROR function.

The syntax for the IFERROR function is as follows:

=IFERROR(value,value if error)

The value argument can be any calculation that may result in an error message, and the value if error argument is what you want to replace the error with.

So, say you have a margin percentage calculation that divides Profit in cell B5 by Sales in B2. You might use the following formula formatted as a percentage:

=B5/B2

However if, in some instances, there might be zero sales, then this function will return the #DIV/0! error.

Instead of seeing #DIV/0! in this case, we can choose what should appear. We may prefer to show zero percent, or a dash, or nothing, or a message such as “No Sales”. We can do any of these with the IFERROR function as follows:

=IFERROR(B5/B2,0)

=IFERROR(B5/B2,”-“)

=IFERROR(B5/B2,””)

=IFERROR(B5/B2,”No Sales”)

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.

From the Not Just Numbers blog:

This week’s post is not so much learning a new Excel function, as learning a concept that you can use to enable you to import data and correct it, without overwriting the imported data. This is important, so that you can refresh the imported data later!

Before we get into that though, just a quick reminder that the 20% discount offer on Mynda Treacy’s Excel Dashboards course expires on Thursday, so if you’re interested you need to get over there now.

Excel is a great tool for reporting on data held in an external database (this could be anything, such as an accounting system, ERP/MRP software or maybe a CRM system). However, sometimes we need the ability to edit or override the imported data with some tweaks that we wish to see included in the final reports.

In most cases, I would encourage any changes to be made in the external database, then the data to be refreshed, however this is not always possible. This can particularly be the case with transactional data (such as orders, invoices, etc.). Once an order is complete, most systems won’t allow you to edit it, so if you need the order to be reflected differently in the final reports, this will need to be done in the spreadsheet.

You could over-type the imported data to make the changes, but this would be overwritten next time you refreshed the link to the external data. Far better to hold the changes in the spreadsheet to be applied to the data as it comes in. This is where I use what I call an override list.

As long as the imported data has a unique reference that you can link the adjustments to, you can use this approach. Simply have a separate sheet in the spreadsheet where you enter the references of any records that you want amended alongside the amendments. You can look up these amendments  in calculated columns alongside the data.

Say that you are importing a list of sales orders but that they are not always allocated to the correct salesperson in the external database. You could have an override list with the following two columns:

• Sales Order No
• Salesperson

Any references to the data (in PivotTables or SUMIF formulae for example) can now refer to CalcSalesperson, rather than Salesperson.

The specific formula will vary depending on what you are trying to adjust, but the principle remains the same – and the changes will be applied every time that particular Sales Order No appears in the imported data.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.