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
Two Days ago in the House of Commons the Chancellor of the Exchequer stood up and blethered on about how bad it was going to be … for you and me!
I read the papers and looked at one or two videos from the Financial Times and other places. The FT pointed me at www.ft.com/autumnstatement and that contains links to some excellent materials. What I wanted to do was to find some data and see what I could make of it in Excel.
The data came in PDF files and there is a LOT of it. What follows, then, relates to just one table from one file. Firstly the data and then some things that I did to it using Excel:
I used Excel for mac: 2011 for this analysis: for this reason you can see the sparklines. Sparklines, remember, are one cell charts or graphs that can be presented as line sparklines, column sparklines or win/loss sparklines. I used column sparklines above and highlighted negative values.
One of the things I was really looking for was the hockey stick effect of economic and financial forecasting: I found it! I think you will understand what the hockey stick effect means when you see the very first of the 11 charts I prepared from the above table:
Get it? The hockey stick effect I mean.
How about this one?
Got it yet? The hockey stick effect I mean.
OK, take a look at this:
I think you’ve got it now: the hockey stick effect is that when we are creating a forecast, we often say that the immediate future will be worse than today but after a while things are bound to look up and then things will get better and better.
Eight out of the eleven charts I produced from the UK Economy demonstrate the hockey stick effect: plot them all to see what the other three look like!
In addition to charts I programmed a correlation matrix, to take a look at the inter relationships that the UK Government has built into its forecasts. Take a look:
I hope you can read that table: I have used ordinary manual formatting for that and for the matrix itself I have used conditional formatting: greater than, lower than, between.
In terms of correlations, changes in inventories seems to relate to nothing … why would that be?
Domestic Demand correlates very well with most things … should this be the case?
Exports of Goods and Services is right in the middle in that it correlates only marginally with most of the other variables … is this a surprise?
Since I am using Excel for mac 2011 you need to know that it does not have the Data Analysis ToolPak Add-In available to it so I had to programme the correlation matrix cell by cell like this:
=CORREL($B$13:$H$13,$B14:$H14)
In terms of the world economy, here is a chart based on the first part of the table of data shown at the start of this post:
There is not that much evidence of the hockey stick effect here: well, not that we can see. Take a closer look at the sparklines and see what you can see. I am not saying the hockey stick effect is there but it’s a good idea to take a look at each variable alone rather than just relying on my composite chart!
There you are! I hope you find this whistle stop review of just one or two aspects of the data tables and so on that the UK Government has used to base its economy policies on … and for the sale of hockey sticks maybe!
The source of data on which this post has been based is: http://media.ft.com/cms/b6e1e1ec-1a91-11e1-ae4e-00144feabdc0.pdf
Duncan Williamson
Creating a Hyperlink in Excel
16/08/2011
There are times when you might want to make an entry in an Excel file, range or cell that not only links to another file, range or cell but actually goes to that file, range or cell at the click of a button. Just as on a web page, Excel allows us to create a hyperlink to somewhere else on your computer or network or even on the internet. Setting up and editing a hyperlink is very straightforward as this example will show.
This morning someone asked me to show him how to set up hyperlinks in an Excel file that confirms if and when an invoice had been paid. Now, what you are about to see is not foolproof or even the best way to manage this situation but it is what the client wants to do: it’s how they manage their small project invoices.
In their Excel file this client has a table that records for each invoice such details as:
- Date
- Name of supplier
- Address of supplier
- Invoice number
- Amount
- Paid or not
Actually, it doesn’t matter what is in their file because all we are interested in is the final column, paid or not.
The brief, then, was to get Excel to point to a PDF copy of an invoice that has been paid and that will sit in the same folder as the Excel file itself.
Every month, the ZIP file will be sent off to a supervisor/controller who is monitoring this project and its payments.
To set up the hyperlink: firstly, ensure that the PDF invoice files are in the folder they are meant to be in.
- in the paid or not paid column type the word yes or paid or cheque issued … whatever you want … keep it simple and I will enter the word yes for all invoices that have been paid.
- good spreadsheet practice says that you should enter something in the unpaid cells just to show that you haven’t forgotten them. Leaving these unpaid cells blank is not good!
- select insert then click Hyperlink icon
- at this stage there are four choices:
- link to an existing file
- link to a place in this document
- create a new document
- link to an email address
- for this example, we are going to link to an existing file so click to choose the file to link to: the invoice file relevant to the current cell … this is in the folder you have created
- check that the Text to Display says “yes” … you can edit that at any time by coming back here at any time in case you forgot to type yes or even made a mistake!
- when you are happy with what you have done
- click OK
Your hyperlink is ready now and you can test it: to test it just click on the displayed text in the cell/link you just created and it should open the file or worksheet that you have linked it to.
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
Recreating Charts: with analysis
22/03/2011
You might find that you read a newspaper or magazine article that contains a chart that you think you’d like to use in some way. One problem is that the chart is in the form of an image and the data on which it’s based is not available. It happens to me a few times a year.
This post then is concerned with turning the image of a chart into a table of data and then back into the chart … plus, plus. Read on to read a case study I have just prepared!
Case Study: recreating charts: with analysis
The purpose of this case study is for readers to extract and analyse data from a bar chart and data table. The case can be used in a wide variety of settings from secondary/high school level to undergraduate and professional examination level.
Having required the reader to recreate a chart from a published source, they are then required to comment on the new charts they create. In addition they are then required to discuss the possibility of an outlier being found in the data and comment on how such an outlier might be dealt with.
Finally, the reader is required to consider the situation in which the outlier is left in the data set and comment on the data set without adjustment.
Required
1 From the following chart, taken from The Economist, set up a spreadsheet file to achieve the following
a) Extract the data from the bars in the chart
b) Recreate the chart from The Economist in your file
c) Create a further chart to illustrate the percentage of domestic consumption data
2 Comment on the charts you have prepared in task 1
3 a) Prepare a third chart with TeraWatt Hours on the X axis and % of Domestic data on the Y axis and comment on your new chart.
b) In your new chart there is an outlier: suggest which data point might be an outlier and recommend what can be done with it from a statistical point of view
c) Comment further on your second charter further once you have eliminated the outlier data point
d) Suggest an alternative approach to dealing with this outlier
© Duncan Williamson
March 2011
There is a solution to the case study for anyone who is interested and it is available for just GBP10 or US$16: the solution is fully worked and it includes a 14 minute video that clearly sets out the two main spreadsheeting techniques needed for the case. Send me an email to sales@excelmaster.co.uk with a brief note to tell me you want to buy the solution and I will send you full instructions.
Duncan Williamson
In this blog I am going to introduce two things I am ashamed to say I have only just learned. It just goes to show that even after all these years of using and learning Excel spreadsheeting, new things can still appear.
I am going to talk about
- · =RAND*(b-a)+a
- · =RAND() and the F9 key
=RAND*(b-a)+a
The formula =RAND*(b-a)+a is used instead of =RANDBETWEEN(…). So if you want to generate random numbers from, say, 7 and 11 then a = 11 and b = 7. That is, a>b.
The following screenshot shows you the effect. In this screenshot I have generated the random numbers in their own cells using =RAND() and then used, for example, =A1*(b-a)+a to finish it off:
Press the F9 key to refresh this volatile function and see that it does give you randbetween using rand. Please note, the answers are NOT integers, use =RANDBETWEEN(…) for that.
That’s is, but here is a screenshot of a chart I prepared so that by pressing the F9 you can see how random your chosen numbers are. In my case I created a table with two sets of 30 random numbers, X and Y.
=RAND() and the F9 key
I really am surprised that I did not know that this utility existed. Imagine you want a random number that is not volatile … you just want to generate such a number and don’t need it to refresh every time you hit F9 or Enter.
Put your cursor in the cell where you want a random number to appear. Then in the FORMULA BAR and NOT in the cell, type =RAND() and then press the F9 key. That’s it and in your chosen cell you will have a random number and not =RAND().
This also works with =RAND()*n where ‘n’ is any number
Duncan Williamson
Just check a little lower down this blog and you will see that you were all invited to come and attend my seminars in Ho chi Minh City and then Bangkok earlier this month. Attendance was fair for both courses but just look at what you missed. Here are the comments that came back via the evaluation forms … nothing doctored by me either.
Ho chi Minh City
All participants found the course informative and all aspects of program content were useful to them, namely in particular, PIVOT Table & RATIOS formula, financial model – Excel model, using excel for Financial Statement Analysis, all methods explained – many are new.
Some verbatim:
- All program are useful and I learn it so much
- I know more the graph, calculation the Excel and more information about the course
- New techniques were outlined
Bangkok
All participants found the course informative and trainer ‘Very Good and a lot of experience, Good presenter”. On program content, most of them found they were all useful to them, particularly, all techniques, excel formula & some tactics, Pivot, Array, techniques to use excel function more effectively, the speaker explains very clearly and allow audiences to practice along. There was only one stated that ‘array’ was not too useful. What pleased all participants the most were ‘content, experience of speaker, new things that can work faster and more efficient, excel formula, proper techniques to use in excel/report, pivot table, waterfall chart’.
Some verbatim:
- Very good and a lot of experience
- Good presenter
- The speaker explains very clearly and allow audiences to practice along
- Experience of speaker
- New things that can work faster & more efficient
There … and I bet you thought it might not be worthwhile!
Just drop me a line at any time and I can run a course for your organisation … any time, anywhere … well, lots of places with a few weeks’ notice to get things organised properly.
Duncan Williamson
Join me in Bangkok
23/07/2010
I am presenting a seminar in Bangkok entitled Advanced Spreadsheet Skills & Techniques for Financial Managers. It will be held in the Shangri La Hotel in Bangkok (fabulous location by the way and fabulous hotel) on 26th and 27th August.
The session headings are:
session 1: FUNCTIONS AND FEATURES NEW TO EXCEL 2007
session 2: WORKING WITH IMPORTED TEXT DATA AND HANDLING
session 3: CHARTS
session 4: EXCEL TABLES
session 5: WHAT IF? AND OTHER TYPES OF ANALYSIS
session 6: ARRAY FORMULAS
session 7: LOOKUP FUNCTIONS
session 8: VARIOUS TECHNIQUES
This is a very intensive course and you’ll learn and take away a mass of information: all files, a working file, all my slides … worth a fortune.
Oh! and you get to meet me too!
Go here to see and book http://www.abf-asia.com/page/public/contents/event/spreadsheet_for_FinMgr/training/249
Hope to see lots of you there!
Duncan
SUMPRODUCT v SUMIFS
25/03/2010
David H Ringstrom has written a series of articles for the Excel section of www.accountingweb.co.uk. One such page, which is well worth reading, is the one on SUMPRODUCT: http://www.accountingweb.com/topic/excel/some-unlikely-uses-sumproduct. In that article David shows us how SUMPRODUCT can in reality emulate the powerful but often misunderstood VLOOKUP and HLOOKUP functions.
However, I think that whilst there is nothing intrinsically wrong with David’s approach I would recommend everyone who is using Excel 2007 to start thinking about the new SUMIFS function.
David’s example is contained in an Excel 2007 spreadsheet that I have built from the information provided in David’s article and that you can download by clicking the link at the end of this article. I have taken David’s SUMPRODUCT ideas and programmed them using his information. In parallel I have also programmed my version, the SUMIFS version.
The Excel 2007 write ups tell us that their new functions, such as SUMIFS require less space, less memory and so on and are therefore more efficient. The comparison between the two functions I am discussing here are clearly shown when I asked the two functions to add together sales based on
Region and City and Chain and Product
Here are my results:
=SUMPRODUCT((A2:A19=A22)*(B2:B19=B22)*(C2:C19=C22)*(D2:D19=D22)*E2:E19)
=SUMIFS(E2:E19,A2:A19,A25,B2:B19,B25,C2:C19,C25,D2:D19,D25)
You can see the programming for SUMIFS is shorter and perhaps easier to manage than the programming for SUMPRODUCT: the asterisk in this function is often mistaken for multiplication rather than the addition or adding that it really represents.
Click here to download my Excel 2007 file relating to this page.
Duncan Williamson
Limit Scrolling Area
08/03/2010
I don’t use VBA coding very much at all, well, hardly ever in fact. However, this evening I came across a couple of features that I thought were useful to know and one of them involves using VBA.
Imagine you want to set up a worksheet with a model or some information or something but it only contains data in, say the range A1:E100 and you don’t want anyone to be able to move their cursor outside that range. Here is how to stop the cursor from moving below row 100 and to the right of column E.
- Open a workbook and right click your mouse on any sheet tab
- Select View Code
- In the coding area copy and paste the following:
- Private Sub Worksheet_Activate()
Me.ScrollArea = "A1:E100"
‘Set back to normal
‘Me.ScrollArea = ""
End Sub - Save your worksheet
You then need to click on any other sheet tab in the workbook and then come back to the sheet that you have saved the macro in and that macro will now be active.
It doesn’t take much to change the area you want to control: just change A1:E100 to F5:N23 or whatever you like. Try it!
Duncan Williamson
Thanks to the people here for this tip: http://www.mrexcel.com/forum/showthread.php?t=3233
