Maxaa-haddii ay dhacdo si fudud loo fahmi karo – si fudud u dhig, su’aashaadu waa, “If this happens, what happens to my numbers, or bottom line? In other words, if we do $20,000 worth of sales over the next few months, how much profit will we show?” Qaabkeeda ugu hooseeya, tani waa waxa -Haddii Falanqaynta^{(What-If Analysis)} loo qorsheeyay in la sameeyo - saadaalinta.

Sida wax kasta oo kale oo ku jira Excel , habkani waa mid adag. Waxa ay ku ogolanaysaa in aad fuliso wax kasta laga bilaabo ilaa xad fudud Maxaa-haddii odoroska ilaa xaalado aad u casrisan. Iyo, sida caadiga ah kiisaska astaamaha Excel , ma jirto si aan u dabooli karo dhammaan suurtagalnimada casharkan gaaban.

Taa baddalkeeda waxaan eegi doonaa aasaaska maanta, waxaanan ku siin doonaa xoogaa fudud oo ah Maxaa-Haddii fikrado aad ku bilowdo.

Samaynta Odoroska aasaasiga ah^{(Making Basic Projections)}

Sida aad u badan tahay inaad ogtahay, gacanta midig, tirada saxda ah ee tirooyinka waa la maamuli karaa si wax uun looga sheego. Shaki la'aan waxaad maqashay tan lagu sheegay siyaalo kala duwan, sida Qashinka, qashinka. ^{(Garbage in, garbage out.)}Ama laga yaabee in saadaaliyayaashu ay u wanaagsan yihiin oo keliya sida malo-awaalkooda.^{(Projections are only as good as their presumptions. )}

Excel waxa uu bixiyaa habab badan oo badan oo lagu dejiyo oo loo isticmaalo What-If Analysis. Haddaba aan eegno habka odoroska ee fudud oo toosan, Shaxanka Xogta. ^{(Data Tables.)}Habkani wuxuu awood kuu siinayaa inaad aragto sida beddelka hal ama laba doorsoomayaal, sida, dheh, inta cashuur ee aad bixiso, u saamayso xariiqda hoose ee ganacsigaaga.

Laba fikradood oo kale oo muhiim ah waa Goal Seek iyo Excel's Scenario Manager . Hadafka Raadinta^{(Goal Seek)} , waxaad isku daydaa inaad qorsheyso waxa kugu dhici doona si aad u gaadho yool hore loo go'aamiyay, sida, waxaad tidhaahdaa, samaynta faa'iido milyan-dollar ah, iyo Maareeyaha Muuqaalka^{(Scenario Manager)} ayaa kuu oggolaanaya inaad abuurto oo aad maamusho ururintaada Maxaa-Haddii^(What-If) (iyo kuwa kale) xaalado.

Habka Jadwalka Xogta - Mid La beddeli karo^{(The Data Tables Method – One Variable)}

Si aan u bilowno, aan abuurno miis cusub oo aan magacowno unugyadayada xogta. Waa maxay sababtu? Hagaag, tani waxay noo ogolaanaysaa inaan ku isticmaalno magacyo qaacidooyinkeena, halkii aan ka isticmaali lahayn isku-duwayaasha unugyada. Ma aha oo kaliya tani waxay noqon kartaa mid faa'iido leh - aad u sax ah oo sax ah - markaad la shaqeyneyso miisaska waaweyn, laakiin dadka qaarkood (aniga oo aan ku jiro) ayaa u fududeeya.

Si kastaba ha ahaatee, aan ku bilowno doorsoome ka dibna u gudubno laba.

• Ku fur xaashi shaqo oo madhan Excel.
• Samee miiska fudud ee soo socda.

Ogsoonow in si aad u abuurto cinwaanka miiska safka 1^{(Row 1)} , waxaan isku daray unugyada A1 iyo B1. Si aad sidaas u yeelatid, dooro labada unug, ka dib, ribbon -ka Guriga , dhagsii falaarta hoose ee ^(Home)Isku-dhafka & Xarunta^{(Merge & Center )} oo dooro Unugyada Isku^{(Merge Cells)} -dhafka .

• Hagaag^(Okay) , hadda aan magacowno unugyada B2 iyo B3. Midig ku dhufo^{(Right-click)} unugga B2 oo dooro Magaca Qeexi^{(Define Name)} si aad u soo qaadato sanduuqa wada hadalka Magaca Cusub^{(New Name)} .

