I am preparing for a course that I will be running next week and since the major topic is Debtor Management, I felt it would be good to develop an Excel file to prepare an ageing schedule.
Once I had started I decided I could prepare a variety of solutions:
- using functions and formulae
- using conditional formatting
- using a Pivot Table
- using Excel Tables
Here are some screenshots that help to illustrate what I have done and after the screenshots, I show you some of the formulae I used in the development of this file.
In any case, this workbook does two things:
it allows pupils to consider a real ageing schedule albeit with a simplified example and to change the payment boundaries: for example, invoices owing between 1 and 30 days then 31 and 60 days … you can change it to 1 – 25 days then 26 – 50 days … and any range of days you like. This will help you to explore the credit control strategy and so on. It leads in to cash forecasting and budgeting too.
To determine whether we are dealing with an entry between 1 and 30 days, 31 and 60 days … =IF(AND(TODAY()-$B14>K$14,TODAY()-$B14<=L$14),$D14,”") … this gives the answer in values
to show the same results but in terms of days …
=IF(AND(TODAY()-$B14>K$14,TODAY()-$B14<=L$14),TODAY()-$B14,”")
Showing the status of a debtor’s balance: overdue or current …
=IF(TODAY()>C14,”Overdue”,”Current”)
Similarly, finding Overdue and Current allowing for the payment of an account …
=IF(D14>0,”",IF(TODAY()>C14,”Overdue”,”Current”))
Then there are the functionalities of Conditional Formatting and Excel Tables
The Pivot table, which I don’t show in screenshot form above even includes the suppression of zeroes.
An apparently simple problem but with some interesting programming to do.
Duncan Williamson
Combinations with my Breakfast!
12/08/2011
As one does, I went into my kitchen this morning and on seeing five different cereal packets in my cupboard, suddenly wondered, how many different breakfasts could I make out of those five cereal types?
For example, if I only had one cereal a day, then I could have five different breakfasts:
Monday = Muesli
Tuesday = Bran Flakes
Wednesday = Weetabix
Thursday = Malted Shreddies
Friday = All Bran
Suppose now, though, that I mix two cereals a day: for example, Muesli and Bran Flakes … how many different breakfasts can I have now? That is, in how many ways can I combine the five different cereals, two at a time?
Make a list: and prove that the answer is that you can have ten different breakfasts from a choice of five cereals taken two at a time.
Suppose now that I make a mixture of three cereals a day from the five cereals in my cupboard: how many combinations of breakfast are available to me now?
Check that you agree that the answer has to be ten again.
Choosing four from five cereals gives me a combination of five possible breakfasts and choosing five from five cereals gives me a combination of one possible breakfast.
That’s taken a long time hasn’t it? A lot of brain work and making of lists and tables. To see a more detailed background to this topic, take a look at my web pages on combinations (and the sister idea of permutations) here:
http://www.duncanwil.co.uk/permcom.html
http://www.duncanwil.co.uk/com.html
In that first page you will learn the difference between permutations and combinations: in simple terms, permutations count ALL possible variations whereas combinations exclude equivalent outcomes.
That is, permutations would count as three different breakfasts
Muesli + All Bran + Weetabix
Weetabix + Muesli + All Bran
All Bran + Weetabix + Muesli
whereas combinations say they are the same as each other … mathematically speaking.
Excel Functions: combinations and permutations
Let’s cut this page short now and introduce two Excel functions that save us from having to create these possibly lengthy analyses to find out how many possible breakfasts I might create from my five cereals:
=COMBIN(n,r) that is, find the combinations of r breakfasts from n cereals
=COMBIN(5,3) = 10 = I can create ten different breakfasts by combining three cereals from the five cereals available
=PERMUT(n,r) that is find the permutations of r breakfasts from n cereals
=PERMUT(5,3) = 60 = I can create 60 different breakfasts by perming three cereals from the five cereals available
The differences between the number of combinations and the number of permutations can be very large and the table below shows a table to compares the combinations and permutations for this cereal and breakfast case study.
| Summary | ||
| n | 5 | |
| r | Combinations | Permutations |
| 1 | 5 | 5 |
| 2 | 10 | 20 |
| 3 | 10 | 60 |
| 4 | 5 | 120 |
| 5 | 1 | 120 |
There you are: that’s what I thought about as I opened my cupboard to make my breakfast this morning.
Why not build the above table for yourself using =COMBIN(n,r) and =PERMUT(n.r)\; make n = 5 and r range from 1 to n; then change n to 10, say; and make r range from, say, 5 to 9 … as you wish.
Duncan Williamson
I am often asked the question of whether it is possible to prepare an income statement and a balance sheet in Excel. I always reply that of course it is simple to do that providing you know the basic layout of those statements.
This set of notes illustrates that all you really need to prepare an income statement and a balance sheet using Excel are:
· The data: a list of revenues, expenses, assets, liabilities and capital are all you need. In these notes, I will be dealing with the income statement and balance sheets separately
· A Pivot Table: well, in this set of notes, two Pivot Tables but the homework at the end of the notes is for you to combine everything into one list and one Pivot Table
I am assuming that you already know the basics of
- · Financial statements
- · Preparing and using a Pivot Table
This is a lengthy note so I have put it into a PDF file for you to download and work through at your own pace.
What you are aiming at here is to prepare these Pivot Tables from lists of data:
The Excel spreadsheet i used to create the above was corrupted beyond repair by Excel 2010 after I gave it the massively onerous task of adding headers to each worksheet. After all, after only 25 years of development, we’ve got to give Gates and his tribe a fair crack of the whip and admit that adding a header is one of the more advanced functions built into Microsoft Office’s 2010 suite.
Duncan Williamson
Office 2010 Beta Version
21/12/2009
I downloaded and installed Office 2010 Beta version last night and I have to say there are some stunning advances there. I’ve not had much of a chance to play with everything new but in Excel, of note, there are
- sparklines
- data slicer
PowerPoint looks a bit niftier.
Outlook promises things that I haven’t explored at all yet and the same with Word.
Worth a look I’d say and I will report more shortly. Don’t forget Excel Master promises to metamorphose into Excel 2010 reasonably quickly.
I’m running a business and financial modelling course this week and four out of the seven delegates are still using Excel 2003! My training consists only of excel 2007 these days but they are coping well with the ribbon system that so many people dread for some reason.
DW
