Excel

Beginner’s Financial Modeling

Management Consultants can come from many walks of life and have a background of a variety of subjects. The recent History graduate about to start at McKinsey is therefore unlikely to have come across Financial Modeling. This is a skill (using Excel of course) that is vital in the consulting industry to complete the research into the companies you have been hired to help. Financial models allow you to analyse large sets of data will help you work out what is and is not working in the business. From this you can therefore spot trends and alter the variables for a more favourable outcome – and provide the perfect financial solutions for your clients.

Financial modeling in Excel involves using VBA and so is considered an advanced Excel skill. It is therefore assumed that basic Excel functions and formatting are known. But where does the ‘advanced beginner’ begin?

1) Firstly, read these 6 points about why a spreadsheet is not a financial model! http://www.plumsolutions.com.au/articles/six-reasons-your-spreadsheet-not-financial-model

2) Now take a look at this article on how to build a financial model. It’s just an overview of the process, not a technical guide. http://www.financial-modelling.net/tutorials/building-a-financial-model-step-by-step

3) http://www.investopedia.com/financial-edge/0812/financial-models-you-can-create-with-excel.aspx Investopedia gives a clear explanation here of the types of financial models that you can create in Excel and what they mean. There are plenty of links to follow for DCF (Discounted Cash Flow) analysis.

And now for the actual learning how to build them in Excel…

4) http://chandoo.org/wp/2010/07/21/financial-modeling-introduction/  Chandoo has a great 6 step guide which goes through financial modeling from start to finish. Includes loads of pdfs to download including cheat sheets and case studies. Definitely a good place to start learning how to get to grips with financial modeling and Chandoo is well known for its Excel tutorials so if anything doesn’t make sense about the terms used to talk about formatting etc. then the answer can surely be found elsewhere on the website (and often already has hyperlinks added to skip straight to those sections as they are talked about within the financial modeling article!)

5) http://www.corality.com/knowledge-and-tools  A great website of free resources here on all aspects of financial modeling. Includes a really helpful blog and every tutorials comes with Excel and pdf downloads.

6) http://www.bestpracticemodelling.com/ This Australian site has a good range of materials here – the videos are easy to follow and the downloadable templates mean that you can follow along with them. Includes pdf guides that explain everything step by step too.

7) https://www.youtube.com/user/dezyreuniversity/videos BusinessFinance has a good range of financial modeling tutorials here, usefully split into shorter topics such as three statement models and discounted cash flow. Aimed at investment bankers but same skills apply.

8) https://www.youtube.com/user/edupristine/videos  edupristine’s channel offers 9 tutorials based on financial modeling in Excel. 30-40 minutes each. They are previews of their masterclasses and are good demos to check out.

9) http://teten.com/blog/2011/11/10/template-startup-financial-model/ Take a look at this template with 5 tabs showing some of the main forms of financial modeling in Excel.

10) http://www.wework.com/magazine/knowledge/financial-modeling-tips-best-practices-ways-save-sanity/ Finally some top tips on how to save your sanity when modeling in Excel!

For paid courses, the top two recommened ones are quite pricey but worth it!

https://breakingintowallstreet.com/biws/excel-financial-modeling-fundamentals/ Recommended by www.financewalk.com this course from Breaking into Wall Street offers 43 hours worth of videos on financial modeling and works through real life case studies such as a valuation of Wal-Mart and large mergers and acquisitions worth millions of dollars. You get a certificate for completion as well which can be added to your CV list of skills. Costs $247 and is aimed at the absolute basic

http://www.wallstreetprep.com/ Said to be a little harder but more thorough. The basic program is $199 and the premium package is $399 and there is also a $39 Excel crash course if you need to do that beforehand. Includes sample demos on the website (and on Youtube) to see if the style of learning suits you and sample downloads to print too. Also offers public programs across America. You can also sign up for a free newsletter which gives you Excel cheat sheets and finance interview questions which may transfer for consultants.

There is also http://www.lynda.com that offers thousands of free videos in return for a monthly or yearly subscription. For one month it is only $15 and there is this 2 hour video going through all the financial functions in Excel which should be worth the price http://www.lynda.com/Excel-2010-tutorials/Financial-Functions-in-Depth/83199-2.html?srchtrk=index:1%0Alinktypeid:2%0Aq:financial%2Bmodeling%0Apage:1%0As:relevance%0Asa:true%0Aproducttypeid:2

And of course, there is always the old fashioned book to learn from. The top ones are:

1) Financial Modeling using Excel and VBA by Chandan Sengupta

And massive plus for this one – you can read it all online for FREE here! http://jcb.gfedu.net/download/Financial%20Modeling%20Using%20Excel%20and%20VBA.pdf

2) Next Generation Excel: Modeling in Excel for Analysts and MBAs

3) Valuation: Measuring and Managing the Value of Companies by McKinsey & Company Inc. (Tim Koller, Marc Goedhart, David Wessels)

