0
Image result for accountant picture

Crunchin’ da numbers

Recently I went in to do some training with a new manager who had been tearing his hair out with his Excel sheet.

The spreadsheet had already been set up for him and the previous incumbent had done some interesting array formula variations using If and Sum – and it worked fine actually. The only problem was that this new user had no idea how to amend or fix this formula. He need a-rray of sum(ifs)shine…sorry.

I decided to show him how to use the SUMIFS function to do exactly the same thing – only easier and no fancy CSE (Ctrl-Shift-Enter) moves as required to create an array formula….

The only data he really required was to show the spend to date for the year (and you can see that formula in the Spent Year to Date sheet. I could have set it up as a table but I wanted to make sure he understood what we had done here so 5000 rows allows lots of room for expansion.

Couple of things to note:

  1. All the criteria ranges have to be the same “height” i.e. 4:5000. Because alas, the SUMIFS won’t work otherwise…
  2. All the criteria ranges are fixed in this question (i.e. I used F4 to fix them) but the criteria1 is not because I needed that to change as I copied it down.
  3. You can check the answers by using the Filter option (click in list – Data – Filter)

Of course I got home and found myself idly wondering – well what if he needed the months as well. So I thought I’d experiment with that – as you do…

So I found I had to add another column to extract the month from the date which I added to the Month column

=TEXT(A4,”mmmm”)

Then in the Spent Month by Month sheet, I put the months across the top and entered the following formula:

=SUMIFS(‘Procurement list’!$D$4:$D$5000,’Procurement list’!$B$4:$B$5000,$A4,’Procurement list’!$E$4:$E$5000,’Spent month by month’!B$3)

A quick way to enter the formula is to:

  1. Highlight the entire sheet (B4:M24 in the Spent Month by Month sheet) 
  2. Enter the formula in the white cell (because I started highlighting at the top, this is where I had a white cell)
  3. Press Ctrl and Enter
  4. This will enter the formula in the entire highlighted area…

You can download the file here : Procurement_summary

Excel Products for Sale

Here are products from some of my favourite Excel authors and bloggers. Please note these are affiliate links so I do earn a small commission from every sale.

MrExcel.com

Mr. Excel (aka Bill Jelen) is one of the Excel gurus. I love his stuff. He also has a slightly bonkers delivery (What! Excel! Fun!)  that I thoroughly enjoy. And of course anyone who calls his Excel publishing book company Holy Macro! gets my vote.

MyExcelOnline.com

Xtreme Pivot Tables course

This is a very comprehensive course on pivot tables. It includes videos and workbooks. There are over 200 hours of videos on it and it takes you from having no knowledge of pivot tables to being a power user. If you know everything in this course, you know an awful lot about pivot tables. John explains the concepts in bite size chunks so they are easy to follow and even provides a tutorial spreadsheet so you can track your progress. This would be money well spent. If you would like to become the go-to person in your organisation for pivot tables, this would be an excellent starting point.

He also has a useful Pivot Table webinar…

This is also a useful chart helper. 

Debra Dalgleish – Contextures.com

Master 30 Excel functions in 30 days –This is a rather brilliant e-book which covers 30 functions including Vlookup,Match, offset. It includes an Excel file, e-book and links to videos that shows you how to do it – all for the princely sum of $10. What I particularly like about it are her descriptions, explanations and what the limitation of each function is. Highly recommended. If you want to upgrade your Excel skills this is a very worthwhile investment.

Excel UserForms for Data Entry

The Excel UserForms for Data Entry ebook kit will take you through the steps to build a UserForm that stores data on a hidden worksheet. No programming skills are required — everything is explained in simple steps, with written instructions, screen shots, videos and workbooks.

PivotPowerPremiumRibbon

The PivotPower Premium add-in saves you time and effort, when working with Excel pivot tables. For example, you can quickly:
-save and apply default pivot table settings
-clear old items from an Excel pivot table
-change all the data fields from the Count function to the Sum function

Chandoo.org

Click here to view more details about Excel Dashboard Video Tutorial
Click here to view more details on Excel Dashboard Templates
Click here to view more details on Excel Dashboard Templates Premium
Click here to view more details on Excel Formula Helper E-Book
Click here to view more details on Excel Formula Crash Course – View Option
Click here to view more details on Excel Formula Crash Course – Download Option
Click here to view more details on Excel School, Online VBA Classes & Dashboard Templates
Click here to view more details on Advanced Excel & Power Pivot Training Classes
Click here to view more details on Excel School & Online VBA Training Classes
Click here to view more details on Excel School & Dashboard Templates
Click here to view more details on Excel School – View & Download
Click here to view more details on Excel School – View Only
Click here to view more details on Excel Project & Portfolio Management
Click here to view more details on Advanced Power Pivot Class
Click here to view more details on Project Management Templates 2003
Click here to view more details on Project Management Templates 2007
Click here to view more details on Project Management Templates Both
Click here to view more details on Project Portfolio Templates
Click here to view more details on the VLOOKUP Combo Book
Click here to view more details on the VLOOKUP eBook
Click here to view more details on Online VBA Training Classes – View & Download
Click here to view more details on Online VBA Training Classes – View Only

 

 

 

 

Continue Reading

0

In today’s tutorial we are going to look at how to use the PMT function in Excel to calculate mortgage repayments. In another post, I’m going to show you how to use the PMT function with another Excel feature (called Data Tables) to very quickly generate a table of values.

Essentially there are 3 main parts to it:

  • Rate – Interest Rate (if the annual rate divide it by 12 if you want to show monthly payments)
  • NPer – payment periods for the loan (don’t forget that if you want to show monthly payments you need to multiply the years by 12)
  • PV – Present Value (the value of what you are borrowing now)

You have two other optional parameters: FV – if you want there to be an amount left when you have finished repaying the loan and Type – the default is 0 and assumes the payments are made at the end  of the year/month or you can enter 1 – which assumes the payment are made at the beginning of the period. It is worth your while to experiment with these two and note how they impact on the repayment amounts.

So here’s the video

 

 

And here (as always) is the file to practise on. 

Continue Reading

0

Jakki Francis is a qualified accountant making her unique amongst bookkeepers.
She offers a virtual bookkeeping service for women in business, done well, on time and stress free, whilst supporting you and your business. www.morethanabookkeeper.com

One of her recommendations is for people to create a simple Excel spreadsheet to track their business transactions.

The Value of Good Bookkeeping

When people start their own business they have to quickly learn to do every task in that business ,including the bookkeeping. Or sometimes, in the case of men running their own business, they delegate this task to their partners. (Sorry chaps but it’s true) Either way confusion reigns as they try to get to grips with what their accountant will need at the end of the year.

Savvy business owners quickly recognise that keeping their records up to date means that they have a firm grip on their cashflow and a snapshot of how their business is growing.

Others bury their heads in the sand and throw all their receipts into a carrier bag and hope for the best.

Guess which ones
a) do well in business? And
b) keep their accountancy fees to a minimum?

