The Components of an IFM are the tab, named-range, formula, and formatting conventions behind the Forecast tab, the main screen of an 📝Integrated Financial Model (IFM).
The Forecast tab is where the monthly information from the various 📝Business Applications that serve as data sources comes together, with historical information displayed in line with the forecast. Three sets of conventions keep every model readable and maintainable.
- The model uses 📝Common Named Ranges:
- Column A = RowName
- Column B = LookupVal
- Column C = Limiter
- Column D = ChangeFactor
- Names of tabs start with T_, as in T_ISSrc
- ISSrc tab = the rolling monthly or weekly income statement, updated automatically from QuickBooks Online using the 📝G-Accon QBO to Google Sheets Connector
- BSSrc tab = the rolling monthly or weekly balance sheet
- DetailSrc = the P&L Detail report, including month-ending and week-ending metadata
- The model uses common formulas:
VLookup(LookupVal, T_ISSrc, I_ISSrc, False)means: find the account code from the LookupVal column on the Income Statement tab in the I_ISSrc month index.Sum(Offset($A34, 0, QuarterColumn, 3))means: sum up the 3 cells that are QuarterColumn rows away from column A in this row.
- The model uses consistent formatting:
- Gray highlights denote actuals
- Rows and columns are collapsed consistently