业务用户的数据科学

最新推荐文章于 2024-10-01 09:29:45 发布

李_涛

最新推荐文章于 2024-10-01 09:29:45 发布

阅读量411

点赞数

文章标签： java python 大数据人工智能小程序 ViewUI

原文链接：https://medium.com/swlh/data-science-for-business-users-f4c050cbec96

版权

预测第1.1部分-使用Excel 2016/2019创建预测 (Forecasting Part 1.1 — Create Forecast using Excel 2016/2019)

This tutorial was created to democratize data science for business users (i.e., minimize usage of advanced mathematics topics) and alleviate personal frustration we have experienced on following tutorials and struggling to apply that same tutorial for our needs. In light of this, our mission is as follows:

创建本教程的目的是使业务用户的数据科学民主化(即，最大限度地减少高级数学主题的使用)，并减轻我们在后续教程中遇到的个人挫败感，并努力将同一教程应用于我们的需求。 鉴于此，我们的任务如下：

Provide practical application of data science tasks with minimal usage of advanced mathematical topics
以最少的高级数学主题提供数据科学任务的实际应用
Only use a full set of data, which are similar to data we see in business environment and that are publicly available in a tutorial, instead of using simple data or snippets of data used by many tutorials
仅使用与我们在业务环境中看到的数据相似并且在教程中公开可用的全套数据，而不是使用简单数据或许多教程使用的数据片段
Clearly state the prerequisites at beginning of the tutorial. We will try to provide additional information on those prerequisites
在教程开始时清楚地说明先决条件。 我们将尝试提供有关这些先决条件的其他信息
Provide both written tutorial and video tutorial on each topic to ensure all steps are easy to follow and clearly illustrated
提供有关每个主题的书面教程和视频教程，以确保所有步骤都易于遵循并清楚地说明

1.说明 (1. Description)

