Calculation of the moving average in Excel and forecasting. Forecasting in Excel using the method of moving average

Transcript.

1 Prediction B. Excel method Moving Middle Doctor Piz. mat. Sciences, Professor Gavrilenko V.V. Assistant Parochnenko L.M. (National Transport University) Theoretical Help. When modeling various economic processes in practice, increasing possibilities of modern computer technologies are widely used, as well as effective methods Forecasting. So, for the development of forecasts in the EXSEL package, you can use such instruments as: constructing regressions; exponential smoothing; Moving average. In this paper, the projection development process by means of Excel is carried out using the method of the moving average. Note that the forecasting methodology using regressions is described in a sufficient detailed by the authors. The moving average method is used for smoothing and predicting time series. Recall that the time series is a set of pairs of data (x, y), in which X is moments or periods of time (independent variable), and y parameter characterizing the value of the process under study (dependent variable). The method of the sliding average allows you to identify the trends in the actual values \u200b\u200bof the y parameter in time and predict future values \u200b\u200bof Y. The resulting model can be effectively used in cases where the value trend in the projected parameter is observed in the dynamics. This method is not so effective in cases where such a tendency is violated, for example, with natural disasters, military actions, public unrest, with a sharp change in the parameters of the internal or external situation (the level of inflation, prices for raw materials); With a fundamental change in the plan of activity of the firm that endure losses. The main idea of \u200b\u200bthe method of the moving average consists in replacing the actual levels of the under study of the time series of their average values \u200b\u200bpaying for random oscillations. Thus, the result is a smoothed series of values \u200b\u200bof the test parameter, which allows you to more clearly select the main trend of its change. The method of the moving average is a relatively simple method of smoothing and * predicting time series based on the representation of the prediction Y t in the form of an average value of M of previous observed values \u200b\u200by (i \u003d 1, m), then M * 1 is: y t \u003d yt i. If, for example, in the study of a time series of data M i \u003d 1 on the profit of the enterprise for months as a forecast to choose a moving average for three months (m \u003d 3), then the forecast for June will be the average value of P. T I

2 assets for the three previous months (March, April, May). If you choose a 4-month moving average (M \u003d 4), then the forecast for June will be the average value of the figures for the four previous months (February, March, April, May). Often, for example, when developing the forecast of sales of the enterprise, the method of a moving average, based on observations for 3 (or 4) previous months, is more effective (it allows to track the actual sales volume with greater accuracy) than methods based on long-term observations (for 12 months and more). This is explained by the fact that as a result of the use of a 3-month moving average, each of the 3 values \u200b\u200bof the indicator (for these three months) is responsible for one third of the forecast value. With a 12-month moving average, each of the indicators of the last three months respond only in one twelfth forecast. Unfortunately, there is no rule that allows you to select the optimal number of M members of the moving average. However, it can be noted that the smaller M, the stronger the forecast responds to the vibrations of the time series, and vice versa, the greater M, the process of prediction becomes more inertial. In practice, the value M is usually taken in the range from 2 to 10. If there is a sufficient number of temporary elements, the value of M can be determined, for example, as follows: set several preliminary values \u200b\u200bM; smooth the time series using each specified M value; calculate middle Error Predictions according to one of the formulas: 1 * o ε \u003d y t y t (medium absolute deviation); n 1 yt o ε \u003d y n y t t T * T (average relative deviation); 1 * 2 O ε \u003d (yt yt) (secondary quadratic deviation), n t where n The number of time used when calculating the moments of time T; Select M value corresponding to a smaller error. The implementation of the smoothing process and forecasting by the method of the sliding average in the Excel medium can be carried out: by introducing an appropriate formula into the cell, for example, using the built-in function of the SRPNAH (); Using the tool, the moving average superstructure "analysis package"; By adding to the diagram constructed by the original time series, the trend line based on the linear filtering method.

3 task. Given the data presented in the table of the monthly profit of the company in 11 months current year, Make a forecast about the company's profits for the 12th month. Fig.1. Table values \u200b\u200bof the profit of the company for months, the problem of the problem later, when solving a formulated task, the working sheets Z1, Z2, Z3, Z4 are used for the convenience of the obtained calculation results will be used for the formation of the smoothed time series based on the method of moving average using the SRVNOW function () and calculating their average deviations from the initial time series; Z2 sheet to implement the process of smoothing the original time series using the tool sliding average superstructure analysis package; Z3 sheet for visual representation of a smoothed time series built using a trend line linear filtering based on the diagram for the initial time series; Z4 sheet for comparative analysis of the results obtained using the above tools: on the basis of the initial time series, the smoothed time series of the values \u200b\u200bof a 2-month moving average are built using the function of the SR will (), the tool moving average superstructure "analysis package" and a linear trend line filtration. Application of the built-in function of the SRVNAV () The process of obtaining a smoothed time series, as well as a forecast for the profit of the company for the 12th month of the current year, according to the initial time series, it will be carried out according to the following scenario: 1. Based on the data shown in Fig.1, on workers excel sheet A table is created that is filled with the source time series. 2. The data of the smoothed time series for the 2nd, 3 and 4-month moving average are formed and recorded in the table.