4) Mastering Financial Modelling: A practitioner’s guide to applied corporate finance by Alastair Dey

60 day Excel Expert

Excel is the saviour for most consultants looking for quick ways to calculate and manage data and make financial models. But the knowledge needed to perform these tasks efficiently is vast and complicated. So to simplify it for you, here is the ultimate Excel list of things to know about this programme for consultants. Short bursts over a long period of time will ensure that these become innate skills. So click on one of these links each day, watch the videos and read the articles and over the summer before you start your graduate placement, you’ll be ahead of the game!

Basic Introductions

Day 1 Learn the Excel interface – https://www.ischool.utexas.edu/technology/tutorials/office/excel/index.html Probably the most basic introduction to the Excel interface and what different things in Excel look like and do. Simply an explanation of how the programme works and how to perform basic tasks.

Day 2 Sort a list – Needless to say, sorting lists can organise your data in more practical ways and it is very simple as Excel has the capacity built in to do this. Here are three ways to use it http://www.wikihow.com/Sort-a-List-in-Microsoft-Excel

Day 3 Filter unique items on a list – Sometimes you may want to filter lists according to specific criteria. Read this basic intro to filtering out data and removing duplicates in the sort http://office.microsoft.com/en-gb/excel-help/filter-for-unique-values-or-remove-duplicate-values-HP010073943.aspx

Now watch this short video on sorting and filtering to recap

Day 4 Create a Custom List – When looking to sort according to criteria that Excel can’t do automatically such as alphabetically or numerically then you will need to create a custom list. This article shows you how http://www.techrepublic.com/blog/10-things/10-steps-to-creating-a-custom-list-for-sorting-in-excel/3581/#.

Day 5 Basic SUM, AVERAGE and MAX/MIN functions – This is a quick video intro to entering data and using these simple functions using a very basic table of data.

Day 6 Customise and format data – Excelteacher offers a video tutorial here on how to generally customise Excel worksheets with colours and formats and how to protect them from interference.

Day 7 Pull data from a website – This is obviously an important skill to learn if using client’s data that is available online.

Day 8 Cell references – Understand the difference between relative, absolute and mixed cell references in this short article

http://www.excel-easy.com/functions/cell-references.html

Day 9 How to Print a spreadsheet in one page – Excel has a tendency to be awkward to print due to the tabular form of the interface but there are ways to ensure that it all fits onto one page and is formatted correctly.

Day 10 How do functions and formulas work? – Excel seems like a very complicated programme when you start using it as there are lots of seemingly random symbols used within formulas to calculate things. Read through Microsoft’s overview of formula construction here and understand what the symbols you will be using actually mean. http://office.microsoft.com/en-gb/excel-help/overview-of-formulas-HP010081865.aspx Essentially, a formula is an expression which calculates the value of a cell whereas functions are predefined formulas and are already available in Excel. So you insert functions such as SUM, AVERAGE, COUNT etc to create a formula that performs calculations on certain cells (which would look for example like =SUM(A1:A2). This could be written out like =A1+A2 but the SUM function is a quicker and easier way to perform such a task in Excel). 

Managing Data

Day 11 Using the ‘Fill’ command – There are several ways to fill data or formulas into multiple cells. This article shows you four types, from dragging the Fill handle to using a Custom Fill. http://www.extendoffice.com/documents/excel/867-excel-apply-formula-to-entire-column-row.html

Day 12 ROUND – It is often useful to ROUNDUP or ROUNDDOWN when making reports in order to make the numbers easier to handle. This video shows you how.

Day 13 CEILING – This is also relevant to rounding as it caps the number of digits displayed in the cell. http://spreadsheets.about.com/od/roundingnumberfunctions/ss/2012-01-05-excel-ceiling-function.htm

Day 14 Nesting functions – This is when two or more functions are used together in one cell to make a calculation. You can use up to 64 levels of nesting in one formula so they can get quite complicated but do follow logical pathways. Read the short explanation here with the SUM and ROUND example http://spreadsheets.about.com/od/excel2010functions/qt/091223-nesting-functions-excel.htm

Day 15 RANK – This function is used to compare numbers against each other.

Watch the short Contextures tutorial here on how to use it

And read techrepublic’s more technical post on the language input here http://www.techrepublic.com/blog/microsoft-office/how-to-use-microsoft-excels-rank-function/

Day 16 VLOOKUP – This function allows you to search for information across vertical columns (that’s what the ‘v’ stands for!) Read how to use them here http://www.howtogeek.com/howto/13780/using-vlookup-in-excel/ The basic construction is =VLOOKUP(what,where,inwhichrow,true/false)

Day 17 – HLOOKUP – Just like VLOOKUP, but the ‘H’ means ‘horizontal’ so is only used for rows. It is not used as much as VLOOKUP as usually tables are based on vertical columns, but it still a useful counterfoil to understand. http://www.contextures.com/excelhlookupfunction.html

