-
Learn how to use Excel and VBA for business process modeling. Find out how to create and run simulations for customer flow, queuing, and manufacturing.
Excel power users: Learn how to use Excel and Visual Basic for Applications (VBA) to create and run simulations of business processes for customer flow, queuing, and manufacturing. VBA exponentially extends the power of Excel, allowing you to control process flow and logic and add custom classes and variables. In this course, Curt Frye demonstrates how to build simulations of increasing complexity, which model the flow of customers through a multi-station process. Learn how to calculate arrival times, add customers, increase individual station capacity, and enable conditional station paths. At the end of the course, Curt shows how to convert output to an Excel table and analyze the results of your simulation.
Overview
Syllabus
-
Introduction
- Build effective simulations using Excel VBA
- What you should know
- Create a class module
- Define class properties
- Create an instance of a class
- Create collections
- Delete objects to free up memory
- Define process flow and programming goals
- Declare variables used in the simulation
- Define the Customers class and add a customer
- Define the Stations class and add a station
- Add code to calculate and write out times
- Delete unneeded objects
- Run the simulation
- Adapt the code for multiple runs
- Define process flow and programming goals
- Create an outline of If and For Next statements
- Add instructions within the framework
- Add code to write out simulation results
- Run the simulation
- Define process flow and programming goals
- Use the Poisson distribution to calculate arrival times
- Add customers to the simulation
- Add code to write out simulation results
- Run the simulation
- Define process flow and programming goals
- Add capacity to each station
- Run the simulation with added station capacity
- Add more stations to the simulation
- Run the simulation with additional stations
- Add rework loops to stations
- Run the simulation with rework loops
- Define process flow and programming goals
- Enable conditional station paths
- Run the simulation
- Read the simulation results as written
- Manually reset the results cells
- Summarize results using the Total row
- Sort and filter Excel table values
- Troubleshoot errors in your code
- Next steps
