How To Use Goal Seek Tool In Ms Excel – What If Analysis Business Tool

The Goal Seek tool is one of the powerful tools of MS Excel used to determine the correct value for one of the arguments of a function that yielded or returned a particular result or answer. It works on the basis of what-if analysis and can also be used to solve practical business problems such as word problems in Mathematics, etc. In this tutorial, I will give you a step by step guide on how you can manipulate and use the MS Excel Business tool: the Goal Seek tool to solve business problems. Then I will give you some practical exercises on what-if analysis to test your knowledge.
step by step practical tutorial on how to use the goal seek tool in ms excel

One of the practical problems that can be solved using Goal Seek goes like this: If 720 is the answer gotten by multiplying 40 by a certain number, what must be this number or figure?

This is part 3 of chapter 6 of the Free Online Excel Tutorial Series at Microsoft Tutorial website. In the previous part (part 2), I introduced the what-if analysis tools and explained how use the scenario manager and data tables in ms excelAlso see the full tutorial course content of this Microsoft Excel Tutorial Series.

SOLUTION THE ABOVE PROBLEM USING THE GOAL SEEK TOOL IN MS EXCEL


To get the correct number using the Goal Seek tool, prepare your worksheet like the one shown below.
First goal seek worksheet
Type 40 which is the known or correct variable in cell B2, then type any number of your choice for the unknown or incorrect variable in cell B3. Then for the expected answer, type the following answer in cell B4: =B2*B3 and press the Enter key.

Click the Data tab, then click the What-if Analysis drop down arrow located in the Data tools group.

Then Select Goal Seek from the resulting options.

This prompts the Goal Seek dialogue box. Enter the values and cell references as shown below.
Enter values in the goal seek dialogue box
MS Excel requires you to provide three parameters or values in the 3 text boxes shown above. The required parameters are:

1. SET CELL: This is the cell that contains the formula that MS Excel uses for the Goal Seek analysis. In the case of the above example, the set cell is cell B4 (the absolute cell reference is $B$4).

2. TO VALUE: This cell holds the desired answer. In this case your desired answer is 720.

3. BY CHANGING CELL: This cell holds the cell reference of the cell containing the incorrect or unknown variable. In this case, its cell B3 (the absolute cell reference is $B$3).

Click OK twice. Your worksheet should now resemble the one shown below with cell B3 now containing the correct value for the specified answer in cell B4.
Final goal seek worksheet

A MORE PRACTICAL ILLUSTRATION OF GOAL SEEK

Now let me illustrate Goal Seek in MS Excel with a more practical step by step example.

QUESTION:

Joe-Links Business center made a total profit of $100000 as at December 2016. The company’s Manage proposed a profit of $180000 for the year 2017. Assuming they offer 50 services, at what price should they offer each of their services to meet up with the proposed income?

SOLUTION:

Prepare your MS Excel worksheet like the one shown below. 
Joe-links business center proposed income worksheet
Type 50 in cell B2. Type in any value of your choice in cell B3 or leave it blank. Then enter the formula: =B2*B3 in cell B4.

Activate the Goal Seek dialogue box using the procedure given in the previous example (that is Data tab > What-If Analysis (in Data tools panel) > Goal Seek).

Enter $B$4 in the SET CELL text box.

Enter 180000 in the TO VALUE text box.

Enter $B$3 in the BY CHANGING CELL text box as shown below.
Enter the following values in the goal seek dialogue box

NOTE:

You can also use the bolts located at the extreme right of each text box to specify the cells directly by first clicking on the bolt, specifying the cell and then clicking on the bolt again to submit the selection.
Click OK twice.

Your Excel worksheet should now look like the one shown below.
Joe-links business center proposed income output worksheet
Cell B3 contains $3600, which is the amount the company should offer each service in order to meet up with the proposed profit for the year 2017.

Recommended MS Excel Textbook

Click Here to know more about the book.

Now let me test your knowledge on PMT function and What-if analysis tools.

PRACTICAL EXERCISES ON WHAT-IF ANALYSIS

Please try to practice these exercises and theoretical questions on our own as it will go a long way to help you.

1. What is Amortization of loan?

2. Explain Scenario Manager as what-if analysis tool.

3. What are the functions of Scenarios?
4. Explain briefly the two types of data tables used in what if analysis.

5. Mr. Jerry is considering going for a loan of $500000 with the interest rate of 6% to be paid back within a period of 5 months. If the bank decides to offer him the following loan amounts:

$550000, $600000, $650000 and $700000 with the interest rate of 6.5%, 7%, 7.5%, and 8% respectively to be paid back within 6 months, 7 months, 8 months and 9 months respectively, calculate his monthly payment and total payment to amortize the loans.

Also calculate the bank’s profit at the end of each period for each loan. Also generate a scenario summary.

6. You are the Manager of ABC Business Center and you decided to offer more services in order to increase the business profit from $90000 to $150000. Assuming you offer each service for $100, use Goal Seek to find out how many services you must offer in order to meet up with your proposal.

This is the end of part 3 of chapter 6 of this online tutorial series. In the next chapter (chapter 7), I will explain how to create excel worksheet template and data form in MS Excel and advanced Excel worksheet operations.

Was this tutorial helpful to you?

Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.

Also click Here to subscribe for free so that you will get our latest game updates in your email.

Recommended Entrepreneurship Books

the passionate entrepreneurs strategies book by buzzer joseph

The Passionate Entrepreneur’s Strategies: Learn how to discover business ideas that will be successful, grow multiple online income streams, make money from home, attract new and retain customers, applications of SWOT analysis in any business, skills that will help you succeed as an entrepreneur, self-discipline, etc. Lucrative Business Ideas Series Book 1. By Buzzer Joseph. Read more about the book.

how to make money from blogging and affiliate marketing book by buzzer joseph

How to Make Money from Blogging and Affiliate Marketing: Learn the Step by Step Guide of Creating, Growing and Optimizing your WordPress Blog from Scratch for Search Engines (SEO) and How to Make Money through Google AdSense, Affiliate Marketing and Other Strategies for Earning Passive Income Online. Lucrative Business Ideas Series Book 2. By Buzzer Joseph. Read more about the book.

how to make money self publishing kindle books by buzzer joseph

How to Make Money Self-Publishing Kindle Books for Beginners: Learn the Step by Step Guide to Self-Publish a Best Selling Book on Amazon KDP, How to Design Book Covers with Adobe Photoshop, Format, Edit and Proofread your Book Manuscript with Microsoft Word, Promote your Book on Various Online Platforms and Earn Monthly Passive Income Online. Lucrative Business Ideas Series Book 3. By Buzzer Joseph. Read more about the book.

diy grid and off-grid solar pv installation guide by buzzer joseph

Complete Solar PV System Installation Guide: DIY guide on how to install grid-connected and off-grid solar PV systems, how to build solar-powered electric cars, convert conventional cars to electric cars, online and offline ways to make money from solar energy sector (including stocks), electrical wiring and protection system installation guides, plus free resources for skill acquisition and personal development. Lucrative Business Ideas Series Book 4. By Buzzer Joseph. Read more about the book.

how to make money from cryptocurrency trading and investments by buzzer joseph

How to Make Money from Crypto Trading and Investing for Beginners: Step by step guide with screenshots on the basics of blockchain technology, bitcoin and altcoins, crypto security tips, cryptocurrency market analysis (FA and TA), long term and short term trading, how to calculate your profits in excel, legit ways to make money with cryptocurrencies, DeFi yield farming and staking, how to use any centralized and decentralized exchange platform and more, plus crypto investment bonus! Lucrative Business Ideas Series Book 5. By Buzzer Joseph. Read more about the book.

blockchain practical guide kindle book by buzzer joseph

Blockchain Practical Guide: Step by step guide with pictures, on how to monetize the latest blockchain technology trends: crypto trading and tools, especially Ethereum, DeFi, Staking, NFTs, Top 100 Play-to-Earn (P2E) and NFT games, Web 3.0, Metaverse, Virtual Reality (VR), Augmented Reality (AR), blockchain job opportunities, with crypto investment bonus! Lucrative Business Ideas Series Book 6. By Buzzer Joseph. Read more about the book.

how to make money with blockchain web3 apps kindle book by buzzer joseph and kelly joseph

How to Make Money with Blockchain Web3 Apps: Step by Step Guide with Screenshots and Videos on How to Make Money from Blockchain and Web3 Projects – NFTs, Play-to-Earn (P2E) Games, Move-to-Earn (M2E) Apps, Watch-to-Earn (W2E) Apps, SocialFi and GameFi Apps, with crypto investment bonus! Lucrative Business Ideas Series Book 7. By Buzzer Joseph and Kelly Joseph. Read more about the book.

No comments:

Post a Comment

WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!