This is part one of series on how to create a forecast, using one of the most widely used data science tool — Excel. If you are surprised by this statement, then we refer you to www.kdnuggets.com (one of the most widely followed blog for data science). They conduct annual poll on tools and according to the poll, Excel is one of the most widely used tool by data scientists (https://www.kdnuggets.com/2018/05/poll-tools-analytics-data-science-machine-learning-results.html). If you have a question about using Excel, just ask any data scientist in your organization. They are most likely to be Excel expert and they will probably be able to assist you.

这是有关如何使用最广泛使用的数据科学工具之一-Excel创建预测的系列文章之一。如果您对此声明感到惊讶，那么我们会将您引荐至www.kdnuggets.com (这是数据科学领域追踪最广泛的博客之一)。他们对工具进行年度调查，根据调查，Excel是数据科学家使用最广泛的工具之一( https://www.kdnuggets.com/2018/05/poll-tools-analytics-data-science-machine -learning-results.html )。如果您对使用Excel有疑问，只需询问组织中的任何数据科学家。他们很可能是Excel专家，他们可能会为您提供帮助。

Forecasting is the process of making predictions of the future based on past and present data and its trend. The accuracy of forecast decreases as you stretch out your forecast. For example, if you are forecasting monthly sales then accuracy of forecast for month 1 sales of forecast will be higher than month 2 sales of forecast and so on. One of my co-worker likes to state that best way to predict tomorrow’s weather is to assume it is similar to today’s weather. Everything else is just a guess.

预测是根据过去和现在的数据及其趋势对未来进行预测的过程。延伸预测时，预测的准确性会降低。例如，如果您正在预测月度销售，则预测的第一个月的销售的预测准确性将高于预测的第二个月的销售，依此类推。我的一位同事喜欢指出，预测明天天气的最好方法是假设它与今天的天气相似。其他所有只是猜测。

Forecasting Series consists of:

预测系列包括：

Part 1.1— Create Forecast using Excel 2016/2019
第1.1部分—使用Excel 2016/2019创建预测
Part 1.2 — Advanced Topics on Forecast using Excel 2016/2019
第1.2部分-使用Excel 2016/2019进行预测的高级主题
Part 2.1 — Create Forecast using Python — ARIMA (https://medium.com/@sungkim11/create-forecast-using-python-arima-part-2-1-efca5c5c14f3)
第2.1部分-使用Python创建预测-ARIMA( https://medium.com/@sungkim11/create-forecast-using-python-arima-part-2-1-efca5c5c14f3 )
Part 2.2 — Advanced Topics on Forecast using Python — ARIMA
第2.2部分-使用Python进行预测的高级主题-ARIMA
Part 2.3 — Extend Forecast (Python) to include What-If Analysis Capabilities — ARIMA
第2.3部分-扩展预测(Python)以包括假设分析功能-ARIMA
Part 31 — Create Forecast using Python — Prophet (https://medium.com/@sungkim11/create-forecast-using-python-prophet-3-1-5ea64c3b103b)
第31部分-使用Python创建预测-先知( https://medium.com/@sungkim11/create-forecast-using-python-prophet-3-1-5ea64c3b103b )
Part 3.2 —Advanced Topics on Forecast using Python — Prophet
第3.2部分—使用Python进行预测的高级主题—先知
Part 3.3 — Extend Forecast (Python) to include What-If Analysis Capabilities — Prophet
第3.3部分-扩展预测(Python)以包括假设分析功能-先知
Part 4.1 — Create Forecast using Python — LSTM (https://medium.com/@sungkim11/create-forecast-using-python-lstm-4-1-1ab8b138a08f)
第4.1部分-使用Python创建预测-LSTM( https://medium.com/@sungkim11/create-forecast-using-python-lstm-4-1-1ab8b138a08f )
Part 4.2 — Advanced Topics on Forecast using Python — LSTM
第4.2部分—使用Python进行预测的高级主题— LSTM
Part 4.3 — Extend Forecast (Python) to include What-If Analysis Capabilities — LSTM
第4.3部分—扩展预测(Python)以包括假设分析功能— LSTM

2.先决条件 (2. Prerequisites)

Following are prerequisites for this tutorial:

以下是本教程的先决条件：

- [x] Excel 2016/2019
- [x] Basic knowledge of Excel 2016/2019
- [x] Historical data with same frequency (e.g., hourly, daily, weekly, monthly, quarterly, yearly, etc.), to create a forecast. This is important since you cannot create a forecast without historical data that does not have same frequency. If your data does not follow same frequency then aggregate your data so it will be same frequency. For example, if your data consists of any random two days per week then aggregate (i.e., sum up those two days) your data into a weekly data then create a forecast using aggregated data.

3.步骤 (3. Steps)

Please follow the step by step instructions, which is divided into 8 major steps as shown below:

请按照分步说明进行操作，该说明分为8个主要步骤，如下所示：

Get Data
获取数据
Format Data
格式化数据
Cleanse Data
清理数据
Analyze Data
分析数据
Prep Data
准备数据
Create Forecast
创建预测
Validate Forecast
验证预测
Maintain Forecast
维持预测

3.1。获取数据 (3.1. Get Data)

United Stated Census Bureau maintains Monthly Retail Trade Report, from January 1992 to Present. This data was picked to illustrate forecasting because it has extensive historical data with same monthly frequency. Data is available as Excel spreadsheet format at https://www.census.gov/retail/mrts/www/mrtssales92-present.xls

美国人口普查局维护从1992年1月至今的每月零售贸易报告。选择该数据是为了说明预测，因为它具有每月频率相同的大量历史数据。数据以Excel电子表格格式提供，网址为https://www.census.gov/retail/mrts/www/mrtssales92-present.xls

3.1.1. Click on the link to save Excel spreadsheet to your local directory/folder.

3.1.1。单击链接将Excel电子表格保存到本地目录/文件夹。

3.1.2. Open the Excel spreadsheet (i.e., Monthly Retail Trade Report).

3.1.2。打开Excel电子表格(即每月零售贸易报告)。

3.1.3. Monthly Retail Trade Report is organized by year where each year from 1992 to 2018 are separated by worksheet. Within each worksheet, there are two different types of figures — not adjusted and adjusted. For each type, there is summary set of figures followed by more detailed figure, organized by NAICS Code (i.e., North American Industry Classification System — the standard used by Federal statistical agencies in classifying business establishments for the purpose of collecting, analyzing, and publishing statistical data related to the U.S. business economy.) as shown below.

3.1.3。每月零售贸易报告按年份组织，其中1992年至2018年之间每年都按工作表分开。在每个工作表中，有两种不同类型的数字-未调整和已调整。对于每种类型，都有一组摘要图，然后是更详细的图，这些图由NAICS代码组织(即北美行业分类系统，这是联邦统计机构用于对企业进行分类以收集，分析和发布的标准)与美国商业经济有关的统计数据。)如下所示。

3.2。格式化数据¶ (3.2. Format Data¶)

We will need to format the data in Monthly Retail Trade Report, so we can create a forecast from consolidated multiple years of data. At the same time, this data is bit more extensive then we would like, so we will be filtering data as follow:

我们将需要在“每月零售贸易报告”中格式化数据，以便我们可以根据合并的多年数据创建预测。同时，此数据比我们想要的要广泛得多，因此我们将按以下方式过滤数据：

Use January 2005 to Present time to ensure cyclic behavior (full economic cycle with boom and recession) is represented in our data
使用2005年1月来表示时间，以确保周期性行为(充满景气和衰退的整个经济周期)体现在我们的数据中
Use “NOT ADJUSTED” data as illustrated on cell line 7 to line 12 on the spreadsheet. Other data is nice, but it is bit much for our needs
使用电子表格上第7行到第12行中所示的“ NOT ADJUSTED”数据。其他数据很好，但是可以满足我们的需求

3.2.1. Insert a new worksheet, entitled “Forecast”.

3.2.1。插入一个新的工作表，标题为“ Forecast”。

3.2.2. Copy and paste data from 2005 worksheet into “Forecast” worksheet. When pasting data, use “Transpose” option on Paste. It is easier to scroll up and down then scroll sideway to see the data.

3.2.2。将2005年工作表中的数据复制并粘贴到“预测”工作表中。粘贴数据时，请在“粘贴”上使用“移调”选项。上下滚动然后横向滚动查看数据会更容易。

3.2.3. Repeat the step 3.2.2 for 2006 thru 2018.

3.2.3。对2006年到2018年重复步骤3.2.2。

3.2.4. Copy and paste column label at top of pasted data. Again when pasting data, use “Transpose” option on paste.

3.2.4。在粘贴的数据顶部复制并粘贴列标签。再次粘贴数据时，在粘贴上使用“转置”选项。

3.2.5. Insert date column at left of pasted data, start with 01/01/2005 on first row then 02/01/2005 on second row then fill the rows with date. The end date should be 10/01/2018 as shown below:

3.2.5。在粘贴数据的左侧插入日期列，从第一行的01/01/2005开始，然后从第二行的02/01/2005开始，然后用日期填充行。结束日期应为2018年10月1日，如下所示：

3.2.6. Save the spreadsheet as mrtssales92-present_step2.xlsx.

3.2.6。将电子表格另存为mrtssales92-present_step2.xlsx。

3.3。清理数据 (3.3. Cleanse Data)

After data has been formatted, we will need to cleanse data. There is a truism in saying that Garbage in Garbage out. Simple thing like if all numbers are stored as number needs to be checked.

数据格式化后，我们将需要清理数据。说垃圾在垃圾里是不言而喻的。简单的事情，例如是否所有数字都存储为数字需要检查。

3.3.1. Cleanse Data. Ensure all numbers are stored as number, not text. Same applies to both date and text. In addition, ensure all numbers, dates and text are consistent. For example, the number is not stored as number, but as text — for example 121K instead of 121,000. You will need to cleanse the data to ensure all numbers are stored as number. Formatted Monthly Retail Trade Report does not seem to have any dirty data so this step is not need.

3.3.1。清理数据。确保所有数字都存储为数字，而不是文本。日期和文本均相同。此外，请确保所有数字，日期和文本均一致。例如，数字不是存储为数字，而是存储为文本-例如121K而不是121,000。您将需要清理数据以确保所有数字都存储为数字。格式化的每月零售贸易报告似乎没有任何脏数据，因此不需要此步骤。

3.4。分析数据 (3.4. Analyze Data)

After data has been formatted, we will be analyzing data to look for some specific items. Those items are:

格式化数据后，我们将分析数据以查找某些特定项目。这些项目是：

Missing Data. It would be nice to have all data filled-in, but in real-life that is not always the case. We will need to identify all missing data and denote as such.
缺失数据。填写所有数据会很好，但是在现实生活中并非总是如此。我们将需要识别所有丢失的数据并以此表示。
Outliers. Outliers happens. It would be nice to include them, but it will skew our forecast without additional benefits. We will need to identify all outliers and denote as such.
离群值。发生异常值。将它们包括在内会很不错，但会在没有其他好处的情况下歪曲我们的预测。我们将需要识别所有异常值并以此表示。
Seasonality. It is a characteristic of data in which data experiences regular and predictable changes which occur every year. This is important since if the historical data has seasonality then our forecast also needs to reflect this seasonality.
季节性。它是数据的特征，其中数据会经历每年定期发生且可预测的更改。这很重要，因为如果历史数据具有季节性，那么我们的预测也需要反映这种季节性。
Cyclic Behavior. It takes place when there are regular fluctuations in the data which usually last for an interval of at least two years, such as economic recession or economic boom.
循环行为。当数据定期波动至少持续两年(例如经济衰退或经济繁荣)时，就会发生这种情况。

3.4.1. Missing Data. Formatted Monthly Trade Report seems to be fully populated, so this step is not need.

3.4.1。缺失数据。格式化的每月贸易报告似乎已完全填充，因此不需要此步骤。

3.4.2. Outliers. Simplest way to detect outliers is to create a line chart of the data as shown below since the data points are limited in scope. Formatted Monthly Retail Trade Report seems to be consistent from year to year, so this step is not need.

3.4.2。离群值。检测异常值的最简单方法是创建数据折线图，如下所示，因为数据点的范围有限。格式化的每月零售贸易报告似乎每年都保持一致，因此不需要此步骤。

3.4.2.1. Open Excel spreadsheet, entitled “mrtssales92-present_step1_2.xlsx” and rename the file to “mrtssales92-present_step1_4.xlsx”. Step 3 was skipped since we did not make any changes in step 3.

3.4.2.1。打开名为“ mrtssales92-present_step1_2.xlsx”的Excel电子表格，然后将文件重命名为“ mrtssales92-present_step1_4.xlsx”。由于我们未在步骤3中进行任何更改，因此跳过了步骤3。

3.4.2.2. To create a line chart as shown below, select all data or cell range A1:G167 then select Insert | Insert Line Chart or Area Chart.

3.4.2.2。要创建如下所示的折线图，请选择所有数据或单元格范围A1：G167，然后选择“插入| 插入折线图或面积图。

3.4.2.3. Create a new worksheet, entitled “Analysis”.

3.4.2.3。创建一个名为“ Analysis”的新工作表。

3.4.2.4. Cut and paste newly created line chart to “Analysis” worksheet.

3.4.2.4。将新创建的折线图剪切并粘贴到“分析”工作表中。

3.4.3. Seasonality. Simplest way to detect seasonality is to create a line chart for each of labeled data as shown below since the data points are limited in scope.

3.4.3。季节性。检测季节性的最简单方法是为每个标记数据创建一个折线图，如下所示，因为数据点的范围有限。

3.4.3.1. Retail and food services sales, total’s seasonality seems be consistent from year to year where sales peak at November/December followed by sales dip in January/February followed by sales recovery in March/April and small dip in sales in September/October. Our forecast need to reflect this seasonality. Copy and paste Monthly Retail Trade Report and modify the line chart by deleting all other lines except for Retail and food services sales, total as shown below.

3.4.3.1。零售和食品服务销售的年度季节性总体上似乎是一致的，在11月/ 12月的销售达到顶峰，随后在1月/ 2月的销售下降，接着在3月/ 4月的销售回升以及9月/ 10月的销售小幅下降。我们的预测需要反映出这种季节性。复制并粘贴每月零售贸易报告，并通过删除除零售和食品服务销售总额以外的所有其他折行来修改折线图，如下所示。

3.4.3.2. Retail and food services excl gasoline stations’ seasonality seems be consistent from year to year where sales peak at November/December followed by sales dip in January/February followed by sales recovery in March/April and small dip in sales in September/October. Our forecast need to reflect this seasonality. Copy and paste Monthly Retail Trade Report modify the line chart by deleting all other lines except for Retail and food services excl gasoline stations as shown below.

3.4.3.2。零售和食品服务除外加油站的季节性似乎每年都保持一致，11月/ 12月的销售达到顶峰，1月/ 2月的销售下降，3月/ 4月的销售回升，9月/ 10月的销售小幅下降。我们的预测需要反映出这种季节性。复制并粘贴每月零售贸易报告，通过删除除零售和食品服务除外加油站之外的所有其他行来修改折线图，如下所示。

3.4.3.3. Retail sales, total’s seasonality seems be consistent from year to year where sales peak at November/December followed by sales dip in January/February followed by sales recovery in March/April and small dip in sales in September/October. Our forecast need to reflect this seasonality. Copy and paste Monthly Retail Trade Report modify the line chart by deleting all other lines except for Retail sales as shown below.

3.4.3.3。零售销售的总体季节性似乎与每年的情况一致，11月/ 12月的销售达到顶峰，1月/ 2月的销售下降，3月/ 4月的销售回升，9月/ 10月的销售小幅下降。我们的预测需要反映出这种季节性。复制并粘贴每月零售贸易报告，通过删除零售以外的其他所有行来修改折线图，如下所示。

3.4.3.4. Retail sales and food services excl motor vehicle and parts’ seasonality seems be consistent from year to year where sales peak at November/December followed by sales dip in January/February followed by sales recovery in March/April and small dip in sales in September/October. Our forecast need to reflect this seasonality. Copy and paste Monthly Retail Trade Report modify the line chart by deleting all other lines except for Retail sales and food services excl motor vehicle and parts as shown below.

3.4.3.4。零售和食品服务(不包括汽车和零部件)的季节性似乎每年都保持一致，11月/ 12月的销售达到顶峰，1月/ 2月的销售下降，3月/ 4月的销售回升，9月的销售略有下降。十月。我们的预测需要反映出这种季节性。复制并粘贴每月零售贸易报告，通过删除除零售和餐饮服务除外的汽车和零件以外的所有其他行来修改折线图，如下所示。

3.4.3.5. Retail sales and food services excl motor vehicle and parts and gasoline stations’ seasonality seems be consistent from year to year where sales peak at November/December followed by sales dip in January/February followed by sales recovery in March/April and small dip in sales in September/October. Our forecast need to reflect this seasonality. Copy and paste Monthly Retail Trade Report modify the line chart by deleting all other lines except for Retail sales and food services excl motor vehicle and parts and gasoline stations as shown below.

3.4.3.5。零售和食品服务(不包括汽车及零部件和加油站)的季节性似乎每年都保持一致，11月/ 12月的销售达到顶峰，随后在1月/ 2月的销售下降，接着在3月/ 4月的销售恢复，并且销售量小幅下降在九月/十月。我们的预测需要反映出这种季节性。复制并粘贴月度零售贸易报告，通过删除除零售和食品服务(不包括汽车，零部件和加油站)之外的所有其他行来修改折线图，如下所示。

3.4.3.6. Retail sales, total (excl. motor vehicle and parts dealers)’s seasonality seems be consistent from year to year where sales peak at November/December followed by sales dip in January/February followed by sales recovery in March/April and small dip in sales in September/October. Our forecast need to reflect this seasonality. Copy and paste Monthly Retail Trade Report modify the line chart by deleting all other lines except for Retail sales, total (excl. motor vehicle and parts dealers) as shown below.

3.4.3.6。零售总额(不包括汽车和零件经销商)的季节性似乎每年都保持一致，11月/ 12月的销售达到顶峰，1月/ 2月的销售下降，3月/ 4月的销售回升，2月的下降很小。 9月/ 10月的销售。我们的预测需要反映出这种季节性。复制并粘贴每月零售贸易报告，通过删除零售，总额(不包括汽车和零件经销商)以外的所有其他行来修改折线图，如下所示。

3.4.4. Cyclic Behavior. Simplest way to detect if the data reflects cyclic behavior is to create a line chart for each of labeled data as shown above since the data points are limited in scope. As you can see from above charts, the data reflects cyclic behavior where there was economic boom between 2005 thru 2006, followed by economic recession between 2007 thru 2009, followed by gradual increase in sales figure between 2010 thru 2015 then economic boom from 2016 to present.

3.4.4。循环行为。如上所述，检测数据是否反映循环行为的最简单方法是为每个标记的数据创建一个折线图，因为数据点的范围受到限制。从上面的图表中可以看到，数据反映了周期性行为，即2005年至2006年间出现了经济繁荣，随后是2007年至2009年之间出现了经济衰退，随后是2010年至2015年之间的销售数字逐渐增加，然后是2016年至今的经济繁荣。

3.4.5. Save the spreadsheet as mrtssales92-present_step1_4.xlsx.

3.4.5。将电子表格另存为mrtssales92-present_step1_4.xlsx。

3.5。准备数据 (3.5. Prep Data)

We will need to prep data to ensure we only use clean data to create our forecast. Some of the basic data prep tasks are:

我们将需要准备数据以确保仅使用干净数据来创建预测。一些基本的数据准备任务是：

Remove data outliers. Let say for one month, your sales doubled or tripled due to once in lifetime promotion. This is nice data point to consider, but it will skew our forecast without providing any value. We will need to cap and floor our data to ensure outliers are removed.
删除数据异常值。假设一个月内，由于终身促销一次，您的销售额翻了一番或三倍。这是可以考虑的很好的数据点，但是它会扭曲我们的预测而没有提供任何价值。我们将需要对数据进行上限和下限以确保消除异常值。
Impute missing data. Sometimes, some of the data are just missing for whatever the reason. If the percentage of missing value is low then you can impute that missing data.
估算缺少的数据。有时，无论出于何种原因，一些数据都会丢失。如果缺失值的百分比较低，则可以估算该缺失数据。

3.6。创建预测 (3.6. Create Forecast)

We spent a lot of time getting data, formatting data, cleansing data, analyzing data and finally prepping data. In data science, it is normal to spend between 80 to 90 percent of your time performing above tasks. Creating and validating a forecast takes fraction of time of all the data steps.

我们花费了大量时间来获取数据，格式化数据，清理数据，分析数据并最终准备数据。在数据科学中，通常花费80％至90％的时间来完成上述任务。创建和验证预测需要花费所有数据步骤的时间的一部分。

3.6.1. First, we will be diving data into two sets of data:

3.6.1。首先，我们将数据分为两组数据：

Train Data
火车数据
Test Data
测试数据

Usually we use 70 Train/30 Test (70/30) or 80 Train/20 Test (80/20) split where train data is used to create a forecast and test data is used to validate the forecast, but for simplification purposes, we will split the data as follows:

通常我们使用70 Train / 30 Test(70/30)或80 Train / 20 Test(80/20)split，其中火车数据用于创建预测，测试数据用于验证预测，但是为了简化起见，我们使用将按以下方式拆分数据：

Train Data: January 2005 thru December 2016
火车数据：2005年1月至2016年12月
Test Data: January 2017 thru October 2018
测试数据：2017年1月至2018年10月

3.6.2. After we have determined data split, we will use Excel’s FORECAST.ETS function, which calculates or predicts a future value based on existing (historical) values by using the AAA version of the Exponential Smoothing (ETS) algorithm. The predicted value is a continuation of the historical values in the specified target date, which should be a continuation of the timeline.

3.6.2。确定数据拆分之后，我们将使用Excel的FORECAST.ETS函数，该函数通过使用AAA版本的指数平滑(ETS)算法根据现有(历史)值来计算或预测未来值。预测值是指定目标日期中历史值的延续，应该是时间轴的延续。

3.6.3. Open Excel spreadsheet, entitled “mrtssales92-present_step1_4.xlsx” and rename the file to “mrtssales92-present_step1_6.xlsx”. Step 5 was skipped since we did not make any changes in step 5.

3.6.3。打开名为“ mrtssales92-present_step1_4.xlsx”的Excel电子表格，并将文件重命名为“ mrtssales92-present_step1_6.xlsx”。由于我们未在步骤5中进行任何更改，因此跳过了步骤5。

3.6.4. Navigate to “Forecast” worksheet and add new columns:

3.6.4。导航到“预测”工作表并添加新列：

Column H is used as separator (I like white spaces)
H列用作分隔符(我喜欢空白)
Retail and food services sales, total (Forecast) on column I
零售和食品服务销售，总计(预测)在第一列
Retail sales and food services excl motor vehicle and parts (Forecast) on column J
J栏上的零售和食品服务不包括汽车和零件(预测)
Retail sales and food services excl gasoline stations (Forecast) on column K
零售和食品服务(不包括加油站)(K列)
Retail sales and food services excl motor vehicle and parts and gasoline stations (Forecast) on column L
L栏上的零售和食品服务不包括汽车，零件和加油站(预测)
Retail sales, total (Forecast) on column M
零售总额，M列(预测)
Retail sales, total (excl. motor vehicle and parts dealers) (Forecast) on column N
N栏上的零售总额(不包括汽车和零件经销商)(预测)

3.6.5. Extend date column to 12/1/2020. Date column is located on column A.

3.6.5。将日期列扩展到12/1/2020。日期列位于列A上。

3.6.6. Now, let’s create a forecast for Retail and food services sales, total (located on cell I146) using Excel’s FORECAST.ETS function. It’s syntax is FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation]) and it has following arguments:

3.6.6。现在，让我们使用Excel的FORECAST.ETS函数创建零售和食品服务销售总额(位于I146单元中)的预测。它的语法是FORECAST.ETS(target_date，值，时间轴，[季节]，[data_completion]，[aggregation])，它具有以下参数：

Target_date: Required. Since we are forecasting for January 2017 thru December 2020, this is cell which contains dates or cell A146 for January 2017.
目标日期：必填。由于我们预测的是2017年1月至2020年12月，因此该单元格包含日期或2017年1月的A146单元格。
Values: Required. Since we will be Retail and food services sales, total’s historical data from January 2005 thru December 2016 to create forecast for Retail and food services sales, total, this will be range of cell or cell range \$B\\$2:\$B\\$145.
值：必填。由于我们将零售和食品服务销售总额作为2005年1月至2016年12月的历史数据，以创建零售和食品服务销售总额的预测，因此将是单元格或单元格范围\ $ B \\ $ 2：\ $ B 145美元。
Timeline: Required. Since we will be Retail and food services sales, total’s historical dates from January 2005 thru December 2016 to create forecast for Retail and food services sales, total, this will be range of cell or cell range \$A\\$2:\$A\\$145.
时间轴：必填。由于我们将零售和食品服务的销售总额作为历史记录，从2005年1月到2016年12月，以创建零售和食品服务的销售总额预测，因此将是单元格或单元格范围\ $ A \\ $ 2：\ $ A 145美元。
Seasonality: Optional. Use default value of 1 for Excel to automatically detect seasonality in data.
季节性：可选。使用Excel的默认值1可以自动检测数据的季节性。
Data Completion: Optional. Since there is no missing data, we will just use default value of 1.
数据完成：可选。由于没有丢失的数据，因此我们将使用默认值1。
Aggregation: Optional. Since our data is monthly data, no aggregation is needed.
聚合：可选。由于我们的数据是每月数据，因此无需汇总。

The completed formula will be =FORECAST.ETS(\$A146,\\$B\$2:\\$B\$145,\\$A\$2:\\$A\$145,1,1) for cell I146. Fill-in the values below or to cell I193.

对于单元格I146，完成的公式将为= FORECAST.ETS(\ $ A146，\\ $ B \ $ 2：\\ $ B \ $ 145，\\ $ A \ $ 2：\\ $ A \ $ 145,1,1)。填写下面的值或到单元格I193。

3.6.7. Next, let’s create a forecast for Retail sales and food services excl motor vehicle and parts (located on cell J146) using Excel’s FORECAST.ETS function. It’s syntax is FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation]) and it has following arguments:

3.6.7。接下来，让我们使用Excel的FORECAST.ETS函数为零售和食品服务(不包括汽车和零部件)(位于J146单元中)创建一个预测。它的语法是FORECAST.ETS(target_date，值，时间轴，[季节]，[data_completion]，[aggregation])，它具有以下参数：

Target_date: Required. Since we are forecasting for January 2017 thru December 2020, this is cell which contains dates or cell A146 for January 2017.
目标日期：必填。由于我们预测的是2017年1月至2020年12月，因此该单元格包含日期或2017年1月的A146单元格。
Values: Required. Since we will be Retail and food services sales, total’s historical data from January 2005 thru December 2016 to create forecast for Retail and food services sales, total, this will be range of cell or cell range \C\$2:\\$C\$145.
值：必填。由于我们将零售和食品服务销售总额作为2005年1月至2016年12月的历史数据，以创建零售和食品服务销售总额的预测，因此将是单元格或单元格范围\ C \ $ 2：\\ $ C \ 145美元。
Timeline: Required. Since we will be Retail and food services sales, total’s historical dates from January 2005 thru December 2016 to create forecast for Retail and food services sales, total, this will be range of cell or cell range \$A\\$2:\$A\\$145.
时间轴：必填。由于我们将零售和食品服务的销售总额作为历史记录，从2005年1月到2016年12月，以创建零售和食品服务的销售总额预测，因此将是单元格或单元格范围\ $ A \\ $ 2：\ $ A 145美元。
Seasonality: Optional. Use default value of 1 for Excel to automatically detect seasonality in data.
季节性：可选。使用Excel的默认值1可以自动检测数据的季节性。
Data Completion: Optional. Since there is no missing data, we will just use default value of 1.
数据完成：可选。由于没有丢失的数据，因此我们将使用默认值1。
Aggregation: Optional. Since our data is monthly data, no aggregation is needed.
聚合：可选。由于我们的数据是每月数据，因此无需汇总。

The completed formula will be =FORECAST.ETS(\$A146,\\$C\$2:\\$C\$145,\\$A\$2:\\$A\$145,1,1) for cell J146. Fill-in the values below or to cell J193.

对于单元格J146，完整的公式将为= FORECAST.ETS(\ $ A146，\\ $ C \ $ 2：\\ $ C \ $ 145，\\ $ A \ $ 2：\\ $ A \ $ 145,1,1)。填写下面的值或到单元格J193中。

3.6.8. Next, let’s create a forecast for Retail sales and food services excl gasoline stations (Forecast) (located on cell K146) using Excel’s FORECAST.ETS function. It’s syntax is FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation]) and it has following arguments:

3.6.8。接下来，让我们使用Excel的FORECAST.ETS函数为零售和食品服务除外加油站(预测)(位于单元K146上)创建一个预测。它的语法是FORECAST.ETS(target_date，值，时间轴，[季节]，[data_completion]，[aggregation])，它具有以下参数：

Target_date: Required. Since we are forecasting for January 2017 thru December 2020, this is cell which contains dates or cell A146 for January 2017.
目标日期：必填。由于我们预测的是2017年1月至2020年12月，因此该单元格包含日期或2017年1月的A146单元格。
Values: Required. Since we will be Retail and food services sales, total’s historical data from January 2005 thru December 2016 to create forecast for Retail and food services sales, total, this will be range of cell or cell range \$D\\$2:\$D\\$145.
值：必填。由于我们将零售和食品服务销售总额作为2005年1月至2016年12月的历史数据，以创建零售和食品服务销售总额的预测，因此将是单元格或单元格范围\ $ D \\ $ 2：\ $ D 145美元。
Timeline: Required. Since we will be Retail and food services sales, total’s historical dates from January 2005 thru December 2016 to create forecast for Retail and food services sales, total, this will be range of cell or cell range \$A\\$2:\$A\\$145.
时间轴：必填。由于我们将零售和食品服务的销售总额作为历史记录，从2005年1月到2016年12月，以创建零售和食品服务的销售总额预测，因此将是单元格或单元格范围\ $ A \\ $ 2：\ $ A 145美元。
Seasonality: Optional. we tried to use default value of 1 for Excel to automatically detect seasonality in data, but unfortunately Excel did not detect seasonality even though it exists. We used “24” to force Forecast to have similar seasonality pattern we have witnessed in historical data.
季节性：可选。我们尝试使用Excel的默认值1自动检测数据中的季节性，但是很遗憾，即使存在，Excel也没有检测到季节性。我们使用“ 24”来强制预测具有与历史数据相同的季节性模式。
Data Completion: Optional. Since there is no missing data, we will just use default value of 1.
数据完成：可选。由于没有丢失的数据，因此我们将使用默认值1。
Aggregation: Optional. Since our data is monthly data, no aggregation is needed.
聚合：可选。由于我们的数据是每月数据，因此无需汇总。

The completed formula will be =FORECAST.ETS(\$A146,\\$D\$2:\\$D\$145,\\$A\$2:\\$A\$145,1,1) for cell K146. Fill-in the values below or to cell K193.

对于单元格K146，完成的公式将为= FORECAST.ETS(\ $ A146，\\ $ D \ $ 2：\\ $ D \ $ 145，\\ $ A \ $ 2：\\ $ A \ $ 145,1,1)。填写下面的值或到单元格K193中。

3.6.9. Next, let’s create a forecast for Retail sales and food services excl motor vehicle and parts and gasoline stations (located on cell L146) using Excel’s FORECAST.ETS function. It’s syntax is FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation]) and it has following arguments:

