Page 664 - Excel 2013 All-in-One For Dummies

646 Using Data Tables
At the end of the chapter, I introduce you to the Solver add-in utility, which enables you to find solutions to more complex what-if problems involving multiple variables. You can use the Solver to help you with classic resource problems, such as finding the correct product mix in order to maximize your profits, staffing to minimize your general costs, and routing to minimize transportation costs.
Using Data Tables
In an Excel spreadsheet, you can see the effect of changing an input value
on the result returned by a formula as soon as you enter a new input value
in the cell that feeds into the formula. Each time you change this input
value, Excel automatically recalculates the formula and shows you the new result based on the new value. This method is of limited use, however, when you are performing what-if analysis and need to be able to see the range of results produced by using a series of different input values in the same work- sheet so that you can compare them to each other.
To perform this type of what-if analysis, you can use Excel’s Data Table com- mand. When creating a data table, you enter a series of input values in the worksheet, and Excel uses each value in the formula that you specify. When Excel is finished computing the data table, you see the results produced
by each change in the input values in a single range of the worksheet. You can then save the data table as part of the worksheet if you need to keep a record of the results of a series of input values.
When creating data tables, you can create a one-variable or a two-variable data table. In a one-variable data table, Excel substitutes a series of different values for a single input value in a formula. In a two-variable data table, Excel substitutes a series of different values for two input values in a formula.
Creating a one-variable data table
To create a one-variable data table, you need to set up the master formula
in your worksheet and then, in a different range of the worksheet, enter the series of different values that you want substituted for a single input value in that formula. Figures 1-1 and 1-2 demonstrate how this is done.
In Figure 1-1, cell B5 contains a simple formula for computing the projected sales for 2014, assuming an annual growth rate of 1.75% over the annual sales in 2014. The 2014 projected sales in this cell are calculated with the following formula:
=Sales_2103+(Sales_2013*Growth_2104)
This formula adds cell B2 (named Sales_2013) to the contents of B2 multi- plied by the growth rate of 1.75% in cell B3 (named Growth_2014). Cell B5 shows you that, assuming an annual growth rate of 1.75% in the year 2014, you can project total sales of $890,312.50.