WIXLIB

One-Minute SpotlightWHAT IS RISK?Uncertainty about a situation can often indicate risk, which is the possibility of loss, damage, orany other undesirable event. Most people desire low risk, which would translate to a highprobability of success, profit, or some form of gain.Risk is the possibility of loss, damage, or any other undesirable event.For example, if sales for next month are above a certain amount (a desirable event), then orderswill reduce the inventory, and there will be a delay in shipping orders (an undesirable event). If ashipping delay means losing orders, then that possibility presents a risk.Thus, there are two points to keep in mind when analyzing risk:1. Where is the risk?2. How significant is the riskAlmost any change, good or bad, poses some risk. Your own analysis will usually reveal numerouspotential risk areas: overtime costs, inventory shortages, future sales, geological survey results,personnel fluctuations, unpredictable demand, changing labor costs, government approvals,potential mergers, pending legislation.Once the risks have been identified, a model can help you quantify the risks. Quantifying riskmeans putting a price on risk, to help you decide whether a risk is worth taking. For example, ifthere is a 25% chance of running over schedule, costing you a 100 out of your own pocket thatmight be a risk you are willing to take. But if you have a 5% chance of running over schedule,knowing that there is a 10,000 penalty, you might be less willing to take that risk.WHAT IS A MODEL?Crystal Ball works with spreadsheet models, specifically Microsoft Excel spreadsheet models.Your spreadsheet might already be a model, depending on what type of information you put inyour spreadsheet and how you use it.Data vs. analysisIf you only use spreadsheets to hold data -- sales data, inventory data, account data, etc., then youdon't have a model. Even if you have formulas that total or subtotal the data, you might not have amodel. For analyzing data, you can use a time-series program.A model is a spreadsheet that has taken the leap from beinga data organizer to an analysis tool.Copyright 2017, Oracle Corporation and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may betrademarks of their respective owners. 07003600

Risk Analysis, continuedA model represents a process with combinations of data, formulas, and functions. As you add cellsthat help you better understand and analyze your data, your data spreadsheet becomes aspreadsheet model.TRADITIONAL SPREADSHEET RISK ANALYSISIdentifying risks in your spreadsheetSo now you realize that you already have a model, or you create your first model. You might noticethat your model has some values in it that you are unsure of. Perhaps you don’t have the actualdata yet (this month’s sales figures) or the value varies unpredictably (individual item cost).For each component, or variable, of the model (e.g., costs, rates, demands), you can ask yourself,"How sure am I of this value? Will it vary? Is this a best estimate or a known fact?" Since youconstructed the model, you will probably be quick to identify which variables these are. This lackof knowledge about particular values, or the knowledge that some values may always varycontribute to the model's uncertainty, helps you to identify your risks.The traditional modeling landscapeTraditionally, spreadsheet analysis tried to capture this uncertainty in one of three ways: Pointestimates, Range estimates, and What-if scenarios. Point estimates are when you use what you think are the most likely values (technicallyreferred to as the mode) for the uncertain variables. These estimates are the easiest, butcan return very misleading results.For example, try crossing a river with an average depth of three feet. Or, if it takes you anaverage of 25 minutes to get to the airport, leave 25 minutes before your flight takes off.You will miss your plane 50% of the time. Range estimates typically calculate three scenarios: the best case, the worst case, and themost likely case. These types of estimates can show you the range of outcomes, but notthe probability of any of these outcomes. What-if scenarios are usually based on the range estimates, and calculate as manyscenarios as you can think of. What is the worst case? What if sales are best case butexpenses are the worst case? What if sales are average, but expenses are the best case?What if sales are average, expenses are average, but sales for the next month are flat? Asyou can see, this form of analysis is extremely time consuming, and results in lots of data,but still doesn’t give you the probability of achieving different outcomes.2

