ICAEW meets Microsoft - How to get better at Excel

Earlier in January, I happened to be in Seattle for some much-needed vacation time.  But I couldn’t resist taking a little detour to visit Microsoft’s campus in Bellevue, just over the lake, to talk to some of the higher-ups that oversee the Excel program.

Meeting with Microsoft - What can accountants do to get a better Excel?

I’ve met with Microsoft a few times, and from my point of view, there’s one theme that always comes up: whether it is niggling, small suggestions or efficiency improvements, or whether it is totally new features or areas for their machine learning efforts to concentrate on, at some point in every conversation, the topic always comes up – they want to hear your suggestions.

And that’s especially true for accountants: Microsoft knows full well that accountants use a lot of spreadsheets, and they want to try and stay on top of the heap for the foreseeable future.

Now, there are already some outlets for making feature suggestions to Microsoft – principally through their community suggestions site, https://excel.uservoice.com.  Users can both submit their own ideas there, and also vote on others’ ideas to make it clear to Microsoft what the best and most popular ideas are.

For accounting specifically, Microsoft have said many times that they’re happy to hear suggestions from ICAEW and other accounting bodies – so feel free to drop me a line ([email protected]) if you have more specific ideas.

Tip of the Week highlight: Evaluating net present value or internal rate of return

We bring you something a bit more accounting-specific from the TOTW this week – a look at content shown back in Tip #85 (membership required).

In short, you can calculate the effect of a constant discount rate on a series of cash flows by using the function NPV (for regularly timed cash flows) or XNPV (for irregularly timed ones):

            =NPV(discount rate, range of cash flows)

            =XNPV(discount rate, range of cash flows, corresponding range of dates)

You can also compute which rate is needed for a 0 NPV using the functions IRR or XIRR:

            =IRR(range of cash flows)

            =XIRR(range of cash flows, corresponding range of dates)

These functions can be used to simplify the calculations necessary in producing a single answer from a model or budget that uses a discount rate. XIRR can also be used to impute an average rate on an investment, pension, or savings account, by showing positive cash flows for each purchase / payment in, and a negative cash flow for the current value.

Principle of the Month

This month’s selection from the 20 Principles for Good Spreadsheet Practice is #15:

Perform a calculation once and then refer back to that calculation.

Creating parallel functions to carry out the same calculation is an easy source of error.  Firstly, each time the function has to be built, a chance for human error is introduced, and the overall chance of an error existing increases. Secondly, by having parallel calculations, you risk a situation where the calculation logic changes and some formulas are updated, but some are missed.

The safer and better practice is to build the calculation you need, in most cases, only once, and use a direct cell reference or named range to pull through the number anywhere else it’s needed in the workbook.                                                  

See you next month,

~David