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
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
When you enter a formula in a cell in Excel everyone can see it in the formula bar when you click on the respective cell. This tip shows how to hide your formula from others and still keep the worksheet usable, by performing the following steps:
- Select all cells (either press Ctrl+A or click on the small upper left square on the edge of the worksheet)
- Right click the mouse and select Format Cells …
- In the popup menu that opens, click on the Protection tab (it should be the last one on the menu)
- Deselect the small box tagged Locked
- Click on OK. Now all your cells can be overwritten, regardless the protection status of the worksheet.
- Select only the cells containing formulas that you want to hide (If you need to perform multiple selection, you can press and hold down the Ctrl key, while clicking on each cell you want to select)
- Right click the mouse and select Format Cells …
- In the popup menu that opens, click on the Protection tab
- Tick the small box tagged Hidden and the Locked box as well. If you don’t tick the Locked box, other users of your worksheet would be able to overwrite the formula cells, without even knowing that they contain formulas (as they become invisible following this operation).
- Click on OK
- Go to the Tab Review and then click on Changes … Protect Sheet
- If you want, you can input a password for unlocking the worksheet. This will prevent others from unlocking it. If you don’t want to do that, leave the password field blank and press OK.
- Now click on one of your cells containing formulas and look at the formula bar. It should be empty, although the formula is still there. The cell would remain locked, but it would be automatically updated when changing the content of its precedents relating to the contained formula.
This is an Excel 2007 update of the tip here: http://www.alltipsandtricks.com/how-to-hide-formulas-in-ms-excel/
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
Custom formatting in Excel is extremely powerful and, well, much more useful than you might think.
In a session I am going to present tomorrow on the beauties of my Standard Costing Variance calculation method, I am going to reveal to my delegates the following cell formatting rule:
<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
THAT is worth a fortune in the right hands: let’s call it an early Christmas present!
A standard costing variance can be positive, negative or zero. If it’s positive, we might want to label it Favourable, if it’s negative we could want to label it Adverse and if it’s zero, we want to leave it at zero with no label.
One approach is to program each cell where there is to be a variance calculation something like this:
=IF(G34-G38<0,ROUND(G34-G38,2)&"Adverse",IF(G34-G38>0,ROUND(G34-G38,2)&"Favourable",0))
This formula takes care of the positive, negative and zero requirements and it takes care of the rounding to two decimal places in case there are more than two!
ALTERNATIVELY, why not learn how to do this:
enter only your calculation in the variance cell(s) =G38-G34 AND then use custom formatting of the cell(s) as follows:
#,##0.00 "Favourable";-#,##0.00 "Adverse";#,## 0.00
TRY IT! RIGHT CLICK SELECT all necessary variance cells at the same time and set their custom formatting together … don’t waste any time by doing it cell by cell.
This works because every cell in an Excel spreadsheet is set up to accept formatting along the lines I introduced above;
<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
Which means that the first part of my formatting expression relates to ALL positive results, the second part to ANY negative results and the third part to zero values. I haven’t entered any text options because there can’t be any … unless your solution needs them.
This is a real top tip.
Duncan Williamson