4 3. Graphs of the initial time series and smoothed time series are built. 4. According to one of the above formulas, the average deviations of the obtained smoothed time series from the initial time series are calculated. 5. As a model, a smoothed temporary series with a smaller average deflection is selected, and based on its indicators is a forecast for the company's 12th month of this year. Go to the implementation of the problem of the problem. 1. Fill the range of the cells A5: B15 of the working sheet Z1 data of the time series from the table Fig.1. As a result, we obtain the table shown in Fig. 2. Fig.2. The source table on the Excel worksheet 2. According to the time series from the range of the A5: B15 cells, we build on the basis of the method of the moving average three models of the dependence on data for 2, 3 and 4 of the previous month, respectively. The values \u200b\u200bof the smoothed time series obtained are respectively in the ranges of C7 cells: C16; D8: D16; E9: E16. First, we build a number of values \u200b\u200bof the moving average for two months: into the C7 cell we enter the formula \u003d CPNPHOV (B5: B6) and, using a fill marker, copy it to the C8 cell range: C16, as a result of which the range of cells C7: C16 is filled with the calculated indicators 2- x monthly moving average. Similarly, rows of 3 and 4 month-old moving averages are built: in the D8 cell we introduce a formula \u003d CRNVAL (B5: B7) and, using a filling marker, copy it to the range of cells D9: D16, resulting in the range of cells D8: D16 Filled in the indicators of the 3-month moving average; Introduce into the cell E9 formula \u003d CPNAVOV (B5: B8) and the filling marker copy it to the cell range E10: E16, as a result of which the range of cells E9: E16 is filled with the indicators of the 4-month moving average. Figure 3 4 shows the tables with the results for the 2nd, 3 and 4-month moving averages, as well as the formulas used.

5 Fig.3. Table of values \u200b\u200bfor 2, 3, 4-month moving average Fig.4. The contents of the cells of the table Figure 3 in Fig. 5 are a graph of the original time series and the predicted line of the moving average trend line is given relative to it. Note that these graphics were built according to the standard method of building diagrams in Excel. Since the obtained values \u200b\u200bof the smoothed time series based on the moving average are based on the data of previous observations, they are delayed compared to the corresponding values \u200b\u200bof the initial time series: the sliding medium trend line is shifted relative to the graph of the original time series (Fig. 5). In the tables in Fig.6 10, absolute, relative and medium quadratic deviations of the values \u200b\u200bof 2, 3 and 4 months of moving average are given

6 from the corresponding values \u200b\u200bof the initial time series, as well as the contents of the cells in these tables. Fig.5. Graphics of the initial time series and smoothed time series Fig.6. Table of absolute deviations

7 Fig.7. The contents of the cells in Table Fig.6. 8. Table of relative deviations Fig. 9. The contents of the cells in Table Fig.8 Fig.10. Middle Quadratic Deviation Table

8 Middle values quadratic deviation In the range of cells B41: D41, the formula is injected into the B41 cell: \u003d root (summite (B9: B15; C9: C15) / account (B9: B15)), the formula is introduced into the C41 cell: \u003d root (summquance (B9 : B15; D9: D15) / Account (B9: B15)), the formula is introduced into the cell D41: \u003d root (Summkvon (B9: B15; E9: E15) / Account (B9: B15)). It should be noted that for the comparative analysis of the errors for the 2nd, 3 and 4-month moving averages was taken same number observations. Output. It follows from the tables that it is preferable to smoothing the initial time series and draw up a trend on the trend of the company's profits; since it reacts more accurately to fluctuations in the original time series and has smaller forecasting errors (absolute, relative, secondary quadratic ). The projected value of the profit of the company for 12 month 8325 thousand UAH. The sliding average tool "Analysis Package" to implement the smoothing process and prediction using the method of sliding average in an Excel environment can be done using a tool to the sliding average "Package of Analysis" using the following procedure: 1. On the Z2 worksheet, we create a table in which the A5 cell range: B15 Fill in the time series data from the source table (Fig. 1). 2. The range of C5: C55 cells. Fill in the values \u200b\u200bof the smoothed row, obtained according to the data for 2 previous months using the tool, the sliding average "Analysis Package" tool, and the range of cells D5: D15 with the values \u200b\u200bof its standard errors. 3. Similarly, the ranges of cells E5: E15 and F5: F15 with the values \u200b\u200bof the smoothed row obtained according to the data for the 3 previous months, and the values \u200b\u200bof its standard errors, respectively, are filled. The technology of building a number of values, for example, for a 2-month moving average using the tool, the sliding average "Analysis Package" tool is as follows: Select the data analysis command in the Tools menu. The Data Analysis dialog box appears (Fig. 11), which contains all available data analysis tools. From the list, select the tool sliding average and click on the OK button. The sliding average dialog box appears (Fig.12). In the input interval field, specify the source data range on the Excel worksheet, that is, the range of cells B5: B15.

9 Fig.11. Data Data Dialog box Fig.12. The moving average dialog box in the interval field we enter the number of months that are included in the calculation of the moving average, that is, the number 2 (as in this case, the moving average is built according to the 2 previous months). In the output interval input field, we enter the range of cells in which the results will be displayed, that is, the C5: C15 cell range. When installing flags in the fields, the output of the chart and standard errors will automatically be created a diagram based on the results of the analysis and a column will be added to the result containing a statistical error assessment. In the label field, check the box if the first line (column) in the input range contains headlines. If the input range does not contain headers, then you must select the checkbox. Click on the ok button. Similarly, a number of values \u200b\u200bof a 3-month-old moving average and its standard errors are built. Figure 13 shows the table of 2 and 3-month-long sliding averages and their standard errors obtained using the sliding average tool to the "Analysis Package" tool, and in Fig.14a, 14b the contents of the cells of this table, that is used in The process of solving formulas.

10 Fig.13. The smoothed rows and their standard errors obtained by the tool the moving average "Analysis Package" Figure 14a. The contents of the cells of the table Fig.13 (beginning)

11 Fig.14b. The contents of the cells of the table Fig.13 (continued) Fig.15. Graphs of the original time series and smoothed time series built using the tool sliding average "Analysis Package" Conclusion: Comparison of standard errors from the D9 cell range: D15 with the corresponding standard errors from the F9: F15 cell range (Fig.13) allow you to consider model 2 monthly moving average is preferable to smoothing and forecasting, as it is considered at all points

