Join Curt Frye for an in-depth discussion in this video Manipulating your model, part of Learning Excel What-If Analysis.
After you add the summary formulas and controls to your model, you can manipulate its assumptions to see how different settings affect the results. In this movie I will give you a quick tour of the workbook that we've created throughout this chapter, so that you can see how to manipulate the model, and perhaps think about the ways that you create your own. I will use the manipulate workbook, which you can find in the chapter three folder of your exercise files archive. This worksheet contains several different elements. At the top, you'll see that we have totals that are calculated based on the quantity, the cost of the items and also the discount that's been applied.
Then we have check boxes which allows to indicate whether the item should be included in the order. So we have servers, desktops, notebooks, and tablets. In the financial terms section, there's the discount, the years, and the interest. And finally, there's the monthly payment, if it's financed, the base price, which would be the cash price if you paid all at once. And the total cost which reflects the sum of the monthly payments. So let's just make a few changes. So let's say that I don't want to buy any desktops at all this time. Instead I want to put everyone on tablets.
So I will clear the include desktops item so it's out. I don't have to do anything about this quantity. Because the formula in E5 sets itself to 0 because this check box is clear. And I'll add the 75 tablets here, so we'll go from 200 to 275, and press Tab. Doing so changes the total. I can also apply different discounts. Let's say that I negotiate and I get a 24% discount.
I can slide the slider until it reads 24%. I'm clicking the left arrow here. Great, so now our discount is at 24%. And if I want I can also enter in new values through the number of years. So lets say that because of the discount, I'll pay it off over five years instead of four. So I'll just change that value to five, press Enter. And say the interest rate is now 6.5% so I will just type that in and press tab. So that's all there is to it. Once you create this model it's extremely easy to go from one purchasing scenario to another. I am sure that you will find many different ways to use these techniques in your own workbooks.
So you can analyze your own operations.
- Explain how to create a scenario in Excel.
- Identify the two input values that should be used to create a two variable data table.
- Recall what the Excel feature Goal Seek is used for.
- Recognize the three things that must be separated from one another to make reading a worksheet with multiple units easier.
- Describe the tools that Excel provides to manipulate data.
- Break down the steps required to recalculate a worksheet's formulas.