Numeracy

Acing the Case Study Assessment

The Case Study of the consulting interview process may seem like the most daunting part for some. But there is thankfully lots of information out there to help you get an idea of things that could be asked. At the end of the day, you have to remember that these are the kinds of scenarios you will be dealing with every day as a consultant, so the true future consultants will be eager with enthusiasm for this stage of the process.

Often the questions will address a variety of areas. These usually include Market Sizing, Business Operations/Strategy and Logic but could include some brain teasers too to test out your financial and business sense.

The first place to check out is the consulting firm websites themselves. They want you to do well and many of the firms have lots of tips and guidance, practice questions and help to get you through.

McKinsey – http://www.mckinsey.com/careers/join_us/practice_case_studies  Four practice case studies to work from here

Oliver Wyman – two practice examples here http://www.oliverwyman.com/careers/join-us/interview-preparation/case-interviews/step-3-case-studies.html

Accenture – has an excellent array of videos on youtube for general information about the work they do with plenty of interviews with current consultants. However, their interview prep section is surprisingly lacking. There are five short videos on the ‘Essential Skills’ consultants need here which are worth looking at to understand the competencies Accenture are looking for in their candidates https://www.youtube.com/playlist?list=PL9D0F0C99A849963B

This document from Accenture explains how to do generally do well in interviews http://careers.accenture.com/au-en/Documents/Interview%20Stages.pdf And the Interview webpage has a short list of points to heed http://careers.accenture.com/gb-en/find-your-fit/experienced/recruiting/experienced/Pages/top-tips.aspx

LEK – has a useful youtube channel. This video on the general case interview is helpful and the interview playlist has some further worked through examples.

Bain & Company http://www.joinbain.com/apply-to-bain/interview-preparation/ Two practice cases and videos that go through questions with three answers – see if you can work out which is strongest.

Booz & Co  http://www.strategyand.pwc.com/global/home/join_us/apply/case_prep Very short bullet points on what they’re looking for

OC&C Consultants http://www.occstrategy.com/sites/default/files/01_burger_machines_v02.pdf One short case study example here in this pdf

A helpful video from a small consulting firm here, but a good helpful walk through of the case interview

Now watch this full half hour interview on how to crack the case study so you know what to expect when you’re the one sitting there!

Next, here are the top three Case Interview Gurus and their books and websites to check out…

1) ‘Case Interview Secrets: A Former McKinsey Interviewer Reveals How to get Multiple Job Offers in Consulting’ by Victor Cheng to go with his website www.caseinterview.com which gives free advice upon subscription to a free newsletter. This book has won praise from its explanation of the rationale behind the case interview process. When you know what the interviewer is thinking it can really help your understanding of how you can make your answers better.

Victor Cheng also has a recorded presentation here

2) ‘Case in Point: Complete Case Interview Preparation’ by Marc P. Cosentino is the book to go with his website www.casequestions.com The Wall Street Journal named it the MBA Bible so it is worth a read with 40 strategy cases and many other types of question too. Here you can watch a 2 hour workshop on case interview success on his website http://casequestions.com/free-workshops.cfm The website also offers some other useful resources including the following short pages on tips for acing the case interview https://www.casequestions.com/cp_tips.cfm a simple framework for working through the case interview https://www.casequestions.com/cp_frameworks.cfm and the ‘commandments’ to follow https://www.casequestions.com/cp_commandments.cfm On the flip side, have you ever wondered what the Interviewers are thinking? Well this pdf is aimed at those who are actually setting the questions – but potential applicants can read this to understand what they are looking for in answers http://www.casequestions.com/downloads/Case_Questions_10_Key_Things.pdf 

3) ‘Crack the Case System: Complete Case Interview Prep’ by David Ohrvall includes 150 integrated videos on the website www.mbacase.com. You can see one free example practice case here: http://www.mbacase.com/t-resources-free-practice.aspx

And here are 10 more useful resources to check out and improve your case interview knowledge:

1) https://community.bus.emory.edu/club/GCA/Shared%20Documents/consulting%20interview%20book.pdf A HUGE document here from the business school at Emory University which goes through literally hundreds of questions. It starts with getting the consulting interview but then goes onto detailing all the aspects of an MBA ‘in a nutshell’. This includes case study scenarios (helpfully listing the firms from which they originate) and which could be useful to take a look at for practicing your case study technique. Definitely take a thorough look through this for practice questions.