Day 18 – MATCH and INDEX – As VLOOKUP and HLOOKUP are restricted by either the vertical or horizontal axes they are searching, many argue that MATCH and INDEX are better to use. This businessinsider article explains why http://www.businessinsider.com/excel-tricks-vlookup-index-match-pivot-tables-array-2013-5?op=1 and Mr Excel explains how to use them too http://www.mrexcel.com/articles/Excel-Index-Match-Functions

Here is a quick summary of all the lookup functions: http://www.excel-easy.com/functions/lookup-reference-functions.html

Day 19 – Using VLOOKUP info from different workbooks

Day 20 – Advanced filtering techniques There are plenty of ways that data can be filtered so read through this more extensive list of different filters you can create (includes worked through video examples)  http://www.contextures.com/xladvfilter01.html

 

Logical Formulas

The following inputs create a formula which will carry out a different calculation based on criteria that you specify.

Day 21 – IF Statements This function is used to work out statements that will come out TRUE or FALSE

Watch the ExcelIsFun video below with 7 worked through examples

Day 22 IF THEN ELSE You can add more and more conditions into IF statements in order to affect the TRUE or FALSE outcome. See how to use the words ‘THEN’ and ‘ELSE’ here

 http://www.excel-easy.com/vba/if-then-statement.html

See this short video here too

Day 23 IF AND OR  

A short explanation of how IF statements work with AND/OR

http://www.excel-easy.com/functions/logical-functions.html 

And watch this video on compound IF statements

Day 24 Logical formulas in practice  Using a simple chart to get to grips with these functions, Excelteacher goes through these functions.

 

Array Formulas

An array is a group of cells that are related e.g. in adjacent columns or rows. An array formula is therefore a formula that works with a series of data values rather than just one. It returns either a single value or an array of values into a selected cell or array of cells. Read the basics in this blog post here http://www.spreadsheetsmadeeasy.com/excel-array-formulas-for-beginners/

Day 25 SUMPRODUCT – This function calculates the SUM across an array of cells. Follow the input steps here http://www.consultdmw.com/excel-sumproduct-function.htm

Day 26 SUMIF – This is used to add up the SUM across a certain number of cells that meet specified criteria. Here’s a five minute lesson on how to use this function: http://fiveminutelessons.com/learn-microsoft-excel/using-sumif-add-cells-excel-meet-certain-criteria

A large range of examples and more complex SUM calculations including videos can be found here at Contextures http://www.contextures.com/xlFunctions01.html

Day 27 COUNTIF – This function calculates the number of times that a value occurs within a set range of criterion. This article explains how to use it: http://best-excel-tutorial.com/58-excel-functions/129-countif-function

 

Text inputs

Day 28 LEFT, RIGHT and MID – These functions cut text from the left, right or middle to simplify the data.

Techrepublic goes through an example to explain how this is useful http://www.techrepublic.com/article/save-time-by-using-excels-left-right-and-mid-string-functions/=

And here’s a Danny Rocks tutorial on how to use these three functions

Day 29 CONCATENATE – This word means ‘to combine’ and is therefore used in order to combine information from different cells that involve text, cell references or blank spaces (otherwise it would be a SUM or other numerical function). It can be used for up to 255 text entries.

Watch the ExcelIsHell tutorial here

And here is a printable example sheet too http://wagda.lib.washington.edu/gishelp/tutorial/concatenate.pdf

Day 30 LEN – This function tells you the character count of a piece of text inputted into a cell. It is very simple to use as LEN is just added with the cell number you want to know. http://blogs.office.com/2011/07/05/use-the-len-function-to-get-a-cells-character-count/

Day 31 FIND – Similar to VLOOKUP but used to search for the position of specific text within the worksheet. Watch out, it’s case sensitive! http://spreadsheetsuperstar.com/excel-find-function-tutorial/

Day 32 Date and time arithmetic – The symbols / and – need to be used when inputting dates and Excel has the capability to add dates and times together too. Follow the Excel Easy article here on how http://www.excel-easy.com/functions/date-time-functions.html Chandoo also has some Top 10 tips too http://chandoo.org/wp/2008/08/26/date-time-tips-ms-excel/

Day 33 Formatting Textual Information

A good video here on formatting textual information so that Excel treats the data as numbers but places the correct information in front (e.g. dollar signs, AM/PM, percentages)

 

VBA and Macros

VBA is the ‘language’ used to create functions and formatting in Excel. So all the functions above are in this VBA ‘language’ but are inputted into each cell rather than into the code. If you use VBA then you have created a macro. This is a very large and complicated topic and goes all the way to configuring the programme. But work through the following links to get the basics on how to do more complicated sums and calculations.

