How to configure and use the Excel forecasting system | Productivity

Microsoft Excel is a spreadsheet editor with powerful features for comparing data. The program also has a sophisticated system of logical analysis and forecasts that facilitates calculations and estimates with mathematical operations.

To use these tools, the user must access commands from the Data tab and configure certain characteristics of the spreadsheets. Check out in this tutorial how to use the features of the Excel forecasting system.

Seven Excel functions that few people know

How to create charts in Microsoft Excel

How to create charts in Microsoft Excel

Hypothesis Testing: Achieving Goals

Step 1. The Achieve Goals feature causes Excel to estimate a value according to the estimated goal for a formula. In the highlighted example, the goal is to know how much to spend on books to not exceed a budget. To use this tool, on the Data tab, select the Hypothesis Test button and choose Achieve Goals;

On the Data tab, press the Hypothesis Test command and select Achieve Goals Photo: Reproduo / Daniel RibeiroOn the Data tab, press the Hypothesis Test command and select Achieve Goals Photo: Reproduo / Daniel Ribeiro

On the Data tab, press the Hypothesis Test command and select Achieve Goals Photo: Reproduo / Daniel Ribeiro

Step 2. Then, select the Define cell field and choose the cell with the sum formula in its table. In this case, there must be a sum in the spreadsheet for the resource to work;

In Define cell, insert the field with the formula you want to use for estimation Photo: Reproduo / Daniel RibeiroIn Define cell, insert the field with the formula you want to use for estimation Photo: Reproduo / Daniel Ribeiro

In Define cell, insert the field with the formula you want to use for estimation Photo: Reproduo / Daniel Ribeiro

Step 3. In the For value field, type the total you want to reach in the formula previously selected;

In the highlighted field, enter the goal you want to achieve Photo: Reproduo / Daniel RibeiroIn the highlighted field, enter the goal you want to achieve Photo: Reproduo / Daniel Ribeiro

In the highlighted field, enter the goal you want to achieve Photo: Reproduo / Daniel Ribeiro

Step 4. In the Alternating cell field, type the cell whose value will be estimated, according to the goal defined for the formula previously selected;

In Alternando clula insert the field to be estimated Photo: Reproduo / Daniel RibeiroIn Alternando clula insert the field to be estimated Photo: Reproduo / Daniel Ribeiro

In Alternando clula insert the field to be estimated Photo: Reproduo / Daniel Ribeiro

Step 5. Press Ok to finish. Excel will do the calculation, present a window with a small report of the operations, while the estimated value for the goal will be inserted in the spreadsheet automatically;

Upon completion of the operation, the amount entered in the spreadsheet and small report will be presented Photo: Reproduo / Daniel RibeiroUpon completion of the operation, the amount entered in the spreadsheet and small report will be presented Photo: Reproduo / Daniel Ribeiro

Upon completion of the operation, the amount entered in the spreadsheet and small report will be presented Photo: Reproduo / Daniel Ribeiro

Step 1. To use the Forecast Worksheet feature, you need to use a number sequence in the main columns name line. In the example below, the months have been transformed into their respective numbers;

The Forecast Worksheet only works if the column names are in a sequence of numbers Foto: Reproduo / Daniel RibeiroThe Forecast Worksheet only works if the column names are in a sequence of numbers Foto: Reproduo / Daniel Ribeiro

The Forecast Worksheet only works if the column names are in a sequence of numbers Foto: Reproduo / Daniel Ribeiro

Step 2. Then, select the entire table you want to turn into a Forecast Sheet and, on the Data tab, click on the resource button highlighted in the image in the upper right corner;

Select the table and, in the Data tab, press Forecast Sheet Photo: Reproduo / Daniel RibeiroSelect the table and, in the Data tab, press Forecast Sheet Photo: Reproduo / Daniel Ribeiro

Select the table and, in the Data tab, press Forecast Sheet Photo: Reproduo / Daniel Ribeiro

Step 3. A window will open showing the Forecast Worksheet. Define the values ​​and the format of the displayed graphs and complete the operation in the Create button;

Choose the graphic template of the Forecast Worksheet and press Create Photo: Reproduo / Daniel RibeiroChoose the graphic template of the Forecast Worksheet and press Create Photo: Reproduo / Daniel Ribeiro

Choose the graphic template of the Forecast Worksheet and press Create Photo: Reproduo / Daniel Ribeiro

Step 4. At the end of the operation, Microsoft Excel will create a new sheet tab with the values ​​and graphs determined by the Forecast Sheet;

The Forecast Worksheet will be assembled in a new Excel spreadsheet Photo: Reproduo / Daniel RibeiroThe Forecast Worksheet will be assembled in a new Excel spreadsheet Photo: Reproduo / Daniel Ribeiro

The Forecast Worksheet will be assembled in a new Excel spreadsheet Photo: Reproduo / Daniel Ribeiro

Hypothesis Testing: Data Table Function

Step 1. The Data Table allows you to create an estimate by multiplying and adding two or more values. The following example seeks to know the total sale with new values ​​for the prices of two products;

The Data Table estimates different values ​​in a series of mathematical operations Foto: Reproduo / Daniel RibeiroThe Data Table estimates different values ​​in a series of mathematical operations Foto: Reproduo / Daniel Ribeiro

The Data Table estimates different values ​​in a series of mathematical operations Foto: Reproduo / Daniel Ribeiro

Step 2. For the feature to work, I need one of the estimates to be in a row sequence, while the other one is in a row sequence, as in the image below. The total of the mathematical operations that will be calculated must be at the meeting between the sequences, as indicated by the arrow in the image;

Place the values ​​that will be estimated in the format indicated in the figure Photo: Reproduo / Daniel RibeiroPlace the values ​​that will be estimated in the format indicated in the figure Photo: Reproduo / Daniel Ribeiro

Place the values ​​that will be estimated in the format indicated in the figure Photo: Reproduo / Daniel Ribeiro

Step 3. Then, select the table with the indicated strings and, on the Data tab, select Hypothesis Test and then the Data Table option …;

Check the table created and, under Data, click on Hypothesis Test and choose Data Table ... Photo: Reproduo / Daniel RibeiroCheck the table created and, under Data, click on Hypothesis Test and choose Data Table ... Photo: Reproduo / Daniel Ribeiro

Check the table created and, under Data, click on Hypothesis Test and choose Data Table … Photo: Reproduo / Daniel Ribeiro

Step 4. In the fields of the resource window, enter the original values ​​that were operationalized in mathematical accounts, indicating what is in the sequence of lines and the other in the column. In the example, the values ​​of the products in the previous month were chosen – including, the number of items sold the total value previously indicated;

Write the values ​​that will be estimated, indicating what is in the row and what is in the column Photo: Reproduo / Daniel RibeiroWrite the values ​​that will be estimated, indicating what is in the row and what is in the column Photo: Reproduo / Daniel Ribeiro

Write the values ​​that will be estimated, indicating what is in the row and what is in the column Photo: Reproduo / Daniel Ribeiro

Step 5. After confirming in Ok, the table will be assembled indicating the total in each value estimate defined by the original rows and columns;

The result will display a table with the mathematical operations between each value Photo: Reproduo / Daniel RibeiroThe result will display a table with the mathematical operations between each value Photo: Reproduo / Daniel Ribeiro

The result will display a table with the mathematical operations between each value Photo: Reproduo / Daniel Ribeiro