What are the financial models to master if you work with them? | Chris Reilly posted on the topic | LinkedIn (2024)

How to fit an entire Financial Model on one page (without complicated macros or VBA)𝗣𝘂𝗿𝗽𝗼𝘀𝗲 𝗼𝗳 𝗢𝗻𝗲-𝗣𝗮𝗴𝗲𝗿No macros or VBA required... just links: we're presenting all the high points of the model in an easy-to-follow format that can also be forwarded in an email (and printed).Beneath this one-pager is a detailed, monthly, operating model for the company. But, you don't print where you model nor model where you print.𝗜𝗻𝗰𝗼𝗺𝗲 𝗦𝘁𝗮𝘁𝗲𝗺𝗲𝗻𝘁Covers the "big stuff" only like Revenue, Gross Profit, Net Income, and EBITDA.𝗕𝗮𝗹𝗮𝗻𝗰𝗲 𝗦𝗵𝗲𝗲𝘁Also covers the "big stuff" like Cash and Debt Balances (these are usually the two "must know" items).𝗦𝘁𝗮𝘁𝗲𝗺𝗲𝗻𝘁 𝗼𝗳 𝗖𝗮𝘀𝗵 𝗙𝗹𝗼𝘄𝘀A summary of the 3 Sections (Operating, Investing, Financing) and ending cash (make sure this matches your Balance Sheet!).𝗖𝗼𝘃𝗲𝗻𝗮𝗻𝘁𝘀Compliance check for the primary covenants, in this case Fixed Charge Coverage Ratio (FCCR) and Leverage Ratio.𝗥𝗲𝘁𝘂𝗿𝗻𝘀Investor returns on the company (if applicable) showing the Equity Value, ROI, and IRR.𝗩𝗶𝘀𝘂𝗮𝗹 𝗔𝗶𝗱𝘀Produced as-needed for the audience. In this example I show Capital Spend (broken out by Maintenance and Growth) as well as Net Working Capital.You would adjust this section for the KPIs your company wants to see.𝙉𝙚𝙭𝙩 𝙎𝙩𝙚𝙥𝙨 ⏬👋Hey, I'm Chris Reilly. If you've ever felt overwhelmed or frustrated by Financial Modeling (but know how important it is), then please follow my page. I can help you become a Financial Modeling expert without the high cost of traditional education.Lastly, if you want to learn more about building FP&A models like this, I cover it in my weekly email series (link in comments 👇)

317

21 Comments

My 𝗨𝗹𝘁𝗶𝗺𝗮𝘁𝗲 𝗚𝘂𝗶𝗱𝗲 to Financial Modeling. (normally for paid students, but today completely free)Table of Contents:• Simplify Your Formatting• How I Layout Most Models• Build 3 Statements with These 4 Things• How I Build FP&A Models• How I Build LBO Models• How I Build Rollup (add-on) Models• How To Find Outliers in Your Data• My 7 Most Used Excel Shortcuts• Modeling Tips I Wish I Knew When I First StartedI hope you can apply some of it to the model you're working on!𝘼𝙗𝙤𝙪𝙩 𝙈𝙚👋Hey, I'm Chris Reilly. If you've ever felt overwhelmed or frustrated by Financial Modeling (but know how important it is), then please follow my page. I can help you become a Financial Modeling expert without the high cost of traditional education.———Lastly, if you want a copy of the guide, I've put a link in the comments below 👇

216

22 Comments

