Business Office

Excel™ Simulation Engine       BPES_SIM

The “Excel™ Simulation Engine” is a VBA macro designed to execute workbook calculations after inserting different combinations of input values, and then saving the results in successive output columns that it can sort by “priority” based on user-selected target values that can be maximized (or minimized). If can also discard results based on out-of-bounds conditions. We use this in a number of analysis tools, but there are many unique applications where the same abilities might provide capabilities for optimizing production or staffing levels or many other applications that can be represented in an Excel™ worksheet model, but that require an iterative approach until the operational “sweet spot” has been identified. This engine is used to drive the marketing plan and business plan models explained elsewhere on this website.

To use the pre-coded engines the student must understand that the code is locked and can only be controlled through data external to the VBA-code itself. This is to stop the code from being corrupted if the wrong code logic is applied. If you want to write custom code we suggest using the VBA training courses that explain the underlying nature of the VBA-code itself, which is a completely different focus from building an Excel™ worksheet application that is based on what the pre-coded engines deliver “out of the box” to make it easier to get started.

The simulation engine has pre-defined logic to save and restore data cells that will be affected by the changes made to various parameters. For each of these data cells it will change the contents in a range “ ” so that it generates all the combinations that must be considered. You can also identify the output cells that it must save to results columns, so that it can later determine which are the best overall results and then sorting the columns in descending order of priority. The engine is fully documented, which means you can incorporate that into your model documentation for the application you want to create.

The actual application logic is defined in the user region to the left of the simulation engine, and the area to the right of the simulation engine is reserved for the output columns. The capacity of the simulation is limited only by the version of Excel™ used that then determines the number of columns available for output.

This session is focused on the columns to the left of the simulation engine that should be used for a particular business application model that you are considering executing under the control of the simulation engine. This is essentially a stand-alone Excel™ worksheet, which means that you could convert most business worksheets into simulations by incorporating them into the prescribed VBA-model structure.

Since Excel™ is used for many unique applications the major distinctive feature for simulation is that the analysis is driven by key parameters that are single-cell in nature (rather than a row of values). For example, a given number of machines, people, or other factors of production that are combined with simple growth rates to calculate the status at a particular point in time. You can tray many sets of values for these parameters to see what the bottom-line impact is.

The simulation engine turns the worksheet calculation off while it changes data values. When it has initialized a new set of values, calculations will be enabled and executed, and then worksheet calculations are turned off to copy values into the output column. This minimizes the overhead and the process runs quicker that way. The simulation engine refers to input cells by placing a simple cell formula like “=C15” in the input selection column, which really is where the parameter value is input.

The same is true for an output column, so you can use standard Excel™ logic to make references that persist even if you make changes to the worksheet. The temporary output column is in the simulation region – from there the output is moved to the current case column, complete with the parameter values. We use the same approach to select data for the SORT commands that prioritize the output columns from best case to worst case.

However, for boundary limit conditions we use “=IF(,”Y”,”N”)” where the condition test can be as complex as necessary to show if the outcome should be “Y” (to include the case) or “N” (to exclude the case). This keeps things simple. To run a simulation keep the cursor in the user region while you click the [RUN] button on the simulation region to start the engine.

Normally we use the [RUN] button selection to start the simulation, but if we place the cursor in one of the output columns the [RUN] button will restore that particular case and show the worksheet as it looks with those values initialized. So, if you want to quickly show the 5 alternative cases it does not take much to restore the views. When it has recalculated these data, the simulation prompts for a worksheet name.

If you enter the name of a new worksheet it will create that worksheet under that name and then copy the contents of the columns to the left of the simulation region to that output worksheet. If you do not enter a name of 8 characters or more there is no output worksheet produced. Either way the process will stop. If you want another case reported, then you need to select that column and click on [RUN] again.

Our sample applications focused on the marketing plan and business plan creation that are examples of a type of worksheet in which you may want to try different combinations of assumptions to see how things play out. You can use the same logic to create time series to illustrate economics, or you can create reference tables, estimates, or other repetitive exercises. You can run through alternate product mixes to see what ratios produce the best overall value.

You can simulate the real estate market to analyze the consequences of different housing prices and interest rates given the assumptions on income demographics and potential affordability. Hence, there are many repetitive calculations that you may want to analyze, and it may be that you want to show the results using basic Excel™ graphics that show the relationship between input and output values. Learn more about the tools and put your imagination to work to find useful ways to put these tools to work for you.

Learning Formats       BPES_SIM

This course is currently available in a classroom setting (public or company private) with approximately 15 contact hours.

PDF – Certificate Of Completion

Each course offers a certificate of completion that identifies the course, the student, and a brief description of the course. To receive a certificate the student must have attended at least 80% of the course sessions. This personalized certificate is forwarded to the student by Email.

PDF – Course Notebook

Each course includes a notebook in PDF format that provides the minimum knowledge the student must master in order to obtain the certificate. In the notebook you will find references to other study materials. Students receive the notebook by Email when their registration is confirmed.

PDF – Program Overview

An overview of this study program can be downloaded from the website by right-clicking on the program link on the enquiry page.

PDF – Current Training Schedule

A list of upcoming training sessions can be downloaded from the website by right-clicking on the schedule link on the enquiry page.

Registration – Service Providers

To register for any training course please look on the enquiry link page of your service provider (from where you accessed this website). On the page you will find a registration request form where you can order the course that you are interested in. The availability dates will be provided to you, along with payment instructions if you decide to go ahead.