Inkasta oo liiska dheer ee hawlaha Excel uu yahay mid ka mid ah sifooyinka ugu xiisaha badan ee codsiga xaashida xisaabaadka Microsoft, waxaa jira dhowr dhagax oo aan laga faa'iidaysan oo kor u qaadaya hawlahan. Mid ka mid ah aaladaha inta badan la iska indho-tiray waa Maxaa-Haddii Falanqaynta.

Qalabka falanqaynta ee Excel Maxaa-Haddii^{(What-If Analysis)} loo kala qaybiyo saddex qaybood oo waaweyn. Qaybta halkan lagaga hadlay waa sifada Hadafka Raadinta^{(Goal Seek)} ee awooda badan taasoo kuu ogolaanaysa inaad dib uga shaqayso shaqada oo aad go'aamiso agabyada lagama maarmaanka u ah inaad ka hesho wax soo saarka la rabo ee unugga. Akhri si aad u barato sida loo isticmaalo Excel's What-If Analysis Goal Seek tool.

Tusaalaha Qalabka Raadinta Hadafka ee Excel

Ka soo qaad in aad rabto in aad qaadato deyn deyn ah si aad guri u iibsato oo aad ka welwelsan tahay sida heerka dulsaarka deynta uu u saameyn doono lacag bixinta sanadlaha ah. Qadarka amaahda guryaha waa $100,000 waxaadna dib u bixin doontaa deynta muddada 30 sano ah.

Adigoo isticmaalaya shaqada PMT^(PMT) ee Excel , waxaad si fudud u garan kartaa waxa ay noqon lahaayeen lacagaha la bixiyo sanadkiiba haddii dulsaarku ahaado 0%. Xaashiyaha faafinta waxay u badan tahay inuu u ekaado wax sidan oo kale ah:

Unugga A2 wuxuu u taagan yahay heerka dulsaarka sannadlaha ah, unugga B2 waa dhererka deynta sannadaha, unugga C2 waa qaddarka deynta amaahda guryaha. Qaabka ku jira D2 waa:

=PMT(A2,B2,C2)

waxayna ka dhigan tahay bixinta sannadlaha ah ee 30-sano, $100,000 deyn ah 0% dulsaar ah. Ogsoonow^(Notice) in jaantuska ku yaal D2 uu yahay mid taban maadaama Excel uu u maleynayo in lacag-bixintu ay yihiin qulqulka lacagta caddaanka ah ee taban ee booskaaga maaliyadeed.

Nasiib darro, ma jiro amaah-bixiye amaah ah oo ku amaahin doona $100,000 dulsaar 0%. Ka soo qaad^(Suppose) inaad wax ka xisaabinayso oo aad ogaato inaad awoodid inaad dib u bixiso $6,000 sanadkiiba bixinta amaahda guryaha. Waxaad hadda la yaaban tahay waa maxay dulsaarka ugu sarreeya ee aad ku qaadan karto deynta si aad u hubiso inaadan ku dhammaanayn inaad bixiso wax ka badan $6,000 sannadkii.

Dad badan oo xaaladdan ku jira ayaa si fudud u bilaabi doona in ay lambarrada ku qoraan unugga A2 illaa tirada ku jirta D2 ay gaadho ku dhawaad ​​$6,000. Si kastaba ha noqotee, waxaad Excel^(Excel) ka dhigi kartaa inay kuu qabato shaqada adiga oo isticmaalaya aaladda Hadafka Raadinta What-If Analysis^{(Analysis Goal Seek)} . Asal ahaan, waxaad Excel^(Excel) ka dhigi doontaa inay dib uga shaqeyso natiijada D4 ilaa ay ka timaaddo dulsaarka kaas oo ku qanacsan bixinta ugu badnaan ee $ 6,000.

Ku billow adigoo gujinaya tabka xogta ee ^(Data)Ribbon oo aad ka heli karto What-If Analysis badhanka qaybta Qalabka Xogta^{(Data Tools)} . Guji badhanka What-If Analysis oo ka dooro Goal Seek ka menu-ka.

Excel wuxuu furay daaqad yar wuxuuna ku weydiinayaa inaad geliso kaliya saddex doorsoome. Doorsoomka unugyadu^{(Set Cell)} waa inuu noqdaa unug ka kooban qaacido. Tusaalahayaga halkan, waa D2 . Doorsoomka Qiimaha waa qadarka aad rabto in unuga D2 ^{(To Value)}uu^(D2) ahaado dhamaadka falanqaynta.

Anaga, waa -6,000 . Xasuusnoow in Excel uu u arko lacagaha lacag caddaan ah oo taban. Beddelka unuggu^{(By Changing Cell)} waa dulsaarka aad rabto in Excel kuu helo si 100,000 amaahda guryaha ah kuugu kici doonto $6,000 sanadkii. Markaa isticmaal unugga A2 .

Guji badhanka OK waxaana laga yaabaa inaad dareento in Excel uu soo ifbaxayo tiro tirooyin ah unugyada kala duwan ilaa inta ay ku celcelintu ugu dambayntii isugu imanayaan lambar kama dambays ah. Xaaladeena, unugga A2 waa inuu hadda akhriyaa qiyaastii 4.31%.

