Data Management, Planning, Forecasting
& Budgeting using Excel^®

• Referência a células, formatação de células e introdução de fórmulas
• Pastas de trabalho versus folhas de cálculo
• Copiar e colar
• Clique esquerdo versus clique direito
• Colar especial
• Gráficos introdutórios

• Utilização de funções de texto, FIND(), LEN(), LEFT(), RIGHT() e &
• Utilização das funções de contagem, COUNTA(), COUNTIF(), COUNTIFS() e SUMIF()
• Funções estatísticas básicas, Máximo e Médio
• Filtragem, ordenação e utilização de formatação condicional
• Diagramas de dispersão

• Utilização de VLOOKUP() e HLOOKUP()
• Funções de data, YEAR(), MONTH(), DAY(), YEARFRAC()
• Seleção de gráficos adequados
• Introdução às tabelas dinâmicas

• Utilizar o MS Excel para calcular a média, a moda e a mediana
• A diferença entre as várias funções de desvio padrão e variância no MS Excel
• Utilização do MS Excel para examinar a interdependência
• Desenhar histogramas no MS Excel
• Introdução às funções de análise de dados

• Nomear células no MS Excel
• Ligação de células para efetuar análises de cenários
• Introdução ao solver
• Gráficos avançados
• Partilhar a saída do MS Excel com outros formatos de escritório

• 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