98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsExcel Forecasting and Data Analysis
Face to face / Virtual public schedule & onsite training. Restaurant lunch included at STL venues.
Designed for Excel 365
(151 reviews, see all 98,621 testimonials) |
From £300 List price £350
- 1 day Instructor-led
- Courses never cancelled
- Restaurant lunch
Syllabus
Who is this course for?
This Microsoft Excel Forecasting & Data Analysis course is aimed at people who want to expand their knowledge into forecasting and more specialised analytical functionality offered by Microsoft Excel.
Learn more about the Excel training spreadsheet.
Prerequisites
Completion of our Excel advanced course or equivalent knowledge.
Benefits
At the end of this course you will understand how you can forecast in Excel using different methods based on seasonal, linear or exponential data. You will be able to calculate forecast errors to update your forecast model to assure more accurate forecasts. You will able to do trend analysis, break-even analysis, calculate correlation coefficient and creating statistical analysis using Excel's analysis toolpak.You will also be able to clearly visualise trends and forecasts in Excel charts.
This course is a part of a wider portfolio of our Excel software training courses.
Course Syllabus
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
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
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)
Using the solver to optimise forecasts
Optimise Exponential Smoothing Forecasts
Optimise Weighted Moving Average Forecasts
Optimise Seasonal Forecasts
Trends and forecasting using charts
Chart Types
Trendlines
Equations
R2
Visualise Forecasts & Forecast accuracy
Comparing Forecasting Methods and Models
Accuracy and visualisation
Manipulate Forecasts using What-If Analysis
Scenarios Manager
Goal Seek
Data Tables
Correlation Coefficient
Correl Function
The Data Analysis Tool Correlation
Display Correlation Coefficient in Scatter Chart
Break-Even Analysis
Break-Even Modelling
Calculate Break-Even
Visualise Break-Even
Use Goal Seek to analyse Break-Even
Data Analysis Tools
Descriptive Statistics
Histogram
Regression
Sampling
Rank & Percentile
Prices & Dates
What you get
"What do I get on the day?"
Arguably, the most experienced and highest motivated trainers.
Face-to-face training
Training is held in our modern, comfortable, air-conditioned suites.
Modern-spec IT, fully networked with internet access
Lunch, breaks and timing
A hot lunch is provided at local restaurants near our venues:
- Bloomsbury
- Limehouse
Courses start at 9:30am.
Please aim to be with us for 9:15am.
Browse the sample menus and view joining information (how to get to our venues).
Refreshments
Available throughout the day:
- Hot beverages
- Clean, filtered water
- Biscuits
Virtual training
Regular breaks throughout the day.
Learning tools
In-course handbook
Contains unit objectives, exercises and space to write notes
Reference material
Available online. 100+ pages with step-by-step instructions
24 months access to Microsoft trainers
Your questions answered on our support forum.
Training formats & Services
Training Formats & Services
|
Testimonials
Chelsea Football Club
Georgina Hogg,
Digital Anaylsis
I think the course could be split into two days, so clients have a little time to take things in. Over the two days, there could be a few simple tasks for the client to try by themselves then the trainer explains how to get the answer. Overall, a very helpful course and knowledgeable trainer. I will definitely be asking for ideas and help on the support forum. Thank you.
Excel Forecasting and Data Analysis
Wilsonart
Kalina Bellis,
Demand Planner
Great training, Jens has an amazing knowledge, the material will help to embed the knowledge
Excel Forecasting and Data Analysis
Kerridge CS
Amelia Rimington,
R&D Planning Administrator
Really enjoyed the course, feel like I have learnt new skills which I can put into practice at work. Possibly the best extracurricular course I have taken to date. Cannot think of anything you can do better unfortunately!
Excel Forecasting and Data Analysis
Training manual sample
Below are some extracts from our Excel training manuals.
Linear Regression
Discussion
Linear Regression analysis is a statistical technique for estimating the relationships among variables. In other words, how do the sales figures change over time?
If the goal is prediction, or forecasting, linear regression can be used to fit a predictive model to an observed data set of y and x values or known actual data (y) over time (x) (time series data). After developing such a model, if an additional value ofx (a new period) is then given without its accompanying value of y, the fitted model can be used to make a prediction of the value of y.
The sales figures (y) are known for a number of periods (x) it makes it possible forecasting sales (y) for future periods (x).
In Excel the linear regression can be calculated using the Forecast function, The Trend function, the Fill-Handle, by calculating the equation: Y = mX + c, and by adding a Trendline to a chart.
The Data Analysis Tool Regression is an analysis tool to return important information if you are working with Linear Regression such as the Slope, the Y-Interceptor,R-square, and other statistical information. The different terms will be explained later in this workbook.
You can also calculate the Slope and the Y Interceptor using the functions Slope and Intercept.
The Forecast function
Discussion
Microsoft Excel provides a variety of functions you can use to calculate forecasts. One of them is the Forecast function. The Forecast function can calculate trend, a linear forecast, or linear regression for as many future periods needed based on data from previous periods.
The Forecast function consists of three required arguments, in the following order: X, Known_y’s, and Known_x’s. X is the period for which you want to calculate the forecast. Known_y’s is the array with the known values. Known_x’s is the array with the known periods (must be a numeric range and not dates, months or years).
Procedures
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The Trend function
Discussion
The Trend can calculate trend, a linear forecast, or linear regression for as many future periods needed based on data from previous periods.
The Trend function is an array function and the steps to use it are different from “normal” functions in Excel. The whole range where you want the result to be displayed must be selected and after the needed arguments have been entered the keys Ctrl Shift Enter must be pressed instead of pressing Enter. Then the function will return the result for the whole range in the selected range.
The Trend function consists of one required argument and three optional arguments, in the following order: Known_y’s, Known_x’s, New_x’s, and Const. Known_y’s is the array with the known values. Known_x’s is the array with the known periods (must be a numeric range and not dates, months or years). New_x’s is the array with future periods if the Trend function is used for forecasting. Const is a logical value specifying whether to force the constant b to equal 0.
Procedures
|
|
|
|
|
|
|
|
|
|
|
The array functions will add the result to the whole selected array and will look different in the formula bar. Excel will display the formula enclosed in curly brackets { }.
If the Trend function is used to forecast:
Procedures
|
|
|
|
|
|
|
|
|
|
|
|
& |
The Trend function will return exactly the same result as the forecast function. |
The Slope and Intercept Function
Discussion
To make it easy to calculate trend or forecast using the equation Y = mX + c you need the slope and y interceptor.
The Slope function consists of two required arguments, in the following order: Known_y’s, and Known_x’s. Known_y’s is the array with the known values. Known_x’sis the array with the known periods (must be a numeric range and not dates, months or years).
Procedures
|
|
|
|
|
|
|
|
The Intercept function consists of two required arguments, in the following order: Known_y’s, and Known_x’s. Known_y’s is the array with the known values. Known_x’s is the array with the known periods (must be a numeric range and not dates, months or years). |
Procedures
|
|
|
|
|
|
|
|
Now you can calculate the trend and forecast using the trend/forecast equation: |
Procedures
|
|
|
|
|
The Forecast function, the Trend function and the trend/forecast equation will return exactly the same result and it does not really matter which method used.
|
.
Exponential Regression
Discussion
Sometimes the growth in a model is not linear, but it is exponential. If the growth is exponential Excel has forecasting tools to replace the Forecast and Trend function.
In the example below the sales has an exponential growth rate. The Forecast function forecast period 11 to 2005333.333. This is not a realistic forecast, because the known sale for period 10 is already £ 2,200,000.00.
In this section, you will see how you can forecast an exponential growth.
The Growth function
Discussion
The Growth function can calculate exponential growth and exponential growth forecast. The Growth function is an array function and the steps to use it are different from “normal” functions in Excel. The whole range where you want the result to be displayed must be selected and after the needed arguments have been entered the keys Ctrl Shift Enter must be pressed instead of pressing Enter. Then the function will return the result for the whole range in the selected range.
The Growth function consists of one required argument and three optional arguments, in the following order: Known_y’s, Known_x’s, New_x’s, and Const. Known_y’s is the array with the known values. Known_x’s is the array with the known periods (must be a numeric range and not dates, months or years). New_x’s is the array with future periods if the Growth function is used for forecasting. Const is a logical value specifying whether to force the constant b to equal 0.
Procedures
|
|
|
|
select a category: box. |
|
|
|
|
|
|
The array functions will add the result to the whole selected array and will look different in the formula bar. Excel will display the formula enclosed in curly brackets { }.
If the Growth function is used to forecast:
Procedures
|
|
|
|
|
|
|
|
|
|
|
|
If you compare the Growth function with the Forecast function in a chart it is obvious that in this example you get a much more accurate forecast using the Growth function.
Thanks. Your download will begin shortly.
Please help us
Share or create a link to this manual today!
Just follow these simple instructions...