Spreadsheet Aided Management - 719

Please contact Janet Mincer with inquiries regarding enrollment fees:



Participants Learn:

  • Increased awareness of how use of Excel™ can enhance the management process
  • Increased integration of management disciplines
  • Better intuition of performance dependence on controlling decision parameters
  • Pitfalls of use of Excel™ for Production Applications
  • Know when use of spreadsheets is appropriate
  • Know the importance of documentation
  • How to develop re-useable functional models.
  • How to setup standard Input/Output fields to monitor the impact of decision variables
  • How to setup output fields to monitor performance and constraint functions
  • How to deal with implicit relationships using Goal Seeker/Iteration and Solver for function inversion
  • How to use VBA management function Add-Ins for rapid decision-making
  • How to use the SpreadsheetWorld-Excel™ Toolboxes to support your management process
  • How to monitor requirements specifications (constraints)
  • How to do performance optimization and simultaneously monitor System Requirements
  • About MS Office Object Models and how they are integrated and work together.
  • How to use Userforms and ActiveX Controls for project control
  • How to easily develop ActiveX graphic user interfaces for project control
  • How to setup Configuration Trade-study Matrix & system sensitivity maps

Course Description
Spreadsheets have long been one of the most important computer tools for managers. The power and range of application of spreadsheets has grown dramatically in recent years. The number of available library functions, third party Excel™ add-ins, and specialized spreadsheet templates available today has added a vast array of additional tools to enhance the management process. The ability to use an Excel™ spreadsheet for command, control and communication of information, schedules, budgets, analysis, hyperlinks to electronic documents and important Internet locations make this tool indispensable for today’s manager. The purpose of this course is to provide a time efficient, hands-on experience using these tools so that it will have an immediate and lasting impact on the management process for those who participate.

Methods taught in the course show how to use simple structured input/output (cause/effect) fields to establish a consistent platform for supporting a wide range of management functions, including planning, cost and budget modeling, schedule simulation, data and information storage and retrieval, analysis and presentation. The course also includes more advanced management tasks such as trade-off studies, optimization and uncertainty analysis. The methods taught in this course will benefit any management system or style.

Course Materials
Each participant receives a set of course notes containing vital concepts not found in other books. A CD-ROM containing all of the course examples; case studies; and many Excel™ Add-ins accompany the notes.

Managers from all disciplines find this course immediately useful, practical and eye opening. Department Managers, Program and Project Managers, Section Heads, Group Leaders, Operations Managers, Plant Managers, and Production Managers will benefit. Participants should have a basic background in Excel with good keyboard skills. Computer Requirements: Computers must have a complete installation of Excel™ 2003 and a CD-Rom drive. Laptops are acceptable provided they are equipped with a mouse to maintain class speed.


Overview of Excel™ Environment

  • Setting Excel™ Environment Preferences
  • The Excel™ Application Object Model
  • Menus and Toolbar Objects
  • Excel™ Internal Function Library
  • Excel™ Add-Ins - Solver
  • Spreadsheet Iterations And Circular References
  • Linked Worksheets And Workbooks
  • VBA Editor & Debugger
  • Object Browser
  • Userforms and ActiveX Controls

Structured Spreadsheets

  • Structured worksheet layout and design
  • Performance and constraint modeling
  • Configuration trade-off studies
  • Input Decision Parameter Field Structure
  • VBA User Defined Functions
  • Output Field Structure
  • Defined Name Ranges
  • Output Range Analysis
  • Time Varying Output Fields
  • Inverting Implicit Performance Functions - Goal Seeker
  • Dual Unit Fields

Information Management, Connectivity & Documentation

  • Storing & linking management information
  • Information communication
  • Information integration into models
  • Excel™/VBA Documentation guidelines
  • Data and results sharing

A Management View of Visual Basic (VBA)

  • What is VBA and what is it's role
  • VBA Projects and Modules
  • VBA User Defined Function Procedures
  • VBA Sub Procedures
  • What does a manager need to know about VBA

