Data Management, Planning, Forecasting & Budgeting using Excel®

• Cell referencing, cell formatting and entering formula
• Workbooks versus Worksheets
• Copy and pasting
• Left click versus right click
• Paste Special
• Introductory charts

• 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

• Use of VLOOKUP() and HLOOKUP()
• Date functions, YEAR(), MONTH(), DAY(), YEARFRAC()
• Selecting appropriate charts
• Introduction to Pivot tables

• 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

• 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

• 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.

• 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.

• 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

• 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

• 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