{"id":85,"date":"2019-04-01T18:25:59","date_gmt":"2019-04-01T15:25:59","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=85"},"modified":"2019-05-06T12:27:54","modified_gmt":"2019-05-06T09:27:54","slug":"how-to-use-goal-seek-in-excel","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/how-to-use-goal-seek-in-excel\/","title":{"rendered":"How to use Goal seek in Excel"},"content":{"rendered":"\n<p>Some of the most useful features in Excel, are hidden under the data ribbon button, <strong>What-If analysis<\/strong>. In this tutorial, we will try to explain and use the <strong>Goal Seek<\/strong> feature.<\/p>\n\n\n\n<figure class=\"wp-block-embed-youtube wp-block-embed is-type-video is-provider-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/py441EJThdA?version=3&#038;rel=0&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-large-font-size\"><strong>Practice file:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-file\"><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/Goal-Seek.xlsx\">Goal Seek<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/Goal-Seek.xlsx\" class=\"wp-block-file__button\" download>Download<\/a><\/div>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Transcript<\/strong><\/p>\n\n\n\n<p>This tool allows us to see how one data item in a formula, impacts another.<\/p>\n\n\n\n<p>In our example, we are planning on buying a new car and have\nour eyes on a certain model with a cost of $20000.<\/p>\n\n\n\n<p>Unfortunately, we don\u2019t have the money and have to turn to a\nbank for a loan.<\/p>\n\n\n\n<p>The initial offer from a banker is shown in the current\nspreadsheet.<\/p>\n\n\n\n<p>The rate is 6% per year for 5 years of monthly payments (60\npayments in total). <\/p>\n\n\n\n<p>As we can see, the monthly payment needed to fill the\nrequirement is about $386.<\/p>\n\n\n\n<p>This happens to be over our budget and we want to explore\nmore possibilities.<\/p>\n\n\n\n<p>We feel that we can\u2019t pay more than $300 per month, but we\ncan handle a bigger duration of the loan.<\/p>\n\n\n\n<p>Here is where goal seek kicks in. We choose from the data\nribbon the what-if analysis button, and then the goal seek tool.<\/p>\n\n\n\n<p>In \u201cset cell\u201d, we choose the cell we want to set to a new\nvalue, in our case E7, the value we want to set it to, -300 in our example, and\nthe cell we can change to achieve our goal, C7. <\/p>\n\n\n\n<p>We press OK and the result is a bit more than 81 months. <\/p>\n\n\n\n<p>That sounds a lot. We click on OK to keep the result and\ndecide, that 80 months in total are the top of the duration we can accept. <\/p>\n\n\n\n<p>We change the Term to 80 and the payment increases to $304\napproximately.<\/p>\n\n\n\n<p>We click on goal seek again, to change the target payment to\n-300, but this time the goal is to alter the annual rate of the loan in order\nto achieve it.<\/p>\n\n\n\n<p>We press OK. So, if we can find a bank that can provide 5.6%\nor less, in 80 terms or less, for a $20000 loan, with $300 monthly payments, we\nare within our goals. <\/p>\n\n\n\n<p>Now we can be off to the banks and negotiate a loan, having a more precise idea, of what to expect and what to ask for.<\/p>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Here are some examples of goal seek<\/strong><\/p>\n\n\n\n<p><\/p>\n\n\n\n<p>Use the <strong>Goal seek<\/strong>, to calculate the amount of the monthly payments, so that the period of the loan becomes <strong>10 <\/strong>years.<\/p>\n\n\n\n<figure class=\"wp-block-embed-youtube wp-block-embed is-type-video is-provider-youtube wp-embed-aspect-4-3 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/6KG7g80go_M?version=3&#038;rel=0&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>In the worksheet there is information about a loan. Use the <strong>Goal Seek<\/strong> to calculate, in how<strong> many months the loan would be repaid<\/strong> if the required amount of monthly installments were <strong>900 dollars<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-embed-youtube wp-block-embed is-type-video is-provider-youtube wp-embed-aspect-4-3 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/EjsFezzVAY8?version=3&#038;rel=0&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Some of the most useful features in Excel, are hidden under the data ribbon button, What-If analysis. In this tutorial, we will try to explain and use the Goal Seek feature.<\/p>\n","protected":false},"author":2,"featured_media":88,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[11,10,21],"class_list":["post-85","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-data","tag-goal-seek","tag-what-if-analysis"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/goalseek-1.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-1n","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/85","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/comments?post=85"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/85\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/88"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=85"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=85"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=85"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}