You can now see the variance in profit for variations

This preview shows page 30 - 33 out of 38 pages.

You can now see the variance in Profit for variations in Gold Grade and Exchange Rate.What abo ut v ary ing by Perc entages?Once again we can re-arrange the input variables to examine percentage changes in the inputs via aTemporary Input Cell.Monitoring Multiple VariablesSo you have a complex model and want to monitor a number of input and output variables at once. Noproblems, Data Tables to the rescue.In this example we are varying one input variable but monitoring 3 Output variables, 2 input variablesand 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.The first 3 columns, Total Cost, Revenue and Profit are output variables even though Total Cost doesn’tchange, we can still monitor it to make sure our model is working correctlyThe next 2 columns, Gold Grade and Gold Price are input variables even though only Gold Grade isbeing varied.The last column Cost per Oz is not calculated as part of the model (ok sometimes we forget don’t we),but it can be calculated on the fly as part of the Data Table.The result is:
10/24/12lo Simulations in Excel π AComprehensive Guide ρ chandoo.org ρ Readability31/38w w w .readability .com /articles/rrqky el6Multiway Data TablesBut I hear you thinking, “If Data Tables are so good why can I only Change 2 variables atOnce? I want to change more!“.No ProblemsData Tables in fact allow you to Change any Number of input variables at once and monitor anynumber 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.First things first,Setup a table of what scenarios you want to examine:Setup the Data Table area to monitor Inputs, Outputs and Calculated FieldsNote that the Input Data Column will be used to select the Scenario No.Also note that we have setup F2 to lookup the Scenarios Name.And in H6 we will put the Scenario name into the Data Table, who said data Tables were only forNumbers!Next Link the Model to the scenarioAnd run the data TableNote how the Description Column is populated with the Scenario’s Name (Text values)So now when your boss asks you what effect the price of … has on the budget, you know where to turn.Monte Carlo Simulations in ExcelMonte Carlo simulation (or analysis) as its name suggests puts an element of gambling into thescenarios, or more correctly allows you to measure the effect of variability on input parameters.
10/24/12lo Simulations in Excel π AComprehensive Guide ρ chandoo.org ρ Readability32/38This is done by running scenarios against your model hundreds or thousands of times and changing theinputs each time and then measuring the effects at the end of the runs.

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture