Simplifiez Votre Modèle De Factures Mensuelles Pour Les Services De Bâtiment

Monthly bills template for Building services

[Music] hi everyone i make excellent powerpoint templates to help people get ahead in their career and get the most out of their business or their organization this one in particular is a project budget or actually any budget but the most powerful and the most wonderful thing about it is it's a budget versus the actual cost and it shows it on this beautiful chart here and there's a few little tricks that we'll do with excel so that you can see how to actually do the the budget versus the actual cost on this line graph because it isn't as easy as it seems especially when we're planning for each expense let's say this is a you know buying something here and then it's every second row that we want to tally up but the way this outputs is so beautiful and then you've got this beautiful chart so that shows you exactly how much you had planned versus how much you are actually spending now that you can show to your executives or to your customers let's get into the sheet and of course the first thing we're going to do is just fix up the template itself do the coloring in the borders so then we can dive straight into the formulas and the actual good stuff in our planned versus actual budget sheet we'll make it a nice deep blue for starters but we'll give this one a little bit of white just so we know that here so that people know that they can fill out this section and we'll merge these cells together give that a nice white as well and just put them over to the left but in the middle and indent them a little bit now we can put our budget name in any time we please for our main heading row i think we're just going to give that a nice light turquoise color something easy to easy on the eye and easy to work with and we'll give a nice thick border around all of this just so that we know the boundaries of where we are now it's time to put the dates right up the top and so what we're going to say in our first cell here is equals the start date so we've obviously got to give ourselves a start date as well so let's make it any date that we please if we put that in and maybe we can just change that up a little bit to suit now this first cell is representing that date value but we actually want that to look like a date so we're going to format that cell and we're going to turn it into a date but we can actually we can make it any any sort of date that we want so what we might say is just we actually want it to be the months in a word format and we want uh maybe two of the years as well and so there you can there you see it's march 23 2023 but it could be any year that you're working with for the rest of them we're going to use the edate function so e date and as you can see this returns the serial number of the date that is indicated a number of months before or after the start date so we want it to actually be a month extra month every time so the start date is going to be this one the previous one and then the months that we want to add we'll just add one month to that and as you can see that's going on and we can just drag that all the way across and that should add the months and that will give us a full year for our budget versus actual cost now it's time to look at the look at filling out the actual sheet itself and there's a few little tricks here the first of all we're going to have to merge a couple of these cells on the left hand side just the first two because this one is going to be our one and the numbers we'll merge this as well merge this one as well and merge this one as well and this is going to be the name of the item so this is where we're saying we're buying something all of these let's put them up in the middle so that they look a little bit nicer and this is going to be our budgeted versus our actual place it over to the right a little bit increase the indent and just put it in the middle as well now for all of the values in here we actually want to format that and we want them to be a currency and we're going to click ok and we can start putting a few values in here maybe we've got five six seven and eight that's the amount that we've budgeted for and the actual amount that we've spent might be a little bit less so four dollars five six and seven a dollar under every time when we've got some values in here then we can start looking at the totals on the side so this is nice and easy to do we're just going to use the sum function and it'll be the sum when we open up our bracket of all of this row copy that and then when we copy that down we just want the formulas only let's make this one the accounting format and that way that looks a little bit nicer now if you want to get rid of the dollar signs when they're not in use there is a little trick to that if we format the cells and say custom then you can use the custom formatting tool to actually change that up and that's what it will look like if we select okay then that will actually reduce the dollar sign when we're not using it as well so it just looks a little bit nicer now we want to do some conditional formatting and to do that we're going to do it on every second row so it's on the actual because if it's above our budget we want it to be red but if it's below our budget we want it to be green so we go to conditional formatting and we manage our rules we select new rule and we say format only cells that contain the cell value and it's going to be greater than the cell before it the font style can be read for that one and we'll click ok and we'll apply we'll also add a new rule where the cell format only cells that contain cell value to be less than the cell before it as well and the color for this one will be green now when we do this we just want to take the dollar sign out of the e because we're going to copy this across so we'll just edit that edit that and take the dollar sign away apply and click ok now if we take that and we select the format painter and if we choose all all over the rest of our other ones then that's going to apply really nicely to the rest of our row and when we've done that we can see that each of these will be will change so if we have five dollars but we've budgeted and then we have actually spent six dollars then that's going to turn red for us so we can clearly see when we're going over budget this is really great but of course we want to copy this and we want to copy just the formatting as well and for each of those we can copy down the formatting and if we copy that again we're just going to select that we can easily copy down the formatting so if we select five dollars here but then we've actually spent nine dollars then that's going to be red for us and so that's a wonderful way to show if we're on budget if we're on track but it's just messed up the lines ever so slightly so now we can really go into here and we can actually fix up the lines for our particular sheet and if we take the view away and take away the grid lines now we can see we've got uh got that looking a little bit better for us but we still want to put a border in between so we're just going to select that as well there we go now we're going to start looking at our total budget and our cumulative budget and our total actual cost and our cumulative actual cost and we're going to do something a little bit sneaky here we're going to just put the word budgeted for these two and we can put that but we can turn that white just so that it's hidden and we're going to use the same for actual put it in the middle and just turn it white so it's hidden but that way when we actually filter these columns it will still show up all of the budgeted sections including the total budget and all of the actual sections including the total actual sections now what we want for our total budget and our total actual cost is actually a little formula and it's an array so an array if you're using the latest version of excel you don't have to press ctrl shift enter but if you're using previous versions of excel you will have to press ctrl shift enter and what we're doing is we're using sum so we're adding up all of the values but it's only for every second or every first row for this particular one so when we're dividing it by two it actually equals zero and that's how we know every every first row that we're going to use here is going to be the one that it's returning and so that's the formula that we're using and if we press ctrl shift and enter that's going to give us the the total for just the budgeted sections again for all of these if we format these and make it that custom view again and there's the custom formula there fairly easy to copy but if we select that then that's going to just give us a nice clean slate if there's nothing there and if there is something there it's going to return all of those functions for us now for the cumulative budget we can simply say the first one equals the first cell because that's that's the first one but then we want to keep adding and we want the maximum value out of all of them added up together so we want it to be keep adding and adding and adding on top of each other like a snowball and in order to do that what we're going to do is we're going to say if if this item is is there's nothing in it then we're going to return n a and that's really important for the chart that we're going to show you in a second because if we've got n a it will actually will just leave a blank on the chart so it's going to make the chart look a lot nicer which you'll see but then if it's not blank we're going to return the highest so the maximum value plus the new value as well and if we if we just press enter there and we can easily scroll this across and pull this across and now this is continually adding to each other and giving us the highest total value so it's our cumulative budget you'll notice that it has a whole bunch of nas here as well which is which we do need for the chart but we don't like that showing up on our actual budget so if we do some conditional formatting and say manage rules we'll say a new rule and we'll say if cell contains an error then what we want is for our font and we'll just make our font white to hide that and click ok and apply and that hides all of those n a values so now we get the the important value that they are for our chart but we don't have to look at them because they don't look very nice now we're going to use the same trick for for summing every second row for our total actual cost and if we select control shift and enter then that will give us the value that we want we can easily scroll that all the way across and we can also use the same formula as we used for the cumulative total budget so we've got this one and we'll just scroll that all the way across as well we'll give ourselves the border on the bottom again and now that is really starting to look quite good the only thing we're going to do is just color this ever so slightly differently so we'll give it a nice maybe a bit of a yellow if it's the actual as opposed to our budgeted and our total actual cost of course we can have a little different color there too if we want to and then we'll make them all bold and now we've got the foundation for our budget so this is really great you've done so well and there are so many little tricks to this one but now all we have to do is do the cumulative budget all the way over here and the cumulative cost and select that hold control and select that one as well if we insert a line chart then now you can see we've got this line chart starting to form and we'll just make that a little bit bigger so we can see we'll make the title what we want let's give our values let's make them stand out a little bit more now we can really see the difference on the chart but we're also just going to select this right click and we're going to format this data series and what we want to do is just make the marker we'll give ourselves maybe the marker options and we're going to give ourselves one of these built-in marker options same deal for the other one as well marker options built in nice circular one there close that off and all of a sudden we've got this beautiful project budget chart and we can clearly see where our budget is getting away from us now this will really help you in your company it'll help you in your project because you can really show people how that project budget is going and whether you're on track or on track or off track i hope you've enjoyed spending the time with me and i've really enjoyed spending the time with you creating this project versus budget versus actual cost i'll see you in the next video and bye for now [Music] you