All Microsoft Excel Expert MO-201 certification exam dumps, study guide, training courses are Prepared by industry experts. PrepAway's ETE files povide the MO-201 Microsoft Excel Expert (Excel and Excel 2019) practice test questions and answers & exam dumps, study guide and training courses help you study and pass hassle-free!

Advanced Formulas & Macros

28. Scenario Manager

The Scenario Manager is the first of Excel's Data Analysis Tools. The scenario manager allows you to save and access specific combinations of cell values. And you can access it from the datatab in the What If analysis dropdown. Because of its nature, the scenario manager is commonly used for modelling exercises where you fix a combination of inputs in order to evaluate and compare their calculated outputs. So when you open the scenario manager, you'll see the following window where you can add a series of scenarios. When adding one, you'll specify which cells are the inputs, and then you'll assign a value for each.

And finally, you can alternate between showing the values for each scenario in your input cells and then evaluating the outputs for each. So in this example, you can see that we have different combinations of downpayments, interest rates, and term lengths that result in different monthly payment amounts. So quick tip: give your input cells meaningful names to create scenarios that are easy to interpret and manage. In the example above, you can see in the Scenario value screen that the input cells have the names percent, down interest rate, and term length, which facilitates filling them out. Now let's jump to Excel and replicate this example. We're back in the MortgageCalculator tab of our course project workbook in Excel. Now, when we went to ask for this loan, this actually wasn't the only payment option that we were given.

So right now we have a 20% down payment, a 5% interest rate, and a 30-year term length. But we were actually given three more options. So what we're going to do now is use the scenario manager to evaluate the monthly payment that we get for each of these options and see which one we choose. So we're going to go to the What If Analysis Scenario Manager, and from here we can add a new scenario. So our first one is going to be the one we have right now.

So that's the down payment, 5% interest rate, and 30-year term length. You can give it whichever name you want, but this is going to help us identify them. And our changing cells are going to be our inputs. So in this case, the down payment and the interest rate And if we press Control, we can select the term length as well. Press OK. And here is where we enter the values for each of the inputs. But as it is right now, it's kind of hard for me to differentiate between these inputs because H 13 doesn't really mean anything to me unless I actually go and look for it.

So, instead of writing them down like this, I'm going to cancel out of here, close this, and use the pro tip to give each of these cells meaningful names so we know which is which. In the scenario manager, I'm going to select the first one, the term length. And you'll notice in the top left corner, where we have the cell reference, that we have a name box where we can give it any name we want, in this case term underscore length. We'll do the same for the rest. so the down payment and interest rate So, when we return to DataScenario Manager and add the scenario, we have a 20% down payment, a 5% interest rate, and a repayment period of 30 years. By changing these cells, you'll see that now we know that this is a down payment, this is the interest rate, and this is the term length. So you'll also notice that it already took the values that already exist in these cells. So 20%, 5%, 30. So we're good to go.

And instead of clicking okay, I'm actually going to click add to go ahead and add the rest of the scenarios at the same time. So the next one is actually 20% down payment, 4% interest rate, and 15 years for the term length. So the changing cells will stay the same. Click okay, so the down payment is again 20%, the interest rate will be 4%, and the term length is 15 years.

We'll go ahead and add the third one. This is going to be a 10% downpayment with a 5% interest rate over 30 years. Press okay, so this is 10%, 5%, 30; add the last one, and this is going to be 10%, 4%, and 15 years. Press OK, so 10%, 4%, and 15%. Press okay, and now we'll see that we have all four scenarios, and it looks like I actually forgot to write in the years here. We can go back and edit this, right? Years. Press OK; everything looks good here.

And there we go, our four scenarios. So now let's see what happens when we press this show button. If we use this fourth scenario, you'll see that our down payment is now 10%, our interest rate is 4%, and our term length is 15 years. And we can see that our monthly payment has now changed to $2,656. Let's look at this one. So 1928 is less expensive.