2) http://cs6256.vk.me/u25184492/docs/96c71aed668d/Ace_The_Case_2nd_Ed.pdf?extra=canvvE9IkaUMXSbar6lNBW_PSbX3SM2o7iHnjyh0bFV_y9H_U4kfjYLHxT08OiGehQRCEKGMMbQ0kFOBexl2Jx53nSJ0zQ A pdf guide from Ace the Case that goes through the entire case study process, including all the sections and worked through examples and comments on what the interviewer may ask and extend the discussion further. A definite must read!

3) http://sites.duke.edu/dukeapdconsulting/files/2010/08/Case_Book-Columbia20061.pdf An even more important document to read through! Written by the Columbia Business School it goes through the dialogue of case interviews to show how they progress and what interviewers may ask. Follow the work through examples and see if you would respond in the same way.

4) http://ucs.yalecollege.yale.edu/sites/default/files/Interviewing_Case_Structured.pdf A good pdf guide from Yale on how case studies are structured and includes some clickable links for further info at the end.

5) http://www.acethecase.com/glossary This is a really useful tool for the case interview as if you know all the vocab they might throw at you in a question then the method of calculating it shouldn’t be so daunting. This website also offers three free sample interview questions for Business Problems, Market Sizing and Logic before you have to pay to access more material. Only $14.95 for the ebook too

6) http://targetjobs.co.uk/career-sectors/management-consulting/306745-how-to-ace-your-case-study-interview-by-thinking-aloud Worried about those awkward silences in an interview? Targetjobs advises thinking aloud as a way of processing your thoughts – it’s certainly much more helpful for the interviewer than you sitting in silence!

7) http://www.consultancylinks.com/case_study_interviews.html A few useful tips on structuring your answer but note that the links are old and broken.

8) http://www.impactinterview.com/2010/01/160-consulting-case-interview-questions/ Not worked through examples though which is slightly less helpful. But plenty to work through to practice your technique

9) http://www.consultingcase101.com/tag/accenture/ Loads of examples here but requires a $6.99 monthly subscription to read the whole articles

10) http://www.wallstreetoasis.com/guide/consulting-case-interviews Buy 11 case studies for $49.99 from Wall Street Oasis .

Advertisements

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

Strategic thinking in a month – 30 top links

Strategy is probably one of the most important things a consultant has to learn on the job. Each firm is slightly different in their approach but the overall aim is the same – have a great strategy and the project will be great.

But getting a strategic mindset requires time and practice. Get ahead of the game and start to learn some strategic thinking before that interview. Read one of these articles/watch one of these videos each day and within a month you’ll go from novice to pro!

1) Firstly, let’s start with this good animated video on how to create a strategic business plan. Focuses on three questions to ask when creating the plan. Consultants often need to give this sort of advice to help the company realise it’s vision.

2) http://www.britishcouncil.org/professionals-work-strategic-thinking-1.htm The British Council offers this little tutorial on what strategic thinking is. As a government organisation they are used to using strategic planning all the time so useful advice to heed.

3) http://blogs.hbr.org/2007/04/how-to-think-strategically-1/ The Harvard Business Review gives a rundown on different ways in which to expand your strategic thinking

4) http://www.wobi.com/blog/strategic-thinking/six-key-elements-strategic-thinking  Wondering what the key skills are to train your brain to be strategic? Well here they are!

5) http://www.effectivegovernance.com.au/what-is-strategic-thinking/ A really great forum post here about how to be a good strategic thinker. Includes a table outlining the differences between a strategic and non-strategic thinker and the Wootton and Horne 3 strategic activities

6) http://www.mindtools.com/pages/article/newSTR_91.htm Aspiring to be a McKinsey consultant? This article explains the 7S Framework used by McKinsey to address problems.

7) http://www.mckinsey.com/insights/strategy/thinking_strategically McKinsey’s own detailed article here on thinking strategically is obviously a must read. It’s mainly a run down of how strategic thinking has developed over the years but all of this should be understood to gain a better understanding of how strategic thinking is developing.

