Effortlessly Manage Your Bill Book in Excel for Administration

Bill book in excel for Administration

if you are working in a hotel or Resort you must be using some software for customer billing in case you are not using any software you must be doing all the calculations either manually or in an Excel sheet in today's video we will see how we can create a fully automated Billing System in Excel and believe me if you will prepare this format no other softwares will be required or just say this format will be working as a software this sheet will calculate all the heads like room rent taxes discounts complementary and many more even the total payable amount of the customers will be calculated automatically hi my name is Vijay and you are watching of small let's start the video without any device this is a hotel or Resort billing calculation sheet which I have already created in advance there are a few Elements which we will have to insert manually and rest of the elements will be calculated automatically in the very beginning we will have to insert customer details like customers name as per ID card he or she has checked in as single couple or with family so this will be managed through drop downs they have submitted their photo ID proof or not these two will be managed through drop downs and color coding is here check in date checkout date total stay it will be formulated advance room or say room type it will be single Villa Deluxe Studio or suit so this thing will be managed through drop downs when we come in extreme right hand side we see total calculations along with taxes and discounts discounts will be managed through drop downs in case hotel or Resort is giving someone 100 discount on room rent but not on food and bus services it will be automatically calculated some Neo and dear ones get all the things complementary or on the house in that case neither room rent nor food and bar services will be charged means all the bill will be complementary or zero we can see 0 over here means total payable amount is zero here we see room details and their charges in case room rents get revised we will have to update over here and it will be automatically changed in the sheet here our room charges charge for single room is 2500 if I'll change it to 2000 here also the room charges got changed few elements will be highlighted through color coding or conditional formatting in short if we say this sheet will be combination of formulas data validation and conditional formatting if I say today we are not going to prepare a format today we will be making a small software it will not be wrong now we will start making our small software we will have to open a new sheet we will start from here and we will type serial number name as per ID card single couple or family photo ID check-in date checkout date total stay advanced room type room charges full charges bar charges room tax food tax bar tax we will insert some percentage over here 15 16 20 I have taken generic figures over here discount over here percentage we will come in the right side discount amount total payable now we will select the whole sheet and we will double click over here and all the cells will be managed we will change the font style we will come in left side we will select from here till end and we will merge it [Music] we will select a color we will change font color and we will type name of the hotel foreign Style and we will increase the size now we will merge this [Music] will come in right side and we will merge this also we will select the whole sheet and we will take all the things in center and in mid we will do this bold and we will manage this we will give some color over here we will give some color over here also over here we will put some color we will take this in left we will insert borders we will prepare this for 15 customers and we will delete the rest we will go in View and we will remove the grid lines we will come in right side and over here we will insert name of the rooms and rent of the rooms single double suit Studio Deluxe Villa now we will insert the rent we will insert borders and we will color this now I'll be coming in the left hand side and I'll insert detail of the customers I'll be taking the details from our example sheet and I'll paste over here for single couple of family we will have to create drop down we will be going in data data validation from here we will select list and over here we will type single couple family we will do okay and our drop down is created we will drag the still end [Music] for photo ID we will be creating the drop down and we will have to go in data data validation list and over here we will type yes or no we will do okay now we will be going in home and we will go in conditional formatting highlight cell rule equal to we will type yes and we will select green color okay I'll be going to conditional formatting once again highlight cell rule equal to and this time I'll type no and I'll select red color I'll do okay and I'll drag this till end so our drop down is created now checking date we will have to type it manually we will right click over here and we will have to go in format cells from here we will go in date and we will have to select day month and year we will do okay I'll drag this till down so all our dates are inserted now checkout date I'll insert some figure over here so checkout date is done now we will calculate formula for total stay and the formula is Plus or equal to checkout date minus check-in date enter and we will drag this till end Advance we will have to insert manually [Music] room type for room type we will have to create drop down we will be going in data data validation from here we will select list and in Source we will type type of the rooms so it's single double suit Studio Deluxe and Villa we will do okay and our drop down is created over here we will drag the still down we will select some rooms over here room charges we will have to insert formula for room charges and we will be connecting the room charges from here so the formula will be plus or equal to if bracket open room type is equal to 0 comma double inverted comma 0 double inverted comma close comma if bracket open room type is equal to double inverted comma single double inverted comma close comma we will select rent from here and over here we will insert dollar we will press function F4 comma if bracket open we will select room type again is equal to double inverted comma double double inverted comma close comma we will select room rent of double we will insert dollar over here function F4 comma if bracket open room type is equal to double inverted comma s u i t e double inverted comma close comma we will select rent of suit we will insert dollar over here function F4 comma if bracket open room type is equal to double inverted comma Studio double inverted comma close comma we will select rentok Studio we will insert dollar over here function F4 comma if bracket open we will select room type again is equal to double inverted comma Deluxe double inverted comma close comma we will select rent of deluxe room we will insert dollar over here function F4 comma if bracket open room type is equal to double inverted comma v i Double L a double inverted comma close comma we will select rent of villa we will insert dollar over here function F4 and we will close the bracket till it doesn't get white or black it got black we will do enter and the formula for room charges is inserted now we will drag this till end food charges we will have to insert this manually I'll take a random figure [Music] bar charges we will have to insert it manually I'll leave some blank now we will be calculating formula for room tags and the formula will be plus or equal to Total stay into room charges into percentage we will insert dollar over here means in percentage we will have to insert dollar function F4 and we will give enter we will drag this till end food tax the formula will be plus or equal to food charges into percentage we will insert dollar over here function F4 enter and we will drag the still End Bar Tax Plus or equal to Bar charges into percentage and we will insert dollar over here we will do enter and we will drag the still end for Discount I'll be creating drop down so I'll be going in data data validation list over here we will provide the source so I'll be typing it manually no five percent ten percent 15 percent 100 percent complementary we will do okay and we will drag the still down so our drop down is created now [Music] we will double click over here we will select this and we will be going in home then conditional formatting highlight cell rule equal to we will type no over here and we will select red color and we will do OK again we will be going to conditional formatting highlight cell rules equal to and this time we will type complementary we will click over here on the drop down custom format fill will select a color from here font we will select yellow color we will do it bold and we will do OK again we will have to click OK and the color for complementary is inserted over here as well as no is highlighted discount amount the formula for Discount amount will be plus or equal to if bracket open this means discount percentage is equal to double inverted comma no double inverted comma close comma 0 means when the discount percentage will be showing no the discount amount will be 0 comma if bracket open we will select this once again means discount percentage is equal to complementary and we will take complementary in double inverted comma double inverted comma close comma 0 comma room charges into total stay into we will take discount percentage we will close the bracket and we will do enter and we will drag the still down so whenever the discount percentage will be showing no or complementary the discount amount will be zero for complementary I have kept zero because we are not going to charge anything from the customer total PayPal now we will be calculating formula for total payable but before that we will have to update our formula for room tax we will have to give two conditions over there first will be if there will be 100 discount or complementary room tax should be zero we will come over here we will click just after equal to and we will insert if bracket open this means discount percentage will be 100 percent comma double inverted comma 0 double inverted comma close comma if bracket open discount percentage is equal to complementary [Music] comma double inverted comma zero double inverted comma close comma we will close the bracket and we will do enter we will insert double inverted comma before and after complementary now we will close the bracket till it doesn't get white or black so it got white we will do enter so our formula is updated now we will drag the still end now we will insert formula for total table and the formula will be plus or equal to if bracket open discount percentage is equal to double inverted comma complementary double inverted comma close comma double inverted comma 0 double inverted comma close comma room charges into total stay we will take this in bracket plus food charges plus bar charges plus room Tax Plus food tax plus bar tax minus Advance minus discount amount we will close the bracket and we will do enter so this is the payable amount of the customer it got calculated very properly and our format is ready now 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 [Music]