So 10%, 5%, and 30%. Here, we get $2,361. And it seems like our original was the cheapest. Let's see. Yup. So with 20%, 5%, and 30 years, we get a monthly payment of $1,714. And in our case, we do want to keep our monthly payments as low as possible. So we're going to stick with our first scenario, which is the 20% down payment, 5% interest rate, and 30-year term length. But this was a good example of how we can use this tool to model different scenarios and combinations of inputs to evaluate their outputs.

29. Consolidate Data

Next up is the consolidate tool, which, to be perfectly honest, is one of the few tools that Excel has that I've never actually found use for outside of the purpose of this exam. Nevertheless, the Consolidate Tool summarises data from separate ranges into a single output range.

And there are two methods for data consolidation. The first is to consolidate by position, and this is used when the data in the source ranges contains the same labels in the same order. In other words, they are identical in structure. The second is to consolidate by category. And this is used when the labels in the source ranges are the same but in a different order. And in these cases, you need to tell Excel where the labels are: either the top row or the left column, and it will consolidate the data with the same labels. Important note: when consolidating by category, Excel automatically enters the labels in the output range.

However, you can enter them yourself if you want them in a particular order. as a pro tip. As well, you can select the Create Links to Source Data option to have the summary range automatically update when changes are made to the source ranges. Otherwise, you have to reconsolidate your data to have those updates take place. Just know that to use this option, the summary range needs to be in a separate worksheet. Now, let's look at some examples for each of these types. Let's say we received the quarterly sales for two of our offices and want to summarise them in a single table to consolidate my position. Well, we can use the Consolidate Tool, but before we do that, let's look at the structure of our tables. You'll see that they have the same labels in the top row and in the left column, and they also have the same order.

Therefore, we can consolidate by position, so we can select the range where our data will live. And we don't need to select any of the labels since they're all in the same place. So we launch the Consolidate Tool, specify the type of aggregation we'd like to perform, in this case, a sum, and add the references for our source ranges. And then, when we click OK, you'll see that Excel will return a correct summary of our information. For example, suppose we received sales for the same offices but by department, and each of these is sorted descending by sales.

So even though we have the same labels in the left column, they're in a different order. And for our summary table, we actually want them in alphabetical order, so we can type those in ourselves before consolidating. Now, when we select our data, we need to include the left column, as Excel needs this information to associate the sale for each department correctly. So, once again, we used thissum to launch the Consolidate Tool, and when adding our source range changes, we needed to include the left column.

We also need to tell Excel to use the labels in the left column by checking this box. And now, when we click OK, you'll see that it will return another correct summary of our information. Now let's head to Excel and do some consolidating of our own. Here in Excel, we're back in the Mortgage Calculator tab in our course project workbook, and what we want to do is calculate the average number of nights per month that we expect our place to be booked. Now, to do this, we want to fill out this calendar template with the summarised information from our 2019 Occupation Tab tab.

And as a refresher, this tab contains utilisation information for a similar place in New York City from the previous year. So the ones are going to be the dates when the place was booked, and the zeros are going to be the dates when the place wasn't booked. So before we start consolidating any of our data, let's actually look at the structure of our source and output ranges. So in the source ranges, it looks like the only labels are in the top row here, and they appear to be the same labels in the same order, which is the weekdays from Monday to Sunday on all of these.

And if we go to our template, you'll see that, again, we have the same labels in the same order, which means that we can consolidate by position. So to do that, we can select the range where our data is going to live, and we don't need to include the labels.

And then we can go to Data Consolidate, which is this small button right here. And in this window, you'll see that the first thing we see is the function. Now, this is a sum by default, but in this case, I want to see the average occupation for each calendar day in the month. So I'm actually going to select an average here. Next, we need to select these source ranges. So let's do that. Go to our 2019 Occupation tab. Let's get this out of the way a bit, and we can select January 1. Again, there is no need to include the labels. Press "Add" to go to February. Add March, add April, May This may take me a bit. I'm sorry.