Risk Analysis, continuedSpreadsheet Risk AnalysisYou can perform a risk analysis in several ways, but one method involves building a spreadsheetmodel. A good spreadsheet model can be very helpful in identifying where your risk might be,since cells with formulas and cell references identify causal relationships among variables.One of the drawbacks of conventional spreadsheet models, however, is that you can only enterone value in a cell at a time. Why would you want to put more than one value in a cell, you ask?Remember those uncertain values that you could represent either with point estimates, rangeestimates, or what-if scenarios? A spreadsheet will not allow you to enter a range or multiplevalues for a cell, only one value at a time. So calculating the range requires you to replace theuncertain value several times to see what effect the minimum, most likely, and maximum valueshave.Calculating more realistic "what-if" scenarios is the same, except it requires you to change yourspreadsheet even more. And don’t forget to keep track of all the results somewhere, or you willhave to repeat the scenario!3

Risk Analysis, continuedThis is where Crystal Ball comes in.Crystal Ball helps you define those uncertain variables in a whole new way: by defining the cellwith a range or a set of values. So you can define your business phone bill for future months as anyvalue between 2500 and 3750, instead of using a single point estimate of 3000. It then usesthe defined range in a simulation. In addition, Crystal Ball keeps track of the results of eachscenario for you.WHAT IS MONTE CARLO SIMULATION?What do we mean by "simulation?"When we use the word simulation, we refer to any analytical method meant to imitate a real-lifesystem, especially when other analyses are too mathematically complex or too difficult toreproduce.Without the aid of simulation, a spreadsheet model will only reveal a single outcome, generallythe most likely or average scenario. Spreadsheet risk analysis uses both a spreadsheet model andsimulation to automatically analyze the effect of varying inputs on outputs of the modeled system.One type of spreadsheet simulation is Monte Carlo simulation,which randomly generates values for uncertain variables over andover to simulate a model.How did Monte Carlo simulation get its name?Monte Carlo simulation was named for Monte Carlo, Monaco, where the primary attractions arecasinos containing games of chance. Games of chance such as roulette wheels, dice, and slotmachines, exhibit random behavior.The random behavior in games of chance is similar to how Monte Carlo simulation selects variablevalues at random to simulate a model. When you roll a die, you know that either a 1, 2, 3, 4, 5, or 6will come up, but you don't know which for any particular roll. It's the same with the variables thathave a known range of values but an uncertain value for any particular time or event (e.g. interestrates, staffing needs, stock prices, inventory, and phone calls per minute).What do you do with uncertain variables in your spreadsheet?For each uncertain variable (one that has a range of possible values), you define the possiblevalues with a probability distribution. The type of distribution you select is based on the conditionssurrounding that variable. Distribution types include:4

Risk Analysis, continuedTo add this sort of function to an Excel spreadsheet, you would need to know the equation thatrepresents this distribution. With Crystal Ball, these equations are automatically calculated foryou. Crystal Ball can even fit a distribution to any historical data that you might have.What happens during a simulation?A simulation calculates multiple scenarios of a model by repeatedly sampling values from theprobability distributions for the uncertain variables and using those values for the cell. Crystal Ballsimulations can consist of as many trials (or scenarios) as you want - hundreds or even thousands in just a few seconds.During a single trial, Crystal Ball randomly selects a value from the defined possibilities (the rangeand shape of the distribution) for each uncertain variable and then recalculates the spreadsheet.HOW DO YOU ANALYZE THE RESULTS OF A SIMULATION?For every spreadsheet model, you have a set of important outputs, such as totals, net profits, orgross expenses, which you want to simulate and analyze. Crystal Ball lets you define those cells asforecasts.A forecast is a formula or output cell that you want to simulate and analyze.You can define as many forecasts as you need, and when you run a Monte Carlo simulation withCrystal Ball, Crystal Ball remembers the values for each forecast for each trial.During the simulation, you can watch a histogram of the results, referred to as a Frequency Chart,develop for each forecast. While the simulation runs, you can see how the forecasts stabilizetoward a smooth frequency distribution. After hundreds or thousands of trials, you can view thestatistics of the results (such as the mean forecast value) and the certainty of any outcome. Theexample below is a forecast for Total Remediation Cost.5