3.6.9。接下来，让我们使用Excel的FORECAST.ETS函数为零售和食品服务(不包括机动车，零件和加油站)(位于L146单元中)创建一个预测。它的语法是FORECAST.ETS(target_date，值，时间轴，[季节]，[data_completion]，[aggregation])，它具有以下参数：

Target_date: Required. Since we are forecasting for January 2017 thru December 2020, this is cell which contains dates or cell A146 for January 2017.
目标日期：必填。由于我们预测的是2017年1月至2020年12月，因此该单元格包含日期或2017年1月的A146单元格。
Values: Required. Since we will be Retail and food services sales, total’s historical data from January 2005 thru December 2016 to create forecast for Retail and food services sales, total, this will be range of cell or cell range \$E\\$2:\$E\\$145.
值：必填。由于我们将零售和食品服务销售总额作为2005年1月至2016年12月的历史数据，以创建零售和食品服务销售总额的预测，因此将是单元格或单元格范围\ $ E \\ $ 2：\ $ E 145美元。
Timeline: Required. Since we will be Retail and food services sales, total’s historical dates from January 2005 thru December 2016 to create forecast for Retail and food services sales, total, this will be range of cell or cell range \$A\\$2:\$A\\$145.
时间轴：必填。由于我们将零售和食品服务的销售总额作为历史记录，从2005年1月到2016年12月，以创建零售和食品服务的销售总额预测，因此将是单元格或单元格范围\ $ A \\ $ 2：\ $ A 145美元。
Seasonality: Optional. Use default value of 1 for Excel to automatically detect seasonality in data.
季节性：可选。使用Excel的默认值1可以自动检测数据的季节性。
Data Completion: Optional. Since there is no missing data, we will just use default value of 1.
数据完成：可选。由于没有丢失的数据，因此我们将使用默认值1。
Aggregation: Optional. Since our data is monthly data, no aggregation is needed.
聚合：可选。由于我们的数据是每月数据，因此无需汇总。