Sida aad arki karto, Magaca Cusub^{(New Name)} waa mid toos ah. Dhanka hoos-u-dhaca baaxadda^(Scope) , tani waxay kuu ogolaanaysaa inaad magacawdo unugga marka loo eego buugga shaqada oo dhan, ama kaliya xaashida shaqada ee firfircoon. Xaaladdan oo kale, khaladaadku waa fiican yahay.

• Guji OK _
• Magaca unugga B3 Growth_2019 , kaas oo sidoo kale ah default, kiiskan, markaa dhagsii OK .
• Dib u magacaw unugga C5 Sales_2019

Hadda ogow haddii aad gujiso mid ka mid ah unugyada aad magacawday, magaca, halkii aad ka heli lahayd isku-xidhka unugga, waxa uu ka muuqdaa sanduuqa Magaca^(Name) (oo hoos ku qoran cas) ee geeska bidix ee sare ee xaashida shaqada.

Si loo abuuro xaaladeena Maxaa-Haddii^(What-If) , waxaan u baahanahay inaan ku qorno qaacido gudaha C5 (hadda Sales_2019 ). Xaashidan odoroska ah ee yar ayaa kuu ogolaanaysa inaad aragto inta lacag ah ee aad samayn doonto boqolkiiba kobaca.

Hadda, boqolleydaasi waa 2. Si aad u hesho jawaabo kala duwan oo ku salaysan boqolleyda kala duwan ee kobaca, markaan dhameyno xaashida xisaabaadka, waxaad si fudud u beddeli doontaa qiimaha unugga B3 (hadda, Growth_2019 ). Laakiin anigu nafteyda ayaan ka hormarayaa.

• Geli^(Enter) qaacidada soo socota unugga C5 (oo lagu qeexay guduudan sawirka hoose):

=Sales_2018+(Sales_2018*Growth_2019)

Markaad dhammayso gelitaanka caanaha, waa inaad ka heshaa lambarka la saadaaliyay ee unugga C5. Waxaad hadda mashruuci kartaa iibintaada ku salaysan boqolkiiba kobaca adiga oo si fudud u beddelaya qiimaha unugga B3.

Horey u soco oo tijaabi U beddelo qiimaha unugga B3 ilaa 2.25%.Hadda isku day, 5% . Ma helaysaa fikradda? Fudud haa, laakiin ma arki kartaa suurtogalnimada?

Habka Jadwalka Xogta - Laba Kala duwanaansho^{(The Data Table Method – Two Variables)}

Miyayna ahayn wax aad u xun inaad ku noolaato adduun uu dhammaan dakhligaagu faa'iido leeyahay - kharash ma lihid! Hoogay^(Alas) , xaalku sidaas maaha; Sidaa darteed, Maxaa-Haddii xaashiyahayadu aanay had iyo jeer u qurux badnayn.

Saadaashayadu waxay sidoo kale u baahan yihiin inay ku xisaabtamaan kharashaadkayaga. Si kale haddii loo dhigo, odoroskaagu wuxuu yeelan doonaa laba doorsoome: dakhli iyo kharash.

Si taas loo dejiyo, aan ku bilowno in aan ku darno doorsoome kale xaashida xaashida ee aan hore u abuurnay.

• Guji unugga A4 oo ku qor Kharashyada 2019^{(Expenses 2019)} , sida tan:

• Ku qor 10.00% unugga B4.
• Midig ku dhufo unugga C4 oo ka dooro Magaca Qeexi^{(Define Name)} liiska soo baxay.
• Sanduuqa wada hadalka Magaca Cusub^{(New Name)} , ku dhufo goobta Magaca^(Name) oo ku qor Kharash_2019.^{(Expenses_2019.)}

Fudud^(Easy) ilaa hadda, sax? Waxa kaliya ee hadhay in la sameeyo waa wax ka beddelka qaacidadayada si loogu daro qiimaha unugga C4, sida tan:

