Become an Excel Pro with 9 case studies | Atif Noor | Skillshare

Playback Speed

  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Become an Excel Pro with 9 case studies

teacher avatar Atif Noor, Finance Leader | Executive Coach

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

11 Lessons (2h 35m)
    • 1. Course Introduction

    • 2. Instructor Introduction

    • 3. Learn the techniques to deal with texts in Excel

    • 4. Master the art of dealing with information to carryout insightful analysis

    • 5. Simplest way to consolidate similar data sets and rearrange them the way you like

    • 6. Use what-if analysis to stay ahead of game

    • 7. Simple but very powerful techniques to drive the performance of teams

    • 8. Learn how to showcase your hard work by presenting in an interactive manner

    • 9. Scenario analysis

    • 10. How to keep your secret work safe from spying eyes

    • 11. An excel based To Do list which is simple but can do wonders

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class


Meet Your Teacher

Teacher Profile Image

Atif Noor

Finance Leader | Executive Coach


Self driven and result oriented experienced Finance leader with rigorous experience in financial planning and analysis, business finance and financial controls of over 17 years. GMAT perfect scorer and executive coach.Polished leadership skills to drive the teams for consistent delivery of results. True believer of and committed to 'Excellence'




• Financial Planning & Analysis-FP&A

• Financial Modelling (Excel Guru)

• Business Restructuring

• Strategy formulation & Execution

• Budgeting & Forecasting

• Statutory / Management Reporting

• Mergers & Acquisitions

• System Implementations (SAP, Oracle)

• Financial Risk Management

&b... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.