So, what do you do if you aspire to be savvy but you recognise that you are closer to the carrier bag scenario?
Answer: start a simple excel spreadsheet

I suggest as a bare minimum, that within an Excel workbook, you keep one sheet to record your sales (invoices or takings), one sheet to record your bank transactions and one sheet to record your cash expenses.

A couple of things to be aware of:
Clients forget to check that their spreadsheets cross-cast. What this means is that they put figures in their spreadsheets and total the columns but then they forget to total all of the column totals to see if they agree to the main total. When I receive a client’s spreadsheet the first thing that I do is check that, because it immediately tells me if I can rely on their figures or if I need to go looking for mistakes. See example below

 

The other big mistake I see is that when clients add columns or rows to their spreadsheets they forget to check their formulas to ensure that the new column or row is included in their totals.

Of course if your spreadsheet that records your bank transactions agrees to your bank balance at your year end so much the better.

Managing your cashflow should also be paramount, more businesses fail because they fail to manage their cashflow than for any other reason.

You can read about an easy way to manage your cashflow and download a simple spreadsheet from my website.

At some point in your business’ evolution you will recognise that there are certain tasks that you need to pay someone else to do. This frees you to concentrate on your main task which is operating and growing the business. This is the part business owners enjoy the most so it makes sense to have someone else do the parts they don’t like so much.

