This Blog contains an ever growing set of detailed pages on How to … do things and pages of simple but very useful Tips.

13th September 2019: a fantastic page on corporate bonds and their analysis. How to program a table using dates, the IF(), AND(), MONTH(), YEAR() functions and arithmetical calculations. If you find difficulty using dates, consider this page.

11th September 2019: a friendly introduction to the setting up and use of a Training/Testing Model in Excel. It is an introduction but it is a good one. The page does not, however, carry out any detailed analysis of the validity of the model you will read about. The data are real and you can replicate what I have done very easily. The page is here.

22nd August 2019: As you all know, this site is free of charge and always will be. Every now and again, I announce something like the publication of one of my books. Sometimes my books are not free of charge because they are professionally published. Whilst that is true of my latest book, it IS free of charge, at least for a while. The title of the book is EXCEL POWER QUERY, AN INTRODUCTION: BUSINESS INTELLIGENCE PART I.Click on the title to go to the site where you can download it free of charge.

1st August 2019: I have already created two pages (Flash Fill, Flash Fill and Quick Analysis) on Flash Fill, both for Excel and for Power Query and here is another one. In this case, I am introducing even more ideas on how Flash Fill (FF) can be used very effectively. Firstly, by using FF to extract, combine, extract and combine; and secondly, I watched a video of a highly respectable Excel practitioner who tried to sell us the idea that there are times when FF is just not good enough and neither is Power Query: in that case, he said, use a formula … he demonstrated a relatively complex formula. The formula worked but I am showing here that that presenter is wrong and that FF will do exactly what we want without programming anything.

14th July 2019: A different approach but the same quality. I have just posted my latest two videos to YouTube. Video 1 is about Flash Fill and Quick Analysis in Excel. Video 2 is about Flash Fill in Power Query and Quick Analysis in Excel. The aim is to illustrate how it is possible, even with dirty data, to create a basic dashboard within 10 minutes! Video 1Video 2

19th June 2019: BOOKS … Since I was last here, I have completed writing two books on Excel and I will announce publication details here when I have them. The first book is the fourth in my Excel Solutions for Accountants series and the second book is the first in the series on Power Query, Power Pivot, Power BI. These books are the only things I talk about here that are not free of charge.

CASES … in addition to books, I create case studies and for courses I am running at the moment, I am sharing four such cases with you, in a PDF file. There are two cases on financial analysis, one case on budgeting for buying and running a private jet and a case on exchange rates. Look at the page to see the outline of each case and what you need to know about Excel to work through them.

5th May 2019: If you are serious about learning VLOOKUP in the Power Query and Power Pivot environments, look no further. I have created two videos, one of PQ and one for PP to help you. Just go to this page and take a look at my detailed explanations.

20th March 2019: One file, two videos. This page outlines what I did after downloading some World Health Organisation data on DTP3 vaccinations (Diphtheria, Tetanus and Pertussis), covering the period 1980 to 2017. From a health point of view, what you see here is fantastic. From an Excel point of view, what you see is intermediate level but there are enough features illustrated to make it well worth your while to take a look at it! The page is here

6th March 2019: This page ends with an Excel file but it is really a discussion of how I created some Power BI files that I then used to create the Excel files by copying M Language code from one to another. What I describe here is magical and I have used the departures and arrivals information for both of Bangkok’s two international airports as the basis of my work. The page you want is here and there are two bare bones Excel files to download … Word Press will not allow me to share Power BI files, I am sorry to say.

15th February 2019: Let’s watch a video! I have created a video showing you how to use Data Types Stocks to pull in share prices, Beta values, market capitalisations of potentially thousands of different companies from around the world. There is a video to watch and a spreadsheet to download. The page is here: Data Types … Stocks

7th February 2019: I know you have read my pages on Sparklines and therefore you know how clever they can be and how you can format the cells they are in, you can add text/formulas to the cell they are in. Well, now, you are about to learn to use Conditional Formatting Icons in them. As far as I know, you can find this technique nowhere else on the web! Here is the link to the page Sparklines with ICONS

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/

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

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

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:

sia_blog_1

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:

sia_blog_2

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:

sia_blog_3

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:

sia_blog_4

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

 

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.

main-qimg-897d5be6ae0d3e466b4ee0095f16d1ab-c?convert_to_webp=true

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 hope you can see this photo.

DW

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

 

%d bloggers like this: