Create a Consultant Bill Format in Excel for Inventory Effortlessly

Consultant bill format in excel for Inventory

one of the tools you will very frequently use in consulting and many other professional roads as well is excel though from my experience having worked in consulting at mckinsey for six years and having worked with lots of clients from lots of corporations many people really struggle to work properly in excel and especially to format their worksheets in a proper way so this is what we're going to discuss today i will show you how you can format your sheets in a way that they look professional i'll show you a couple of tips and tricks that i picked over time how you can quickly in a nice way make sure that your sheets look proper and nice and good so welcome to another coffee break here on my channel firm learning my name is heinrich and on my channel i want to help you to become successful in the first years of your career so today we're going to tackle this topic in the following way first i will share a couple of general remarks and observations why it is indeed important to format your sheets in a decent way and then i will show you here on my screen on the pc some practical tips what you can actually do to format your sheets in a pragmatic way so let's start why is it even necessary to format your sheets well first it just helps to bring the key messages across an ill-formatted sheet would be much more difficult for yourself or for your clients to orientate yourself in the data it's just less accessible less intuitive to look at but the second reason is that it just helps with the look and feel with the end product quality of the sheets that you produce and indeed in professional services every single document every single sheet that you send out to a client will just be expected to meet a higher level of quality than maybe what would be okay internally in the client organization that you work for many clients walk around with the expectation that what you deliver as a bcg consultant as a bank consultant is just on a higher level just meet quality standards which are higher than the standards that they hold themselves accountable for and indeed many consulting firms have internalized that so most project leads most partners will hold you accountable that you indeed deliver results to the size levels so that being said let me share a couple of practical things what you can do to just improve the formatting improve the look and feel of your sheets so let's just look at this random file let's assume now we want to work with it format it in a proper way to make it client already and indeed if you look at this it looks quite messy it for sure is not the way how it should be if you send something like this to a client and let's just look at what we see here so this apparently is merchandise from different seasons so for instance from an apparel company with the season code the supplier code and then the quantity and the purchase price that is available here so let's get started and the first thing i notice is here that the title of this file copy of book one this is not great right you should never send a file with such a title so let's look into it and indeed save it in the proper way so usually you go with something like this so the date so it start with the year then the month then the day and maybe then something like merchandise overview 2021 version one if we just start doing this so this could be one way and then also notice here this one book so this is also always really something that you would like to name it a proper way so for instance something like merchandise overview to make it consistent so let's start let's continue by looking into this so here you see a mess of different font sizes different font types and so on and so forth so again this doesn't look the way it should so that's all standardize this make it the same font size make it the same font type so at least this is not all in the same style then one little thing that i also like to do is always to insert a little column here to the left and then the buff and then we make this a little bit smaller just quite a little bit of distance this just in my experience gives it a nice and clean look here i don't think we need this underline and then here also look at this title this is like a long messy title so again one thing that i often like to do is to have a main title and then a subtitle so let's potentially call this something like merchandise overview as the main title and then we can call it as a subtitle and just to save this season 2020 and 2021 report power bi then we can delete this this might potentially be bolded now let's make sure that here all the columns are of proper size to space it out something like this next one to do the borders so here what i like to do is always to give the borders here to the main data but not necessarily to the rest so we do something like this and now to make it a bit look better here you go on view and then you take away the grid lines my experience really is if you take away the grid lines it all looks a bit cleaner now here you go for a dark color something like this dark blue and then here the white and then you could potentially also bold this now i would also like to reduce the distance a little bit now next thing we can do is ready to freeze the paints because now currently if i'm scrolling i'm not seeing the headers anymore so you click here view freeze panes freeze panes that this this thing here above is fixed this also makes it look quite nice much better so let's make sure that all these number formats are clean as well so you see that this is in some weird way that this is centered even though this should be aligned to the right so let's just use here the format painter to make sure that this is all set up in a proper way now you can see that this is an id so i would be fine with that i would now continue to format this though the quantity here it would always be good to have some separators right for all the thousand so let's do that now we can see that this is done and here for the purchase price for sure this is something we would like to do as well so here now let's assume we know that this is euro then it definitely would be nice to also include this in such an accounting format with the euro sign so if we now open the old one and now compare it to the new one i hope that you agree that now the new one looks nicer and cleaner to what you had before and indeed this really is quick right now even with explaining it it just took me a couple of minutes and usually of course it's even faster if you just do it on your own now just in addition to that let's imagine have a bit of a more complex layout with maybe different types of tables next to each other's and you'd like to do that as well but here are two tips so my general recommendation would be that if you know start building different tables rather build them one under the other instead of now adding them here to the right this is just the standard way of modeling especially if you have more complex tables with maybe times here in the columns then it's always nicer cleaner to do it from top to bottom than from left to right because then you potentially for instance could also use the same date fields than throughout the table for different lines again there might be some exceptions to that especially if you make dashboards for instance then it might make sense to do it in a different way and here indeed if you would like to learn more about that i once made a distinct video with a couple of tips on how you can build quite powerful and easy dashboards in excel so i will link it somewhere above here if you would like to learn more about that but now back to this because indeed a few you would have different types of tables one after the other so how would you do that right and let's imagine we might have even more so here i would probably do it is i do it exactly like this right so i just here then copy the the title of course this might also be now a different title and then i would leave like one lane distance and then probably also make this distance a little bit shorter and by that now you could build all kinds of stuff one after or one below the other and then keep such a nice clean look for all of this to keep going now let me end with an additional little formatting convention and this is especially important if you're now working with more complex files more complex models because indeed usually it's important also with colors distinguish the input cells from the output or the calculated sets so let's imagine now we would like to calculate the price per unit just here to make a basic example so now probably what we would like to do is just to format this over here just to have it in the same way now we would calculate that by dividing the total purchase price by the units so just like this okay and now what we would love to do is as you can see that this is now a calculated field and now not really an input field right where we just input the raw data and here one convention is to color calculated fields in a dark blue something like this for instance so here now on first side you see that the black cells are cells that you hard copy that you hard input and then the blue cells are calculated once so again this just really helps if you have a more complex larger model to really understand what and all the inputs what are the cells that you hard coded hard typed into it what are all the output sales which probably you shouldn't not directly manipulate but rather their value is derived from the value of some other sets and again if you'd like to learn more i have more videos like this on excel for people working in professional services i will link a playlist somewhere above here where you find all the videos that i currently have on my channel and if you would like to see more content like this subscribe because on my channel i will create more videos on excel in the future as well so now i would love to hear from you do you have any little tips and tricks that you'd like to share to other people here in the firm learning community let me know your tips in the comment section and as you know i will do my very best to answer every single comment that you write if you took away any value at all out of this video please smash the like button for the youtube algorithm and also subscribe to my channel to stay up to date on all my content if you want to see even more from me follow me on my instagram and recently also started to become more active on twitch and on tiktok so you find links to both of these platforms below in the video description so check this out now i also want to give a shout out to all the members of firm learning thank you so much for supporting me for your contribution for this channel this is really making a difference and if you want to become a member as well and benefit from some member only perks hit the join button next to the subscribe button to learn more about this this is heinle from from learning i'm releasing weekly videos every single saturday here on my channel so looking forward to see you again next saturday until then good weekend to all of you this is heimlich bye