Okay, so now we have all our source ranges, and since we're consolidating by position, there's no need to check any of these top row or left column boxes. So we can just click okay. And there we go. So this is our summarised data, and if we take a second to analyse it, we'll realise that these are utilisation rates. So since one is booked and zero is not booked, an average value of 0:75, for example, represents a utilisation of 75%. So to calculate our average nights per month, we can just get the average utilisation for an entire month and multiply this by the number of days in a month. and we can just use 35. Press Enter, and we get a result of 18 average nights per month that we expect our place to be booked. And there we go. Now, I do realise that this isn't the most accurate way to calculate this, but it is a great demo for the consolidate tool.

30. Goal Seek

The final data analysis tool included in the exam And my personal favourite is goal seek. Goal Seek allows you to find the result you want in a formula by changing the value of a given input cell. So it requires a single hard-coded cell and a single formula-based output cell. You can't test multiple inputs using GoldSeek; for that, you would need Solver.

But don't worry, because problem solving isn't covered in the exam. Now, let's look at an example. Consider that you work for a manufacturing company that produces a single product. You have a fixed cost of $250 per unit and a variable cost of $2. With that information, we can write the following formula to calculate our profits: So unit price minus unit cost multiplied by quantity produced (our variable) minus fixed cost.

So we have a hard-coated input that sells quantity and a formula-based output that sells profit. Therefore, if we wanted to know the amount of product we'd need to produce to obtain a profit of $1,000, we could use GoldSeek. In the goldsiek window, we need to set sales C-8, which is our profit formula, to a value of $1,000. By changing the value of cell C2, which is our quantity, Excel would then return the following: A quantity of 833 units would give us a profit of $1,000. We're looking for it again—my favourite data analysis tool by far. Now let's head over to Excel and use it ourselves. Here in Excel, we're back in the mortgage calculator tab of our course project workbook. And in the last lecture, we found that we expect our place to be booked an average of 18.6 nights per month.

So if we were to establish a price per night for our place, we could then calculate the profit per night, which is the price minus the AirBnB service fee, and our total monthly profits, which would consider the profit per night, the nights per month, and our monthly expenses. So let's establish a starting price. It could be $100. And now let's calculate the profit per night. So it's going to be equal to our price per night multiplied by one minus the Airbnb service percentage. And since the service varies depending on the price, we need to use an approximate match lookup. So let's use a VLOOKUP function.

Our lookup bath value is going to be the price, and the table array is the service fee table in our placestab, with the minimum price on our leftmost column. The column index number is going to be two, since the service fee is the second column over. And we don't need to specify a range lookup since an approximate match is the default. So we can close our VLOOKUP function, and we can close our second parentheses. And before I press Enter right now, we have a price of $100, which falls in this range. So our service fee should be 3%. Pressing enter, we get a profit per night of $97, which is accurate considering that 3% of 100 is $3, which is the service fee that's being subtracted here. So we're all set.

Now, our total profit would be equal to our profit per night multiplied by our nights per month, and then we would subtract our monthly expenses. Press Enter. And it looks like we can't price our place at $100 per night since we'd be losing money here. So how do we know what price will work? Well, first, it would be good to know our break-even point. So at what price per night do we need to obtain a total profit of $0? Well, we could spend all night guessing and moving our prices around. But before we do that, let's think about our situation. So we're only moving a single hard-coded value, which is our price, and we have a formula-based output, which is our total profit. As a result, we can use goalseek to help us determine our breakeven price. So let's do that. Let's go to data what-if analysis and golseq. And what we want is to set Cu 11, which is our total profit, to a value of $0. And we want to do that by changing our price per night.

So let's press okay. And there we go. So it looks like if we priced our place at $149, we would get a total profit of $0. So we know that now. But we do want to be making money here. In fact, our investors are expecting a monthly profit of $1,000. Well, let's use goal-seek again and find our price. So, once more, what if analysis goal seeking And now we want to set sales UEleven, which is our total profit, to 1000. And again, by changing the sale of our nightly rate. Press OK. And there we go. So we have our place, we have our price per night, which is $207, and we have our projected monthly profits. You.

31. Formula Auditing

