Sensitivity Analysis

FP and a guy here last day in Toronto. And earlier this week I shared what you could do with dynamic arrays. And many of you asked an instructional video would be helpful. So today I'm going to walk through the sensitivity table I set up and how I did that. So right, as you can see here, down here we have a sensitivity table. And if I adjust any of my values up here, the table automatically adjusts. So let's just say only going to sell 5,000 units. You can see all the numbers automatically adjust. Let's say my worst case price, which is where I start. Let's say I think that's going to be $8. So that's where this table starts. Let's say my worst case cogs is going to be $5. And I want to move this in increments of 25 cents. Right, I can do all that. So the first thing I did is I set up these dropdowns for each of my assumptions. I had units sold, worst case, worst case cogs, price increment, how many increments I want. So here is how much it moves. So it goes from 8 to 8:25 to 8:50 to 8:75. That's 25 cents each. The next is how many I want. So that's the number of rows I have. There's 30 rows here. I had a base case price and base case cogs. That's just to help me think, okay, you know, on this table, where would I be under my kind of normal assumptions? And if that doesn't show up, I'd adjust. So it's saying, okay, at 950 and 650 I'd be here. That would also help if I wanted to color code down here on the gross margin, I could color code it based on looking those up using conditional formatting and say, okay, what would be my margin? So this shows the margin up here shows the actual gross profit. So I'm going to talk through how I set this up. So first I built all my assumptions. That's pretty straightforward. In this case, all I did is I used data dropdown. So I went here, I went to data, I went to drop down to data validation and I created lists. In this case, I entered each of the lists right in the box. Normally I would have created a list sheet and laid them all out. But I was just doing this as kind of a quick example to show what you can do. So I didn't build a formula list here, but you could reference this to a formula. So I could have said, hey, equals and Maybe it equals this range right here. And I have all my numbers in that range. So there's different ways you could do that. So the first thing I did is I set up all my dropdowns. Where this is unique in the sensitivity table is I'm using dynamic arrays. The whole idea behind dynamic arrays is they can automatically adjust and spill. So instead of things operating on a single cell, it spills into multiple cells. So first I'll show you a formula here. This is the sequence, which is one of the new formulas in 365. And what you'll notice is there's a bunch of box. You can see the little blue line here goes around the price. So what happens is if I adjust this to 10, the formula automatically adjusts to 10. Same here with COGS. If I adjust COGS to 5, my table adjusts to 5. And the rest of it is conditional formatting. So all I've done under conditional formatting to get those colors is I did basically a heat map. So I came to color scales and I said, hey, you know, build, build the color scale for this range. So if I come down here under manage rules and I look for this worksheet, you can see graded color scale, apply it to the entire range, as large as it can be. So whatever my dropdowns are, if 30 is the most rows, and that goes over to 30, if 20 is the most columns, and it automatically resizes when there's data. So now I'm going to walk you through the formula. There's only three formulas that are being used here. It's really simple once you understand the syntax of the formulas in dynamic arrays. So again, you're going to see here the blue around it. The formula lives in the first cell. The rest are grayed out. So all I've done is I've used a sequence formula. A sequence formula is what it says. It's a sequence. You can create a dynamic sequence. There are four parameters. You have your rows, your columns, your start and your step. So here I want it to be one row. So I put the number one. The columns are going to be the number of increments I want. So if I want 10 prices, that's 10 columns, then my starting value is going to be my worst case. So whatever I put in my worst case is always where it's going to start. Then I'm going to increase it by my price increments, which is B4. So that's how I've made it dynamic. So you have again, four items. Row, column, start, step, Three of the four dynamic I did hard code the one. Really I should make that dynamic as well. Try to avoid hard coding, make it very, very clear. But for simplicity did that right. So what we'll see is if I come in, I'm going to escape here. So I don't formula. If I come and change this, let's say I want to start at $9. The formula, nothing else changed it dynamically is $9. Now let's say I want 15 price increments. So I change this to 15. Now I have 15 rows moving at 25 cents each. Say well, I really probably should look at 10 cents. That's a better increment. So now we can see I go from 9 to 1040 and everything adjusts. So that's how I set up the price. Next formula we have to set up is the cogs. The cogs works the exact same as price except for my increments number is in my rows instead of my columns. Then I have my work. My start is my worst case cogs and my step. How much I want to increase it by is my cogs increment. So right, I can do the same thing here. I could change my cogs increment to 10 and that goes down 10. So that only dynamically does price and cogs. That does nothing here in the middle without one more formula. So the next formula is using two spilled arrays to create it. So what we're doing here, Sorry, my camera. Forgive me, I'm in a hotel today. So you're getting a little bit of issues as I'm dealing with. But we got it. Alright, so now what you're going to see here is we need to do this formula. And notice this formula is just referencing price cogs and units sold. So B13 pound is saying take this entire range, whatever the dynamic array is, take it and minus it by cogs. So here it's saying, hey, I'm going to take 9 minus 5 and then 9 minus 5 15, 9 minus 530, 545. Then here it's 910 minus 5. And it's doing that for every single one of these. It's taking them, subtracting them as you can see in the formula and then it's timing it by the units sold. So it's basically taking contribution margin and it's spilling. What spilling is is that pound means the formula spills so it automatically adjusts. Right. The other way I could have done this If I said B13 minus this. Right, we'll go ahead and just say you know, hey, put it in parentheses. Then we'll times it by the units sold. Lock that. That's the old way we would have done that. And then what I would have needed to do, copy it over, you know, copy it down, whatever. And I would have needed to lock this here, the 13 and I would have not needed to lock A, so it doesn't move, right. And then go control R and copy it over, control down. Right. And I've created it, but every time I make a change, it's no longer dynamic. Right. If I Change this to 810, you can see the 810 worked and this adjusted because it's linked to that. But as soon as I increase the price increments to 11, right, you can see all those later ones, it's like, well, what do I do? It's no longer dynamic. So it's, hey, there's formulas there, but it doesn't have a formula there to times it. So that's the difference between the old way Excel worked and the new way with dynamic arrays. So I'm going to go back and reset that to dynamic arrays. And there's the video for you of dynamic arrays. I hope you enjoyed this and let me know if you have any questions.