Create a Commission Bill Format in Excel for Banking Effortlessly

Commission bill format in excel for Banking

hi friends in today's lecture we will study how to maintain income and expenditure account in microsoft excel over here on the left side we would be entering the income and the total of the income would be reflected in this corner and over here we would be entering the expenses and the total of the expenses would be reflected over here if the income is greater than the expenses then the total profit would be reflected over year if the expenses is greater than the total income then the total loss would be reflected over here if both the income and the expenses are equal then total profit would be shown as 0 and total loss would be shown as 0. now let us try to see this with the help of an example now notice that the total income over here is 35 000. now in the income column i make one entry and i write over here that i'm entering a bank ft so i say bank fd and the amount is 5000 so you will see immediately the total income becomes 40 000. now observe here the total expenses is 34 200 and also observe over here that the total profit is 5800 now supposing i go over here and i enter one expense example staff salary and i enter an amount of five thousand so notice the total expenses have become 39 200 and the total profit has become 800. so let us go ahead and see how to create this worksheet in microsoft excel so here we have a blank spreadsheet of microsoft excel so first we will select the cells from b2 to j9 this way after that we will change the color fill color by clicking over here and selecting this then we select the last row this way and we again change the fill color to something slightly dark like this then we select the third row and we also change the fill color to a slightly darker shade then we select the cells from g3 to j3 and we select merge and center then we type over here income and expenses sheet followed by the month for which we are creating this income and expenditure so i will write down over here january 22. on the left side over here i will write the name of the company as an example i am entering i mind training solutions i will make the font to bold and increase the size to 12. same way over here i will make the font to bold and increase the size to 20. now we will come to the cell b11 and we will type over here the headings example serial number date particulars and amount then we will select these column headings we will press ctrl c to copy and will come to the new location and press ctrl v to paste then we will ensure that both the headings are selected and we will press b to make it bold like this now after we have made it bold we would like to increase the width of the date column so we select the column entire column c by clicking over here and keeping the control key pressed we would click on the h column heading over here so that both the columns are now selected and then we increase the width of the date column same way we would like to increase the width of the particulars column so we click on the column heading d keeping the control key pressed we also click on column number i and now i want to increase the width of both these columns to 32 so i click on home and in home you have format and in format you have column width and we change the column weight to 32 now we click above the heading serial number and we type the heading as income then we highlight these cells and we select merge and center in the home menu same way we come above serial number over here and we type expenses and then we select these cells and we select merge and send then keeping control key pressed i click on income and make this to bold i would then just click outside to remove the selection and reduce the width of column a like this and reduce the width of column f as well like this after that i select a few cells from here this way and i press ctrl t from the keyboard to convert this into a table then i would select my table as headers and then i would click on ok then in the table styles i would click over here and select any table style from here same way i would select set of cells and again i would press ctrl t to convert it into a table and select my table has headers and then click on ok and then from the table styles over here we select this style now i want to remove these branded rows so in the table design you have this checkbox branded rows just remove this check mark so the branded rows will go away and same way i select this set of cells and remove the check mark from branded rows now i would like to change the color of income so i click over here and i select a dark color for the font and same way for expenses i select a dark color for the font now we would select these cells this way and press ctrl shift and l to remove the filters same way we would select these cells and press ctrl shift and l to remove the filters next we would select these cells and change the fill color to a light fill like this and same way these cells and change the color to a light film next we would insert four rectangles in this area and for doing that we click on insert illustrations shapes rectangle and then we draw a rectangle like this and then in the shape format menu we change the shape field to a white color and in the shape outline we say no outline now we want to duplicate this shape three more times so i'll press ctrl d and the shape gets duplicated i will take it to the side like this again i would press ctrl d and one more time ctrl d after that i would like to align these shapes so i would first take them a bit to the center then i would select all the shapes by using the shift key then in the shape format menu you have align and in align we will select align middle again we'll click on align and select distribute horizontally and then again we would click on align and select distribute vertically so our four boxes are ready now we would like to insert some icons in these four boxes so the icons i have already installed from the net and i have saved them in a separate sheet this is the income icon which you can download it from the google by just typing income icon expense icon profit icon and loss icon so i have downloaded it simply from the google so i select this icon i press ctrl c i come into my sheet and i press ctrl v and paste this icon over here same way i select the expenses i can control c come into my sheet and place the expenses i can over here same way i select the profit icon ctrl c copy come into my sheet and ctrl v paste again i go into icons select the loss icon ctrl c copy click on your sheet and ctrl v paste and bring it to a suitable location in your sheet now we want to write down the heading over here as profit income expenses income and loss so in order to write the heading over here we click on insert then we click on illustrations then we click on shapes and here we select text box now we draw a text box over here and we give the heading as income then we select the heading by pressing ctrl a and make the heading to bold and increase the size of this heading to 20 and bring this heading on the top like this and in the shape format menu we select the shape fill as no fill and the shape outline has no outline so it looks this way now we copy this income control c and we paste it or you can alternately also press ctrl d to duplicate it and we can bring it here and again ctrl d to duplicate it and again ctrl d to duplicate it then we change this heading to expenses and you can increase the size and we change this heading to profits and this heading to loss you can realign the headings by selecting the headings by pressing shift and in the shape format in align selecting align middle now below this i want that the income value should come below this expense value should come below this profits and below this loss so what we'll do we will first enter some data in these columns now i have entered some data so what i will do is i'll select this range over here and in the table design i would select something like this so that the data becomes more clearer to read and again i select this range over here click over here and select this so that the data becomes clearer to read also i select this column click on home make its center align select this column click on home make it center align also i select this column and in the home menu i select the thousand separator and same way i select this column and in the home menu i select the thousand separator this way now what i want to do is below income i want the total income total of this column should come and below expenses i want the total of this column should come and the profit should be calculated and the total loss should also be calculated so what i do next is i click on this plus icon and make a new sheet in this new sheet over here i will write down the three respective column headings that is income expenses profit and loss these are the headings which you see over here so in my new sheet i type income expenses profit and loss now in the income column i click on equal to i will type sum then i will type open parenthesis then click on my sheet and remember to click on this heading over here so that the entire column gets selected close the parenthesis and hit enter and you will see the total income same way for expenses i type equal to some open parenthesis i come into my sheet and i click on this heading over here and then i close the parenthesis and hit enter so i get the total expenses and then in order to find the profit i will give a formula over here equal to if then i give a parenthesis and i write down that if the income is greater than the expenses then over here it should substitute income minus expenses otherwise it should substitute zero and then i give close parenthesis and hit enter so it shows me a profit of 800 which is 35 000 minus 34 34 200 same way for loss i give a formula equal to if open parenthesis expenses is greater than income then it should substitute expenses minus income otherwise it should substitute zero close parenthesis and hit end so in this scenario the expenses is not greater than income hence it is substituting 0. now these values i want should be substituted in our sheet over here so what i am doing over here is i am once again clicking on insert clicking on illustrations clicking on shape and in shape i am selecting the text box then i am drawing a text box like this and in this text box in the formula bar i will type equal to and then click on my sheet and click on income and just hit enter so you will see that the income gets substituted over here i will change this value i'll increase the font size and i'll make it bold as well and i'll increase the size of this column so that it can accommodate it and then from the shape format menu i will select shape fill as no fill and shape outline as no outline then i would just select this value and press ctrl d to duplicate it and have it moved over here below total expenses and one more time i'll press ctrl d for moving it towards profit and again ctrl d for moving it towards the loss and then in this total expenses i would click over here i would type equal to come into my sheet click on expenses and hit enter and if i want the formatting to be same like this i first click over here click on format painter and then click here to get that same format same way for total profit i click over here type equal to click on sheet file and click on profit and hit enter if the formatting gets changed come on the previous format select format printer and then click here so the formatting gets changed again for the total loss click over here type equal to and again click on sheet 5 click on total loss and hit enter and if the formatting gets changed select format printer and click over here if you don't find this with the thousand separator come into sheet file ensure that profit and loss are selected and in the home menu give the thousand separator and come back to your sheet and you will see the data being reflected in a proper format now we will see whether the data is actually working so i'll click on this drop down and i'll increase number of rows and same way i would click on this drop down and increase the number of rows i would increase this serial numbers this way and this serial numbers this way and now i enter some value if the date format has got change just select this right click and format cells and just select date and click on ok ensure it is center aligned select this or you can just select this format printer and just copy that same format here now over here i am typing the new date and i am typing bank fd notice here it is 35 000 when i enter some new value it becomes 40 000 instantly and same way notice the expenses over here also notice the profit so i enter some new value i come over here and i type staff salary notice that right now it is 34200 the moment i enter some new expenses the expenses get increased and the profit gets reduced and right now we are not in a loss so friends in this lecture we have seen how to maintain income and expenses with this we end the lecture goodbye