Bill Hanna, CPAAccounting Leader Driving Financial Excellence

Financial Model under 3 minutes!

21 Oct 2025 · 3 min

Watch on YouTube

2:57

Short tutorial by Bill Hanna, CPA demonstrating how to build a top-down, dynamic financial forecast in Excel. He shows listing discrete growth assumptions (10%, 2%, 20%, 30%, 40%) for revenue, COGS and OPEX, creating drop-down menus using Data Validation (List), and writing formulas that reference the selected assumption and previous-period values (e.g., Revenuet = Revenue{t-1} (1 + growth)). He then computes Gross Profit (Revenue − COGS) and Net Income (Gross Profit − OPEX) and highlights that changing the drop-downs updates the whole model for scenario testing.

Questions this video answers

10%, 2%, 20%, 30%, 40%.

Data → Data Tools → Data Validation → List.

CurrentPeriod = PreviousPeriod * (1 + selected_growth) (uses absolute/dollar-sign references when copied).

Is the quickest way to creating a dynamic financial model. So I have here my revenue and cost of sales and OPEX net income for the current month of September. And I'm creating a forecast for the future month. For my assumptions I have revenue growth, cogs and OPEX growth. I'm going to go ahead and create drop down menus that can drive dynamically my model. So what I can do here is I can list my assumptions for revenue. So the first one is 10%, 2, 20%, 30 and then 40%. And I can make similar assumptions for my other two categories, Opex and cogs. And then I can create a drop down menu that can enable me to easily select my assumption. So I'll go to the cell where I want my dynamic drop down to be. I'm going to highlight it and just go to Data Data Tools and under data validation I'm going to go ahead and select a list. So now that I have a list, select the source of my list right here. So the source is going to be this set of sales next to my revenue growth and then I'm going to hit. Ok. I'm going to do the same thing for cogs and OPEX growth. Okay, great. Now that I have my drop down menu, I can just select 10% for each of them to begin with. Awesome. The next step, I'm going to say revenue revenue in my forecast is going to be equal to revenue in the previous period multiplied by one plus this increase in revenue. Close parenthesis. Before I leave it, I want to add a dollar sign so that when I copy my formula to the left or to the right, it's not going to shift my column around. Now revenue grew by 10% and I can drag this across. Great. I can do the same thing for cost of sales. If I drag my sale down, it's going to increase my cost of sales by 10%. Notice that if I change my 10% to 20% is going to dynamically increase my cost of sales. Great. I'm going to go ahead and drag this across as well. Gross profit is just equals to revenue minus cost of sales. Go ahead and drag that opex. I'm going to do the same trick. I'm going to say equal to previous month OPEX multiply by one plus the increase that I added here. And I'm going to add my dollar sign before the column so it doesn't shift around when I drag it across. Now my OPEX is ready to drag. Net revenue is just going to be gross profit minus opex. I can just drag this across. Now what you have here is you have a dynamic model. If you change revenue growth to 20%, it's going to dynamically change. If you change cogs and change the whole model, you can also go ahead and play around with OPEX to get to the optimal level. And that's how you do a high level, top down financial model for the company. If you're interested to learn more, subscribe to the Channel.