My Layout for FP&A
Operating Models with a
bunch of Examples →
BY CHRIS REILLY
The GENERAL STRUCTURE
BY CHRIS REILLY
Control
Panel
Summaries
3 Statement
Model
Consolidate
d P&Ls
Department
P&Ls
Sub-
schedules
Raw Data
The Detailed Structure
BY CHRIS REILLY
Control Panel Summaries 3 Statement Model Consolidated P&Ls Department P&Ls Sub-schedules Raw Data
Errors YTD performance Rolling Inc stmt Variance (detailed) Dept 1 Headcount Acct system
Date changes Variance analysis Rolling Bal sheet Rolling Fcst Dept 2 Known contracts
Rolling Cash flow Actuals Dept 3 Capx & Depr
NWC Budget Dept 4 Other…
Debt
Covenants
Other…
Raw Data
BY CHRIS REILLY
• This is where I’ll bring in all data from an
accounting system (QuickBooks, Xero, etc.)
• I don’t make any changes to the data, rather,
I build my helper formulas around the data
• More sophisticated approach is to use Power
Query, but that’s often beyond the understanding
of my clients
BY CHRIS REILLY
Helper Column
Example
• If you use QuickBooks, often the
accounts come in different
columns – a huge pain for
lookups
• I use a cool INDEX/MATCH
formula to put everything in one
column (to help with lookups)
• I don’t need to change my data, I
just build my helper formulas
“around” my data
Schedules
BY CHRIS REILLY
• This is where I’ll build P&L or Balance Sheet schedules
that require detail
• Common examples are Headcount, Known Contracts,
and Capital Expenditures (broken down by
Maintenance and Growth)
• These schedules link to various P&Ls / BS as needed
BY CHRIS REILLY
Schedules
(example)
• Example of capex schedule
• Too complicated to model
at “3 statement level” and
needs its own tab
• Totals link to P&L and BS
where appropriate
BY CHRIS REILLY
Schedules
(example 2)
• Example of Headcount schedule
• Too complicated to model at “3
statement level” and needs its
own tab
• Totals link to P&L and BS
where appropriate
Department P&Ls
BY CHRIS REILLY
• Most departments want a budget for their stuff
• Their Revenue, their COGS, their people, etc.
• Previous schedules will be mapped accordingly
• i.e., Headcount, Contractors, etc.
• Excludes any “Corporate Expense” like CEO, CFO, etc.
• Unfortunately I don’t have any examples I can show
here that aren’t confidential
Consolidated P&Ls
BY CHRIS REILLY
• Have to bring it all together
• Includes “Corporate Expense” like CEO, CFO, etc.
• Includes Interest, Taxes, & Depreciation
• Too hard to model at dept level even if actuals recorded there
• Will show 2 views:
• (1) Total by Chart of Accounts (i.e., total software sales)
• (2) Totals by Department (i.e., total Depts 1-4 + Corporate)
• Both should tie out
Consolidated P&Ls (continued…)
BY CHRIS REILLY
• Freeze one P&L for a “Budget”
• One P&L purely grabs “Actuals” from Raw Data
• One P&L is “Rolling” i.e., Actuals + Budget
• If model date > latest actual date, pull Budget, else pull Actuals
• One P&L is “Variance”
• Line by line comparison to budget for mgmt. to explore
• Will chat with department heads, etc.
• This is an example of the tabs from a model ⤵️
3 Statement Model
BY CHRIS REILLY
• Condensed P&L here (or detailed if preferred)
• EBITDA build
• Primary purpose is to forecast cash
• All (or nearly all) Balance Sheet schedules
• Except for Capx & Depr
• Will be a rolling forecast each month as Actuals come in
• Will “freeze” budget as own file before using rolling fcst
• Modeled vertically (giant tab, 600-1,000+ rows)
3 Statement Model (Inc Stmt)
BY CHRIS REILLY
3 Statement Model (Bal Sheet)
BY CHRIS REILLY
3 Statement Model (Stmt Cash Flow)
BY CHRIS REILLY
3 Statement Model (Debt Schedule)
BY CHRIS REILLY
3 Statement Model (Debt Covenants)
BY CHRIS REILLY
summaries
BY CHRIS REILLY
• Print-friendly summaries of all the details
• Usually presented in thousands ($000s)
• Several items bucketed together
• Big picture YTD performance
• Various summaries built to end user
preferences
• Easy to print & forward — purpose is to have
conversation
BY CHRIS REILLY
Summaries
(example 1)
• Summary output hitting
on only the high points
• Print friendly
• Can look at the detailed
model if there are
questions
BY CHRIS REILLY
Summaries
(example 2)
• Line by line dashboard
• Variance analysis
• TTM, Monthly, YTD, Outlook
BY CHRIS REILLY
Summaries
(example 3)
• Visual of major indicators
• Working capital balance
• Working capital stats
• Capital expenditures
• Covenants (FCCR & Leverage)
BY CHRIS REILLY
Summaries
(example 4)
• Written list of assumptions
• Easy to print
• Purchase info
• Operating info
• Balance sheet info
Control panel
BY CHRIS REILLY
• Easy to update Actuals date (change one cell)
• Flags business issues
• Covenants, Debt Capacity, etc.
• Flags all model issues
• Summaries aren’t set until all errors are fixed
BY CHRIS REILLY
Control panel
(example)
• Easy to update Actuals date
(change one cell, though not in
this picture)
• Flags business issues
• Covenants, Debt Capacity, etc.
• Flags all model issues
Last thing…
BY CHRIS REILLY
• If you’ve been following me for a little while, you’ve
probably seen some of these images before
• It’s designed that way on purpose – I try to make my
models similar so I know what the heck is going on
• “Re-inventing the wheel” each time leads to errors
and frustration
• So I use these “modules” as often as I can and just
tailor them to the client as needed
The GENERAL STRUCTURE
BY CHRIS REILLY
No matter what, I’m always thinking about
this structure and it keeps me grounded.
Control Panel
Summaries
3 Statement
Model
Consolidated
P&Ls
Department
P&Ls
Sub-
schedules
Raw Data
I hope you found this helpful!
Thank You
BY CHRIS REILLY