Excel 2016 IFS Function Overview

Introduction to the IFS Function in Excel 2016

• Excel 2016 introduces the IFS function, a significant enhancement over traditional nested IF statements.

• The IFS function addresses the complexities and clunkiness associated with nested IF statements, making it easier to construct formulas.

• It simplifies the process of handling multiple conditions without requiring intricate nesting of IF statements or managing parentheses and commas.

Understanding Traditional IF Statements

• Nested IF statements can lead to:

  • Difficulty in formatting due to numerous parentheses.

  • Complexity in tracking comma-separated arguments.

• Example of a simple income statement used to illustrate forecasting:

  • Company revenue data for 2013, 2014, and 2015 is used for profit forecasting into the future.

• Basic components of the income statement include:

  • Revenues from three historical years.

  • Cost of sales for those years, leading to gross profit calculations.

  • Operating expenses such as administrative and marketing costs.

  • Final calculation of operating profit.

Historical Data and Assumptions

• Key Historical Ratios:

  • Revenue growth in 2014: 8%

  • Revenue growth in 2015: 3%

  • Cost of sales as a margin: 34% (defined as cost of sales divided by revenue)

  • Operating expenses as a percentage of revenue: 8% growth with slight adjustments.

Forecasting Future Revenue and Costs

• Future Forecasting Methodology:

  • Revenue growth forecasted at 3% yearly.

  • Cost of sales is projected to be 37% of revenue moving forward.

  • The approach involves utilizing last year's numbers for projections.

Implementation of Operating Expense Forecasts

• Various forecasting drivers for operating expenses are established:

  • 1. Last year's operating expense margin (returning straightforward forecasts).

  • 2. Last three years' average operating expense margin (for a more robust calculation).

  • 3. Last two years’ average operating expense margin (a middle-ground approach).

Traditional IF Statement Construction Example

• Traditional Nested IF Example:

  • If the user selects option 1, use last year's operating expense margin:

  • Formula: IF(H2=1, E14, ...

  • If the user selects two, take the average of the last three years.

  • If neither option is chosen, apply a third logical test for option three.

• Emphasis on the difficulty and potential errors associated with nested IF statements, like missing parentheses.

Transition to Using the IFS Function

• Introduction to how IFS simplifies the process:

  • IFS function allows for multiple conditions to be tested sequentially, with logical tests laid out cleanly with simple value return parameters.

  • Example for the IFS function:

  • IFS(H2=1, E14, H2=2, AVERAGE(...), H2=3, AVERAGE(...))

• Benefits of using IFS over nested IFs include:

  • Enhanced readability, as it eliminates the need for complicated logical nesting.

  • Reduces chances of errors such as incorrect parentheses placement.

Key Functions and Logical Tests with IFS

• Logical Tests Focus:

  • IFS allows for a series of logical tests directly, making evaluation straightforward:

  • Example constructs: H2=1, providing outputs based on simplicity.

  • The final condition can be kept as a general true statement to capture any remaining evaluations.

• Emphasizes that the IFS function does not require an explicit false condition; the last true condition effectively serves this role.

Conclusion and Implications of IFS Function Use

• The launch of the IFS function marks a critical improvement in Excel's capability for managing conditional statements:

  • Greater ease of use and user-friendliness, potentially increasing productivity.

  • Encourages users to adopt cleaner coding practices, steering them away from cumbersome nested structures.

• Highlights the importance of transitioning to new tools within Excel for efficient data analysis and forecasting strategies.