Day 34 Create a Macro – basic intro Read this HowtoGeek intro into what macros are with plenty of screenshots to help you navigate. http://www.howtogeek.com/162975/geek-school-learn-how-to-use-excel-macros-to-automate-tedious-tasks/

Day 35 Macros from Easy to Intermediate http://www.teachexcel.com/excel-tutorials/category.php?section=macros Go through the short videos and accompanying text here on everything to do with Macros from easy to intermediate levels.

Day 36 How the VBA InputBox works – Follow this article that uses an example VBA code and what it shows https://www.udemy.com/blog/excel-vba-inputbox/

Day 36 Practice Macros and VBA Use Microsoft’s 30-40 minute tutorial here to check everything is understood.

http://office.microsoft.com/en-gb/training/get-in-the-loop-with-excel-macros-RZ001150634.aspx

Day 37 Apply conditional formatting This function allows you to apply certain formats depending on the criteria that you set e.g. any number over 5 turns the cell green. This is a useful tool for you to visually analyse large sets of data quickly

Watch this video to see it in practice

Day 38 VBA Cheat Sheet Read this Dummies cheat sheet that goes through most of the VBA language that you will come into contact with. A good reference sheet for the future too. http://www.dummies.com/how-to/content/excel-vba-programming-for-dummies-cheat-sheet.html

 

Statistics

Excel is not built for high level statistics but can easily deal with simple statistical problems that a management consultant is likely to come across.

Day 39 Quick Descriptive Statistics This Excel function allows you to see the main statistical outcomes of a set of data at the click of a button. It automatically calculates mean, average, sum, skewness, standard deviation etc).

http://www.excel-easy.com/examples/descriptive-statistics.html

Day 40 Correlation and Regression Read this help sheet from page 2  http://ire.org/media/uploads/car2013_tipsheets/excel_stats_nicar2013.pdf

Day 41 TREND – This function calculates the trend line through a given set of y and x values. Of course this is easier to see on a graph but this function enables you to complete the maths behind the graph line.

Here is a short video on using TREND with a simple linear regression

On Trend Analysis, Wikihow has quite a detailed description of various methods you may come across. http://www.wikihow.com/Do-Trend-Analysis-in-Excel

Day 42 STDEV – Short for ‘Standard Deviation’ which can be calculated from lists of data. Enter STDEV and the range of cells you want to calculate it for. Watch the simple video tutorial example here:

Day 43 VAR – Short for ‘Variance’ of a set of data. Simply explained by Microsoft including the statistical equation this function is solving http://office.microsoft.com/en-gb/excel-help/var-function-HP010335665.aspx

Day 44 FREQUENCY – As the name suggests, this function shows how many of a certain type of something are mentioned within a data set. Some examples given here on how it works http://www.accountingweb.com/topic/technology/whats-frequency-using-excels-frequency-function

Day 45 – Statistics overview Follow the Microsoft training website for their 50 minute tutorial on everything about Excel statistics. Includes some practice examples too that will ensure you understand all. http://office.microsoft.com/en-gb/training/overview-RZ001091922.aspx?section=1

 

Financial Modeling

Day 46 How to create financial scenarios in Excel – Financial Modeling is an important feature of Excel for consultants as it allows for the manipulation of the inputted and managed data to come to decisions regarding investments or profitability of a company.

Watch this 15 minute edutechional video on various types of scenarios that can be modeled within Excel

Chandoo offers a 6 part intro into how to start building financial models and project evaluations here http://chandoo.org/wp/2010/07/21/financial-modeling-introduction/

Day 47 DCF (Discounted Cash Flow)

This is a type of analysis used by consultants to value a project, company or asset over time. All the future incoming and outgoing cash flows are estimated and discounted to give Present Values (PVs). Follow this step by step blog post here on what to do to work out the DCF of a set of data http://www.financialmodeltraining.com/blog/21_dcf_discounted_cash_flow_basics

And the well known Wall Street Prep offers a video tutorial here

Plus there is a free template download available here to start building your own DCF model http://www.axial.net/blog/free-excel-dcf-template-download/

Day 48 NPV (Net Present Value)

This is simply the sum of all future cash flows worked out during the DCF calculations to estimate the overall present value of a company. Your clients are sure to be wanting this to be a high number and your job may be to find solutions to get this number up!

Watch the ExcelIsHell video to see how!

Day 49 IRR (Internal Rate Return)

This is a term used in capital budgeting to work out the profitability of investments. Again, another term that is going to be very useful for consulting work. Follow this friendly voice through the IRR tutorial

Day 50 WHAT-IF analysis Allows you to try out different scenarios for formulas depending on the criteria you set. This function is therefore very important for management consultants when working out what types of mathematical solutions they can offer to the client based on the data they have researched/provided. Read the Excel Easy article on how to use it here http://www.excel-easy.com/data-analysis/what-if-analysis.html