1. Course Introduction: hi. A very warm welcome to the course. Become an excellent pro with nine case studies. In this particular course, instead of going through various functions or techniques in Excel, I will be presenting you with nine real life case studies whereby will have some problem to solve or some tasks to complete and by doing so will be learning various excell functions and techniques the structure off the course. As I said, there will be several case studies. The first case study is textile phobia. We will be learning the art off dealing with text strings. Next case study is cracked. Did it abyss where we will learn the art off doing insightful analysis, dealing with databases? Next case study Is consolidation made easy? It's particularly very helpful for accounting and finance professionals. Next case study is ahead of game where we can do different kind of analysis, which will put you ahead of Kim. Then the case study is named be like a boss, so very simple techniques that will really help you in completing your task at work. Next case study is auto men Sha Learn the art off automation and impress e o Co workers Next case study is making sensitive where we learn the sensitivity, analysis and running different scenarios, Then we'll move on to the case. Study off top secret. We will learn how we can have different security measures in our Excel walk. And then there is a bonus for you, where I'll be sharing a very simple to do list, a simple Excel model which I made 78 years ago. And since then I've been using it, and it has made me organize my walk tremendously. Last but not the least. It's not goodbye it all, and hopefully we'll stay in touch and I'll be coming up with other exciting courses. Before we begin the course. I want you to learn the complete learning process. The first part is learning itself. When you go through the videos or lectures, focus on that do not have any interruption so that you can digest the new skill. Then very important is reflection. Sit down and ask yourself, What did you learn? This is very important if you miss that part that you cannot have the proper ending off the learning process. Last part of the process is implementation, so you learn, reflect and then implement. If you miss out the reflection apart from this, the implementation part would be very weak. So focus on reflection part. This part actually helps you identify which part you still need to go back and learn more and practice more. If you cover this part than implementation would be successful. Thanks for joining the course. Good luck. 2. Instructor Introduction: Hi, This is art If Nor and I will be your coach for this course. I'm a finance graduate and certified management accountant from I am a US. I have 17 years off experience, and currently I'm director off financial planning and analysis in an aviation group. My career progression I started right after my graduation as financial analyst got promoted toe seeing a financial analyst position. And then I had my first leadership position as Fbn the manager. Then I grew, and currently I'm director FB Any. My first eight years were in a multibillion dollar conglomerate, which was based in Kuwait and operations in more than 20 countries. Next, I moved into an investment group with investment holding not only in Middle East but also North Africa and Europe. Currently, I am based in Oman and walking in aviation sector. In total. I have 17 years of experience after Fitch, 13 in Middle East and for last nine years I'm holding different leadership roles. My profile summary academic background I've covered in dumbs off sectors. I have covered various sectors in different rules that I helped before which cover investments, aviation and several B two c Business is like FMC, G, retail, dining, education, healthcare, real estate, construction and engineering in terms off skills. My concentration has remained in F B and A, but I was also involved in the core finance operations off various businesses. I have excelled in financial modelling and have done several Emini projects. Throughout my career. I was regarded an Excel guru. I have always been helpful to my co workers and improving their excellent financial modelling skins in terms off brands. I have worked with several well known international parents like Mars models cost a coffee and some technical brands like Phillips, Hitachi, Cisco and Bush. I have covered several geography ease like Oman Kuwait Care, say you a Bahrain, Egypt and Jordan. Here are some examples off some dashboards, which I created through various positions I held in past. Currently, you are seeing a sales monitoring dashboard, which is interactive. You can slice and dice data. You can do different kind off analysis, and it's linked toe a simple date of is, but it's very interactive and user friendly, and it was all made in exit. Similarly, another dashboard, which I made was expense monitoring dashboard. Here we were able to monitor expenses off the group, and we were able to do a different, kind off variance analysis as well. Here's another example off real estate management dashboard to manage different properties , which my group waas holding at that time. Last but not the least. I would take this opportunity to share this thought with you. And I've been a big advocate throughout my career that never stopped dreaming. Always dreamed because that is the starting point. If you don't dream, then you are missing a riel trust and motivation. So that's very important. You dream first and dream big. And then while you plan your carrier, you actually chase your dreams in the end. I wish you good luck in this course and in your carrier. 3. Learn the techniques to deal with texts in Excel: Hi. Welcome to the model off textile phobia. As Excell users, we know that we deal with different types of data like numbers, text, strings, currencies. It's quite easy to deal with numbers because we can apply different kind of formulas. We can carry out different tasks off calculating averages. Summation, etcetera. But it becomes a little bit tricky when it comes to the text strings, because obviously they only mean tow us. But for computer, it's just extreme, with no meaning. So in this model will be learning different formulas or techniques that we can apply to take strings to get you out. You know, different tasks that we can have on our job or on our projects. So let's go to the problem statement. In this short case study, we have this problem available. For example, you have a list of 100,000 candidates for Vegas jobs, and you need to schedule their interviews over a period of one month in alphabetical order . But that should be with respect to their last names. Let's look at the list. We have the list of employees or candidates, but their names are first name and last name. Together. We don't have them in different cells. So here is the challenge. How we can sort these names with respect to the last name. First, I want to show you that this cannot be done right away. Let's put filters at this level. Now Here's a tip, and I want you to learn these little shortcuts to getting out different tasks because your speed will be enhanced when you want to put a filter. Just select those columns and the shortcut to put filter is called the F F. I pressed that, and then this filter is applied. Now, if I come here to this little arrow, I click here. I have all these names available here. I can do different kind of sort ing's. Let's say I sort a to Z. It sorts the data, but it's with first name because that is the whole string. Excell doesn't understand that this is first name and this is the second or last name. Now let's learn how we can split this name into first and last name. Now we'll be learning previous text formal us First. We need to understand how we can split this into two. This pace here basically defines that anything which is on the left side of the space, his first name and anything which is on the right side of this space is last name. So there is a formula in excel which is find a particular text which in this case is space that you have to put in the inverted commas. I put space inverted, commas closed. Find this particular text within the text, which is this Looks like cake here. Parentis has closed and it gives me seven, which is the location off this space in this particular dext. So you can see one, 23456 and seven. So that is the location off text which we have searched in this particular streak. How we can use this in our cask. There is another function which is corn left. Now I want you to learn one best practice when you are writing a complicated formula, please use them in fragments. So when you are devising, unless you perfect that formula, don't put that in one self. In fact, put that into pieces. So, for example, this left function I'm putting in a separate formula. What I want to do. The left part off this text string and after Dalit. How many characters now, How many characters would be in the first name that we need to find out? We have already found out the location off space in this text ring and anything on the left is the first name. But we have to exclude the space. So we have two minus one. So what we'll do? The number of characters we warned in the first name would be the location off space minus one. And what is the formula for location for space? We just calculated. So I would select that cell minus one, and I close this so it will give me the first name. Now, since I have used in this particular cell, I have used sell deed to which found out the location off the space of Just copy this control. See, I come here and this de tool I replaced with my particular formula. Now, even if I delete this, I have this. So But this is obviously for this cell. So I'll just copy from the text and I go here paste. I have my first name extracted on this text drink. No, I have to extract the second name, our last name. How we can do that. We have a function similarly toe left function pulled right in this text string and we have to give it how many characters it should pick. So let's say for example, five it has given us. But we don't know. We knew that it's five, so it has given us the last name. But we have to really mortal it. Now there's another text function which is the length off a particular text. So this text has 12 characters and we know that out of these 12 space is at location seven . So anything from director number 8 to 12 would be in the last name. So how we can find these, we have to take 12 total characters minus until here which would be seven. So that gives us five. So five characters from the right would be not last name. So I have the land formula. What I have to do instead of this five, I will take the length off this text string minus the location off the goal space in our texture, and it will give us the last name. So now I have this text string split into first and last name. Let me get rid off all these notes Similarly, as we applied the filters before If I want to remove those filters, select and press old d f f The filters are removed. You can press old the F f again to apply the filters now have photos here and I can sort with respect to the last name. So we have learned a few formulas here just to recap we have learned right function we have learned left function. We have learned fine function and we have four learned l e and land function for the length of any text. Now let us brings little complexity to this problem. If we have names with three parts So first middle and last name how we can carry out the same task using the techniques we just learned. So I have first same applies here. I need left off Dix, take string. How many collectors first find the location off space in this text and we have two minus one because we don't want space to be included in the first name. Close it and we have the first name now how we can have sickened and last name. Here's a technique. Whatever we learned. Just try. So what I'll do, I'll just copy this formula. Control. See, I come here. I paced her Control V. Let's see what happens. So what has happened? It has split this particular dext into two parts. This is the first time. And it has combined the second and last name because the moment it find space, anything on the left is the first name. Anything on the right is let's a second name. According Toa Exit. Now we have this and we have to split it into again using the same technique. So if I copied this formula on the right side off this string, you can see have now extracted the second name. So this is my second name here. And similarly, this is the formula which extracts the right side of the text string. I copied this. I paced here and it gives me the last name as well. I should put second. Okay, let me get rid off these now Notice something that our second and last names are dependent on an intermediary cell which is 32 which in turn depends on odd base text media. So if I come here and press F two, you can see it's dependent on D two. Similarly, our last name is dependent on need to as well. So what we do, we come here. We copy this formula and we come to these cells. And wherever we have these d two, we replace. So I select this and Control V. I place it with the original form, a line, that particular cell. And now my calculation or extraction off second name is no longer dependent on the to I can do the same for my last name extraction. I highlight the to control V because I copied that formula from D to sell itself again here and here. So I have the same result. But I press f two. It's no longer dependent on there. So I can safely delete this column now. Little short skirt to delete select control minus. I have my 1st 2nd and last name. Now, even if we complicated further by having a texting or a name which has full parts, we can apply the same technique re of lard Here. I'll go very quickly. I'll copy this from here paste. So I have my first name. I have my second name. Yeah. The third name is actually third and last. So we have to break it again, as we did in our previous exercise. So similarly, I need to use the left function formula. I will take this. I'll copy here. So my third name is extracted to extract the last name. I will go to my first exercise. I copied this last formula and I paste. Yeah, this is my last. This is my third. This is my second. This is my first. I still have this intermediary cell which I have to get rid off. So But you can see after is intermediary and my cells G two and H two are dependent on this . So similarly, I will copy the formula from here. Andi, replace wherever I have to. I have in these two places I would replace control. We enter. I will do the same for my last name. Selective too. Control V. I have another after here. Select that Control V. I have last one. I select that control three and I have my turd and lost. Similarly, I can delete, but Intermediate e and I have my 1st 2nd 3rd last. So, basically, if you have text string using these formal last right left, fine or lend, you can carry out lots off tasks. For example, people who work in schools or colleges. The deal with lists people who work in banks who deal with, you know, a lot off account holder names. These are very handy functions at the end. I wish you good luck and see you in the next morning by 4. Master the art of dealing with information to carryout insightful analysis: Hi. Welcome to another exciting model Cracked the date of this. So, first of all, we need to understand what a databases. It's simply set off data, which has various columns. Andrews in this example, will go through this data set, which is a database off master distributor and they're distributing in various geography is , as you can see, Africa, Europe, Middle East, North America, South America and so T stay show. Similarly, what they're distributing can be classified in various categories as chilled products, frozen products or products which are kept at room temperature. Then there are further categories against each category, where you can see gan, full daily grains, ice cream, etcetera. And obviously all these sub categories are actually some branded products. So, for example, I've taken some burns here like Libby's Nestle Saadiya V it and then we have their say shown in 1,000,000 off dollars. So our Data BS is basically a dead assert, which has several dimensions. So, for example, if I take this particular room, we're saying that we have sold in the Southeast Asia in the frozen category in the subcategory off ice cream and the brand off Mars around $0.1 million. Similarly, if we take this, it's a similar brand similar category. But the geography is different. It's North America, and the sale is 2.4 million. Databases are very important. These are available in all kind of businesses, like banks who keep database off their customers with their demographic details as well as their savings or, if they have any loans. Hospitals who keep date off their patients. They're ailment record airlines keep did off their passengers when they traveled. What war? There's travel documents, details, how many people in family traveled and what are the destinations they traveled. Google are very good example. Whatever you do on Google, they keep track off all our movement. Also, when you download APS from Apple Store or Android Google Store, you actually give a lot of data there, which they keep, like which geography we are from what's our age and many a times the track, our Web activity as well. So databases are unavoidable, so it's very important that we understand how they work and if we learn toe slice and dice this data for some intuitive analysis, that would be really great skills that we cannot toe profile. So let's get started now. Similarly, as I'm doing in all other models will go like a case study. So you have seen this data and we have some questions which we need to answer. Like with geography is the biggest market. The geography is consuming least ice cream, which brand is best seller in Middle East. And what is the frozen segment split between ice cream and meat? So let's address the first question with geography is biggest market. Now. All these rules that you're seeing here, these can be in numerous, several 100 thousands because generally these are based on one order. So if we want to get the feel off a particular job, the field category, there is a very useful function in Excel, which is some meth. So if I put that here, for example, in fact, what should I do? I should just keep a copy off there so that we don't jeopardize, uh, provisional database and do everything here. So I name it Hulk, and we do the calculation you. So the formula is some If and then I select the range. This is the range we have to select. Then I have to give criteria my criteria. We have to see what was. The question with geography is our biggest market. So basically we need to find out sales off each geography. Now I have these geography is all here. So let me put it dark way I select North America coma and then I have to give the some range. CanDo. It gives me the sail off this geography against all categories. All brands also get a greased. I give it coma format. Keep it simple and I simply drag all the way to our last, which is here. So you can see here to 69 is the highest so far biggest market is Europe. Now a second question. The geography is consuming least ice cream. Now here is the trick. We have to find the sales off ice cream against each geography. How we can do that? I'll insert a column here and I'll do a function off concatenation. Congratulate is a function which combines to text strings. So could that got in eight first, which is our geography Second, which is ice cream. So what it does it simply combines these two and I pulled it all the way down. Now what I have to do, I have to put the formula. I can do it here some. If now I will give this range because we have to find out all the jungle fees against each subcategory because ice cream is a subject tickly where it should equal. I'll give this reference that North American ice creams sold in North America. That's my criteria. And I will choose this. Do some and it gives me the result here. Well, I dragged here. You can see the highest consumer is North America and least is Sultan Stasia. So this is our second problem solved. Third, which brand is best seller in Middle East. So what we have to do, we have to combine two things. Geography and brand. But how? Maney burns we have so many. Now we learn another function. First, let's change the concatenation. We'll come get in eight geography with brag and I I dread the formula. Now I have Mars Nestle so many now how we can do it an easy way. You just highlight this column control. See? Go to another sheet Control V Now Whatever data was there is here. I want to have only Mars appearing once or Nestle appearing once because I don't want to cap alert for all. So how I'll do that, I will highlight this column and press old D f A. What it does. It puts the advanced Philidor on this column and I want unique records. Only press okay, and it gives me all the brands appearing just once. So I copy this data and paste in another sheet. It gives me only unique records, and these are all continuous straws. The problem here We had that all the records which are not unique, they have been filtered out. So the data is still there. You can see their data is actually hidden. So I delete this sheet because our guys cleansed. Now let's go back to our question. The question WAAS, which brand is best seller in Middle East, Which means we have to check the sales off all these brands in Middle East. So we put a concatenation formula here. I'm getting nicked in inverted commas, Middle East inverted, commas closed and bread. So I have now all the brands sale criteria. Sit here and I can put up some if formula some of my range in my database is this concatenation, which has geography and bread. I select that my criteria is in this sheet, which is this and my some range is the column having the sales figures. So now I have san figure off Mars bread in Middle East. Now, if we see here, the highest is the Monte and the lowest is Tilden. So the best seller brand in Middle East is I don't want last, but not the least. What is the frozen segment split between ice cream and meat? How we can crack this If you can see one of our category is frozen. If I select this data for frozen only, then all the records are filtered for frozen category and we'll check the sub categories. We have ice cream and meat, so we need to find the split between these two. We will simply some, if subcategory column for ice cream. Oh, so we'll get the result. And similarly, we'll do the summer for meat and we'll get the result. If I add both of them. We have 253 million sale, which is split between ice cream and meat, so their respective share will be. Don't forget toe freeze the denominator Then I can word this present age by control shift present age and I dragged. So the split between ice cream and me there's 27% and 73% 27% to 73%. Sorry. Oh, that's it. I hope you would have liked this section and I'll see you in the next model, but by 5. Simplest way to consolidate similar data sets and rearrange them the way you like: Hi. Welcome to the module off consolidation. First, we need to understand where we need consolidation. For example, if you are requesting data from various departments in your organisation and you have given them templates to fill in, then after the deadline is over, when you receive those templates, you have those information available and you need to consolidate them at an organization living. Similarly, if you have several entities, several businesses and you're requesting data from them, you have to consolidate them once you have it. When we report our financial results, the companies which own further companies which are called subsidiaries or group companies or sister concerns they need to consolidate their financial results. Now, how to go about it? There are several ways you can always link thes Excel sheets, which are templates given to these entities, and you can add those up. But today I'll be teaching you a technique which is very dynamic, and you can do different kind of consolidations and I'll show you the practicality over. So let's get started. Heavy is the example off various cost centres. As you can see, these are like system cost in their numbers. 501 5 11 and all these course centres have some data. These are various expense categories. At the top. We have revenue categories, so it's basically a profit and loss statement for each course, and you may see there are a lot off accounts which do not have any value they might have value in some other cost centre. What we have to do, we have to consolidate them into one organisation level, profit and loss. Before I jump into the consolidation part, I want to show you how to deal with such kind off data where you have so many blanks or zeros because it's very problematic and untidy when you deal with such dead. So I always do one thing at the end off such data. I put a column which classifies that particular role as zero or non zero. So, for example, have already put this column here, and I have put a formula here, which is if the some off all these data, which is here in column C, is zero. Then I classified as zero. Otherwise I classified as Nancy and in order to cleanse it, I simply filter for non zero rules. So now you can see. My data looks much more understandable in a cleaner. For now, I have some macros, Ritter, which I'll tell you about. I can expand it. So I have to first enable the macros. I can expand it back to its original state and I can collapse it and it comes back to the filtered level. How we can do that? I will be covering that in another model. How to put these buttons here, which can do this kind off Very handy stuff. Okay, so I have all these data you can see. I have these buttons in all sheets. I can do that. Now let's take one sheet and walk on it. I will make it a copy. Let's move it to the face off the workbook. And I called it console. Now what I have to do, You come to the first data point, which is here now it's a formula. If I press f two, I can see this formula. Second is again a formula. Here is my data set, which is zero. But it might have some data in any off the cost centre. So what I have to do, I have to write some but And this is I go to my first sheet, so which is selected here And then I press shift and go to the last one. Now you can see all these air highlighted and my formula has turned into 501 colon 561 means all these sheets inclusive. Then I give the reference off the cell which is Q five pressed. Enter And it has now consolidation off all these into this. Now my task. I want to copy these form now throughout the sheet. But the doubt these cells which already have some formula So a good way to do that. I put control. See, I have copied this and I select from here I select all the place where I have to put my age down. I go all the way to the end of the sheet. All the area is selected now I press control G. It's a go do utility in excel Very handy. You go to special now you select Constance only so Constance. And if you press ok, it will highlight only door cells which are constants. So you can see all those cells which are constants have been highlighted those cells which have formula. They are not heard. And then I simply I can do control V that will pace my formula. But I always use old E S F which placed special formulas on Why do that? Because I don't want to jeopardize the former. I want to keep the former's. If I copy control V. In fact, the former discovered as well. I might have different colors in those cells, so those colors remained there. Only the formula off the Selva charm copying is copied, and I press bookie. Now all this is copied. If I collapse it, you can see I have consolidated sheet, which is relatively bigger than any other sheet. If I come here collapse, it's a fairly small because it has only data in these accounts. But in my consolidated treat, I have data in various accounts because its picking from all the cost centres. I hope you understood Abdel here. No, I could have done instead off this I could have simply taken some from here, plus self from here, plus cellphone here and saw I would do control Z undo. But the problem in that technique. First of all, it's cumbersome. Second, the next feature, which I'm going to tell you about this particular Stein consolidation that you can. It's very dynamic you can play with it, which is this consolidation is based on the starting sheet off 501 in the ending sheet off 561 you can see. For example, my total expense for February is 3 98,000 If I don't want this course center 5 34 in this consolidation, I just come here and drag it out off this ridge, and you would see my consolidated number is reduced by the amount, which is in 5 34 If I want to pull 5 16 just drag, it's out and my consolidation drops the beauty off it that you can have a very dynamic consolidation. It becomes very handy when you're doing budgeting. You have a budget allocated to all the departments, and you want to know the budget off all the departments except ICTY and finance. So you simply take these 24 centers out and you will have the consolidation ready. Similarly, if there is some reorganization, for example, my consolidated sheet is for commercial operations, and it has 78 She's between 78 Cost centres are there and there is another cost centre in some other category off the same organization which is being transferred here. So there is some business reorganisation what we need to do. We simply bring that sheet in this range from 50125 61. I simply bring it inside and my consolidated number is ready now. I generally do that. I put I insert a blank sheet here. I folded. I stopped. Then I put a bland sheet at the end and I call it end. And in the consolidated formula, whatever. I have 5 61 I just copy it controlled. See, I select the whole sheet and I press control h which is find and replace function. I control V paste that here I have to replace this particular text in this sheet with and Alan comma calls. And I replaced so all my legs off replaced Similarly, I'll go back to the formula and I'll see my starting point is 501 I copy that. Select the whole sheet, find this and change it with stopped. I have to keep it exactly the same stomach I select all the place and you can see all my formula are now start and end, which means I can even pull out the last four center because I don't have any data here. It's just a reference, and anything within this range will be consolidated. I hope you would have enjoyed this model and I'll see you in the next one by 6. Use what-if analysis to stay ahead of game: Hi. In this section we learn some excellent analysis function to put you ahead of game. A basic, fundamental concept off any business is profit maximization. Now, whether you are in a business which is selling products or a business which is rendering some kind of service, we have revenues and costs. Revenues minus costs is our profit. And that is the ultimate goal off the business that we have to maximize that profit. If I can explain that graphically and I put a chart on excess Xs, I have costs and on why exes. I have sale price. This line would be the profit line on the scope. You can see if we increase the costs, our prophet would decline. So as the cost is increasing, a prophetess declining Similarly, as our sale prices increasing, our profit is increasing. Now the goal off any business is to be on this CO. We have to move upwards in this direction. Now we can use a very handy tool in Excel, which is called gold seek to carry out such kind off analysis and that can put you really ahead of game. You can actually forecast and set the direction off your company. Let's see what kind off questions we have with this data. We have a company named Future Chairs Inc. It produces chairs. Very simple example. We have model Gosper JIA. We have labor cost per chair. We have sale price poacher And this is the company Whole overheads as in salaries off staff which is not included in producing those chairs. And we have put in this scenario that we have to produce 1000 chairs that says you we've got an order for this month and we need to produce these chairs. So how much are sales would be if I come to this cell interest? If two you can see the number of chairs multiplied by the sale Price Butcher. So 20,000 bucks is ourselves. Now I'm a deal. Cost would be similarly material. Gospel chur multiplied with the total number of cheers. Same goes with the labor cost and company overheads. Since these are our top off, our manufacturing costs these tool material on labor. Uh, our men affection ing costs overheads. Come on. Top of that and all these three components give us total cost sales minus total cost is our profit. So 4.250 4000 to 50 would be somewhere here on this gulf. Let's see what kind off questions we have. Okay, first, how many chairs to sell to break you? Now I want to show you this thing that if instead, off 1000 reproduced 9 50 Just people I on this trophic Fieler it produces, I reduce it further. 700 introduces for I put just 20 in our prophet Don's negative, which means we are actually making loss. So you can see if you produce less Our prophet reduces, and ultimately it becomes a loss. So we need to know how many chairs we need to produce in orderto at at least break even. Break even means zero profit. How we can do that. So if you go toe data What if analysis and then gold sick? It gives us three parameters search. So? So what is the sell? We want to achieve a particular value. Our prophet What value? Because we want to break even. So no profit, no loss report Cedo, by changing would sell and we give the reference off. How many chairs to produce now when I press OK, it changes the number here and gives us that if we produced 321 they make no profit, no loss. We break even. You can try this. We can put 319 and we make a little bit off loss. We make 3 21 and we make a little bit off profit at 320 we are breaking even know profits. So that is how we can find out the break even point. Second question. How many chairs to sell to make a profit off $1000? We go back in for gold, Zeke. Now, this time, our self reference the value that we want to set and we want to achieve instead of Zito 1000 profit again. By changing this cell which has the number of chest produce, I put okay and we get 400 80 chairs to produce. So that is the answer to our second question. Third, if 200 chairs to be sold, how much sale price can be dropped without making loss? Now, here we are, fixing the number of chairs to produce. Now, a practicality around that would be that we have limited labour available or limited supply off material no matter what we do, but we cannot produce more than 200. So the question is, if 200 years to be sold, how much sale price can be dropped without making loss. So we need to find out the sale price, which is here. How much that should be that we do not make any loss. We put the gold seek and give the cell reference off profit to zero by changing with cell not cheers to produce this time. But the sale price and we find out at $23.75 we actually make no loss. I just increase the decimals here last, but not the least by what percent material cost to reduce to increase company profits by 5% . That's a tricky one. Now, this is a scenario that we have any number off chairs that were producing, say, 1000 are profit is in Tulsa. We want to increase our profit by 5% and we cannot increase the price because off maybe competition. So what other avenues we have? Labour goes probably not a good idea, and maybe the law is probably thing that we cannot produce the hourly rate. But yes, we can moved toe cheaper material suppliers. Or probably we negotiate the material cost well, or probably we get bulk supply in order to reduce the cost, so they could be several reasons. Now, we want to know how much material price or material cost should be reduced if you want to increase the company profits by 5%. So what should be the company profit? I calculate that my current profit in tow 1.5 that will give me a company profit, which is 5%. So we know that when we put the gold seek, we have to put the target value. So my profit on target. Well, you should be 8400 by changing the material cost. Keep in mind, we have 5.45 no deal cost. As of now, I put okay and I get 5.5 So our material ghost old new old was 5.45 and new is 5.5 We have to reduce the material cost by 7%. Simple formula. How did I calculate that? One minus new, divided by old. That will give me the percent age reduction from all. Don't you know I want toe put this cell in percentage Former control shift five and it changes to percentage. Former let me increase one decimal 10.7 point 3%. So the answer to last question is that we have to reduce the material cost by 7.3% now, why? I call this ahead of game, because in any business scenario, when your ultimate goal is to maximize profit or limit your loss, such kind off analysis becomes very handy because you can talk with data. Imagine you're sitting in a business meeting where you're discussing sort of challengers. Maybe there's pressure on the price. There is labor strike. There is hiking in material cost. Some import duties are increased. So in a very dynamic and changing business world, you have such kind off limitations all the time. This kind of analysis makes you agile. You can respond to such kind of challengers with your mitigation plan. I hope you enjoyed dissection, and I'll see you in the next one by 7. Simple but very powerful techniques to drive the performance of teams: Hi. Welcome to the section Name to be like a boss. Many times we required data from business units, other users, everyone their data in a specified former. In order to get their data, we generally designed some templates and request those people to fill in those templates. But it's a common challenge that you hardly get complete data or data, which is in the right format. So how to overcome this problem? Well, there is a very handy tool in Excel data validation, which we can use for this kind of problems. Now, in this particular exercise, I have poor to some simple data points that we need from se various departments. We need unit sales forecast. Then we need also of you on that forecast. Whether that is pessimistic, it's neutral or its optimistic. Then let's assume that all departments have to arrange an event. It could be a sales event or maybe investor relations event, so they need to decide on the date. It's a prospect of date and obviously that comes with a cost. So we need event cost. Also, all departments can subscribe to some available services where they can market their product. So we would want them to tell us what kind of subscription they want. As in whether that subscription is paid on a monthly basis weekly basis. Semyon will. And what is the end? Will cost for that subscription at the same time, we want them to tell us if they want any additional sales force. And what would be the Salesforce? Our lead it. Now, This is the input which already has some data here just for the example. Uh, and now we'll see how data validation can come in handy toe help in such kind of situation . So for that, you need to goto data tab, and you can see here in data tools. This particular Aiken, if we click of window appears which is for data validation. The Excel short, curt toe reached. That is old. A glee glee. And this window will appear. I always loved these shortcuts. You would have to struggle in the very first few days to remember them. But once you remember, they will become your second nature and you will save hell. Amount of time. So the first input, we want them to put unit sales forecast. Now, this particular let me close this This particular cell currently they can fill in anything and they will say they have given us the input. But we cannot do anything with this kind of data. So we have to restrict a certain type off input in this particular cell. How? Well, do that again. I'll use the short court or a the V and I will put allow only whole numbers. I can also give a range. So minimum obviously zero. They cannot put minus maximum, put arbitrarily Very large number. So now you can see they can put 200. Our sheet will accept that. But if they put minus five, it will give an error message. If they put any non numerical data here again, they will get an error message. Okay, moving Oneto 2nd 1 Now we want them to classify this particular input whether they think it is pessimistic, neutral or optimistic. I have another sheet validation data and I have put these scenarios here Pessimistic, neutral, optimistic. So how we can control him, click on this cell and go toe data validation and what kind of values we want toe allow here. We want them to pick from a list So we have to give the address off the list. So we select these pessimistic, neutral and optimistic Select these trees, cells press Sochi and when they will come here, a small ad would appear here. And by clicking here, they can select. Where do they think about this forecast? You want to put any data here? Excell would not accept even new medical. Then in the prospective event date, we want them to restrict it toe a date. So come to the cell. All a levy date of elevation would appear. We won't restricted toe date. Let's assume we are doing our budgeting exercise for 2020. So start date would be seed over one Jan. 20 and and it would be 31 December 20. So now they can put to say 23 August it will accept, But if they put 34 a number, it won't accept. If the put any letters, it won't accept either event. Cost How to put limit here? No, here comes the tricky part. Looks like in this particular example, you are trying to make budget for the company so you cannot give the departments of freehand toe put whatever number they want because generally use the department's sandbag their budget. So how you can control that? You go to data validation. And now any value they can even put in decimals. Find no problem. Minimum zero, but maximum. They cannot call beyond save 15,000 fresco. Okay, so up, Bill. 15,000. It's fine. But 15,000 and one it will give the Now comes the subscription type again. We want them toe choose from the available list. If you go toe evaluation data, I have put this weekly monthly, quarterly semi and will animal two years, three years, more than three years. So these are all possible combinations, you know, subscriptions are available in the market. So again we'll go to data validation and we'll choose from a list. Will give the link. Select all these press. OK. And now this drop down would appear they can choose subscription on will cost again. We can limit it so that they do not demand a very high budget. So I want to restrict it decimal value minimum zero maximum save 20 times. So after 20,000 is fine. More than that, we'll get another additional sales force now, obviously, in order to have easy life. They would want toe recruit as many people as they want, but we want to restrict it or a V V. We get dead of validation. I cannot give decimal because we cannot hire 2.5 people. So I support whole number Minimum CEO. And after discussion with the HR, we have decided that no department can hire more than 10 so we limited toe 10 so they can request 10. But not beyond that again. Salesforce Hourly rate they have toe specify here. Now we can restrict values here as well. It can be decimal many. Mom, let's zoom. Obviously, all the countries are geography is they have certain labour loss and you cannot pay below the minimum wage. So let's assume it's $10 but at the same time, we don't want them to pay extraordinarily. So let's assume 30 is the upper limit. So anything in between they can pay now how you have put data validation here, you can control work. Your departments or users can fill in data. So we have solved one problem that they cannot put data which you don't want. But at the same time, they would simply not put any date. And they will send you this template and they will classify it a proper submission, whereas only you know that it's incomplete data. So a very small technique which I've been using throughout my career and which proved to be very, very helpful. I want to share that with you. How you can control that, that you get a proper complete submission. I go here and let me just copy all these. I could be here and then I put formulas here. If I go to the import cell, I give reference that if this cell is not filled, if it is empty so if this is equal to empty. So I put inverted commas without putting any thing in between, which means it's an empty cell. Then give me Siegel. Otherwise, give me one. So since they haven't put anything after 20 if the foot say to 50 it's a valued input. So I get one now. I haven't here for simplicity sake, let me delete fuel from here. Now, I summed that up here. I put summation formula here. I always use the shortcut key, which is all. Plus it will give me some. And here I can come. How many in Port Side acquired it? So we can put the Formula five by eight and convert this cell into percentage shortcut to do that Control shift five. So we can see that our input form is 63% complete. So how I control it, I just come here. I put completeness score. And I think this cell too. Here. I need to change the format of this cell again. Control shift 5 63%. I want this to highlight so I can do some conditional formatting in the home dub. You go to conditional formatting and I concerts. Um, rules. So let's see. I'm saying, if it's less than 75% then make it. I want to custom former and I want to fill it with red color so that it's very conspicuous . If they have given us save beyond 75%. I put another condition here that they have given us between 75 percent to 99 percent. Then custom format. You fill it with yellow. So if I put some cost here, 2000 at 75 turns yellow and last but not least hi night. If it is equal to 100% then custom format. Make it green. Okay, I dropped this classified about forecast Figure this eggs neutral. It turns yellow because they have reached 75% perspective even date say, 15 off November 88% Still yellow subscription and will cost two C 5000. It hits 100% and it turns green, so you can very easily control that when people submit, they cannot claim that they have submitted unless they have submitted in our format and completely so. That's how using data validation tools, you can behave like a boss. You don't have to waste time chasing people to complete their inputs, and you can work more efficiently. I hope you will have learned some good tips here, and I'll see you in the next model by 8. Learn how to showcase your hard work by presenting in an interactive manner: Hi. Welcome to the module. I call auto men Shia. Now, this is the section I feel most excited about. And if you really want to impress your boss, your coworkers or executive management, you got to learn this. Take it very seriously because the outcome off this is support up. I must admit that I got immense popularity in my early Guerrier when I first used this and you would see that it's simple, but very powerful. Okay, so to get started, I'm presenting here some sector daito, that zoom. It's a government data off revenues generated in each sector, or it could be an investment holding company with their investments in multiple sectors. So we have education, sector, healthcare, aviation, oil and gas and so on. And we have three years data off sales, and these data are presented in monthly facing so that we can also look at the seasonality . Okay, now this data we need to present let's assume we have pulled this data out off the source accounting system and we want to discuss, so I will show you how we can use some automation tool to present our data in more structured way and in very interactive where you can actually talked to data. You can analyze it life when you're walking with your co workers or sitting in a meeting. So I will add a worksheet. Let's call it somebody, and I put ahead of here. I select that's how I generally former my sheets and I call it set, uh, somebody I really love this. Form it that your title, you give, uh, dark background. And why next board? These grid lines always annoy me. So I hide them and a formula to do that or w t o you press this again or W G. And they will appear back. So old W E. G. And the grid lines are gone. Before we go into that, I want you to focus on this. It's a pyramid. Whatever we do, the rule off 2080 applies. What do I mean by that? This is 80% which is hard work analysis, and obviously before analysis, data cleansing. All this is laborious and time consuming. The 20% which is the top. It, um it that is presentation. Throughout my career, as I was learning and growing, I kept on mentoring the young individuals about this rule. We all generally focus in this area and here we ignore. It's very important when you put 80% efforts to carry out some fantastic work. You should put really focus on that 20% side off presentation. If you have some flaws in your hard work but you present it well, it will be accepted. But if you have very terrible walk but not well presented, it's not taken very well. So it's very important, guys. Then he raised this and we move on toe a real topic that how we can use some automation to showcase our heart book. Okay, see this developer option? It's generally not available in all exit. So if it's not available, what you have to do, go to file options Customized ribbon and here main taps. You see this developer option since I have developer option available, So I unchecked and now you will see that it will be gone. So most off you might be having this kind off river go to options, customize ribbon and check developer so that it appears on your excel. No, When you goto developer come to insert, we have some form controls here like Gumbel walks, we have checkbooks. We have radio control or option button. We have some simple buttons, so I'll show you how we can use them and show our work in a presentable way. So I click on combo box. Can I draw here? Once you click This plus sign appears and currently doesn't have anything. Okay, so what we want to do We want to show these sectors did over there. So basically we want those sectors to appear here. And when the user selects any sector, we want to see that particular data in this area. How we can do it first we have to create a list which I have already created. So all these sectors I have put them here. And on top of that, I have put select sector. Okay, so I go to somebody I right click on this and select former control input range. I click here, go to my data and select this list. Then based on the selection, it will give me a number. I have to give a selling where that number will appeal. I give. Yeah, I always like three d shading, so I really shared it. OK, now you can see one soup click here. These sectors will appear like select sector and then you can select these sectors based on the selection. The link that we have given here a number will appear which is based on the location or position off that particular item in that list. Currently we help chosen infrastructure. So that is number six in that list. 12345 and six. So you can see now if we click here are dropped down appears which shows select sector education, healthcare. Our list is now available here at the first selection which is select sector. It gives us the value one. When we select save 12345 we select the fifth option. It will give us number five here. Now, once we have selected say, select sector, which is not a sector, it will appear by default. The value would be one and we don't want to use this number. So we'll have another cell where we want toe reduce one from this And this is the sin we're going to use in all our automation hands folk. So, in order to put some data here way. Want to show sick, uh, revenue? We want to show sector growth, and we also want to show cumulative or compound and will growth rate gaggle. Let's assume we want to show last two years. So I want to show 2018 and 2090 first. We'll have to modified this data of it. I want thes two years here. I want 2018 revenue, which is this and 2019 revenue, which is this now. You might be wondering why I'm putting it here, whereas I already have them here. See, because I'm showing this data here or is entirely so if when I'll put formula here, I can simply drag it. Whereas I have these data sets available in one column, so I cannot do that. I put them coma, former on then I want to put some growth rates so growth rate off 2018. How will calculate that? 2018 total sayings divided by 2017 minus one 4.3% toe converted toe Wasn't age former. Simply click here or alternatively controlled shift and number five. Similarly, for 2019 take 2019 revenue divided by 2018 revenue minus one and control shift five. I have minus 6% group in 2090 to put compound annual growth rate, C, k, g, R or cattle. The formula is or before we go to the formula, I want you to understand this. How many years we have. We have 17 to 18 1 year and then from 18 to 19 another. So we have two years jumps. Remember this when you put cmdr formula, you take the last year figure, which is this divided by the first year figure, which is this and the value. So you would see on the keyboard there is a small hat button which is on the digit six. So you press that by shift six and it will give you that and you opened. Parentis is one by two. Parentis is closed minus one. This is our compound annual growth rate. I convert this to present age former which is controlled. Shipped five. Now, One thing you want to understand here, why I put two here because we have two jumps. If we had five, then we would have taken last year number, which is this and then the initial number, which would be five years back. And then instead of two here, we would have put five. So that's how you calculate the compound annual growth rate. So know we have a tear. Let me simply copy this in front off each sector. And it will. I can relate this particular metrics for each sector. So I'm simply basting in front. Off each set. No, we have our form. So this drop down menu here, a Congo box. Now we want to show the revenues and growths. So how we can do it? There is a function or juice What it does based on a particular value, it Jews is from a list off items. How we use it. Choose it requires an index number which we will take from here going. And we want these values. So we want the annual values off 2018. So, this Goma, this guy, this guy, this guy, this comma this going on, this going on this and, uh, it will give me another here. Why? Because it works on one. So if it finds one hair, it will make the first value, which is 4 64 But currently it zero. And I wanted it to be zero, which I'll show you. Why at select sector there is nothing, so I don't want it to pick any value. When we select education, it's the first sector. Our dog itself becomes one and it picks the first value, which is 464. I put it on, form it and you can see if I select any other. It picks the corresponding revenue figure. Now, don't forget that this in this formula you have to fix this index because we want to drag this form of how to freeze that function at four or simply before, and it will freeze both column and rule. And now we can drag this and it will not only give us 2018 revenue, but also 2090. Similarly, if you drag it down, these data which we haven't fixed for either row or column, will be dragged down this well, which corresponds to our data set, which is below which are growth rates. So I will change this to a percentage former and we have our growth rates here by the selection off different options. These figures change. You can see we have, after the growth rate off 2019 the scag er calculated here. If I drag one more time, this figure will be gathered. And I can simply bring it here because that's where we are showing gag. I hope it's clear up to now. Now we'll move on. We want to show some graphic representation off data as well. How we can do it. We come to data, we come down and then here, let me just copy one data set so that we have the former here. And then I want to use the same juice function. Choose based on this index and I freeze it. My pressing. Therefore, it should pick 2017 Jan for education. Coma for next sector Coma Next. Common. Next. Come on. Next coma Next. Goma next. Goma Next. So based on this election, it has taken the corresponding value. I simply dragged all the way and drag it dumb. So we have all our data. You can check it. We have selected nuclear. So it has pricked the did off nuclear which is for 19 4 16 for 40. But you would find here we want to use similar formula canIs will. So I take this I copy and I based here. So we have the name off that sector appearing here as well. Okay, now what we want to do, we want to show a graphical representation. So let's select the data. Go to the insert Recommended charts in the first chart. That looks good. Press. OK, they have the chart here we selected we cut from her control X. We go to the summary Control V and we based it. Teoh. Now you can see when we select our chart changes as well. Okay, now let's assume all this data is in USD and we want to give I user some option toe you some other currency A spell. So for USD, our rate should be one. And for let's assume, we want to show in u eight dirhams and the conversion rate would be 3.67 So how we can do it? We can automate that by using another form item. So you go to developer concert and see this option. But, um, you click on this plus would appear and you can draw it anywhere. I draw it here I click inside and right u S d and I delete the text. I go here again. I select and I put and not the and I put e d. Now I want to align these to how you can do that, right? Click here, then press control and click on this supporter selected. Now you go to format online a line center. They will be aligned at center, huh? Control? See, you want to align them, Aliant top and they will be aligned in same horizontal line. Okay, so what happens? Similarly, we have toe now former control here. So right, click. Go to former control. We have to give a selling. I come here and that's a zoom. I give a selling cough. Be 62. I want really shading. Ok, When you do that, the option button which is next to it, gets the control for my thing automatically. So when I select this, I will have one appearing here. And when I select this, it shifts from here toe 80. And this value changes toe. No, we can apply this particular currency instead. Off ust how we can do it. We have this number here now what we want. Based on this selection, it should give us either USD or 80 based number. So we'll say, Well, put a if condition if this number and we freeze it. So I press f four is equal to one. Did this number which is currently the number appearing or else we want all of this selected opiate based here All of this multiplied by the conversion and freeze this conversion as well. Gross. You can see the number has changed because we have the selection off to. So you drag it all the way and then drag it. You can see it changes when I select different. My data appears or this chart appears in different currency. So we want the same to happen here as well. How well do that? We can put similarly if function as we have put before. If this and I freeze this so this'll I pressed f four. It's frozen, is equal toe one, then this particular function. Otherwise Goma, I copy that based here, multiplied by the conversion rate and I freeze that parentis is closed. You will see I have now this number changing as well. I drag it. You can see nothing would happen here because thes our growth rate so we don't need to apply here. Okay, Now, generally, we don't use this, but it's very handy. When I learned I was very excited. Maybe 15 years back. This chart title is generally we type here, but you can actually link it. So come to this charting to click Here, Come here. Press equal sign and give it a link. So we go to data. See, we have this name here which is already picking from the data. So we select that. And so we have tourism? No. When you select any other option, not only my data changes, but also the title. So it becomes very interactive, very dynamic. It's the same data which is here. But how you're presenting becomes very presentable. I can, for example, delete this part. And I have a mini dashboard where we are showing the same data, but in a very presentable way. Now you can have several other options. And this developer tab, we have some checked boxes. So, for example, if you want to show something else, you can choose that Jack box option. There things would appear if it's not checked based on the value, we can do different kind of things now. One thing very important here. Once the sheet will be lorded. We don't want any particular selection to appear here. We wanted to remain that select center. What would happen? We would have nothing appearing here. Why? Because we have now. The target cell is zero and jewels function only works for the values one and above at zero value. It gives error. This error is good for us for this graph. Because nothing is appearing but not good here. So how we can control it? You can put another a function. So you come at the very beginning. Copy all this formula. But if is end of Goma, then all of this function which is already here. You go to the bottom off. It is energy. You close it. So this is our logical test. If there is air, it will give a true value. What should it do? It should simply not sure anything. So have put two inverted commas. Otherwise, I want all this formula which is here to work out and present us the number. So I control V because I had copied all this before. I just placed a game and I closed the breath. So you would see now the editor is gone. Once I select the number appears once it goes back toe select. The number disappears. I simply dragged here. I said you drag it here and you would remember that we had growth rates off 18 and 19. Calculated some redenius Lee followed by Canada. So for Jagger, I drag one more time. This would give me Jagger and I delete this and bring the Get them here. So at the landing page, I don't have anything appeared here even if I select this. But as soon as I select any sector, the numbers appear. I just have to change the former here. Selectees on. Put, uh, percentage forward. Now let's go back to select sector. We still have this error, so we have to remove that as well. You can do that by again. Same thing. Copy this and put any function If is ever But And this is open parentis is closed. Coma. If the other is there, just show us nothing. Otherwise I faced the same formula which I just copied. So control V parentis is closed. It will disappear. So at this point off time, our landing page. We have nothing, and when we select, everything appears and we can have. We can put more radio buttons here and show more currencies. This particular bit is very entrusting. It's like a micro programming that you can do within the excel. If you start doing it, you would love it. And it's very easy way to impress your co workers. I hope you would have enjoyed this and I'll see you in the next model by. 9. Scenario analysis: Hi. Welcome to the section I am sensitive for this will use the same example we have used in our previous section where we learned about goal, seek function and how we can use it to carry out different, kind off analysis. Now there is some cool feature available in Excel if you go to data in the ward. If analysis, we have data table. So we'll learn how we can use data tables, toe do sensitivity analysis we have seen before that if we change the material cost if we change the sales price or if we change the number of chairs that we have to produce, our sales figures are cost figures and resultant Lee are proffered changes. So if we want to show ah, holistic picture off how the profit off company is sensitive to this. These perimeters, we can show that very beautifully in the excel. So what I want to do I want to just reduce the size of peace columns. Maybe it's still small, so little bigger and see on this excess. I want to show the sensitivity toe sales price and I start in between the price that we currently have, which is $20. So I put 20 and then I give different values off sales price to see the sensitivity off our profit. So let's give a break off $2 each. Now, if I want the following cells to have a difference off to just select these two and drag it will give you all the following numbers which are a difference off to you. Drag left words and the same would happen. So this is my sales price. Let me move it here and I monitored. I also want to put a border here. So if you want a board around all the shortcut for that is control shift seventh and it will put the border here. Similarly, I warned the sensitivity here for number of chairs produced. So let me add a couple off columns here. You know, this should be little narrow and I give currently 1000 I put here and I want to sensitize it, Uh, at increments doffs 100. Cheers. So I take it I select these are not 111 100. So the difference is 100 years. I select these two when this plus sign appears, I drag it say to 2000. Similarly, I could drag it upwards to 400. Maybe I should move this a little bit lower because I want even 200 to appear here. Now this one is the quantity. Let's much and center and even center were quickly and I put the border by control shift seven. Okay, so now what do you have to do? We have one nexus here and we have one nexus here. Where these to coincide. This particular cell here you have to put link it to profit. Whatever you will link that value will appear here in metrics against each parameter. So, for example, here the value would be against price off 80 and quantity off 400. So let's see. Powered walks, then what you have to do. You select all this area, you go to data. What if analysis did our way? You have to give ruin, put self. So wrote input cell would be our price and column input cell would be cheers first. Okay. And it will give you the value so I simply increase so there. We can see that these values and I want to change the format toe goldman format. Don't in decimals? No. All these values are appearing here. You can see that Lower price and lower quantities. We are making loss. All these negative figures. And as our sale price increases are, profit increases now lets you some conditional formatting here. I select all this results. I goto conditional formatting on these color scales are fantastic. You can see that we have different heels here which give very nice picture off different scenarios that as we increase the price, we go into the green region. This is in a year where we have mediocre profits and removed this side. So we have actual loss now. One thing Interesting. You would see that we are producing more. But at these prices are loss is increasing, even if the quantities increasing. Why is that? Because at these levels, our sale price is even lower than our cost price. Which means at this price level, the more will sell. Our lost would increase because you can see it. Here are material cost and labor costs together is 13.75 which means a sale price lower than this would reserved in a loss. So if you are selling your cheer, it loss the more you will sell, the more you will lose. That's why you have, Ah, very darker shade of red hair. I hope you would find this particular sensitivity analysis Feature off Excell helpful, and I'll see you in the next section by 10. How to keep your secret work safe from spying eyes: Hi. Welcome to another exciting section Top secret. If you want to hide your walk, which is top secret from spying eyes, there are several options available in Excel, which will discuss now, and we'll discuss the security level off all those techniques. Some can be all right, and we'll also tell you simple jailbreak techniques for that to get started. We have a simple form here, so it is a quotation model, which we have given to our sales force. It's a construction company where sales people talk to the clients on. They assess what could be the area off the building. So based on a very primary design, they put the area figure here and then this. Select several options. What kind of services the client is looking for. Great structure. Yes. Do they want to put flooring as well, Electrical plumbing, H V, a. C smartphone connectivity and drilling management system. So if they want services from us, we will simply select years. Otherwise, we'll have no and we have back and calculations sheet where we calculate our cost and we give them the quarter price. So, for example, in this example, they want let's assume 10,000 square meter area to be built and they want all the services . So we select all the services. Yes, and they want manager discomfort us? Well, yes. So Price produces our sales. Guy uses this form and tells the client that your quotation would be $278,000 on the back end, we have the calculation sheet which is linked to our input or quotation sheet. We have some trade secrets here. We have cost for square meter against each of this service. So these are our trade secrets. Another very big secret is our markup. We are putting 25% Markel and quoted price is dependent on a markup. So these are the secrets. We don't want the Salesforce to see how we can hide it. At the same time, we want this morning to be dynamic so that they can put inputs here and get the quoted price so very primary level security technique. We just hide this sheet now. This sheet is not appearing. Now the sales guy is doing changes here and get in the quarter price, but doesn't know where it is coming from. Obviously you would be laughing at this that this security is almost zero because he can come here and see that it's linked toe another sheet, which is calculation sheet. If he tries to follow the link, let me give you another tip here. If you want to follow a link, you have to press control and this square bracket. So I come here and I press control square bracket. It doesn't take me anywhere. Why? Because the corresponding sheet is hidden. So I come here, right click on hide. It gives me the list of ships which are header and I unhygienic. Now I go back here put my Kerr severe and press control square bracket It takes me to the cell where it is linked toe so you can see a smart sales that are not even smart A very average excel user can very easily jail break this So how we can avoid this and have better security living So for that first you hide it Then you go toe review and protect the workbook It will give you a prompt to put a passport Put a very simple passport I'll put my name after, but names are generally not a good idea to put us password very easily. People can guess it. Okay, it will ask again, and I have now protected the workbook. Now you would see when I right click. The unhygienic option is disabled, so it's a slightly better security. But bear in mind when the guy comes here, he finds out that there is a sheet in this world book with the name calculation sheet, and this particular cell is bringing better from that sheet. It's hidden and it's password protected, so he cannot bring that another way to check that is press old F 11. It will take us to the We be application, and you can see we have one sheet, which is quotation and another sheet, which is calculation shit. Okay, so it's, you know, relatively better security, but we can still jailbreak before we go into dark. If we still want to improve this, as in, the person can check that there is another sheet. So what you have to do, you have to protect this sheet as well. You have protected the World book now, protect the sheet as well, and put the formula. It's protected. Now you would see that nothing is appearing here so the person cannot know where this number is coming from. But relatively smarter user would press. Old F 11 will go to the visual basic application and would see that there is another sheet which is hidden and its name is calculation ship. No, and even more or even smarter person can jailbreak this how I'll show you. So let's open and other excellent work dress control and in a new workbook appears. Now what I'll do on press equals in a link it here. Now it's frozen. So I simply press F four till I get rid off these dollar science. And I have this selling now. The sheet I press control square record. It takes me Here I go back. This is quotation sheet. We already know that there is a sheet in the Other World book with the name calculation sheet, and it's hidden, so I simply change. It changed the name here, Cal, Alicia, and you can see. Now it's fetching data from dark hidden sheet, which is password protected. I simply drag it. Don't drag it right, and this is our data. So a smart guy can gin break this and can find out the's secrets which we want to hide. So what can be done now? There is no straightforward way We can only conceal this So in, you know, in more than digital word we encrypt data. So what we have to do? We have to encrypt it in a way that he cannot read it. So how we can do that first I can protect the World Book, put the same formula. I am protect sheet for the formula. I unhygienic the sheet either by right click or unheard, or else I can use the shortcut, which is old. Oh, catch you. And this will unhygienic my collection treat. Now there are 23 ways off encrypting this kind of data. I don't want them to see these When they will read what is here, they will understand. Now I have done this. I want to delete this instead. I want to put a command here A shortcut to put commanded you get put command by right click and insert. Commit poor, go to the cell and press shift after the committee will be there And now I will would cost . But SQL So whatever data was here is now appearing in the command, but not in the body off this cell. Similarly, I will put our delete this and I will put command shifted to and total cost. Yeah, I don't want to call it markups. Soy delete. I put command shift off to Markle and all these names which are appearing here weekend, you know, hide them. So what I do, I simply delete them or I shift temp who commands section? I believe this as well. I didn't eat this. You on this and the last encryption I want to do is to encrypt this number, because what he can do, he knows that the quarter prices 1 39 0 create and in calculation sheet that number of years here. So some smart person can try to diverse, calculate and find these secrets. So what we'll do will simply multiply this number by some absurd number. See 23 full, 56 So it becomes a huge number. And what we'll do here, we will divide that number by same number 23456 Similarly, my cost per sq m. I have to divide. Why 23456 So I have my numbers here. He knows that the final quarter prices 1 39 038 But when they tried to jailbreak hidden calculation sheet, they won't find this value anywhere. Now go back to the other sheet. This data means nothing. Our data is encrypted. I hide this sheet by pressing old or h h. It's hidden. I go to the view. I protect the World book by putting password. I protect the sheet by putting password, and now I am fully protected. The user cannot see my trade secrets, even if they know that there is a sheet named calculation sheet. It's hidden and they try to jailbreak, but they will get some kind of data, which they cannot make anything out of it. So three cow. Okay, I have to unprotected. I am pregnant. She does will to recap. We have four level of security first, rather very silly one hide. Their secret cheat security level is very low, easily breakable simply and hide the sheet. You can protect the world book. It's a medium level security. You can protect the sheet. It's a medium level security. How to jail break you link to that hidden sheet and you can see what's in there, the top level security that in that hidden sheet you delete all the texts. She tried to shift them into commands so that whenever that's sell this link, nothing would appear. Try toe encrypt data by multiplying or dividing by arbitrary numbers so that no one can make out any sense. So I think by now you would have learned how you can protect your top secret stuff. Hope to see you in the next section by 11. An excel based To Do list which is simple but can do wonders: Hi. Welcome to the last model of the course as a promised in the beginning that I will be giving a bonus at the end of the course, which is this simple to do list. I prepared that list like, 10 years back, and I've been using it since then. Very simple. Whatever techniques and formulas we have learned so far have been used here. And very directive you can actually manage your walk. Not only yours, but also your teammates as well. So let's go through the structure of this sheet first. So that's me active. And let's assume I have three employees reporting into me. So I have employed ABC. Obviously, you can change your names here and then I have different tasks against each dusk. I have assigned time. Then I have priority, which is set to high medium low. I can change it medium here, low here so you can assign the priority. Yes, well, then we have the deadline for this task and the status in the status. I have data validation already put in whether that task has started in progress or completed a column day, which picks up which days that against each date. Then the task is assigned to a particular person. As in the 1st 3 dusk, I have to do myself next. Three desks I'm assigning to employ be than employees see And so on. Now I have some calculation done in this sheet. If I on group here, I have these number of factors which are due date factor, priority factor, resource factor. And then I have a composite factor which I used to sort this list. So I want to show you, for example, here I put a specific name complete excellent training, given 30 minutes. If I change it to complete and I put reset. Just see here. If I do reset the completed tasks, task is filtered out. So I have these two buttons put in hair and if I put show all, then it appears again. So when I reset, I only see the tasks which are still either in progress or I still have to start working on those Now Here I have used the functions which we have already learned to summarize the tasks as a 21st June. I have to do six tasks, imply a five employees to tests and three dust are assigned to employ. See now, as for us, I'm concerned my six tusks are split into these hours. So important I have three hours off walk that need to be completed today. You would see here it's showing two hours for high priority work. Now let me find out one off this and I classified as completed and you will see that my two hours will reduce. I have to once a complete I have toe delete this time and my hours are now reduced. I reset so that completed task is filtered up. There is another cool function here. I have the calendar view so you can actually see how many tasks I have to do on each day Have five tasks on 21st June, nothing on 22nd 3 on 23rd and so forth Based on the time required, I have also put till what time today I will finish my today's tasks. So it's always very handy when I go to office every day. The first thing I do is to open the sheet. I look at my tasks. I update priorities change on daily bases. So I organized that and then this is a key cell, which I have my own. At what Diamond be finishing all important things today. Now this is your task. Just use it. Enjoy it. Make it your daily routine. Trust me, it will help you a lot at the same time. Go through the structure, learn and implement art. I hope you will find this helpful and you'll be able to organize your walk in better way. Since this is our last item off this course, I would really want to thank you all who have taken this course. And I wish you good luck in your future. In your carrier, I would be bringing more exciting courses, someone financial modelling someone called finance topics. And after all, it's not a good bye. We would love to stay in contact. I was always more than happy and eager to respond to your queries. Commence once again. Bye for now and good luck