The completed formula will be =FORECAST.ETS(\$A146,\\$E\$2:\\$E\$145,\\$A\$2:\\$A\$145,1,1) for cell L146. Fill-in the values below or to cell L193.

对于单元格L146，完成的公式将为= FORECAST.ETS(\ $ A146，\\ $ E \ $ 2：\\ $ E \ $ 145，\\ $ A \ $ 2：\\ $ A \ $ 145,1,1)。填写下面的值或填写单元格L193。

3.6.10. Next, let’s create a forecast for Retail sales, total (located on cell M146) using Excel’s FORECAST.ETS function. It’s syntax is FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation]) and it has following arguments:

3.6.10。接下来，让我们使用Excel的FORECAST.ETS函数创建零售总额的预测(位于M146单元中)。它的语法是FORECAST.ETS(target_date，值，时间轴，[季节]，[data_completion]，[aggregation])，它具有以下参数：

Target_date: Required. Since we are forecasting for January 2017 thru December 2020, this is cell which contains dates or cell A146 for January 2017.
目标日期：必填。由于我们预测的是2017年1月至2020年12月，因此该单元格包含日期或2017年1月的A146单元格。
Values: Required. Since we will be Retail and food services sales, total’s historical data from January 2005 thru December 2016 to create forecast for Retail and food services sales, total, this will be range of cell or cell range \$F\\$2:\$F\\$145.
值：必填。由于我们将零售和食品服务销售总额作为2005年1月至2016年12月的历史数据，以创建零售和食品服务销售总额的预测，因此，这将是单元格或单元格范围\ $ F \\ $ 2：\ $ F 145美元。
Timeline: Required. Since we will be Retail and food services sales, total’s historical dates from January 2005 thru December 2016 to create forecast for Retail and food services sales, total, this will be range of cell or cell range \$A\\$2:\$A\\$145.
时间轴：必填。由于我们将零售和食品服务的销售总额作为历史记录，从2005年1月到2016年12月，以创建零售和食品服务的销售总额预测，因此将是单元格或单元格范围\ $ A \\ $ 2：\ $ A 145美元。
Seasonality: Optional. Use default value of 1 for Excel to automatically detect seasonality in data.
季节性：可选。使用Excel的默认值1可以自动检测数据的季节性。
Data Completion: Optional. Since there is no missing data, we will just use default value of 1.
数据完成：可选。由于没有丢失的数据，因此我们将使用默认值1。
Aggregation: Optional. Since our data is monthly data, no aggregation is needed.
聚合：可选。由于我们的数据是每月数据，因此无需汇总。