To finish off this objective domain, we are going to go over Excel formula auditing tools. Formula auditing tools allow you to troubleshoot formulas by tracing references, performing calculations, and diagnosing errors. And the first of these is the Trace Priority Tool, which draws arrows from any sales that impact the selected value.

So in the example below, if we select the cash to close, sell, and trace its precedent, you'll see that arrows will be drawn to it from the sales that impact its result, in this case, the purchase price, the down payment, and the estimated closing costs. The Trace Dependence tool, on the other hand, draws arrows to any cells impacted by the selected value. So if we were to select the down payment, sell, and trace its dependence, we can see that arrows are drawn from it to the Salsa impacts.

In this case, cash to close and the loan amount The Show Formulas Tool temporarily displays all formulas within the worksheet, which is a quick way to find out which of the values you're working with are formula-based and which are hard-coded. Next is the error checking tool, which scans the sheet for errors and traces the source to present cells. And in the example below, we use the error-checking tool to trace the value errors we are receiving back to the down payment input cell. The Evaluate Formula Tool evaluates each component of a formula step by step, which is great for pinpointing where a formula might be breaking, especially if you have a very complex formula or a number of nested functions. Finally, we have the Watch window, which you can use to keep track of the value or formula in the cell in any workbook.

Now, let's head over to Excel and replicate these. Here in Excel, we're back in the mortgage calculator tab of our course project workbook. And even though we used the PMT function ourselves to calculate the monthly payments, the rest of the mortgage calculator was already filled out. And you may be interested in knowing how the different formulas in here interact with each other. So to do this, we can use Excel's formula-auditing tools from here in the Formulas tab. So let's start with the trace precedents and trace dependence options. Now, these may sound very complicated, but they're actually quite simple. So let's use this money to level the playing field. This, for us, represents an end result. And what I mean by this is that we no longer need to use this value as an input for any other formulas. This is a value that we want it to eventually land at. And to prove this, we can use the trace dependence option.

So let's click on that, and you'll see that we get this window saying that there are no formulas that refer to this active cell. So press okay, but there are, however, cells that impact the formula in this cell. And the way we've looked at this So far, I've entered edit mode by pressing the F2 key. And here we'll see the cells that are being referenced in this formula. So we have the purchase price, the down payment, and the estimated closing costs. But I'm actually going to press Escape to jump out, and instead we're going to use the Trace Priority option. So we'll click there, and you'll see that we're getting arrows from the same three cells in our Catch to Close field. So this is essentially doing the same exact thing. It's telling us that cash to close is a function of our purchase price, our downpayment, and our estimated closing costs. So we can go ahead and remove our arrows. And since we know the purchase price works as an input for our cash to close, we know that this sale does have dependents.

So we can use the trace dependencies option to trace these. So you'll see that the purchase price is impacting, I think, five different cells. We have the property tax, the loan amount, the closing costs, the price per square foot, and the one we already knew: the cash to close. The trace dependence option is also cool because Right now, we're only looking at the cells that are directly impacted by our purchase price. But we can click on it one more time, and you'll see that now arrows are being drawn to the cells that are impacted by the sales or impacted by a purchase price. So what I mean by this is that we know that our purchase price impacts the property tax, but then the property tax will impact our monthly expenses in the same way our purchase price is going to impact our loan amount, and that loan amount is going to impact our monthly payment. And if we unhide the columns, here is our term length for our fixed payment option.

So a really cool pro tip about the trace dependence option Now we can remove our arrows and move on to our show formulas. So this is pretty straightforward. By clicking it, you're simply showing the formulas in your worksheet, which in this case are right here. And we have some more over here to our right. So this may not seem very useful at first glance, but it's a great way to get an initial idea of where you're working with hard-coded values like these and where you're working with formula-based values. So we can click that to go back to normal. And now we have the error-checking tool. So if we use that right now, you'll see that everything is working smoothly and there are no errors in this worksheet.

