Business executives have access to large quantities of data obtained from a variety of sources. However, data complexity makes the extraction of practical information difficult without the use of statistical methods. Furthermore, executives seek information based on statistical forecasting that concentrates on using the past to predict the future by identifying trends, patterns, and business drivers. For better insight into the future, executives need to deal with uncertainty in business external factors such as market demand.

Some simple yet effective approaches enable the Xcelsius dashboard applications to perform probability analysis and simulation techniques. You can combine Microsoft Excel components with custom Xcelsius components to perform those simulation tasks.

The Microsoft Excel spreadsheet has become somewhat of a standard for quick and manageable data management and analysis. It is easily used to do a variety of calculations. It includes statistical functions and a Data Analysis ToolPak. Its strength lies in two areas: a set of formulas and Excel programming. 

Due to some valid reasons, Xcelsius does not include Excel-based programs as part of the final application. Excel macros or VBA libraries that need to execute Excel macros cannot be bundled in a Flash product that is generated out of Xcelsius. However, you can take several approaches to perform statistical analysis that is easy to implement.

Before I jump into an explanation of the approaches, this sample business example shows the essentials of statistical data model design and implementation in the Xcelsius application. To implement the concept you need Xcelsius 2008, Adobe Flex Builder 2 or 3, FLEX SDK, and Add-on Packager.

Statistical Data Model for Monte Carlo Simulation

Monte Carlo simulation enables you to model situations that present uncertainty and play them out thousands of times on a computer. Generation of a pseudo random number R that is distributed uniformly over interval 0 < R < 1 is the heart of any Monte Carlo simulation. You cannot generate only a single sample and use it as the result of the simulation. Instead, you need to generate many samples and get the average (or any other statistics) as the result.

The Xcelsius application can be designed for Monte Carlo simulation. I have selected two approaches to design and develop the Xcelsius application for business simulations:

• Approach 1: An Excel worksheet with only manual simulation that allows you to simulate results thousands of times. In this approach, you use Excel statistical function NORMINV along with the RANDOM function to define probability using mean and deviation values for uncertain business parameters. You also use simple arithmetic functions along with COUNTIF, MAX, and MIN. The approach does not use any Excel programming. The repetitive tasks of business formula evaluation are achieved only in the Xcelsius worksheet.
• Approach 2: Simulation using an Xcelsius custom simulation component. This approach is more advanced than the first. An Excel worksheet in the Xcelsius tool is used only to model a business formula with uncertainty parameters.

Following are business examples to illustrate the basic statistical data model concepts and help you solve a problem that would be difficult to do without a Monte Carlo method approach.

Business example: A company plans to introduce new “product A” and would like to know the probability distribution of PROFIT and LOSS under perfect market conditions. Further, the company is interested to know business elements that contribute to success and failure and what the safe value ranges are to minimize risk. Figure 1 shows the business elements that are involved and the correlation among them.

Figure 1

Business elements and their relationships

As shown in Figure 1, the profit is calculated based on revenue generated less the cost of the component. The cost component is comprised of fixed costs and variable costs (quantity * variable cost per product). Table 1 shows the uncertain business parameters in ranges and standard deviations.

Table 1

Uncertain business element and their limits

Approach 1: Excel Worksheet with Only Manual Simulation

Figure 2 shows a simple process for Xcelsius application design.

Figure 2

Application development process

The challenge of developing Monte Carlo models lies in translating a real-world problem into Excel equations and simulation techniques. The typical steps involved in data modeling follow:

Step 1. Classify business parameters as certainty parameters, uncertainty parameters, and evaluated parameters. Certainty parameters are those business elements for which fixed values define a given set of business conditions, such as a fixed cost. Uncertainty parameters are values that fluctuate, including quantity, price, and variable cost per product. Evaluated business parameters are influenced by certain and uncertain business parameters and include profit, revenue, and total cost.

Step 2. Lay out all business parameters in rows instead of columns. This is a convenient way to organize the data in Excel when a large number of results is generated by simulation (Figure 3). The columns marked in yellow are evaluated parameters and the gray columns are uncertainty parameters.

Figure 3

Layout of business parameters in Excel

Step 3. Design appropriate statistical formulas for uncertainty parameters based on the parameter probability definitions. In most cases, fluctuation ranges are specified by minimum and maximum limits or with mean and deviation values. These are normal (Gaussian) distribution classic examples. In both cases use the Excel formula shown in Table 2.

Table 2

Simple mathematical and statistical formula applied for sample business example

Figure 4

Sample business example parameter layout with an Excel formula