Day 51 Solver and Goal Seek Indeed Excel now offers tools that find optimal solutions to all kinds of decision problems! Solver can take into account constraints and variables and Goal Seek is used to find a result when you are not sure of the starting value. Find out how Solver works here http://www.excel-easy.com/data-analysis/solver.html  and for Goal Seek check out http://www.homeandlearn.co.uk/excel2007/excel2007s7p4.html

 

Visualising Data – Reports and Charts

Day 52 How to create a graph or chart from worksheet data

As creating charts is a very visual skill, GCF Learn Free has a great slideshow like page here that goes through the different types and how to create them. http://www.gcflearnfree.org/excel2010/17

Day 53 Creating a Gantt chart This type of chart is used a lot by management consultants as it is used for project management. Tasks are shown on the vertical axis, and the project time span is represented on the horizontal axis. Each task has a corresponding bar that shows the time span required for that task. The bar can be filled in to show the percentage of the task that has been completed.

Watch this video tutorial that goes through how it works

And you can use a free Gantt template to work from here http://www.vertex42.com/ExcelTemplates/excel-gantt-chart.html

Day 54 – Sparklines A new feature in Excel 2010 that offers graphical lines in a cell to chart the rise and fall of the values entered in the column/row

Day 55 Create a Pivot Table

They are considered an advanced Excel skill but are not very hard to create! Watch this short video with transcript below.

http://video.about.com/spreadsheets/Excel-Pivot-Tables.htm

Now watch this excellent longer worked through example

Day 56 Power Pivot a Pivot Table This is a new function of Excel that allows larger sets of data known as ‘Power Pivot’ to be entered into Pivot Table analysis.

Watch Microsoft’s quick explanation of how to create a Pivot Table using Power Pivot data here

Day 57 Pivot Charts These enable the user to visualise Pivot Table data. Excel Easy shows how to quickly do it http://www.excel-easy.com/examples/pivot-chart.html But for a more fancy interactive format, watch the following video

Now Check out this 6 page guide and download the practice workbook to try out your newly learnt Pivot Table skills!  http://www.gcflearnfree.org/excel2013/27

Day 58 – Create a Summary Report Reports will be vital for your consulting career as ways of visualising and summarising the data that you have collected and researched. Here is the Dummies rundown of how to create one http://www.dummies.com/how-to/content/how-to-produce-a-summary-report-in-excel-2013.html

For a quick overview of Summary reports, watch this

Day 59 – Create a Key Performance Indicator (KPI) Dashboard Report Another vital reporting technique that all consultants will be using at some point. The dashboard is designed to consolidate KPIs from numerous areas of the business research to increase the visibility of business-critical information to the end-user (the client). They are also particularly useful as they can be interactive with graphs changing according to other criteria that are clicked on.

See this 9 minute video to build a simple KPI dashboard

For other types of Dashboard report, see Chandoo’s  templates and tutorials here http://chandoo.org/wp/excel-dashboards/

 

Conclusion

Day 60 – Refresh and Consolidate! And there you are! You did it! Now you should have a pretty good understanding of most of the things you will need to know in Excel but it is important to refresh and consolidate this knowledge. Spend this final day looking back over the skills you have learnt over the last 59 days and ensure you remember them all. There will always be more to learn and your skills will improve on the job, but as a good starting point for your management consulting career this course should equip you with the necessary knowledge. For now, we dub thee an Excel Power User!

 

Other useful resources

Here are some more useful resources which cover more topics than were appropriate for the daily quick fire tutorials.

http://www.contextures.com/xlfaqApp.html#App04 A good FAQ list with simply explained answers including codes to enter and screen shots.

http://www.washburn.edu/faculty/boncella/BU956/Excel%20Modeling/Ch3.pdf A really good pdf here of all basic Excel knowledge from navigating the windows to inputting data and creating charts.

https://www.lmu.edu/AssetFactory.aspx?did=53607 The checklist for the lynda.com Excel course – useful to compare your skills to what is listed here

http://laude.cm.utexas.edu/courses/OAS/OASskillmodules/sme16f08f08stasin%20excel.pdf A printable statistics revision sheet from the University of Texas

Building a CAGR calculator in Excel

We already know that Excel is an incredibly powerful and useful tool, even if perhaps it is pretty pesky to get the hang of! One useful consultancy function to build in Excel is a Compound Annual Growth Rate (CAGR) Calculator. This is the measure of growth on an investment over a period of time, a particularly useful thing to find out if the investment fluctuates over time.

Watch this short video tutorial from this MBA e-mentor programme to understand the basics of CAGR and how it is calculated

Websites such as Investopedia http://www.investopedia.com/calculator/cagr.aspx have their own inbuilt CAGR calculators that can give you the numbers you need if you have the necessary information to hand. But to calculate the numbers from large sets of data you have collated in Excel, it is important to build your own CAGR calculator.

The following 8 websites will give you all the CAGR understanding you need

1) The best video tutorial to show the CAGR function input for a very basic table is offered by Savoir-Faire…

