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
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 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 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 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 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 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 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 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 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 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 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 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 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 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 Ribeiro