Functional Modeling and UDF Libraries

  • High level functionality
  • Function Modeling Trees
  • Modeling Trees using VBA UDF's
  • Excel™/VBA function libraries
  • Financial function library
  • Building Excel™ Function Add-Ins
  • Using Add-Ins in Excel™
  • SpreadsheetWorld On-line Documentation Editor

Object Oriented Concepts

  • What are Excel™ objects?
  • Properties of Objects
  • Methods associated with an object
  • Common Excel™ objects
  • Objects and Events
  • What happens when an event happens?

Graphic User Interfaces (GUIs)

  • The role of Drawing in Excel™
  • Userforms and Active X Controls
  • Elements of design Project Control
  • Linking and drawing forms

Operations Research/Optimization

  • Formulating a system for optimization
  • System performance objectives and constraints
  • Using Excel™ Solver for streamline the process
  • Objective/Cost Functions
  • Constraint Functions
  • Decision VariablesStructured Optimization Sheets
  • Linking System and Optimization Sheets
  • Solver™ for Local Minimum of Continuous Functions
  • Evolver™for Global Minimum of General Functions
  • Configuration Optimum Trade Matrix (COTM)

Data, Data Analysis and Graphing

  • Managing data
  • Importing Data into Excel™ Worksheets
  • Automatic data downloads from remote databases
  • Enumeration and Lookup Functions
  • Interpolation and Extrapolation
  • Linear Regression
  • Linear Surface Regression

PivotTables Reports & PivotGraphs

  • Role of pivot tables for complex data sets
  • Setting up for PivotTable Reports
  • Setting up for PivotGraphs
  • PivotTable Report Wizard
  • Publishing PivotTable Lists for Internet

Process/Time Sequence Simulation

  • Discrete event simulation
  • Simulation in the Optimization Loop

ActiveX for Project Control

  • Developing a user interface: An overview
  • Creating user interfaces
  • How to design userforms as GUIs
  • Designing Userforms with graphics

Management of Uncertainty

  • Uncertain Decision Variables
  • Decision Variable Probability Distributions
  • Expected Values for System Modeling Functions
  • Role of Monte Carlo Simulation
  • Structured Uncertainty Analysis Sheets
  • Uncertainty Add-In: INSIGHT.XLA™
  • Optimization with Uncertainty - RISKOPTIMIZE
Short Course Seminars

Spreadsheet Aided Engineering (576)

Advanced Spreadsheet Aided Engineering (676)

Refresher to Microsoft Excel (701)

Intro to Computational VBA for Excel (702)

Intro to VBA Object Sructure for Excel (703)

Visual Basic for Applications (704)

Advanced Excel/VBA Techniques (705)

Introduction to Programming Microsoft Windows with VBA (706)

Online Documentation Techniques (707)

Database-Aided Engineering with Microsoft Access and Microsoft Excel (708)

World Wide Web-Aided Engineering with Excel/Access/Front Page (709)

FORTRAN, DLLs and Integration with Excel/VBA (710)

Practical Risk Modeling in Spreadsheets (711)

Spreadsheet Aided Simulation and Optimization (712)

Spreadsheet Aided Thermal-fluid System Modeling and Optimization (713)

Spreadsheet Aided Propusion System Design (714)

Spreadsheet Aided Signal Processing (715)

Integrated Excel/Access Aided Engineering (716)

Spreadsheet Aided Management (719)

Spreadsheet Aided Vehicle Dynamics (723)

Spreadsheet Aided Data Plotting & Analysis (724)

Systems Modeling and Analysis (726)

Access Aided Engineering (727)

Spreadsheet Aided RF & Microwave Electronics Design (728)

Using Excel and Mathematica (731)
SWI © Copyright 2011. All rights reserved. home page   |    about us   |    services   |    training   |    contacts