Falanqayntani waxay noo sheegaysaa in si aanad ugu bixin wax ka badan $6,000 sannadkii 30-sano, $100,000 deyn ah, waxaad u baahan tahay inaad ku hubiso deynta wax aan ka badnayn 4.31%. Haddii aad rabto inaad sii wadato samaynta waxa-haddii falanqaynta, waxaad isku dayi kartaa isku darka tirooyin kala duwan iyo doorsoomayaal si aad u sahamiso fursadaha aad haysato marka aad isku dayayso inaad hesho dulsaar wanaagsan oo amaah ah.

Excel's What-If Analysis The Goal Seek^{(What-If Analysis Goal Seek)} tool waa mid awood leh oo kaabaya shaqooyinka kala duwan iyo qaababka laga helo xaashida caadiga ah. Markaad dib uga shaqeyso natiijada qaacidada unugga, waxaad si cad u sahamin kartaa doorsoomayaasha kala duwan ee xisaabintaada.

Using Excel’s What-If Analysis Goal Seek Tool

Although Excel’s long lіst of functions іs one of the most enticing featurеs of Microsoft’s spreadsheet application, there a few underutilizеd gems that enhance these functions. One oftеn-overlooked tool is thе What-If Analysis.

Excel’s What-If Analysis tool is broken down into three main components. The part discussed here is the powerful Goal Seek feature that lets you work backwards from a function and determine the inputs necessary to get the desired output from a formula in a cell. Read on to learn how to use Excel’s What-If Analysis Goal Seek tool.

Excel’s Goal Seek Tool Example

Suppose that you want to take out a mortgage loan to buy a house and you are concerned about how the interest rate on the loan will affect the yearly payments. The amount of the mortgage is $100,000 and you will pay back the loan over the course of 30 years.

Using Excel’s PMT function, you can easily figure out what the yearly payments would be if the interest rate were 0%. The spreadsheet would likely look something like this:

The cell at A2 represents the yearly interest rate, the cell at B2 is the length of the loan in years, and the cell at C2 is the amount of the mortgage loan. The formula in D2 is:

=PMT(A2,B2,C2)

and represents the yearly payments of a 30-year, $100,000 mortgage at 0% interest. Notice that the figure in D2 is negative since Excel assumes that the payments are a negative cash flow from your financial position.

Unfortunately, no mortgage lender is going to lend you $100,000 at 0% interest. Suppose you do some figuring and find out that you can afford to pay back $6,000 per year in mortgage payments. You are now wondering what is the highest interest rate you can take on for the loan to make sure you don’t end up paying more than $6,000 per year.

Many people in this situation would simply start typing numbers in cell A2 until the figure in D2 reached approximately $6,000. However, you can make Excel do the work for you by using the What-If Analysis Goal Seek tool. Essentially, you will make Excel work backwards from the result in D4 until it arrives at an interest rate that satisfies your maximum payout of $6,000.

Begin by clicking on the Data tab on the Ribbon and locating the What-If Analysis button in the Data Tools section. Click on the What-If Analysis button and choose Goal Seek from the menu.

Excel opens up a small window and asks you to input only three variables. The Set Cell variable must be a cell that contains a formula. In our example here, it is D2. The To Value variable is the amount you want the cell at D2 to be at the end of the analysis.

For us, it is -6,000. Remember that Excel sees payments as a negative cash flow. The By Changing Cell variable is the interest rate you want Excel to find for you so that the $100,000 mortgage will cost you only $6,000 per year. So, use cell A2.

Click the OK button and you may notice that Excel flashes a bunch of numbers in the respective cells until the iterations finally converge on a final number. In our case, the cell at A2 should now read about 4.31%.

This analysis tells us that in order not to spend more than $6,000 per year on a 30-year, $100,000 mortgage, you need to secure the loan at no more than 4.31%. If you want to continue doing what-if analyses, you can try different combinations of numbers and variables to explore the options you have when trying to secure a good interest rate on a mortgage.

Excel’s What-If Analysis Goal Seek tool is a powerful complement to the various functions and formulas found in the typical spreadsheet. By working backwards from the results of a formula in a cell, you can explore the different variables in your calculations more clearly.

Related posts

• Sida loo isticmaalo Excel's What-If Analysis

• Sida loo Isticmaalo Tilmaamaha Unugyada Hadalka ee Excel

• 2 siyaabood oo loo Isticmaalo Shaqada Transpose ee Excel

• Sida Loo Fahmo Maxaa-Haddii Falanqaynta Microsoft Excel

• Sida loo tirtiro khadadka banaan ee Excel

• Sida loo Isticmaalo Tixraacyada Absolute ee Excel

• Hagaha VBA Sare ee MS Excel

• Sida loo Isticmaalo If iyo Nsted Haddii Odhaahyada Excel

• 3 Siyood Oo Unugyada Excel Loogu Kala Qaybiyo

• 5 siyaabood oo loogu badalo qoraalka nambarada gudaha Excel

• Sida loo sameeyo shaxda Pie ee Excel

• Sida Loo Raadiyo Excel

• Sida Loogu Mideeyo Xogta Faylal Badan oo Excel ah

• Sida Dhakhso Loo Geliyo Safaf Badan gudaha Excel

• 4 siyaabood oo Excel loogu badalo Google Sheets

• Si degdeg ah ugu qor nambarada Word iyo Excel

• Sida loo Hagaajiyo #N/A Khaladaadka gudaha Foomamka Excel sida VLOOKUP

• Ku xidhida Excel iyo MySQL

• Sida loo Furo Word iyo Excel si Badbaado ah

• Hal-Column iyo Xog-urur badan oo Aasaaska ah oo ku kala soocida xaashida faafinta ee Excel