2) https://www.udemy.com/blog/cagr-formula-excel/ The Udemy blog gives a good simply explained introduction to CAGR. Also offers info on the more flexible XIRR function to achieve similar results.

3) However, as this video warns, XIRR can produce some incorrect results. So watch this to ensure you are not using wrong data – it really is imperative that the numbers are correct!!

4) http://www.chartrecipes.com/article-cagr.html  Chart Recipes offers a good breakdown of how the CAGR calculation works and includes a downloadable pdf of the page and an Excel template with the charts used in the example so you can test it for yourself

5) http://www.make-money-stock-value-investing.com/cagr-formula.html  A simple sheet that gives the spreadsheet CAGR formula too

6) Now of course CAGR functions can be used for bigger sets of data than these small tables. This Youtube video shows how to input the CAGR function in VBA format

7) http://guj800.wordpress.com/2008/06/19/create-your-own-excel-formulas/ This blog post explains the VBA format for the CAGR function

8) http://economistatlarge.com/featured-articles/compound-average-growth-rate This article includes how to calculate CAGR on a financial calculator as well as on Excel

Finally, as CAGR is an advanced Excel function, it may be worth signing up to Udemy’s ‘Microsoft Excel Advanced Functions and Formulas’ course here: https://www.udemy.com/microsoft-excel-advanced-formulas-and-functions/?tc=blog.cagrformulaexcel.text.p&utm_source=blog&utm_medium=udemyads&utm_content=post19152&utm_campaign=content-marketing-blog&xref=blog  You get a free trial and lifetime access to the 58 tutorials when you buy the pack. You also get a certificate of completion which is useful to add to your CV.

Top Video Tutorials for Creating Macros

Our earlier post here listed top websites to learn the VBA language for Excel. So now it’s time for the top video tutorials to get to grips with the macro features of Excel further…

To start off, this video gives a good introduction on how to start recording a macro

Next, Hun Kim gives a good basic introduction to working with macros:

‘Excel is Hell’ tries to alleviate the more ‘hellish’ Excel features by creating this helpful list of 35 short videos on tips and tricks to work with macros. https://www.youtube.com/playlist?list=PL5CEC03D7625A0D6E Useful when you are having problems and looking for a solution as the tips answer commonly asked questions e.g. ‘using the range property’ or ‘making dynamic cell references’

http://www.mrexcel.com/excel_video_training_vba.html Here you can find the directory of ‘VBA for macros’ videos from the well known Excel guru Mr. Excel

Xtreme Excel offers 17 videos here on creating macros. https://www.youtube.com/playlist?list=PL1R_HJw0CDYLYX_dcNxN4V_FlMeMGzeDW Includes an excellent 3 minute short intro to macros…as we’ve all been wondering what they actually are?!

VBA4Excel offers 4 beginner and 4 intermediate VBA videos https://www.youtube.com/user/VBA4Excel/videos

http://www.teachexcel.com/free-excel-macros/ Teach Excel offers many resources here but scroll down for the formatting macros section.

And finally Chandoo offers plenty of tutorials on VBA macros here for all abilities http://chandoo.org/wp/excel-vba/videos/

As always, there are courses you can pay for online to become Excel savvy, this one is only at $49 and dedicated to the ever troublesome macro ordeal.  http://spreadsheetsuperstar.com/excel-macro-training-course-online/

You can also view plenty here at lynda.com but you have to become a member to view all thevideos http://www.lynda.com/Excel-2010-tutorials/macros-in-depth/74463-2.html

Top online Powerpoint and Excel Templates for Consultants

To help your work go a little faster, there are lots of Powerpoint and Excel templates available online. Many of these are business and management specific with good, clear and professional looking designs – so why spend ages trying to align all the text boxes on a presentation or worry about the functions and formatting in a spreadsheet? The basics are already out there to build from!

Firstly, a quick mention of this website: http://capabilitycenter.com/ It has to be the best for consulting professionals to get templates from as there is such a multitude and they are all submitted by consultants themselves. It claims to have 220,000 downloadable files including 140,000 Powerpoints and 50,000 financial models. That’s quite a lot. Only catch to get access to this goldmine is that you can only access the resources if invited after writing up an application. Definitely worth it if you can get through though as it is recommended as the #1 site for consultants.

Powerpoint

1) http://www.slideteam.net/powerpoint-presentation-slides/management-powerpoint-templates-and-presentation-slides.html Definitely the best slides for consultants looking for great diagrams with vibrant colours and enticing designs. There are some free slides available here but the subscription is worth it for these excellent slides.

2) http://slidehunter.com/powerpoint-templates/ Here there are uploads from top consulting firms such as BCG if you search for ‘consulting’ presentations

3)http://www.strategyexpert.com/categories/powerpointtemplate Strategy Expert is a consulting website so these templates will be consulting specific.

