Set up dynamic cash flows for the future based on compounded annual growth rates.
- [Instructor] The corporate finance function is responsible not only for short-term one quarter ahead forecast but also for longer term forecasting for that visionary picture that helps executives to figure out how to grow the firm over years to come. Now, let's take our one-period Excel forecast and extend this to the full year, shall we? This would be the basis for the type of multi-year forecasting that executives would wanna see when they're thinking about strategic drivers for the business.
I'm in the 04_03_Begin Excel file. As you can see, we've built out our revenue forecast for quarter one and quarter two and those revenue forecasts are built around product one and product two sales. We have our CAGR, our Compounded Annual Growth Rate, which is 8% for the year and we assume that that'll grow evenly each quarter. What we've added though is a seasonable component so our base effect in quarter one is no seasonal effects.
After that, all else equal, in quarter two, sales fall 3%. In quarter three, sales rise 2%. And in quarter four going into the end of the year, sales rise 6%. Again, versus quarter one. This type of seasonal effect is very important to quantify from a forecasting perspective in a corporate finance function. If you don't take into account seasonal effects, demand planning and sales forecast will be off and potentially you'll be short raw materials that you need.
You could be short inventory that customers are looking for and you might even be short labor that's required to meet production quotas. We take this into account by adjusting 1/4 of our CAGR amount of 8% plus negative 3% which is our Q2 seasonal effect. Now, notice that even though our sales grew, our net income remained the same. That's interesting. Gee, look our taxes are the same and our Earnings Before Taxes, EBT, are the same and huh, our EBT is slightly different, but once we get below EBT, all of these figures remain the same.
Why is that? How can that be? Well, the answer is it's because we've built our forecast around all of these different expenses being as a percentage of sales. So when sales grow, the expenses grow. Now, that's a conservative thing to do and certainly, we do expect that expenses will grow as sales grow, right? If the firm doubles in size, expenses will go up. We'll need more people. We might need more raw material, things like that. But will all of our expenses grow proportionally with sales? For example, we're probably still just gonna have one president or CEO of the company, right? We probably won't move into a 6% larger office just because sales grew 6% last quarter.
So the reality is that one way we can achieve efficiencies as a firm is by having a lower percentage cost associated with growing sales. Maybe in quarter two, our SG&A as a percent of sales falls to say 10% or it falls to 11%. Now, for our purposes, we've simply left it alone, but when you're making forecasts for your firm, you wanna think about whether these kinds of changes are appropriate.
That's a major factor to consider. There are other factors like tax rate that won't generally change as we change net income, but certainly SG&A and maybe COGS will be factors that will change. We're gonna wanna look at compressing our cost as much as possible so that we get economies of scale over time. Now we're prepared to extend this forecast through the end of the year so I'm simply gonna drag and drop this over, so I'm widening out our columns, and now we need to make adjustments.
Here, we've still got our same CAGR levels, but we now need Q2 to Q3 changes in seasonal effects and we need that for both of our product categories. Similarly, in Q4, we need our Q4 sales impact for the seasonal effects for both product one and product two. So that'll correct our sales. So what we see is that sales, which I've italicized 'cause they're a component in overall revenue, grow from 780,000 in the first quarter for say product two to 867,000 in the fourth quarter for product two.
Now, our cost go up correspondingly, but overall full year revenues grow to a total of just under $8.2 million and our full year net income is $874,000. Overall, we now have a full year projection of where we think sales, costs, and various metrics for profit are gonna be by the end of the year. We still don't know what our cash position will look like though and this is a concern that we'd wanna take into account as corporate finance professionals.
But as you can see, we now have a reasonable sales budget that should help us with corporate planning and investment from now until the end of the year. And this type of forecast lets us go through and potentially renegotiate with our suppliers and vendors so that we could hopefully increase our profitability metrics.
- Capital budgeting in Excel
- Using Excel in project selection
- Analyzing capital structure using Excel
- Estimating weighted average cost of capital (WACC)
- Applying Excel to operational budgeting
- Cash flow budgeting
- Stress test tools for budgets