The 12th time range has smaller standard errors. The forecast value of the profit of the company for 12 months will be the value contained in the C15 cell, that is, 8325 thousand UAH. Building a trend lines using a linear filtering method For a graphical data analysis on a diagram, you can use the construction of the trend line through the points of the moving average. Such a trend line allows you to construct a smoothed curve, the graphical representation of which more clearly shows the existing pattern in data development. For the source table of values \u200b\u200b(Fig. 2), we use a linear filtering method (or a moving average method) and construct a trend line. The Trend Line Technology is as follows: according to the source table (Fig. 2), we build a chart, choosing the type of spot in the Diagram Type dialog box. Optionally, you can change the type of the designed schedule and its marker, line type, color and thickness. To do this, go to the edit mode of the obtained graph, clicking the double click of the mouse button on the built schedule. In the dialog box that appears, the format of a number of data specify the necessary parameters for changing the chart and press the OK key. Next, we allocate this data series by clicking on the line of the graph with the right mouse button (the selection of the row will be produced by black squares). In the context menu that appears, select the Add Trend menu item. Or after selecting a row by clicking any mouse button, click Add Trend Line in the Chart menu. The Trend Line dialog box appears on the screen (Fig.16). On the Type tab, select Trend Type Linear Filtration (moving average). When choosing a linear filtering, you must enter the number of periods (points) used to calculate the moving average. We introduce a number 2 to this field, because We carry out the construction of the Trend line for 2 months. Click OK. By analogy, we enter the construction of the trend line for 3 months, entering the number 3. Par18 in the field. The constructed graphs of the initial time series and the trend line of the 2nd and 3-month-long sliding average are presented.

13 Fig.16. The trend line dialog box built trend lines can be formatted. To do this: We highlight the trend line by clicking on it with the mouse, then right-click and from the context menu that appears, select the formatting of the trend line. The trend line format dialog box appears (Fig. 17), in which you can set the desired trend view: line type, color, thickness; You can change the name of the smoothed curve by opening the parameters tab in the same dialog box. By setting the necessary parameters, click OK.

14 Fig. 17. Trend line dialog box Note the following: Since the linear filtering method is implemented by applying a trend line to the diagram, its action can be observed visually, but it is not possible to obtain at its disposal numerical results, since they are not entered into the spreadsheet.

15 rice 18. Graphics of the initial time series and the Trend Lines and 3-month Molding Middle Tool Comparison Tool Comparison Technology can be implemented by the following actions: based on the time series data shown in the original table. Fig.2, we build a number of 2-month values moving average using the SRVNAF () function and 2-month moving average analysis package. We construct a graph of the initial time series and the trend line of smoothed time series.

16 Fig. 19. Table of the values \u200b\u200bof a 2-month moving average, obtained using the function of the SRVNOV () and the analysis package Fig.20. Graphics of the initial time series, 2nd monthly moving average, obtained using the function of the SR will, the tool moving average superstructure "analysis package" with the addition of a trend line linear filtering

17 Comparing the values \u200b\u200bof the moving average in the column C obtained by directly administering the formulas in the working sheet cell, with the values \u200b\u200bof the moving average in the column d, calculated using the tool to the sliding average of the "Package of Analysis" (Fig.20), can be noted that the indicators of the sliding The average in the column with shifted to one position down compared to the D column. This problem can be solved, for example, as follows: after the values \u200b\u200bof the moving average are calculated, you should select all these values \u200b\u200band shift them to one line of the working sheet down. This action will allow you to associate predictions precisely with those periods to which they relate. However, if the graph of the output of the graph will be checked in the moving average dialog box (Fig. 12), the schedule will host the projection data in accordance with the working sheet data. Shifting the values \u200b\u200bof the working table to one line down, it is also necessary to edit the built schedule according to the forecast data. We note the advantages and disadvantages of the preparation of the forecast using the method of the moving average: the preparation of the forecast using the tool of the moving average is quite simple and quite accurately reflect changes in the main indicators of the previous period. Sometimes in the preparation of the forecast, they are even more efficient than methods based on long-term observations. However, a simple sliding average is though faster, but not always in a precise way Detection of general trends in the time series. When drawing up forecasts of the moving average with the help of the superstructure, the analysis package is created for one time period earlier. You can build a chart in which the time series data is used to build a sliding medium trend line, but the actual numeric values \u200b\u200bof the moving average are not shown on the chart. And also there is no possibility to change the location of the trend line on the chart. The preparation of forecasts based on the moving average does not give a forecast of emerging beyond the limits of known data. To move the boundary of the estimate to the future by the time axis using one of the statistical function regression analysis Excel package. Literature 1. Carlberg K. Business Analysis with Excel. K.: Dialectics, p. 2. Gavrilenko V.V., Parokhnenko L.M. Solving Approximation Tasks Excel Tools // Computers + Programs, S N.V. Makarova, V.Ya. Trophimets. Statistics in Excel: Tutorial. M.: Finance and statistics, p. 4. Yu.N. Tyurin, A.A. Makarov. Data analysis on a computer / ed. V.E. Figured. M: Infra-M, s.

Laboratory work 2 Topic: Analytical modeling technology in SPPR. Trend-based analysis and forecasting technologies Purpose: Exploring the possibilities and the formation of the use of universal use

Practical work 3.7. Using MS Excel functions wizard. Building chart goal goal. After completing this work, you will learn: enter formulas in the table cells; Use MS Excel Function Wizard

Laboratory work 8. Building graphs and diagrams in Excel Objective: learn how to use graphic information tools in Ecel environment, ways to format it and use

