Financial Modeling in Excel For Dummies by Danielle Stein Fairhurst

Author:Danielle Stein Fairhurst
Language: eng
Format: epub, pdf
ISBN: 9781119357551
Publisher: Wiley
Published: 2017-04-14T00:00:00+00:00

The syntax looks like this:

=IF(statement being tested, value if true, value if false),

So, for this example, in plain language, the syntax looks like this:

=IF(the weather is sunny,go to the beach,stay home),

Written in an Excel formula, if the weather has been put into cell A1, the formula would look like this (see Figure 7-22):

=IF(A1=“Sunny”,”go to the beach”,”stay home”)

FIGURE 7-22: Inserting an IF function using the Function Arguments dialog box.

When using text within a formula, as you’re doing in this example, you must put quotation marks (““) around any text. However, if you use the IF Function Arguments dialog box, as shown in Figure 7-22, there is no need to put the quotation marks in manually — the dialog box will do it for you.

The IF function can be used to automatically calculate whether a set of numbers meets certain conditions. For example, you can create a variance alert when comparing actual costs to budget — if the variance is greater than 10 percent, you want the formula to automatically alert us. For a practical example of how to use the IF function as part of a financial model, follow these steps:

Download File 0701.xlsx from www.dummies.com/go/financialmodelinginexcelfd and select the tab labeled 7-23-blank or enter and format the data in columns A through D, as shown in Figure 7-23.

In cell E3, enter the formula =D3-C3 to calculate the variance.

When preparing an actual versus budget report, you should always show the variance as a positive if it’s “better” than budget or a negative if it’s “worse” than budget. This report shows expenses, so an actual amount higher than budget is a bad thing and should be shown as a negative value. For an expense report, the variance calculation is budget minus actual; for a revenue report, the variance calculation is actual minus budget.

If you’re showing an income or profit and loss statement with revenue at the top part of the report and expenses at the bottom, the formula can’t be exactly the same all the way down the page. Although the consistency of formulas is an important part of financial modeling best practice, it’s not always practical!

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.