Phase 1, and a model of my own

I have just finished Phase 1 of my schedule: the Wall Street Prep financial statement modeling course, two days before the deadline I set myself. The other half of that finish line was rebuilding a three-statement model on my own, without the course walking me through it, and that is what the rest of this post is about.

After finishing the course, I started thinking about how much of the financial statement modeling process could actually be automated. A lot of the work involves entering assumptions, building schedules, and creating circular formulas in Excel. Pulling years of filings into a clean historical sheet, laying out the working capital, fixed asset, debt and equity schedules, wiring the statements together so the cash flow statement actually reconciles the balance sheet — none of that is where the thinking happens, and most of it is the same every time. It seemed like something that could be automated, or at least partially automated.

So I started building my own version, inside F12. It is definitely still a work in progress, with a lot left to fix, refine, and improve. Right now it does the mechanical half: it reads a company’s 10-Ks from SEC EDGAR, builds a historical sheet line by line in the company’s own wording and units, and lays out a forecast with the schedules already wired up and color-coded, so the only cells left to fill are the assumptions. My goal is eventually to let users choose their own assumptions, decide which drivers they want to use, and have the model build around those inputs rather than forcing everyone into the same framework.

The course gave me a basic understanding of what a financial statement model (FSM) should look like, but where it really helped was teaching me the mechanics behind the model: what needs to be plugged in, how the different schedules are built, how the financial statements connect, and, most importantly, the reasoning behind why certain assumptions and drivers are used. Knowing that receivables are forecast on days of revenue is the easy part. Knowing why you would use days instead of a percentage of revenue, and when that choice stops making sense, is the part I could not have picked up from reading a finished model.

For my own FSM practice, I chose to build a model for AT&T (T). I started by downloading the pre-made F12 template, which pulled in three reported years plus the year before them for opening balances, and built the forecast primarily around revenue-based drivers: service and equipment revenue growing at rates I set, then cost of revenue, SG&A, depreciation and amortization, and capital expenditures each as a percentage of the revenue they serve; working capital on days and ratios; debt growing at a rate I enter; and a revolver that draws whenever cash would otherwise fall below the minimum I set. Interest runs off average debt and the revolver’s opening balance, which keeps the whole thing free of circular references. Five forecast years, 2026 through 2030, and a balance check row that stays at zero in every one of them.

I know revenue-based drivers may not always be the best approach for a company like AT&T. It is a capital-intensive business where subscriber counts, ARPU and the capex cycle say more about the next five years than a revenue growth rate does, and someone doing this for real would build the revenue lines up from subscribers rather than growing a total. But the main purpose of the exercise was to practice the mechanics of building a full FSM, so the simpler drivers were the point rather than the problem.

Realistically, I am probably never going to be able to create one universal template that is perfectly tailored and equally robust for every company and industry. That is also part of what makes this project interesting. Different companies require different drivers, schedules, and assumptions — a bank’s balance sheet is not a telecom’s, and a software company holding no inventory does not need half the working capital schedule — so the goal is to make the system flexible enough to account for those differences instead of pretending they are not there.

Attached below is the Excel model I built for AT&T. I also built the scenario analysis from scratch without using a template: one cell picks a best, base or weak case, and an XLOOKUP feeds that case’s growth rates into the driver rows, so the whole forecast reprices when the word in that cell changes. The base case comes off the historical averages, and best and weak move it 1.5 percentage points either way.

Phase 2 is the SIE. It starts Monday: 60 hours, a few thousand practice questions, and an exam at the end of it.