I have listed all the uncertain parameters limits in column B and C in Figure 4 and use those in corresponding business parameters formulas in cells G4, H4, and J4. Since these limits hold true for a simulation cycle, I used them as absolute cell references in the formula. Absolute ranges have a $ character before the column portion of the reference or the row portion of the reference. Absolute cell reference in a formula helps you to copy that formula from one cell to a range without losing the reference to the original cell, which is a prerequisite for a manual simulation design model.

Define a Manual Simulation Technique in Excel

Business expression simulation in Excel is a repetitive task of evaluating results by generating probability values using random functions. It is a simple process of copying a formula for all parameters in a number of rows. The following sequence of operations eases the task of copying the formula to multiple rows.

Step 1. Define the business parameters using Excel formulas and lay them out in a row as shown in Figure 4.

Step 2. Define the number of simulations and fill simulation numbers in the dedicated column. I chose 1,000 times simulations for my sample business example and have decided to use fill column E with simulation numbers as shown in Figure 4. You can quickly fill numbers in the column as follows: Select cell E4. Fill value 1. Click the Home tab. Click the Fill option. Select the Series… option. That opens a small dialog box. Select the Columns radio button. Input the step value as 1 and the stop value as 1,000. Click the OK button.  

Step 3. Define the simulation range and profit range. A simulation range covers all the parameters involved in the simulation process along with the simulation number column. The simulation range for sample business parameters used in Figure 4 is from column E to column K. The simulation range rows are equal to the number of simulation numbers. Therefore the simulation range for my sample business parameter simulation example is E4…K1003.  

The profit range only covers the column related to profit values. It is column F in Figure 4. Therefore, the profit range for my sample business parameter simulation example is F4…F1003.

The simulation range helps to copy all formulas to multiple rows in one step. Use Name Manager to define ranges. To open the Name Manager dialog box first click the Formulas tab, then click Name Manager. Enter the values as shown in Figure 5.

Figure 5

Profit range and simulation range definitions

The profit range is used later for a frequency distribution model as the successor to the simulation model.

Step 4. Replicate the Excel formulas used to define business parameters to all the rows defined in the simulation range. The following sequence of operations quickly copies formulas across the range. Select a range from E4 to K4 and copy all cells (using Ctrl + C). Select the simulation range from the name box (Figure 6) and press Enter.

Figure 6

Simulation range selection

This replicates all formulas in all the cells in the simulation range.

Step 5. Provide the necessary formats (such as right alignment to numeric data with appropriate decimals and currency formats) to the respective column data and you are ready with 1,000 simulations.  

Step 6. You can press F9 to simulate as many times as you want for the next set of 1,000 values. In my example, I have ended up with a column of 1,000 possible values (observations) for profit. The last step is to analyze the results by creating a histogram in Excel, which is a graphical method for visualizing the results.

Frequency Distribution Data Model

The histogram tells a good story, but in many cases, you may want to estimate the probability of being below or above some value, or between a set of specification limits. To provide a concise summary of the results, it is customary to report the mean, median, standard deviation, standard error, and a few other summary statistics to describe the resulting distribution. A simple frequency distribution data model is able to generate the result set for the histogram and can provide a summary of the results.

A frequency distribution data model calculates the frequency of profits that lie in an equal range of profit spans, spread across the full range of minimum to maximum profit limits obtained in a simulation. Take these steps to generate a frequency distribution data model.

Step 1. Identify the minimum profit value and the maximum profit value (Figure 7).

Step 2. Calculate 40 equal profit spans (intervals).

Figure 7

Maximum, minimum, and intervals for profit values

Step 3. In a new column, list profit spans (intervals) from minimum to maximum with the calculated interval as shown in Figure 8.

Step 4. List the respective counts of profit values that fit into the profit spans. I used the COUNTIF Excel formula.

Figure 8

Profit spans and respective frequencies

Once you reach this stage you are done with the data modeling part of the design. You can experiment with multiple simulations using the F9 key.

Presentation Layer Design

The last part of the application development process is to design an appropriate presentation layer for the simulation application. I have chosen four horizontal slide bars to control the business parameters (especially uncertainty parameters) as shown in Figure 9.

Figure 9

Business parameter binding to horizontal sliders

To display probability distributions of total profit, you can use a simple column chart. The column chart X-axis binds to the Profit distribution column and the Y-axis to frequency distribution. Figure 10 shows the overall application presentation layer design.

Figure 10

Monte Carlo simulation application presentation layer

Once you are done with the presentation layer design, the application is ready for execution. At application runtime as you adjust the business control parameters using the horizontal slider, the graph instantly reflects the frequency distribution for those changes. Each parameter change makes 1,000 simulations and generates 1,000 profit values, spanning from negative to positive. In Figure 10, the first profit span starts at $79801 to $74102. The span application calculates the number of profit values that can fit within the profit span. That number is shown using the bar. The profit value count for all profit spans becomes the typical frequency distribution. The underlying Excel functionality calculates probable profit values 1,000 times due to the introduction of Excel random functions in the business parameter evaluation. You can automate the simulation processes and simplify the design process as shown in the next section.

