Capacitas Logo
About Us
Our Solutions
Our Services
Industries
Our Clients
Investor Relations
Training
Portfolio
Accreditation
Training Calendar
Onsite Training Quotation
Feedback
Research
News & Events

Capacity & Performance Management using MS-Excel

Capacitas Training Centre,
London, United Kingdom
3 Day

Price: £1,710 +VAT

Scheduled Dates:
26th to 28th Jun 2012

Learn how to use MS-Excel to do the following:

  • Pivot tables: Manipulating data quickly and effectively; Dealing with gaps in data; Summarising; Creating interval records from event records
  • Filtering: Auto Filter and Advance Criteria Filter
  • Forecasting functions; Linear regression with trend lines, Data Analysis wizard and ‘in cell’ functions
  • Automation: Creating Visual Basic modules through keyboard macros
  • Charts: Dealing with too much data; General tips
  • Lookup functions, Named Ranges, Dynamic Ranges
  • Batch modelling: Gantt charts
  • Develop performance and capacity models of OLTP and batch systems
  • Increase productivity by automating data analysis tasks
  • Automate the delivery of data and charts to the web
  • Improve the quality of your Excel solutions

Course Agenda

Day 1
09:00 - 9:15 Registration
9:15 - 9:30 Introduction
  • Excel - the ultimate modelling tool?
  • Excel Limits
  • Office 2007
9:30 - 10:50 Pivot tables and Filtering
  • Filtering (Auto Filter and Advanced Criteria)
  • Pivot Tables
10:50 - 11:10 Coffee/tea
11:10 - 12:30 Forecasting Functions
  • FORECAST, GROWTH, TREND
  • Linear Regression
12:30 - 13:30 Lunch
13:30 - 14:50 General Tips, Charts, Automation
  • Creating GIFs
  • Conditional Formatting
  • Increase Number of Undo Actions
  • Array Formula
  • Hiding Errors
  • Dealing with “too much” Data
  • Avoid Plotting Zero Values
  • User-defined Charts
  • Data Labels Different to Plotted Data
  • GIFs and HTML
14:50 - 15:10 Coffee/tea
15:10 - 16:30 General Tips, Charts, Automation (continued)
  • Dynamic Ranges
  • Smoothed Lines
  • Keyboard Macros
Look-up Data and Named Ranges
  • LOOKUP, VLOOKUP, HLOOKUP
  • OFFSET, MATCH, INDEX
Batch Modelling
  • Representation of Simple Dependencies
  • Dates and times
  • Lookup tables
  • Gantt Charts
  • Case study
16:30 - 16:55 Questions & Answer session
16:55 - 17:00 Summary and closing remarks
Day 2
9:00 - 9:30 Registration
09:30 - 10:00 Introduction
  • Visual Basic for Applications
  • Excel Product Versions
  • Macro Security
10:00 - 11:00 Writing Macros
  • Recording a Macro
  • Relative References
  • Debugging
11:00 - 11:15 Morning Break
11:15 - 12:15 Writing Macros (cont.)
  • Error handling
  • Event driven macros
12:15 - 13:15 Lunch
13:15 - 14:45 Writing User Defined Functions
  • Argument and function types
  • UDF performance issues
  • Volatile functions
  • Dynamic Ranges
14:45 - 15:15 Queuing Functions
  • Example: Erlang B blocking function
  • Example: Erlang C blocking function
  • Example: M/M/N queuing function
  • Example: M/G/1 queuing function
  • Example: M/G/N queuing function
15:15 - 15:30 Afternoon Break
15:30 - 16:15 Creating Menu Bar Items and Add-Ins
  • Menu bar items
  • Add-ins
16:15 - 17:00 Importing Data
  • Opening a file
  • String manipulation functions
  • Removing unnecessary and repeated headers from imported data
  • Example: Importing and analysing UNIX SAR data
  • Example: Importing and analysing Windows System Monitor data
  • Example: Importing and analysing W3C web logs
 
Day 3
9:30 - 10:30 Building a GUI
  • Adding navigation buttons
  • Cleaning worksheets and charts
10:30 - 11:00 Publishing to the Web
  • Publishing data
  • Publishing charts
11:00 - 11:15 Morning Break
11:15 - 12:15 Publishing to the Web (Cont.)
  • Publishing interactive content
12:15 - 13:15 Lunch
13:15 - 14:15 Building Capacity & Performance Models of Batch Systems
14:15 - 14:45 Building Capacity & Performance Models of OLTP Systems
14:45 - 15:00 Afternoon Break
15:00 - 17:00 Case Study: Modelling a Billing System

Sample Session

Click the icon below for recorded sessions from this course:

Capacitas Sample Session

Course Brief

Capacity and performance practitioners traditionally used SAS to undertake analysis and reporting. However, Microsoft Excel is widely available but its capabilities are often surprisingly underused, especially its ability to analyse and report on capacity and performance data.

This course gives practical advice on how to undertake a multitude of tasks using Excel. The course is platform independent, using various sources of data in demonstrations, and focuses on providing a value-for-money approach to capacity and performance analytical tools.

Prerequisites

  • None

Course Audience

  • Capacity Planners
  • Network Planners
  • Network Engineering
  • Performance Analysts
  • Technical Architects
  • System Architects

How Your Organisation Will Benefit

Attending this course you will learn how to:
  • Manipulate and agregate data
  • Conduct data analysis
  • Use Excel's inbuilt forecasting functions
  • Present results effectively using charts and graphs

Comments from Previous Attendees:

  • "Greatly extended my Excel knowledge"
  • "Lots of very good information"
  • "Covers all forecasting areas"
  • "Knowledgeable presenter"
  • "Good pace"
  • "Excellent explanation of linear regression"
  • "A good course covering the basic techniques in Excel and giving plenty of examples of how to use"

Feedback Scores for this Course

Scores on a range of 1 to 5, where 1 is completely dissatisfied and 5 is completely satisfied.

Course
Did the course meet your objectives4.3
Level of detail4.3
Quality of visual materials3.9
Quality of printed materials4.1
Relevance of course4.5
Pace of course4.1
Training facilities3.2
Presenters
Knowledge of material4.6
Quality of communication4.3
Understanding of subject4.6

Or consider an onsite event.
© Capacitas Ltd 2012 Privacy Policy Code of Professional Practice