:: User Conference Americas
November 13-14, Miami
:: Live Web Training
Risk and Decision Assessment Training using @RISK, Part I
Risk and Decision Assessment Training using @RISK, Part II
I love @RISK’s unlimited modeling capacity. However, Excel’s current column limits are impacting the correlation matrix I want to build in @RISK. How can I set up a correlation matrix that exceeds the 256-column limit in Excel?
Rebuilding the structure of the matrix allows you to use a larger correlation matrix with @RISK. Below is a description of how to rebuild and “stack” the matrix.
:: Utility-to-Utility Transactions
Deregulation has sped up the market environment for utilities and freed them in the ways they can buy not only power from other utilities, but power plants and other utility companies. As it does in many other industries, @RISK guides utility companies in their dealings with other utilities. Their buy/sell decisions involve variables with which many @RISK users are familiar—value drivers, return on capital, VARs of target companies—and they use their models to be poised for changes in strategy. “We’re not usually targeting a specific number,” says an analyst from Duke Energy, “but we are looking for results we can be 90 percent sure of.”
This kind of confidence is important, an analyst from NIPSCO points out, to achieving any utility-to-utility transaction. “The great thing about the Palisade tools is that they take the emotion out of the process of making tough decisions. They allow top management to see right away whether or not they can accept the risk in a particular course of action—and why.”
:: The Role of Risk Management
Risk management is embedded within the core principles of the Environment Agency. Careful management of financial risks can also assist in delivering more flood risk management schemes to protect vulnerable communities. All suppliers to the Environment Agency must therefore adhere to a proactive level of risk management. Furthermore, if suppliers meet the agreed terms of the contract they receive a pre-agreed share of the project budget. If however they do not perform as well as expected, their revenue is reduced.
:: @RISK for Portfolio Optimization and Budgetary Compliance
Halcrow has used @RISK for years, and for Environment Agency projects, Halcrow employs @RISK in the risk quantitative stage to set risk budgets at the start of each phase of the project commission. Starting with the ‘sunny day estimate’ of the project risk, it identifies, assesses, and quantifies every potential threat that could affect (i.e. increase) this base figure.
For example, severe weather could lead to a delay in the collection of survey data, with consequential effects for related work. Or, poorly articulated project outputs could result in tasks with weak focus that lead to inefficient resourcing and the need for reworking. Other hazards that potentially affect projects include increases in material costs, changes to the team, equipment failure, and more.
Using @RISK for ongoing risk quantification on individual projects allows Halcrow to monitor progress against original budget incentive thresholds and potentially allows decision makers to make strategic decisions on allocating funds across the whole portfolio of UK flood defense projects.
:: @RISK Shows “Most Effective Use” of Public Funds
Tim Wells, project manager at Halcrow, comments: “Halcrow’s line of business makes robust risk analysis an essential part of our daily lives, and the Environment Agency’s core principles reinforce this way of working. Palisade and @RISK provide us with a flexible product that is easy to use, as a result of which we are able to maximize our own revenues on projects. In addition they ensure that the Environment Agency is accountable to the UK public by putting its funds to the most effective use to guard against flooding.”
Excel Developer Kit for @RISK
Included with every copy of @RISK Professional and Industrial for Excel is the Excel Developer Kit (XDK). The XDK allows you to fully customize your @RISK for Excel experience using Excel's built-in VBA language (or any OLE-compliant programming language), giving you the flexibility to automate simulations, create tailored reports, or even design your own interfaces.
:: Complete Library for Unlimited Flexibility
A complete library of @RISK macro and function commands is at your disposal. The @RISK simulation engine, all 38 @RISK probability distribution functions, @RISK graph types, reporting functions, statistics functions, target analysis, sensitivity graphs and reports, correlations, distribution fitting algorithms, and more are available. Stress Testing, @RISK Goal Seek, and advanced sensitivity analysis are included as well. Every aspect of @RISK for Excel is at your command.
Automate common tasks to avoid time-consuming repetition. Run macros before, during, or after simulation runs. By writing your own custom applications using @RISK for Excel, you will enjoy truly unlimited analytical power.
:: Get up to Speed Fast
Documentation and example files are included to demonstrate how to use the most common @RISK commands. In addition, technical support is included with your maintenance plan for basic XDK questions. Palisade consulting services are also available to help you design and write applications from scratch.
:: You May Already Have It
If you own @RISK 4.5 for Excel Professional or higher, you already have the Excel Developer Kit. Many companies have used this powerful toolkit for their custom needs. Get started today.
:: @RISK Developer Kit Outside Excel
All the functionality of @RISK for Excel can also be added to applications outside the spreadsheet using the full @RISK Developer Kit (RDK). The @RISK Developer Kit enables you to write applications using .NET or any Windows programming language, and includes ActiveX and DLL interfaces. Bring the power of Monte Carlo simulation to stand-alone or Web-based applications. Available separately from @RISK for Excel, the RDK has been deployed by thousands of users for a range of applications.
Author David Apgar to Deliver
Keynote at Palisade User Conference continued from above
“We are very excited to have Mr. Apgar at the Palisade User Conference,” says Palisade President Sam McLafferty. “His innovative research into risk is what our event is all about – new and effective ways to deal with the uncertainty we all face every day, regardless of industry.”
:: A New Way to Think of Risk
Contrary to conventional wisdom, Apgar shows that operating, business, and strategy risks are not random but in fact learnable. As a consequence, businesspeople must develop their “risk intelligence”—how fast they could learn about the risks of the projects they take up. The talk builds upon Apgar’s new book, published in August by Harvard Business School Press.
Simply put, learnable risks must be approached entirely differently from random ones. The traditional approach of treating projects like stocks—that is, subject to random risks—and diversifying project portfolios accordingly is dangerous because it leads managers to take risks they have no natural advantage in assessing. Unlike stock portfolios, lists of projects where managers have low risk intelligence across the board are likely to fail.
:: Broad Experience
Apgar is a managing director of the Corporate Executive Board, with research management responsibility for corporate treasurers and controllers. He joined the company in 1998 from McKinsey, where he served insurance, reinsurance, and capital markets clients as a consultant and engagement manager for three years. Prior to that, he was a vice president in Lehman Brothers, Senior Policy Advisor to the Comptroller of the Currency, and staff economist to Senator Bill Bradley, where he proposed a debt relief program for Mexico and designed the precursor to interest rate relief Brady bonds. Apgar also teaches at Johns Hopkins University’s School of Advanced International Studies.
Ask Amy continued from above
Note: If you have a version earlier than @RISK 4.5.3, you may need to install a patch that makes @RISK recognize the stacked matrix. To obtain this patch, click here.
To rebuild the matrix, take the following steps:
1. Break your original matrix up into smaller blocks.
Move from left to right breaking up the matrix into smaller blocks. The number of columns in the blocks should be less than the column limit in the version of Excel that you’re using. Each block should have the same number of rows as the original matrix. Make as many of the blocks the same size as possible.
For example, when using @RISK with Excel 97 or higher, work within a limit of 256 columns. With a 400 x 400 correlation matrix, break it up into 2 blocks, each with 400 rows and 200 columns. A 789 x 789 correlation matrix could be broken up into 3 blocks with 789 rows and 750 columns and one block with 789 rows and 39 columns.
2. Stack the blocks vertically to create a new matrix.
Move from left to right placing each block under the one before it. The second block will be placed under the first block, the third block will be placed under the second block and so on. If there is one block that has fewer columns than the other blocks, the smallest block should always be placed at the very bottom of the stack.
3. Add RiskCorrmat.
Once the matrix has been rebuilt and entered into your workbook, the final step is to add the RiskCorrmat function directly to the cells containing your inputs. In your Excel workbook, add the RiskCorrmat function directly to each cell containing the input functions that you wish to correlate. The syntax for the RiskCorrmat function is:
RiskCorrmat (matrix cell range, position, instance)
Note: The matrix cell range reference must be rectangular; it can’t have an irregular shape. If you end up with one section of the matrix that has fewer columns than the rest, make the matrix cell range reference rectangular by including empty cells in the reference.