Bootcamp 3.0 Rapidshare

Data Tables Monte Carlo Simulations in Excel A Comprehensive Guide Chandoo. Posted on May 6th, 2. All Time Hits, Analytics, Charts and Graphs, Excel Howtos, Featured, Huis, Learn Excel, Posts by Hui, simulation 1. This is a Guest Post by Hui, an Excel Ninja and One of the Moderators of our Forums. Please note that this post is unusually large by Chandoo. If anybody asks me what is the best function in excel I am drawn between Sumproduct and Data Tables, Both make handling large amounts of data a breeze, the only thing missing is the Spandex Pants and Red Cape How often have you thought of or been asked Id like to know what our profit would be for a number of values of an input variable or Can I have a graph of Profit vs Cost vs This post is going to detail the use of the Data Table function within Excel, which can help you answer those questions and then so so much more. Update 2 If you are looking for a guide to Dual Boot Windows Vista and Mac OSX Leopard, please follow this guide Hack Attack Dual Boot Leopard and Windows Vista. Think more Creatively. INTRODUCTIONHow often have you thought Id like to know what our profit would be for a 1. This post is going to detail the use of the Data Table function within Excel, which can help you answer that question. The Data Table function is a function that allows a table of what if questions to be posed and answered simply, and is useful in simple what if questions, sensitivity analysis, variance analysis and even Monte Carlo Stochastic analysis of real life model within Excel. The Data Table function should not be confused with the Insert Table function. DATA TABLE BASICSThe Data Table function is hidden away in different locations within different versions of Excel but apart from the menu location the functionality is the same throughout. Zpgyq.jpg]];var lpix_1=pix_1.length;var p1_0= [[781' alt='Bootcamp 3.0 Rapidshare' title='Bootcamp 3.0 Rapidshare' />Press release distribution helps you create buzz, increase online visibility and drive website traffic. No more missed important software updates UpdateStar 11 lets you stay up to date and secure with the software on your computer. Where is the Data Table Function. Excel 2. 00. 71. In Excel 2. Data Tab, What If Analysis panel and select Data Table. Stepper Motor Controller Using L297 And L298 here. Excel 9. 7 0. 3In Excel up to 2. Data Menu and select TableBoth Excel 9. KC.Software.SUMo.Pro.4.3.0.302.Multilingual..Por.jpg' alt='Bootcamp 3.0 Rapidshare' title='Bootcamp 3.0 Rapidshare' />Data Table dialog box. Yes Blue Sky Mine Co. For demonstration of the Data Table function I am going to use a simple profit model of a Gold Mine, The Blue Sky Mine Co. This is a fictitious mine but provides a simple model which we can use the data Table function to analyse. It consists of 6 input variables and a simple cost and revenue model to produce a profit. In our Blue Sky Gold Mine Co model, we can see that if we mine and treat 1,0. A 5. 4. 52. M profit. But what if the inputs change WAY DATA TABLESLets make a 1 Way Table with our Blue Sky Gold Mine Co example. This is shown in the attached Excel Workbook on the 1 Way Tab or 1 Way Example. In our Blue Sky Gold Mine profit calculation example, we can see that if we mine and treat 1,0. A 5. 4. 52. M profit. But what if the grade is more or less than that value of 1. After all it is only a geological estimate. This is what the Data Table function is made for. Next to the model add a couple of columns as shown in blue. Note Throughout this post you will see the use of 1. E6 in formulas which is simpler to write than 1,0. The first column is a list of values that will be applied to each iteration of the Column Input Cell. The Top Cell of the second Column contains a formula which will retrieve the answer you want to watch, in this case Profit. It will be displayed as M. Now select the entire Blue Area and Select Data Table. This is the Data Table input screen. The trickyconfusing part here is that in our example we are changing the input value to our Gold Mine Profit model using a Column of Numbers, so enter C6 in the Column Input Cell, Leave the Row Input Cell blank. Click Ok. You can now see a Table of Profit Values for each Grade Value. The variance in the Profit can easily be graphed against the Gold Grade and we can now see that if the Gold Grade is below about 1. Au we will not make a profit and conversely if it is above 2. Au we will make a large profit. Before we move onto 2 Way Data Tables it is worth exploring small variations on One Way Tables. What if my Data is in Rows Had our input data been arranged horizontally in Rows, we could have used a Row Input Cell to process the data. What if I want to vary the inputs by a certain Percentage Another Scenario is often where you want to vary an input by a Fixed Percentage. This is easily done using Data Tables. Setup the input cells with the percentage variations you want to examine, noting that the values dont have to be evenly spread. Setup a Temporary Input Cell, This will hold the Percentage Variance briefly whilst calculations are happening. Set a default value of 0 zeroChange your Main Input Cell, Gold Grade in our case, to Multiply the fixed answer by 1 the temp Input Cell. Run the Data Table with a Column Input Cell, which will refer to the Temp Input Cell. WAY DATA TABLESSo the Boss comes in and asks, what Happens if the Gold Grade changes as well as the AU Exchange Rate You guessed it, Two Way tables to the rescue. This is shown in the attached Excel Workbook on the 2 Way Tab or 2 Way Example. Two way data Tables work the same as One Way Data Tables except that you can vary 2 parameters at once. Glacier 2 Engine. With Two Way Data Tables you need to setup a Column of data for one Input and a Row of data for the second Input. The answer is returned at the intersection of the Row and Column. Here we have setup a Column of Gold Grades ranging from 1. Softly Sings The Donkey Poem. Au and a Row of Exchange rates varying from 0. AUNote at the intersection of the Row and Column there is a Reference to the variable you want to monitor in this case profit. You can now see the variance in Profit for variations in Gold Grade and Exchange Rate. What about varying by Percentages Once again we can re arrange the input variables to examine percentage changes in the inputs via a Temporary Input Cell. MONITORING MULTIPLE VARIABLESSo you have a complex model and want to monitor a number of input and output variables at once. No problems, Data Tables to the rescue. In this example we are varying one input variable but monitoring 3 Output variables, 2 input variables and then doing a calculation all as part of the Data Table. This is shown in the attached Excel Workbook on the Monitor Multi variables Tab or Monitor Multi Variables Example. The first 3 columns, Total Cost, Revenue and Profit are output variables even though Total Cost doesnt change, we can still monitor it to make sure our model is working correctly. The next 2 columns, Gold Grade and Gold Price are input variables even though only Gold Grade is being varied. The last column Cost per Oz is not calculated as part of the model ok sometimes we forget dont we, but it can be calculated on the fly as part of the Data Table. The result is MULTIWAY DATA TABLESBut I hear you thinking, If Data Tables are so good why can I only Change 2 variables at Once I want to change more No Problems. Data Tables in fact allow you to Change any Number of input variables at once and monitor any number of input and output variables. It does however require a slight of hand. This is shown in the attached Excel Workbook on the Multi Way Tables Tab or Multiway Table Example. First things first,Setup a table of what scenarios you want to examine Setup the Data Table area to monitor Inputs, Outputs and Calculated Fields. Note that the Input Data Column will be used to select the Scenario No. Also note that we have setup F2 to retrieve the Scenarios Name. And in H6 we will put the Scenario name into the Data Table, who said Data Tables were only for Numbers Next Link the Model to the scenario. And run the Data Table.