Forecasting the volume of gasoline sales by the method of extrapolation Trends Puchkova V. S., Raster, N.V. Don State Technical University (DSTU) Rostov-on-Don, Russia FORECASTING OF SALES VOLUMES

Solving tasks of descriptive statistics using MS Excel Analysis Pack Simplest descriptions of descriptive statistics can be solved using table processors. Further, all examples are given for

Excel laboratory work (file.xls on a page www.matburo.ru/sub_appear.php?p\u003dl_excel) Creating, filling, editing and formatting tables What is mastered and studied? Enter and formatting

3.4. Work with spreadsheets 3.4.1. User interface program Microsoft Excel.. Creating and editing the tables Document in Microsoft Excel (MS Excel) is called the Working Book,

Row titles Graphic presentation of data using charts 1.1 Basic concepts Any diagram is built in the coordinate system asked by a horizontal axis called the axis of categories, and

Chapter 4. Working with diagrams and summary tables Presentation of data using charts allows you to provide a visual demonstration of information located in the working sheet cells. So, with the help of charts

Workshop 5.2.4. Charts. Technology for building and editing workshop 5.2.4. Charts. Technology for building and editing ... 1 Chart objects ... 1 Building chart ... 3 1st step. Selection

Charts and charts Preliminary information on the construction of charts Construction and editing charts and graphs Set the color and style lines. Editing Chart Formatting Text, Numbers,

Number of newspapers Laboratory and practical work Subject: "MS Excel. Building, formatting and editing diagrams, graphs. " The purpose of the lesson: learn how to build, format and edit charts, graphics.

Building graphs of functions and trend lines. Volchkov V.M., Styazhin V.N. cafe. Applied Mathematics, Volgight Lesson 3 There are many specialized computer programs that allow you to build graphs

Laboratory work 5. Processing experimental data in the spreadsheets Task 1. On the first work sheet of the document, enter the source data corresponding to the task option. Build Graph

Microsoft Excel 2007 laboratory work. Working with charts 1. Insert columns call the context menu for column and select Paste (the new column is added to the left of the highlighted). 1.1. Selection

Using MS Excel for graphics processing results (recommendations for students and teachers) MS Excel Table Editor, which is included in the standard MS Office software package.

Automation of econometric modeling T. A. Zayatsov UO "Belarusian Commercial and Economic University of Consumer Cooperation", Gomel in modern economic conditions Planning and management

Ministry of Health of the Russian Federation GBOU Higher Professional Education Amur State Medical Camemic E.V. EXCEL CSEE ELECTRONIC TABLE. Methodical instructions

Laboratory work 4 Tabulization of functions and building charts Purpose: Purchase the skills of calculating the table of values \u200b\u200bof the function and build graphs. Methodical instructions: Tabulation function is a calculation

Lesson 10. Electronic tables The main parameters of the spreadsheet (ET). This allows you to process large arrays of numerical data. In contrast to tables on paper, spreadsheets provide

Practical work: Practical work 1. Entering data in cells, data editing, column width change, lines insert (column) Practical work 2. Enter formula Practical work 3.

Laboratory work on MS Excel 2007 Laboratory work 1 .... 1 Laboratory work 2 ... 3 Laboratory work 3 ... 4 Laboratory work 4 ... 7 Laboratory work 5 ... 8 Laboratory work 6 ... 10

Approximation in practice often have to face the task of smoothing the experimental data of the approximation problem. The main task of approximation Construction of an approximate (approximating) function

Laboratory and Practical Work 13 "Related Tables in MS Excel 2007" Basic principles for the formation of a working book. For right organization Works in electronic excel tables 2007 form layout

Excel. The names of the ranges may have been able to work with sheets in which the formula type was used: \u003d amounts (A5000: A5078). You wondered what is in the A5000 cells: A5078!? If in cells A5000: A5078

Real estate investing: Economy, Management, Examination UDC 332.622 Application of regression analysis when calculating adjustments to the size in the comparative approach Niklinova Natalia Evgenievna,

Chapter 1 Basics of constructing diagrams The data in the spreadsheet is presented in the form of rows and columns. When adding a diagram, the value of these data can be enhanced by selecting links and trends that are not

Basic teams and operations! Check how you remember the studied material windows system 7 and text processor MS WORD Basic actions when working in Windows 7. Select the icon click

Laboratory work Subject: Building Function Schedules Objective: Studying the graphic features of the MS ECEL package Acquisition of the skills of building a function of a plane on a plane Task package

Building diagrams. Tabeling functions The goal of the work: to master the main techniques for creating and editing diagrams; explore the operation of copying the formulas by filling; Learn to solve settlement