4) http://www.slidevana.com/ No slides can be downloaded for free here but there are some nice designs, if only to get some inspiration to create your own.

5) http://www.shmula.com/management-consulting-powerpoint-template-downloads/ Another subscription needed for this one, but several management consultant specific slides available.

 

Excel

1) http://www.skilledup.com/learn/business-entrepreneurship/best-free-excel-templates-dashboards/  Skilled Up has an excellent list of Excel templates here with links to all the websites to download them from (note some of them require membership but have one free download). Clear explanations are given for what the templates are for – some are more relevant to small businesses but definitely an all-purpose useful list of links including financial modeling and Excel templates for Macs.

2) http://chandoo.org/wp/project-management/  Lots of useful templates from Chandoo here including Gantt charts, project reporting dashboards, management tracker tools and ready-made formula sheets

3) http://www.strategyexpert.com/categories/exceltools  Strategy Expert again with a small range of Excel models here but all aimed at consultants.

4) http://altoconsulting.com.au/blog/spreadsheets/  Slightly annoyingly not in any order but over a 110 Excel spreadsheets for free download here. As they’re free, it’s certainly worth a search for what you’re looking for

5) http://www.exinfm.com/free_spreadsheets.html  Another non-ordered list but almost all based on financial models with direct links to the free downloads from other sites

6) http://www.financialtemplatesstore.com/  For a relatively small price, this website offers a range of financial templates including Analysis, Planning and Projection.

Power Pivot your Pivot Table – Top tips

Pivot Tables are an incredibly powerful and useful tool in Excel. But did you know that with a new free Microsoft Extension you can power up your Pivot Tables to do even better quick summary and organisation with over a MILLION rows of data?

Here Ed and Michele from Microsoft quickly show how to create a Pivot Table using Power Pivot data:

Microsoft offers its own Power Pivot tutorials here http://technet.microsoft.com/en-us/library/gg413497.aspx There’s help on installing the extension here too in case you are having problems.

And here is a website dedicated entirely to Power Pivot http://www.powerpivotpro.com/what-is-powerpivot/  No doubt this will solve all your Power Pivot needs!

So what are you waiting for? Download PowerPivot now at http://www.microsoft.com/en-us/bi/powerpivot.aspx

Top 8 Excel Blogs

You wouldn’t think that there would be blogs out there purely dedicated to giving you a daily dose of Excel fun…but there are. Probably more useful for the advanced users who are looking for answers to common problems – beginner’s would get far too lost in the Excel geekiness here…!

1) http://andrewexcel.blogspot.co.uk/p/excel-tips.html Andrew Engwirda is a self-proclaimed ‘incurable Excel addict’ so a crazy amount of blog posts here on common Excel issues. Includes a few downloads as well.

2) http://www.excelguru.ca/  A large Excel ‘knowledge base’ here. Plenty of categories on the left to easily navigate and try and find the topic you are looking for more help with. Also includes a forum community to ask further questions.

3) http://www.jpsoftwaretech.com/excel-vba/ Very technical, but some important VBA codes here to solve common problems. You can subscribe to the general blog for free and there’s some example workbooks too to practice with.

4) http://www.excelguru.ca/blog/ A very detailed blog with step by step guides, VBA codes and recent news.

5) http://peltiertech.com/WordPress/ Focused on charts and VBA programming

6) http://www.andypope.info/ A simple website but some interesting Excel info here including a ‘fun stuff’ section and a good book list.

7) http://excelhero.com/blog/A very good blog whose author has just set up an online course for further instruction. Includes quite a lot of advanced stuff like Power Pivots

8) http://spreadsheetpage.com/index.php/blog A good Excel news blog and trouble shooting.

Top 10 Books on Excel for Consultants

Websites are good starting points for trouble shooting problems and to watch videos of Excel in action. But sometimes, good old fashioned books are more helpful for fully understanding concepts, with the added bonus of being able to put sticky notes in and highlight important points as well as being available offline. So to create your own essential Excel library, get reading the following books…

1) ‘Excel 2010 All-in-One for Dummies’ By Greg Harvey

Excel is certainly something complicated enough and in need of simple explanation that the Dummies are a useful book to turn to. This All-in-One book contains 8 separate books: Excel basics, worksheet design, formulas and functions, worksheet collaboration and review, charts and graphics, data management, data analysis, and Excel and VBA. If you get a handle on these 8 topics then you’ll have a good starting point to delve into the more complicated subject specific books.

2) ‘Excel 2010 Made Easy’ by Lynn Wright

Aimed at the very beginner and assuming no prior knowledge, this is an essential starter book. Very colourful and simple explanations to follow, the book works it’s way up to basic Pivot Tables. And as recommended by Which? magazine, you can be assured that it has been thoroughly tested and approved by the general public who use it.

3) ‘Microsoft Excel 2013 Bible’ by John Walkenbach

