Manipulating your model

After you add the summary formulas and controls to your model, youcan manipulate its assumptions to see how different settings affect the results.In this movie I will give you a quick tourof the workbook that we've created throughout this chapter, sothat you can see how to manipulate the model, andperhaps think about the ways that you create your own.I will use the manipulate workbook, which you can findin 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 onthe quantity, the cost of the items and also the discount that's been applied.

Then we have check boxes which allows to indicatewhether 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 doanything about this quantity. Because the formula in E5sets 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 slideruntil it reads 24%. I'mclicking 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'llpay 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 easyto go from one purchasing scenario to another.I am sure that you will find many differentways to use these techniques in your own workbooks.

So you can analyze your own operations.

Resume Transcript Auto-Scroll

Author

Released

1/7/2014

Make better decisions for your business by taking a deeper dive into your data with Microsoft Excel's analysis tools. In this advanced course, Curt Frye shows how to analyze hypothetical business cases using formulas and variable data. He'll show you how to build data tables, define alternative data sets with scenarios, find target values with Goal Seek, and analyze complex problems with Solver. The last two chapters show you how to create a configurable model with sliders and check boxes and how to run a Monte Carlo simulation.