The completed formula will be =FORECAST.ETS(\$A146,\\$F\$2:\\$F\$145,\\$A\$2:\\$A\$145,1,1) for cell M146. Fill-in the values below or to cell M193.

对于单元格M146，完成的公式将为= FORECAST.ETS(\ $ A146，\\ $ F \ $ 2：\\ $ F \ $ 145，\\ $ A \ $ 2：\\ $ A \ $ 145,1,1)。填写下面的值或到单元格M193中。

3.6.11. Next, let’s create a forecast for Retail sales, total (excl. motor vehicle and parts dealers) (located on cell N146) using Excel’s FORECAST.ETS function. It’s syntax is FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation]) and it has following arguments:

3.6.11。接下来，让我们使用Excel的FORECAST.ETS函数创建零售总额，总销售额(不包括汽车和零件经销商)的预测(位于N146单元中)。它的语法是FORECAST.ETS(target_date，值，时间轴，[季节]，[data_completion]，[aggregation])，它具有以下参数：

Target_date: Required. Since we are forecasting for January 2017 thru December 2020, this is cell which contains dates or cell A146 for January 2017.
目标日期：必填。由于我们预测的是2017年1月至2020年12月，因此该单元格包含日期或2017年1月的A146单元格。
Values: Required. Since we will be Retail and food services sales, total’s historical data from January 2005 thru December 2016 to create forecast for Retail and food services sales, total, this will be range of cell or cell range \$G\\$2:\$G\\$145.
值：必填。由于我们将零售和食品服务销售总额作为2005年1月至2016年12月的历史数据，以创建零售和食品服务销售总额的预测，因此将是单元格或单元格范围\ $ G \\ $ 2：\ $ G 145美元。
Timeline: Required. Since we will be Retail and food services sales, total’s historical dates from January 2005 thru December 2016 to create forecast for Retail and food services sales, total, this will be range of cell or cell range \$A\\$2:\$A\\$145.
时间轴：必填。由于我们将零售和食品服务的销售总额作为历史记录，从2005年1月到2016年12月，以创建零售和食品服务的销售总额预测，因此将是单元格或单元格范围\ $ A \\ $ 2：\ $ A 145美元。
Seasonality: Optional. Use default value of 1 for Excel to automatically detect seasonality in data.
季节性：可选。使用Excel的默认值1可以自动检测数据的季节性。
Data Completion: Optional. Since there is no missing data, we will just use default value of 1.
数据完成：可选。由于没有丢失的数据，因此我们将使用默认值1。
Aggregation: Optional. Since our data is monthly data, no aggregation is needed.
聚合：可选。由于我们的数据是每月数据，因此无需汇总。

