Spreadsheet Skills for Planning, Forecasting and Budgeting

Courses Syllabus

Intorduction

This hands-on, practical course will demonstrate how you can develop spreadsheet models to create a forecast, which can then be utilised to generate business plans and operating budgets with the use Excel® as it is used in practice. The applications will be focused on three crucial aspects of business and financial management – strategic planning, intelligent forecasting and realistic budgeting. Delegates will learn how accessible the power of Excel® is to provide realistic forecasts and prepare flexible budgets. The impact on efficiency in planning and control and therefore return on investment for companies and/or divisions will be startling.

This course will feature:

• Mastery of the power of Excel®
• What functions to use from the Excel toolkit
• Principles of strategic planning and budgeting
• Mathematical forecasting models will be demystified
• Building financial models enabling “what if” analysis

Objectives

• Navigate through the features and functions of Excel®
• Distinguish between data and output in spreadsheet architecture
• Analyze financial statements in order to plan ahead
• Build effective financial planning models
• Use forecasting techniques for budget preparation

Content

Day One

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 Two

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 Three

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 Four

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 Five

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