8) http://www.inc.com/paul-schoemaker/6-habits-of-strategic-thinkers.html Are you a strategic leader rather than a lackey? 6 habits of strategic leaders. Includes a video

9) http://www.bia.ca/articles/TheRoleofStrategicThinkinginBusinessPlanning.htm A good article including a flow chart of a strategic planning model, explaining the role that strategic thinking plays in business.

10) http://www.tdan.com/view-articles/5018 Written by a consultant, this excellent list shows how projects work and the fact that ‘the soft skills are the hard skills’ a lot of the time.

11) http://www.experience-on-demand.com/downloads/How_to_Be_a_Strategic_Thinker.pdf A pdf version of an extensive powerpoint presentation on being a strategic thinker. A definite good thing to read through as includes useful flow charts and bullet point questions to show you how to think like a strategist.

12)  Rich Howarth, CEO of the Strategic Thinking Institute, gives an 10 minute presentation at a conference on how to develop your strategic thinking skills. He has worked with some of the largest companies in the US so he knows his stuff…!

13)  Here is an interview with Rich Howarth too!

14) http://smallbusiness.chron.com/strategic-management-needed-61313.html This article succinctly explains why strategic management is needed – that’s where consultants come in!

15) http://www.innovationexcellence.com/blog/2012/12/05/the-best-strategic-thinkers-5-sure-characteristics/ Five characteristics of great strategic thinkers – make sure you fit this model!

16) http://smallbusiness.chron.com/definition-successful-strategic-business-plan-2717.html A quick rundown of what a successful business plan should include

17) http://blogs.hbr.org/2014/02/develop-strategic-thinkers-throughout-your-organization/ Harvard Business Review article explaining how to develop strategic thinkers within an organisation – again advice that a consultant would probably give on a project.

18) http://blogs.hbr.org/2014/01/strengthen-your-strategic-thinking-muscles/ Another Harvard Business Review short article on how to strengthen those strategic thinking muscles!

19) http://www.fastcompany.com/3026646/leadership-now/nine-ways-to-share-your-strategic-thinking-skills-with-your-team CEOs often have trouble implementing their strategic plans, or making them clear to their team. Here is a list of nine improvement suggestions that a consultant would typically make.

20) http://www.skillsyouneed.com/lead/strategic-thinking.html Seeking to demystify the art of strategic thinking by explaining how to make a causal diagram and follow the logical progression of a project.

21) http://www.forbes.com/2010/11/09/strategic-thinking-innovation-creativity-leadership-managing-rein.html A short Forbes article on how to improve strategic thinking

22) http://www.ppssuccess.com/PPSMentoringServices/MentorArticles/MentorFullArticleDisplayPage/tabid/521/smid/2512/ArticleID/72/reftab/520/Default.aspx This page goes through how to conduct SWOT analysis in order to understand where a business is at and to develop a business strategy from the results.

23) http://www.evancarmichael.com/Business-Coach/223/Strategic-Actions-For-Learning-How-To-Think-Strategically-According-To-Your-Strategic-Thinking-Business-Coach.html A good set of steps on putting strategic thought into action – transcript along with video.

24) http://www.strategyskills.com/Articles_Samples/What_is_Strategic_Thinking.pdf A pdf on strategic thinking and a useful set of pitfalls to avoid

25) http://www.ceffect.com/blog/strategic-thinking/five-elements-of-thinking-strategically/ A really good set of five elements of strategic thinking that are essential to think about

26) http://humanresources.about.com/od/strategicplanning1/a/implement_plan.htm There are a selection of articles on Strategic planning on about.com and here is one on how to implement a strategy across a business

27) http://www.mckinsey.com/insights/strategy/becoming_more_strategic_three_tips_for_any_executive An excellent article from McKinsey again on how executives can think more strategically.

28) http://simplysuccessful-llc.com/think-strategically-and-act-tactically A really useful detailed tutorial here on how to develop strategic thinking. Includes plenty of models and diagrams to visually represent the thought process

29) http://www.intelligenthq.com/resources/50-models-for-strategic-thinking-or-how-to-start-think-strategically-on-a-daily-basis/ An interesting list here of the blog writer’s top four strategy models. It is in reference to the book ‘The Decision Book: 50 models on strategic thinking’ by M. Krogerus and R. Tschappeler

