Sometimes in building out our business scenarios the answer to the question what's the best decision to make is, well it depends. So, for example, how many units required for manufacturing a set of speakers should we order this month? Well it depends on the demand. It depends on the cost. It depends on possible price breaks we might get if we order in larger quantities. In this lecture, we'll take a look at some of the built in functions that Excel provides to handle that type of circumstance. Excel provides conditional expressions which we can incorporate within the logic of your model and the formulas in your spreadsheet. Let's turn back to the computer to understand some of the applications of conditional logic in spreadsheet models. Looking again at the prices for the components of the speaker, let's assume that the prices that are shown on the spreadsheet for cost of manufacturing a speaker are based on a production run of 50 units. However, the manufacturer is willing to provide discounts for larger production runs. Let me show you an example of that. In this spreadsheet, Amy made several changes. In cell B4, for example, she stored the number of units being ordered at this time. In cell D4, she's noted the discount available for orders that are over 50. The volume discount is shown in row 8. This number is calculated with an if statement And this statement takes three inputs, the first is an equation that will result in either a true or false value. In this case, is it the case that we're ordering more than 50 units? Yes or no? The second input is a formula to be used in this cell in the event that the first equation yields a true answer. So if we are ordering over 50 units then the formula for this cell is B7 times D4. In other words, 20% of the standard price applied as a discount. If its not true then last input 0 is applied for this cell. Meaning there will be a 0 dollar discount. Next let me show you a slighty more complicated use of the if function In this case, we will use a nested if statement to show three price points. One if an order is less than 50, one if the order is greater than 100, and a third if it's between 50 and 100. So to read the logic of this if statement, the argument is if the amount of the order is less than 51, there is no discount. If the order is larger than 50, another if statement comes into play. The second check sees if the order is greater than 100. If it's true that the order is greater than 100, it applies then the discount rate in cell E4, which is 30%, times the standard cost per component. If that isn't true, and the value is less than 100 but greater than 50, that middle 20% discount applies, using the formula B7 times B4. So to summarize, in this exercise we used the sum product function to calculate the product of two ranges or a raise. We used average, min, max, and standard deviation to explore variation in a series of numbers. And we used the if statement to calculated stepped discount rates.