Lesson Objective: To apply statistical forecasting methods directly within Excel and learn how to clean, merge, and transform massive external datasets (from ERP systems and stock exchanges) without manual copying.

In-Depth Notes:

1. Linear and Exponential Forecasting (The FORECAST.ETS Suite):
While financial modeling often relies on driver-based assumptions, global standards require a statistical baseline to validate those assumptions.

• FORECAST.ETS (Exponential Triple Smoothing): This function predicts future values based on a historical timeline that exhibits seasonality. For example, if a European retailer has known sales spikes in Q4 and dips in Q1, FORECAST.ETS will detect that seasonal pattern and project it into the future. It requires three arguments: the target date, the historical values, and the historical timeline.

• The TREND Function: Used to fit a straight line (linear regression) to historical data and project it forward. This is commonly used to project a “normalized” growth rate for mature industries (e.g., utilities) where growth is stable and predictable.

• GROWTH vs. LINEST: GROWTH is used for exponential curve fitting (best for high-growth tech companies where revenue doubles), while LINEST returns detailed regression statistics (R-squared, standard error) that are required for risk analysis disclosures under European MiFID II regulations.

2. Data Cleaning with Power Query (Get & Transform):
A professional model rarely uses data that is perfectly formatted. Raw data from SAP, Oracle, or Bloomberg contains blank rows, merged headers, and inconsistent formats. Power Query is the globally accepted tool for this transformation because it creates a repeatable “query” that can be refreshed with one click.

• The M-Code Language (Basic): Power Query records every step (e.g., “Promote Headers,” “Remove Blank Rows,” “Change Data Type to Number”) in its M-language. This provides an auditable trail of data manipulation, which is a requirement under the Sarbanes-Oxley Act for data provenance.

• Merging Queries (The VLOOKUP Replacement): Instead of using VLOOKUP to combine a revenue table with a headcount table, Power Query uses a “Merge” join. This is superior because it can handle left, right, inner, and full outer joins across millions of rows without freezing Excel.

• Unpivoting Columns: Financial data often comes in a “cross-tab” format (e.g., Revenue 2020, Revenue 2021, Revenue 2022 across the top). Power Query’s “Unpivot” function transforms this into a normalized database format (Column: Year, Column: Revenue), which is necessary for creating dynamic pivot tables and time-series charts.

3. The SWITCH and IFS Functions for Multi-Tier Logic:
Nested IF statements (e.g., =IF(A1>10,"High",IF(A1>5,"Mid","Low"))) are difficult to audit. Globally, modelers are migrating to IFS and SWITCH.

• IFS: Allows you to test multiple conditions sequentially without nesting. For credit rating modeling, you can use =IFS(Ratio>4,"Junk",Ratio>2,"BBB",Ratio>1,"A").

• SWITCH: Best for exact matches. If you have a scenario toggle (1,2,3), =SWITCH(Scenario,1,Assumption_Base,2,Assumption_Upside,3,Assumption_Downside) is significantly clearer and faster to audit than a nested IF.

4. Handling “No Data” and Zero-Value Assumptions:
In European modeling, it is common to have data gaps for new product lines. The ISBLANK and ISNUMBER functions are used to test whether a cell is empty. The best-practice formula for calculating growth is =IFERROR((Current/Prior)-1,"NM"), where “NM” stands for “Not Meaningful,” a disclosure term accepted by both the SEC (US) and ESMA (Europe) to avoid presenting misleading zero-growth figures.