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
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
Randy posted this question on the brilliant Excel-G discussion list:
Morning ALL, Happy Valentines to you and yours.
I am in need of help with a TRUE statement in the form of a formula for Conditional formatting in Excel 2007I have a check register that I want to use an X if the amount has cleared, an M for memo to the account, and a H for HOLD.
I have created a formula to create 1, 2, 3, In column A for the statement above. so if X is in cell B3 then 1, if M then 2, if H then 3.and so on.
I have created the TRUE statement in the conditional formula with =AND($A:$A=”1″) then the condition is TRUE changing all cells in the row ( B3:J3) to Orange, and so on, I have approximatly 350 rows in my sheet. and this formula doesn’t work for conditional format.
Does anyone have a better idea on this ?
TIA
Randy
Here is my response and there is a link to the sample file I created in case you need further help with working through the Excel 2007 Conditional formatting way of doing things:
Try this Randy,
Firstly, I agree with Wyatt that you don’t need to translate X into 1 … just leave the X/M/H column alone
Select the entire range you want to format: extending beyond that for future entries if you wish.
Now select Conditional formatting and select the New Rule option
Select Use a formula to determine which cells to format and enter three separate formulas:
=INDIRECT(“B”&ROW())=”X” for the cleared cheque option … format as you wish
=INDIRECT(“B”&ROW())=”M” for the memo cheque option … format as you wish
=INDIRECT(“B”&ROW())=”H” for the hold cheque option … format as you wish
… see my sample file for why there is “B” in this formula … see below
Click OK OK
The =INDIRECT() function is used to tie the row you are in to the value in column B where the value X/M/H is to be found
The question and full thread, including this reply can be found at: http://peach.ease.lsoft.com/archives/excel-g.html … it’s a free discussion list and it really is brilliant!
Click here to download the Excel 2007 file I created to demonstrate the solution here.
Duncan Williamson