That’s where someone like me comes in.

Having been an accountant for almost 30 years, I’ve seen most things where bookkeeping is concerned and well kept books that balance are a joy to behold as well as a rarity.
I’m sorry to say that this is because businesses either do their own bookkeeping or they employ a member of the family or a someone calling themselves a bookkeeper to do the work.
The business owner often believes that their books are in good order but the reality is somewhat different and that is because they do not spend time understanding what their books and accounts are telling them about their business and they trust that the person they are paying to do the work is doing it correctly. Regretfully it is also because good bookkeepers are hard to find.

When you get one value her, your business will thank you

 

Excel Products for Sale

Here are products from some of my favourite Excel authors and bloggers. Please note these are affiliate links so I do earn a small commission from every sale.

MrExcel.com

Mr. Excel (aka Bill Jelen) is one of the Excel gurus. I love his stuff. He also has a slightly bonkers delivery (What! Excel! Fun!)  that I thoroughly enjoy. And of course anyone who calls his Excel publishing book company Holy Macro! gets my vote.

MyExcelOnline.com

Xtreme Pivot Tables course

This is a very comprehensive course on pivot tables. It includes videos and workbooks. There are over 200 hours of videos on it and it takes you from having no knowledge of pivot tables to being a power user. If you know everything in this course, you know an awful lot about pivot tables. John explains the concepts in bite size chunks so they are easy to follow and even provides a tutorial spreadsheet so you can track your progress. This would be money well spent. If you would like to become the go-to person in your organisation for pivot tables, this would be an excellent starting point.

He also has a useful Pivot Table webinar…

This is also a useful chart helper. 

Debra Dalgleish – Contextures.com

Master 30 Excel functions in 30 days –This is a rather brilliant e-book which covers 30 functions including Vlookup,Match, offset. It includes an Excel file, e-book and links to videos that shows you how to do it – all for the princely sum of $10. What I particularly like about it are her descriptions, explanations and what the limitation of each function is. Highly recommended. If you want to upgrade your Excel skills this is a very worthwhile investment.

Excel UserForms for Data Entry

The Excel UserForms for Data Entry ebook kit will take you through the steps to build a UserForm that stores data on a hidden worksheet. No programming skills are required — everything is explained in simple steps, with written instructions, screen shots, videos and workbooks.

PivotPowerPremiumRibbon

The PivotPower Premium add-in saves you time and effort, when working with Excel pivot tables. For example, you can quickly:
-save and apply default pivot table settings
-clear old items from an Excel pivot table
-change all the data fields from the Count function to the Sum function

Chandoo.org

Click here to view more details about Excel Dashboard Video Tutorial
Click here to view more details on Excel Dashboard Templates
Click here to view more details on Excel Dashboard Templates Premium
Click here to view more details on Excel Formula Helper E-Book
Click here to view more details on Excel Formula Crash Course – View Option
Click here to view more details on Excel Formula Crash Course – Download Option
Click here to view more details on Excel School, Online VBA Classes & Dashboard Templates
Click here to view more details on Advanced Excel & Power Pivot Training Classes
Click here to view more details on Excel School & Online VBA Training Classes
Click here to view more details on Excel School & Dashboard Templates
Click here to view more details on Excel School – View & Download
Click here to view more details on Excel School – View Only
Click here to view more details on Excel Project & Portfolio Management
Click here to view more details on Advanced Power Pivot Class
Click here to view more details on Project Management Templates 2003
Click here to view more details on Project Management Templates 2007
Click here to view more details on Project Management Templates Both
Click here to view more details on Project Portfolio Templates
Click here to view more details on the VLOOKUP Combo Book
Click here to view more details on the VLOOKUP eBook
Click here to view more details on Online VBA Training Classes – View & Download
Click here to view more details on Online VBA Training Classes – View Only

 

 

 

 

Continue Reading