1 Laboratory work 3 solution tasks. Selection of parameters, Solution Search 1. Implementation of the mathematical model in Excel mathematical Model This is a description of the state of behavior of some real system (object,

Task Laboratory work 6. Constructing the empirical dependence of the heat capacity of the substance from the temperature by the least squares method. Construct a graph of temperature dependence of the heat capacity of the substance in

General. Function tabulation is the calculation of the values \u200b\u200bof the function (dependent variable) when the argument is changed (independently variable) from some initial value to some finite

Introduction The function tabulation is the calculation of the values \u200b\u200bof the function (dependent variable) when the argument change the function (independent variable) from some initial value to some finite

Practical occupation Analysis of test results for analyzing test results Perform the following actions:. Count middle score According to the group obtained by testing;. According to the matrix results

28 Chapter 1. We start working with Microsoft Excel 2013 Insert and delete cells, rows and columns If you need to insert a new cell, column or string in the already dialed part of the table, click on the arrow

Chapter 8 Databases in OpenOffice.org Calc In this chapter, we will study the capabilities of the OpenOffice.org Calc package when working with databases. Quite often the need to store and process data

Practical work 8 Topic: Microsoft Excel Tableware Computing Functions For Financial Analysis The purpose of the lesson. Studying information technology for using built-in computing

Basics of summary tables Definition of the consolidated table. Imagine that Excel is a big box with tools. Then the summary table is just one of the Excel tools. Continuing the analogy with the tools,

Laboratory work Primary acquaintance with Microsoft Office Excel 2007 As a result of this laboratory work, you can: know the basic concepts and objects of the table processor, to be

Topic 6.8. Calculation certain integral Didactic goal. Generate students with methods of approximate calculation of a certain integral. Educational goal. The topic of this occupation has a big

Laboratory work 5 Text design in the form of lists and columns Creating lists in text documents of enumeration of various types are made in the form of lists. There are lists different types: Numerous

ECONOMometric modeling laboratory work 3 pair regression table of contents pair regression ... 3 Method of least squares (MNC) ... 3 Interpretation of the regression equation ... 4 Quality assessment built

Microsoft Office Excel Discipline "Software Professional Activities" Lecturer: Art. Lecturer of the Department of "Electric Drift and Electrical Equipment" Voronina Natalia Alekseevna Appointment

Main methods of data entry in NormCAD: on the Data tab in the report text in the dialogue mode (automatic data query when calculating) on \u200b\u200bthe document tabs (in tables) data entry on the tab

1 Laboratory work 1 Editing workbook. Building diagrams Objective: Studying methods for working with data in a cell. Study of the ability to autofill. Building diagrams. Exercise 1.

6 IT investment objectives (survey) Increase of efficiency of operating activities New products, services, business models Close contacts with buyers and suppliers Support for decision-making Competitive

PZ 6. Technologies for using a package of analysis for statistical data processing 1. Testing hypothesis Very often general aggregate 1 should obey some parameters. For example, packaging

Combined Chart in Excel The combined diagram combines two or more types of standard diagrams. To create a combined diagram, you must perform several steps: allocate

Practical work Creation of control systems by means microsoft programs Excel Task 1 Create a system for monitoring students' knowledge of Microsoft Excel tools containing at least 3 test

1. Introduction Laboratory work 3 Selection of parameters when solving various tasks It is often necessary to deal with the problem of the selection of one value by changing the other. This goal is very efficiently used.

Ministry of Education and Science Russian Federation Federal state budgetary educational institution Higher vocational education "Vladimirsky state University name

Laboratory work. MS Excel 1. Create a working book by saving it under the name "Office Applications". !!! Do not forget to periodic perform information saving. 2. Rename the first sheet by asking him

The task of the distribution of resources of the enterprise is a meaningful setting of the task of the factory produces bags: women's, male, road. Data on materials used for the production of bags and monthly stock

Working with charts in MS Excel Charts are designed to graphically present data. With the help of diagrams, the process of analyzing dependencies between different indicators presented is significantly simplified.

Working with lists in MS Excel Purpose: Purchase the search and aggregation skills in the list. Brief theory Computer information Technology widely used to analyze data and prepare managerial

R A F and E with a solution of systems of equations Analytical geometry is examined by geometric objects according to their equations. MS Excel provides ample visualization capabilities. various equations. In Excel

Chapter 7 Processing Experiment Results in Opeoffice.org Calc In this chapter, we will consider the options of the OpeOffice.org Calc package when solving the tasks of processing experimental data. One of the common

Passportization. The system of passporting equipment of boiler and elements of the heat supply system allows you to consider individual specifications Real objects when performing settlement tasks.

I repeat. The behavior of the crowd is inertial. And therefore, the likelihood that the crowd will act as soon as yesterday and the day before yesterday, much higher than the probability of mood change.

In order to track the behavior of the crowd on the market, there is an ancient MACD indicator. Its abbreviation is decrypted as moving average convergence-divergence or if in Russian convergence-discrimination of moving averages (meaning historical meanings Prices for stocks or other tools).

The graphical meaning of the MACD histogram is to confirm the continuation of the trend (directions to the development) of the price movement. Roughly speaking, stocks continue to be cheaper or rushing. The direction of the price movement is defined as the difference between the two adjacent columns.

To build a MACD histogram, we use Excel.

1) First, we will need historical data for analysis. In the previous article, I led an example where such data can be requested. Let us follow this example and proceed to the brokerage page of the export data:

By setting the requirements for the format of the downloadable data, we obtain a file with CSV format data that Excel understands. Also historical data on the tool of interest to us can be downloaded on the site broker CJSC "Finam this link.

2) These should format as described in.

Ultimately, it should be like this set:

3) Now create a new list in the Excel book for calculations and build a schedule technical analysis. So let's call this sheet: "Calculation of MACD". Then copy the column with dates on this sheet and column with data closure prices . Like this:

4) Now we calculate the exponential moving average with a 12-day window (EMA 12). EMA 12 is calculated by the formula:

We will put this formula in the column to the right of the closing price . To do this, write to the cell starting from the "\u003d" symbol, which informs the Excel processor that the formula will be introduced. For the first cell formula, a little different than for the other cells, due to the fact that instead of yesterday's EMA12 should be substituted with today's closure price. Like this:

Copy the resulting formula in the cell below and a slightly consequence: instead of the value from the cell B3, in the second part of the formula, we substitute the value from the C2 cell. C2 is the EMA12 of the previous day.

It should turn out like this:

Now spinning the formula obtained in the second cell for the entire EMA12 column. For this, click once the mouse in the C3 cell so that a black bold frame appears around the cell, then we move the cursor into the lower right corner of the black bold frame so that the cursor takes the shape of the fat black cross and the double click of the left mouse button spread the formula to the entire column. Like this:

