Goal Seek and Solver - Essay Example

The miles per hour are constant, and Excel is goal seeking to determine the number of miles traveled. A data table is a collection of cells that display how changing certain value in worksheet formulas affects the result of those applied formulas. Data tables provide a shortcut for calculating multiple versions in one operation, and a way to view and compare the results of all of the different variations together on one's worksheet. Using the bicycle example again, one could create a table that summarizes the number of miles traveled at different speeds and different elapsed minutes traveled.

Excel can save a set of values and substitute them automatically in a worksheet to allow one to forecast the outcome of a worksheet model. One can create and save different scenarios on a worksheet, and then switch to any of these scenarios to view different results. For the bicycle example, one could switch between two or more different number of miles traveled using combinations of different speeds and elapsed minutes traveled. Using solver, one can find an optimal value for a formula in a target worksheet cell.

Solver works with a group of cells related to a target cell's formula (Paul 2005, p. xix). Solver changes the values of adjustable cells to produce the desired results one specifies in the target cell formula. One can also apply upper, lower, and exact constraints to restrict the values Solver can choose from to adjust the cells. Using the bicycle example again, one could determine the least and greatest possible number of miles traveled at a given speed and distance.Here's a summary of when one would use each of these tools:Use Goal seeker when one wants to find the correct single input value to achieve the desired single output value.

Use Solver to find the best solution to problems that revolve around the manipulation of several changing cells, variables, and constraints (Paul 2005, p. xx). The productivity of electricity is causing profit deterioration. Is electricity being wasted Or is it due to a change in manufacturing process Is it possible to cut down the electric consumption without affecting the production If so, what should be the electricity consumption for it not to affect profitability negatively The answers to some of these questions can be determined by using the "Goal Seeker" and the "Solver" features of Excel.

Goal Seeker and Solver features are found in the Tools means of Microsoft Excel. Goal Seeker is the opposite of What-if analysis. Using Goal seeker, we begin with the target value in a dependent cell and determine the correspondent value in the independent cell upon which the target cell is dependent. Goal seeker and solver can change independent cell values, but only Solver can change a range of cells and accept constraints, making it a powerful feature. It can also be used to solve complex optimization problem such as linear programming and integer programming problems.

Following is a description of finding answers to some of the questions above:What should be the electricity consumption for it not to affect profitability negatively As shown in figure 2, the company is losing $748 because it is currently using 48,000 kWh of electricity. To reduce this loss to zero, go to Tools menu and choose Goal Seeker, and set cell Q15 (profitability of total energy) to zero, by changing