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
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
Gantt Charts in Excel
03/01/2011
A Gantt Chart looks like this:
And lots of people use them and/or need to use them. The question is, how on earth do you prepare them in Excel. After all there isn’t a Gantt chart option in there. It’s true, there is no Gantt chart option in Excel. It is possible to draw a Gantt chart in Excel though because that’s where that chart above came from!
Here are the data on which the above Gantt chart is based:
| task | start date | duration | end date |
| planning meeting | 29/12/2010 | 1 | 29/12/2010 |
| develop questionnaire | 30/12/2010 | 11 | 09/01/2011 |
| print and mail questionnaire | 13/01/2011 | 9 | 21/01/2011 |
| receive responses | 16/01/2011 | 15 | 30/01/2011 |
| data entry | 16/01/2011 | 18 | 02/02/2011 |
| data analysis | 03/02/2011 | 4 | 06/02/2011 |
| write report | 09/02/2011 | 12 | 20/02/2011 |
| distribute draft report | 23/02/2011 | 1 | 23/02/2011 |
| solicit comments | 24/02/2011 | 4 | 27/02/2011 |
| finalise report | 02/03/2011 | 5 | 06/03/2011 |
| distribute to board | 09/03/2011 | 1 | 09/03/2011 |
| board meeting | 17/03/2011 | 1 | 17/03/2011 |
A series of tasks with a start date, an end date and a duration: typical data for a Gantt chart. Please note, the end date column and data are not needed to draw the Gantt chart, they are there for information and confirmation
Please copy and paste that table into a worksheet with the heading task in cell A5. Now work your way down the following instructions that relate to Excel 2007 (and 2010):
How to Draw a Gantt Chart
1 Enter the data as shown in Table 1. The formula in cell D6 (end date), which was copied to the rows below it, is =B6+C6-1 the end date column is NOT used in the chart but is for information to support it
2 To create a chart select the rangeA6:C17 then click Insert and create a stacked bar chart … use the second subtype, which is labelled Stacked Bar.
3 Notice that Excel incorrectly uses the first two columns as the Category axis labels.
4 Right click the chart and click on Select Data to open the chart wizard. Now, set the chart’s series to the following:
- Series 1: B6:B17
- Series 2: C6:C17
- Category (x) axis labels: A6:A17
Click OK to leave the chart wizard to create an embedded chart.
5 Delete the legend
6 Create or amend the title and add the horizontal axis label … this is no real need for the vertical axis label but feel free to add Task if you wish.
7 Adjust the horizontal axis Minimum and Maximum scale values to correspond to the earliest and latest dates in the data (note that you can enter a date into the Minimum or Maximum edit box). You might also want to change the date format for the axis labels.
8 Right click the vertical axis and select the Number sub menu: select Format Axis dialog box for the vertical axis. In the Axis options select the option labelled Categories in reverse order and also select the option labelled Horizontal axis crosses at maximum category.
9 Right click the first data series and select Format Data Series. In the Fill section, set fill to No fill and Border Colour to no line. This makes the first data series invisible and is the key to this chart.
10 Apply other formatting, as desired.
That’s it! Read this information once, twice, as many times as you need. Then enter your own data, adjust the formulae and change the data series in the chart, as you need.
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
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
A member of a discussion list asked a question earlier today and I answered it … thought you’d like to see the question and the answer!
In summary, he wants to limit column A to a width of 8 characters and column B to 25 characters in width. How to do that?
Data Validation is probably what you want S. In Excel 2007 you can do the following
- Select the entire column A
- Data … Data Tools … Data Validation: click on the down arrow
- Select Data Validation
- Allow … Text Length
- Data … less than or equal to
- Maximum … 8
- OK
Now try to enter any combination of characters greater than 8 characters in length …
Repeat for column B with the maximum set to 25
Duncan Williamson
Functions and Formulas II
21/10/2009
I gave a long list of basic functions and formulas that Excel 2007 contains that I said I will be using to start off an in house course I am about to run. Well, I have now prepared examples that illustrate how all of them are to be used and how they work.
Watch the site, excelmaster.co.uk, for more on this as I will prepare a page and possibly a video to illustrate some of them … not all of them on the site but they will all be in the book, Excel 2007 with Excel Master.
Duncan Williamson
Functions and Formulas
16/10/2009
The other day I told you about the in house course I will be running next month and here is a list of some of the functions I will be starting the seminar with: as many as possible in the first session. Of course, since the delegates should not be beginners, some of these will be for information only: to act as a flag that tells them we will be using them at some stage throughout the three days.
Financial Functions
- RATE(NPER,PMT PV,FV,TYPE,GUESS)
- NPER(RATE,PMT,PV,FV,TYPE)
- PV(RATE,NPER,PMT,FV,TYPE)
- FV(RATE,NPER,PMT,PV,TYPE)
Mathematical and Statistical Functions
Mathematical
- ABS(NUMBER)
- COMBIN(NUMBER,NUMBER_CHOSEN)
- FACT(NUMBER)
- INT(NUMBER)
- POWER(NUMBER.POWER)
- PRODUCT(NUMBER1,NUMBER2 …)
- ROUND(NUMBER,NUMBER OF DIGITS)
- ROUNDDOWN(NUMBER,NUMBER OF DIGITS)
- ROUNDUP(NUMBER,NUMBER OF DIGITS)
- SUMIF(RANGE,CRITERIA,SUM RANGE)
- SUMIFS(SUM_RANGE,CRITERIA_RANGE …)
- SUMPRODUCT(ARRAY1,ARRAY2 …)
Statistical
- AVERAGE(NUMBER1,NUMBER2 …)
- AVERAGEA(VALUE1,VALUE2…)
- AVERAGEIF(RANGE,CRITERIA,AVERAGE_RANGE)
- AVERAGEIFS(AVERAGE_RANGE,CRITERIA_RANGE,CRITERIA …)
- CORREL(ARRAY1,ARRAY2 …)
- COUNT(VALUE1,VALUE2 …)
- COUNTA(VALUE1,VALUE2 …)
- COUNTBLANK(RANGE)
- COUNTIF(RANGE,CRITERIA)
- COUNTIFS(CRITERIA_RANGE,CRITERIA …)
- FREQUENCY(DATA_ARRAY,BINS_ARRAY)
- GEOMEAN(NUMBER1,NUMBER2 …)
- HARMEAN(NUMBER1,NUMBER2 …)
- INTERCEPT(KNOWN_Y’S,KNOWN_X’S)
- LARGE(ARRAY,K)
- MAX(NUMBER1,NUMBER2 …)
- MAXA(VALUE1,VALUE2 …)
- MEDIAN(NUMBER1,NUMBER2 …)
- MIN(NUMBER1,NUMBER2 ..)
- MINA(VALUE1,VALUE2 …)
- MODE(NUMBER1,NUMBER2 …)
- PERMUT(NUMBER,NUMBER_CHOSEN)
- RANK(NUMBER,REF,ORDER)
- SMALL(ARRAY,K)
- TDEV(NUMBER1,NUMBER2 …)
Logic
- AND(LOGICAL1,LOGICAL2 …)
- IF(LOGICAL_TEST,IF_TRUE,IF_FALSE)
- IFERROR(VALUE,VALUE_IF_ERROR)
- NOT(LOGICAL)
- OR(LOGICAL1,LOGICAL2 …)
Text
- CHAR(NUMBER)
- CONCATENATE(TEXT1,TEXT2 …)
- FIND(FIND_TEXT,WITHING_TEXT,START_NUMBER)
- LEFT(TEXT,NUMBER_CHARACTERS)
- MID(TEXT,START_NUMBER,NUMBER_CHARACTERS)
- PROPER(TEXT)
- REPT(TEXT,NUMBER_OF_TIMES)
- RIGHT(TEXT,NUMBER_OF_CHARACTERS)
- SEARCH(FIND_TEXT,WITHIN_TEXT,START_NUMBER)
- TEXT(VALUE,FORMAT_TEXT)
Here’s a bit of fun for you, too: using three or more of the above TEXT functions, devise a formula that will create a unique password for, for example, your family, your friends, your colleagues, your football team members … Once you have prepared your formula, you need to evaluate
- the security of the system
- how uniquely it assigns a password
- how efficient the system is
Duncan Williamson
Up and Coming Seminar
13/10/2009
In November I am running an in house three day seminar entitled Mastering Business & Financial Modelling Using Excel®
The topics, in brief, are as follows:
MODULE ONE
· A review of a variety of functions vital for successful modelling:
· Financial functions
· Mathematical and statistical functions
· Logic and text functions
· Using the Analysis Toolpak AddIn
· Analysing a database of financial information
· Using a financial analysis template
· Charting
MODULE TWO
· Formulas
· Forms and Dialogue Boxes in Spreadsheets
· Forms
· Dialogue Boxes
MODULE THREE
· Pivot Tables
· Budgeting with pivot tables, for example:
· SOLVER and Forecasting
MODULE FOUR
· Financial model building
· Example models:
· Bankruptcy prediction
· Profit prediction
· Asset allocation
· Cost minimisation
MODULE FIVE
· Designing and carrying out a Monte Carlo simulation
· A Consolidating Case Exercise
I’ll let you know how it goes.
Duncan Williamson
