Home » Budget Spreadsheet » How To Make a Cost Analysis Spreadsheet
How To Make a Cost Analysis Spreadsheet

How To Make a Cost Analysis Spreadsheet

How To Make a Cost Analysis Spreadsheet – Data table is a range of cells in which you can change values for some of the cells and pose multiple responses to a problem. A good example is to use the PAYMENT function with different loan amounts and interest rates to find out what loan can be afforded for a house or a vehicle. Experimentation with values that can be modified to obtain different results is part of a discipline called data analysis.

Basics of Data Tables

You can create one or two variable data tables, depending on the number of variables and formulas you want to test.

Data tables of a variable Use a data table of a variable if you want to see how different values ​​of a variable in one or more formulas will change the results of those formulas. For example, you can use a one-variable data table to see how different interest rates affect the monthly mortgage payment using the PAYMENT function. Type the values ​​of variables in a column or row and the results will appear in the adjacent column or row.

In the following illustration, cell D2 contains the payment formula, = PAYMENT (B3 / 12, B4, -B5), which refers to the input cell B3.

Data table with one variable

Two-variable data table Use a two-variable data table to see how different values ​​of two variables in a formula will change the results of the same. For example, you can use a two-variable data table to see how different combinations of interest rates and loan terms will affect the monthly mortgage payment.

In the following illustration, cell C2 contains the payment formula, = PAYMENT (B3 / 12, B4, -B5), which uses two input cells, B3 and B4.

Data table with two variables

Data Table Calculations Data tables are updated when a worksheet is updated, even if they have not changed. To speed up the calculation of a worksheet that contains a data table, you can change the Calculate options so that the worksheet is updated automatically but not the data tables. See the section Accelerating calculations in a spreadsheet with data tables.

Create a data table of a variable

A data table of a variable has input values ​​that appear in a column (column-oriented) or in a row (row-oriented). Formulas that are used in the table of a variable must refer to a single input cell.

Type the list of values ​​that you want to replace in the input cell in a column or row. Leave a few empty rows and columns on either side of the values.

Do one of the following:

If the data table is column oriented (variable values ​​are in a column), type the formula in the cell located one row higher and one cell to the right of the column of values. The illustration of the data table of a variable shown in the General Information section is column-oriented and the formula is inside cell D2.If you want to examine the effects of different values ​​in other formulas, write the additional formulas in the Cells to the right of the first formula.

If the data table is rows-oriented (variable values ​​are in a row), type the formula in the cell located one column to the left of the first value and one cell below the row of values. If you want to examine the effects of different values ​​in other formulas, type the additional formulas in the cells below the first formula.

Select the range of cells that contain the formulas and values ​​that you want to replace. According to the first illustration in the previous section General Information, this range is C2: D5.

On the Data tab, in the Data Tools group, or in the Forecasting group (in Excel 2016), click Hypothesis Analysis, and then click Data Table.

Do one of the following:

If the data table is column-oriented, type the cell reference for the input cell in the Input cell (column) box. If we use the example of the first illustration, the input cell is B3.

If the data table is rows-oriented, type the cell reference of the input cell in the Input cell (row) box.

NOTE: After you create the data table, you may want to change the formatting of the result cells. In the illustration, the result cells are in the currency format.

Add a formula to a data table of a variable

Formulas that are used in the data table of a variable must refer to the same input cell.

Do one of the following:

If the data table is column-oriented (variable values ​​are in a column), enter the new formula in a blank cell to the right of an existing formula in the top row of the data table.

If the data table is row-oriented (variable values ​​are in a row), enter the new formula in a blank cell below an existing formula in the first column of the data table.

Select the range of cells that contains the data table and the new formula.

On the Data tab, in the Data Tools group, or in the Forecasting group (in Excel 2016), click

Create a two-variable data table

A two-variable data table uses a formula that contains two lists of input values. The formula must refer to two different input cells.

In a worksheet cell, type the formula that references the two input cells.

In the following example, where the initial values ​​of the formula are specified in cells B3, B4 and B5, the formula = PAY (B3 / 12, B4, -B5) is written to cell C2.

Write a list of input values ​​in the same column, below the formula.

In this case, write the different interest rates in cells C3, C4 and C5.

Specify the second list in the same row as the formula, on the right.

Enter the loan terms (in months) in cells D2 and E2.

Select the range of cells in the formula (C2), both the row and the column of values ​​(C3: C5 and D2: E2) and the cells in which you want the calculated values ​​(D3: E5).

In this case, select the range C2: E5.

On the Data tab, in the Data Tools group, or in the Forecasting group (in Excel 2016), click Hypothesis Analysis, and then click Data Table.

In the Input cell (row) box, type the reference of the input cell for the input values ​​in the row.
Type cell B4 in the Input Cell (Row) box.

In the Input cell (column) box, type the reference of the input cell for the input values ​​of the column.
Type B3 in the Input cell (column) box.

Click OK.

Example A two-variable data table can show how the different interest rates and loan terms affect the monthly mortgage payment. In the following example, cell C2 contains the payment formula, = PAYMENT (B3 / 12, B4, -B5), which uses two input cells, B3 and B4.

Data table with two variables

Accelerate calculations on a spreadsheet with data tables

Do one of the following:

In Excel 2007, click the Microsoft Office Button Picture of the Microsoft Office Button, click Excel Options, and then click the Formulas category.

In all other versions, click File> Options> Formulas.

In the Calculation Options section, under Book Calculation, click Automatic except for data tables.

Next steps

You can use other Excel tools to perform hypothesis analyzes if you have specific goals or larger sets of variable data.

Goal search

If you know what result you want to get from a formula, but you’re not sure what input value the formula needs to get for that result, use the Find Target feature. See Use Target Search item to find the desired result by adjusting an input value.

Excel Solver

You can use the Excel Solver add-in to find the optimal value based on a series of variables. Solver works with a group of cells called decision variables, or simply cells of variables, that are used in the computation of formulas in the target and constraint cells. Solver adjusts the values ​​in the decision variable cells to meet the limits in the constraint cells and produce the desired result for the target cell. See the article Define and solve a problem with Solver.

How To Make a Cost Analysis Spreadsheet