• Wax ka beddel qaacidada unugga C5 sida soo socota (ku dar *Expenses_2019 dhammaadka xogta qaaska.

=Sales_2018+(Sales_2018*Growth_2019*Expenses_2019)

Sida aan hubo inaad qiyaasi karto, What-If's wuxuu noqon karaa mid aad u faahfaahsan, iyadoo ku xiran dhowr arrimood, oo ay ku jiraan xogta aad ku jirto, xirfadahaaga qorista, iyo wixii la mid ah.

Si kastaba ha ahaatee, hadda waxaad samayn kartaa odoroska laba dhinac, dakhliga ( Kobaca^(Growth) ) iyo Kharashyada^(Expenses) . Hore u soco oo beddel qiyamka unugyada B3 iyo B4. Ku xidh nambaradaada oo sii wax yar oo ah Maxaa-Haddii xaashida shaqada uu wareego.

Daraasado Dheeraad ah^{(Additional Studies)}

Sida ku dhawaad ​​wax kasta oo kale oo aad ku samayn karto gudaha Excel , waxaad qaadan kartaa tan Waa maxay-Haddii Falanqaynta muuqaalada qaar ka mid ah xaaladaha qaarkood. Dhab ahaantii, waxaan ka qori karaa maqaallo dhowr ah oo ku saabsan xaaladaha saadaalinta oo aan xitaa u dhowaan in aan mawduuca si faahfaahsan u daboolo.

Dhanka kale, halkan waxaa ah dhowr xiriiriye oo aad u faahfaahsan Maxaa-Haddii^(What-If) qoraallo iyo xaalado.

• Maxaa-Haddii Falanqaynta^{(What-If Analysis)} : Tani si fiican loo sharraxay sida loo eego, waxyaabo kale, Maamulaha^{(Scenario Manager)} Muuqaalka Excel , halkaas oo aad ka abuuri karto oo aad maamuli karto ururintaada Maxaa-Haddii^(What-If) (iyo kuwa kale) xaaladaha.
• Hordhac Maxaa-Haddii Falanqaynta^{(Introduction to What-If Analysis)} : Halkan waxaa ah hordhaca goobta Taageerada Xafiiska Microsoft ee Maxaa-Haddii Falanqaynta. ^{(Microsoft Office Support)}Waxaa jira macluumaad badan oo halkan ku xiran oo leh tiro badan oo faa'iido leh Maxaa-Haddii tilmaamaha.
• Sida loo isticmaalo Goal Seek in Excel Maxaa-Haddii Falanqaynta^{(How to use Goal Seek in Excel for What-If analysis)} : Halkan waxaa ah horudhac u ah Hadafka Excel ee Raadi^{(Goal Seek)} Maxaa-Haddii feature Falanqaynta .^(Analysis)

How To Understand What-If Analysis In Microsoft Excel

A what-if scenario is fairly easy to understаnd – put simply, your queѕtion is, “If this happens, what happens to my numbers, or bottom line? In other words, if we do $20,000 worth of sales over the next few months, how much profit will we show?” At its basest form, this is what What-If Analysis is designed to do – projections.

As with most everything else in Excel, this feature is robust. It allows you to perform everything from relatively simple What-If projections to highly sophisticated scenarios. And, as is usually the case with Excel features, there’s no way I can cover all the possibilities in this short tutorial.

Instead we’ll look at the basics today, and I’ll provide you with a few relatively easy What-If concepts to get you started.

Making Basic Projections

As you probably know, in the right hands, the right set of numbers can be manipulated to say just about anything. You’ve undoubtedly heard this expressed in all sorts of ways, such as Garbage in, garbage out. Or perhaps Projections are only as good as their presumptions.

Excel provides many, many ways to set up and use What-If Analysis. So let’s look at a fairly simple and straightforward projection method, Data Tables. This method enables you to see how changing one or two variables, such as, say, how much taxes you pay, affects your business’s bottom line.

Two other significant concepts are Goal Seek and Excel’s Scenario Manager. With Goal Seek, you try to project what has to happen for you to achieve a predetermined objective, such as, say, making a million-dollar profit, and Scenario Manager allows you to create and manage your own collection of What-If (and other) scenarios.

The Data Tables Method – One Variable

To get started, let’s create a new table and name our data cells. Why? Well, this allows us to use names in our formulas, rather than cell coordinates. Not only can this be useful – much more exact and accurate – when working with large tables, but some people (including me) find it easier.

In any case, let’s start with one variable and then move on to two.

• Open a blank worksheet in Excel.
• Create the following simple table.

Note that to create the table title in Row 1, I merged cells A1 and B1. To do so, select the two cells, then, on the Home ribbon, click the Merge & Center down-arrow and choose Merge Cells.

• Okay, now let’s name cells B2 and B3. Right-click cell B2 and choose Define Name to bring up the New Name dialog box.

As you can see, New Name is straightforward. As for the Scope drop-down, this lets you name the cell relative to the entire workbook, or just the active worksheet. In this case, the defaults are fine.

• Click OK.
• Name cell B3 Growth_2019, which is also the default, in this case, so click OK.
• Rename cell C5 Sales_2019

Now notice that if you click any of these cells you named, the name, instead of the cell coordinate, shows up in the Name box (outlined in red below) in the upper-left corner above the worksheet.

To create our What-If scenario, we need to write a formula in C5 (now Sales_2019). This little projection sheet allows you to see how much money you’ll make by percentage of growth.

Right now, that percentage is 2. To get different answers based on varying percentages of growth, when we finish the spreadsheet, you’ll simply change the value in cell B3 (now, Growth_2019). But I’m getting ahead of myself.

• Enter the following formula in cell C5 (outlined in red in the image below):

=Sales_2018+(Sales_2018*Growth_2019)

When you finish entering the formula, you should get the projected number in cell C5. You can now project your sales based on a percentage of growth by simply changing the value in cell B3.

Go ahead and try it. Change the value in cell B3 to 2.25%. Now try, 5%. Are you getting the idea? Simple yes, but can you see the possibilities?

The Data Table Method – Two Variables

Wouldn’t it be terrific to live in a world where all your income is profit – you have no expenses! Alas, that’s not the case; therefore, our What-If spreadsheets aren’t always so rosy.

Our projections also need to take into account our expenses. In other words, your projection will have two variables: income and expenses.

To set that up, let’s start by adding another variable to the spreadsheet we created earlier.

• Click in cell A4 and type Expenses 2019, like this:

• Type 10.00% in cell B4.
• Right-click in cell C4 and select Define Name from the pop-up menu.
• In the New Name dialog box, click in the Name field and type Expenses_2019.

Easy so far, right? All that’s left to do is modify our formula to include the value in cell C4, like this:

• Modify the formula in cell C5 as follows (add *Expenses_2019 at the end of the parenthetical data.)

=Sales_2018+(Sales_2018*Growth_2019*Expenses_2019)

As I’m sure you can imagine, your What-If’s can be much more elaborate, depending on several factors, including the data you include, your formula-writing skills, and so on.

In any case, now you can make projections from two perspectives, income (Growth) and Expenses. Go ahead and change the values in cells B3 and B4. Plugin your own numbers and give your little What-If worksheet a spin.

Additional Studies

As with almost everything else you can do in Excel, you can take this What-If Analysis feature to some rather elaborate scenarios. In fact, I could write several articles on projection scenarios and not even come close to covering the topic in detail.

Meanwhile, here are a few links to some more elaborate What-If scripts and scenarios.

• What-If Analysis: This well-illustrated how-to looks at, among other things, Excel’s Scenario Manager, where you can create and manage your own collection of What-If (and other) scenarios.
• Introduction to What-If Analysis: Here’s the Microsoft Office Support site’s introduction to What-If Analysis. There’s lots of information here with links to a slew of useful What-If instructions.
• How to use Goal Seek in Excel for What-If analysis: Here’s an introduction to Excel’s Goal Seek What-If Analysis feature.

Related posts

• Sida loo abuuro Gantt Charts gudaha Microsoft Excel

• Casharrada Aasaasiga ah ee Microsoft Excel - Barashada Sida loo Isticmaalo Excel

• 40 ka ugu wanaagsan ee Keyboard-ka Microsoft Excel

• Sida loo isticmaalo Excel's What-If Analysis

• Sida Loo Bedelo Taariikhda Kooxda Microsoft

• Sida loo isticmaalo VLOOKUP gudaha Excel

• Sida loo isticmaalo COUNTIFS, SUMIFS, AVERAGEIFS gudaha Excel

• Sida loo Helo Qiimaha Iswaafajinta gudaha Excel

• Adeegso Excel si aad u ogaato dulsaarka waxtarka leh ee ka imanaya heerka dulsaarka magaca ah

• Sida Gebi-ahaan Loo Tirtirayo Xogta Shakhsi ahaaneed ee Dukumentiyada Microsoft Office

• Sida loo sameeyo Histogram gudaha Excel

• Kudar Khadka Dib-u-Celinta Toosan ee Qorshaha Kala firdhisan ee Excel

• Sida Loo Sameeyo Liiska hubinta gudaha Excel

• Sida loo isticmaalo Sparklines gudaha Excel

• Hagaha Dhammaan Kordhinta Faylka Excel & Waxa ay ula jeedaan

• Sida loogu daro faallooyinka unugga Xaashida shaqada ee Excel

• Isticmaal Hawlaha Kooban si aad u soo koobto Xogta ku jirta Excel

• Isticmaal daaqada daawashada Excel si aad ula socoto unugyada muhiimka ah ee buug-shaqeedka

• Sida loo qariyo xaashiyaha, unugyada, tiirarka, iyo qaaciidooyinka gudaha Excel

• Sida loo abuuro oo loo isticmaalo AutoText gudaha Microsoft Word