There have been a lot of posts on Excel on this blog…so for all of you that can’t get enough of Excel, here are some hilariously nerdy jokes. They really are awful, but hey…
Sales skills are so important in both freelance and company consulting in order to attract new business. For the clients you have secured, they will expect high class pitches to explain the solutions you are offering. If your pitch flops with a poor performance, you can be sure you won’t be on the recruiting list for the best projects in the future. But if you can make yourself the most persuasive and convincing public speaker with the best structure and control of a presentation, no matter who you are pitching to, you will be an indispensable member of any team that is being recruited.
So have a read of the following 20 articles and improve those all-important skills!
1) To start off, if you can pitch yourself in 60 seconds then you can probably pitch anything. Craft your perfect ‘elevator pitch’ with ManagementConsulted’s 10 do’s and don’ts http://managementconsulted.com/consulting-jobs/10-dos-donts-delivering-elevator-pitch/
2) Following the arc of a consulting project from pitch to strategy to execution, consultant Benjamin Estes offers some good consulting specific advice http://moz.com/blog/planning-for-success-three-essential-consulting-project-plans
3) A really great help sheet from a consultant at Microsoft on how to develop, perform and follow up on a pitch effectively and the questions you should be asking when writing it http://office.microsoft.com/en-gb/project-help/develop-and-pitch-project-proposals-HA001171775.aspx
4) How do you give the perfect pitch? This is the most comprehensive yet concise set of sheets on how to do just that, including the Trojan Horse and Pizza analogies. In pdf format to print off easily too http://supplychainventure.info/PDF/WhitePaper.pdf
5) It’s not just your words but also your body that can speak during a presentation. Forbes lists the top 5 ways to improve your body language during a pitch http://www.forbes.com/sites/carolkinseygoman/2013/10/23/5-body-language-tips-for-pitching-to-investors/
6) The Harvard Business Review offers an interesting article here on how to ensure your pitch is pigeon-holed into the correct corrective box – the key is to make the client feel like a collaborator in the process rather than being dictated to http://hbr.org/2003/09/how-to-pitch-a-brilliant-idea/ar/1
7) Building a rapport with the audience is so important in a pitch. IBM salesman Shamus Brown gives his top 5 Sales presentation tips here http://ezinearticles.com/?Shamus-Browns-Top-5-Sales-Presentation-Tips&id=4076 He has a persuasive way of writing down his pitch on his website to sell his Persuasive Skills CD (for the princely sum of $297) http://www.persuasive-sales-skills.com/
8) A great top 10 list on how to give the best pitch from CNBC’s research taken after talking to investors and entrepreneurs. http://www.usatoday.com/story/money/business/2014/01/12/10-tips-for-making-the-perfect-pitch-cnbc/4396281/
9) Another list of top 10 pitching tips, though this one includes how appearance can make a difference as well http://www.claretyconsulting.com/it/comments/sales-pitch-top-10-tips/2009-09-30/
10) A short little article by the Institute of Management Consultants USA on how to make your pitch stand out against all the rest with titles that hook you – and this article title ‘Management Consulting is Like Sex’ certainly does it as a good example! http://www.imcusa.org/blogpost/334056/136001/720-Management-Consulting-is-Like-Sex
11) How do you win more clients to ensure a pipeline of business? Follow these 8 tips http://www.business2community.com/strategy/eight-tips-to-deliver-a-successful-pitch-and-win-new-clients-0142286#!CpDNy
12) What about the client’s point of view on a pitch? Based from experience as a VP at an ad agency but still relevant for consulting, here are the top tips from the client’s perspective on what they look for in a pitch http://adage.com/article/small-agency-diary/helps-pitching-client-s-point-view/235576/
13) Don’t think you’re selling emphasis is strong enough? Take a look at Selling Power’s sales management top 10 tips http://www.sellingpower.com/content/article/?a=10089/10-tips-to-improve-your-sales-performance
14) Looking for a simple template for how you can get the important part of a pitch across? The sentence starters for the top 6 tips here will be useful http://www.managingamericans.com/BlogFeed/Sales-Business-Development/Is-Your-Sales-Pitch-Working-6-Steps-to-Open-More-Sales.htm
15) Are you a consultant branching into freelance work and looking for your first clients? Take a look at this About.com entrepreneur tips page to bag the clients and generate business with your pitch http://entrepreneurs.about.com/od/salesmarketing/a/firstclient.htm
16) Although based at website SEO consultants, these detailed 17 tips from the client perspective here can be applied to any sales pitch http://www.globalsources.com/ST/SEO-consultant
17) It’s not about bragging about all the success stories of your past – new clients will want to know how you can help their particular business. Take a look at Rick Conlow’s simple 5 steps to writing a good sales pitch http://wcwpartners.com/sales-2/salespitch/
18) The Guardian offers some good advice on creating a good pitch here and emphasising how important all aspects of it are to the overall performance, from the brief to the goodbye http://www.theguardian.com/small-business-network/2013/nov/08/top-tips-pitching-for-investment
19) Based on the experience of small businesses pitching to investors but the principles remain the same in this New York Times blog post http://boss.blogs.nytimes.com/2012/10/31/making-your-best-pitch/?_php=true&_type=blogs&_r=0
20) Pitching isn’t just about the presentation but also about the proposal itself and how well written it is. Follow Marketer/Advertiser Brandon Eley’s advice here on how to ensure your proposal and pitch are up to scratch http://www.sitepoint.com/the-pitch-is-as-important-as-the-proposal/
21) And finally, Wikihow actually has some good consultant-specific pitching tips to gain and nurture business http://www.wikihow.com/Get-Clients-for-Consultants
In the consulting world, personality is a big factor in the business – the way you present yourself can really transform your relationship with your clients. And if you’re a freelancing consultant starting your own business, then personal branding is even more important to get your name out there as the one person companies need to hire for their projects. First impressions are really important so make sure your personal brand is saying what you actually want it to say…!
Watch this 4 minute keynote speech by the personal branding guru William Arruda on ‘Personal Branding for Project Managers’
He also has an excellent Youtube channel with plenty more videos with specific advice https://www.youtube.com/user/williamarrudaReach/videos
Central to initial first impressions of your personal branding is the ‘elevator pitch’. This is around a 60 second round up of yourself and your achievements that can be crafted to get all the essential information about yourself across in a short and succinct way, such as at an initial introduction at a networking event – or indeed on an elevator ride! Take a look at the following 5 websites for some tips…
1) http://www.inc.com/guides/2010/05/mastering-business-elevator-pitch.html A good article here on what is important to say with a list of books to read at the end to get more inspiration
2) http://www.boardroommetrics.com/blog/four-personal-branding-tips-to-help-shorten-your-elevator-pitch-20131129.htm#.U0gmX_ldXD8 Not sure what you should say? Some good questions put here to help you realise what’s important
3) Finding it difficult to squeeze all you want to say in 60 seconds without speaking too fast? Take a look at these 4 tips to help shorten the pitch.
4) http://rfmoran.hubpages.com/hub/Your-Elevator-Talk-Does-it-Bore-People-or-Make-Them-Interested Worried your pitch won’t interest anyone? Read the examples of ‘bad’ and ‘good’ responses and see how small changes in the way you say things can dramatically alter whether the listener wants to hear more.
5) http://blogs.hbr.org/2012/01/why-you-need-a-better-elevator/ A good explanation from the Harvard Business Review about what an elevator pitch is and how to make it better.
6) http://www.smartsavvy.com/smavvy-blog/2013/aug/what%E2%80%99s-your-story An excellent scenario setting of how people react when asked to ‘tell me about yourself’. The 5 step breakdown of what the pitch should include is really helpful.
There’s even an interesting article in the Harvard Business Review that your smartphone might be the key to making your elevator pitch stand out in the future… http://blogs.hbr.org/2010/08/smartphone-transform-elevator-pitch/ and another that a ‘dumbwaiter’ pitch is perhaps more important than the ‘elevator’ one http://blogs.hbr.org/2010/04/forget-your-elevator-pitch-wha/
Elevator pitches aside, personal branding extends to online presence and general demeanor. How do you build upon your personal brand? The following websites give some of the best tips…
1) http://blog.brandyourself.com/how-tos/personal-brand-building-10-steps-to-define-your-unique-personal-brand/ 10 steps on how to brand yourself. Includes two worked through examples. This website also offers a service of managing what comes up when you are searched on Google.
2) http://www.transformleaders.tv/market-your-personal-brand-in-five-easy-steps/ A website aimed at leadership for women, to encourage them to not be reserved in corporate environments. Includes an article on ‘To be or not to be humble’ with a very appropriate quote from C.S. Lewis that “Humility is not thinking less of yourself. It is thinking of yourself less”
3) https://blog.kissmetrics.com/personal-branding-seo/ 14 tips to ensure your personal brand rates high on Google listings!
4) http://www.careerdirectors.com/members/articles/r26.pdf A worksheet to help you work out your personal brand
5) http://philanthropy.com/blogs/good-advice/10-traits-of-a-great-consultant/488 What makes a great consultant? Better find out to ensure you’re branding the right traits in yourself!
6) http://www.foreignpolicy.com/posts/2009/11/01/ten_timeless_tips_to_becoming_a_management_consultant Light-hearted but with some useful ideas.
7) http://www.cio.com/article/733782/9_Steps_to_Build_Your_Personal_Brand_and_Your_Career More aimed at a business and how to fulfil a great atmosphere with the best people – but consulting is about that too as a project manager and their team!
8) http://socialmediatoday.com/feldmancreative/2230121/i-am-what-i-am-personal-branding-tips-michael-hyatt-and-me An extensive article going through plenty of personal branding techniques – again relevant to people starting businesses but most of these apply to consultants too.
9) http://despreneur.com/personal-branding-tips/ Another Top 10 countdown for personal branding including an interestying Tedx video with Simon Sinek explaining how great leaders inspire action.
10) http://theundercoverrecruiter.com/how-build-your-personal-brand-linkedin-21-useful-tips/ Still unsure how to use LinkedIn’s full potential? These 21 tips will show you how to boost your personal brand on this up and coming site.
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!
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!
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
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).
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
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
See this short video here too
Day 23 IF AND OR
A short explanation of how IF statements work with AND/OR
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.
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
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.
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
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).
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
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.
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/
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
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.
Consultants are always on the go and it often feels like every second counts. These days, ipads and smartphones are becoming more and more useful in enabling us to continue our work lives at any point of the day. This may be slightly frustrating, but it is no doubt useful.
Being able to access all the Big Data systems such as SAP software from your smartphone means that whilst travelling you can continue the work that you would do on the office systems.
Smartphones are already becoming more powerful and capable of performing tasks formerly reserved for the computer. A recent amusing poster on the tube in London showed the new Nokia Lumia 925 with the full Office suit entitled ‘Excel in Bed’
No doubt this is a major development in how consultancies are working and will continue to work in the future. SAP has started to develop enterprise apps in 2014 and some of the options include:
- CRM Service Manager
- Rounds Manager
- Strategy Management
- MMX 365
- Push 365
- Cart Approval
- Infrastructure rapid-deployment
But there is still a long way to go as Charles McLellan reports here http://www.zdnet.com/enterprise-mobility-in-2014-app-ocalypse-now-7000028499/
Let’s see how far the developers can take these EM apps!
Consultancy companies often encourage their employees to undertake an MBA (Master of Business Administration) at some point during their career. For those who went into consulting as a fresh graduate, this is a chance to put some business academic perspective onto the work that you have been working on over the last few years of your training. Companies like to encourage MBAs as of course for those who return, they are more qualified and able to take on more senior positions and offer better advice to the clients. And for the student, there are the benefits of career progression, networking and higher salaries.
Thinking about taking one? Take a look at these websites for some advice…
http://www.thecompleteuniversityguide.co.uk/mba/ Huge range of information here, including lists of all places in the UK and abroad that offer the MBA. Read the real life story profiles, how to apply and the benefits of taking one
http://www.positionignition.com/blog/2011/2/7/top-ten-mba-business-schools-in-the-uk.html A ranking of the top ten MBA schools in the UK
http://www.accessmba.com/?id=2130 A nine step guide to choosing your MBA
http://www.ft.com/cms/s/2/7aeb423c-7d0e-11e3-a579-00144feabdc0.html#axzz37XxUIqU9 The Financial Times offers a list of things to consider when thinking about an MBA
http://www.telegraph.co.uk/education/educationadvice/9683232/Choosing-an-MBA-get-with-the-programme.html An article as part of the Telegraph’s focus on MBAs in their education section. Plenty of points to consider in these articles
http://howtomba.com/how-to-find-your-mba/mba-programs Here is a website offering profiles of the top MBA institutions in America
http://www.collegexpress.com/articles-and-advice/career-search/articles/explore-careers/choosing-mb-program-and-pursuing-business-career/ Ten points to consider when looking at MBA courses
http://www.theguardian.com/education/2013/jan/23/mba-students-consultants This Guardian article sheds light on the employability of people with MBAs – often as consultants!
http://www.economist.com/whichmba/finding-the-right-mba Article from The Economist with more points to look out for during the MBA search
Thanks to the internet it is even possible to study the MBA entirely online or through distance learning. This could be useful if you are still going to be working rather than taking time off to complete it. The FT ranking of online MBA courses can be found here http://rankings.ft.com/businessschoolrankings/online-mba-listing-2013
‘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.
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.
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!