Help and ideas for busy numbers people
Here are the five most useful power query functions for accountants, in my personal opinion:
It is, of course, impossible to pick just five. However, the point of this post is to illustrate that the power query functions I use the most are actually the simple ones. They are still enormous timesavers. Let’s have a look.
Continue reading “The most useful power query functions for accountants”
I had a fun challenge recently – accounting for a property purchase in Xero where a third party had paid grant funding directly to the vendor. As it took me a while to work it out, and I couldn’t find what I was looking for online, I thought I’d set it out here.
Some of this advice also applies if you need to account for any kind of purchase where a third party has paid the supplier directly. Or if you are accounting for a property purchase in Xero where you have already paid a deposit but only received an invoice at completion. This post assumes that you have reasonable prior knowledge of accounting for invoices, bills, receipts and payments in Xero.
Continue reading “Accounting for a property purchase in Xero – partly paid by direct grant to vendor”
I attended an excellent livestream last week about Excel dashboards, presented by the legendary Chandoo. In his tutorial, he built a KPI dashboard in Excel in around an hour. As well as learning lots of useful tips, it also made me think about the key skills (Excel and otherwise) that you need to build a great dashboard.
Continue reading “Creating dashboards in Excel – key skills”Power Query has been out for over a decade and yet the majority of accountants I meet still seem unaware of what it can do for them.
A possible issue is that it is usually presented in the context of Power BI, and analysing Big Data. And the type of people who like to talk about that also like to talk about other scary things such as VBA and SQL. There aren’t many training resources or articles focussing on Power Query for accountants more generally.
Before I was an Excel trainer, even before I was an accountant, I taught English as a foreign language. Even though the subject matter is quite different, I learned some really useful techniques and principles which I apply to Excel training now.
Continue reading “How English language teaching helped me become an Excel trainer”
Excel Tables (capital T) have been part of Excel since the 2007 edition. Given it is so old, why is it still common to see spreadsheets holding and calculating data but not using Tables?
A quick internet search will reveal tonnes of articles about why you should use Excel tables.
Here is a really good one, for example.
However, in this post, I thought I would explore this from a different angle, to try to understand people’s reluctance to use this amazingly useful tool.
Continue reading “Why aren’t you using Excel Tables?”
This week is Mental Health Awareness week so today’s blog post is a short plug for one of my favourite frameworks for thinking about mental wellbeing. (See here for another one.)
The 5 Ways to Wellbeing was developed by the New Economics Foundation in 2011. It’s been adopted by many organisations, including the NHS.
Continue reading “5 ways to wellbeing – the happy finance team edition”I have the visual sense of a blind goat, but over the years I have picked up a number of Excel design tips which I apply to any spreadsheet that someone else has to look at.
This post sets out ten areas which you can look at to make your spreadsheets cleaner and more professional.
I’ve written this post on the assumption that you are reading spreadsheets online, but most of these Excel design tips also work for printed documents.
The first tip is the easiest – turn off gridlines. Gridlines are the lines marking the rows and columns.
I recently set up a management accounts model using Power Query to combine multiple forecasts.
This is for a startup charity, where we need the ability to forecast at a high level over a five year period but to be able to update that quickly based on current decisions. We also need to do a detailed six monthly forecast for cash flow purposes. We also want to approve changes on a quarterly basis. Finally, we want to be able to assess the quality of our forecasting so that we can continue to improve it.
Continue reading “Multiple forecasts with Power Query”
Even small charities can have big complexities when it comes to choosing and using accounting systems.
There are two issues in particular which can be hard for charities to deal with on many accounting systems. The first is VAT partial exemption. Charities may have a mix of exempt income (grants, donations) and taxable income (eg selling goods or running a cafe). This means that they cannot reclaim all of their VAT and have to do “partial exemption” calculations.
Continue reading “Yes, Xero is a good accounting system for charities”