Now we calculate the exponential sliding average with a window of 26 days (EMA 26). EMA 26 is calculated by the formula:

We will put this formula in the column to the right of the calculated EMA12. To do this, write to the cell starting from the "\u003d" symbol, which informs the Excel processor that the formula will be introduced. For the first cell formula, a little different than for the rest of the cells, due to the fact that instead of yesterday's EMA26 should be substituted with today's closure price. Like this:

Copy the resulting formula in the cell below and a little alternate: instead of the value from the cell B3, in the second part of the formula, we substitute the value from the D2 cell. D2 is the EMA26 of the previous day. It should turn out like this:

Now spinning the formula obtained in the second cell for the entire EMA26 column. For this, click once the mouse in the D3 cell so that a black bold frame appears around the cell, then we move the cursor into the lower right corner of the black bold frame so that the cursor takes the shape of the bold black cross and the double click of the left mouse button spread the formula to the entire column. Like this:

Congratulations! We coped with the calculation of exponential averages. Now you should get a "fast" line MACD. To do this, you need from EMA12 subtracting EMA26. Let's drink this formula in the next column to the right:

Now you need to calculate the nine-day exponential sliding average for the "fast" line MACD. The resulting line will be called a "signal" MACD line. Calculation will produce according to the following formula:

Similarly, we clog the calculation formula in Excel into a cell to the right of the "fast" line MACD:

In the cell of the lower row, correct the formula as well, as it was done when calculating the twenty-sest and twelve-day exponential moving averages. This should be the formula in the F3 cell:

And finally, we can calculate the last data column to build a MACD histogram. The values \u200b\u200bof this column to build a histogram is the difference between "fast" and "signal" lines MACD. We drive the last formula for calculating data to build a histogram:

Consider the MACD histogram is much more convenient next to the price fluctuation schedule for the analyzed tool. In the previous article I described in detail how to build such a schedule. To build a price schedule for the tool, copy the selection of the necessary data to a separate sheet. Something like this:

Building stock exchange graphics is easiest to produce here, on this sheet. Then it should be copied to a separate sheet, which we will place and the MACD histogram.

Create a separate sheet for our graphs. Insert a copied diagram from the clipboard and configure it a bit appearance. The window with the schedule is stretched and reduced in length and width like windows in Windows itself.

And by pressing the left mouse button in the scale with price values, you can change the format of the data axis of the schedule. After such a pump, the scale of the values \u200b\u200bof the vertical (in our case) axis is highlighted by a rectangular frame. As soon as this frame appeared, you should click the right mouse button to call the context menu. In the context menu with the left mouse button, select the string<Формат оси…>, like this:

In the setting dialog box, the settings of the parameters of the graph of the graph setting the minimum value (80) and the maximum (160). This is the top two lines in the opened dialog box. The figure below shows the desired position of the radio pools and the values \u200b\u200bof 80 and 160 are inscribed in the corresponding lines:

Under the price schedule window, insert the window for the future MACD histogram. In the main menu, select the tab<<Вставка>\u003e then submenu<<Гистограмма>\u003e And in the drop-down menu, select the left upper icon of the histogram, this icon is highlighted yellow on screen screens below:

The main thing, before inserting the second chart, do not forget to remove the selection from the first one. Otherwise, the replacement of one schedule can occur to others, and we need both graphics.

Before calling the menu<<Гистограмма>\u003e There will be a thumbs up the cursor on the A16 cell and click the left mouse button. After inserting a histogram, we need to specify our column with the calculated data of the MACD histogram. To do this, make the mouse cursor to the histogram and click the right mouse button to call the context menu of the diagram. In the opened context menu, select the item<Выбрать данные>:

After pressing the button<<Добавить>\u003e In the previous window, we need to gain the name of our graph - "MACD", and press the button to the right of the row in the bottom:

After pressing the button on the right of the lower row, the narrow window "Changing the row" opens. Without closing this window, we move with the mouse on a sheet called MACD:

After the data column is covered by a thin dotted line in the "Changing Row" window, press the button on the right. After that, the "Change Row" window will open with two lines. Here in this window you can click the button.<\u003e And go to the window of publishing a schedule:

Returning to a sheet with the name "Graphics" in the data selection window for building a histogram, also press the button<>:

You can play a bit with the size of windows for graphs and get the result that seems clear:

But the same graphics built by the quik trading system. It seems to be with us with you?

Dear reader! If you decide to build these graphs and you can't do something - leave your question in the comments and together we will definitely deal with and learn how to build graphs in Excel.

Excel source files from which screenshots are made and in which there are built schedules you can download software.

Practical modeling of economic situations implies the development of forecasts. Using Excel funds, such effective forecasting methods can be implemented as: exponential smoothing, regression constructing, moving average. Consider the use of the moving average method.

Use moving averages in Excel

The moving average method is one of the empirical methods for smoothing and predicting time series. Essence: absolute values A number of speakers change to medium-sized arithmetic values \u200b\u200bat certain intervals. The choice of intervals is carried out by the method of sliding: the first levels are gradually cleaned, the following - are included. As a result, a smoothed dynamic range of values \u200b\u200bis obtained, which allows you to clearly trace the tendency of changes in the parameter under study.

The time series is a set of x and y values \u200b\u200brelated to each other. X - time intervals, constant variable. Y is the characteristic of the studied phenomenon (the price, for example acting during a certain period of time), dependent variable. Using a moving average, you can identify the nature of the changes to the value of Y in time and predict this parameter in future. The method is valid when the trend in the dynamics is clearly traced for values.

For example, you need to predict sales for November. The researcher selects the number of previous months to analyze (the optimal number of M members of the moving average). The forecast for November will be the average value of the parameters for the last month.

A task. Analyze the revenue of the enterprise for 11 months and make a forecast for 12 months.