Do you know how to build the Statement of Cash Flows? This simple cheat sheet gives you the steps:○ 𝙍𝙚𝙦𝙪𝙚𝙨𝙩 𝙇𝙞𝙨𝙩First things first, you'll need some data.You need the Income Statement and the Balance Sheet.○ 𝙎𝙩𝙚𝙥𝙨 — 𝙄𝙣𝙘𝙤𝙢𝙚 𝙎𝙩𝙖𝙩𝙚𝙢𝙚𝙣𝙩1. Pull the Net Income from the Income Statement2. Pull the Depreciation from the Income Statement(or use the change in Accumulated Depreciation from the Balance Sheet if available)○ 𝙎𝙩𝙚𝙥𝙨 — 𝘽𝙖𝙡𝙖𝙣𝙘𝙚 𝙎𝙝𝙚𝙚𝙩You're calculating the changes between two periods.For Assets: Previous Period - Current PeriodFor Liabilities & Equity: Current Period - Previous Period(note, if Fixed Assets are shown on a "net basis," you'll need to subtract Depreciation from your calculation as well)○ 𝙀𝙣𝙙 𝙍𝙚𝙨𝙪𝙡𝙩If done correctly, the ending cash should match what you see on the Balance Sheet.Once it does, you can be confident your model is structurally sound.And now you can carry those formulas forward to reflect your forecast.💡𝙋𝙧𝙤 𝙏𝙞𝙥Once your Statement of Cash Flows is built correctly, you leave it alone. FOREVER.Your modeling should only be done in the Income Statement and Balance Sheet.The Statement of Cash Flows is nothing more than a formulaic bridge between the other two statements.○ 𝙎𝙠𝙞𝙢𝙢𝙖𝙗𝙡𝙚 𝙑𝙚𝙧𝙨𝙞𝙤𝙣• Get Income Statement & Balance Sheet• Calculate Changes b/t Balance Sheet• Working when Ending Cash matches the Balance Sheet○𝘼𝙗𝙤𝙪𝙩 𝙈𝙚👋Hey, I'm Chris Reilly. If you've ever felt overwhelmed or frustrated by Financial Modeling (but know how important it is), then please follow my page. I can help you become a Financial Modeling expert without the high cost of traditional education.Lastly, if you want to learn more about the Statement of Cash Flows, I cover it in my weekly email series (link in comments 👇)

173

18 Comments

If you've ever felt confused by the term "Cohort Analysis," you're not alone.This post breaks it down in simple terms 👇𝘾𝙤𝙝𝙤𝙧𝙩 101A "cohort" typically refers to a group with some kind of shared characteristic.For example, all people that subscribed to a newsletter in March 2024 would be considered part of the "March 2024 Cohort."𝘽𝙪𝙞𝙡𝙙 𝙖 𝘾𝙤𝙝𝙤𝙧𝙩 𝘼𝙣𝙖𝙡𝙮𝙨𝙞𝙨One way to build a cohort analysis is to "level set" the data back to a common starting point, and that's what I'm doing in the image.In the first table, I'm looking at a list of franchisees that opened in various years starting in 1990.This is called "time series data."In the second table, I build the cohort analysis by "level setting" all franchisees back to their first year.See how the headers have changed from calendar years to relative years (i.e., Year 1, Year 2, etc.)?By doing so, we can compare them as if they all started in the same year.𝙏𝙝𝙚 𝙄𝙣𝙨𝙞𝙜𝙝𝙩 𝙒𝙚 𝙂𝙖𝙞𝙣This allows us to extract a ton of great information such as:- Average by year- Standard deviation by year- Coefficient of Variation by year- Growth trends- Benchmarking (for new locations)- Finding anomalies (they tend to jump out in this view)𝙄𝙣 𝙎𝙪𝙢𝙢𝙖𝙧𝙮A "cohort" is a group with a shared characteristic.We transform our data from "time series" to "cohort" by putting everyone on a common timeline.This gives us a ton of information we can use to make estimates about future groups or "cohorts."𝘼𝙗𝙤𝙪𝙩 𝙈𝙚👋Hey, I'm Chris Reilly. If you've ever felt overwhelmed or frustrated by Financial Modeling (but know how important it is), then please follow my page. I can help you become a Financial Modeling expert without the high cost of traditional education.———👇Lastly, if you want to learn more about cohort analysis, I cover it in my weekly email series (link in comments)

111

23 Comments

How to take the headache out of cash flow modeling:(it's easier than you think)𝙎𝙩𝙖𝙧𝙩 𝙬𝙞𝙩𝙝 𝙔𝙤𝙪𝙧 𝙋𝙚𝙧𝙨𝙤𝙣𝙖𝙡 𝙇𝙞𝙛𝙚Why?It's so much more relatable.Instead of "Receivables" and "Payables," you've got:- Paychecks- Rent- Utilities- Groceries- Etc.(stuff we inherently understand without having to think too hard)𝙐𝙥𝙜𝙧𝙖𝙙𝙚 𝙩𝙤 𝘽𝙪𝙨𝙞𝙣𝙚𝙨𝙨 𝘾𝙡𝙖𝙨𝙨Once you grasp the fundamentals using relatable context, then the "complicated stuff" is much easier to understand.At the "business level," we're completing the same exercise, just using business line items instead of personal ones.(from a technical perspective, this is called the "direct method" of forecasting cash, whereas in a Three Statement Model, you would use the "indirect method")𝙄𝙣 𝙎𝙪𝙢𝙢𝙖𝙧𝙮Start with the basics.Relatable, personal context first.Then the "business stuff" gets easier from there.𝘼𝙗𝙤𝙪𝙩 𝙈𝙚👋Hey, I'm Chris Reilly. If you've ever felt overwhelmed or frustrated by Financial Modeling (but know how important it is), then please follow my page. I can help you become a Financial Modeling expert without the high cost of traditional education.———Lastly, if you want to learn more about cash flow modeling, I cover it in my weekly email series (link in comments)👇

220

15 Comments

Sick and tired of not knowing where to start when building models?Grab this checklist 👇𝘽𝙞𝙜 𝙋𝙞𝙘𝙩𝙪𝙧𝙚Anytime I build a model I like to map out two things:1. The data request list (what I'll need from the client), and;2. The model structureThis checklist is my starting point for both, broken down as follows:———𝘁𝗹;𝗱𝗿: you can download this checklist for free, just check the comments section ↓———𝙍𝙖𝙬 𝘿𝙖𝙩𝙖Typically direct exports from an accounting system.Call me old school, but I still prefer to copy/paste CSVs into the model so that everything is self-contained.*𝘈𝘐 𝘢𝘯𝘥 𝘗𝘰𝘸𝘦𝘳𝘘𝘶𝘦𝘳𝘺 𝘩𝘢𝘷𝘦 𝘦𝘯𝘵𝘦𝘳𝘦𝘥 𝘵𝘩𝘦 𝘤𝘩𝘢𝘵𝙎𝙪𝙥𝙥𝙤𝙧𝙩 𝘾𝙖𝙡𝙘𝙪𝙡𝙖𝙩𝙞𝙤𝙣𝙨Detailed schedules to forecast the "biggest movers" of the business like Revenue, Headcount, and other material OpEx items.𝘾𝙤𝙣𝙨𝙤𝙡𝙞𝙙𝙖𝙩𝙞𝙤𝙣Raw Data and Support Calculations "roll up" here into the Three Statement Model consolidation.𝙊𝙪𝙩𝙥𝙪𝙩𝙨As the name implies, these are print-friendly outputs that are linked from other places in the model, typically the Consolidation.You don't model here or have them link anywhere else.They are outputs, should be automated, and left alone.𝘼𝙨𝙨𝙪𝙢𝙥𝙩𝙞𝙤𝙣𝙨A place to enter most of the assumptions that govern the forecast portion of the model.Some people live and die by the "Assumption Page," but I usually go against best practices here and put my assumptions inside the Support Schedules and Consolidations themselves.I do this because I like to see the numbers change as I change the assumption.However, I will do my best to link everything back to an Assumptions page so it's printable and sitting in one place.𝙀𝙧𝙧𝙤𝙧 𝘾𝙝𝙚𝙘𝙠𝙞𝙣𝙜Despite our best efforts, nearly 90% of spreadsheets contain errors (Google it).This is the place in the model where I do my best to capture anything that might go wrong so I'm notified immediately.In my opinion, this is the most important sheet in the entire file, should be reviewed constantly, and is always the last thing you check before you close (and send) the file.𝙄𝙣 𝙎𝙪𝙢𝙢𝙖𝙧𝙮↓ Raw Data↓ Support Calculations↓ Consolidation↓ Outputs↓ Assumptions↓ Error Checking———👋Hey, I'm Chris Reilly. If you've ever felt overwhelmed or frustrated by Financial Modeling (but know how important it is), then please follow my page. I can help you become a Financial Modeling expert without the high cost of traditional education.———👇Reminder, check the comments below to download the checklist.

462

32 Comments

Want to get better at Private Equity modeling? Watch this video.(it's one of the hardest parts of an LBO transaction...)Before you start, know this:(1) it's a 5 minute video (on social media)(2) it doesn't have closed captions (sorry)𝗕𝗨𝗧 𝗛𝗘𝗔𝗥 𝗠𝗘 𝗢𝗨𝗧!This is one of the most complicated parts of building an LBO model.So it takes a little longer to explain and I didn't have room for captions.So take a few minutes, turn the sound on, and check out how to model the Pro Forma Balance Sheet.𝙒𝙖𝙣𝙩 𝙩𝙝𝙚 𝙩𝙚𝙢𝙥𝙡𝙖𝙩𝙚?I'll put a link 𝗶𝗻 𝘁𝗵𝗲 𝗰𝗼𝗺𝗺𝗲𝗻𝘁𝘀 𝗯𝗲𝗹𝗼𝘄 where you can download the template for free ⤵️

304

55 Comments

Build a Depreciation Schedule in 90 seconds (with free template you can download 🔽)This is the classic "waterfall build" that you'll see in a lot of Three Statement Models.As your skills progress, you can actually stuff all of this into a single line using the OFFSET function.But, it's important to understand the underlying components of the schedule you're building first.So, I prefer to teach this "waterfall method," and then condense in a more robust FP&A model.(also, this method is easier to audit)———👋Hey, I'm Chris Reilly. If you've ever felt overwhelmed or frustrated by Financial Modeling (but know how important it is), then please follow my page. I can help you become a Financial Modeling expert without the high cost of traditional education.———👇Lastly, check the comments below to download the Depreciation template.

154

10 Comments

An often-overlooked part of middle-market Private Equity (and why it's crucial to the Managing Partner) 👇It's called the "𝗡𝗲𝘅𝘁 𝟭𝟮 𝗠𝗼𝗻𝘁𝗵𝘀" one-pager.It shows the business pre- and post-close, and provides a snapshot of all the financial changes we plan to make as part of the deal.Here's how it works:𝗖𝗹𝗼𝘀𝗶𝗻𝗴 𝗗𝗮𝘁𝗲I like to split the report down the middle so the Partner can see how the business looks pre-closing and post-closing, and make it very clear when the deal is completed.𝗡𝗲𝘄 𝗗𝗲𝗯𝘁A Private Equity transaction often comes with incremental debt, at least during a control buyout.I like to show each new tranche of debt, so the partner can clearly see the impact of our new financing.𝗖𝗼𝘃𝗲𝗻𝗮𝗻𝘁𝘀We need to know if the business can sustain our new financing structure, so all Covenants are shown in one place.Reality may play out differently of course, but we want to have a sense of where things are headed early on, since there's so much learning to be done about the company (due diligence is just the beginning).𝗥𝗲𝘁𝘂𝗿𝗻𝘀Not as meaningful at this stage since it's so early, but still helpful to get a sense of where things are headed, which investors are "in the money," and which ones aren't just yet.𝗦𝘂𝗺𝗺𝗮𝗿𝘆Private Equity is all about "pro forma," meaning, "what will things look like on a go-forward basis?"This one-pager gives the Managing Partner all the big picture information they need at a glance without having to hover over my shoulder and stare at my financial model.It can also easily be printed and forwarded to other members of the team.(Lastly, it should update automatically based on changes in the Closing Date, so it's always ready to go)~~~I'm Chris Reilly and I post about financial modeling for middle-market FP&A and Private Equity professionals.If that's you, please consider following!♻️Lastly, please share this with your network if you found it helpful.Thanks!—Chris

48

11 Comments

Unsure what data to ask for? There are only 2 things you need to build a three statement model:1. Income Statement2. Balance SheetFrom there, you can build the Statement of Cash Flows (indirect method) by pulling the Net Income from the Income Statement, and then calculating the changes in Assets, Liabilities, and Equity on the Balance Sheet.~~~Your initial forward-looking assumptions come from analyzing the past.How much did Revenue grow? Placeholder assumption can be year-over-year growth.Last year's Gross Margin? Carry that forward for now.How much did OpEx grow? Placeholder assumption can be year-over-year growth.Working Capital? Look at last year's stats (DSO, DPO, and DOH) and carry those forward.What about Capex? Look at the Cash from Investing section and carry forward an average.~~~So long as the "plumbing" is correct, you continue to add more detail and overwrite your placeholder assumptions.~~~𝕃𝕖𝕥'𝕤 𝕔𝕙𝕒𝕥: what part of three statement modeling trips you up? Please add a comment below.~~~If you found this post helpful, please consider re-sharing it with your network ♻️.Thanks!—Chris

34

10 Comments