30) Finally this is a short clip from the Capture Your Flag series on Youtube – an excellent channel which gives inspirational messages via interviews with people at the top of their professions. Here, consultant Audrey Parker says that being a listener rather than a talker is a much better strategy for being a consultant.

 

If you’re looking for a book to supplement this list, take a look at

– Thinking Strategically, the Pocket Mentor guide from the Harvard Business School Press (2010) – the HBS is always a good source of business info and developments

– Thinking Strategically: The Competitive Edge in Business, Politics and Everyday Life by Avinash Dixit and Barry Nalebuff (1993) – voted into the Top Ten Financial Times Bestsellers list. It is more of a guide in how to beat rivals but businesses obviously have to understand these tenets to outdo their competitors

– The Boston Consulting Group on Strategy: Classic concepts and New Perspectives (2006) – BCG has been at the top of its game for 40 years and this new edition of their strategic techniques brings together new ideas, insights and lessons. It is set out in the form of articles that were published in the Harvard Business Review or for clients so can be quite dense and challenging but is meant to expand your knowledge.

– The McKinsey Mind: Understanding and Implementing the Problem-Solving Tools and Management Techniques of the World’s Top Strategic Consulting Firm by Ethan Raisel and Paul Friga (2001) – This book is part of a trilogy of books printed by McKinsey that outline the way in which consultants work within their company. With real-life examples of strategic thinking in action within a consulting company, this is a definite must read!

– Be Your Own Strategy Consultant: Demystifying Strategic Thinking by Tony Grundy and Laura Brown (2001) – recommended for MBA courses, aimed at company chiefs trying to use strategic thinking for their businesses without having to use outside management consultants…but as an aspiring consultant you can take all the tips for the advice you would give on your projects!

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.

Understanding Volume and Price

‘Volume’ and ‘price’ are two of the most important things in any business. If there is too much volume bought or sold at too high or too low a price, the finances of the entire company can rocket or plummet. Consultants often offer advice on how to cut costs and therefore drive profits up as well as analyse sales and stocks to work out the most efficient ways of selling products.

So what does the consultant need to know? Read the next 10 links and you should get a swift idea…

1) http://www.caycon.com/blog/2011/02/ten-top-product-pricing-models-for-startups/ An article giving advice for start up companies on ways of pricing their business models – different models work for different businesses remember!

2) http://www.marketingmo.com/strategic-planning/how-to-develop-a-pricing-strategy/ Not sure how to develop a pricing strategy? Read this great detailed account of how to do it with examples. If you sign up to the website for free, you can even get further step by step guidance on pricing strategy

3) http://www.tudorbusinessconsulting.com/blog/category/optimal%20pricing The first in a ten part blog series on strategic pricing.

4) http://quantaconsulting.com/article1.php The director of Quanta consulting explains here why volume and price are so important for a business

5) http://hbr.org/1992/09/managing-price-gaining-profit/ar/1 Price management is majorly important to gain profit – Harvard Business School explains why

6) http://blogs.hbr.org/2013/10/when-it-is-wise-to-offer-volume-discounts/ When is it good to offer discounts? Harvard Business School again, with an interesting insight

7) http://hbsp.harvard.edu/multimedia/flashtools/pricing/ Not sure how to calculate your pricing? Harvard offers this flash tool to help you work it out easily!

8) http://www.carlsonschool.umn.edu/Assets/71544.pdf What happens when competitors raise or lower their prices? This  important article from the Harvard Business Review in 2000 explains ‘How to Fight a Price War’ and gives varying opinions on what the best action to take.

9) http://ernesto.villanueva.tripod.com/estfomato.pdf Academic article from Indiana University on the importance of pricing in business.

10) http://www.imshealth.com/ims/Global/Content/Insights/Featured%20Topics/Emerging%20Markets/Magazine_Asia_softcopy_Price_Volume3.pdf A short pharmaceutical study on volume and price in this sector of industry – includes a BCG growth share matrix.

 

Tips for Calculating Market Share – the BCG Matrix

Market Share is an important indicator of the strength of a business within its industry. This is an important calculation for consultants to use in order to help companies analyse and allocate their resources for maximum efficiency and profit.