Approach 2: Simulation Using an Xcelsius Custom Simulation Component 

A custom simulation component triggers simulation of the business formula in Excel, collects all simulation results, processes those results for the frequency distribution model, and sends them back to Excel for further data modeling (Figure 11). In short, the simulation component only automates the repetitive task of business formula evaluation but does not actively participate in the actual business formula evaluation processes.

Figure 11

Monte Carlo simulation using a custom component

In this approach, the business formula evaluation is still performed by Excel formulas as described in approach 1. The simulation component does not calculate the business formula or the business parameter evaluation. Unlike the first approach, you eliminate writing or copying business formulas multiple times to perform manual simulation. The simulation component provides the frequency distribution model data back to Excel for further operations. Thus this approach allows a designer to focus more on the business formula model design, which is key to the business analysis process. Before starting you should be aware of some design and implementation challenges.

Custom Simulation Component Design Aspects and Implementation Challenges

At design time, the Xcelsius component is bound to an Excel cell or to a range of cells. At runtime these bindings help the component either push or pick values from Excel. A typical activity cycle starts from the application presentation layer when a user triggers an event (generally a CLICK event) on the user interface (UI) component. If the UI component pushes values to its linked or bound Excel cell or range of cells, changes are reflected in all other cells that are connected via Excel formulas to a linked cell or to a range of cells. That in turn changes values or displays of the UIs of the presentation layer that are bound to those impacted cells. Although it is a simple activity cycle, a couple of situations might challenge the design of the simulation component:

1. Activity cycle trigger by user
2. UI component ONLY picks new values from the binding cell.
3. Only cells that are connected via the formula are affected
4. Components can pick or push ONLY values of a cell or of a range of cells
5. Activity has a definite end after the cycle completes

I now define component interfaces (input and output properties) along with technical and programming aspects. While doing so, I explore possible ways to overcome the challenges of transforming a normal activity execution process cycle into a repetitive execution process.

To trigger component interface (properties) simulation, you define two properties for the custom component as pushValue and incomingValue.

• pushValue property: Use this property to push incremental numeric values to Excel cells at every simulation cycle. Use of this action triggers an Excel formula only if the triggered cell is connected directly or indirectly to any part of an Excel formula definition.
• incomingValue property: Use this property to pick the value from a cell that keeps copying incremental values from the target cell of  pushValue property.

To trigger component interface (properties) for histogram data integration, you need only three properties to generate the required frequency distribution data model:

• Result_Collector property collects all result values evaluated at each simulation cycle
• Histogram_Result_Set property pushes a set of result values calculated at the end of predefined simulation cycles

• Histogram_Frequency_Set property pushes a set of frequency count values against each result range set by Histogram_Result_Set

Custom Component Code Snippet for User Interface Design

You need to design two buttons. You need a Start button to begin the simulation process. It should display a label such as Simulating… while the simulation process is active. When the simulation process is over, the label should dynamically change to say Start Simulation for the next round of the simulation cycle. Another button remains inactive while the simulation process is on. A Show Histogram button indicates the simulation is over. Refer to the MXML code snippet in Figure 12 to understand the button labels and related click event functions.

Figure 12

Monte carlo simulation application presentation layer

Custom Component Code Snippets for Triggering a Simulation Function

At runtime, as values are read from Excel by Xcelsius, a visual or non-visual component triggers the component commitProperties function. The function commitProperties coordinates modifications required for that component property. Override the commitProperties function and tweak it to make the next call for another cycle of push and pick values between the simulation component and underlying Excel. You can control a number of such calls with the help of simple counter. To understand the implementation of this concept, study the sequence of code snippets in Figure 13.

Apart from component properties discussed earlier, you use two public variables (Figure 13). An array variable stores all evaluated results of the Excel formula at each simulation cycle and counter variable control simulation cycles within the specified limit. The simulationCounter variable is set to 500 values for my example to avoid unnecessary execution of the custom component at design time. If the value is set to zero then the design time component execution starts and goes into an endless loop.

Figure 13

Declaration of variables required for the simulation process

As a user clicks the Start button, the component calls the Start_Simulation() function for execution (Figure 14). It empties the local array and makes it available for a new intake of results values collected via the component property Result_collector. Next, it resets the simulation counter to zero to prepare for the counting simulation cycles. Finally, a small routine cycleGenerator is called.

Figure 14

Trigger simulation code

The cycleGenerator routine executes a simple cycle of push and pick values using defined component properties as shown in Figure 15.

Figure 15

Simulation cycle code

