Learn how to forecast linear and exponential growth accurately using Excel's fill series tool. Gain practical insights with step-by-step examples tailored for both steady and accelerating growth scenarios.
Key Insights
- Identify linear growth by selecting the first and last values in Excel, then use the "Fill Series" feature from the Home tab; for example, a consistent increment of $225 per period grows from 100 to 1,000.
- Forecast exponential growth by choosing "Growth" and "Trend" options within Excel’s series fill tool; resulting increases start smaller at $78 initially and accelerate up to $438 per period.
- Use Excel series forecasting to accurately plan monthly revenues, such as determining consistent monthly increments of approximately $27,273 to reach a targeted revenue of $500,000 by year-end.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
Series forecast. In this section, we're going to take a look at two different types of growth, linear and exponential growth. Linear growth is where your values increase by the same amount during each period, whereas exponential growth grows at an exponential rate.
So your values may double or even triple until you get to your goal. We have two examples here that we're going to show you as an example of both linear and exponential growth. Let's start with the first exercise.
What linear growth values would be needed for years two to four if year one is 100 and year five is 1,000? We want to predict how much the value would need to increase each year to get to 1,000. Now mind you, for all these exercises, we already have the answer here.
And so you can see that we would need to have a consistent growth of $225 for each period. And so when you add that value to 100, you get 325. And then when you add 225 to 325, you get 550 and so on until you get to 1,000.
We're going to use a tool in Excel to calculate this. Now, to do this, you're going to select the first value and the last value. Then from the Home tab, you're going to go over to the Editing group.
You're going to click the dropdown for Fill, and then you're going to choose Series. So you're making a series forecast where the growth is linear. Now based on the cells you selected, you can already see the answer here.
So all I have to do now is click OK. And I see the same values that I see on the right. Now down at the bottom, you'll see the difference between the current period and the last period.
And that's a consistent growth of $225. Let's say for the trend series forecast, I'm working at a startup and I expect that my values are not going to be consistent, but they're going to grow exponentially. There's going to be viral growth.
So what nonlinear growth values would be needed for years two to four if year starts at 100 and year five is 1,000? So again, I'm going to select the same exact range. I'm going to go to the Home tab. I'll head over to Fill.
I'll choose Series. Now the options I'm going to choose here are a little different. Now I want the values to grow, so I'm going to choose Growth.
And I want those values to trend. So those are the two additional values that I'm going to select. And then I'm going to click OK.
Now I still get to 1,000, but for period number two, I have a growth of 78. And then for period three, I have a growth of 138. And then from there, I go to 246 and then 438.
So I start small and then each period increases over the prior period, and I ultimately get to my goal. There are two additional exercises here that you can do on your own. If you'd like to, you can pause the video here.
They're very similar. So what linear revenue values would be needed for February to November to end December with $500,000? So I'm going to start with $200,000 and I'm going to go over to $500,000.
I'll go to the Home tab. I'll go over to the Editing group, click the dropdown and choose Series. This series is going to be linear.
And so I'm not going to choose growth here. I'll click OK. And the amount that is considered consistent for all the periods is $27,273.
That's how much I would consistently need to make each month to get to $500,000. Now, if I were interested in starting small and then having each period be larger than the next, I would choose from the Fill dropdown, Series, and I would choose Growth. This time, my values would trend.
And now I'll click OK. And now you can see I start with $17,000. Then I go to $18,000, $20,000, $22,000, $24,000.
Each period, the value increases by a greater amount. Ultimately, my last value is $39,962 and I ultimately make it to $500,000. If you're looking to predict for either a linear or trending series forecast, you can use the Fill Series tool in Excel to predict what those values would be for all the periods in between.
So this has been a look at series forecasts. Thank you for watching.