Microsoft Excel for Advance

OVERVIEW If you have historical time-based data, you can use it to create a forecast. When you create a forecast, Excel creates a new worksheet that contains both a table of the historical and predicted values and a chart that expresses this data. A forecast can help you predict things like future sales, inventory requirements, or consumer trends. This training will focus how to using Ms. Excel to expand participant’s knowledge into forecasting and more specialised analytical functionality offered by Microsoft Excel. OUTLINE MATERI 1. What-If Analysis

• Goal Seek
• Data Table

2. Pivot Table

• Persiapan untuk Membuat Pivot Table
• Membuat Pivot Table

3. Advanced Chart

• Spin Button untuk Mengganti Jenis Chart
• Menandai Nilai Maksimum pada Chart
• Penanda Bergerak pada Chart
• Chart Dinamis

4. Plan a Forecast Model

• Which data can be forecasted?
• Set up the source data the right way to get an efficient model
• Decide which method is the right for your data or should I use more methods?
• How can I measure the accuracy of the forecast?
• Which measurement method is the best for my data?
• How can I track that I am using the right forecasting model over time
• Maintenance of the forecast models

5. Forecasting Methods

• Linear Regression
• Forecast Function
• Trend Function
• Slope & Intercept Function
• Exponential Regression
• Growth Function
• Exponential Smoothing
• Data Analysis Tool Exponential Smoothing
• Naïve Forecast
• Moving Average
• Data Analysis Tool Moving Average
• Seasonal Forecasting

6. Measuring Forecast Accuracy

• Forecast Error/Deviation
• Forecast Absolute Error/Deviation
• Forecast Percentage Error/Deviation
• Forecast absolute percentage error/deviation
• Square Error
• Standard Error
• MAD (Mean Absolute Deviation)
• MSQ (Mean Square Error)
• MPE (Mean Percentage Error)
• MAPE (Mean Absolute Percentage Error)
• TSE (Tracking Signal Error)

7. Using the Solver to Optimise Forecasts

• Optimise Exponential Smoothing Forecasts
• Optimise Weighted Moving Average Forecasts
• Optimise Seasonal Forecasts

8. Trends and Forecasting using Charts

• Chart Types
• Trendlines
• Equations
• R2
• Visualise Forecasts & Forecast accuracy

9. Comparing Forecasting Methods and Models 10. Manipulate Forecasts using What-If Analysis 11. Correlation Coefficient 12. Break-Even Analysis

• Break-Even Modelling
• Calculate Break-Even
• Visualise Break-Even
• Use Goal Seek to analyse Break-Even

13. Data Analysis Tools

• Descriptive Statistics
• Histogram
• Regression
• Sampling
• Rank & Percentile

TRAINING METHOD Presentation Discussion Case Study Evaluation FACILITY Training Kit Handout Certificate Lunch + 2x Coffee Break Souvenir Pick Up Participant (Yogyakarta)