Excel Business and Financial Modeling PowerPack Course

1. Business Plus Financial Modeling Course Info: Hi everyone and welcome to this basic to eight months XL business and financial modeling course. In this course, we will learn how you can use your basic to advanced Excel skills and then use it to create the management systems like accounting management system, employee management system, inventory management system. We will create the systems out of it. This obviously means that you need some background of excellent knowledge as well. There is another course which you might have joined, and that is my basic or advanced Excel course. This is a prerequisite for the course, but if you already have the knowledge, you can directly start from here as well. But anyway, during the course, if you feel that you'd need that course as well, you can find it in the resources section with the link of this video, I will attach this. And that will be a Word file which create, which contains the coupon of that particular course, so that you can grab that course for the minimum price. And afterwards, you can move to this training. If you want to directly start with this training. From the next and upcoming videos, we will see the employee management system first, it, in which we will use multiple x-ray techniques like data cleaning filters, formatting techniques. And then we will create an employee database management system such that it syncs the data with this form so that as soon as you write the employee code or name, it will fill out all the details related to that employee. And even it will look up the picture for that particular employee as well. This will be a very interesting reduce CDS. Just go ahead and subscribe to this course, and I'll see you in the class. 2. Data Cleaning and Reformatting Tricks: Now let's start with the training and our first file for the training is GSA employed ideal form start. You can find this with the link of this reduces. Click on the Resources button. And you will find this file to download this file. And this involves three sheets, basically, which we will use for this training. The first sheet will involve some of the basic formatting techniques that we can use to improve the look of the sheet. Like for example, religion subunits mentioned some matrix not and medical status. Let's say that we want singled in all of these fields automatically. So we need to fill that as well. Nationality, we will fill that as well. And some other fields like President address, permanent address, relationship status, relative address. We need to fill out all of these details and customize if required. Let's scroll back. Now as you can see that this sheet doesn't have any particular format. That means it might have format before, but we have removed all of this with a simple click. Let's say that formatting was irregular and we've selected the whole sheet. And click on here and click Clear Formats. It will be turned into this. Let's say that we have done that. But afterwards, if you clear the formatting like this, you might see some adjustments required. For example, in the Date of Birth field. You can see that it requires the d to be properly mentioned. It is not showing as a deed. We need to change it to that date format. I'll select the whole column. And right now it's showing the date format, but we need to change it to journal first and then change back to short date so that it will show the date property. Now the second problem that we have identified is the irregular serial numbers later, some CD numbers that are missing here. So what we need to do either we can control shift and hold it down, down, down, distorted or control shift. Every time it sees a gap, it will make a stop here. So you'd need to push that with the down arrow key on the keyboard. Now afterwards, just write one. You can just double-click it to send it down. As you send it down, you can just write one. And from this dot, if you bring that cursor to the dot, it will change the course assigned to the black cross. Just double-click to center down. And from here you will notice that pop-up box appears that It's called autofill options. Right now it is selected on Copy Cells statements. The first cell result is copied to all the other cells. What I need to do is to fill the cities into it. You can notice that now it's arranged in ascending order of the numbers. Now let's see further. If we see further, data are a lot of blanks as well. That might be because of incomplete information somewhere we need to write, let's say not available. Sometimes you may need to fill, fill it with same details as well. So let's see that if we want to fill all the fields with the same details, how can we do that? So let's say that the religion ADF for all the employees the same. So just select the complete data. Note that we are selecting the data only don't select the whole column because we are using Find and Replace for this technique, risk control F. It will open up the Find and Replace option. And then I'll go to replace. I need to replace all blank fields with the religion name that is Islam. Does keep it blank and replace it with Islam. And click on Replace All. You can see that it quickly replaced all the blank fields with the religion name. Click OK and close. This feed is now complete. Next field is medical status. Let's say that all the other individuals are single. So you can drag the desert off one particular cell, other cells as well. Like this, let's say to our mattered. You can drag and drop to complete the data elsewhere. This is also a technique. I'll show you multiple, ridiculous, multiple ways of doing things in Excel. Now, nationality, for example, all of these are at the same nationality. So just drag and drop to all of the data. Now, for the further fields, Let's say that we don't have the information, so we cannot mention the president injuries obviously by ourselves. So all the other data is not available and also the size of the columns are very white. You need to auto adjust all the columns automatically with a significant. In fact, let's adjust the column size. What all the columns, just select the first column. If you go to the a column name and just press left single click and hold that click and start moving towards strike. It will select the whole columns on columns. And just release the mouse key. When you are done. Now come between any two columns, like if an egg is the column names. So when you come in between this, it will show this black cursor sign indicating that you can either adjust towards left or right and not adjusted manually. I'll just double-click here. And you can see that it immediately readjust the size according to the maximum quantity that is available in the particular column. Now let's see further. If we go towards right, we can find that at the end there are extra columns that are for market, which needs to be removed because what happens, because what happens is sometimes because of the extra formatting, it increases the file size overall. So just hoard and select this column. And just release the mouse keys. Just release all the keys. And now let's hold Control Shift and press the right arrow key and press again. If it stops. Then let's go to the Home tab and click on no fill from here. Now whenever you are lost, anywhere in Excellent, You want to go to the first cell directly. You can just press Control Home. Now let's see the further fields that are empty. Excel, recommend, stat. Even there is no data in the particular cells. It shouldn't be left blank. We need to fill it. Let's save it not available. So we will select all the plants manually. Now, why I'm not using the shortcut Control Shift right and down, because it will vary down because of the empty fields. So that's why it is recommended to fill out the empty fields as well. To select the words right, and go down. And stop right here. Now I am, we'll replace all the blank fields with not available. So press Control F, go to the police and find blank and replace it with not available and replace all. You can see that 213 replacements are made. Now the final step in this is the further formatting. If you see that this sheet is too simple, you'll need some further formatting. You don't need to do anything. Just select any, just select any column header and go to the Home tab, Formulas tab, and select any format that you want for your table. Or you can directly, if you want to use the shortcut directly, you can just hit control D or Control rusty. And it will show you the option vi is the data for your table. It automatically detects the table. And that is because we have filled all the empty fields as well as see this is the benefit of filling the empty fields with some data. And just keep on selecting my table has headers. It should be selected that it identifies the headings and click Okay. And now it is formulated. Now as soon as the data is formatted, you would see a special tab related to the table design. It. You can change the format of it as you like. There are a lot of options to 2s. So I'll keep it simple. That is how we format the table and do some basic formatting and data cleaning in Excel. Now let's move to the next videos and see what other adjustments we can make to the advanced level of X. 3. Calculate Employee Age: Now let's insert one more interesting period in this data, and that is the current age of the employee. Let's say that these are the data words. And according to the today's deed, I need to find out that this employees how all in years, months, and even days. That is very interesting formula. I'll just insert a new blank column right in front of this date of birth. Right-click and insert right here. Now the formula that I will use for this is a very special formula. And in fact it is a hidden formula, right? If equals to dy dt. You can see that from dude, I can see only two formulas that x is suggesting. That is dy dt and dy dt value. But I'll keep on writing and I'll grade it and open the bracket. Now you can see that it shows up a suggestion. That means it is a formula. Now, most of the formulas apply is by following the tooltip. That means it also suggests the procedure of applying that formula. But since it is a hidden formula, it is not showing the syntax or the method of applying formula. So I'll tell you the method. Now, I'll select the date first. You'll notice that instead of showing this cell number as F5, it is showing a complete column name. And that is because we have formulated the table as controlled BY it doesn't create an issue. But if you want to convert this to a normal table, you can just right-click anywhere in the table, go to the table and Convert to Range. Click Yes, it is your choice completely. Now let's apply. Again. Open the bracket, and now if I select the cell, it shows F5. Now, we have mentioned the star did. Now press comma. Now select the indeed. Now enter it should be the today's date, whatever is in the system did, it should show that that is a specific formula. So that is a specific formula in Excel data is Dave function. Today? As soon as it's suggest you don't need to write, it. All, just hit tab and it will automatically autocomplete this and just close the bracket. You don't need to write anything. Now comma again. The next thing is what we want out of it. You want to calculate started to indeed in years and months or in this. First of all, I want that in years. Under the inverted commas. Now hit Enter. Now, as soon as you press enter, it will show you that you haven't close the bracket, which will be done by other autocratic function. Do you want it to do that? Click Yes. Now it shows the deed, but the deed format. Now it shows that edge. But it is indeed format. The columns are indeed format. Just select the whole column j into two general wired whiteboards indeed format because it extracted the column just right after this. That's why it copied the format of the earlier column. The age of this person is 38 years. Now I need that. 38 should also be mentioned with years. So it should be written here, 38 years. That is, that can be done with outcome coordinate function. I need to assign a text after this formula. So I write n, I need to join that with a specific formula. Whenever you want to join anything you will use this formula. Inverted commas and index will be mentioned inverted commas. And I'll write years. Close this and hit Enter. Now you can notice that it doesn't contain any space. That means that in inverted commas. I need some space between number n alphabets. So I'll give the space here. Didn't the years in the starting, hit Enter again. Now it's showing in the perfect format. Now if you are thinking that you might not need this formula. So why are we learning it? Sometimes you don't know about the practical implementation in your scenario of that particular formula, but it can be used in a lot of scenarios. You will get to know that in future as well. You will also know from this formula practice that how to merge different formulas into one formula to create the result that you desire. So basically this is the practical learning of excellent. Now similarly, if I want it in months and these as well, what I'll do is I'll copy it up to date it. Just ignore this equal to sign, but because equal to signs only comes once in the starting of the formula. Just copy and paste this, but it requires an in-between because now the text is further joined with the next formula, so n should be compulsory. So now all of this syntax will remain the same. I'll change it here. I need months. It doesn't matter if you mentioned it in small or capital months. Let's see the result first. Now it shows for 64 months, that means it is showing basically from this starting off the date of birth. So the sentence should be just ignore the years and mentioned months. So y m will be the same x. Now let's see the changes and let's put a space after months as well. Actually it's joining it with years. That means actually I need space, not in months, just reduce the space. But in years. I support this piece in the starting. I need to put that at the end as well. So now it's showing 38 years and eight months. Now let's move further and copy from n digit two months and just be Stat again. So it continues the formula further and reiterative this to the deltoid it. And now I need to ignore months and mentioned these M. And now it should be shown in this. I need a space after months as well. At the start as whether there is space in the starting and then, let's see now, 38 years, eight months, and 17 days is exact each. Now if you want it to appear it like this, 38 years, eight months, and 17 days should be here. Then after months, let's put, and as well in the text. And put a space. Basically this is the text. Don't confuse it with the other end that is outside the text. Whatever is not, attacks will not be in order to commas. This is, this Alter Index is in inverted commas. This is just the additional tax that we want with the formula. So now let's apply it to all of these in a single click, double-click, and boom, you have all the employee with the exact each to the nearest peers as well. Now as we double-click and drag down, you will notice a change in the formatting that is not exactly like the other columns. So just hold that column for what you want to copy the format. To select that column, go to Format Painter and then click on the next column as well. It will make the format character. That's how we can calculate the age of employee in Excel. 4. Inserting Cell Fitted Pictures Automatically in One Click: Now after this specific customization and some adjustment about the formatting and different formulas, we will start moving towards the final goal that is completing the employee detail form. Now we want the function so that if I mentioned the employee name, unemployed code, all of the data should automatically get filled with picture. But for that, there is some prerequisites. That means I'll go to the main employee data file. And here I will attach manually each name with the relevant picture. Google obviously use the quick techniques for this as well. But this is the first step in order to move to the next step. First of all, I'll go to the employee duty and copy the employee name, code and designation. Just copy and paste the whole relatives. The mean employee database file. Now let's create a new column named employee picture. Now the formatting is not the same, so I'll just select the C column completely and click on Format Painter ones and try to paste on b. It says we cannot do that to merge file. Just remember that it is not recommended to merge any cell in Excel. Merging means combining multiple cells into a single-cell. Like here, we have done employee data form, direct access file that was previously preconfigured like this. So we need to unmerged this simple. If you want to merge all of the cells on a single sheet, just select this option. Between one and e comes a folded piece like just click on this, it will select the whole complete sheet. If you notice on the home tab that is emergency interruption, this is highlighted. That means some of the cells in this sheet is matched to unmerged. Just click on it again and it will match. Now, I need to adjust the size of the cells so that it can fit the employee picture within this area. For example, I'll extract it, expanded like this. But I need to do it for multiple cells sort I'll do instead is I'll select rule number six and Control Shift, and then press down arrow key. And then from the formula, I'll click on the row height. And let's try it. Let's try green D. Let's try 50. Let's try 100. I think this is a suitable size. We can expand the column size as well. Let's make it 150. Now. To make the formula same, I'll select the C column now. Format Painter. Click on D column. Now. The whole idea. Now since the text is too small and the ADA is too big now, so control shift right and dump. It will select the whole data. I'll move it to the middle align. You can notice that it will show up something like this. You can increase the text size as valid. Let's increase the text size as well. Something like, let's say the formatting, I need to change the formatting little bit as well. Let's make all of these in a single color. There to be most suitable. Looks nice, like this. Now that we have completed the formatting, now let's attach the pictures of each employee. You will find sample pictures with the link of this reduced. So just downloaded from the resources and unzip it, you will find all the pictures. Now let's try to insert the pictures. Under the Insert, you can either directly find pictures or since I'm using Excel 2016. So I have to go in illustrations and click on pictures from here, and click on pictures and click on this device. Now let's go to the location where we have downloaded this pictures. Now it was a zip file. I have extracted the zip file and it comes up like this. Now let's select one picture, for example, this one. Now as you can see that you need to readjust the size because it is too large. We need to fit it. In single-cell, for example, this one. Just make sure that it fits exactly in the cell. What you can do is you can, we can apply borders that begin to identify the limit of a particular cell. Let's see. Borders. Let's apply to here as well. This is how we figured single picture. Now v are learning the advanced techniques of Excel. As we can see, the inner lot of employees. And if I attach each of these pictures one-by-one, it will take a lot of time. So I need to save the maximum time. We will use an additional tool that is called key rules. It is not by default the function of x. If we want to attach all of the pictures automatically resized and fitted into the cells. It cannot be done by Excel default functions. There is a separate add-on that is called KU tools, which I'll attach with the link of this video. It is a software just downloaded from the resources and install it. That will do the job. After you download the software, just double-click. Click Yes. Okay, Next, except Next. Next. And it is simple installation. Now I recommend you to see if this file first and closer so that it can properly apply the extension to the software. Start. Microsoft epsilon is already mentioned there. Now you can see that there are two tools now, tools and enterprise. There are two separate tabs which contains amazing functions, out of which one is automatically resizing and inserting the pictures. Let's open my sheet again. And let's close it for now. Now. I don't need to insert each of the pictures separately like this. Now. Now since we have two separate tabs for the tools, I'll go through enterprise and I'll go in Insert option. But before that, let's select all the data. Now select all the cells where you want pictures to be inserted. Shift key and move downwards. Now let's go upwards to see the effect of it as well. I'll go to import and export and click on Import pictures. And it says Fill vertically cell after cell. It will automatically select and fill all the cells one by one with a picture. You can add a single file or folder. I'll select the folder. And I have the files in desktop, employee management system and employee picks, an employee picks this my file. Now for now, the name is according to the image one, image two, image three. So this is just a test. You can give all the picture name, the relevant employee codes, and then according to that, you can import. Now the source says, is this and it will import the same size, is there to sing. But I'll adjust the import size and click on matching the cell size. Click Okay. It will automatically match the cell size automatically. Now let's click on important see the effect of it to a single cell. This is basically the indication of where should the data start from. Click Okay. It will process a little bit. And now you can see that it exactly fitted all the pictures under each name. The perfect order. We only have some pictures. That's why we have inserted some only. This is the technique to fit the pictures in the cells automatically. Now, in the next video we will learn how you can use the VLOOKUP function to automatically pop up the picture with all the reduce finding from the name, the form. 5. Apply Auto Search Boxes: Now until now, we have sorted out and character database and then we have attached the employee pictures with each employee. The next step is completing the employee digital form such that then iodide employee name. It should search all the fields from the database and plot it here. So this is what we are trying to achieve. It should automatically search all the fields according to the employee name. And it will also show the picture of the particular employee. We will see the picture lookup as well in this training. But first of all, in this video we will see how you can create a combo box or a search box, which automatically auto complete the data as you start writing. So for example, my employee name is Fanon octet. So what I'll do is I'll start writing Fanon. And as soon as we write a K, it automatically completes to the full name. And that combo box will also include a drop-down from which we can easily choose the name of a particular employee if we wanted to. This is a special feature that involves the developer tools. Now if you don't know about the Developer Tools, developer tools are all the advanced functions used in excellent. So we need to activate that tab because by default you cannot see the Developer tab and delivering rate. I'll just right-click anywhere in the blank field. Let's say we are in the home tab. So here is the white field. I'll just right-click here and click on Customize the Ribbon. Now, on the right-hand side I have Developer tab. It is unchecked. I'll just check this and click Okay. Now the Developer tab is here. The function I'll be using is under the Developer tab Insert option. Now theta two categories under here. One is Form Controls and one is active X controls. I'll use the combo box. But under the ActiveX controls. As soon as we select this, you will notice that the cursor sign changes. Which this u2 or your left click and start dragging towards right, so that the box can expand its size and show the proper search box. Now I need to place it over the employee name field. But for now I'll keep it here. You will notice that as soon as I inserted the combo box automatically, it is by default in design mode. Design more does turn on. That simply means that we can adjust the size makes settings, and then we will turn off the design mode for it to work. Right now I'll right-click on this combo box and click on properties. Now there are lots of options in properties, but don't worry about that. You will stick to what we only need. And that is list filled range. Let's finish. Range simply means that you need to attach the name you have given to all the employees names. What do I mean by that? Let's go to employee names. Let's click on the heading and as soon as I press down, it will show the first employee. From here, I will hold the Shift and Control key and hit Download. Okay. Now this is all the employees I have. I'll go to the Name Box, little box right here. And I'll name this particular Lynch as employees. Now there are a lot of options about the named ranges. How to add further employs through the list, how to customize that, delete that. But that is part of the other training program that is basic to advanced training. We will not discuss all of these options in detail. But just remember that you can name the name ranges like this. Now, I'll go to digital form. Again. Right-click and click on Properties. Here is list filled range. I. Name it as employs. Now the linked cell should be. What is the cell number? That is C9? Actually, let's close this. Go to here again. Employees is linked and this is C nine. That means that whatever name appears in this combo box, the desert of it is linked to the cell. For example, if the name is Fanon. It will automatically print in the cell as well. And that is what we exactly need. Because just remember that the search box or any other thing that you apply on the excellent That is not the part of the sales formula cannot be applied on that. For example, if you want to search all the other details with this employee name, we can only do that with formulas, let's say VLookup, but we look up with only work with the cell, not with the search boxes. This combo box doesn't contain any sale. It should bring the data in the cell. I have given and linked it to the cell. Now, right now it is in the design mode. That's why we can just place it here and it will not work until we turn it off. And now let's see. For Hudepohl, you can see the name appears here and here as well. Now if we wanted to design it further to make it look the part of the cell, we will, what we will do is we will just paste it right here and just adjust the size so that it looks like it is part of the cell. Now let's turn off the design mode. Whenever I had right Any letter, it automatically completes the data. If you want to choose it from the employee named list. We can do that as well. This is how we insert the combo box in Excel, which is used as an auto search box. Now if we want this function to work in future as well, that obviously we want, because we have used the Developer tab whenever you have used the Developer tab. Now this excellent sheet is not a simple cheat. It is a Macro Enabled Workbook. You need to save it as macro integratable book. Otherwise, even if we try to save it for now, let us see Control S. Click Okay. This stat. Otherwise if you press Control S, usually suggest you to have a Macro Enabled Workbook. What do you do is I'll go to Save As this piece. And we have our data here. Save as types should be changed to macro enabled workbook. And click Save. Now let's click. Okay. Now let's check again. When we open this file. The function should book. Yes, it's working. This is how we apply the combo box. In Excellent. 6. Employee Data Extraction Technique: Now that we have created the employee name as a search box as limit z, right? The employee name, it should automatically search all the fields and please the data right here from the employee list. That means it is kind of a searching or a formula that is called VLOOKUP. Now again, if you already known about the VLOOKUP by yourself or by our basic to advanced Excel course. That is good. Because I'll just show you the summary of how the VLookup will be applied. The details are discussed in the basic training. Employee name is this. I need to extract the joining data according to this employee name. So let's apply equals to V lookup. I don't need to write the complete formula as soon as it suggests, I will press Tab. That's right. Now I chose the syntax of how the formula should be applied. First option is lookup value. That means according to which data we will be extracting the joining did. According to employee name means we will search or local for the employee name under the database and then extract the relevant data that is the joining date for this employee. That means local value will be this cell. Now, since I have applied the combo box in the cell, so I won't be able to select this cell directly. Rather I write C19. The cell is selected in the background. You can notice the next option is stable. I need to go to the employee list and select the whole table. But wait a minute. Let's press Escape. Let's name the employee. Be able to complete table same as we did for employees. But this time, just remember that we will not be including serial number as a first column to select the database. Why? Because VLookup has a rule that whatever the lookup value is here, the lookup value is name. The database, whatever you select or name, it should start with employee name. That means whenever you are applying VLookup, the first column was the database should contain the lookup value. Let's select the data from here. Control shift right and down. Let's try to employ the school people. Now why I've given underscore because it doesn't accept spaces. So that's why pressed Enter. Now let's apply VLookup equals to fee lookup. C9 is my lookup value. The database we can find is in employee table. What about the column index? Column index is, we will count the column one-by-one, how far it is from the employee name. This will be considered as column 11234567. But there are number of columns, a lot of columns are there. What is the automated method for that as well? So that we don't have to count. That is match function. What we will do is we will use the match function first. Apply match lookup value is joining. Did I need to look up this heading under press coma and lookup array. It says lookup array. Lookup array is all the headings. So I selected this and I'm seeing that lookup for joining did this all headings and hit exact match. Now change this to generate. Joining gate is under column number 22. So see how quick it is. Otherwise we have to count to apply VLookup for every single column. Imagine counting the columns up too grindy too. It takes a lot of time. Even we can make it simple as, more simple as well. Select all the headings control shift right, and write the name employee headings. Now let's try equals to match. Lookup value is joining data lookup array. Now we don't even need to go back because we have already given the name as employee headings. So this is employer takes an exact match. Now it finds out. That joining dude is under volume number 22. Now we can easily apply the VLookup. Now what is the method of formula merging? Once you have applied one formula, just go after the equal to sign and write VLookup. Now. Press Tab. Now the lookup value will be, as we have decided before, C9 array will be employed table. The column index will be whatever the match function finds. And now after that column index number, this is the column index number requirements fulfilling. Afterwards, it coma again and write 0 for an exact match. It automatically suggests that one more record is required. Click Yes. Now it finds out that wrong joining gate. Why is that? Because we have started the data table from here, but we've selected the headings from here. We need to customize the data. Now. Now we need to customize this employee headings. Let's go to Formulas, Name Manager, and under the employee headings to select this. And it should start with a four. But before this should be the inch, we can simply do is just remove all of this. Refers to start, should start from here. Click Okay, and Close. Now you will notice that as soon as we go here, it will automatically gauging is the data back here. I'll change it to short it. This is the joining. Do it, Let's check it manually now. Control F. This is our employee. Let's check the joining me. Joining deed. This is the joining date column. Let's change it to the proper format. 2030. Now it's correct. Now let's try to change the employee in. Let's see the result changes are not. So see how quickly the VLOOKUP function finds out the joining gate for that particular employee. Now let's move to the next video and complete all the other fields with advanced techniques as valid. 7. Employees Lookup Formula Application to All: Now, until now, we have applied the VLOOKUP formula for the joining. Did we need to apply the formula of VLookup to all the other fields as well. But should I apply it one by one for every single field? Know, what I will try to do is try to adjust the formulas such that I can easily drag it to all the other sets. And we do that. Yes. First of all, the requirement is all the fields. Data will be found according to the employee name. So all the VLOOKUP field will search the data by finding the employee name in the database first. That means the primary lookup value will be always employee name. Now whenever the field is fixed on a certain lookup value, like this one, when I move, drag this formula down in the other cell, you will notice that it changes the look of value to CDN, which is wrong. That's why I need to delete this. And before moving downwards, I need to fix it on the employee name. Again, I will not explain much in detail because this is the part of basic to advanced training or advanced. You might already know that then comes to employ dB. And then the match function. Under the match function, the lookup value is joining did for dy dt. For next one, it should be employee court. In this case, the match lookup value should move because we have to extract what? We have to extract the column index number from the match function. So let's keep it as it is and hit enter. Now let's check the results. If I drag it down, it shows the employee code of GSE 032. Similarly, employee name is fixed. Let's move to the designation. Designation is 3D artists. Perfect. Let's go down department 3D. Let's check it. Once. Let's check the Department of this 3D artist. Yes, department is 3D perfect. You can see that. How much time does it take to find the details of one employee only? We have to scroll towards, right, to find the relevant details here in the form. It is very easy to find. Similarly, date of birth, profit, CNI, see number, gender. And let's track down now marital status and the permanent address. Now when we try for literal legion field, it's not available. But after pasting the formulas, just double-click to open. And we have to make only a little adjustment for match. Because when we started on the right, it misplaced the field. Because there it is on the other side. That's why we have to manually move it just for these two fields which is adjacent to it. Now it's working fine. Now let's move it downwards. Here this field is empty. Let's paste it here. This is also not available. Let's see contact. Contact numbers extracted perfectly. Similarly for email. Now, nationality, since it is there just in field, I've already made the starting swell. Copy it from here. Mobile number is not available. Sometimes the field heading name is not exactly what we have here. That should be same otherwise it will not extract the data. You can see that here it says C is contact number, and here it sees something else. So as soon as a piece of copy and paste the same heading beam, it extracted the data. But now the formatting has changed. No problem. Just select any other sets whose formatting I want to copy. To select that. Under the Home tab there is Format Painter. Just click it once and be stood on their deliverance cell. These cells from worst, I think, just unmatched. Now it's working fine. Similarly, I'll paste the formula of here. That's fine. It's fine. Let's select borders here. On partners. You have to make little adjustments to the borders. Now let's move downwards. This is emergency contact. Address. Similarly, del home, not availabilities. Let's see what other field do we have? Let's see this one worked out. Relatively mobile relationship. Let's try to pace your father. And relative e-mail not available. Now you can see that the extracted the complete data according to this particular employee. Now when a gene is the employee, all the deals change accordingly. For all the employees. See how cool and convenient it is that you just simply change the employee name and it will extract all the information related to that employee. This is what we are learning in the excellent business and financial modeling course. We have learned a couple of functions and formulas. Now we are using all of these formulas combined to develop a management system and employee management system or some other management systems. Just practice this under low. And then we will move to the next video. 8. Creating Picture Lookups: Okay, Now that we have created a lookup function and apply to all of the fields so that as soon as we change the employee name, all of the data is searched from the database and extracted the result in the form. But what about the picture? Now we will learn how we can even find picture related to that particular employee. The main requirement for that is that pictures should be attached with each name in the Excel sheet. So have you done that? Yes, we use the formula for that. Now one more thing that Let's see that there is no gap between the starting. Now if we wanted to create that gap, to select the column, right-click and click on insert. It will create a little bit space that you're gonna just later on. So that is an additional point jet data I just told you. Now what is the ideal formula to search the data according to the pictures? That is index match, index patch. And it isn't other formula just like we lookup, but in some situations it is more suitable than VLookup. Now to apply the index match function, the first requirement is first you will apply the match function and then comes to index function to link with match. This is the basic rule of applying Index Match function. Now let's begin with naming the local values and deeper range first, so that it will be convenient for us to apply the match function afterwards. Now the lookup value for this match function is employee name, because obviously we're finding all the data according to the employee name. And similarly will be the case for the pictures as well. To make it convenient, we can also name this C9 cell. Now it's D9, right? Because we have just created the gap. Because we have applied combo box. As you can see, that even if I try to click on the cell, we cannot go in this set it directly. What we can do is select any other cell and just move from keyboards to work. Right? Now it shows D9. Instead of D9, which is a variable cell name. We can make it fixed to look up name. Hit enter. If you have given spaces, make sure to change the space with underscore. It doesn't accept the species. The next step is since we need to look up pictures. This is my employee list. On the picture step. Just select employee name, first cell and just hold Control Shift and down. Now let's name it as employee underscored names for PICS. Now let's name the pictures column as well. Since we are already on the last day, scroll up Control Shift and press up arrow key up until employee picks. Let's name this at employee underscore picks and hit Enter. Just make sure that whenever you select the columns and name it, it should be of equal length. Means you shouldn't do that. For employee names you selected from here downwards. And for employee picks you also took the heading. If you're taking the heading here, you should take the heading here as well in the selection while naming the ranges. If you've made any mistake here in naming, you don't need to delete names. You can go to Formulas, Name Manager and adjust the name by editing it as you want. One more thing whenever you see if the list, just make sure by drop-down that whether the list name has been saved or not. Now to apply the picture first, what we will do is first we will try to find out the data under this box. Instead of fiction, I will just move the victor of plus one and test image. Extract. First, we will learn whether we can extract this text. If we can extract the text, then we can extract the relevant pictured as well. But for now, the test is for this text according to the highly pile. Now let's apply the match function first equals to match. Now what is our lookup value? Lookup value is lookup name. Make sure Tyler's this employee name. Lookup array was employee name for picks, an exact match. So this shows that in the US m, it is under this list and It is under Grundy five number. Now that we have extracted this match function, what we will do is we will link it to index function. Index function is very simple. Just double-click and come into start just after equal to comes index. Whenever you want too much any formula with any new one. This is the best thing. Index under the ADEA means. Index basically means whatever data you finally want to extract only means that data. So obviously that is employee picks and rule number. Now the area is this, and row number is found with match function. Hit Enter. It asks you to auto connect the brackets at the end. Now let's write it extracted the background data. This means a formula's working perfectly fine. Now we can place the image back here. Now what we will do is, since we now know that our formula's working perfectly fine, we will not keep this formula, but we will use it with picture lookup. So just copy all the details of the formula by, you can double-click or you can select this wheel. You will see on the top about the formula. So select the formula. Now let's go to Formulas and click on Define Name. Under the Define Name, I'll leave it as picture lookup so that I can use this formula with pictures. Now, for the reference, I'll paste this formula and click Okay. Now we will remove it from here. Now the next step is since they employed in particular employee name like light rail palettes electric, we have to select the element picture for regulate bile, this copy this. Then go to the employee to form NPC right here. You can resize it as you want. Now what we will do is we will keep on selecting the picture. It is already selected under the formula bar. Now I will write equals to vector lookup and link it with the formula and hit Enter. Now you will notice that it will automatically resize. Again. Just resize as you want. Now let's try to apply and change the name of any particular employee. Let's see the second employees, Javadoc. Let's remove this job Adam did. So see how cool it is now it extracted the second employee. Similarly, all of these employees are found with the formula. As we have learned about the picture lookup for the employee database management file. Now if you're thinking that how you can practically apply, you can even apply it for your store management. Let's say that you have a lot of inventory in the warehouse and sometimes your employees doesn't even know that how that inventory looks like so that they can pick it from the shelf. So it might help here as well, that as soon as you write the inventory name or in an inventory cord, it will extract the picture of that inventory so that it will be easier for the store persons to pick that item and hand it over to the customers. It can be used in multiple functions. So see how cool this function is. Just practice this totally and then move to the next video. 9. Formatting Cells and Currency Defaults: Now the second assignment of the business modeling course is check printing system. This assignment we will mainly use the mail merge function. Meal much function is the function that merges Excel with Word so that the certain fields can be picked from Excel on the word template. Let's say that you have created a mail to send all of your employees at the month and at the start of each month. That for example, Dear Mr. And the employee name should change automatically. Your Saturday X amount has been credited to your account. Account number is something. Please check and respond. For example, this is the meal. But for this particular template, you need to change the employee name, the account number, and the amount credited to their account separately for each single employee, the template is same, but these information are different. So let's suppose that you have 200 employees. It will take a lot of time to send each of them manually. What you can do is you can just strike some names on Excel and then create a Word template and then use the mail merge function to automatically pick that memes one-by-one and send it under e-mail automatically. How cool is that? We can use that function to create multiple systems. For example, I'll throw you the example of check printing system, the billing cash book. You have prepared that at the end of the month, you need to be D suppliers these amounts, what this meal much will do, it will automatically pick up each name and amount and we'll create a check for it. It will even write the amount inverts automatically with a specific formula. Here is my Word template. It looks like this. It will pick up all the data from Excel directly and we'll plot it here. And we will also learn how we can convert the amount in figures to alanine repeats automatically with the formula. Then it will automatically create all the checks for, let's say two hundred, five hundred and suppliers. It will automatically create that. See how cool it is. So let's see the steps involved in this particular assignment. Now to practice and follow along, I have attached all of these files with the link of this video. So just with the video, you can see that a sources, but just download all the assignments from the resources and you can easily follow along. Now let us see and work on the scenario. The first file that you need to open is delete gash book and check printer. Actually, let's close this file, save it. Now this is the scenario that at the end of the month I need to be salary to specific employs. This is the amount and this is the name. We need to prepare checks for each employee with the same template I have shown you earlier. But before starting to plot the data on the check printer template, Let's see some of the basics. Now let's revise some things from Excel. The Excel basically at one scores. I have told you that how you can clear that custom signs. For example, if I change it to journal and change it to dollar now, changing to pound now, it shows bound 40 thousand. But since we have to create rupees, vegan enabled a separate function for that. There is a method. So I've bought rupees with that method only. Because whenever, let's see if you write a hundred and two hundred. Now this format is not identified as a currency in the background. If I summit, it will choose 0. Why? Because it will consider these two cells, not as numbers but as text. So if you add text with the text, nonsense will be 0 obviously. But this therapy sign, I have installed it in the background. Now, even if I summit, let's look at it. Some. And even if I select all of these figures, so it shows the sum, right? How to create that currency. Let's see. Let's see Now I want to enable PKR. Let's see, one hundred and two hundred. I will right-click and click on Format Cells. Now, I want to format it as currency, but I won't be good. So I'll choose the guarantee that is similar to that contains the similar letters as began. That means three letters should be there. Any Guernsey? Let's see. I will use. And you can see like this one. See it also have the negative numbers. Now as soon as you choose the symbol, it shows how you did the negative balances in that currency. It should join regular ordered minus. So let's see in regular and bracket. Now, the symbol I am trying to form it is not Caesar key, it is speaking. Our sort I'll do is I'll juice any symbol that contains three letters and configured it. Basic contribution from here, and then customize it further from the customer burden. Here, just change the seas at k2 became against whatever it is mentioned. You mentioned PKR and click, Okay. Now it identifies the symbols. Now if I summit, since it is a currency symbol, now it can show the total as well. This is how we deal with formatting of cells and setting up the currency lettuce. 10. Convert Amount In Figures to Amount in Words: Now that we have learned some of the basic stuff about this sheet, for example, formatting the currency and functions like that. Let's see that. To mail merge, to print all of these struggles on the check so that it brings to check for each individual completely, automatically. But before that, we need to see what elements to be required on a check. Let's see the format of it. We have to check right the format. Now here I can see that I need to do you don't check the person's name, the amount inverts and amount in figures. What we already have. We only have name and amount. We need dude as well. So I'll insert a new column and let's write a date. Let's see, today's street, grantee 5th, November 2021. Let's double-click to center down and apply to all of the other fields. I'll go in this film handed the little dot and just double-click from here. But you will notice that it changes the deed for every person. We can see this small auto-fill options as well. Just going this option, it says Fill Series. That means it filled the dude with, with series. I need to copy the first cell in all the other cells as well. So I'll change it to copy cells. Now it's same for every cell. Now the next step is converting the amount to amount inverts. So we need amount inverts column. Now what do you think? I need to mention all the mountain verts manually? No, it will take a lot of time and this is the most careful thing that you do on a check. You carefully right? Amount invert, especially if it ends in sense or the figure is very big. This should be automated as well. So we have the formula for it that is spelled number, which I found out simply from the internet. You can do that as well. Just write on Google convert amount in words. Excellent. What's the first suggestion? Spell number. It is the most authentic vector that is support from Microsoft directly. What we will do is just scroll up or scroll down, depending on where you learned. Just copy this whole formula. You can go copied directly from my Word file as well. But I'll show you from here. Just copy this. All the instructions are given here as well. Just copy this. Afterwards we need the developer tool. If you haven't enabled it, just right-click anywhere on the empty area, customize the ribbon. On the right-hand side you can see the Developer tab and just check it and click Okay. Now let's go to the Developer tab and click on Visual Basic. Remember that if you need to insert any new formula and exit, you need Visual Basic. You don't need to learn the visual basic. Just need to be stat formula that is already created. Click on Insert and click on module. Now let's paste this formula. What does this formula court? That is mentioned with function. Function's name is spelled number. That's okay. Close. Now the formulas be stirred. Simply. Spell number. Data is a new formula now. And select this figure. I think data is a mistake. I think they had the wound formulas mentioned here. We need to copy until n function. The next function is different. They have created this confusion. Let's try this now. Equals to spell number again. Let's see if I remove this one. To spend number. I think there is some problem with their syntax. I don't know what. Let's just open my formula of Spell number. Let's piece this one and check if it works or not. Now, this one is working properly. It is a customized formulas, so it's mentioned, it mentions that Hume's double-click. You can see that it immediately converts all of the figures in words 13 thousand. That was only 5,500 Hz atoms only. This is how it will work. Now I need to convert that. I'm stood up is I'll go to Developer tab, go to Visual Basic, click on the module. What I'll do is simple Find and Replace Control F and go to the replace function. Now I've been replaced the humps by rupees and replace on 11 replacements, okay. Now there will also be single figure that should become converted to rupee replacements. Now the shortest form of that Holmes is filtered and we have, let's see, passes and replacements. And if it is filled, then it should be, Besser. Replace all. Close it. And let's drag it again. Now it shows this. You can use my custom formula as well. Maybe there is some problem with this online mentioned or maybe we're making some mistake. Okay, now, let's try to enter something which contains the passers are sense as well. So 40,532.32, let's see, see how perfect it is in this 40,532 rupees. And 32 passes. Perfect. Just double-click to apply on all. Now, since we have used the Developer tab to insert a new formula now it should be Macro Enabled Workbook, even if you try to save it will show you that do you want to save it and make it macro free workbook. Remember that? Always click on No. Because if you make it macro free, then this formula will not work. So right-click on no, safe. On this PC. The location is my assignment is this check printing system. Chick. Let's try it cash book macro. And ended. Sorry. I'll change the CVS type-2 macro enabled workbook and save it. This step is now complete. This is how you convert the amount in figures to amount in words in excellent. In the next step we will block the details on our check branding system and use the mail merge function to complete this task. 11. Connect Word Cheques Template With Excel: Now after converting the Excel file as a Macro Enabled Workbook, which we have just done. You've completed all the fields in Excel. Now the next step is connecting this Excel file with the word template of Czech writer. Now this tempered is of a and b, d, e and BD is a specific bank. Like emanates enmity. If you want the template for any other bank. Method is very simple. Let me just open it. It asks you to link with the Excel file, which we have linked earlier. Sometimes click No, because we will generate the fresh link. Now here we have D IT field name among inverts and among figures. Now let's say that I want to create or adjust template or some other bank, Let's see. Standard Chartered Bank. Let's suppose that the Standard Chartered Bank date comes here. Other fields are also different. What you can do is this is basically a heat and dry. What you can do is just take a printout of blank tech copies, just cut it out exactly like a check. Then burned the checks on that copies so that the original object doesn't get wasted. When you print it out with the printed. You will notice that maybe half of the field is adjusted according to your general idea. You need to scroll a little bit down so that it comes over the length and their district property. You will print once, twice. Third time, more time, and maximum fifth time. It will match all the fields according to your requirements. So just adjusted a little bit every time you'll see a difference between the box and your figure. That is how you do it. After that. Just go to File, Save As go to the location, and then save it like let's say Standard Chartered Bank. And click Okay. Now these are all the fields that is written for just more than indication purpose. Now that we know it, we will remove all the fields and we will connect it directly from Excel. To connect the Word file directly from Excel, so that it can pick up each data from the Excel file. The function we will use is called meal merge. So I'll go to meetings and I'll select the recipient. And since we already have the Excel file from where we will pick the data. So let's click on use an existing list. Now. Let's go to desktop. This is our cash book. Click, Okay. Now it contains three sheets for the written the book. And I have to connect it with Saturday given click, Okay. This is it. Now the Word and Excel are connected. Now from the Excel file. What data do we need here according to the check? Now, on the meaning step, just literally fire from select recipients. I have Insert merge fields, just drop it down. You can see all the fields that comes directly from Excellent. Here we want the date. Here, we want the name. Here, we need a mountain verts Among. As soon as we click on Review deserts. This is our first check that is created automatically, dedicate. We can update it from Xcel toward this is a second check, third check for check. And all the checks are now here. Isn't this cool? That's amazing right? Now the next step is if you want to see all the checks on a single sheet, you can find it easily gone finishing merch and click on Edit Individual documents. What it will do is, instead of showing it one-by-one, it will chew all the Jacks together. You can just generally reviewed. It is showing what each check. All. Just notice that this is a separate file. The name of this file is let us one let's say the name. I'll change it to checks, to print and click. Okay? Now here we have all the checks showing right here. Now one condition we will add here. Sometimes due to security reasons on the tick, we prefer to start it from equal to and ended with slash or dash, or in some cases 0, so that no one else can edit the figures. Mess with the check. What can we do about that? Now? I want you to complete it the same way until finish and merge. And then we will see further options which we haven't made much. 12. Edit Fields and Merge Results in Main Merge: Now to protect the check from being edited or overrided, some people protect the check figures by placing an equal to on the start. And after the conditional figure, the port slash or dash. Or in some countries they put 0 so that no one can increase the number of zeros. Although when it is presented to the bank, it must match with the amount in words as well. But still some people bought it. For security purpose. What we can do is we can automatically add that symbols right after the amount. The method is very simple. Just select the amount, make sure that this amount is selected in green. Right-click and click on Edit field. Now here we have a lot of options, but we will stick to it takes to insert before. Yes, I insert before equal to text, to insert after this slash and dash and click. Okay. You can notice that it will assign slash and dash to all the fields. Now the next thing after the completion of all this is let's say that we have finalized the salaries. Now we want to print all the checks. If I go to the file and click on Print, right now it's showing only one. Although if I click on print, it will print all the checks one-by-one. But to gain confidence about it, that it should print all of them together on a single click. You can finally go to finishing much and click on Edit Individual Documents. Merge Records, click OK. Now you will see on a separate Word file, this is a separate Word file which is created and compile it for you. We choose all the checks to be printed. Now it is also useful to general idea before actually bringing on the checks and wasting a lot of checks, you can actually get an idea on how the final result will look like. And maybe some of the fields like this one in the Indian is an empty field. Only the figure is there. This might be a total of all checks, but just showing like this. So afterwards does compile this and see, Let's see, checks to print. Let's say final. Click OK. Now one more option before moving forward. Let's suppose that V of compiled and attach excellent with word to compile all the checks. But there is a dispute with some employees and we want to hold the checks for now. What we can do is before printing, before finishing and much, you can click on Edit recipient list. Just uncheck the text that you want to hold. Similarly, if there is any extra fields to be printed, you can ignore that as well. You can uncheck that. So that will not be printed. And then click on Finish and much. This is how we can print unlimited checks in Excel by connecting it to the word tempted. Now the final step is you might be thinking that if there could be comma style for the big figures like for example, 1 million or 10 thousand, they should be comma between that so that it will be easier to read the figures. That can also be done, but that is a little bit detail or n complex procedure. I'll explain that in the next video. Just follow this until now and then move to the next video. 13. Toggle Field Codes to Apply Comma Style: Now, whenever the data is copied or linked from Excel into world, it doesn't maintain the formatting of currency formats or the comma style. We have to manually edit that. For that, we have to use the toggle button keys. I'll show you how to use this. First of all is just simply select the figure and right-click and click on the Toggle field goals. You will not see the full code like this. You have to expand this field a little bit. But we can see that the coding of this contains the slashes and dashes as well. So we have to make it simple. So just right-click and toggle field codes. At the time. First of all, we will go to edit, fill, and just uncheck these tools options to make it simple. Nowadays, Let's click on right-click Toggle field codes. Now we can see only one single field that is much field among. Basically, now we want to format it. So what we will do is within this curling bracket, I'll put a forward slash sign. Forward less sign is just before Enter key. And then we will write the code that is hash comma and hash, hash hash. That means we're born the Goma at first figure, like in thousandths. This is the thousands format which will automatically be converted to Lesson millions as well. So if the figure is 5 thousand, so five after that is coma, and then comes the three zeros. Let's see if it works or not. Doesn't work. Actually, the format is such that you put a forward slash sign and then breaths a hash and a space. This is represented debt. This field contains the number. Then comes your format. That is hash, hash, hash and hash. Then a space is required at the end as well. You have to follow this one. Make sure there is a gap after the amount. Single gap, then forward slash and hash, that is for number representation. Then again a space. And then comes your reinforcer, that is hash coma, hash, hash, hash, and then space at the end as well. Now it works for all the other fields, like 1150. Now let's try to customize this further. Let's see that if iodide two hashes here, what is the impact? Or choose with the two? And the single one as well. This one is ideal in most of the cases. What I will do. Now let's write rupees in the starting. Let's see if I want repeats to be mentioned. We can simply edit it from here as well. Text to insert before, yes, rupees, dot and space. Click. Okay, now it comes like this. And if you want to see the full quarter of it, right-click and click on Toggle Field Codes. It will show something like this. But let's just keep it simple. And what I'll do is for your convenience, I'll attach it as a separate text with the link of this video. So if you are facing any problem, you can just copy this format. So let's close it for now. W field codes. Now we can see that it works with the ten thousandths means the multiple digits and the singular digits, both. So that's how we apply the formatting style in the e-jet printing system. 14. Configure Gmail Account on Outlook 2016: Now let us see an other scenarios related to the mail merge that we've just learned. Let's say that we have a lot of customers. Here's the list of our customer, company name, contact person, customer, e-mail, month, and depending balance. Now, since this spending balance is bending from many months, we want to e-mail customer. But this template, I've compiled a template on Word high customer names would be here. This is a courtesy image to let you know that company, company named should appear here from Excel has some alternate payments off among trip gum. And link from Excel. Month name is this null overdue? We would greatly appreciate you will fixing up this outstanding balance at your soon as possible convenience. You can try this by calling it and being by credit card. So all of these details are mentioned. Now let's say that these are not three customers, but they are 300 customers. Would it be possible to sing emailed to all the long outstanding customers one-by-one, know even that if it is possible, it will take a lot of time to do that. We can easily do that with male much the same way we did for Czech writer. But before that, it is one additional option that will be used in this method and that is configuring the Outlook account on your PC and link it with Gmail or any other email that you mainly use. Only the outlook and send the e-mail statically from word. We have to configure that first. The first step is let's open Gmail. And under the Gmail account, go to Settings and click on see altered things. Now let's click on forwarding and POP and make sure that BOP status is enabled for all meals. Then click on I'm at make sure it's enabled. If it is disabled and click on enabling and save changes. Now let's scroll down and click on save changes. Now let's minimize this window and now we will go and open Outlook. Now also copy this address from the description or resources section of this video. And just peace this in the browser. I have a two-step verification turned on. Otherwise, you will see on the right-hand side to enable the less secure apps. Now let's minimize that screen and now let's open the outlook. Let's mention the email. It will be directly asked you for the Google login. Let's click Next. Now let's mention the password. Now since again, I have two step verification. So it will ask you on your mobile, just stickiness and click on. I responded from my phone. Otherwise it will just login directly. Allow to all of these. Now my account is added successfully. Let's click on Done. Now you can see that now I'm logged in to my Outlook account. And it will sync all the emails and extract all the folders from your mean email one-by-one. This is how you connect the Gmail account on Outlook 2016. As you can see, it's much easier than other versions like 20132010. It is very simple to do in 2016. Just do the step, configure Outlook. And then we will see in the next video about how you can directly send emails to all of your customers using the outlook future from Word document. 15. Sending Emails From Outlook Final: Now after successfully configuring the Gmail account on our glucose, the next step is configuring the email message and linking it with the fields in excellent. Let's open the Word file. You can find both of these links with the link of this video. Here is my template high, and here should come the name of the person from the Excel file. This is a courtesy email to let you know that your company. Here comes the company name has an outstanding payment off Among should be here for the month of this are now overdue and risk. All of the template is siem. Let's see how we can do that. Sometimes the file opens up in the protected view that has been you don't enable the editing feature. So let's open it again. Now here we can see Enable Editing option. Just click on Enable Editing. Now let's link it to the Excel file that we have that e-mails. This remember that finally we bid on both this message to send out to all the customers automatically using the Outlook feature and emailing them. But for that, you must have the e-mail with all customers on the Excel sheet. Select the Spin, use an existing list. Here's my list of customers. Click Okay. Now here should come the customer name. So let's remove this under the meeting step. Just go right here and insert the customer name here. This is the courtesy email to let you know that your company Let's remove this and insert feed. Company name has some outstanding payments of this amount. Let's insert the amount now. Ending balance for the month. Maybe the month field is also linked here. Month are now overdue. We would greatly appreciate fixing up this outstanding balance as we have composed this and linked it would all XL freed it. Let's click on Review desserts. First email. Autumn boards, 35 thousand burning. The next one we have is sought safe I mean ankle. Third one we have is Sophie Andrea Wheat product XY limited. And you can see that that balance and all the details change according to the Excel sheet. Now let's learn how we can send out these messages to individual customers via e-mail. I'll click on Finish and much. Send e-mail messages. I'll send it to the customer e-mail. Customer e-mail field is must in Microsoft Excel, you've created all the data for customers. Let's write the subject as beam integer reminder. The meal format will be HTML. Here we have different formats as well, but let's keep it to this standard regards to all click Okay. Now as you can see that the beam and remainder is now received here. But sometimes even when you send the e-mail from your Word document, and even if you have made all the settings, it will still not send the emails. Because of the less secured apps. What we can do is just Google it. Allow less secure apps right here, and click on the support. Then to turn on, go to this option. Then click on secure apps. Now, if you see this two-step verification, like I told you that, just barely fired by mobile. Even then, it will not turn on the less skewed apps like this. So you need to enable that. Let me show you how to disable two-step verification first, to see this kind of option for less secure apps so that you can find this button to directly turn on to allow the less secure apps. Let's see on our mobile how to disable the two-step verification for Gmail. What you can do is go to Gmail from your mobile. Click on the icon and click on Google account. Now here you can scroll to see the Security option. As you scroll down, you will see the two-step verification is on disk. Click on the Browse area. Right here, your password. Click Next, and click on turn off from here, and click on turn-off again. It will be successfully turned off. Afterwards. If you check the less secure apps refresh that, you will see this option. Just allow it from here and enable it. Then tried to send the e-mail. You will easily send email. Click, Okay? And afterwards you can see the payment reminder sent to you. This is how you can directly send emails by configuring Outlook with a particular email and send all the email statically from Word. 16. Database Management Part 1: Our next assignment is related to database management, where we will basically combine the knowledge of advanced Excel and business plus financial modelling. Basically, you understand all the basics and essential requirements of excellence that I kept on telling you that you will find advanced Excel. But we will discuss it here as well, so that we will be able to know how to handle the day-to-day database of any kind. It's not very complex. It will involve all the steps we have learned from advanced Excel course as well. And we will see some advanced techniques as well here. Here we have taken this scenario of a distribution company which has the distribution of some beverages like Pepsi fender, gawk do spread. There are different regions of large city in Pakistan. They are supplying these products. These are the salespersons will are making these silts. And these are the number of grids sort on each particular day. As you can see that this data is completely raw. Even the dude is not formulated in the date format, and we have to adjust the column size as well. And afterwards you will complete all the data. A lot of data is missing as well because it needs some formulas to be implemented to extract the price from the table. And then the rest of the figures will depend on the data. If you extract from here from the tables by formula. Let's get started. Now here we can see that the first requirement is connecting the date format. As we can see that this format is general right now. What we will do is just select the column and go to the Home tab. And under the home depth comes all the formatting options. From here, I'll change it from journal to short, it a little bit shorter, then you can expand the column size as well. The next requirement is as you can see, that a lot of columns have limited space or standard sized space, but they require some more space to fit in the data properly. So what we will do is we will not adjust the manually columns one-by-one. Select the column one. With the left-click and keyboard holding the click and start moving towards straight. What it will do, it will select the old columns. Then we will move the cursor to the center of any two columns. Whenever you see the center of any two columns like G and K, This is the line. When you come on this line, you will see a right and left arrow key. That means either you can hold and move it towards this will expand all columns. And if you want to reduce, it will reduce the column size. But we want to auto adjust the size according to eat columns, particular requirement, whichever columns need more space, should be distributed automatically according to that, what will be the method? Just double-click when you see discusses, just double-click. Here we go. Now for some fields, you will notice that the limited sort of field, but because the heading is much larger index, that's why they expanded the whole column size. The data will not be that much large for itself. What we can do in this scenario is just compress the column manually and then go to the Home tab and click on Wrap Text. What it will do. It will not expand the column size. Rather it will expand the rule to fit it in that you can do the same for any column you want. Now we have done the basic formatting. The next requirement is completing the data. And data will only be completed when we have the seal prices because we know that we have sold 460 credits, let's say for this beverage port, what is the sales price? But grid will determine the sales amount. And when we know the sales amount, then we can extract the discount on that amount. So for that, we had the sprays list, but it is only mentioned once. What we can do about it. Let's say I wanted to find out the Pepsi price. I'll keep that on my mind. Then let's start getting fan, a Pepsi. Pepsi and the price is 240. I can write it manually here, but we have a lot of beverages. The better way of doing this is applying the formula. Now the basics of formula will depend on how we humans do it. Formulas exactly the automation of human work. Let's see. We want to find Pepsi and their price. So imagine this huge price list. So we will start reading from top to bottom in the vertical sequence. And we will start finding Pepsi, pepsi, Pepsi. Here's Web. See. What you will notice is. That we are looking at for Pepsi in the word equal sequence. And then going to, they're just one column to see the price. This is exactly the function of Vlookup. Vlookup means vertical lookup. How we will apply this equals to vlookup. Whenever this formula press tab, you don't need to write the whole formula afterwards. Here comes the pool dip, that is the method of applying the formula. First is lookup value, for which product you want to find the price. Obviously, we're in this row, first row, and here it is mentioned. Pepsi, we want to extract the price of Pepsi. Gama table array. The ability simply means that from which data you can extract the sales prices. Here's my complete data. Now you have the choice. You can either start from here or you can select the whole table, including the headings afterwards. Goma. When you are done with this requirement, you have to press comma and then comes the column index number. Column index number is, let's suppose that you have find the product now afterwards, from which column do you want the price? In which column of the data is the price in the first column or in the second column. Obviously in this second column, the column index number is from which column we want to extract the price or our desert comma. The last requirement is exact match or approximate match. Just remember that exact match is always used in the text-based finding like Pepsi and dear price, Pepsi's are text-based thing to look up. This will almost always be exact match, while the approximate match depends on these kinds of data. For example, the discount between any sales between 20 thousand to 30 thousand is 4%. So it is a range between 20 to 30 thousand. In these scenarios, we will use approximate match, but here we will use exact match 0. You can either select it, it will automatically show false, or otherwise. 0 is also for exact match. Hit Enter. Here we have the sales price. Now, as we drag it down from this little point, just double-click to send it down. You will notice that a lot of data shown an editor of not available. Then we double-click on any cell to see the formula applied. You will easily notice and identify the error or what is wrong with the data. You will notice that the table's start movings or dragging downwards. We have to fix the table at the point that this data or lookup value should move or slide down. But the data should always be at a certain point fixed. Whenever you identify this mistake, you can connect it, but always connected from the beginning. Select the whole table or stability, and hit F4. Now in some keyboards, you need to hold fn function key with F4. So you will notice that the dollar signs are applied with the table array. The dollar sign is late to fix it on their points. You can see that now it is fixed on their point. Now to reapply, to hit enter, to save it in this cell, and then just double-click to send it down again. Now, you will notice that it will show the correct data. The more easier form of doing the same work is. You can simply select the table and name it as data. Price. Let's say price underscore. Now why I put an underscore? Because spaces are not allowed when you are naming the Indus. Hit Enter equals to vlookup. Find the beverage price in the table, array, price table. The column index is column number two, an exact match. Now since we have given the name of the table, we don't need to fix it. Whenever you name the table, it automatically fixed. Just double-click to sand it down. After extracting the sealed sprays for each beverage item. We have great sort and sales price both. So we can easily find the steels among seeing the moon formula is create sort of multi-cloud and sealed space equals to this cell multiplied by the cell. None of the cell needs fixing because they all should move down one by one so that now they can multiply on the next cell, H7 divided multiply by 168 in 759 multiplied by 168, and so on. So just double-click to send it down and then hold the column. If you want it to show in the comma style when you see the extra bool decimal places and you don't want it. Column is already selected. You can decrease the decimal places. Now it's done until now. In the next video, I will continue with the same assignment and we will see how we can extract the discount percentage using the VLOOKUP, but with some other adjustment like approximate function. 17. Database Management Part 2: Until now, we have extracted the sales amount. Now that we know the sales amount of each particular transaction, we can find the discount applicable on that particular seals amount. Let me show you how. If we go on the right-hand side, we can see the discount rates which says, if the sale amount is between 0 to 20,002% discount is applicable. Whereas if it is between 20 to 30,430% to 40,006%, we have to see in which particular slab rate that we follow. The amount is 110400. We most probably fall under this area. Grundy 2% will be applicable. But how can we apply that discount on it? If you can see that in these scenarios we will use VLOOKUP with approximate match. So all the syntax and formula application technique will be seemed to find out the discount percentage. But since one let 10,400 is not exactly match in any of these area, it is approximately match. So these are the scenarios very approximate natural US. We are the figures don't exactly match, but it matches within the inch. What are you going to do is just select all of the stable and let's name it so that we don't have to go down to select it and then fix the particular table. Let's write deed it and hit Enter. You can, you can mention space as relevant for that, you need to mention underscore instead of spaces. So DDH is fine. Let's enter. Now let's apply VLookup equals to V lookup. Lookup value is what we need to find down here. Lookup value is on which value do we need to find the discount greets on this value? So this is the value that we will be searching on. Does this countable people, it says from this table, table name is already said. Deviates. Does remember that whenever we name the particular table or a cell, we don't need to fix it. Afterwards. Column index number will be 1233. And on the last people mentioned approximate match. So 0 wars for exact match. And we will mention one for approximate match. That's it. Hit Enter. It shows point to two, which if we need to convert in percentages, we go to Home tab and we have the personal style here. We to send it down. Now it's applied to all of the fields. Now, we have the seal demo and we have the discount amount. Let's find out the discount amount based on these special dish sealed, multiply by discount percentage and hit Enter and double-click to send down. Here we can see kind of an error message. It is not an exactly error. It is just a caution which XL is giving you that maybe you have done something wrong. It says inconsistent formula. If you select the cell, you can see on the left there is a caution sign. It says inconsistent formula. The problem here is you have applied, you have multiplied these two fields in the sealed among and in the discount. You have also done almost the same thing. That is multiplied these two hormones. Now XL is indicating you that it may be an error that you have applied some other formula here which is not consistent with either of the right side or the left side. We need Excel to ignore that. Ignore that either we can right-click and click on Ignore error. And we can do the same for all the cells as well. The select the first cell, control shift down. And then under any option, you must see the option as well. So you can ignore the air like this. But if you want to turn off the error indication from the backend, how can we do that? Let's see. I'll go to the file and click on More, and then click on Options. I'm showing you the options of Microsoft Office 20162013 is almost similar. But if you're using some other versions like 2010, it might be a little bit different. You need to go to File and Excel options must be right at the bottom directly. So anyways, let's repeat what to file more and click on options. Now here is Formulas tab. Here is error checking rules. This Cs inconsistent calculated column formula in the tables. Let's uncheck this only. No, it's not finished yet. Options. Let's go to Formulas again. Formulas inconsistent with other formulas in the region. Just uncheck this click. Okay. Now we can see that we got rid of that error. Now the next step is net sales among net sales amount is obviously after discount equals to seal them up minus discount amount and hit Enter and just apply to all other cells. Select cell, double-click to send it down. For the next step. The cost of goods sold, we will just suppose that cost of goods sold is 40% of the gross sprays equal to seal them up. Multiply that 40%. This will be our cost of goods sold. This is just for an example, cost of goods sold. We are supposing that it is 40%. Just double-click to send it down. And profit. Let's extract profit now, let us save them on minus cost of goods sold and hit Enter. Just double-click to send it down. Now our data is complete. The next video, we will see how we can further customize the data and just finalize our this mean datasheet. 18. Database Management Part 3: All right, We have completed the data. Now we need to format it in order to improve the look of the overall cheat. You go to Home tab, go to Format as Table, and select any format you want. Or otherwise, I will just hit Control T from my keyboard. Just hold Control and press T. It asks you that VT is the data for your table. So it automatically selects the people till the bottom. But where did that you don't have any empty spaces or empty lines in between. That's really important to make sure that if you have any empty spaces, you should delete that. Or otherwise just read zeros 0 in that empty spaces. Multiple hazard is because I have headings as well in the streaming market. Click Okay, now because I have marked that, I have the headings. So it automatically assigns filter for all the headings that I can filter the data or sort the data from here. This is the main function of former distributor. 19. Database Management Part 4: Now that we have completed the data, we will see some details about the reporting based on this data. As we can see that all of you have completed the data. It is still in raw form and we cannot extract a deep analysis out of it. We need some particular reports like region-based contribution in total sales in terms of percentage, what is the sales of each beverage item per month or with salesperson is selling the most beverages in different regions per month or in which dates, months we have given the maximum discount as per the maximum sales generated. These are some of the analysis example that we want to see how the company is performing overall. So to extract these kinds of analysis, we need pivot table. What is pivot table? Pivot table can plot the data with simple drag-and-drop using your mean raw data. And then plot according to whatever requirements you want. To insert the pivot table, I'll go to Insert and click on PivotTable. Now, because we have given the table format that is controlled, it is showing the table name. What we can do is I'll go to Table Design. And we can also name it as mean data. It will automatically pick up complete data from top to bottom using this reference, the short key, whenever you want to use the short key, the golden short key is ordinate. So even if you don't know any particular shortly after pressing alternate, it will guide you through each menu. For example, now I want to go to Insert menu, to insert a pivot table. What is the short? If it is n ordinate, then I'll press N and for pivot table I'll press V. Let's select the Danish not enter. And we will paste the pivot table on the new worksheet. So as you can see that new worksheet is Mark. I'll click Okay. Now it will insert the data pivot table like this. Now let's understand the pivot table first before applying to any of the data. What is the concept of PivotTable? Pivot Table is the blank table, which has row headings area, the columns hurting area. Then the main data area. It is a blank people which fills the data according to your requirements, whatever data you want anyway, let's see. I want to see the region by seals off all salesperson. So salesperson OEMs root come here and regions should come here. And the data after applying the sum of according to each salesperson and the region should come here. This is the manual way. The modern we are doing the same work is pivot table also has two morphs. One is this advanced function and the other one is the classic format. Let me show you the classic format first and then we can easily understand this concept has been just right-click anywhere within the pivot table. And then go to Pivot Table Options. And under the Display tab, click on the class and PivotTable layout. Click Okay. Now you can see the exact same format, how I've shown you earlier. Here it sees dropped the rule fields here, heresies drop the column fields here and here, even drop the value of views, whatever we want. Let's see. I gave you an example of salesperson should come here, region should come here, and we want to extract the total sales salesperson. On the right-hand side, you can see all the fields directly extracted or linked from the master data. Just hold any of the name with your left mouse key. Just twirl it. You will notice that it is kind of a block or a brick which contains your data. Whenever you want to bring any notes. As soon as you see this green time, you can just drop our lead. The most key. Here comes the salesperson's. Now let's do the same for regions does toward drag and drop. Then we want to find the seals own drag-and-drop. See is Alina has made 1.5 million of sales in beta tongue and defense, 1.15 million in greedy shallow Goldberg, these are different sales. Let's do the same. Again. Let's delete this with the modern PivotTable. Let's press V and Enter. And then Enter again. Now, we will keep that classic pivot table form in mind. We know that the rule fields are, which is here, column fields are here and raw data here. Same data can be found here as well, rows, columns and values. We will do the same instead of dragging and dropping directly in the PivotTable. Now it doesn't allow me to drag and drop dedicate. Instead, have to drop it here. Salesperson, drop in the rules region, I'll drop in the columns. You can see that it extracted in the same form. And now I want a total of sales amount in the values. This is the Sun Zi, how cool it is. It performs the function within just a couple of drag and drops. Now if I wanted to further design it by default, the designers in blue and white. But as you can see, whenever we come within the pivot table, we can see the stool apps. We will analyze and design. Let's change it from the design, whatever it be born. Let's say this one. Now let's say that I want to see the seals of only the salespersons. And the seals means it should not be further divided in two regions. It should sued directly the potency for each salesperson. That means we don't want the columns aggregation. So what we will do is we will drag out the blank sheet. It shows the cancel sine. So it will get rid of this and now it's showing the total scenes of each salesperson. Now let us see that. I want not the salesperson, but the regions here. So now it turns into reason why sales. That's very simple. Now if I drag to the beverage item, it shows region-wide sales for each product. Now sometimes you have to manually analyze. Let's see, I have nearly seven regions. Let's see I have 70 products. I please the data regions here and the products here. Since it has 70 products, it will go too long in the horizontal form. Then I feel that this data is wrongly pleased. Product should be in the vertical form so that the data can be manageable. It is not going horizontally, but should go vertically when the data is more. Now, I want to change the sequence. Let's say I want to appear here and regions here. So simply do that. Here. Move beverages in the rows, and move regions in the bottoms symbol. That's how simple and easy it is to use pivot table to analyze your data. District is all these scenarios. Until then I'll see you in the next video with some more amazing functions of pivot table. And finally, we will see the dashboard reporting in this area. 20. Database Management Part 5: Now what if I want to insert more pivot tables on the same sheet? Now I have to cheat. Let's see. The sheet name is Dashboard data. Now I will insert one more PivotTable which I want to please on the same sheet, not on the new shoot. So ordinate envy and press Enter. Now it sees the new worksheet which I will select the existing worksheet. Just go into location and then choose dashboard Data and click anywhere you want to place the new pivot table and hit Okay. Now here is my new PivotTable. Let's say now I want to report off month by seals. So let's put the deed under the rules. Sometimes you'll see like this, Let's right-click and remove gears. Remove quarters. Let's remove data as well. Let's put rows one more time. The options in 2016 excellent is little bit different from what we have in 2013. But sometimes what I was trying to show you is when we please studied under the rules, maybe you can see only the t it's not the months. So we need to group it under months. But since we are using Excel 2016, it is automatically grouped. So let's put the suit and then we will see further. So I wonder total of sales, please, the sales in the values. Now I have the year wise sales amount total. Let's right-click and go to group. It will automatically analyze the starting date and the ending date under the data. In the data it will automatically collect the minimum date and the maximum date. It grouped it according to months, quarters, and years. Let's uncheck the quarters in years. I wanted to only compile it in months. Let's click. Okay. Now you can see that it is showing monthly sale, but it is one problem. Let's see what it is. If I group it, I can see the dude's off starting from first row generally do tongue 16, an ending to first of January 2018. That means it was approximately two years. If I didn't segregate it by years as well, what it will do is it will let us take a minus sales of January 2016 and January 2017 together. And same it will do for all the other months as well. We need to segregate in years as well to see the proper analysis. This is the analysis of 2016 sales month base. And this is for 2017 month wise. That's how we do it. The next analysis, let's see, is off, let's say order N V D and go to existing worksheet and place it anywhere like this. Now let's see. I want the analysis of region by sealed. So let's put the region in the rules and let's scroll down. Now let's support the sales amount under the values. Right now it's showing the total of each region. Let's say I want to see the total transaction in world that made up the seals. Let's see how many transitions are the lead two hundred, three hundred transactions. That means the total of the sale. I wanted to see the count of transactions as well. What I'll do is I'll put the sales amount again in the values field. Now I can see the second column with exactly the same figures. But what I'll do now is right-click and summarize value as not some but count. So you can see that now it's showing that town is with this seals and in waltz 86 transactions. Afterwards, if you want to change the labels, this analysis, you can do that as well. Region data, let's say month, let's say total sales and total transactions. This is how we can see the multiple results using the same value fields. So just follow these one-by-one, this practice on some pivot table by your own drag-and-drop and extraction different results. Then we will see in the next reduce some more functions. 21. Database Management Part 6: Now let's see one more pivot table. I'll go back to my master data and click on ordinate n v. And I'll place it on my existing worksheet. Just choose the location, I'll place it here. Click Okay. Now let's say I wanted to DIJ and wastes sealed contribution as a percentage of total seats in terms of percentage I wanted. But let's not complex it. I'll simply say just extract for me the reason why sales. So you will see it's very simple. I'll drag the region here, scroll down a little bit, and then please the sales amount here. But this is region by seals in among form not in the percentage. If you want to convert in person, did this right-click, Right-click. You can see in the Show Value As we have a lot of options to choose from, I'll change it to percentage of grand total. Now it's showing that if the seal is a 100%, what is the contribution in terms of percentage for each region? You can see it is more readable, even better than figures. Because in figures you need to concentrate on the figures to see what is performing well and whose contribution is the most. Here in terms of force, indeed, we can easily see that the most contribute during region is this in the total sales. And if we see in the percentage style, we can easily tell you that defense is the most contributing region in the overall regions, which is making the most sales. Now let's see one more interesting scenario and then we will move to the dashboard reporting. Let's see that we need one more column. Let's say I want the column to appear between cost of goods sold and profit. So what I'll do is I'll hold the column of profit and right-click and insert. Now let us see this as other CGS. Let's assume that this cell is the cell. Multiply by 20%. Let's say 10% of cost of goods sold. Here is my field. I won't be using this, but I just wanted to show you one thing. Now if I see that my existing pivot table, we cannot see this new column data here. What we can do is we will simply right-click refresh. And let's now see that all of our data is refreshed, including this pivot table fields. Now we can see this new field as well. That's how we will include the new field. Now let's assume one more thing. Let's see. I'll delete it. Let's see, I don't want to include a special column or a new column in the data table. But still I want the new column here. What you can do is you can create a new calculated field as well. This is an amazing function of PivotTable that you can create your own fields within the pivot table only. Go to PivotTable, Analyze, go to Fields, items and Sets, and click on calculated field. You can simply define the formula here. Let's see. Other CGS is the name of the field. And the formula involved is, I'll take CGS, just double-click to insert it and multiply it by 20%. That is the formula. It can also involve multiple complex formulas as well. This is just a simple example. Just click Okay. So T is with the same name, it exist. So let's see. Let's say other serious to, let's see, let's say other profit. Because we have created the field already. Maybe it's not taking that field because of that. Let's see profit and 10%. Let's see shattered profit, 10%. Click Okay. This is the sum of charity that we will give on that particular region sales. Let's see. If I want to create a new table now. I cannot see the free lower column here of the Saturday, but I can. Let's use the existing sheet like regions here. And let's see the chatter team out here. We can use that, we can create that, but it will not be a part of our master data sheet. It will be created from the fields within the fields in the pivot table. That is an other amazing function of the pivot table. Now let's move to our next video and see the dashboard reporting. 22. Database Management - Creating Charts and Static Dashboards: Now let's move to the next amazing video. For that, I delete this dashboard data and we will start it from scratch. Because here I'll see the dashboard reporting that involves applying the charts to all of these pivot tables. We will create this reasonable pivot tables that we only want for the charts. And then we will apply the graphs or charts like bar chart, line graph, pie chart, these kinds of charts here, we have some extra data here. That's why we're deleting it. Just right-click and delete. Now let's reanalyze. It will be reviewing for you as well. Let's insert the first pivot table. We will not use the mouse. Now we will use the keyboard shortcut. And what was that? It was alternate envy and Enter. And again enter on the new worksheet. So don't worry about if it is showing pivot table seven because we have inserted some couple of PivotTables before. It doesn't just the sequence, but that we have deleted. So we can start from new. Let's see, the first analysis I want is month wise and year wise sales. So let's place the sales in millions field. Now let's go to group and just uncheck the quarters. I wanted to be segregated in months and years. Click Okay. Because there's, this involves multiple years. That's why I wanted to segregation in years as well. This is done. Now let's see and apply another pivot table. Ordinate envy, existing worksheet. Let's go to the sheet. And please is right here. Click Okay. Now I want here salesperson in the root's regions in the columns. Then we want the seals amount to appear in the values. Simple analysis again. Just remember that whenever you will use this in applying charts or dashboard reporting, you might be confused with the pivot table names. Let's name it properly. If you select any pivot table, you will see this PivotTable Analyze button. Just go to Pivot Table Options from here and here it is, PivotTable seven. Let's name it as, let's say month, monthly sales. Let's see. Let's make it short and hit enter. Now let's check again. If I go here and go back to the PivotTable, let's go to the PivotTable InDesign. And here I can see that the name has changed to moderately seals. Now here it sees in this ADATs, but we're doing it now again, it might confuse you. So let's change the name to seals. Parsons regent. Hit Enter. And now the final pivot table that we want is I'll press again coordinate n v. And I'll go to the existing worksheet and I'll place it here and click Okay. Now here I will extract the pivot table off region wise sales contribution. If you remember that we have pleased the regions here and we've simply extracted the seals first with the values, and then right-click and click on Show Value As percentage of grand total. So it will show you the person did. Now if I also wanted to sort it from the lowest to the largest percentage, I can simply right-click and click on the sort and smallest to largest or largest to smallest, as you wanted. Now these are my favorite tables. I can read pyruvates formed. Now I will apply our chart on it, and I'll simply compile all of these, not here. I'll place all the charge on a separate sheet, and that is called the dashboard reporting. Dashboard reporting is growing in popularity nowadays. It's simply the policing of all the charts on a single tab. So that you can see quickly through two arcs and analyze the company organon performance. It is a graphical representation of all the sunrise Jagger in the chart form. And I'll show you how to create the dynamic charts as well, so that as you change the live data, it will automatically reflect it in charts through buttons. We will create that buttons as well. Let's format the sheet. I don't want it in white. I'll select the whole cheat. Between one and e, You can see this kind of folded page, the select that and change it to black. And now we will keep our distance from the top to about eight lines or nine lines, whatever you want. For our timeline that we will insert later on. And first chart we'll apply here. In this first chart. For the most of charts, we have the option of line chart, bar chart, and pie chart. Whenever the data is changing constantly or data is in large Among, then we will use the line chart when the data is limited. And data is also in, let's say, in big variations like Amanda Mansfield, then we will use the bar chart. Here. I can see that the seals is, although it's not changing after every minute, like share prices in which the line chart would be obviously suitable. But even if it is multi-celled, it is still a lot of months, like Grundy four months to analyze. That's why we will use a line chart because it has a lot of data. Similarly here, just remember that whenever you have two-dimensional data, that is row and columns to be as data. You cannot use pie chart, you cannot use a line chart. You only have the option to use bar chart. Here. Obviously, in the person D design, you will use the pie chart. By chart shows the portion of each ADR within a pie. Let's insert our first chart and that is line graph. So this confident the data, you will see the PivotTable Analyze button and Design button. I'll go to the Analyze button and click on pivot charts. Now, as I told you, like charters most suitable, select the first chart and click Okay. Now let's formulate further. Go to the Design tab. And we have a lot of formats. I'll select the black because my team is black. Let's change the, let's change the total. Let's see sales analysis. Now let's got this and paste it here. This will be our first chart. For the second chart, as explained you that since this is two-dimensional data, line chart and bar chart one work properly. I'll also show you, Let's see, I selected this pivot chart. You can see that it only picked up the data for barrier down. It will not be able to pick all of the data. That's why we cannot use this. Similarly, if we use the line chart, you can see the lines will show something like this. It will be over complicated to read. The line chart. Column chart is suitable, but even if we apply the column chart, the chart should not be overcrowded. And we can see here that the chart is overcrowded here is that because for each salesperson, it shows the seals, but every single bar, if we have arid regions, at least showing it separate bars for just one salesperson. So although it is much more readable as compared to the other two charts, but still it is not visible. We will change it to our chart so that it will be a single bar, but it will denote in different colors that which region is selling more. We don't need to delete it. We will just simply right-click and click on Change Chart Type. From here instead of clustered, go to the stock column and click OK. Now we can see that the data is much more manageable and we can also read that. Let's see, for omer, which region do you think is selling the most defense because it is in orange. We can clearly see that, let's say for the least seals is in more down and so on and so forth. This is also manageable. Now I'll change the design and I'll select the black sequence black team. Now let's cut it and paste it in the dashboard. Let's go back again. And the third one we have is this data and we will apply the pie chart on it. I'll go to PivotTable, Analyze and click on pivot charts. Select pie chart, and click OK. I'll go to the Design tab again. Let's change it to black. If you want to increase the size of Despite. Just click on the corner, you can see this indicators. You can hold any of them to expand the size from the corners. Now let's change the name to the region contribution. Let's see. Now let's cut it and paste it on my dashboard. Let's just minimize the size a little bit. It looks like this. That's how we apply the charts on our data to create a static dashboard. In the next video, I'll show you how to create a dynamic dashboard or change this data to dynamic dashboards so that it can change it according to the filters or burdens. Follow it until now. And then we will move to the next video. 23. Database Management - Convert Static Charts to Dynamic Charts: Now since it is a static dashboard, I need to convert it in the dynamic dashboard. What is dynamic dashboard? It will work according to some filters, some beautiful buttons that will change your data as you need. You will not understand in this way. I'll show you exactly the steps of doing that thing. Let's just select the first chart first and then go to insert. And here we have two options, timeline and slicer. Just remember that anything which is related to DH, month years will work bed timeline. So I'll select timely and it only shows the option of date field. Click OK. As we click Okay, we have a timeline that we can use here. Just expand this. So for this, we have kept this area. If you want to format it further, as soon as we apply our timeline, we can see our tab related to that. I'll open it and go here in front of the options. You can change this. Now. Here it shows the data from 2016 to 2017 according to each month. In the chart, we have the whole data of two years. Now let's see. If I only want to see the data from March until August of 2016. As I leave the mouse, it will only show us the analysis of that particular months. Similarly, as we change the data or we select anybody regular area, it will change the charge accordingly. This is basically dynamic dashboard, which changes according to your filters easily so that you can analyze the seasonal trends or anything you want to discuss in your meetings. Now if we notice, just tell me that with this django filters, only this status changing or all the other charts are changing as well. Only this chart is moving and others are still static. Now, I want to connect this timing with all the charts as well. What I'll do is I'll right-click here on the timeline. Right-click and click on report connections. Now, if we see here, we have named our tables. You'll connect to all of these three to over tables. It will be easier for us to analyze as well. Now we can see that as we change the data, it shows the objective results for all the charts and all the charts are moving along with the timeline. Now, similar thing we will be doing for the filters. I'll select any of the chart. Let's see this one and go to insert. And this time I'll select slicers. Now slicers is to further slice down the result. Like you weren't this particular region for specific beverages regions and salesperson. It won't involve the data that is different for all transactions like create swirl is just a number, sealed amount is just a number, person is just a percentage. So anything which is in numbers cannot be filtered, like beverages, regions and salespersons. Click Okay. So we have these three filters. I'll arrange it. We can change the format of each, change it to beautiful formats. Now as we click on our ****, This is the sales for only a short. If I wanted to further realize and I should Coca-Cola sales, this is our shirts. Coca-cola sells. Coca-cola sales in defense. This defense sales. So we can apply one single filter on multiple filters to work with the same chart. Now seen as the case here, only, the timeline is moving and the other charts are static. That is why, because we're selected discharge and apply filters, it is now connected to only discharge. Now we will just right-click and click on report connections to connect with other two as well. Similar is the case here. Connected, right-click. Report connections, connect this element. Now these will work with each filter. Now. That is how we can create dashboards, a beautiful dashboard out of this pivot charts and convert our static charts to the dynamic charts. 24. Power Pivot Introduction: The next function that we are going to discuss is PowerPivot. Bomber pivot is a very important and essential data management tool that can handle both later in Excel. It is basically developed by Microsoft Team, which is mastered in data handling. Basically it is not developed by the XL team itself. Rather it is developed by the SQL database software team. If you know that Microsoft has a database management software that is called SQL, that is used to manage the databases of ERP systems. So that team has worked on it. And the main purpose of it is handling the large attend welcome owns. The main purpose of developing the power pivot is dead are lots of scenarios where the data rules are in millions. So whenever, even if you are applying the symbol function or formula like VLookup, it will take a lot of time to process if the data has millions of rows. That's why the Power Pivot plays an important role here. It can handle the database in millions without any problem scenarios. We can use the Power Pivot. Let's see. We have a couple of different tables here. Here we have the product name and the relevant prices, the salesperson and the regions, the product and the discount is applicable. Here is the mean data, which contains the deeds, the sales representative that is making that seals the product name and the units sold. So it is exactly what we have done in the last assignments of database management. But the one thing is change that if you want to extract the prices of the particular product, Let's say I want the price of Pepsi here. The formula we were applying before was Vlookup. Vlookup lookup value will be, this. Diversity will be, we will pick from here. Column index will be to an exact match. But now we will see that. Let's see that this formula needs to be applied in millions of rows. So it will definitely make sheet too slow and it can create problems for us. In these scenarios, our pivot will be used. Now let's see how to do the VLOOKUP work with PowerPivot. But before that, I need to activate by the Power Pivot. I already have the tab, but if you don't have the tab, Let's click anywhere under any tab. Just go to a white or a blank section. Just right-click here, and then click on the Customize Ribbon and go to Add-ins. And then click on OEM add-ins, select coil meetings from here and click Go. From here. You can activate the Power Pivot and click. Okay. Then you can see the step. The first step that we need to follow to handle the data in Power Pivot is we will load all of these tables in Add to Data Model. So basically, like I told you that it is a separate database management completely. So the handling will also be different. It will not be 100 on this mean sheet. Rather, it will be handed in a separate data model. We will load all of these sheets or you can see that the data's in the data model. Now the best we do load the data in the rater models from Excel is to name the table by formatting as control. We will consider each of these as a separate database. So let's format it as stable. Control D is the shortcut create table. My table has headers. Click Okay. Now this is formatted. If you wanted to change the format, you can change from here as well. Or if you want to completely change the format from scratch, then you can select the whole table, click on Normal, and then Control T and Enter so that it will give you the proper format. The bulb is off, omitting it as stable, is it will enable a new tab that is stable design where you can actually name the table. This will be very useful for us in future for PowerPivot. So let's see. D. D means datum is handling product. Price. Table. Hit Enter. So this is the name. Now, just make sure when you name the table, just select anywhere else and then gum within the table to see that the name is still there or it is showing the proper name or not. Now let's continue. Let's fill this as well and press Control T and Enter. You can change any format if you want. Let's name this as the product. Discount. D means dimension. Now let's select anywhere else and go again here to check the name is feeded or not. Now similarly for the third table, normal first, and you don't need to select the whole table to form it, establish it as table to select one particular cell within that table and hit Control and Enter. Now let's name it as B. Underscore. Sales Rep. By region, hit enter. Now let's click anywhere else to check it is properly fitted. And now comes our mean datasheet, that is the master sheet. Hit Control T and enter. And let's name it as master data. Now, it enter. Crosscheck. Now what is the main purpose of formatting the data stable for the Power Pivot Is it will automatically expand the range. Let's say that we have selected a particular range and we have named it as product price. Now since the accident database is different from the Power Pivot database, we will, we will add the data. We need the system. So we need such kind of tables that can automatically take new data within this range. When you format it as stable, you will notice that when you come on the last row, let's say we need to enter one other item as well. So let's press tab. You can notice that it expands the data. Now it expands even more. So the benefit of this is, let's see. It is our test item and Grundy three is surprise. Whenever I will select and other data or refer to this data, it will automatically include the new items as well. Control Z to undo. This is the main benefit of it. I'll show you later on to give you a proper understanding of what I'm trying to say. Now, let's start entering or adding all of these tables to data model. I'll select the first table and go to the Power Pivot and click on Add to Data morning, it will take some time. Then it will upload it something like this. You can notice that this sheet is a little bit different from your normal X-ray database sheet. So as I told you that power pivot is maintained in a separate database sheet. Now let's minimize this. Let's select the second one and click on Add to Data Model. Let's minimize third one, add to Data Model. Now let's minimize fourth one algorithm model. Now since I showed you an example of this, I want, Let's see, product price. We did it before with VLookup. But since I told you the scenario PowerPivot null, what we will do is we will go to Manage and click on Diagram View after loading all of these databases. These databases look more or less like Microsoft Access databases. If you have used that, it will be beneficial here as well. We will simply make the relationship with mean master data sheet off different sheets so that it can directly extract the data from here. We need a common thing to make the relationship. For example, if we want to extract the price of the product, product name should be in this sheet as well. An industry. That particular name or field should be common to make the relationship. Now let's say that I want to link sales representative by region sheet. The distributor sales representative to sales representative is common. Just toward the left click and start moving towards the sales representative here. You will notice that now it is linked and made the relationship. Similarly, I'll link product name to the product name. Similarly in the third database, rock name is common, so let's link it with the product name. All of the other databases are linked now to the master data. Now let's come back to the data view and we have different tabs here. Now let us see, this is my master data sheet. Now let's see the master datasheet. Since we have now made the relationship, it will be very easy for us to extract the product price, the relation, not to mention the price. I need to name this column. First. Let's double-click and let's write any name. Let's write unit price. Hit Enter. It will create this new column. Now, I need to extract the sales price according to the product. I don't need to do anything. I have to apply VLookup neither any if conditions. Now, what simply we need to do is we will write equal two. You will notice that whenever we write equal to in this database, it will not show up here. It will only show up in the formula bar. Now we will write related press tab. I'll go in the database, weird the prices are mentioned and simply select the column from where it should extract the braces. If you hit Enter, it will automatically close the bracket. If you are using Excel 2016, if you're using any other version, you need to manually close the brackets and then it will automatically extract all the data. As you can see, Pepsi price is 16 here and lefty price is 16 here. That means that brace should be 16. Similarly, you can check for a couple of random items, like for contributes, this is 13, country choose 13. Now, it extracted the exact data with a simple formula. Only. Name the column equals two related. And then we went to the product brace and the selected the price and hit Enter. See how simple it is. The most important thing. Why we are using this if you are related to sales or marketing or anything which involves data's in millions. It can handle large data very quickly and it will update the results very quickly. Which VLOOKUP will be unable to do. That is the main purpose of Power Pivot. Now let's extract the discount. Now. We have made the deletion under the diagram. Now, the process is simple for all the fields. Let's try to discount here. Let's simply write equals two related. Then go to the product discount and select the product discount. Let's try one new thing here. If we keep here, keep it here related, and then select the product discount. Now let's do the same thing for discount. Since we also added the discount in the diagram mic relationship, we will use the same formula equals to delete it and just go to the product discount and select the discount column and hit Enter. It will quickly extract the discount for all. Then you can format it as the percentage. Then let's add another column. And that is, and now we will add a new column than that is sealed. Amount. Hit Enter. Now sales amount is the multiplication of unit prices with the number of units or equals to select this cell, equals to select this column, multiply by this column. Now to extract the seals amount, we will use the symbol formula. And that is equal to unit. And that is equals two units multiplied by unit price and hit Enter. Now it shows the result 29 multiplied by 16 is for 64. You can check the total width calculator. This is how you can handle the bug database to look up the values. True power vivid. Now if I need to save this, I'll press simply Control S and disclose this. Whenever you want the results from here. Just go to your main sheet, open it, and go to Power Pivot and click on Manage. Here you can see all of your results. This is how you can handle the Power Pivot in Excel. 25. Using Power Pivot for Pivot Table: Now the next function that we want to see here is applying the Pivot Table. In the Power Pivot function. We will see the power of Power Pivot when we apply the pivot table with this new technique. But let's just first understand the pivot table and how PivotTable box so that we can identify the shortcomings of normally permit table. And then we will see how the Power Pivot results. This shortcomings through the PowerPivot. Now let's see the master data sheet. For example, this is my master data sheet. Now, you'll notice that this assignment is little bit different with the assignment that we have covered earlier. Although the data seem we have kept the data on differentiates intentionally. And we have inserted a separate column with a VLOOKUP for the cluster of sales. You can download this assignment from the link of this video. Now here if I want to remove this formula so that it can become independent videos without the formula. So what we will do is we will select the whole column, pop it, control C and P special as values. What was the short key or ESV? And Enter, specifically alt plus E plus S plus V. Just press Alt and leave it. And then one-by-one, press E first, then S, then V. Then it will be pasted as values based as values means the formula will be removed and only the values will be remaining. Now let's see. I want to see the cost of sales according to the sales representative or product name as perceived, or monthly cost of sales for each particular item. We have already seen in the previous assignments how to deal with these scenarios in pivot table. Now let's see how we can apply these pivot table. I'll show you completely. You can either select the whole data or just if there is no gap in data or no blank fields, you can just directly go to insert and insert pivot table. It will automatically select the whole data, as you can see here. Give it a religious show from A1 cell to D 27 cell. Now let's place the pivot table on a new worksheet. Now let's click. Okay, right now it's showing all the fields it has extracted from the master data sheet. Now let's say I wanted to seal the representative in the rows. So just hold and drop it in Rows. Let's drop the product in the columns, and let's drop the product cost of sales in the values. As we can see that it shows the total cost of sales for all particular salesperson according to particular beverage item. Now let's see if I'm only worn the cost of sales according to the sales representative, then we can a select and cancel this by placing it in the sheet. Or we can directly just uncheck this from here. I've shown you all of this pivot table by manually applying this, because I wanted to show you that deductible is already very simple. What is the use of Power Pivot and what is the shortcoming of PivotTable? Basically, that Power Pivot can resolve. Now let's see the scenario. We have a separate tree tab, ds rep. Here we have silt representative regions and units. I want the units of a particular representative be shown according to the product name. Because I already know that sales representative has the connection with product names. So I want the unit wise analysis of the particular product according to the sales representative. You got my point. Now the sharp coming that vertebral has is it cannot meet a relation between differentiate depths. It can only work if all the data is pleased under one Jude and that is Master sheet. If we have multiple tabs, it cannot mean that relation and show the combined result in under one pivot table. That is the issue that pivot table has. That is the issue that PowerPivot can resolve. Now let's see how the Power Pivot works. Let's delete this PivotTable and let's go to each sheet tab. And as we've done before, we will format it as D, Control D. Click. Okay. And as soon as we formulate a testable table design, new tab opens up. And let's write the name of the table. And that is price. It enter the means dimension and b means product price. Now let's go to the second sheet, tab, control Enter. And here we will say dimensions, Product, discount. Hit enter. Now let's move to the next fit, and that is sealed representative. Let's hit Control and Enter elder people name. I'll change it to d four dimension n. Let's write the same name as rep and hit Enter. Now the last sheet that I'll hit no fill first, I'll hit Control. Enter. Let's try keep the name as master. Now let's start loading the data under the PowerPivot database, and it will be done in the same way as we have done before. Add to Data Model the school to each table one by one. Click anywhere within the table and click on Add to Data Model. Let's minimize and go to the second sheet. Data mode. Touch it and through data morning. And finally the fourth cheat at the data model. All of our sheets is now loaded in the data models. Basically if all the tables are in one sheet down or each day we'll just consisted in different sheet tabs or PowerPivot. It is same thing. Finally, as we've done before, after that, after uploading all the tables, Let's go to the diagram view. And since we have to make the connection with our main sheet to all the other sheets. So let's please the master. You can hold it by heading and then move downwards. Now let's link the common fields. For example, in the DB price, we have product name that is related to product mean. Now again, for DB discount product name is again common. So let's make the common. Now here, the sales representative should be linked to sales representative field. Now whenever you want to change the relationship or you have made any mistake, you can just right-click or not right-click, but just select this and double-click. And you will open up in Utrecht relationship. Let's close this. Now we will apply the pivot table on these reports. So pivot table function is also available in this Power Pivot special window. I'll definitely go in PivotTable from here. Let's paste it in the new worksheet. Now you can see that v-hat, the data for all the four cheat tabs under one Tibor table, which is impossible to do in the normal PivotTable. Now, if we try to make an analysis out of Master sheet, Let's say I want to see the product name, cluster of seals. So we can easily do that, but that is part of normal pivot table and that is what normal pivot table can easily do. We will make a relation such that the normal puberty will, won't be able to do that. That will be a fair, so that will be an amazing thing to see. So under the BCL trap, we have the sales representative, regions and units. While in the Master sheet we have the sales representative, but we don't have the units wise seal. What we can do is we will pick the products from product name from the master sheet. We will drag and drop this. And let's take the unit from this sheet. What is the relation between these? Here? We don't have the product name even, but we have the sales representative and seals. And under the master sheet, we know that this products are sold by this this salesperson. So it can easily analyze that according to the product, what is the unit sold for each word? Because there is a relationship between sales representative in the background. So it already can relate that this is representative has sold these products overall, the products seals are these units. Basically what it is actually doing is it is not matching the product by its units because we cannot match like this. It is matching the sealed isn't intuitive and trying to identify that how much easily sales representative it has sold units and what each of these integrative is selling under the products. For example, elisa is selling Pepsi. It is automatically making the relationship according to the sales representative by matching the products sold by a0 tilde and then extracting the unit sword. So see how cool it is with some simple clicks, it easily analyzes the data that will take a lot of time for you to compile. Now you can cross-check by yourself and see the product relation for each sales representative, and then identify whether it's extracted the right amount of units or not. So with this said, let's move to the next video and a new scenario. 26. Using Scenario Manager: In excludes there is one other very useful and effective tool for budgeting and forecasting. And that is the function of Scenario Manager. Now let's see that you have prepared the budget for a couple of months, where it is showing the revenue for different months. And then it will show you the expenses of each month. According to this assumption that Let's see, in the first month, the expenses, 5% of the revenue expense minus 5% of the revenue, 7% of the revenue, 15% of the revenue, 22%, 4. And now after that, it will increase over time according to the person date of assumption. It should automatically pop up as you change the percentage, All of the budget should change. The scenario Managers please. A vital role in when we want the growth assumptions in three or four formats. Basically these figures are, these assumptions keeps on changing because you are creating a better grade. This is just a budget and forecast. Let's see that we have seen and analyzed all the scenarios and the expense that we will incur according to these person datas. Now you want to save this as, let's say suggestion one or scenario one. So that even if we change figures to a second scenario, whenever we want to see our first scenario, it will give us the comparison or buttons to see that. That is the beam use of Scenario Manager. So basically you will understand all the concepts when we practically apply this. Let's start applying and then I'll show you what the scenario manager actually does. Let's first analyze the return, extract the expensive one according to the revenue. And that is, let's say equals two revenue multiply with since expense one is 5% of revenue, I'll select this 5% and hit Enter. Now, then I drag it towards the right. Now as we have applied the formula, I need to set to the formula. Fixed the formula in such a way that I can simply drag it towards right? And it should show all the analysis. But right now it's not doing that. Because we have not fixed the formula property. We need to fix the formula not just for expense one, but all the expenses. So I can simply drag from top to bottom and then from bottom to towards right. And it will show all the expenses. We have to adjust the formula sanctioning. So basically we need to fix some variables. Now what do you think if I drag the formula towards right? Now, it should chew expense as a percentage of February revenue rate. That means when I drag the formula towards this central, changed their position and come towards, that means the column should move. But when I drag it, for example, for expense to then even expense two is according to the revenue percentage rate is 7.5% of the revenue. So it should not come down. When I drag it down, it should not come down. But when I drag it towards it should move. That means Golem should switch and router should not switch. Whatever you want to fix, you will put a dollar sign behind that. So if it is behind the rule, that means sees, fix the row and column is filled. Similarly, let's see this scenario for this sequence. Then I move my calculation towards right. Then what do you think this exponential school going this way? No. Because for January, February, March, April, May, June, we need five version of the revenue. So it doesn't matter if the data or D cell is moving. In this way. It should not move from here. That means the column should not move. Or switch disposition from B2C, it should not move. So Golem will be fixed. What about the rule now? Let's see if I drag it towards downwards. Now it should not 5% of the revenue, but 7.5%. That means that rule should move. Rule is free and column is fixed. Now, let's see the magic of it. If I move it towards right, it shows all the expenses and then just double-click to send her down and it shows all the expenses. Now one more thing, since it is RANDBETWEEN, it will keep on searching two different figures. So let's fix that. Let's select all of these numbers. And get rid of this formula. Control C. And I've shown you the formula or ESV, and enter what it does it is it will remove the formula from the background and only give the values on the friend. This scenario is complete. Now, just remember that whenever you apply the formula, just check randomly that whether the scenarios following properly, this expense for should be revenue and expense should be 22.5% of the revenue. So perfect, Perfect cells are selected until all the functions we have seen are the normal functions of excellent Now comes to roll off the scenario manager. Since this is my scenario one where we have assumed that expensive one will be 5% of the revenue, 7.5%, then 15% and onwards. Now if I want to change the figures for a different budget, let's select all of these fields and go to Data What-if Analysis and click on Scenario Manager. Here I will add a new scenario. I'll name it as. Let's see it, this is sec one. Let's click Okay, keep all the settings as it is. Click Okay. It asks you for the prompt again that these are the cells you have selected. If you wanted to change the figures, you can directly make changes from here, but I'll give it as it is and click on. Okay, this is my set one. Now let's close this and let's change the percentages now. Let us see to 23.51%.8973. This is my scenario two. Let's do this again. I'll select the data and go to What-If Analysis and click on Scenario Manager once again and click on Add. Now this is my set to it is my ranges. Click Okay. These are my figures. Click Okay. Now the two sets are saved. By the way, you're not only can select only one column, you can select the whole scenario as well. Let's see if I want to involve these two columns, I can do that as well. Let's select this. What if analysis, scenario manager. Click on Add. Let's say set three. Let's actually change the figures. Let's select this. What if analysis, scenario manager add. Let's say set three, Enter. And now let's see the effect of it. Let's say that we have created the multiple scenarios. And now we want to see what we have done in set one. We don't have to write all the figures manually. And especially this is just an hourly rate. So let's say that we have more than 60 expenses. Even you have listed written on paper. It will leave a lot of time to enter all the personal data is once again. So I don't need to do all of these things because my scenarios are saved. I'll simply go to What-If Analysis and go to Scenario Manager. And let's say, I wanted to see the second one. Select set one, short. It will change all the percentages now set to show. It will change all the scenarios and the total figures obviously works according to this. Following this person did this as a formula. And finally, step three, you can easily check multiple scenarios with some single clicks. That is the power of Scenario Manager. So basically you cannot only apply this in this kind of scenario, just relate it with your Delete life or with your daily working. You can implement this kind of function in multiple scenarios, whatever your scenario is according to your organization. This is just to give you an idea of Scenario Manager and how it works. You can use on your data and on different kind of analysis as well. So basically what I'm trying to say is it doesn't only work on budgeting, forecasting, it can work on other scenarios as well if you want to plan it like that. Now, we have learned that how the Scenario Manager works. But every time to play these scenarios or switch from set one, set two, set three, we have to go to the data then what if analysis scenario manager, and then we can switch the sets. What we can do is we can add a tightly in the quick access toolbar. This is the quick access toolbar. From here. Right now we can only see, we only have the save button. Let's add the Scenario Manager. Here. I'll go to the File, click on More, and click on Options. And from here I'll go to Customize Ribbon. I'll go to Quick Access Toolbar. Now. Let's change it to All Commands. Now let's search by pressing Scenario Managers several times until you reach the Scenario Manager. Here I have two options to choose from. Scenario Manager or scenarios. So we have scenarios, so we don't need to open the Scenario Manager for it. We should be able to search the scenarios deadly. So let's add this and click Okay. Now, you can see that even if I press OK, it will not show the scenario bar or Quick Access Toolbar. Basically here, we need to enable this. Go to Options quick access toolbar. Let's click on Show Access Toolbar, bilirubin or above ribbon. Let's see, above ribbon. Choose. Now like this. Now I can directly switch from set one, set two, set three. That's how you can easily work on Scenario Manager for your special scenarios. 27. Collecting Bulk Data From People Automatically: If you have any business requirement to collect the data from people in bulk, that could be related to anything like employs information you want to collect from every employee. Or maybe it is related to supply registration forms or let's say some kind of customers feedback forms. It could be or student registration forms or any kind of participation in an event, or just random data that you want to collect from. Let's say you have a restaurant and you want to collect the data from your customers about the feedback and as well as their phone numbers and e-mails for pitching the future for marketing about the data's promotions. It could be any general opinion from the public or anything like polling or any such activity that involves a lot of people to part speed and you need to collect a bulk amount of data from lots of people. What is the method to be preferred? One method is the physical paper method, where you pin a lot of volume forms or survey forms and you give it to a lot of people one by one, and then collect all of these impedance. Now how will you enter this in the system? It will take a lot of time. You will make an Excel sheet and you will record all of that physical records in the system. Now there is a method that will collect all the data by the customers from the customers. What that means is, let's see if I want to collect the customer survey, I can just directly email them a survey form. They can click on it. They will fill all the details and as soon as they will submit that, it will automatically create an Excel sheet. It will not only compile the data for that customer, but all the customers will be arranged. And in a proper data form, you don't even need to create the Excel sheet. You don't need to collect it one by one from each customers. You can just simply create a Google Form and that will do the tough for you. For that, we have to go on Google and open our Gmail account. Then from here I'll click on Google Drive. Now from here I'll click on New and go to more. Or let's say if we have Google forms on the top, then you can go from here. Otherwise you can go to more and select Google form from here. Let's click on Google Forms. Now it will be an under do form where you will create all the fields. So let's change the name. Let's see, I want to create a customer feedback form, right? Feedback form. As soon as I press tab, you will see that the name is now fixed. Now, you have the choice on how you want to collect the customer feedback. It can be some radio buttons where you have a question, then the customer can choose from four of them, only one. We have the checkboxes, we have the drop-downs. So if you want to give it the option like where are you from? So it can be many states. What he will do is just dropped down from the menu and choose. You can enable the option to upload any file. There are lots of options to choose from. So basically design of the form is totally dependent on you. It has lot of options. You can ask questions in any of the sequence. Let's say four multiple choice questions. I can ask the question of which product do you prefer? Option one will be product one. Product to go down, you have to manually Click on each line. Finally, product for this really disputed. Now, let's see, I have the same multiple choice question for the next question as well. What I can do instead of creating from scratch, I can just simply click on duplicate it and it will copy the same thing. You can just change the question. Let's say that joys for the multiple questions is seen. So we can keep that as it is. Now let's say that I want to duplicate again, but this time it will not be the multiple choice questions, rather, it will be checkboxes sphere. The customer has the option to choose multiple answers for single question. Let's see. The question could be like, What do you prefer in your products? The questions could be. This is just an example of what you can do with the checkboxes type of questions. Now similarly, if I want to create the copy of this, I can duplicate it. Let's see, recopied that. But now we're planning that we don't need that. You can simply delete it from here. Now let's add one more field. Let's click on Add. If you don't want to copy that field, you can just directly click from here. Or you can choose from here. I'll click Add. This will be a short answer, or could be better graph. Let's say I want to receive a long answer. Your review and feedback about our program, please. So this could be a paragraph long or you can even change it to short answer. Because a lot of people don't prefer to write a very detailed review. But if you want to enable the future, even the Texas long, it should get that. So in that case is you can activate the paragraph. One thing I forgot. I want to collect the customer name and phone number or email ID. We need to add that fields as well. So let's add a question, and this will be our short answer. And the question is your name. We will duplicate and your phone number. Now in the form sequence, it should come on the top before any feedback. Let's see, I want to move it. Under each question you will see this loaded it erudite is to move the question anywhere you want. Hold the mouse and drop it wherever you want to. Similarly, for the number, I'll bring it up under the name. Now there are situations where sometimes customer don't fill the form properly in the leaves. Some fields, if you want your fields to be filled, and without that, the form wouldn't be saved. So you can change it to required. That will become a competent revealed. Let's change it to required. On then. If the customer wants to write the review, you will write it. Otherwise you can leave it blank to send it to all customers. I can just directly click on sin. If they are my existing customers icons send them via email. But let's see that it is general public. We don't have their emails or any kind of deals. So how will we collect the data? Then we will click on the shareable link. You can shorten the URL as well. So it will be a very short link. You can just copy this and you can send them to WhatsApp or you can even publish it on your website, distribute a customer feedback tab. Let's see. Let's say that we have a tab of please review us. You can create a tab on your website and then you can publish that link on your website. Listed, ask your customers to go on website and boosted review that can provide the feedback there. Similarly, you can post it on Facebook. You will simply go on Facebook and just create a post and simply paste the link here and publish. You can easily collect the data from general public in this way. Now let us see, I've created the link. And see this is my, let us say this is my Facebook page or Twitter account or my website where the link is updated. Let's close this. I wanted to show you one thing. I'll just copy this. And whenever any customer wants to open it directly paste the link. He can fill the form and submit. Now, you can notice that this is the fields. Now let's make some changes. And let's see that I want to create our test field. Let's close. One amazing feature of this is this form is connected live with the mean forms. So even if you have send out all the links, Let's see, you have send out the link to 4000 customers, or let's say you have bolstered the form link on your feasible pitch. And a lot of people have lagged it already. So you don't want to remove that. You have made some changes in the background now. It will automatically be updated on the same link. So now we have the test field as well. One more thing, you can also assign some conditions to it. Let's see, it is a short answer, but you can just further customize it so that they cannot put the wrong responses. For example, just click on these three daughters arrows. For example, just click on these three dots and click on response validation. Here you can assign it that this number, phone number field cannot be text. You can fix the length or any conditions, so I'll restrict it to number. And that should be equal to, let's see, Eleven. This is my condition for the number. Now let's make one more setting that activity. That's fine. Let's close this. Let us now check login with my other email to see the effect. Now let's check this link on my new email. Let's click. It will show the form like this. Like your name, your number. It gives the editor you can see must be number and equal to 11. So these are the conditions that need to be fulfilled. Now if I press step, which products do you prefer? Let's say product one. Which product is the best? One? I prefer in Brooks, quality, immutability, and warranty. Let's see, your products are good. And let's submit. It still shows the editor 1234567891011. Submit. That was a mistake. It only picks up 11. So we can make that changes. Basically, this condition would be not number, it would be length. The maximum character count is 11. Now we will save it, will automatically make the changes. Let's now try BBC. Even if you don't write that as once it is not a compulsory fields, so we can submit. Your response has been recorded. You can submit another response. We can limit that as well, but that will require to sign in with Gmail. So they would need to sign in with Gmail first in order to record the response and they can only record one response. Now let's check our data collection. You can see that we open the form and we have collected two responses. One is sod on is ABC and this is the numbers. And these are some of the demographics of the data we have collected. It will also show you in chart form the details and summary about the data. Now, if I want to compile all of these responses, let's see, from 400 customers in a single excellence sheet. You don't need to do anything. Just click on the Create File, new spreadsheet or existing stretch treat. This is the first time, so we will create a new name is customer feedback form. Let's create it and see how amazing it is. It has recorded at work time and did the customer has recorded the response that can be used as a filter for future as well. Here's the customer name, their number, which products do they prefer? Each column is separated so that you can filter out or use pivot tables or anything you want for your analysis based on this data. You can see that how properly arranged this field is. What do you prefer VFB chosen three options. It says quality, coma, durability, warranty. See how simple it is to collect the data from a lot of people. You can just simply send the link or publish the link on our social media page or any website. And they can just click and definitely they will fill all the details. You don't need to do anything and you will collect the data automatically compiled in the extra achieve. This practice all these functions that I've shown you here. And then we will see what further advancements vegan make on these Google Forms. 28. Customizing Google Forms: Now let us see what further customizations can we do on the Google Forms. You will just go to Dr. Becky and you will see the customer feedback form in the semester because we've just recently created that. Just double-click. Here, we can do some further customizations like changing the color of the team. Let's change it to, let's say blue. You can change it to any color you want. Basically. Here are some background color suggestions as well. Some font styles. You can choose the header image as well. Right now you can see that the header is blank. I can choose any theme from here. It will show you a lot of options. Just choose what suits the requirement or the form you are forwarding. Let's say this is the form. This will be the limit. Now let's see if I want to use the company logo in straight off this header. I can go to. Just choose the image again. You can click on Upload. Browse the photo from your BC. Now let us say this is the logo. Let's upload this click Done. And it will be short like this. It's just for the demo. You can adjust the size that are different preference of the logos that can be fitted. For. Now, let's change it back to any color. I just canceled the image. You can see the life review whenever you update the form. What we can do further is, let's say that we have multiple forms to create. Let's say now for an event registration, and we don't exactly know what fields do we require in that gives us you can use temperature as well. Just click on New and Google Forms. It sees a blank form, blank quiz, or from a template. I'll choose from a template. Here I have multiple templates to choose from RSVP party in white contact information, there are lots of options. Let's choose Course Evaluation. This is the header image, class name, instructor, level of effort, contribution to learning. You can see that it is a very detailed form which is very well created. You can use that as well. Just make some changes and it will fulfill your needs. Afterwards. Just go on the top. And let's say course evaluation or account deck. Let's enter this. You can also share it in a particular folder. Let's see, move forward. Move to which folder? Let's say companies move. Now if I want to access it from folders, it is very easy now, even if it is not showing in the suggested, you can just statically score down. We'll do companies. And here you have the data. And this is my course evaluation. Now, let's open that form again, which we're customizing. And let's see the further options. I'll again go to my drive and customer feedback form. Now let's see, I want the option of moving to the next page for every new question. Let's say the customer should mention the name and there should be a button. Or the next question. So it will move to the next question, our next section, that is called section. Basically, what we can do is on the right-hand side we have Add Section option. Just click on it. It will show you the section, section one. This is section one, this is section dual. Further, wherever you want to select the section, you can name it. And even it is not compensated that how many of the questions you want to put in a single section, it is completely your choice. Let's say I want to say the section two contains your personal details. Now, I want to put two questions in section two and then move to this section. Let's create a section here. Naturally Control Z. Let's select for number two, create this section because section will be created, whatever you selected at the bottom of that new section will be created. This is my new section about the course, about the product. And then let's say feedback. Now we just saved. Now let's see, just to open it again and click on Preview to see how it will be shown. Customer feedback form, first option. You can see the next option. Fill in your details. Click Next. Let's say this. What do you prefer? Next? You'll review and submit. It will be recorded like this. On the last year, have you also see a message that is that your response has been recorded. We can change that as well. To customize the message, you can just directly click on settings. From here. It seems responses, how we manage our responses. You can make settings collect e-mail, address, allow response routing, limit to one response. As I was telling you that you can also limit to one response, that one customer can submit one response only. But this will require sign-in presentation. You can enable different presentations here. Now, under the presentation, it C's confirmation message response has been recorded here. I can edit this and I can drag my own message. I will drag my custom message. Now if you try that again, Let's say this is the link, I'll paste it. Next. Competency fields, I need to mention that. You can see that now we have changed the end message, so it looks more professional. Thank you for taking time in participating towards survey. If you have any further queries, please do not hesitate to contact us. You can change it to any custom company message. That's how you can use very effectively Google Forms to collect bulk data from general public or people. 29. How to Email Google Forms Professionally: Now let's say that you want to email that specific form like this one to your vendor for registration or your customer for any kind of registration. You need a professional email for that as well. I've attached this kind of email, email with the link of this video. So just download and follow along. It is greetings as a part of continuous procurement process, it is required that all existing vendors complete and return the supplier registration form. These are the requirements and how it will be fulfilled. Submitted procedures is mentioned. And what other information do we need as mentioned here. And it sees that failure to submit the required documents within 15 days will result in the activation of your account with distress trend in coffee shop. This is an example. So please find the link for the vintage institution for. Now here is the form attached. But let's see how it will look in the e-mail. Just copy this. And I'll go to the email. Right? Vendor. Creation form. Lp is the content here. Now I've pasted the content from Word, but if you paste the link from the form, it will look something like this. Let me just remove the hyperlink. It will look something like this. It will not be clickable. So how we will make that clickable be, we'll select this whole link and click on Insert Link. Now it becomes clickable. But I don't want this link to be attached in this way that it shows lots of numbers. Alphabet is combination of these. I will change it to, please click here to submit the form. It looks more professional. So I'll click on Change. Under change, the vibrators will remain the same, but the text to display, I'll change it to click. Okay. Now you can see that it changed it to. Please click here to submit the form. Now let's click sin and see how it looks. If we receive this e-mail. I'll click on it. This is the e-mail. Here is the link attached. It says please click here to submit the form. Let's click. And it will open up the form like this. That's how you can email the form in a professional way. Do any of your customers, vendors, employees, or students. To make it look more professional, you can do one thing as well. You can instead insert an image and make it clickable. So let's go to Google. And right here, let's say submit form. Let's see the image. Let's Write Submit button instead. These are different buttons that we can use. Now, I want to use only the transparent images. So if you want to further filter this summit, but inspire transparent images, I'll click on the tools and click on Color. Select here transmitted. So it will only show you transmitting buttons. Now, let's scroll and select any button. I'll attach this button with the link of this video as well. Let's save it. Save image as Write, Submit, and save it. Now, let's compose the email once again. And right here. Now let's copy the content and pasted here. This time because I want to adjust and insert the image instead of link. I'll remove this link. Instead what I'll do is I'll click on Insert photo and click on Upload. Choose to upload this file. Click Open. As soon as it attaches the image, it will show it in standard size. You can adjust the size of it. Click on small. Now to insert the link in here. It's a little bit tricky. So just select this whole file like this. Now on the normal selection, you will see this option that's very, you have to put and click on Insert link. It will unselect the option, and then we will click on this to show this option. It should show this link. Then I can choose that. And I'll only paste the link in here, this one. Copy this. And let's paste this in the web address. Extra display will remain blank because instead of x we have mentioned or we have inserted the image. So click Okay, and now let's click Send. I've immediately received an e-mail. Let's check this. Open it. Now we can see the button instead of the text link. Let's click on it. It will simply open up the form from this button. Isn't this cool? That's how we can improve the look to a more professional form when sending out these forms two different people. So that's about it. Distractors, this all things about Google Forms because it is a very useful tool. And then we will move to the next topic. 30. 30: If you want to use your Excel files online, or maybe you want to use your Excel files on the desktop. But there should be a folder that takes a backup of this file online as soon as you save it. So basically if you want a real-time backup of all your desktop based Excel files, how can we do that? We will use the Excel files with our desktop based version, but it will be automatically saved or uploaded to the Cloud as soon as we save it in a particular folder. How can we do that? Let's see. For that, I had to download an application. I'll go on Google and Google Drive for desktop. Enter. And you can download directly from Google.com. They provide you the link. Download drive for desktop. Just click on this. Download is approximately 250 MB of data. It will take some time to download. Just sit back and relax. Now the file is downloaded and let's open it, showing just double-click. Click Yes. And it sees installed Google Drive, add an application shortcut to your desktop and add desktop shortcuts to Google Doc Sheets and Slides. Let's add these shortcuts and click Install. Now it says sign in with browser. Let's click on it. And it needs your login. So let's login for the verification. Let me verify the code. So let's send this code to get the verification code. Now let's enter the code and click Next. Let's click the sign-in. The authentication is done. Now n is a window right here which sees some messages. Google Drive is loading your files. Click Okay. And Close. Now here is my folder of Google Drive. It's up and running. Now let us see that I want to denote order to be uploaded directly to the Google Drive S1 as anything or any file gets in that folder. I'll create a new folder. Let's try it online files. If you go there on this ribbon, you can see the Google Drive icon here. Just double-click to open up. Here you can make the settings. Click on Settings and click on Preferences. No tanks. And it has my laptop full list from your computer. So it says safety backup your files upload students sync your files to the Google Drive and Google Photos. Choose a folder on your computer to get started, let's add a border. The full load is this. Select Google Drive or sync with backup to Google Photos as well. I'll only thing it would Google Drive and click Done and click Save. Ok. Now this folder is life. Let's test this now. Let's see if I please any file in here like this one and then copy it in my folder of only in files. Be sued already. Let's replace this. Now let's check whether it uploaded it in the Google Drive or not. I'll go to the Google Drive. And here it sees Scenario Manager start. If I click on download and open up. Is this. Now if I make the changes to this file, but the file that we have in the folder, let's make the changes. I just wanted to see how fast it's thinking is. Let's write upload. Or two, magnetic. Let's click on Control S, save, and close. And it sees items remove items removed from one link. Files on your computer have also been reading from the new trash in the 30 days. Like Okay. Now let's refresh this and just download that file. Even we can see here, if you open it, it says Upload to automatic. We don't need to download it. We can see here as well. So let's see that if we make the changes by opening the file, like this, means on the Google Sheets, I have written SS. Now let us see that if we use that only, whether it updates the desktop based file as well. Let's open up. Yes, it does. And that's an amazing thing. You know that this changes has been recently made by Google and axial that it will support inter support Google Sheets and Excel files. Otherwise, we're bored. It was like this that if you are using it in Google Sheets, you won't be able to use in the desktop version. They didn't support each other like this. But now it's very convenient. You can either work online or on desktop as you like. If you are traveling somewhere, you can just open up this file from any laptop or any PC from your drive. Just work on Dr. deadly. When you are home and you have your laptop, you will see the objects here as well. That's how you can use the files only. You can even make it more effective by adding contributors to it. Let us say that if you want to manage the store, you weren't the storekeeper to update the inventory items here, and you will get the live access on your desktop as well. So you both have the common file. What you can do is you can just use a Google Drive folder. Now you must have Google Drive like this. This, use this folder, my grave, and put here all the files. And then configure the Google Drive with the seeming e-mail ID on your PC and on the store BC, they will sync and whatever the store manager will object there, you will get immediate obligation here as well. 31. Funds Management Part 1: Okay, from now on, all the scenarios related to business and financial modeling are covered in the single file called beam FMLA superclass. You can download this file with the link of this video. There are a lot of assignments in the single file. So it will be easier for us to handle our practice assignments in this way. Now our first assignment is related to somebody schedule in which we will learn how you can manage the bank EMI payments on a monthly basis and create an automated scheduled for that so that they automatically remind you about the payment. And that will really help to manage. Lots of EMI is every month that is paid by the company. Because as we already knew, that the business takes a lot of loan in the form of car loans, business loans, personal loans. So it is very difficult to manage all of the EMI's because they are incurring every month. And different bitumens are in green in a single month on different dates. As we can see here. If you highlight and select all of these details, you can see the count of it. It is nearly 15 loans. That means 15 payments are paid every month. This simply means that every other day you have to arrange payment in the particular account so that when the bank auto deducts from that particular bank, you should not be out of balance. You have to make sure about that. You arrange funds before their particular date of deduction. This is weird, this detailed schedule in which we will see that how Excel again remind five days before the payment date comes so that you can easily manage the funds and you can easily look what is due in the coming this, we will also see the scenario, as you can see here, that we have two companies, first traveled and j's machine. In this scenario, what companies does is, let's say that this JS maturity restaurant is unknown business, newly established business. The banks might refuse to give loan to this new business. What they do is they will take the loan from their other Bailyn Company, then use that known in the newly established business. Obviously, the deduction will be made from the first travel account, but it should be accounted for in the machine. So we need to transfer either from transmission to first traveling, first traveled to choose machine as it is required. Let's see that as I told you, the scenario that Jasmine shared restraint is a newly established business. The first travel has taken the loan and bought a car for transmission. That means G is Michelle will manage the funds every month and then transfer to first travel because ultimately it will do that from first travel, but it is the expense of Jesus mission. So if we move forward, we can see that there are a lot of installments that needs to be paid every month. We need to arrange 182,314 items every single month. This is a lot of amount. It has been mentioned with the repeated. We are considering this scenario that this is the traditional style, how the company is managing the funds. They have only written what is due and how the bank will transfer to other bank. What is the remediate. But it doesn't automatically suggest that you need to and use this payment five years earlier or some reminders, it doesn't do that. And this all scenarios related to EMI. Emi means equated monthly installments. That means from the total loan you need to pay every month, some amount, let's say for this car, 770, that will be paid every month until 36 months. Similarly, for other GAR, the installment is much higher. For some business loans, the lunar mode is 1 billion items. So obviously the installment will also be very bulky, almost 26,898 amps. Now let's learn some more things about the scenario. Here. It says transfer from and transfer two. Then it is mentioned that transferred from first travel and transferred to first travel. That means this loan is only related to first traveled. That means we don't need to transfer anything, just we need to see the mode of treatment and that is issued PDZ. That means we have issued both digit checks for all the installments in advance to the bank. Whenever the deed comes, what the bank will do, they will deposit the check and clear the payment. We have to make sure that we have enough funds on that particular date. Otherwise the bank check will bounce and that can blacklist you in future. We need to avoid that. Now here are other details regarding this loan. The loan total amount is 24,250. The downbeat mean that we have paid in advance is 7,500. Items, processing fees is 500. This is interest rate, total interest over the years. And the interest is this net amount, interest An installment amount. Finally, what do we exactly need to do is we need to make sure that from whatever company the bank is deducting the amount from whatever bank we need to make sure that we have that enough funds in that bank on that particular instalment date, particularly, this is the complete scenario. In the next video, we will completely learn how to make the schedule in Excel. 32. Funds Management Part 2: Now to manage these funds, what we will do is we will select this whole first payment. And as we can see here, that repeated roots are mentioned only in the, in a single box. What you will do is we will split this installment in every month date separately, on separate line in the greedy schedule. How we will do that? As you can see that the first payment is due on Monday, it May 2020. This means that it has been already bid, but since we are splitting the installments separately on each row for each month, we will account for this as well. So grantee admin 2020. So firstly it is let me just zoom it. A liquid. First read is 28, May 2020. And hit Enter. Now you have to follow the deed sequence according to your country requirements. So let's say if you are in Canada, you will follow the sequence as Month first. They indeed, and then year. If you are somewhere else, you need to follow date, month, and year sequence. You have to make sure that it is showing the correct sequence as you want. Otherwise, it will not show the proper repudiates and you cannot operate it properly. So two teams, the deed sequence, you can simply do one thing. I'm telling you the options of Windows ten. Just right-click on the state area and click on adjusted in time. Go to tea time and regional settings, additional D1 time and regional settings, and then go to set D1 type. Now here it will show change date and time. Change calendar settings. It is very simple. It involves multiple areas, but it's very simple. And go to additional settings, then go to short date. Just make sure that even if it is, let's say Monday it and I'll change it to DD. Mm. Why buy as per my country requirements and click on apply. Apply, OK. And then close all of these windows. That's how you do it. Now we will copy each field separately and then piece right here. Why is it purely? Because this main sheet has multiple fields that we will not be using in detailed schedule. We will only use some of the fields. Let's see, transfer from, let's copy the transfer from field and be straight right here. Control V. Then transfer to copy and paste. Detail, thrilled, copy and paste. It should be DC detail of the loan installment. Now for the deals of installment, I'll copy this field and paste it here. Then we will put a space, right? First installment and hit Enter. The field size requires expansion, then just go between these two columns. And when you see this kind of icon to double-click. And then the installment amount, let's copy and paste that as well. The installment amount is 770 distributors right here. Now, just move to where it's starting. And then we will create the second installment details as well. To copy all of these fields, we can simply select all of the blank areas, blank cells, ignoring this detail of loan installment field for now, this press Control D. Control D means copy down. It copied down all the fields. Now, if we drag down to see that a second installment in the second figure, but it doesn't change. It is still on first installment, just showing first installment. That means that v should write the first installment not on the end, but in the start first installment. And let's put a space and hit enter. Now let's check if I drag down. Now we'll change it to second installment. Now the installment amount is 7070, same. Now we have created two installments. Now if I select all of these spheres, these two lines, to select a pattern, what this should do is this should automatically figure out that first Whitman was Grundy at May 2022nd payment is 28 June 2 thousand Randy. So obviously the third payment will be following the spectrum guaranteed July 2020. Cm is for the installment. If first is first installment, second is second installment, then it ritual third installment. Let's drag down to the third field. As we can see, it follows the background. What I'll do is I'll follow the spectrum until a lot of payments. And we have how many installers? 36 installments. Just go up a little bit. 36 installments. Up till 36th installment. We will drag this figure. If you see a difference in the fields, this installment, we'll just toward two installments and done, just double-click to send it down and just select Copy Cells if it changes it to fill series, we can see the change in the background. What you can do is you can select the first field format printer and then be straight on the second fear. And then select both of these and double-click to send it down. And it will show you all the figures as seen. Similarly, we will make the second installment. Now, I'll fast-forward the process because the process is seen. So let's see how we can make the second installment, the data string D, July. Let's see. I'll scroll down and I'll follow the same pattern. I'll just fast forward this process. We have period. The second scenario as well. The similar method will be followed for all. We will create one more scenario and then I'll show you what further we need to do. This is the third loan. Now what we can do after that is v will simply go to the top. Let's raise this field's first headings. To freeze this headings, I'll select B2 cell so that it will freeze the top headings and the D two as I move towards right, it will freeze the dates. As I move down, it will freeze the headings. So we have to select the selling view, go to View, three spins and three spins. When I move down, now the headings won't disappear. And when I move towards the deed will disappear. Now after that, I can apply filters. The shortcut to apply filters or Control Shift and press L and sort by oldest to newest. That's how we will create the installment. Now to learn all the scenario completely, what do you need to do is you need to complete all of these installments and make their breakup. This is your assignment like this, and then sorted by or less strenuous or new stewardess. This is your assignment. Then you will see in the next video what further we can do with this. 33. Funds Management Part 3: In the last video, I've given you the task to complete the EMI schedule. I hope you have completed that. But if you haven't, you can download EMA scheduled field. You can find this link of this video. You can just follow this. I've completed all the reduced schedule for you, for all the ribbons. Now let's move further and see what second step we will do. As I told you that if you haven't applied filters yet, you will go to the Home tab, click on Sort and Filter, and click on filter if you want to use the short key, that is Control Shift and press L, then we will sort by oldest to newest. Now I need n other separate field. I'll just right-click and insert a new field to make the auto reminders work. What I need to do is I need to figure out on what did the installment was due? What is the current date? So I'll match the installment did with the current date to find out that these left in the upcoming visit, the formula will be equals to today. Today we'll show the current date. You need to close the bracket. This right today. Press Tab. It will automatically open the bracket and then close the bracket manually. And then I'll minus today's deed with the instalment date and hit Enter. Now it is showing some deed because it is indeed format. I need to change the format to general. So now it's showing that 2018 has been gone 1294 days ago. But since that it has already passed. It should join minus. So that means we need to inverse this formula. And we will select the a2 first and then minus it today and hit Enter. Now it will show in minus as we change the format to journal. Now I will double-click to send it down. It is applied to all of the fields, but I need to show blank fields wherever the d's left is in minus because that dude has already passed. We only need to see the future dates for reminders. So what I'll do is let's apply a condition on it. I'll go on the start and I will write after equal to, I'll add the formula if, if, what is the logical test? This all is the logical test. If this whole calculation gives the result and that is less than 0, then I'll use comma four then show blank. Blank means inverted commas, open and inverted commas clothes. Nothing is written between the inverted commas. That means we're seeing that show blank. If the result is in minus. Other ways, we'll use a comma for otherwise as well. Otherwise, just continue with this calculation. So I'll copy and paste this calculation here. If I read this formula in simple English, it will be if the formula we have applied shows the result that is greater than 0, then show blank, otherwise. Continue the calculation. Let's see. It will show you that autocorrect function recommends you to close another bracket of the overall formula of F at the end. Do you want to accept this correction? Yes. It turns it in blank, just double-click to send it down. A lot of humans are now already passed. Our payment starts from here, actually. That's great. Now if I only want to see the upcoming payments, what I'll do is I'll open this filter and I uncheck the blanks. That means I'm seeing that. Just don't throw the blanks. Only show the upcoming payments due. Now it's showing only the upcoming permits. Now you can easily see that what upcoming payments are due and after how many days? After it is 11 days, 11 days, 15 years, 17 days, tedious, so on and so forth. This is how you calculate these left in Excel. 34. Funds Management Part 4: Now let's apply a further condition. Since it has extracted the days left. Now, I need to highlight the upcoming payments up to, let's say 20 days. So if the d's left are between 0 and Grundy, it should be highlighted in red so that I can arrange the funds. The simplest way of doing that is just highlight this whole column of D is left. Go to the Conditional Formatting under the Home tab. Click on, let's say conditional formatting. And I'll go in the highlight cell rules. Here is the rule of setting our range that is between criteria. I'll set the range of 0 and green tea. Let's see. I need to highlight that in red. Click. Okay, so it's highlighted. But let's see, instead of highlighting the single cells only, I need to highlight the whole rule which fulfills this criteria. So I cannot apply this. Let's clear this conditional formatting. I'll select the whole column. Now I'll go to Conditional Formatting again and go to clear rules from the selected cells. Now it's cleared up. Now let's apply the condition. Since I need to highlight hall rules based on this criteria, what I'll do is from the second row, or let's say just clear this up. Clear the filter from the second row, ignoring the headings. Select the first cell of the second row. Hold Control Shift, move towards right and then dump. Now, go manually, do the top scroll up so that I can tell you about the formula. Now, I'll go to conditional formatting, but this time since I am creating my own rules, because I want to Final something different. So I'll go to the new rule and click on a special formula that I will create by myself. Use a formula to determine which cells to format. That means I will tell Excel which cells to format based on the particular criteria. If you notice, I've told you that it should highlight the cell autocomplete rule based on the criteria that it should be greater than 0 and less than Grundy. So that is called a range between 020. But if we talk about how to apply this condition manually in the conditional formatting, we have to consider that there are two conditions. One, that, that these left should be greater than 0 and other debt it should be less than grindy. In that case, I'll start the formula with equals to sine. I will write and this remember that if you already know about the if then conditions from basic to advanced training of Excel, you might already know about the if and if all criterias. And if you still believe that you need a grip on that, if then conditions and other basic things, you can join our course of music to advanced Excel. I'll provide the link in the description of this video. So anyway, the point is that if you know about the if n criterias, you already know that whenever we applied if N, It was with if conditions. But here we have only use n conditioned deadly. That is because conditional formatting just needs the result, whether the condition is true or false, we will only set the criteria. We will not tell that if this happens, then do this because obviously if condition is not required because the condition formatting job is only to color. We don't need to tell the system that if the condition fulfilled, then color. Otherwise, don't color. It will automatically do that. Obviously, if the condition fulfills, it will color and if not, it will not color. So that's why we have used and conditioned directly. Now the condition is this cell means D2. The D is left first cell. This cell should be greater than. Let's see, greater than or equal to 0. Comma four. The second condition, this cell should be less than or equal to. Guarantee. If we set the formula like this, it will be fixed formula. So whenever, let's say I want to move the criteria to 0230 days. So in that case, we have to readjust the formula. If we want the formula to work. Let's consider it. If we want the formula to work based on a cell-based criteria. I'll write a field of criteria here. Let's set the criteria to 0, too. Grindy. We will format that later on. But let us see the condition and how it will affect. Select all rows, excluding the headings, and then make sure that you can see the criteria on the sheet. Let's go again to Conditional Formatting. New Rule. Use the formula to determine which cells to format. Our criteria was equal to N bracket open. This cell D is left cell, should be greater or equal to our first criteria. The second condition is, again, this cell should be less than or equal to this criteria and simply bracket close. Now one thing is for sure that the criteria is and will remain totally fixed. We will not exclude any dollar sign from J3, J2. We will only try to readjust the cells D2 Column because, because the criteria that will be checked by conditional formatting should move in rows. That means it will first check this rule for the condition, then second row and third row. That means that rule should not be fixed. It should move in the rules. So whenever the formula you are trying to apply should move in the rows, you have to remove the dollars behind that. I'll remove the dollar from the rules and keep the dollar intact on the columns. Now let's format. Go to the format. Let's go to fill and let's fill it in grid. For the form. If you want the regular form, you can use that as well. Otherwise, I can highlight in bold. I can select the color as white. So it will do all the job for you. Let's click Okay now, right now it's not showing anything because we need to uncheck the blank fields. Click Okay. Now it has highlighted all the cells matching the criteria. Let's change the criteria. That's why we have created a manual criteria. I cannot see the criteria right now. Actually, I should move the grid area from here to here. It is. Let's clear up from here. But now as you can see that our formula is disturbed. I need to change that. Whenever you need to change or just the formula, go to conditional formatting and click on Manage Rules. And just double-click here you can see the formula. Manage Rules. You need to see the criteria fields in the background. If you cannot, move towards right. And make sure you can see that before you try to adjust the formula. Instead of G, Let's select one. Now. Instead of G3, let's select L1, and that will be fixed. Click Apply. And Okay. Now let's go back and uncheck the blank fields and click. Okay. Now it's showing according to our criteria, let's change this to 0 to, let's say 30 days. You can see that now it highlighted 30 days. Let's change it to, let's see, 0 to 11 days only. It has changed to 11 days only. This is the perfect example on how you can apply described areas with find the days left first and then apply the conditional formatting to create a beautiful funds management system in Excellent. 35. Depreciation Scedule Part 1: In this session, we will learn that if you want to manage the deposition scheduled automatically by using some formulas in Excel, how we can do that? Until now, we have completed the detailed schedule of EMI's. Now we will move to our next sheet tab that is deposition schedules. Now here we are taking an example of motor vehicle asset name is motor vehicle, model is Honda Civic 2013, and registration number is this. And duty of acquisition is first of January 2013. The cost of the vehicle is 1500 thousand and residual values 150 thousand. Let's see. And the useful life for this car is ten years. Now there are two deposition methods. If you are an accounting student, you must already know that. That there are two methods. One is straight-line method and one is reducing balance method or declining balance method. We will see what results we get from each deposition method. Now here, let's say that these rules are limited and we want to extend the number of rules. We can easily do that by simply selecting some of the rules, a couple of rows to select that. Right-click and click on insert. It will increase the rules. Here we have to mention the years. Since the useful life is standards. We can simply write one here and just drag it down until some rules and click on Fill Series. That will give the result of years. But it will not automatically change. If we change it to, let's say five years. We need a formula that can automate this part as well. Even though we can write manually as well. But we need to automate each and every part of excellence to increase its efficiency. Now what is the benefit of this automation? The benefit of this is we will apply the formulas in each field according to the years. So whenever the years is mentioned, it will only calculate the deposition charge for that particular year. So if we automate this section, it will automate all the other fields as well depending on this field. Now, the first part of the formula applying is we will write one. And for the second cell v1 great to rather we will use the formula equals to above cell plus one. That means it will pick the upper figure and add one more figure to it that will change it to two. Now if I it down a little bit, you can see now it shows three, y. Just opened the formula, double-click. You can see that now the selection of the cell has moved down and genes to sell position to do. Now it's adding one more to the above cell. That will give the result of three. So we can drag it down until the bottom. Be upgraded this formula. But let's see, now that it is depending on this or not. Like I was telling you that if I change it to five years, it took only five years. It's not union. Why? Because we haven't meet a relationship of these cells formulas with this useful life. Now to automate this further, we will apply a condition on it. We will add a condition with the earth that if the above cell is greater than 0, then continue this calculation. 11 plus one. Other ways, leave it blank. So whenever you want to add the formula with the current formula, we will open the current formula. And after the equal to sign, we will add the formula. In this condition. If press Tab, if the above cell is greater than 0, then I'll use comma four, then, then continue this calculation. Otherwise, use the comma four otherwise as well. Then other ways show blank. We're simply saying that if the above cell is greater than 0, then continue the calculation otherwise show plank. Let's drag it to all the other cells. It doesn't show any difference. But let's see if I remove this. Now. If I change the useful life now, let's say to five years, Let's say leave it blank. Then it should not show any years. That is the purpose of applying if condition. But since we haven't made the connection yet as well, what we will do is we will change the formula for the cell one, year one as well. As you can see that in the cell one or year one, we haven't applied any formula. We have manually written that number. So we have to apply the condition that equals to f. This cell is greater than 0, then one, then one. Other way it's shown mill. So now if we show the figures, it will change to calculations. And otherwise, if I remove the years, it will show blank. But it's not exactly showing blank. It's showing the value error because it cannot find the value. We need to eliminate that as well. So whenever the editor comes, we need to change this error not to the standard value error, but you can change it to any text you want here. Right now it's showing hash value error by default. But we can apply and other conditions. Do it. Go to the starting after the equal to sign? Let's write IF error. If editor is to change any result. When the editor gums to change the text of the error. If adder is on this whole text. Just apply if editing the starting and the end. We will add if editor press Tab means if editor is gums on this whole text, then you will use coma for them. Then show blank. And an autoclave. Yes. Just double-click to send it down. And now let's check. If I change it to ten years. It's showing all the years. And when we remove that, not showing any years. Now for this particular cell, we can apply other conditions as well. Let's see that if I don't want to apply it like this, that if this cell is greater than 0, then show one otherwise blank. We can change it to other formulas as well, depends on your logic. Equals to F. Let's see. I see that if this cell is equal to blank, then show blank. Otherwise show one. So it will give the same result. When I write any figure, it will show when I remove that, it doesn't show anything. This comes to a conclusion that there is no specific formula to get the results we want. Rather depends totally on your logic that how you want to apply the formula. You can apply the formula by using your mind. You can apply multiple techniques to it. Now, since we have applied multiple formulas, Stewart, I recommend you to practice this first, this video and reach until this point. And then we will move to the next video to see what further things we can do to make it even more automated. 36. Depreciation Calculation Straight Line Part 2: Now until this point we have applied the formula. Let's apply that one more time from scratch to squiggly do that. I will remove all of these fields. You will apply tried to apply the formula in a different way. As I told you that if the useful life is ten years, it should automatically show the years, but we haven't really made the relationship yet. First we have mentioned year one, and then we have the calculation, this cell plus one simply simple formula. Then we have grant that down to the bottom. First time you have to drag it down manually because there is no data to determine until which point to drag automatically. So once we have the data on the all other fields, you can just double-click to sand it down. But when the data is first written, you have to manually drag it down. Just keep that in mind. Then what we have done is we have automated this part as well. But before we have applied the condition of equals two if this cell is greater than 0 rate. We can also apply the condition that if that cell is equal to blank, then show blank. Other ways. One, you can see that when it's slag, it shows blank. When we write any number, it shows the number. So it completely depends on your logic that how you want to apply the formula. There is no hard and fast rule to apply a particular formula. You can create your own formula by your own thinking right now, the inner bark, Let's resolve that as well. Whenever edit occurred, excel has some default index for the error. Here there is some value error, so it shows the decks of value. But we can convert that text error to our choice of text. And we want to say that if error on this formula then show blank, other ways. Natural blank, hit Enter, then reapply. Now we will move to the one more step. For that step, the main requirement is this. Remember that if error will only be applied at the end when all of the formula compilation is complete. Then we will apply the IF error on the complete formula as a whole. Right now our formulas incomplete because we haven't made the relationship as of yet. The relationship I want is when I write five, it should show only five numbers. So we will make the condition to it on the second cell because we have a different formula and for seven seconds cell, I'll go on the second cell and write that if this calculation is less than this cell plus one, why the cell plus one? Because I want to show all five years. So that means in this condition, I'm saying that if the years calculation that this plus one formula is doing is less than six, then start showing blank. On less than six. Just continue to calculation otherwise show blank. That's what I'm trying to do here. Let's check the result. We cannot continue the result under low because we're fulfill this condition also only that what if the value is true? We have to mention otherwise as well. So otherwise, otherwise what? Just continue with the calculation. Plus one. Let's see the result now. Just double-click to send it down. One more thing. Whenever you are making the connection with any particular cell that needs to be fixed, we don't want the cell to drag down from here. This point should be fixed. Select the cell, press F4 to fix. You can see the dollar sign if not, with therefore, you have to use the combination of Fn plus F4 function key plus F4. Now let's apply. We have applied the wrong formula. If this cell, this calculation is less than six, then we have to continue to calculation. You will continue the calculation. Otherwise show blank. Now it's correct. Finally, we can see the relationship. If we change it to ten years, it's showing ten years. If I change it to three years, it's showing three years, five years, five years. That's perfect. Now we can apply if adder as well. If error on all this comes, then go to the right. Blank. Double-click to standard down. Perfect. Now let's see the final result. 32 inches, 236 changes to six. Now if anyone else checks this formula, might find it a little complex, but we know that the formula application is very simple. You just have to follow step-by-step the method. You will not apply this formula in one goal. Rather, you will follow always the same process. Just make a simple formula, then add more to it than add more to it. Only then it will make logic. You cannot apply this formula completely directly, even when you become an expert. You have to follow the same way because it is a mechanical process, so it totally depends on your real-time thinking then how you can customize that further. So just work on it until this point. And then we will move to the next video. 37. Depreciation Scedule Straight Line Part 3: Now the further step is year-end calculation. This motor vehicle was acquired on first of January 2013. That means after one year that is 31st of December 2013. The first deposition will be charged. Now, I will try to automate this as well. That Let's see for any further calculation, if I change the date of acquisition, it should change the years as well. Let's write instead of year mentioning manually, rate equals to the self plus 364. Now why 364? Because for certain rate has already passed, I'll add 364 plus 1. First off, generally, don't sue. 31st December 2013, total 365 days. Now for the other cell, Let's continue the formula equals to the cell now plus 365. So it gives 31st December 2014. And now we will follow this far all the other cells until bottom. Now we can see here that the first years It's 31st, December 20141513. But in other years it's showing 3030. It means there might be a leap year in-between. Now from now on is showing problem. Now introduces from 30 to 29 then to our limited 28. That means we cannot use this formula. For all the other cells. We will use the 360 formatted. That means this formula, the cell plus 364, can only be used in the first year. For all the other years, the most suitable formula is IO month. Humans means end of the month. Now what it does, if I remove the bracket, it shows the purpose of this formula. It is the CA limit of the last day of the month before and after the specified number of months. I know you didn't understand that. Let me explain. Now. This means if I press tab, Let's see if I select the started this coma in the months, I'll mention growth. What it will do is it will move it month by month, and it will always show the deed of that particular month. And so that means it will move from this deed up to 12 months. And for each one, it will move to the end. This gives us 31st December 2014. Let's apply to all of these. Now it's perfect, but showing 31st December for all the other dates. Now the question is, we can also apply IO month in the first cell. The answer is yes, but we have to use a literal reference logic. Let's write IO month. The strategies, this. Now since the January is already mentioned here, that remaining months will be 11 months, then it gives the proper result. Yes, we can use human formula for the first cell as well. Now let us see that if I change years here, we can see clearly that the years is changing, but the year's end, as been mentioned, up till N. And we obviously need to apply formula not on a couple of years, but all fields. But it should work automatically. That means if I change it to five years, it should not only show the years limited to five, it should draw all the calculations limited to this five-years only. Now, I will use simple formula to automate this part as well. I'll select the first cell just open that. I'll mention that if this cell is equal to blank, then show blank. Otherwise, continue with this calculation. Now, since this first cell is only for 11 months and the other one is literally changed. I cannot drag it down from the first cell. Rather I'll drag down from the second cell. Just open the second cell now. Right? If if this cell is equal to blank, then show blank. Otherwise, continue the calculation. It yes. And just double-click to sand it down. Now it's showing the proper dessert. Let's change it to ten to see the result. Perfect. Six. That's fine. This is how you can extract the year ends with IO man from Lula and then automate it with mixing off. If condition.