Create and Manage Your Invoice Bill in Excel for Building Services Effortlessly

Invoice bill in excel for Building services

in today's video we will see how we can create Housing Society maintenance bill in Excel this maintenance bill will be fully automated with annual interest in this sheet most of the things will be automated means fully formulated hi my name is vij and you are watching office M let's start the video without any delays in this format we have got few sheets like setting sheet total maintenance sheet total Collections and collections month-wise in month-wise collections we have got most of the calculations on the top we have got drop down for months and on the right hand side we have got drop down for ears few columns over here are highlighted in Gray and few are in white white columns are fully formulated and we have to put our data only in gray celles flat number we will have to insert manually bhk size means size of the house we will have to select from drop down 1 bhk 2 bhk 3 bhk 4 bhk or more than that or less than that so we will select 2 bhk over here once we selected 2 bhk we have got the maintenance amount 2200 when we will change the bhk size the maintenance amount will reduce or increase Bill date over here we will have to insert Bill generation date I'll insert a random date over here 1st January 23 payment date I'll keep this as last date of the month so payment duration got calculated automatically 30 days annual interest rate on delay payment will be calculated automatically so we don't have to insert anything over here occupancy or non-occupancy we have to click on the cell and the drop down will appear we will have to click on the drop down and from here we have to select owner or tenant when we will select owner in non-occupancy charges only zero is visible and when we will select tenant amount of 100 rupes got visible if I'll change bhk size to 2 bhk the amount increased by 50 rupees means now it got 150 when I'll do it 3 bhk it got 200 and when I'll select 4 bhk the amount got 250 so I'll do it 2 bhk over here we can see the payable amount 2350 payment date we have to put the actual payment date over here so over here ideal payment date is 31st January 23 in actual payment date also I'll put the same date payment duration actual was 30 days so there's no delay days and no annual interest and the payable amount is 2350 if I'll change the payment date from here I'll do it February 5th the actual duration for payment was 35 days delay days were five annual interest was 39350 so the payable amount earlier was 2350 now it became 235 6.76 and the interest amount is 6.76 so the interest got added on delay days and that is of 5 days if the delay days will increase interest amount will also increase as well as payable amount will increase we will come to calculation sheet one once the months will be updated the data will automatically feature in the total collection sheet total maintenance whatever expenses are done for society it will appear over here even the income from all the months will appear let's start creating a new sheet now I'm not going to create the format because formatting part takes lots of time if I'll start creating the format it will take another 10 minutes we will see what formulas are getting used in the whole sheet we will we will start from the setting sheet maintenance components maintenance charges water charges sinking fund repair fund miscellaneous in case you have got any other components you can put it over here I have divided this bhk wise means flatwise for 1 bhk the maintenance charges is this for 2 bhk amount is different and for three and 4 bhk amounts are totally different so you have to put details over here then amount B HK wise over here we will put the function of sum Plus or equal to sum bracket open we will select this we'll close the bracket and we'll hit enter so we have got our amount I'll drag this to the right hand side now I'll right click the mouse format sales we'll go to custom we'll remove this double inverted comma rupees RS dot double inverted comma close space has and I'll do okay so RS got inserted just before the amount now I'll click over here Plus or equal to I'll select the amount for 1 bhk means maintenance amount for 1 bhk enter Plus or equal to maintenance amount for 2 bhk Enter maintenance amount for 3 bhk enter Plus or equal to maintenance amount for 4 bhk enter non-occupancy charges 1 bhk I'll take some random figures for 1 bhk I'll take 100 2 bhk 150 3 bhk 200 and for 4 bhk I'll take 250 currently annual interest rate for delay payment is 21% so I'll put 21% over here months I'll put January and we'll hit enter here 2023 and we'll hit enter now I'll select both and we'll drag it down so we have got our months till December so our setting sheet is ready now we will move to Total maintenance sheet over here we have got all the maintenance expenses for the society I have put all the expenses date and day wise I'll show you the formula for days I'll delete this Plus or equal to text bracket open we will select our date comma double inverted comma d d d double inverted comma close bracket close and we will hit enter so we got our dates over here I have taken some random expenses like janitor lift man salary electricity bill water supply electrician electricals for second flow drainage maintenance water pump prepair you'll have to put all your expenses over here and in case you have got any remarks it can be noted in this column amount again I have taken some random figures total the function of formula will be plus or equal to sum bracket open we will select from top to bottom we'll close the bracket and we'll hit enter so we have got our total amount month and year we will create drop down for this we will click just in front of month we'll go to data data validation from here we will select list we will click over here on Extreme right hand side we will go to our setting sheet and we'll select name of the months we'll click over here and we'll do okay so the drop down for months got created I'll go to home and we'll change the font color we'll increase the font size we'll make it bold I'll change the font color for year also we'll make it bold we increase the size now I'll go to data data validation from here we will have to select the list Source I'll click over here on the white part we'll go to setting sheet and we'll select years from 2023 till end we'll click over here once again and we'll do okay so the drop down for years got created we'll go to home and we'll take this in Center we'll take month in Center perfect now we will copy this control C or command C and we'll paste in all the months now we will change the months from the drop down this is January this will be February March and we will do the changes till December done so I have changed the months from January to December now we will copy the formula of sum and we'll paste in all the months contrl C or command C and we'll paste in the month of February contrl V or command V and we are going to do this till December so this is done till December we will go on top now we will click over here on expenses Plus or equal to and we will select January total expenses and we'll hit enter Plus or equal to we will go in the month of February we'll hit enter March Plus or equal to we will go to the month of March and we'll select total expenses and we'll do this till December so all the months got interconnected I'll delete the amounts from here because this is really not required in the beginning I had taken the amount just to check the formulas done now we will insert the formula of sum over here the income column will be blank right now once the total format will be ready we will connect this Plus or equal to to some bracket open we will select from January till December we'll close the bracket and we'll hit enter now we will drag to the right so our total maintenance sheet is ready now except income part total collections paid or not paid we will select this we'll go to data data validation from here we will select list Source we will type yes comma no and we will do okay so we have got our drop down over here now we will select the whole sheet we'll go to home conditional formatting highlight cell rule equal to and over here we will type no and we will highlight our no in Red so we will do okay so whenever we will select no it will be highlighted I'll delete this we'll copy our validation from top to bottom control C or command C and we'll paste in all the months over here in total collections we have got serial number flat number flat owner's name contact number of the owners and email ID over here we have got name of the months from January to December and in each month we have got heads like total due paid and total amount total due I'll take a random figure right now later on we are going to connect this sheet with the months paid or not paid I'll select no total amount the formula will be plus or equal to we will select this means total due enter here we have to put two criterias so we will go to formula bar and we'll insert our criteria if bracket open H6 is equal to double inverted comma yes double inverted comma close comma when it will be yes we want this amount to feature over here comma if not yes we want zero to be visible so we will put zero we will take Zer in double inverted comma we'll close the bracket and we'll hit enter now we will drag this down we will copy our formula and we'll paste in all the [Music] months done it's done till December now we can delete this we'll come to the end we'll click on total received Plus or equal to we will go to the month of January and we'll click on total amount plus total amount of second month means total amount of February plus total amount of March and we will connect this till December plus the Su total amount and we will hit enter we will drag this down outstanding amount Plus or equal to we will go to the month of January and we select total due from here plus total dues of February plus March and we will connect till December and we will hit enter now we will drag this down now we will click over here on top we'll go to formula bar and we'll take all the things in a bracket minus total received amount and we will hit enter now we will drag this down this sheet is nearly ready but we have to connect total dues from all the months so we will have to create 12 sheet for 12 months so first of all we will create for one month and then we will create 12 copies this is the sheet for January over here we have got serial number flat number bhk size Bill date payment date ideal payment duration ideal annual interest rate maintenance occupancy or non-occupancy means who is living in the flat owner or tenant non-occupancy charges maintenance amount actual payment date actual payment duration actual delay days annual interest table amount and interest amount so few things we will have to insert manually and most of the things will be formulated I have already put the flat number over here in case you want to put owner's name you can I'll select this means I'll select bhk size we'll go to data data validation from here we will have to select list Source I'll click over here we'll go to setting sheet and we'll select bhk details means 1 bhk 2 bhk 3 bhk or 4 bhk we'll click on white part and we'll do okay so our drop down got created what we have to insert manually we will highlight in gray flat number and bhk we have to insert manually so I'll highlight this in Gray we'll go to home we'll click over here and we'll select light gray color build date we have to insert it manually payment date also we have to insert manually so I'll select both and we'll highlight in Gray bhk size I'll select two BK Bill date I'll take a random date first of January payment date I'll take last date of the month so what will be the payment duration means ideal pay duration Plus or equal to Ideal payment date minus Bill date and I'll hit enter so we have got 30 over here I'll drag this down I'll click on the top and we'll insert the criteria of if if bracket open payment date is equal to blank for blank I'll insert double inverted comma twice comma what I want in return I want blank so I'll insert double inverted comma twice once again comma we'll come to the end we'll close the bracket and we'll hit enter now I'll drag this down annual interest rate we will connect this from our setting sheet Plus or equal to we'll go to setting sheet and we'll select annual interest rate from here in case it changes in future you will have to do the changes in setting sheet and not in the calculation sheet I'll hit enter it's showing zero we'll go to home we'll come in the center and we'll select percent sign from here or I'll click on percent sign and now we are able to see 21% for now I will leave it blank I will just highlight this otherwise we are going to forget that we haven't inserted the formula maintenance amount now we will insert the function of v lookup over here Plus or equal to V lookup bracket open look up value bhk size will be our lookup value comma we will go to our setting sheet and over here we have got details and amount of all the bhks so we will select from Top we log the sales function F4 the basic reason behind selecting from Top in case our data increases we will not have to change the selection comma the amount is in column number two so we will put two over here comma 0 we'll close the bracket and we'll hit enter now we will drag this down if the data is blank over here we are getting some error so we will have to insert the criteria of if we will go to formula bar if bracket open bhk size is equal to blank for blank we will insert double inverted comma twice so what we want to throw we want to throw blank so we will insert double inverted comma once again comma we'll come to the end we'll close the bracket and we'll hit enter now we will drag this once again perfect owner or tenant we will select this we'll go to data data validation from here we will select list Source we will type owner comma tenant and we'll do okay we will go to home and we'll highlight this in Gray because every time we will have to select this manually I'll select owner for owner there will be no non-occupancy charges but for tenant non-occupancy charges will apply so we will have to insert our formula we will use V lookup over here also Plus or equal to V lookup bracket open look of value we will take dhk size as lookup value comma we will go to our setting sheet for table array we'll select this from Top we'll press function F4 and the sales will be logged comma non-occupancy charges are in column number two so we will put two over here comma 0 we'll close the bracket and we'll hit enter so we have got the amount of 150 when it will be 1 bhk the amount got 100 when it will be 3 bhk the amount got changed to 200 and in case of 4 bhk it's showing 250 so we will keep it as 2 bhk as of now we will have to insert few criterias before dragging this down we will go to the formula bar if bracket open j4 is equal to double inverted comma owner double inverted comma close comma if it will be owner we want zero to feature over here so we will put zero in double inverted comma comma we will come to the end we'll close the bracket and we'll hit enter let's drag this down and see what result we are getting we are getting some error so we will have to insert one more criteria of if we'll go to the formula bar if bracket open j4 is equal to blank for blank we will insert double inverted comma twice comma what we want to see if it will be blank we want to see nothing means we will insert double inverted comma once again comma we will come to the end we'll close the bracket and we'll hit enter now we will drag this down when it's owner we are able to see Zero but when it will be tenant we are able to see non occupancy charges maintenance amount so what will be the maintenance amount it will be maintenance amount plus non-occupancy charges so the formula will be plus or equal to maintenance amount plus non-occupancy charges and we will hit enter let's drag this down we are getting some error So to avoid void this error we will use if error over here if error bracket open we will come to the end comma double inverted comma twice we'll close the bracket and we'll hit enter now we will drag this down so all the errors got vanished payment date we will have to do these entries manually so I'll select this and we'll put gray color remember where we have got gray sales we have to do entries over there only and not in the white sales payment date I'll take a random date payment duration so the formula will be plus or equal to actual payment date minus Bill date and we will hit enter so over here actual payment duration is 30 days and ideal payment duration is also 30 days before dragging this down we will insert criteria of if we will go to formula bar if bracket open actual date is equal to blank means double inverted comma twice we want blank to feature so we will insert double inverted comma once again 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 leave this selected we'll go to conditional formatting new rule from here I'll select classic from the drop down we will have to select use a formula to determine which sales to format and the formula will be plus or equal to this is greater than ideal days we remove dollars it should be highlighted in Red so I'll do okay I'll change the date and we'll see if our conditional formatting is working properly great it's working I'll drag this down I'll change the date if actual payment duration is less or equal to Ideal payment duration it it's not getting highlighted but if the payment was done late it's getting highlighted in red delay days the formula will be plus or equal to actual payment duration minus ideal payment duration and we will hit enter we will change the date from here we will do it 25th of January so it's showing minus 6 we will have to do some tweakings if there will be delay then only it should be showing the figures otherwise it should be blank or zero we will go to our formula bar and we'll insert the criteria of if if bracket open actual payment duration is less than or equal to Ideal payment duration comma if it's less or equal to we want blank so we will insert double inverted comma twice comma we'll come to the end we'll close the bracket and we'll hit enter so we are not able able to see anything if it's paid before ideal payment date we will change the date from here we will do it February I'll change the date from here I'll do it February when there is a delay we are able to see delay days now we will come over here we will remove the red color and we'll insert our formulas Plus or equal to if bracket open delay days is equal to blank we will insert double inverted comma twice for blank comma we want blank if this will be blank we want annual interest rate sales to be blank comma if not blank we want to feature 21% over here means annual interest rate percent to be visible we'll press function F4 and the sales will be logged we'll close the bracket and we'll hit enter we'll drag this down if there will be delay in payment only then 21% will be visible let's change the date and do it January and 21% got vanished so this formula is working properly annual interest you'll have to see this formula very minutely otherwise there will be lots of confusion so the formula will be plus or equal to actual maintenance amount into this is annual interest so we will multiply this by one if it will will be half yearly interest we will have to multiply with 0.5 so maintenance amount into 1 into interest rate and we will hit enter so we got our total annual interest so this annual interest is for 365 days and not for 1 month before dragging the formula we will insert one criteria over here we will go to formula bar if bracket open delay days is equal to blank we will insert double inverted comma twice for blank comma what we want to see we want to see blank so I'll insert double inverted comma twice once again comma we'll come to the end we'll close the bracket and we'll hit enter now I'll drag this down table amount the formula will be plus or equal to annual interest divided by 365 and we will hit enter so we have got interest amount for one day so this is the interest amount now we will have to add this in total maintenance charges we will take this in bracket we'll click in the end of the formula into delay days and we will hit enter so for 25 days the interest is 33.80 we will go to formula bar once again we'll close the bracket over here Plus we have to add our actual maintenance amount and we will hit enter so this is our payable amount now we will have to insert few criterias over here we will go to the formula bar if bracket open delay days will be blank if delay days will be blank is equal to double inverted comma twice comma if it will be blank we want total amount to be visible comma we'll come to the end we'll close the bracket and we'll hit enter now we will drag this down now we will insert our formula for interest amount but before that we will go to our maintenance amount and over here we will come to the formula bar and in the end we will put zero just between two inverted commas if we will not do this we may face some error while adding all the things we'll hit enter now we will drag this down interest amount and the formula will be plus or equal to payable amount minus actual maintenance amount and we will hit enter now we will drag this down perfect so this sheet is ready now now we will come on top we'll select c d and e and we'll merge together we'll go to data data validation from here we will select list we will click over here we will go to setting sheet and we'll select months from here we'll click over here on white part we'll do okay so the drop down for months got created now we will select f and g and we merge together by going in home we'll go to data data validation from here we will select list Source we'll click on white part we'll go to setting sheet and we'll select the ears we'll click over here and we'll do okay so the drop down for years got created now we will create 12 sheet for 12 months we'll click on the month's name we'll right click the mouse move our copy move to the end create a copy okay we'll right click the mouse once again move our copy move to the end create a copy okay and in the same way we will create 12 sheet for 12 months so our 12 sheets got created now we will rename this this is January this will be February March and we'll rename till December now we will go on top and we'll change the month this is December November and we'll do this for rest of the months also so all the things are done now we will be going to Total collection sheet and we'll connect this sheet from all the months total due Plus or equal to we lookup bracket open look up value flat number will be our lookup value over here but before that we will be going to our January sheet once again we'll skipe from here January we will click over here we'll type one and we'll drag this till end this number will be required for our V lookup we need payable amount over there so payble amount is in column number 15 we'll go to Total collections we'll click just under total du Plus or equal to we lookup bracket open lookup value flat number will be our lookup value comma table array we will be going to our January sheet we select sheet from Flat number till end we need paper amount that is in column number 15 so we will put 15 over here but before that we will lock ourselves we'll press function F4 comma 15 comma 0 we'll close the bracket and we'll hit enter now we will drag this down over here we will put the formula of sum Plus or equal to sum bracket open we will select from first to bottom we'll close the bra ET and we'll hit enter now we will drag this till end it's not required in paid so we will delete from here now we will connect our second month plus or equal to V lookup bracket open lookup value flat number will be our lookup value comma table array we are connecting this from February so we will be going to February sheet we'll select from Flat number till end comma table amount is in column number 15 so we will put 15 over here comma 0 we'll close the bracket and we'll hit enter we'll drag this down and in the same way we will connect our sheet till December so all the months got connected now now we will go to our total maintenance sheet and we'll connect this sheet from Total collections Plus or equal to we will go to Total collections and we'll click over here in total amount enter Plus or equal to we'll go to Total collections we'll go in the month of February total amount we'll click over here and we'll hit enter and in the same way we will connect from all the months so all the sheets got interconnected we will delete this and 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