The completed formula will be =FORECAST.ETS(\$A146,\\$G\$2:\\$G\$145,\\$A\$2:\\$A\$145,1,1) for cell N146. Fill-in the values below or to cell N193.

对于单元格N146，完整的公式将为= FORECAST.ETS(\ $ A146，\\ $ G \ $ 2：\\ $ G \ $ 145，\\ $ A \ $ 2：\\ $ A \ $ 145,1,1)。填写下面的值或到单元格N193。

3.6.12. Save the spreadsheet as mrtssales92-present_step1_6.xlsx.

3.6.12。将电子表格另存为mrtssales92-present_step1_6.xlsx。

Modified spreadsheet with modified “Forecast” worksheet is available here => mrtssales92-present_step1_6.xlsx

此处提供具有修改后的“预测”工作表的修改后电子表格=> mrtssales92-present_step1_6.xlsx

3.7。验证预测 (3.7. Validate Forecast)

We will need to validate the forecast using the Test data (January 2017 thru October 2018 or 20 months of data) and maybe improve upon the forecast, created by Excel’s FORECAST.ETS function.

我们将需要使用测试数据(2017年1月至2018年10月或20个月的数据)验证预测，并可能会改善由Excel的FORECAST.ETS函数创建的预测。

3.7.1. First, we will need to validate Forecast (January 2017 thru December 2010) with Actuals (January 2017 thru October 2018).