In the 1970s the Boston Consulting Group (BCG) developed a new way to represent market share by plotting a scatter graph which ranked business units/products against their relative market share (i.e. growth rate). For simplicity, BCG named the different quadrants the following:

• Cash cows are businesses that have a high market share (and are therefore generating lots of cash) but low growth prospects (and therefore a low need for cash). They are often in mature industries that are about to fall into decline.

• Stars have high growth prospects and a high market share.

• Question marks have high growth prospects but a comparatively low market share (and have also been known as wild cats).

• Dogs, by deduction, are low on both growth prospects and market share.

BCG_Matrix

This method therefore provides a graphic representation for an organisation to examine different businesses that it owns on the basis of their related market share. This is now known as the BCG Growth Share Matrix and the following articles go through how to make one…

http://www.strategicmanagementinsight.com/tools/bcg-matrix-growth-share.html An article that explains what the quarants mean and how to fill out the matrix along with an assessment of the pros and cons of using this matrix.

http://managementstudyguide.com/bcg-matrix.htm Another worked through example of the BCG matrix

http://www.businessnewsdaily.com/5693-bcg-matrix.html Short Business Daily article that goes through the quadrants thoroughly

So now it’s time to learn how to make the market share calculations and graphs in Excel  (which as we know by now, is a consultant’s best friend…!)

Read this screen shot tutorial http://best-excel-tutorial.com/56-charts/176-bcg-matrix

And then have look at this useful Youtube video:

And you’re good to go, you Market share expert you!

Tips for calculating Market size

Market Size is the measurement of the total volume of a given market. In simpler terms, it’s the general size of the market in which a business is placed in. Consultants use this calculation a lot when doing research for clients and creating forecast reports and it is often a question asked in case studies and interviews to see how your financial head for numbers and business sense works.

There are several ways to think about approaching such market sizing questions and practice of course makes perfect! So get reading the help sheets below, follow the worked through examples and have a go at the practice questions!

1) http://www.academia.edu/4597326/Harvard_Business_School_Management_Consulting_Club_Case_Interview_Guide_Harvard_Business_School_Management_Consulting_Club_Case_Interview_Guide This is a pretty helpful document from the Harvard Business School on how to tackle case interview questions. Includes lots of flow charts to try and visually organise your strategic thinking when having a go at the problems. Consists of a lot of questions to help train your pattern of thought rather than worked through examples though. Can also be downloaded as a pdf for printing

2) http://www.docstoc.com/docs/73338650/Mckinsey-Market-Sizing-in-Consulting-Companies-Questions A slide show style document going through some McKinsey case interview questions based on market size. Includes a list of some of the common mistakes which you should be aware to avoid!

3) http://businessorientedwomen.com/wp-content/uploads/2013/01/ace-your-case-iii-market-sizing-questions.pdf Here is the Wetfeet guide to Market sizing questions in the case interview that you can download for free!

4) http://managementconsulted.com/case-interviews/case-interview-question/ Using an example of how many baseballs fit inside a Boeing 747 often asked in the case study assessment, this shows how to calculate market size.

5) http://www.mconsultingprep.com/case-interview/interviewer-drive-cases/market-sizing-guesstimate/ Explains the ‘4-step approach’ to these questions and some worked through examples.

6) http://www.mconsultingprep.com/market-sizing-guesstimate-case-question-tips-and-tricks/ Another post with 5 useful tips on market sizing

7) http://www.simplythecase.com/the-market-sizing-question-in-3-steps/ This blog has several useful posts on the consulting case interview but here is a useful 3 step market sizing post.

8) http://www.consultingcase101.com/case-interview-questions/market-sizing/ Loads of worked through market sizing questions here, though to read them all you need to fork out a subscription of $6.99 a month

9) http://www.wallstreetoasis.com/forums/how-best-to-prepare-for-market-sizing-questions A post on a forum asking for help on prepping for the market sizing questions has some answers from those who have been through the process with examples of what they were asked.

10) http://www.orcinternational.com/US/Insight/recorded-webinars/Documents/Market%20Size%20Estimation%20Whitepaper.pdf A more technical look at market sizing here offering a new way to calculate and analyse this data.

Here an LEK consultant goes through the market sizing question in the case study

Finally, here is a video on tips about measuring markets – aimed slightly at start-up companies but still a short useful set of info

 

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