{"id":2222,"date":"2016-01-29T12:30:17","date_gmt":"2016-01-29T12:30:17","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/?p=2222"},"modified":"2023-12-30T23:10:08","modified_gmt":"2023-12-30T23:10:08","slug":"forecasting-in-excel-2016","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/forecasting-in-excel-2016\/","title":{"rendered":"Forecasting in Excel 2016"},"content":{"rendered":"<p>Forecasting is important\u00a0in many\u00a0circumstances to be able to do effective and efficient planning.<\/p>\n<p class=\"BodyText\">The\u00a0future electricity needs\u00a0forecast vital\u00a0to planning building more\u00a0power stations; scheduling employees\u00a0in a call centre next week requires forecasts of call volume; just imaging how much forecasting the planners behind London 2012 Olympic Games must have done. Forecasts can be needed months or years in advance, or only a few minutes beforehand. Whatever the situations\u00a0or time horizons involved, accurate forecasts are vital\u00a0part of preparation and planning. Learn more about <a href=\"\/microsoft\/excel-training-london.php\">Excel training spreadsheet<\/a>.<\/p>\n<p class=\"BodyText\">A key step in forecasting is knowing if something can be forecast accurately, or if\u00a0it is better just tossing a coin. Good forecasts capture the authentic\u00a0patterns and relationships which exist in the historical data, but do not replicate events from the past that will not happen\u00a0again.<\/p>\n<p class=\"BodyText\">In all businesses\u00a0where data\u00a0are collected and employees\u00a0make use of the\u00a0data\u00a0the capability\u00a0to forecast may be required. It doesn&#8217;t really matter if\u00a0it is\u00a0sales figures, expenses, man-hours, growth, market shares etc. to create a budget you need to forecast.<\/p>\n<p class=\"BodyText\"><strong>WhatIf Analysis<\/strong><\/p>\n<p class=\"BodyText\">Excel offer a lot of tools to do accurate forecasts based on historical data and analysis tools as the <strong>What If Analysis\u00a0<\/strong>tools to help predict the future.<\/p>\n<p class=\"BodyText\">Some of the useful forecasting tools in Excel are:<\/p>\n<ul>\n<li>For trend analysis you can use the\u00a0<strong>Trend\u00a0<\/strong>function and to visualize and calculate trends, Excel charts can show trends and the equation for the trend. You can calculate the accuracy of the trend or Excel can provide you with this information in a chart. The\u00a0<strong>Trend\u00a0<\/strong>function will only return a accurate result if you work with linear data.<\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/02\/forecast-trend.png\"><img decoding=\"async\" class=\"alignnone size-large wp-image-2240\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/02\/forecast-trend-1024x492.png\" alt=\"forecast trend\" width=\"625\" height=\"300\" \/><\/a><\/p>\n<ul>\n<li>For forecasting of linear data\u00a0you can use the\u00a0<strong>Forecast<\/strong>\u00a0function. The <strong>Forecast\u00a0<\/strong>function can forecast any number of periods into the future.<\/li>\n<li>If you work with exponential data you can use the\u00a0<strong>Growth\u00a0<\/strong>function to forecast and an exponential trends can also be visualized in a Excel chart.<\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/02\/forecast-exponential.png\"><img decoding=\"async\" class=\"alignnone size-large wp-image-2242\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/02\/forecast-exponential-1024x364.png\" alt=\"forecast exponential\" width=\"625\" height=\"222\" \/><\/a><\/p>\n<ul>\n<li>the\u00a0<strong>Solver\u00a0<\/strong>tool in Excel can be a huge help if you want to forecast seasonal data.<\/li>\n<li>The\u00a0<strong>Analysis Toolpak\u00a0<\/strong>provide us with\u00a0<strong>Exponential Smoothing, Moving Average\u00a0<\/strong>and\u00a0<strong>Regression\u00a0<\/strong>which are all tools we need for creating the right forecast model.<\/li>\n<li>The\u00a0<strong>Scenario Manager\u00a0<\/strong>and the\u00a0<strong>Goal Seek\u00a0<\/strong>tools can be a great help to get a accurate forecast.<\/li>\n<li>Excel also has all the options and functions to measure the accuracy of our forecast to make it possible to continuously develop our forecast model to get a very precise forecast.<\/li>\n<\/ul>\n<h2>Forecasting\u00a0Chart<\/h2>\n<p>In Excel 2016 Microsoft has made it much easier to forecast. Microsoft has added the\u00a0<strong>Forecast Sheet\u00a0<\/strong>tool to Excel. This tool can give you a forecast in few seconds.<\/p>\n<p>In Excel 2016, select your two corresponding sets of data and go to \u201cData&gt;Forecast&gt;Forecast Sheet\u201d. In \u201cCreate Forecast Worksheet\u201d box, choose either a line chart or column chart, pick an end date, and then click \u201cCreate\u201d.<\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/02\/Forecast-Sheet-dialog.png\"><img decoding=\"async\" class=\"alignnone size-large wp-image-3156\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/02\/Forecast-Sheet-dialog-1024x600.png\" alt=\"Forecast Sheet dialog\" width=\"625\" height=\"366\" \/><\/a><\/p>\n<p>For more advanced options click \u201cOptions\u201d in \u201cCreate Forecast Worksheet\u201d. Here you can set \u201cConfidence Interval\u201d and set how you want Excel to handle seasonality in your data. You can include statistics in your forecast worksheet and change the input range if needed.<\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/02\/Forecast-Sheet-expanded-dialog.png\"><img decoding=\"async\" class=\"alignnone size-large wp-image-3158\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/02\/Forecast-Sheet-expanded-dialog-1024x758.png\" alt=\"Forecast Sheet expanded dialog\" width=\"625\" height=\"463\" \/><\/a><\/p>\n<p>In the \u201cFill Missing Points Using\u201d list, you can tell Excel how to handle missing data. \u201cInterpolation\u201d will calculate missing data.<\/p>\n<p>In the \u201cAggregate Duplicates Using\u201d list, select how you want Excel to calculate duplicate entries.<\/p>\n<p class=\"BodyText\">\u00a0<a href=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/02\/forecastETS-function.png\"><img decoding=\"async\" class=\"alignnone size-large wp-image-3157\" src=\"https:\/\/www.stl-training.co.uk\/b\/wp-content\/uploads\/2015\/02\/forecastETS-function-1024x342.png\" alt=\"forecastETS function\" width=\"625\" height=\"209\" \/><\/a><\/p>\n<p class=\"BodyText\">Best STL offer a <a href=\"https:\/\/www.stl-training.co.uk\/syl\/161\/excel-forecasting-and-data-analysis-training-course.html\">Forecasting &amp; Data<\/a> analysis course where all the different methods &amp; models are explored. It is a selection from our <a href=\"\/syl\/136\/office-365-end-user-training-course.html\">Office 365 training London<\/a>\u00a0<span data-sheets-value=\"{&quot;1&quot;:2,&quot;2&quot;:&quot;office 365 training london&quot;}\" data-sheets-userformat=\"{&quot;2&quot;:14851,&quot;3&quot;:{&quot;1&quot;:0},&quot;4&quot;:[null,2,16777215],&quot;12&quot;:0,&quot;14&quot;:[null,2,6121321],&quot;15&quot;:&quot;Lato, \\&quot;Helvetica Neue\\&quot;, Helvetica, Arial, sans-serif&quot;,&quot;16&quot;:11}\">courses.<\/span><\/p>\n<p class=\"BodyText\">Excel is an amazing tool also when you need to forecast.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Forecasting is important\u00a0in many\u00a0circumstances to be able to do effective and efficient planning. The\u00a0future electricity needs\u00a0forecast vital\u00a0to planning building more\u00a0power stations; scheduling employees\u00a0in a call centre next week requires forecasts of call volume; just imaging how much forecasting the planners behind London 2012 Olympic Games must have done. Forecasts can be needed months or years [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[23,4,9,12],"tags":[],"class_list":["post-2222","post","type-post","status-publish","format-standard","hentry","category-data-visualisation","category-excel-training","category-microsoft-office","category-office-2016"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2222","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=2222"}],"version-history":[{"count":4,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2222\/revisions"}],"predecessor-version":[{"id":3732,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2222\/revisions\/3732"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=2222"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=2222"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=2222"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}