Module 1: Data Management, Manipulation and Analysis using Excel®
Day One: An Introduction to the MS Excel Environment
- Cell referencing, cell formatting and entering formula
- Workbooks versus Worksheets
- Copy and pasting
- Left click versus right click
- Paste Special
- Introductory charts
Day Two: Using MS Excel Functions for Fundamental Data Analysis
- Use of text function, FIND(), LEN(), LEFT(), RIGHT() and &
- Use of count functions, COUNTA(), COUNTIF(), COUNTIFS() and SUMIF()
- Basic statistical functions, Max and Average
- Filtering, sorting and use of conditional formatting
- Scatter diagrams
Day Three: Intermediate MS Excel Functions
- Use of VLOOKUP() and HLOOKUP()
- Date functions, YEAR(), MONTH(), DAY(), YEARFRAC()
- Selecting appropriate charts
- Introduction to Pivot tables
Day Four: Carrying out Statistical Analysis using MS Excel
- Using MS Excel to calculate mean, mode and median
- The difference between the various standard deviation and variance function in MS Excel
- Using MS Excel to examine inter-dependency
- Drawing histograms in MS Excel
- Introduction to Data Analysis functions
Day Five: What if and Scenario Analysis Using MS Excel
- Naming cells in MS Excel
- Linking cells together to undertake scenario analysis
- Introduction to solver
- Advanced charting
- Sharing MS Excel output with other office formats
Module 2: Spreadsheet Skills for Planning, Forecasting & Budgeting
Day Six: Introduction to Spreadsheets using Excel®
- The power of Excel® for building financial models
- The Ribbons of Excel with their commands and functions
- Using formulae: Copying, anchoring and special pasting
- Using functions: financial, statistical and mathematical
- Review of the financial objectives of business: ROI, ROA, ROE
- Overview of Financial Statements
Case Study: Building a Quick Access Toolbar in Excel® and applying it to the analysis of financial statements of a division.
Day Seven: Proper Planning
- Classical strategic planning models
- Cost-Volume-Profit Analysis and Break-Even as a planning example
- Economic Order Quantity as a planning example
- What-if analysis to build scenario's and test sensitivity
- Maximizing and optimizing techniques
- Linear programming and Solver as optimising tools
Case Study: Preparing a planning model and subjecting it to a range of sensitivity analysis in a manufacturing environment.
Day Eight: Fantastic Forecasting
- Forecasting in perspective - the Past vs. the Future
- Necessity to apply a range of different forecasting methods:
- Qualitative Models used in forecasting
- Quantitative Models focussing on time series and regressions methodology
- Forecasting growth rates
- Recording, applying and modifying forecast assumptions
Case Study: Applying the forecasting functions in Excel® to past data and building a model offering various scenario's
Day Nine: Beyond Budgeting
- The budget process: Timing and Cycles
- Setting budgeting objectives and tolerance levels
- Budgeting Techniques
- "Beyond Budgeting" compared to traditional budgeting principles
- Operating and Capital budgets
- Monthly reporting procedures and timely action
Case Study: Building budget based on assumptions - Operating Budget, Cash Budget & Capital Budget
Day Ten: Putting it Together – Building the Comprehensive Model
- Considering the financing mix in strategy
- Considering the Return to Shareholder as the primary indicator
- Build your planning model
- Build your forecasting model
- Build your budgeting model
- Link these together in review
Case Study: Building an integrated planning, forecasting & budgeting model