Top 20 MS Excel VBA Simulations, VBA to Model Risk, Investments, Growth, Gambling, and Monte Carlo Analysis

Top 20 MS Excel VBA Simulations!

VBA to Model Risk, Investments, Growth, Gambling, and Monte Carlo Analysis

By

ANDREI BESEDIN

Copyright © 2017

TABLE OF CONTENTS

Simulation Definition

Simulation Applications

Simulation Models (called risk models)

Model inputs that are uncertain numbers -- we'll call these uncertain variables

Intermediate calculations as required

Model outputs that depend on the inputs -- we'll call these uncertain functions

Creating Models in Excel or Custom Programs

Choosing Samples for Uncertain Variables

Simulation Methods

Monte Carlo Simulation

What is Monte Carlo Simulation?

Why Should I Use Monte Carlo Simulation?

What Knowledge Do I Need to Use It?

How Will This Help Me in My Work or Career?

Some basic Excel VBA skills needed for creating simulations. 

Creating and Managing Array

Declare an Array With Dim Statement

Resize an Array With Redim Statement

Manage Dynamic Array

Create Multi-Dimensional Array

Find The Size of an Array

Decision Structures - IF and Select Case

            IF ... Then

          IF ... Then ... Else

          IF ... Then ... ElseIf

          Select Case

Loop Structures

For ... Next

For ... Next Loop With Step

Do While ... Loop

Do Until ... Loop 

Do ... Loop While

Do ... Loop Until

Sorting Numbers in an Array

Find Maximum and Minimum Values in an Array

Double Sorting - The Secret of Resampling Without Replacement

Simulation

Simulation is a flexible methodology we can use to analyze the behavior of a present or proposed business activity, new product, manufacturing line or plant expansion, and so on (analysts call this the 'system' under study).  By performing simulations and analyzing the results, we can gain an understanding of how a present system operates, and what would happen if we changed it -- or we can estimate how a proposed new system would behave.  Often -- but not always -- a simulation deals with uncertainty, in the system itself, or in the world around it.

Simulation Applications

Simulation Models

Simulation Methods

Monte Carlo Simulation

Simulation Applications

Simulation is one of the most widely used quantitative methods -- because it is so flexible and can yield so many useful results.  Here's just a sample of the applications where simulation is used:

Choosing drilling projects for oil and natural gas

Evaluating environmental impacts of a new highway or industrial plant

Setting stock levels to meet fluctuating demand at retail stores

Forecasting sales and production requirements for a new drug

Planning aircraft sorties and ship movements in the military

Planning for retirement, given expenses and investment performance

Deciding on reservations and overbooking policies for an airline

Selecting projects with uncertain payoffs in capital budgeting

Simulation Models

In a simulation, we perform experiments on a model of the real system, rather than the real system itself.  We do this because it is faster, cheaper, or safer to perform experiments