Sales budget planning for building services

How to create outlook signature

[Music] hello good people and welcome to finance skills help here we learn we connect and regrow in a short video we are going to post the solution to our earlier challenge on how to use absolute missed and relative reference to speed up your calculation our goal in this video is to fill up an annual budget in less than five minutes using these tricks so if you missed the earlier video you can watch it using the link above follow the solution and have a complete set to practice with please join me in excel and let's go through this so this was our challenge we have this annual budget template our goal is to use the assumptions in the first month which is january and then project for each month all the way to december and then when we are done we put in our annual hotels so we have revenue cost of goods sold and expenses down here now if you look at this carefully you realize that there's a certain pattern to the calculation so example if i take first month january i'm going to project february's numbers using this rate okay and then i will use match to also do the same on february now if i do this you realize that the calculation for february will be the same as the calculation for may okay august and november in terms of layouts okay because this these columns i've selected are the second month in each okay in each quarter so the idea is if i'm able to determine the pattern for the first row and i calculate for the first row i'm able to now copy down all the rest of the cells down okay so that's the trick we are going to use we calculate the first row and then you copy down but in calculating for the first row what we need to do is to make sure we hold this rate which is our quarterly inflation rates such that if we copy down it doesn't move down with us we learned that you can use the dollar sign to lock that row so it doesn't go down so armed with this trick what we are going to do is to calculate just the first row and then we copy down and we are done so my five minutes starts from now but i'll be talking slowly so that you're able to follow along even though i aim to be this in five minutes so the first calculation is going to be equal to the january sales or revenue i open my bracket one plus and then i take my rate here so i'm going to lock the row so f4 f4 the dollar sign against route 10 and i close my bracket i get the calculation for february for match i take february multiplied by one plus and then i go take the same quartus rate f4 f4 and then i get this so i have the first quarter now standing the first quarter i will just press alt equal to to give me the sum for these three when i come to april i do equal to match and then i multiply by upping brackets one plus the rate for the second quarter f4 f4 and then enter essentially these are all the calculators i need to do because april will be the same as july july will be the same as october and so on so with this what i'm going to do is now take the calculation i did for february and march and even add the subtotal here so ctrl c then i come to may june and then i'll paste ctrl alt v f for formulas and i have something like this and i have a full set so i'm going to take april may june and second quarter ctrl c to copy stand in july ctrl alt v f paste this october ctrl alt v f the control v is just paste special f is paste formulas and then enter and i have that so i've done for the first row and i'm now in my annual total because i've been consistent what i'm going to do is i'm going to stand here okay and then press alt equal to alt equal to excel is smart enough to know that i expect the quarterly totals so i have something like this my first row is done and then now i'm supposed to copy so i'll just take this ctrl c highlights this up this side which is the revenue 2 hold ctrl select this side hold ctrl select the expense side all the way down here okay ctrl alt v f for formulas and then i press enter so i'm now done with my real calculations now let's subtotals i'll highlight the whole of this alt equal to highlight the whole of course of goods sold both equal to my gross profit i'll highlight the whole of this equal to my gross revenue minus cost of goods sold ctrl enter because i've selected a bunch of cells and then finally i'll come down here highlight this alt equal to and i have that my final net profits i'm going to highlight the whole of this do an equal to and then i'll take my gross profit minus my total expense control enter now i have my 500 000. sure i was able to do this in five minutes even though i was talking through the idea is that if you have calculations you are supposed to do just understand the layout pick a pattern decide which row or column should not move as you copy and paste your formulas and for a layout like this you should be able to get it done in under five minutes as usual if you want to practice you can access the workbook and solution in this folder i'm going to share it so take your time go through you have any questions you can send it to this email and we can learn together so thank you for watching for more of these short videos you can send ad to our whatsapp number will add you to our broadcast list so you receive videos on mondays and fridays directly on your phone all our old videos are on our youtube channel finance closer please visit and subscribe for notification of new videos or connect with us on any of these social media handles thank you so much for watching