2016
Lynda
Curt Frye
3:14
English

Excel power users: Learn how to use Excel and VBA to create and run simulations on business processes for customer flow, queuing, and manufacturing. Visual Basics for Applications exponentially extends Excel's power, allowing you to control process flow and logic and add custom classes and variables. In these exercise-centered tutorials, Curt Frye builds simulations of increasing complexity, which model the flow of customers through checkout stations. You'll learn how to calculate arrival and transaction times, add customers, and increase individual station capacity, and find out when you need to add more stations. At the end of the course, Curt shows how to convert output to an Excel table and analyze the results of your simulation.

Introduction
Welcome
What you should know before watching this course
Using the exercise files
1. Defining Necessary VBA Constructs
Creating a class module
Defining class properties
Creating an instance of a class
Creating collections
Deleting objects to free up memory
2. Developing Process Control Structures
Describing process flow and programming goals
Declaring variables used in the simulation
Defining the Customers class and adding a customer
Defining the Stations class and adding stations
Adding code to calculate and write out times
Deleting unneeded objects
Running the simulation
Adapting the code for multiple runs
3. Adding a Ticker to Track Time
Describing process flow and programming goals
Creating an outline of If and For Next statements
Adding instructions within the framework
Adding code to write out results
Running the simulation
4. Adding Customers During the Simulation
Describing process flow and programming goals
Calculating arrival times using the Poisson distribution
Adding customers to the simulation
Writing values to the Results worksheet
Running the simulation
5. Recording Simulation State
Describing process flow and programming goals
Adding capacity to each station
Running the simulation with added station capacity
Adding more stations to the process
Running the simulation with additional stations
Adding rework loops to stations
Running the simulation with rework loops
6. Analyzing Simulation Results
Reading the simulation results as written
Manually resetting the results cells
Converting simulation output to an Excel table
Summarizing results using the Total row
Sorting and filtering Excel table values
Conclusion
Next steps

lynda.com/Excel-tutorials/Process-Modeling-Excel-Using-VBA/431059-2.html

Download File Size:388.77 MB

---