We form the smoothed time series by the method of sliding average by means of the function of the SRVNA. We find the average deviations of the smoothed time series from the specified time series.

Relative deviations:

Medium quadratic deviations:

When calculating deviations, they took the same number of observations. It is necessary in order to hold comparative analysis Error.

After making tables with deviations, it became clear that to prepare the forecast using the method of moving average in Excel on the trend of changes in the enterprise's revenue, a preferably model of a two-month moving average. She has minimum errors forecasting (in comparison with three and four months).

The forwarding value of revenue for 12 month - 9,430 cu

Application of add-on "analysis package"

For example, take the same task.

On the Data tab, we find the "Data Analysis" command. In the dialog box that opens, select the "moving average":

Fill. The input interval is the initial values \u200b\u200bof the time series. The interval is the number of months included in the calculation of the moving average. Since we will first build a smoothed time series according to two previous months, enter the number 2. The output interval is the range of cells to remove the results obtained.

After selecting the checkbox in the "Standard Error" field, we automatically add a column to the table with a statistical estimate of the error.

Similarly, we find a moving average for three months. Only the interval (3) and the output range changes.

Comparing standard errors, we are convinced that the model of the two-month moving average is more suitable for smoothing and forecasting. It has smaller standard errors. The forwarding value of revenue for 12 month - 9,430 cu

Forecasts on the method of moving average simply and efficiently. The tool accurately reflects changes in the basic parameters of the previous period. But it is impossible to go beyond the limits of known data. Therefore, for long-term forecasting, other methods are applied.

The moving average method is a statistical tool with which you can solve a different kind of task. In particular, it is often used in predicting. In Excel, this tool can also be applied to solve a number of tasks. Let's deal with how the moving average is used in Excele.

Meaning this method It is that with it, there is a change in the absolute dynamic values \u200b\u200bof the selected row to the average arithmetic average for a certain period by smoothing the data. This tool is applied to economic calculations, forecasting, in the process of trade on the stock exchange, etc. Apply the moving average method in Excele is best using the most powerful data statistical tool called Package Analysis. In addition, for the same purpose you can use the built-in Excel function Srnzoke.

Method 1: Analysis package

Analysis package It is an Excel add-in, which is disabled by default. Therefore, first of all, it is required to include it.

After this action package "Data analysis"activated, and the corresponding button appeared on the ribbon in the tab "Data".

And now let's look at how directly you can use the package capabilities. Data analysis For work on the method of moving average. Let us, based on information about the income of the company in 11 previous periods, will make a forecast for the twelfth month. To do this, we use the filled with data, as well as tools Package Analysis.

Go to the tab "Data" and click on the button "Data analysis"which is located on the tape ribbon in the block "Analysis".

The list of tools that are available in Pack of analysis. Choose from them the name "Moving average" and click on the button Ok.

The data entry window for predicting the moving average method is launched.
In field "Input interval" Indicate the range of the range where the monthly revenue amount is located without a cell, the data in which should be calculated.

In field "Interval" You should specify the processing interval of the smoothing method. To begin with, let's set the smoothing value of three months, and therefore fit down the figure "3".

In field "Output interval" You need to specify an arbitrary empty range on a sheet where data will be displayed after processing them, which should be one cell more input interval.

You should also install a tick near the parameter "Standard errors".

If necessary, you can also install a tick near the item "Conclusion of the Graphics" For a visual demonstration, although in our case it is not necessarily.

After all settings are made, click on the button Ok.

The program displays the result of processing.

Now perform smoothing for a period of two months to reveal what result is more correct. For these purposes, again launch the tool "Moving average" Package Analysis.
In field "Input interval" Leave the same meanings as in the previous case.

In field "Interval" put a digit "2".

In field "Output interval" Indicate the address of the new empty range, which, again, should be one cell more input interval.

The remaining settings are left for the same. After that we click on the button Ok.

Following this, the program makes the calculation and displays the result on the screen. In order to determine which of the two models are more accurate, we need to compare standard errors. Than less this indicatorThe higher the probability of the accuracy of the result obtained. As we see, in all values, the standard error when calculating a two-month moving smaller than a similar indicator for 3 months. Thus, the predicted value on December can be considered the value calculated by the sliding method for the last period. In our case, this value is 990.4 thousand rubles.

Method 2: Using the function of the SR will

In Excel, there is another way to apply a moving average method. To use it requires to apply whole line standard functions of the program, the basic of which for our purpose is Srnzoke. For example, we will use all the same table of income of the enterprise as in the first case.

Like last time, we will need to create smoothed temporary rows. But this time actions will not be so automated. It is necessary to calculate the average value for every two, and then three months to be able to compare the results.

First of all, we calculate the average values \u200b\u200bin two previous periods using the function Srnzoke. Make it we can, just starting from March, because for more late Dat. There is a break of values.

We highlight the cell in a blank column in the line for March. Next we click on the icon "Insert a function"which is posted near the line of formulas.

A window is activated Masters functions. In category "Statistical" We are looking for a value "SRNNAK", highlight it and click on the button Ok.

The operator arguments window starts Srnzoke. The syntax is as follows:
SRVNOV (number1; number2; ...)

Only one argument is obligatory.

In our case, in the field "Number1" We must specify a link to the range where income is indicated in two previous periods (January and February). Install the cursor in the field and select the corresponding cells on the sheet in the column "Income". After that we click on the button Ok.

As we can see, the result of the calculation of the average value in the two previous periods is displayed in the cell. In order to perform similar calculations for all other months of the period, we need to copy this formula To other cells. To do this, we become a cursor to the lower right angle of the cell containing the function. The cursor is converted to a filling marker, which has a cross-piece. Click the left mouse button and stretch it down to the very end of the column.