In the above code, the pushValue parameter uses the current value of simulationCounter and pushes it to Excel. You can use it to show simulation progress using a suitable visual component – for example, a horizontal progress bar. This operation triggers the underlying Excel formula. The evaluation result value obtained from the formula is passed to a local result collector array using the Result_Collector property, as shown in the next code line in Figure 15.

Although the job of the cycleGenerator routine is over at this point, the incomingValue property picks new values from its linked cell that is connected with the target cell of pushValue. When the component executes the commitProperties() function you validate the change of incomingValue and make a next call for a simulation cycle as shown in Figure 16.

Figure 16

Override of commitProperty for simulation cycle next call

Custom Component Code Snippets for Histogram Data Generation 

For histogram data construction, all collected evaluation results are processed to derive result value sets and frequency of occurrence sets. The execution process starts as the user clicks the Show Histogram button and calls function histogramGeneration. This function ensures that collection of result values should be available in ascending order before jumping to run routine histogramRtn (Figure 17).

Figure 17

Histogram generation process code

A logical execution step for the histogramRtn function follows along with a code snippet.

Step 1. Define private variables to facilitate the execution process that releases immediately after successful execution of the routine (Figure 18). You need two data arrays to store result span values and frequency counts so that you can supply data to the component properties Histogram_Result_Set and Histogram_Frequency_Set at the end of routine. As you move to the next step, you will understand how other variables participate in the process.

Figure 18

Variable required for routine

Step 2. Calculate equal intervals span of result values by finding the difference between the maximum and minimum result values and then split that difference with the defined interval value. Since the local array is in ascending order, the last array element represents the maximum result value and the first element represents the minimum result value. This difference is split using equal intervals (Figure 19).

Figure 19

Code snippet to calculate equal result value span

Step 3. Populate the resultDistArr array variable with the help of resultValueSpan (Figure 20).

Figure 20

Code snippet to populate resultDistArr array

Step 4. Calculate the total counts of the result values against each result span value. I have used a control loop statement that goes through every value of the local result values collected at the time of simulation cycles and placed those values in the freqDistArr array (Figure 21).

Figure 21

Code snippet to calculate frequency of occurances

Step 5. At the end of the routine, you assign private array values to the respective properties of the component as shown in the code snippet in Figure 22.

Figure 22

Assign array to respective component properties

We have discussed the important part of custom simulation component programming code. Now I resume the discussion on modeling using a simulation custom component. 

Business Data Model Design for Simulation Component-Based Approach

Designing a data model for this approach is similar to the earlier approach. I will continue using the previous sample business example. Figure 23 illustrates the business parameter and related Excel formulas that evaluate the result.

Figure 23

Monte Carlo simulation data model and Excel formula

A custom component property binding with the Excel data model is shown in Figure 24.

Figure 24

Custom component property binding with data model

You now can follow a simple Xcelsius application design process.

Application Presentation Layer Design Using a Custom Component Approach

For simplicity, I will keep the presentation layer design used in the earlier approach with the addition of one UI component that is a horizontal progress indicator (Figure 25). The horizontal progress indicator conveys the simulation progress status at runtime of the application.

Figure 25

Presentation layer UI connected to Excel data model

At application runtime, you can adjust business parameters using the horizontal scroll bars. Once you are done with the adjustment, click the Start Simulation button on the top left of the histogram. The simulation progress displays using a horizontal progress bar with a running counter that shows the number of simulations. To see a histogram for your business parameters, click the Show Histogram button. You can repeat the process as many times you want to make more accurate business decisions.

Similarities and Differences in Manual and Custom Approaches

Both approaches discussed allow you to estimate the probability of being below or above some value, or between a set of specification limits. However, you may find differences in implementing the approaches. A manual approach required more systematic work on the data model design. Designing repetitive business calculations multiple times increases the overall database size of the application. Although it takes more time, at runtime you get a quick response from the application. The manual approach is more suitable where you need quick decisions based on a number of business parameters.

A custom simulation component implementation approach reduces the work on a model design and allows you to focus on the business model design. At runtime you need patience to see the simulation results, but the overall model and application size is considerably reduced. This approach is suitable for a quick design of a data model.

Sandesh Darne

Sandesh Darne is a senior lead for the Consulting ERP Practice at L&T Infotech, India. He leads a group of senior consultants whose primarily focus is on SAP upgrades and SAP usability consulting services. He also directs the SAP NetWeaver Portal and the SAP Center of Excellence, which focuses on excellent practices in organizations. He is certified in SAP NetWeaver Business Warehouse 3.5. He has also worked in the product development department of L&T Infotech where products such as ZoomUP, eALPS, and CodeReview were built and used as accelerators for SAP upgrade projects.

You may contact the author at sandesh.darne@lntinfotech.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.