- Absolute solution to Accountants who have not used the excel experience in doing calculation and graphs on financial data analysis
- The book is really fantastic, a must read for professional accountants.
Welcome to this Blog: Excel with ExcelMaster
21 04 2017
This Blog contains an ever growing set of detailed pages on How to … do things and pages of simple but very useful Tips.
9th January 2019: This is a fabulous page … not because I am so brilliant but because I have just learned how to combine array constants with a VLOOKUP function. It has been around for years but it just filtered down to me. You can thank me later! Here is the page you need: https://excelmaster.co/vlookup-with-array-constants/
8th January 2019: Happy New Year Everyone. Secondly, the purpose of this update is to share with you my solution to creating a histogram for any one of àbout 90 possible line items in a ratio analysis worksheet. The data relate to Netflix and comprise a ten year view of the ratios that come from the morningstar.com web site. The file also includes my Positive Skewness Test that I have recently started to program and use. There is a file to download too and if you find it useful, please tell me how you are using it. the file is here: https://excelmaster.co/one-histogram-90-choices/
22nd December 2018: I just posted a brief article on THIS PAGE, click the following link or scroll down from here to read it, called, FUNCTIONS ,,, top 100. It comes from the Harvard Business Review and is well worth reading and following up on. They might be behind a paywall but you can probably take a trial and see the articles if nothing else.
15th December 2018: A Power BI exercise for you, relating to the blood pressure history of someone. The data are contained in a CSV file you and you have to complete the PBI file yourself: security settings won’t allow me to upload my PBI file. I describe in detail the tables and graphs and so on that you can prepare. The page is here: https://excelmaster.co/bp-pbi/
6th December 2018: Today is a sort of a magical day in which I combine Power Query and an array formula. The reason for this magic is so that I can build a Query that eliminates null values from an input, Excel, table and then allows us to find median values by days of the week. Sounds complicated, doesn’t it but no, it’s not that bad, especially when I have spelled it all out for you and given you my Excel file to download. Here is the page you need: Medians with Zero … https://excelmaster.co/medians-with-zero/
14th November 2018: Here is a page that shows you how to compare two lists of employees and deduce who was employed in both lists, who has left from period 1 to period and who has joined in period 2. This problem flattens some people but this solution sues conditional formatting to show the solution is a breeze. Here is the page https://excelmaster.co/who-is-who/ …
Page two for today: Tax Computation … how to programme Excel to find your tax payment when there are incremental steps and not just tax bands! There are two solutions to consider as well as a tax schedule that makes it all look so simple! https://excelmaster.co/tax-computation/
2nd November 2018: you will be delighted by this post, in which I use a Radar Chart to illustrate some of the rings in a 990 year old tree. Using a radar chart is innovative and it gives a very easy to read and maintain graph. The page is here and there is a file for you to download that includes the data set as well as my radar chart.
25th October, 2018: If forecasting is important to you, take a look at this new page showing how to use all of the many FORECAST functions now being offered by Excel. For Office365 Excel users, I have also used the Forecast Sheet Utility and that is included here too. The page is here and there is a file for you to download too.
24th October 2018: BIG VIDEO day today. I have prepared a two video set that shows how to download data from a flight tracker web site by using Excel’s Power Query/Get & Transform. They also show how to duplicate a Query and then Append the two queries there now are. I then take the Queries from Excel to Power BI for the Desktop to create visualisations and maps from there. You can do all of this in Excel but the map was no good for me. You can do all of that in Power BI but it kept giving me niggling problems as I did it. So, bonus: you get to see how I work with both Excel and Power BI. There is NO fie to share as you need to build your own directly from the web site but I do give you the URL I started with … the page with the video file links is here …
17th October 2018: This time I am giving you an exercise. As you know by now I am a data sink and a couple of weeks ago I spent some time putting together a file of prices and details of some second hand Aston Martin cars: 189 of them to be precise. All of the cars are located in the UK and all prices are in GBP. Just go to the page and work your way through the exercise: I have really designed it as a Power BI exercise but you can also work on it as an Excel Power Query exercise. I do give you clues of what to do but, still, you will need some skills to finish the task. I will not provide a solution unless you offer me one first! Then I will share my Power BI file with you.
24th August 2018: Surprise! Here’s a Power BI file to think about: it is fandabidozy. I tracked an Emirates Airlines flight online last night and I tracked it using Power BI. Word Press won’t let me upload my file here but ask me for it in a comment. Go to this page to see some of what I did! EK377 tracking file
14th August 2018: A superb result from my analysis of Singapore Airlines. You need to read this page to understand my excitement over what I have found: it really is fascinating! The page is here and the spreadsheet file to download only contains the data since I want you to replicate what I did by yourself.
24th July 2018: Combinations can be tricky things to work with so I answered another question on quora.com for Kristy and here is the result: As I say in this post, the combinations follow a fairly basic pattern. Download my file and you should be able to find the pattern in my introductory tab and especially in my fully worked tab. Three numbers in five cells, a total of 243 combinations … find them!
9th July 2018: I return to Benford’s Law over and over and this time it was to investigate a known fraud to determine whether Benford would have caught Bernie Madoff, that fraudster of all fraudsters. Read this page to find out!
22nd June 2018: the leap year effect came as a bit of a surprise to me but it was easy to spot and it’s easy to deal with. Take a look at this page, that contains a video and an Excel file to download to see what I am talking about.
13th June 2018: You want to split a column but still have the data in your new columns able to talk to the input columns? Here are two ways of doing that: of the two solutions, the one that uses Power Query is by far the better but the other solution works pretty well too. Here is the page to go to.
1st June 2018: Two new pages today: talk to me and a challenge for you: try this case
14th April 2018: Did you know you can do all of your regression work using SOLVER? You are in for a treat then because it is possible. There are a couple of warnings to take into account, of course; but in principle, it works. Go here to read the page and download my Excel file too.
6th April 2018: New tax year, new section! I have decided to create a new section called Power BI and I have already prepared and uploaded my first article to it. Quick Insights is coming to Excel 2019 but it is ALREADY built into Power BI. Read all about it here, in the Power BI section
23rd March: 2018: There is a question on quora.com where someone asks, In Excel, how do I add the numeric vale of one cell to another cell containing multiple texts? Three people say it cannot be done. I have answered the question and will post my answer here soon. Try it yourself and tell me what you think!
22nd March 2018: I just read a solution to a problem that I think gives a ludicrous answer: {=INDEX($C$2:$C$11,SMALL(IF(($B$15=$B$2:$B$11)($A$2:$A$11<=$B$14)($A$2:$A$11>=$B$13),MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)),””),ROW(A1)))}. Take a look here to see my two suggestions for a MUCH easier way of solving the problem they discuss.
28th February 2018: Do you ever filter your Pivot Tables to show the Top 10 (or even Bottom 10) iteems? Does that filter disappear when you use a slicer to take another view of your table? A delegate showed me her problem with this and the video on this page shows the problem and the solution: watch the video
23rd February 2018: I think you know that I am using Power BI for the Desktop to a limited extent. Here is the link to one of my Power BI reports … I am more interested in the content than the way they look so forgive my complete lack of artistic ability: here is the link to https://app.powerbi.com/view?r=eyJrIjoiZjhhOGFkMTYtMzA1Ny00NTRhLThhY2ItMGM1YWYyMDBlNWQ4IiwidCI6IjZjNGIzMDYxLTRiNmItNGUwNy05Y2JmLTYwMjE2YTQ5YWNmMCIsImMiOjh9” target=”_blank” rel=”noopener”>take a look … by the way the data are entirely artificial
23rd February 2018: The DATEDIF() function is very unusual: it is unlisted in the list of all built in functions and it is largely undocumented. However, take a look at my DATEDIF() page to see how useful it is i finding ages from dates without the pain of other methods.
31st January 2018: Microsoft Excel has just been updated and they added some really nice things to Get & Transform (Power Query). I have created four videos and an Excel file for you and here they are. will discuss them in detail when I update this page and blog but in the meantime, consider this:
As a taster … from another of my Get & Transform files, I entered this in a worksheet:
And by using G&T, I got all of this … and I could have had even more, by including a Pivot Chart or two, for example:
I went from a city name with a value for a city to a table that included the city, the country, the value, class intervals, comment on positive, negative or zero and then I used that to create three pivot tables … could have been more.
NOTE: not a VLOOKUP in sight, no INDEX and MATCH combination to be seen. No jiggery pokery of any kind. If you’re not excited by this, you never will be an Excel maestro!
12th January 2018: Happy new year first of all. Look at this page on Nobel Prizes … as connected to my home town of Todmorden in West Yorkshire, England. It’s fascinating and there is a PDF and an Excel file to download. My best friend from childhood was born on 12th January and he died as a result of an almost freak road traffic accident a few years ago, so hail John Sutcliffe.
29th December 2017: Another page on Benford’s Law as I am discussing the law with an email friend. This time: its it possible to fool Benford’s Law? Go here to find out or the Other tips menu!
27th December 2017: for Excel fans as well as for football fans and followers of the English Premier League (EPL). I have created an alternative league table that adjusts points for squad values, foreigners per squad and fair play league positions. This file is rich in Excel functionality so look at it from a football point of view AND an Excel point of view. The Excel file is free to download. The page is here.
17th December 2017: UPDATE I have updated my Sunday Times Rich List Benford’s Law Analysis page because I have added to the data I have analysed there. Take a look!
6th December 2017: STOP USING EXCEL … boring, heard it all before! What was it you were trying to sell me? Tatsuo Horiuchi Excel artist. Two cracking discussions in one short page. Excel Tips … Other menu!
21st October 2017: There is a simple page, BP Graphs, in the charting tips menu that asks you to express a preference for one kind of graph over another and for you to suggest how I created those graphs! Take a look!
14th October 2017: I have updated the DGET page following a couple of emails trying to tell me how good it is to use INDEX-MATCH …
6th October 2017: A video … with music … Contingency Tables in Excel. Excel file to come in a week or so. Anyway, take a look here Menu: Excel tips … other tips …
18th September 2017: I got involved in writing several articles on http://www.quora.com over the weekend and here they are: Pie Chart sorted: Excel Tips, Charting Tips menu), log linear regression analysis (Excel How to, Excel Functions menu) and AVERAGEIF question that became a Get & Transform (Power Query) question (Excel How to, Excel Functions menu).
28th August 2017: I have always called in Power Query up to now but it’s really called Get & Transform now. Take a look at this fantastic page I have just created in which I show you how to take a list with 39,000 rows that need to be split out column by column without using any VLOOKUP or INDEX and MATCH … purely using Get & Transform. Menu Excel How to …
18th August 2017: unlinking two or more Pivot Tables from the same cache. This page shows you how to use one database as the source of two or more pivot tables from which you then create two or more pivot charts. Not intuitive and I show you two methods and mention two more. How to menu, Pivot Tables …
9th August 2017: Here’s a very simple task but it is worth a fortune to the delegate I used it with! In my course in Riyadh this week I had a delegate who had an apparently very simple problem. Smart man but he just couldn’t find the answer. Look at my TEXTJOIN page to see the problem and my solution. You’re welcome!
24th June 2017: Danielle Stein Fairhurst published an interesting list of favourite Excel functions as nominated by 20 Excel MVPs. This page shows you what I had to do to get this list into Excel, to separate out and count all unique instances of the functions listed and then how I used Pivot Tables to finalise my work. Pivot Tables How to Menu. You can download my Excel file, too.
15th June 2017: how about the English Premier League Fixture List for 2017 – 2018 that uses an Excel Table, Power Query, Pivot Table and Slicers and yet is very easy to use? Here it is: Pivot Tables How to … menu.
13th June 2017: Video Time. Every now and again I make a video and today is a day like that. Go to this page, Video Time!, to see a sort of a dashboard by video I have created using data from opinion polls in the days leading up to last week’s ridiculus British general election. The video is there and the spreadsheet can be downloaded.
3rd June 2014: 1 Next week I am presenting a two day course on Financial Modelling and Business Intelligence in Singapore. Whilst it’s not too late to sign up, look here for one of the cases I will be including. There are no solutions presented here except what you see in the case itself: if you want my solution, either come on the course or send me your honest attempt at using Power Query, Power Pivot as well as Power BI for Desktop and we can talk. Talk to me initially by commenting on the page.
2 YDAYDG …You don’t ask, you don’t get. I don’t program in VBA to any significant extent but I do include it in my courses from time to time. Following a a query from a previous delegate, I have put together a free pack on starting Macro/VBA programming and it’s yours … but YDAYDG … call me! Talk to me initially by commenting on the page.
3 Benford’s Law Update I have updated my 2013 page on the Sunday Times Rich List analysed according to Benford’s Law. Again, not what we should expect but what to make of it? Not at all sure. Here is the link
14th May 2017: POWER QUERY example … you don’t have to be rich to use some rich features in Excel! The UK’s Sunday Times published its 2017 Rich List last week and I have used Power Query to take the basic data that comes with the list, included the sub categories, put all of that into an Append query and then started to analyse everything using Pivot Tables. There is no page for this because I am under pressure from work at the moment. That means, if you know Power Query already, you can take the files and see my query and add to them, change them and so on. If you don’t know Power Query yet, you can take the files as a working example of something very useful that you can learn to use over time. Since I am having a membership clearout, you need to ask for the two files you need for this exercise but do please note I can’t offer any support on the files for a couple of weeks or so. Send me an email to duncanwil … at … gmail … com and I will send the two files you need within 24 hours or so. In your email you need to include a subject or message that tells me what you want! I have had completely blank messages in the past that I just delete because I am not psychic!!!
7th May 2017: FIVE ways to create a histogram: from the highly specific to the rough and ready. There are six methods, by the way, but number six is left out! Excel Tips … Charting menu or click here to go straight there.
21st April 2017: My Excel Solutions for Accountants books: I have just completed some updating work on this three volume set and this is what my readers have said about them:
The books are available from my page here: www.bookboon.com
21st April 2017: A page on Data Analysis. I have taken data on the human body among other things and analysed them as worked examples. There are four cases to study and few all of them you get the data only excel file, the fully worked Excel file and a PDF with my notes and explanations. There is a further six cases that I have worked through and that you will find in both Excel files but there is no analysis in the PDF file: these are for you to complete. I have put this in the Excel How to… Other … Functions menu but you can go straight to it from here.
6th April 2017: A really useful insight this time: Pivot Charts from Power Pivot even Without Pivot Tables. Take a look at this page and download the PDF file that comes with it. Sorry but I cannot share my Excel file with this one. Excel How to … BI sub menu.
3rd April 2017: This is something new, using Microsoft Sway to format a file for me. I know what it looks like in the software itself but what happens when you go here … EPL Analysis and click the Sway page link … at this stage I don’t know. My analysis is good so let’s see what Sway an do! This page is in the How to … menus too!
2nd April 2017: Rice … some very interesting and useful data gets the Power Query Unpivoting treatment. I have discussed upivoting data before but here is a very practical example that uses international data on rice. You can go straight to the page I have created or go to the Excel How to … BI sub menu and look for Unpivoted rice
If you’d like my help and guidance on something, just ask and I’ll do what I can.
Duncan Williamson
Introduction for Absolute Beginners: Introduction to Excel for Absolute Beginners
Excel Files for Practice:
The basics of spreadsheeting 1
The basics of spreadsheeting 2
The basics of spreadsheeting 3
The basics of spreadsheeting 4
FUNCTIONS … top 100
22 12 2018
I just came across a couple of pages on hbr.org that you need to know about and forgive me if these are behind a paywall: I subscribe to the HBR so I went straight to every page.
Firstly, 10 Excel Functions Everyone Should Know by Adam Lacey and Deborah Ashby. I prefer DGET to their INDEX-MATCH function but otherwise, this is a good start to learning the top 10. Top 10 Excel Functions
Then there is a REALLY useful PDF file The Definitive 100 Most Useful Excel Tips at https://excelwithbusiness.com/blog/definitive-100-most-useful-excel-tips/
Finally and following on from the 100 must useful Excel tips, Marc Zao-Zanders gives us this, A 2×2 Matrix to Help You to Prioritise the Skills to Learn Right Now, here: https://hbr.org/2017/09/a-2×2-matrix-to-help-you-prioritize-the-skills-to-learn-right-now This 2×2 matrix puts the 100 tips on a grid/graph to illustrate four quadrants:
- Decide whether you need to learn it
- Learn it as the chance arises
- Schedule a block of time for learning it
- Learn it right away
Marc says this, “You’ll find the quickest wins in the bottom-right quadrant, which we’ve labeled “Learn it right away.” In here we have time-saving shortcuts that can be applied frequently, like Ctrl-Y (redo) and F2 (edit cell) and a nice combination formula that cleanses your spreadsheet of errors (IF(ISERROR)).” Of course, IF(ISERROR) is way out of date and should be replced by IFERROR()
Duncan Williamson
22nd December 2018
Singapore Airlines Superb Result
14 08 2018
Introduction
If you have stumbled across this page hoping to find a cheap Business Class seat on Singapore Airlines (SIA), sorry! What you have arrived at is a page that explains how I found a superb result to my analysis of the last 19 years of SIA’s financial results.
The Data
As I was using Power Query to help me to analyse SIA’s financial results, I downloaded their last 19 years’ worth of Annual Reports and Accounts and from there I downloaded everything you see here:
The question is, what can I do with all of that? What I often do as part of my financial analysis is to create a Net Income model. That is, I make Net Income the dependent variable and have one or more independent variables.
The First Model
My first model comprises Y = Net Income = All Other Variables in the above table, all six of them. I used the Data Analysis ToolPak for this and here is my output:
A Correlation Matrix of all variables and I have conditionally formatted the results to highlight the extreme values and the mid range values. For example, Revenue and Fuel Costs are very highly correlated as are Passengers and Rev Pax Km (Revenue Passenger Kilometers). Rather oddly, there is virtually no correlation between Staff Costs and the Number of Employees.
The Regression output are as follows:
Let’s note that the Adjusted R Squared value is high at 0.6755, the F statistic is significant at 7.25 but of the six independent variables only Staff Costs are significant, with a t statistic of 3.3287 and a P Value of 0.0060.
I think this is a superb result: Staff Costs being the only significant variable as its coefficient shows that a unit change to Staff Costs leads to a 2.5913 million SGD increase in Net Income.
The residuals plots show that everything is probably fine although we are only dealing with 19 data points or years of data:
Conclusion
On the one hand, this model fails because only one variable is significant and using just Staff Costs to predict Net Income is not rational. This says that we need to refine or replace the model to find something better. That being true, why do I think I found something superb?
SIA is famous for its recruitment of and investment in high quality cabin crew and other members of staff. Whilst they don’t pay the highest salaries, they do reward well overall, training is high level and frequent, they use more cabin crew per flight than most, if not all, of its competitors to maximise customer satisfaction.
Out of all of that, the data contain the relationships that I just mentioned and regression analysis has brought them out! That is superb in my opinion!
I want you to replicate my work here so, whilst there is a spreadsheet to download, it only contains the data I have extracted and used. Feel free to ask for advice and guidance here but I am not providing the full spreadsheet file.
Download the file of data from here sia_data_blog
Duncan Williamson
14th August 2018
Multi worksheet scenario model
25 10 2016
Tuesday 25th October
This is on its way … as promised … I will upload it later today or early tomorrow.
Duncan Williamson
Someone asked this question in Quora and here is my answer which I think many of you will find useful:
If you use Excel on a Mac the chances are that you are not running Excel 2016 for the Mac and that your Mac does not have the ToolPak at all … I know, older versions have it and I know you can get alternatives!
In that case, I often demonstrate to Mac users how to create and automate the functions in the ToolPak: correlation matrix, regression analysis, moving averages, descriptive statistics … the others as well!
Descriptive statistics, for example, could be, for data in column A:
=AVERAGE(A:A)
=STDEV(A:A)
=KURT(A:A) …
=SKEW(A:A)
and so on.
Other answers have mentioned statistics software packages and that’s fine except they might not be free! Yes, if you are a student, your college or university is likely to have statistics software free for you to use.
How about R and R Studio, however? Open source, free, with massive amounts of support? Of course, it takes time to learn R but here is the code for some descriptive statistics using the psych package in R:
describe(order_sales_profit$Sales)
That’s it! This is what I get from my current data set, sales values: not exactly the same as the ToolPak but my point is, it is very easy to replicate. Look at the screenshot of the output from R.
By the way, as a novice or beginner level user of Excel, there is a lot to learn from manually automating what’s in the ToolPak. Moreover, if you take my next learning point, use this opportunity to set up templates for you to analyse your data sets: that means, you automate the ToolPak elements once and that is it!
Finally, many elements of the ToolPak return non volatile results which means that if you change your data, you have to run the ToolPak again. If you automate it yourself, the formulas you create will all be volatile: change the data, change the answers!
Duncan Williamson
I like such photographs
09 09 2016
I hope you can see this photo.
DW
Top Tips: rules you really should follow
20 08 2016
19th August 2016
I have just completed another very successful Financial Modelling course and as you know, at the end of such courses, I come here and offer something new: a new topic, a new file or some advice. In this case, it is advice: things that you really need to think about when you create and work on any Excel file.
- Tab/Sheet Names
- Links
- Dead Cells 125,433 rows created but only 831 needed/active … files that balloon to many Mb for no real reason
Ever seen a tab name like this: FBU or OPT? I bet you have: short and sweet and probably mnemonic so easy to read and remember. How about L_P_Obasange_receivables_dont_forget_to PRINT_it_out? You think I am joking? I am serious! Just imagine you are working on your file with the large tab name and you want to link to a cell on that tab from another one: this is what will appear in your formula, by way of an example … =IFERROR(AND(A15=45,D26=”Jack”,L_P_Obasange_receivables_dont_forget_to PRINT_it_out!BA154 …
I am sure you see the point now. Keep tab names short and simple! More than that, if you do feel the need to use tabs to give instructions, colour code them to pass such messages: there are many colours to choose from so do that. Have a table of contents too. Give everyone a chance for a simple life!
Links
If you share a file with someone, make sure any links in your file are either live or delete them. If you receive a file with links that you cannot use or update, you know how frustrating it is. Think of the user before you send linked files.
Dead Cells
It is the easiest thing in the world to create a worksheet and as you work and improve what you are doing, to delete cells and ranges. We all do that. We create new ranges too, don’t we! Check your work now and again though and if these happen, take a break and check your file:
- it takes 30 seconds 45 seconds or even longer for the file to open
- what seems like a small file in terms of content and complexity has ballooned in size to 20 or 30 or more Mb
- saving the file takes an age too
If these things happen, go to a worksheet and press Ctrl+End and see where that takes you. You work only in the range A1: CD831 but Ctrl+End has taken you to CG125433 … what? How did that happen?
Even if there are not as many as an additional 1.8 million cells but just 500,000, look in those cells for formulas that are trying to find something from somewhere that is not there … in some of these extra cells for example. Delete all of these extra cells. I did that this week: an extra 1.8 million cells in TWO separate worksheets complete with formulas. File size down from 28 Mb to 0.8 Mb, opening time just seconds, recalculation time hardy noticeable.
They were just some of things to report on from this week. Otherwise, this group of delegates really enjoyed the work and their end of course presentations were interesting and showed that significant learning had taken place!
Duncan Williamson