3.7.1。首先，我们需要使用“实际值”(2017年1月至2018年10月)验证“预测”(2017年1月至2010年12月)。

3.7.1.1. Open Excel spreadsheet, entitled “mrtssales92-present_step1_6.xlsx” and rename the file to “mrtssales92-present_step1_7.xlsx”.

3.7.1.1。打开名为“ mrtssales92-present_step1_6.xlsx”的Excel电子表格，然后将文件重命名为“ mrtssales92-present_step1_7.xlsx”。

3.7.1.2. Navigate to “Forecast” worksheet and add new columns:

3.7.1.2。导航到“预测”工作表并添加新列：

Column O is used as separator (I like white spaces)
O列用作分隔符(我喜欢空白)
Retail and food services sales, total (Forecast Diff) on column P
零售和食品服务销售额，总计(预测差异)，列P
Retail sales and food services excl motor vehicle and parts (Forecast Diff) on column Q
Q栏上的零售和食品服务不包括汽车和零件(预测差异)
Retail sales and food services excl gasoline stations (Forecast Diff) on column R
零售和食品服务不包括加油站(Riff栏)
Retail sales and food services excl motor vehicle and parts and gasoline stations (Forecast Diff) on column S
S列上的零售和食品服务不包括汽车，零件和加油站(预测差异)
Retail sales, total (Forecast Diff) on column T
T栏上的零售总额(预测差异)
Retail sales, total (excl. motor vehicle and parts dealers) (Forecast Diff) on column U
U栏上的零售总额(不包括汽车和零件经销商)(预测差异)

3.7.1.3. Now, let’s create a forecast diff for Retail and food services sales, total, located on cell P146. The formula is =ABS(B146-I146)/B146. Fill-in the values below to cell P167.

3.7.1.3。现在，让我们在单元格P146上创建零售和食品服务销售总额的预测差异。公式为= ABS(B146-I146)/ B146。将以下值填写到单元格P167中。

3.7.1.4. Next, let’s create a forecast diff for Retail sales and food services excl motor vehicle and parts, located on cell Q146. The formula is =ABS(C146-J146)/C146. Fill-in the values below to cell Q167.

3.7.1.4。接下来，让我们在单元格Q146上创建零售和食品服务(不包括汽车和零件)的预测差异。公式为= ABS(C146-J146)/ C146。将以下值填写到单元格Q167中。

3.7.1.5. Next, let’s create a forecast diff for Retail sales and food services excl gasoline stations, located on cell R146. The formula is =ABS(D146-K146)/D146. Fill-in the values below to cell R167.

3.7.1.5。接下来，让我们在单元格R146上创建零售和食品服务除外加油站的预测差异。公式为= ABS(D146-K146)/ D146。将以下值填写到单元格R167中。

3.7.1.6. Next, let’s create a forecast diff for Retail sales and food services excl motor vehicle and parts and gasoline stations, located on cell S146. The formula is =ABS(E146-L146)/E146. Fill-in the values below to cell S167.

3.7.1.6。接下来，让我们为位于单元格S146上的零售和食品服务(不包括汽车，零件和加油站)创建预测差异。公式为= ABS(E146-L146)/ E146。将以下值填写到单元格S167中。

3.7.1.7. Next, let’s create a forecast diff for Retail sales, total, located on cell T146. The formula is =ABS(F146-M146)/F146. Fill-in the values below to cell T167.

3.7.1.7。接下来，让我们在单元格T146上创建零售总额的预测差异。公式为= ABS(F146-M146)/ F146。将以下值填写到单元格T167中。

3.7.1.8. Next, let’s create a forecast diff for Retail sales, total (excl. motor vehicle and parts dealers), located on cell U146. The formula is =ABS(G146-N146)/G146. Fill-in the values below to cell U167.

3.7.1.8。接下来，让我们在单元格U146上创建零售总额的预测差异(不包括汽车和零件经销商)。公式为= ABS(G146-N146)/ G146。将以下值填写到单元格U167中。

Comparing Forecast with Actuals, it seems forecast predicted sales figure worked well compared to actuals as shown below:

将“预测”与“实际”进行比较，看来预测的销售数字与“实际”相比效果很好，如下所示：