But let's say that when we wrote our down payment, we made a finger mistake and pressed the dollar sign instead of the percentage sign. So now we're getting errors all over the place. We have an error in our loan amount, our monthly expenses, our monthly payment, and our total profit. So we want to know what happened here. And what we can do is use our error-checking tool. So click on that, and it will tell us a bunch of things. Number one, it's telling us that we have an error in cell U-11, which is the one that we had selected, and it contains this formula. And it's also giving us a description of that error, saying that a value used in the formula is of the wrong data type, which we know for a fact because we made the error on purpose. But if we didn't know that, what we could do is use this trace error function.

And if we select that, you'll see that it's tracing arrows, just like we did with the trace precedence and trace dependent options. And the cool thing about it is that it shows us exactly where our errors are happening. So we know that this is an error, and it was already an error because of this red arrow before it came here. So we had an error here, we had an error there, and we had an error here. But in the step prior to this, everything was still going well, which is telling us that this is where the mistake or error is coming from, which again is our down payment cell that has 20 dollars instead of just 20 percentage.

So we can exit this and return to edit to correct our error. So now we've got all blue arrows, which is perfect, and we can remove them since we took care of our error. The evaluate formula tool comes next. And this one is actually the one that I use most often and that I find most useful. So let's say again: we want to figure out how the formulas are working here. And one that I'm particularly interested in is the property tax, since I'm not really sure how it's calculated. So what we can do is use the Evaluate Formula Tool, and you'll see that it'll open up this window. So what we have to begin with is the same exact formula that lives in our formula bar. However, you'll notice that a section of that formula, in this case cell H-3, is highlighted. So, when we go to evaluate, only that portion of our valued formula window will be evaluated.

So far, three of them are worth $399,000 each. So that's where we end up. The next step is to divide that value by 1000. So we evaluate it, it does that, and we get 399. Next, we go to our h4, which when we evaluate has a value of eight, which is a tax rate. Next, we're going to multiply these two. So we get 3511.2. And the last step is to divide by twelve. So we click on that, and we get the final result of 293—which is our property tax. So we can put an end to that. And I'm actually going to use the Evaluate Formula tool one more time in our Cash to Close field to show you another cool feature that it has. So open that back up, and you'll see that we have a very simple formula here. So H is three times H plus 13 plus 16, so we can start to evaluate. Three dollars is our purchase price. So 399,000 divided by 13 is the down payment amount. So only 20% of that is evaluated, and H16 is not a hardcoded value.

So this is going to be our estimated closing costs. And what we can do is step into the cell and see the formula that lives within it. So it's kind of like peeling off a layer of our formula or going one level deeper. And here you'll see that we have H three times zero zero two. So we can calculate that the answer is 7980. And then when we step out, you'll see that that is how we got this value here. So then it simply adds them up, and we get our final result of 87,780. You can close this window. And the last tool we have is the watch window. And how this works is that we can add cells to our watch window and keep track of them regardless of which workbook we're in. So let's go ahead and add one. And in this case, I want to keep track of our total profit. Add to that, I want to keep track of our prices and our monthly expenses.

So these are kind of like our key metrics here. Now, you'll notice that we have a lot of information about them, so we know what book they're in, what flock they're in, their sale, their value, and their formula. But one thing that we don't have for them is a name. So if we were in another workbook and we looked at this, it would be hard to tell what this value of 1000 represents or the rest, because cells U 11, S 11, and J 7 don't really have any meaning to me. So what we're going to do is close this, and we're going to give them meaningful names the same way we did with the down payment, our interest rate, and our term length. So I'll choose one for the total profit that we can call profit. The price can be a price, and the monthly expenses can be expenses. So now, when we're in another workbook and we open up our watch window, we can immediately know that our profit right now is $1,000. Our price is $207, and our expenses total up to $2,696. So again, this is a really cool option to keep track of key metrics in your data, and the Wrap app syncs up. So those are Excel's formula auditing tools, which are always great to have in your back pocket when you're working with it.

Microsoft Excel Expert MO-201 practice test questions and answers, training course, study guide are uploaded in ETE Files format by real users. Study and Pass MO-201 Microsoft Excel Expert (Excel and Excel 2019) certification exam dumps & practice test questions and answers are to help students.