We obtain the calculation of the results of the average value in the two previous months before the end of the year.

Now we highlight the cell in the next empty column in the line for April. Call the function arguments window Srnzoke In the same way, which was described earlier. In field "Number1" fit the coordinates of the cells in the column "Income" from January to March. Then press the button Ok.

Using the filling marker, copy the formula in the table cells below.

So, we calculated the meanings. Now, as in the previous time, we will need to find out what kind of analysis is better: with smoothing in 2 or 3 months. To do this, calculate the average quadratic deviation and some other indicators. To begin with, we calculate the absolute deviation using standard feature Excel ABSwhich is instead of positive or negative numbers Returns their module. This value will be equal to the difference between the actual revenue for the selected month and the projected. Install the cursor in the next empty column in the line for May. Call Master of Functions.

In category "Mathematical" Select the name of the function "ABS". Click on the button Ok.

The function arguments window starts ABS. In the only field "Number" Indicate the difference between the contents of the cells in columns "Income" and "2 months" For May. Then press the button Ok.

Using the fill marker, copy this formula to all the table lines on November inclusive.

Calculate the average value of the absolute deviation for the entire period using the already familiar function Srnzoke.

A similar procedure is carried out in order to calculate the absolute deviation for moving for 3 months. First apply the function ABS. Only this time we consider the difference between the contents of the cells with actual income and the planned, calculated using the method of moving average for 3 months.

Next, calculate the average value of all the absolute deviation data using the function Srnzoke.

The next step is to calculate the relative deviation. It is equal to the ratio of absolute deviation to the actual indicator. In order to avoid negative values, we will again take advantage of the opportunities that the operator offers ABS. This time, using this function, we divide the value of absolute deviation when using the moving average method for 2 months on the actual income for the selected month.

But the relative deviation is made to display in percentage. Therefore, we highlight the corresponding range on the sheet, go to the tab "The main"where in the tool block "Number" In a special formatting field, exhibit a percentage format. After that, the result of the calculation of the relative deviation is displayed as a percentage.

A similar surgery calculation operation is done with data with the use of smoothing for 3 months. Only in this case, to calculate as a divide, we use another column of the table, which we have name "ABS. Off (3m) ". Then we translate numeric values \u200b\u200bto the percentage.

After that, calculate the average values \u200b\u200bfor both relative deviation columns, as before using the function for this. Srnzoke. Since to calculate as the function arguments, we take percentage, then no additional conversion is needed. The output operator issues the result as percentage format.

Now we approached the calculation of the average quadratic deviation. This indicator will allow us to directly compare the quality of the calculation when using smoothing for two and for three months. In our case, the average quadratic deviation will be equal to the root square of the sum of the squares of the difference between the actual revenue and the moving average divided by the number of months. In order to make a calculation in the program, we have to use a number of functions, in particular ROOT, Summkvson and SCORE. For example, to calculate the average quadratic deviation when using the smoothing line for two months in May, in our case, the formula of the following type will be applied:
Root (summquance (B6: B12; C6: C12) / Account (B6: B12))

Copy it to other cells of the column with the calculation of the average quadratic deviation through the filling marker.

A similar operation for calculating the average quadratic deviation is performed for a moving average for 3 months.

After that, we calculate the average value for the entire period for both of these indicators by applying the function Srnzoke.

By comparing the calculations by the method of moving average with smoothing in 2 and 3 months according to such indicators, as an absolute deviation, relative deviation and the standard deviation, it is safe to say that smoothing for two months gives more reliable results than the use of smoothing for three months. This is evidenced by the fact that the above indicators on a two-month moving average, less than three months.

Thus, the projected indicator of the income of the enterprise for December will be 990.4 thousand rubles. As you can see, this value completely coincides with the one we received by producing calculation using tools Package Analysis.

We calculated the forecast using the moving average method in two ways. As you can see, this procedure is much easier to perform using tools. Package Analysis. Nevertheless, some users do not always trust automatic calculation and prefer to calculate the function Srnzoke and related operators to check the most reliable option. Although, if everything is done correctly, the result of calculations should be fully the same at the exit.

Moving average or just Ma (moving average)is a medium-tariff price series. The total formula of the sliding average is as follows:

Where:
Ma - moving average;
N-averaging period;
X - stock price values.

For prediction pricing stock For several periods, we use the formula. The price forecast in the next period will equate the values \u200b\u200bof the moving average in the previous period.

We predict using a moving average model the cost of shares Companies Aeroflot (AFLT). For this, we export the campaign quotes from the FINAM.RU site for half the 2009. There will be 20 values.

Schedule of the value of Aeroflot shares The selected period of time is presented below.

Selection of averaging periodn.in the moving average model
The use of larger in the Ma model (n) leads to strong distortion of the data, as a result of which the essential values \u200b\u200bof the price range are averaged, and as a result, the clarity of the forecast is lost, it can be said that it becomes blurry. Using too small averaging period adds more noise component to the forecast. As a rule, the averaging period is selected empirically on historical data.

Build a moving average With a period of averaging in three months Ma (3). To calculate the value of the moving average for the promotion, we use the Excel formula.

SRVNOW (C2: C4)

The "D" column calculated the values \u200b\u200bof the moving average with a period of averaging 3.

After calculating the moving average build a 3 period forecast Forward (for three months ahead). We use the formula to determine the value of the share price, the first forecast value will be equal to the last value of the sliding average. Orange region is the scope of forecasts. C22 will be equal to the value of the sliding average, that is:

C22 \u003d D21 C23 \u003d D22, etc.

From the new projected data of the value of the action calculates the sliding next average.

Build forecast values By moving average for Aeroflot shares for three months ahead. The following is a schedule and forecast shares.