If this is the Excel Bible, then Walkenbach is the Excel God. A fully comprehensive guide to everything about Excel generally as well as all the new features of this newest version of the software such as Sparklines, Flash Fill, and Analysis ToolPak. Includes chapters on VBA for Macros so everything from the basics to the complex is covered here.

4) ‘Learn Excel 2010 Essential Skills with The Smart Method: Courseware Tutorial for Self-instruction to Beginner and Intermediate Level’ by Mike Smart

A good teach yourself guide starting with the basics then moving to improving skills, working with multiple workbooks and formatting for printing. A good basic introduction, but does not move into VBA code for Macros or analysis.

5) ‘The Mr. Excel Library Series’ by Bill Jelen.

This is an excellent series to learn advanced Excel techniques. Of course Bill ‘Mr. Excel’ Jelen is widely known as one of the top Excel gurus in the world so his step by step instructions including real world business examples are highly recommended reading. Titles in the series include ‘Charts and Graphs’, ‘Pivot Table Data Crunching’ ‘VBAs and Macros’ and ‘Business Analysis in Excel 2010’. By pinpointing specific areas and expanding on them, a good range of in depth Excel knowledge is achieved.

6) ‘Slaying Excel Dragons: A beginner’s guide to conquering Excel’s frustrations and making Excel fun’ by Mike Girvin and Bill Jelen

An amusing romp through Excel with the two top Excel masters. Plenty of screenshots and talkative tone makes it not a chore to work through and can easily be combined with the ExcelIsFun and Mr. Excel Youtube content. A really good reference book for the simple answers to commonly asked questions. Definitely worthy of a place on your shelf!

7) ‘The Mr. Spreadsheet Bookshelf Series’ by John Walkenbach

John Walkenbach is known as ‘Mr. Spreadsheet’ so of course friends (or perhaps enemies…?) of Mr. Excel. This series includes some areas not covered by the Mr. Excel Library series such as ‘Dashboards and Reports’ and ‘VBA Programming’ and ‘Fixing Problems in Excel’. A recommended title is ‘101 Excel 2013 Tips, Tricks and Timesavers’ which gives useful general advice for easier operation of Excel.

8) ‘Next Generation Excel: Modeling in Excel for Analysts and MBAs’ by Isaac Gottlieb

An excellent book for the intermediate user that has knowledge of Excel and wants to tailor it to perform analysis tasks. The book covers topics such as financial functions, data mining using Pivot Tables, statistics for non-statisticians, accounting and ‘What-if’ analysis. Isaac Gottlieb has taught Excel skills to some of the top analysts and the approach of the book is on short descriptions coupled with plenty of screenshots rather than long textual descriptions.

9) ‘Excel Basics to Blackbelt: An Accelerated Guide to Decision Support Designs’ by Elliot Bendoly

This has been reviewed as the standard reference book for all analysts and consultants. Excel can be used to assist decision making within an organisation which consultants will often use as part of their solutions for their clients. So to learn how to design such support systems, make your way through this book. Not useful for a beginner of Excel but very useful for a beginner of this more complex use for Excel.

10)  ‘Financial Modeling’ by Prof. Simon Benninga

A second book from really useful guide to financial modeling by combining complex financial theories with the practical Excel skills needed to use them in business. Includes plenty of exercises and a CD ROM with practice workbooks and solutions to the exercises. Definitely for experts – an appendix chapter details the VBA knowledge needed to understand the book.

The VLOOKUP function – 5 Top Websites

The VLOOKUP function is a database function that works to retrieve information within a list. It’s extremely important and useful for consulting as by using it you can quickly find out corresponding information about the ‘unique identifier’ that you enter along with the VLOOKUP. But it’s also a function that causes persistent confusion – so to avoid that, here are the top 5 things to do to understand how to use the function.

Firstly, take a look at How To Geek and their simple step by step worked through guide of how to use VLOOKUP in the context of an invoice database. http://www.howtogeek.com/howto/13780/using-vlookup-in-excel/  At the very bottom of the page you can download the invoice template so you can practice.

Contextures offers a more bullet point format but using a lot more technical language about writing the functions so more for people with a bit of understanding. Includes a trouble shooting section and templates to download http://www.contextures.com/xlFunctions02.html

Now to get to grips with the function in action, ExcelIsFun offers over an hour of examples using the VLOOKUP function, covering beginner’s all the way to advanced. Certainly after watching this you will know everything from the basics to the pro!

As VLOOKUP can retrieve information, it might be necessary to combine information from different workbooks. This video shows you how…

Finally, if you want t0 stand out for your superior Excel knowledge, Business Insider has a useful tip here on using the MATCH and INDEX functions to perform tasks similar to VLOOKUP but when you want to search other vertical columns which VLOOKUP cannot achieve. http://www.businessinsider.com/excel-tricks-vlookup-index-match-pivot-tables-array-2013-5?op=1 Certainly a vital trick to know!