Streamline Your Society Maintenance Bill Format for Research and Development

Society maintenance bill format for Research and Development

In today's video we are going to see how we  can create Housing Society Maintenance format   in Excel. In this format we are going to cover  each and everything like Maintenance Collection,   Total expenses, Occupancy charges,  Non-occupancy charges and so on.   Hi my name is Vijay and you are watching Office  Monk. Let's start the video without any delays.   This is the Housing Society maintenance format  which I have already created in advance. This   is the collection sheet in which we will track  all the collection as well as outstanding amount   on the top we have got maintenance amount with  occupancy charges and without occupancy charges   here we have got the monthly charges and over  here we have got the yearly charges we have bought   total three worksheets for this first worksheet  will be for maintenance calculations where we will   have occupancy charges and non-occupancy charges  whenever the amount over here will be changed   it will feature in our drop down also second  worksheet is for total collections and third   will be for total maintenance charges for  the whole year on the right hand side we   have got months name along with extenses  and below that we have got total collections   if we want to check the total expenses of December  we will have to click over here on December   and we will be diverted to December expenses  to go back to January we will click over here   and we are in the January expenses  let's start creating a new sheet now   fourth tab will be maintenance calculations we  will start from B4 serial number details amount we   will click over here and we'll take all the things  in Center we will insert serial number till 10. we'll increase the column size and over  here also we will increase the column   size we will select this and we'll create borders we will select this and we'll give it a  color we will change the font color will   make the row size bigger we'll select the whole  sheet and we'll take all the things in made   we'll increase the font size we'll select this  and will give it a border now we will select   this and we'll merge it together it will  be total amount we will change the color   we'll change the font color we'll  change the color for this also   and we'll change the font color we will  make it bigger will increase the font   size now we will copy this and we'll paste  it over here we'll increase the column size   we will select this and will merge together we'll  change the color we will change the font color   will increase the row size this will be without  non occupancy charges we will copy this and we   will paste it over here this will be with  non-occupancy charges we'll go to view and   we'll remove the grid lines we'll take it closer  we will insert some expenses along with amount   maintenance charges water charges sinking fund a  repair fund non-occupancy charges miscellaneous   in case you have got some other expenses  you can insert it over here I'll copy this   and we'll paste it over here maintenance  charges I'll insert some random figures   I'll select this will go to home and we'll take  this in left side water charges zero sinking fund   500 repair fund 200 non-occupancy charges this is  without non-occupancy charges so amount will be   zero miscellaneous zero we'll click over here in  total amount and we'll insert the formula plus sum   will select the range we'll close  the bracket and we'll hit enter   we'll take this in Center we'll right click the  mouse format cells we'll take this up custom   with remove General double inverted comma rupees  double inverted comma close space has and we'll do   okay so RS got inserted just before the amount now  we will copy this controls your command C and will   paste it over here this is with non-occupancy  charges so I'll insert a random figure 150.   so this is the amount with non-occupancy  charges and this is the amount without   non-occupancy charges now we will  select this and we'll merge it together   we'll drag it down will create borders we'll  give it a color we'll change the font color   we will copy this controls your command  c will come over here and we'll paste it   now we will copy this controls your command C and  will paste it over here and this will be pending   now we will select this and we'll insert borders  we will change the font color will make it bold   if you click over here we'll insert Plus  or equal to and we select this without   non-occupancy charges and we'll hit enter Plus or  equal to we will select this and we'll hit enter   here we will type pending we will select this and  we'll take this in Center we'll take this and left   we'll do one thing we will select this and we'll  merge it we'll do the same thing for all three we select this and we'll create borders now  we will click over here without non-occupancy   charges we will right click the mouse format  cells and just after hash we will type without   non-occupancy charges without non-occupancy  charges we'll take this in doubly noted comma   and we'll do okay now we will click over  here we'll right click the mouse format cells   we'll take this up and just after hash we will  insert double inverted comma with non-occupancy   charges with an OC double inverted comma close  and we will do okay later I'll tell you why I   inserted without NOC and with NOC now we will  insert a new worksheet we will start from B7   serial number flat number flat owner's name  contact number contact number email ID we'll make   it bigger we'll click over here and we'll take  all the things in Center we'll select the whole   sheet and we'll take all the things in made serial  number we will insert for 20 owners or 20 Flats   we will select this and will create borders we'll  make this bigger we'll put some color over here   we'll change the font color over here  we will type flat on rent or empty over here we will type Jan or January we'll  hit enter and we'll drag this till December we'll come to the right hand side and we'll drag  it till December this will be total and this will   be outstanding or pending now we will select this  and we'll insert color we'll change the font color   will create borders we'll take this and left  we will click on V2 and we'll select till l   will merge it together we'll make it  bigger we'll insert some color over here we'll create borders   now we will select from n to Q will merge it  we'll put some color and we'll create borders   we'll come to right hand side we will click from  s till U will merge it will put some color and   will create borders we'll come to left hand side  this will be flat wise maintenance collection we'll make the font size bigger this  will be here will come to right hand side   we'll select both and we'll increase the font  size we will click over here we'll go to data   data validation over here we will select list and  in Source we will type 2 0 2 3 2 0 2 4 2 0 2 5 and   we'll do okay our drop down for here got created  we will select two zero two three we'll come to   the left hand side now we will select this flat  on rent or empty we'll go to data data validation   we will select list in Source we will  type yes comma no okay now we will go to   home conditional formatting highlight cell  rule equal to over here we will type yes   and we'll select green color we'll do okay we will  go to conditional formatting once again highlight   cell rule equal to and this time we will type no  and we will select the red color and we'll do okay   yes no we will select this and we'll take this  in Center we will leave third row blank will   come to fourth row means B4 will select from  B to D and will merge it we'll give it a color   we'll change the font color we will drag this  down this will be maintenance amount per month   in bracket we will type without non-occupancy  without non-occupancy charges and we'll hit   enter we will drag this down this will be  with non-occupancy charges and will hit enter   we will increase the column size we'll  select this and we'll take this in left   we will increase size of this also maintenance  amount per month without NOC Plus or equal to   we will go to our first sheet and we select this  amount and we'll hit enter maintenance amount per   month with NOC Plus or equal to we will go to our  4 sheet and we'll select this enter we'll select   this and we'll take both the things in Center  we leave it selected we'll right click the mouse   format cells we'll take this up custom will remove  General has space double inverted comma monthly   doubly noted comma close and we will do okay now  we will click over here on F4 Plus or equal to   we will select this into 12 this will be yearly  I'll drag this down we'll select both and we'll   take this in Center we'll right click the mouse  format cells we'll take this up custom we will   remove General has space double inverted comma  yearly doubly noted comma close and we'll do ok   now we will select this and we'll give  it a color we'll change the font color   we'll make it bold we'll select this and we'll  give it a border we'll make this column little   bit bigger will increase size of this we'll  go on top View and we'll remove the grid lines   we'll come to right hand side we'll select  two rows and we will select till outstanding   amount means till end will go to home  and will merge it we'll give it a color   we'll change the font color we'll increase  the font size this will be amount received   slash outstanding we'll give it a border will come  to left hand side we'll take this closure now we   will select all the months from Jan to December  we'll go to maintenance calculation we will select   all these three and we'll remove the merged  field we'll make it bigger we'll remove this if it will be merged we will not be able to create  the drop downs we'll come to C2 will rename our   tab it will be total collections now we will  select the months we'll go to data data validation   from the drop down we will select list source  we will go to our first sheet we'll take this   in right side and we'll select all three we'll  do okay and our drop down list got created   rupees 2200 without NOC means without  non-occupancy charges rupees 2350 with   non-occupancy charges and pending but when we  will select the amount it will be showing only   amount and nothing else we will select this and  we'll take all the things in Center if flat will   be on rent or empty non-occupancy charges will be  applicable so with non-occupancy charges it's two   three five zero and if the flat is not Wicked or  not on the rent non-occupancy charges will not be   applicable now we will come to right hand side  total the formula will be plus or equal to sum   bracket open we will select from Jan to December  we'll close the bracket and will hit enter   now we will drag this down we'll  take all the things in Center   outstanding amount I'll make this bigger  the formula will be plus or equal to if   bracket open this means G8 is equal to double  inverted comma yes double inverter comma close   comma I'll come to the left hand side if it's Yes  means non-occupancy charges will be applicable so   we will select this we'll press function F4 and  the cell will be logged comma we will minus this   we will insert the second criteria coma if bracket  open this means G8 is equal to double inverted   comma no double inverted comma close comma we will  select this amount without non-occupancy charges   will press function f o and the cell will be  logged minus we will select the total will   close the bracket and will hit enter now we will  drag this down will take all the things in Center   we are getting an error over here so we will  insert criteria of if just in the very beginning   if bracket open this is equal to 0 comma double  inverted comma twice means we want blank comma   we will come to the end will insert  the bracket and we'll hit enter   now we will click over here and we'll select  till end we will change the color will change   the font color we will make it bold will  increase the font size we'll create borders   and we will insert our formula for sum Plus  or equal to sum bracket open we will select   from first till last we will close the bracket  and will hit enter we will take this in Center   now we will drag this till end this is our total  amount we'll right click the mouse format cells   we'll take this up custom will remove General   double inverted comma RS means rupees dot double  inverted comma close space has and we will do ok   we will come to the left hand  side now we will select this   and we'll merge it together we'll change the  color to change the font color this will be total   will increase the font size we'll create  borders we will take some random figures so this format is ready now we will click our  last sheet and this will be total maintenance we will start from B4 this will be month  we'll select this and we'll take all the   things in Center we'll select the whole  sheet and we'll take all the things in made   for months we will create drop down we'll go to  data data validation from here we will select list   Source we will type name of all  the months from January to December [Music]   and we'll do okay so our drop down got created   over here we will type here and we'll create drop  down for years we'll go to data data validation   list source and we will type 2 0 2  3 2 0 2 4 2 0 2 5 and we'll do okay   so our drop down list for ER got created we will  select month and year will go to home and we'll   change the color we'll change the font color will  increase the font size now we will select this   and we'll change the font color we'll make it  bold and we'll increase the font size it will   make the column size bigger we'll create  borders now we will select from P to G   and will merge it will give it a follow we'll  make the font size bigger we increase the row size   and we'll create borders this will  be Society maintenance details will increase the font size now we will click  over here in B6 this will be serial number   date day expense details remarks if  any we will take if any in bracket   and over here it will be amount we'll select  this and we'll take all the things in Center   I'll take till serial number 10.  if required you can increase it you will select this and we'll create borders now  we will select this and we'll change the color   we'll change the font color will increase the size  I'll take this closer we'll select from I to K   will merge it and we'll put a color over here   we'll insert borders this will  be total expenses and collection   I'll select this and we'll merge together now  I'll select this and we'll put a color over here   I'll type Jan over here and  we'll drag this till December   we will put color over here we'll take  this closure but I'll cut this and   we'll paste it over here we'll put the  color you will put color over here also   I'll put some color we'll take this closer  we'll make this columns as bigger almost this   will change the font color this will be total  expenses will increase the font size I'll select   this controls your command C and will paste  it over here this will be total collections   we'll insert one row over  here we'll change the color this will be total will change the  font color we'll take this in Center   we will create borders for this will  come down we'll change the color   we'll copy this controls your  command C and we'll paste over here   we'll put some color over here to create  the borders now we will merge this   this will be balance in hand we'll change  the color we'll change the font color   we will merge this also and we'll change the color  we will change the font color we'll create borders   we'll create border for this also our  expenses are till 10 we will increase this will create borders I'll increase the  size of amount now we will copy this   controls your command C we will come down   and will paste for February and in the  same way we will paste till December now we will go on top this is January   so this will be February and will  change name of the month till December total collection we will flick over  here Plus or equal to we will go in   total collection sheet and we'll select Jan enter   we'll take this in Center swab Plus or equal  to we will go to Total collection sheet and   we select Feb and in the same way we will  connect till December Plus or equal to March so all the sheets got connected we  will insert formula of sum over here   some bracket open you will select this  will close the bracket and we'll hit enter   we'll change the font color we will copy  this formula and we'll paste over here   now we will click on Jan we'll go to  formula bar and we'll press function   F4 means we will lock the cells in the  same way we will lock all the cells balance in hand the formula will be plus or  equal to tal collection minus total expenses   will press function F4 and we log the cells and  we'll hit enter we'll insert some date over here   we will select this we'll right  click the mouse format cells date   and we will select a desired date  format okay we'll take this in Center   day formula for day will be plus or equal to  X bracket open you will select our first date   comma double inverted comma D D D D four times  doubly noted comma close bracket close and we   will hit enter before dragging this we will  insert the criteria of if if bracket open   date is equal to 0 comma double inverted comma  twice comma we will come to the end we'll close   the bracket and we'll hit enter now we will  drag this down I'll insert some amount over here   we select this and we'll take this in Center we'll delete this will merge this together  we'll give it a color will change the font   color this will be total we'll change the color  of this also Plus or equal to sum bracket open   we will select this will close the bracket  and we'll hit enter we'll make this bold so we will have to do this for  all the months I'll copy this controls your command C and  we'll paste in all the months I'll remove the amount from here I'll  delete the date now I'll copy this   and we'll paste in all the months so formula got inserted in all the months I'll  take this in left total expenses for January   Plus or equal to we will come down and we'll  select this enter will go in Feb Plus or equal to   will go to February month and we'll  select the expenses and will hit enter   in the same way we will connect all the months [Music]   we will select this and we'll  take all the things in Center   now we will lock all the cells from  January to December function F4 enter [Music]   now all the cells got logged now we  will create hyperlink for the months   January cell number is C4 I'll  write 4 over here this will be C 4 C 4 is January c39 is February 39 c74 is March we will see  what's the difference in this   difference of 35 so difference of  35 is there so plus this plus 35 I'll delete this so this is Jan I'll drag this  down it will be till December I'll delete this   now we will create hyperlink we will delete  this we'll click over here we'll go in insert   link address it's C4 we will go on top and we'll change  the name the name will be January   will do okay now we will click over here  link this will be c39 this will be February and in the same way we will  create link till December [Music] so all the sheets got connected I'll  select this and we'll take this in left   now I'll delete this we'll copy this and we'll paste till December thank you [Music] we will go on top view show and  we will remove the grid lines So our format is ready now. I'll provide  link of this file in description box. In   case you want to download you can. I'm sure  you must have loved the video. Please don't   forget to like share and subscribe .See  you soon with a new topic till then Bye Bye