Retail and food services sales, total
零售和食品服务销售额，总计
Absolute Avg Diff: 0.02395833
绝对平均差异：0.02395833
Max Diff: -0.061331562
最大差异：-0.061331562
Min Diff: 0.001778499
最小差异：0.001778499
Retail sales and food services excl motor vehicle and parts
零售和食品服务，不包括机动车和零件
Absolute Avg Diff: 0.0216799
绝对平均值差：0.0216799
Max Diff: -0.054707776
最大差异：-0.054707776
Min Diff: 0.004399175
最小差异：0.004399175
Retail sales and food services excl gasoline stations
零售和食品服务，不包括加油站
Absolute Avg Diff: 0.017094236
绝对平均差异：0.017094236
Max Diff: -0.054772583
最大差异：-0.054772583
Min Diff: 0.001791694
最小差异：0.001791694
Retail sales and food services excl motor vehicle and parts and gasoline stations
零售和食品服务，不包括机动车，零件和加油站
Absolute Avg Diff: 0.023225606
绝对平均差异：0.023225606
Max Diff: -0.070237471
最大差异：-0.070237471
Min Diff: -0.006441288
最小差异：-0.006441288
Retail sales, total
零售总额
Absolute Avg Diff: 0.022402977
绝对平均差异：0.022402977
Max Diff: -0.062273887
最大差异：-0.062273887
Min Diff: 0.001141435
最小差异：0.001141435
Retail sales, total (excl. motor vehicle and parts dealers)
零售总额(不包括汽车和零件经销商)
Absolute Avg Diff: 0.022714821
绝对平均差：0.022714821
Max Diff: -0.058850847
最大差异：-0.058850847
Min Diff: 0.00364976
最小差异：0.00364976

3.7.2. Next step is to analyze the forecasted sales figure with actual sales figure by creating a line chart of both to ensure seasonality seen in historical data is reflected on forecast.

3.7.2。下一步是通过创建两者的折线图来分析预测的销售数字和实际的销售数字，以确保历史数据中看到的季节性反映在预测中。

3.7.3.1. Navigate to Analysis worksheet.

3.7.3.1。导航到“分析”工作表。

3.7.3.2. On right of existing “Monthly Retail Trade Report”, create a line chart that combines actual Monthly Retail Trade Report with adjusted forecast Monthly Retail Trade Report as shown below, label as “Monthly Retail Trade Report (Actual and Forecast)”.

3.7.3.2。在现有“月度零售贸易报告”的右侧，创建一个折线图，将实际的月度零售贸易报告与调整后的预测月度零售贸易报告结合起来，如下所示，标记为“月度零售贸易报告(实际和预测)”。

3.7.3.3. On right of existing “Retail and food services sales, total (Actual)”, create a line chart that combines actual Retail and food services sales, total with adjusted forecast Retail and food services sales, total as shown below, label as “Retail and food services sales, total (Actual and Forecast)”.

3.7.3.3。在现有的“零售和食品服务销售总额(实际)”的右侧，创建一个折线图，将实际的零售和食品服务销售总额与调整后的预测零售和食品服务销售总额合计，如下所示，标签为“零售和食品服务销售总额(实际和预测)”。

3.7.3.4. On right of existing “Retail and food services excl gasoline stations (Actual)”, create a line chart that combines actual Retail and food services excl gasoline stations, total with adjusted forecast Retail and food services excl gasoline stations as shown below, label as “Retail and food services excl gasoline stations (Actual and Forecast)”.

3.7.3.4。在现有的“零售和食品服务非专利加油站(实际)”的右侧，创建一个折线图，将实际的零售和食品服务非专利加油站与调整后的预测零售和食品服务非专利加油站合并在一起，如下图所示，标签为“零售和食品服务不包括加油站(实际和预测)”。

3.7.3.5. On right of existing “Retail sales, total (Actual)”, create a line chart that combines actual Retail sales, total with adjusted forecast Retail sales, total as shown below, label as “Retail sales, total (Actual and Forecast)”.

3.7.3.5。在现有的“零售总额总计(实际)”的右侧，创建一个折线图，将实际零售总额(总计)与调整后的预测零售总额(如下所示)组合在一起，标记为“零售总额(实际和预测)”。

3.7.3.6. On right of existing “Retail sales and food services excl motor vehicle and parts (Actual)”, create a line chart that combines actual Retail sales and food services excl motor vehicle and parts with adjusted forecast Retail sales and food services excl motor vehicle and parts as shown below, label as “Retail sales and food services excl motor vehicle and parts (Actual and Forecast)”.

3.7.3.6。在现有的“零售和食品服务不包括汽车和零件(实际)”的右侧，创建折线图，该折线图将实际的零售和食品服务不包括汽车和零件与已调整的预测零售和食品服务不包括汽车和零件如下图所示，标签为“零售和食品服务不包括机动车及其零件(实际和预测)”。

3.7.3.7. On right of existing “Retail sales and food services excl motor vehicle and parts and gasoline stations (Actual)”, create a line chart that combines actual Retail sales and food services excl motor vehicle and parts and gasoline stations with adjusted forecast Retail sales and food services excl motor vehicle and parts and gasoline stations as shown below, label as “Retail sales and food services excl motor vehicle and parts and gasoline stations (Actual and Forecast)”.

3.7.3.7。在现有的“零售和食品服务不包括汽车及零件和加油站(实际)”的右边，创建折线图，将实际零售和食品服务不包括汽车和零件及加油站与调整后的预测零售和食品结合起来如下所示的服务专卖车及零件和加油站，标签为“零售和食品服务专卖车及零件和加油站(实际和预测)”。

3.7.3.8. On right of existing “Retail sales, total (Actual)”, create a line chart that combines actual Retail sales, total with adjusted forecast Retail sales, total as shown below, label as “Retail sales, total (Actual and Forecast)”.

3.7.3.8。在现有的“零售总额总计(实际)”的右侧，创建一个折线图，将实际零售总额(总计)与调整后的预测零售总额(如下所示)组合在一起，标记为“零售总额(实际和预测)”。

3.7.4. Save the spreadsheet as mrtssales92-present_step1_7.xlsx.

3.7.4。将电子表格另存为mrtssales92-present_step1_7.xlsx。

3.8。维持预测 (3.8. Maintain Forecast)

Like everything, newly created forecast needs to be maintained. More likely on a monthly basis since this is monthly forecast, forecast needs to be updated to reflect revised trend.

像其他所有内容一样，需要维护新创建的预测。由于这是每月预测，因此更可能按月进行，因此需要更新预测以反映修订后的趋势。

To revise the forecast to reflect more up to date data, follow the steps above where you will revise step 3.6.1.

要修改预测以反映更多最新数据，请按照上述步骤修改步骤3.6.1。

When diving data into two sets of data:

将数据分为两组数据时：

Train Data: January 2005 thru January 2017 (+ 1 month)
火车数据：2005年1月至2017年1月(+1个月)
Test Data: February 2017 (+ 1 month) thru November 2018 (+ 1 month)
测试数据：2017年2月(+1个月)至2018年11月(+1个月)

Repeat the process for all subsequent months.

在随后的所有月份重复该过程。