ULearnOffice.com

Navigation

Previous
Next
Homepage
FAQ
What's new
Sitemap

Topics

Excel
Office button
Home
Insert
Page Layout
Formulas
Data
Review
View
Developer

Excercises
Autosum
Chart - Insert a second axis
Create a half-moon chart
Create a demographics chart
Conditional formatting
Data table
Goal Seek
Pivot table
Scenario Manager
Solver

Tutorials

Word
Excel 2003
Excel
PowerPoint
Access

Others

Demonstration files
Texte en français

Contact

By e-mail
Join our Newsletter

Share this page

AddThis Social Bookmark Button

Excel/2010 - Exercise on Goal Seek

Introduction
Before we start
Excercice

Introduction

You've completed your business model and want to try out some "What if" possibilities. Specificly, you want to know what you have to do to reach your goal. That's where Excel's Goal Seek comes into play. You can use this option to tell it your goal and let it change one cell in your model to acheive it.

Before we start

To follow this exercise, you can use the file excel2007-2010-exercises.xlsx on the demonstrations files web page.

*Select the Completed Model worksheet.

 

Complet model

Exercice

In a model, there are two types of values: variables and constant. Variables are the values that can change at any time. You want to try simulating possibilities by changing some of these values. Constants don't change in the near term. They change over longer periods of time. For example, you tax rate may not change for many years and suddenly increase (rarely decrease).

This is the model that will be used for most of the Excel exercise’s on this website. The only variable in this model is the growth rate located in the B21 cell. The values for the months of February and March are increased according to that rate. This model, such as it's, shows a profit for the trimester. The content of the D19 cell is 40 031, 25 $. You want to know what growth rate is needed to reach 50 000 $ for the same period. That's your goal. But this option can only let you change the content of one cell. For this example, the cell that we will allow Excel to change is the growth rate witch is cell B21.

In other word, what would have to be the growth rate (B21) to reach a quarterly profit (D19) of 50 000 $?

*Select the Data tab.
*Press the What-If Analysis button.
*Select the Goal Seek option.

*In the Set cell box, select the D19 cell.

It's the cell with the value you want to reach.

*In the To value box, enter 50000.

It's the value you want to reach.

*In the By changing cell box, select the B21 cell.

It's the cell you allow Excel to change so that you can reach your goal.

*Press the OK button.

For this example, goal seek was able to reach the desired goal. But that could not be the case, the next time, you use this option. It always depends on the way you created the model and on the conditions you decided to use. There may be some limitations that would prohibit Excel from reaching the goal you seek. Another limitation for this command is that you can change the content of a single cell. Excel offers other options to change many cells to complete the analysis you seek. For example, a Data Table can give you results by changing a maximum of two variables. The scenario manager can help you view the results of many cells that you have changed and compare them to other scenarios. And the Solver add-in will help you optimize the model if you give it the right rules to do so.the help you optimize you model if you give it the right rules to do so.

 

You like what you read? Share it with your friends.





This site is hosted by 1&1.com