Collaborate on Consultancy Invoice Format in Excel for Insurance Industry with Ease Using airSlate SignNow

Consultancy invoice format in excel for Insurance Industry

One of the things I wondered when I was thinking about getting started as a consultant, was how I would need to use Excel as a consultant. And that just led to a broader question. How much do I need to know about Excel, in general. I’m Jenny Rae Le Roux, the Managing Director of Management Consulted, and a former Bain consultant. And my answer to that question should have been you don’t know enough. I was certainly not as proficient in Excel as I should have been and could have been when I started at Bain. I thought that being able to open up spreadsheets and do some formulas would lead me to everything that I needed to know. But there was a lot more to it than that. In this video, I’m going to walk through some of the functions, and some of the techniques that you’ll need to use to apply Excel to solve business problems for large companies and corporations. And our focus today is really going to be on helping you get not the training of how to use those things, but a checklist of what you need to know, and what you might not know so you can better prepare if you’re thinking about a role in consulting. So without further ado, I’m going to go through a couple of the main features and functions of Excel, where I’ll walk through how you use them and why they’re important. The first one is pivot tables. Pivot tables are a mid-range Excel function. It’s not super basic, but it’s also not super complicated. But privet tables take large elements of data and create matrices on them, where you can do calculations of entire data sets without changing or manipulating the data, and without constantly having to re-jigger what data is an input for it. You can also look at multiple things across each other, so you can like at segments of something, and sub-segments. So it gives you the opportunity to do both analysis and calculations at the same time. In addition, one of the most important things that I found in Excel was the ability to use Vlookups, Hlookups, and indexes or matching. What Vlookups and Hlookups do is it enables you to call out the things when you’re looking for data inside a data source, not just taking full sums, or full meanings, or full medians, but actually calling out data that will matter to you, and then pulling just that data into a function. So Vlookups and Hlookups, depending on the orientation of your data, will be valuable in order to pull out select information. And again, this is often used on not just 10 lines of data, but hundreds of thousands of lines of data. The next thing that’s important to understand our arrays. So arrays are matrices that help make your analytical processes more efficient and more clean. If you’ve never used arrays before, there are some amazing tutorials, and we include data on that inside our Excel for consulting course. Macros, in contrast, are an added bonus, but not necessary when you go into consulting. There are some consultants that you will find that use them regularly. They are often coming from engineering backgrounds, or they have extensive and advanced analytical modeling skills. But when you’re just in consulting in general, keeping things clean, not too data heavy, is more important than being able to do advanced manipulations of it. One of the biggest challenges of macros in consulting is that consulting clients often can’t handle it. They don’t know how they work, they get confused by them. Sometimes we work to keep things more simple so that our clients can interpret and utilize them better. Shortcuts for formatting, pretty much used all the time. Those are essential. Converting text to columns, that’s important because often when you get data from a client, or you get it from a spreadsheet, you’ll have multiple pieces of text together, and being able to separate it out so that you can segment it into the key areas. That’s going to be effectively important. Conditional formatting is helpful, although that’s probably the most helpful when you’re working toward dashboards. It’s not super helpful when you’re working in raw data because often you’re actually looking at the scope of the raw data. But in dashboards, super helpful because it gives you the ability to quickly see if there is a red, or yellow, or green. Something’s fine, some thing’s ehhh, something’s really dangerous. IF statements are absolutely essential, you need to understand how they work and how and how IFs and nested IFs work, and IF statements, again, kind of like Vlookups and Hlookups, call out certain pieces of information from a line, or a piece of data in order to pull out what you’re looking for. That’s good for data cleaning, and also good for analysis. MIN MAX functions can help you set ranges when you are doing analysis. You can hypothesize what ranges are, you can play around with them. You can also set them into your dashboards. SUMPRODUCT and SUMIF is also really important because again, if you want to do analysis, you can do IF statements first, and then you can SUM them, but then if you change the IF statements, then your SUMS are going to be different. So SUMIF and SUMPRODUCT are just easier ways to pull the information you want from the raw data. And then finally truncating. Truncating gives you the ability to take a long piece of data and to make it shorter, and then concatenation. It takes small pieces of data and adds them together. If you are not completely confident with all of the terms that I ran through, and their applications, if you’re not able to execute them in a model without looking something up, then you definitely need to work on your Excel skills before day one in consulting, just so you’re not behind. Now I want to walk through a couple of examples of how consultants use Excel. The first example is in the most basic type of model, which is a profit model. But before I start by saying what a profit model is and how it works, the main thing that you need to know about profit models is that the model some element of profitability. That’s where the similarities end. At Bain, I saw 70 different profit models for seven different workstreams that I was on. So you’re going to see profit models that are designed by each individual person, and there is no right or wrong one per firm, and there’s no right or wrong one that works across the firm. So a lot of organizations are going to have this basic approach to how you should do the models, how they should look and feel, but what actually do in a profit model is going to be really determined by the question that you’re trying to answer. This is something that’s a very different from the way that banking, investment banking for other kinds of banking use as Excel, because banking predominantly uses Excel for valuation models, using the DCF method. Valuation models get updated, but they are built in the same way, and they have different numbers of inputs, and different line items for cost, but basically you can replicate them one after the other, after the other. So that the really important thing to understand when you’re thinking about the models in general. The second type of model is an M&A model and again, different from banking, our goal isn’t to define price, our goal is to define optimization opportunities. Places where companies can derive more profit that they don’t have to pay for in the bid price. So our focus is on operating post-M&A, and giving recommendations for what the company should do. The third part is a market study model focused on how you would engage in a market, how you would engage in competitive landscapes inside a market, and overall, what you would do inside that market. So again, like the profit model, there’s not one thing that you’re trying to define here. You’re trying to figure out how you would compete more effectively, and it’s a growth-based model. Because different customers, competitors, or even the products that you are selling into different areas are going to be different, based on the company that you work for. There is no uniform market study model. All of these business problems, you’ll need to be ready to solve using Excel. And there are couple of final rules that I want to layer in for you. Number one, all Excel models in consulting need to be formatted beautifully. This is one thing that I did not understand before I started. I thought Excel was the workhorse, and PowerPoint was the beauty. But when I actually found is the people internally want to see and understand so that they have trust in the model that you built. And if they don’t trust your model, they don’t trust you. If they don’t trust, they don’t trust your data. If they don’t trust data, they don’t trust your slides. So it’s really important to build that chain of trust by having something that looks good. If it looks bad, they’ll question everything. So formatting, on top of of all these things, beautiful formatting. Formatting by firm guidelines are just by general best practices are super important. The second thing is that everything needs to be soft coded. There should be one section where every single piece of the hardcoded data inside your model is held. And that is where all of the base data is. Then there should be one place where you have drop downs, or callouts for the model on the dashboard. But there shouldn’t be any place where you just overwrite data and it overwrites the whole system. Everything should be flexible and flow through the model. And that’s something you have to know as an end-state when you start to build the model. If you get those two things right, make it beautiful and make it work with no errors, you’re going to be well on your way to beginning in Excel for consulting. Now if you’re not comfortable building a working model using pivot tables that you would feel good about handing your manager, then you should definitely consider our Excel training. Excel training, in general, is valuable when you need some guidance and structure to work through an Excel process. But our Excel training covers more than just each of the individual lessons, it will teach you how to do neat tricks. It has advanced modules where you can work on applied business problems in order to succeed in the workplace. If you’ve got questions about this, or any other of our Excel training offerings, we would love to answer them. Just visit us at managementconsulted.com, or on social.