 So today, what are the things that we will be looking at? Looking at 10 easy ways in which you can forecast industry-based model. I'll be starting from what we call drivers in financial modeling. Financial modeling is all about drivers, and that serves as a basis for calculating our historical trend, which we then be used for forecasting. Now, let me even use the literary meaning of what we call driver. Everyone of you will hear the word driver. A driver can be said to be an individual who sits behind the wheel and controls the movement of a vehicle, or you call it a car for one place to the other. Similarly, financial modeling, if you want to grow your line item in your financial statement, there is a need for you to identify driver for such line item. And this will lead us to we identifying drivers for the line item in our income statements. And before you say a driver for a particular line item is this, there's a need for you to ask, what will cause this line item to happen? Hence, drivers in financial modeling can be said to be a cause and effect. Now, for example, trade receivables. If you say, if there is no sales on credit, that implies that you cannot have trade receivables. It is because you buy something on credit. That is, I mean, you sell something on credit. That is why you have trade receivables. And as such, you can set your sales as a driver for your trade receivables, although there are some certain macroeconomy factors that you may need to also factor in as well. However, you can only factor this in when you have sufficient information about the firm. Talk about you breaking down the cost, their expenses, their price, and quantity. Now, with the understanding of this, we then proceed to see 10 different methods that you can use to forecast your line item having identified the driver. And one thing that's usually with mode last time is getting accurate and reliable assumptions for their model. Well, this is necessary, but it is not sufficient because model is a representation of reality and not a reality in itself. We cannot be 100% accurate of what will happen tomorrow. And there is a saying that states no one knows tomorrow. All we're just trying to do is to mirror what will happen in the future. Today, we'll take a look at some different method that can help us to build at least 80% realistic assumptions in your model, where I'll be demonstrating that. But before we go into the demonstration, let's get started to what we call drivers in financial modeling. As I said earlier, the financial modeling is all about drivers. And when you say driver, we mean what can serve as the basis to grow or forecast our line item. So that if you want to forecast this particular line item in your financial statement, you can use it as the basis to forecast that particular line item. And this will lead us to identifying drivers in our financial statement. Remember that before you can say that you are setting this particular line item as a driver for another line item, you need to ask what is the cause? What would call this line item to happen? What are the components of this line item? What can I use to grow this line item or see what can be set as the basis for forecasting or predicting the future trend of this line item? Now, let's try to identify one of these line items one after the other. And I'll be starting from revenue. Revenue is the same thing as sales, right? So when you say revenue, it's also the same thing as sales. But then let me ask us, when you hear of revenue, what do you think can cause revenue to happen? What are the components of revenue? You can have your price, you can have your quantity, capacity as well, you can set it to be the driver of your revenue, demand, growth and competition. But then there are some limitation to some of these basis. Number one, when you look at the price and quantity, you may not have the information as to what your price will be or what the quantity will be, right? So in such a situation that you don't have access to the price and the quantity, right? You just make use of growth. You make use of growth rates. And then capacity as well can also be set as the basis of predicting your revenue. Now, take for example, a manufacturing thing that has a manufacturing plant. Now, the quantity that they can produce, it's limited to that their capacity. It tends to bring out a constraint, right? So they will be limited to what they can actually produce. Also demand as well can be set to be a driver for your revenue. And when you talk of demand, you are talking about the willingness and the ability of people to buy your product, right? That is the people who are ready and they are willing to buy your product at a particular period of time. Then we go to competition as well. Competition can also be set as a driver for our revenue. Now, let's talk about cost of good food, cost of good food. What is that? This course, what is the cost per unit? You can also make use of their variable cost. But one thing that I want us to also note is that revenue is more like the umbrella of every business. Which means that almost all the line item that you have in your income statements can be set as a percentage of your revenue. Where you can break down the cost of the fame, you have information as to their cost, right? What is their cost per unit? You can make use of what? Their face cost and then the variable cost. But in a situation whereby you do not have information about their cost, their cost per unit, all you just need to do is to set it as a driver of your what? Of your revenue, so revenue will be the driver for your cost of sales. Inflation as well can also be factor in. Because, I mean, inflation, we say that that is the price of goods and then services. And that can affect your cost of sales as well. And that is in terms of a time value of money. And let's talk about SG and A. When you say SG and A, that is selling, general and administrative expenses. You can also call it your operational expenses. What do you think can be the driver for our selling, general and administrative expenses? So also we can also make use of our revenue. Remember, I told us that you can set, that revenue is more like the umbrella of the business. And then almost all the item that you have in your income statements can be set as a percentage of your revenue. And where you can list out all the costs, you have all their operational costs and everything, then definitely you can also make use of costs. Then inflation as well to factor in time value of money. You can also consider competition and expansion. You know, when a firm is trying to expand, definitely the operational expenses is also going to what? To increase. So it's expected that the operational expenses is going to increase. So let's take a look at inflation. If you want to, I mean deposition, right? So if you want to forecast your deposition, you need to ask what are those things that we make you to have deprecation? What are those things that will make you to have deprecation, right? So number one is that that firm must have assets. There should be what? PPE, property plans and equipment. Then you can also consider the rates of deprecation, right? So some firms, they tend to say, okay, they are going to be depreciating their assets over the years at the rate of 10%. So I might say, okay, it's 15% annually, as the case may be. You can also consider the lifespan of the asset as well. Now, let's take a look at the net finance costs. When you get net finance costs, that's the difference between your finance income and your finance costs. So when you look at the difference between your finance income and your finance costs, you have the net finance cost. And you will need to ask that, what are they using to finance their business? Do they obtain loan? Do they have overdrafts? What is the interest rate? The cash in the business, then talk of the investment that they have. And what is the interest income on investments? And before we now go to our demo, let me talk about tax as well, right? So tax for tax, you need to consider the profit before tax as well as the rate of tax. Now let's now demonstrate all 10 different ways in which you can focus some of the line item in our financial statements. And the first one that I'll be talking about is the straight line method. It's a straight line method or call it the constant growth method. This method is the simplest and the most common method of incorporating assumptions into your model. And it is done by using a constant rate, right? When you're using growth rate or percent, so you make yourself a constant rate. And even this in terms of value, that means that you're making you a good trend. Now, I know that you observe something. You should see something here. You will notice that there's more or less a consistent growth across the year. In 2017, they had 37.9%. 2018, you can see 39.6%. Although there was a reduction in 2019 to 38.9%. In 2020, you have 39.3%. And in 2021, you have 43.8%. One thing that I wanted to observe there is that there is a bit of consistency. So for you to make use of that constant growth, or you call it the straight line method, you must look at the historical performance of the frame. See if there's a consistent growth rate. Therefore, even I want to focus making use of this method, you will have a constant trend across the years. Let's even check the average performance of the frame in the five years. So let me just highlight this and see the average performance of the frame across the years. Now, when you go to your status bar, when you go to your status bar, when you come down here, you'll see my status bar and you see the average performance of the frame across the five years. They have 39.9%, more like 40%. So if you now want to predict what your cost of sales is going to be across the next five years, you can then see that it is what? It is 39.9%. So let me come here and type 39.9%. And I can just copy this to the right for my five years. Now, let's make use of these two forecasts. Let's make use of these two forecasts. So if you want to forecast your cost of sales, that would be equal to, that would be equals to, you can either select your revenue, multiply by the cost of sales growth. Then I press enter and I select this. I like to the right and press control R. I see that automatically it's filled out for the five years. So one thing about a fast standard is that you should have a consistent structure in your model, right? So once you have a consistent structure in your model, you can easily highlight to the right and then it will feel automatically. Second one that we'll be looking at is what we call the moving or we call it the rolling average. The moving or the rolling average is another method that you can use to predict the future trend of a business. And it is mostly applied to a matured fame as well. There's a need for you to also check their historical performance. You need to observe that there is a consistent growth, right? And so during, you can forecast based on the average performance of the fame over a specific period of time. And this method is often applied to line item that are subject to seasonal variation look of your inventory, right? So let's try to do this. So I will take a look at two-year moving average as well as three-year moving average. But then let's start from the assumption of two-year moving average. Two years moving average. Two years moving average. So now let's take a look at your historical trend force. Let's look at your historical trend. That will be my cost of sales divided by my revenue. I press enter. Then remember, because I have a consistent layout in my model, I can always copy to the right. You also observed here that there is consistency in the growth margin. There's consistency in the growth margin. Now, even I want to use the moving average. Remember that this is two-year moving average, which implies that you need to look at two years across the forecast period. Two years across the forecast period. And the function that you use in Excel is called average. So you can just say equals to average, equals to average. And remember, you have to select two years. You have to select two years. Then I close my bracket, I press enter. Then I can always align to the right and press control R. Now, let's take a look at what we have in 2023. So you see that I'm having for two years. And then if I come to 2026, I also have for two years. But one thing is that you should not perform calculation in your assumption. So you just need to copy this, copy this, and now paste a special value. Copy it and paste a special value. Now, let's make a use of this assumption to forecast. Let's make a use of this assumption to forecast. But that will be equal to, that will be equals to my revenue multiplied by my cost of use. And I press enter, I can always do this to see what my cross-profit is going to be for the period. Now, with the understanding of the two-year moving average, let's take a look at the three-year moving average. Let's take a look at the three-year moving average. Now, here's the way we've done for the two-year moving average. Let's check the historical trend. Let's try to look at the historical trend. And you also observe that we have a consistent trend right in the historical period. Now, when you want to forecast, remember that this is three years. So instead of selecting two years that we did in the two-year moving average, you're going to make use of what? Three years. Yes, you're going to make use of three years. So that would be equals to average. Equals to average. Now remember, we are selecting three years. I close my brackets, I press enter. Now, let's look at what will be the future trend. Remember, there should not be formula in your imputes. What you use for your future trend. So let's just copy and paste a special value. Having done that, then we can then use that to forecast. But one thing I wanted to observe here, when you look at my formula bar, when you look at my formula bar, you'll see that I have huge decimal, you call it decimal places, right? It's quite huge. I have 40.66 is something, seven percent. Now there's a way in which you can round these. So now, let's try to round it to, let's say, maybe one or two decimal places. And for you to do that, you make yourself a function in Excel that is called round. So here we now be combining two different functions together. We'll be combining round and average. We'll be combining round and average. So let me delete what I have here before. Let me just delete what I have here. And I will say equals to round average. equals to round average. And how many years are we looking at? Three years. So I'll close my brackets for the first argument and then how many decimal places am I looking at? So if it is one, you type one. I mean, you press one. If it is two, you press two. As a case may be, let's just use two. I'll close my bracket and I press enter. Now I can just copy to the right. Remember, I need to copy and paste a special value. I need to copy and paste a special value. Now you now see that the key has even rendered it to 41% for us. Now let's take a look at what will be the cost of sales for 2022. That will be equal to my revenue multiplied by my cost of sales. And I'll say this, then I'll copy to the right and then press control R. Then we can then compare that with what I have for my two-year moving average as well as the three-year moving average. You can see this year in 2022, I have 875.1. We are lost in 2022 for the three-year moving average. I have 863.5. Let's go to the third method. And the third method is what I call the maximum of historical, the maximum of historical. And this is referred to as the optimist method of predicting the full-shot trend of a business trend. And in doing this, for our SGA need, in the situation whereby you do not have information as to the breakdown of the operational expenses, you just set it as a percentage of your revenue. So this will be equal to remember, make sure that you're selecting the appropriate sale that will be SGA need. The method, like my revenue, then I'll press enter. Then let me just release for the five years. So having done this, remember that what I want to do is that I want to check the maximum of the historical trend. I'm trying to look for the maximum in the average performance of the fame between 2017 and 2021. And the function in Excel that I can use for that is what we call max, max. So that will be equal to max. Then let's try to look at the five years. I'll use my brackets and press enter. You see that it's picking the maximum value for me. So I can just copy the first one. This is special value before copying to the right. Having done that, let us use it to forecast. My selling genera and administrative expenses. That will be equal to my revenue multiplied by my SGA need. I press enter, then I can copy this to the right to get for the other years. Now the fourth one that I will be looking at is the minimum of the historical trend. This is more like the opposite of the maximum of historical method of forecasting. And then this method is referred to as of determining the full shot trend of the business. And it is done by checking the minimum rate across the historical trend. So you also look at the historical trend of the business and then you make use of the minimum rates. Make use of the minimum rates. So that will be equal to, let's take it like this. That will be equal to my SGA need divided by my revenue. You can even do this, right? Let me just do it at once. But I like all of these. I will say equal to my SGA need divided by my revenue. And I want everything to fill out at once. I'll just press my control and enter. You see everything will fill out at once. Now remember what you want to do. We want to make use of minimum of historical. Let me ask us what formula in Excel can we use to find minimum? What formula in Excel can we use to find minimum? So I can just, I like the right. I will say equal to mean. You see mean? Then I will select the five year period. I will close the bracket and I will press my control, enter. Then remember I will just copy and paste a special value. Copy and paste a special value. Then let's make use of these two forecasts. Let's make use of these two forecasts. That will be equal to my revenue multiplied by the SGA need. Then I'll press enter. I can just delete this and I like to the right. The fifth method that I'll be sharing with us is all called the last historical year. The last historical year. And the last historical year, we analysts will believe that the last historical year give the latest updates about the fame. And as a result, we often make use of it as a way of forecasting the full shot trend of the business. And this method is used mainly for a balance sheet line item. And then you must also notice that they have a consistent trend over that historical year. So what this method make use of is the last historical year to predict the full shot trend of the business. So now with this now, let me ask us what is my last historical year? Okay, so let's continue. So I have 150, I have 150, right? So I can just, I'll add this to the right and press control R. And then for my share capital that means that for my share, thank you very much Daniel. That means that for my share capital that will be equal to this. So I can add light to the right and then press control R, right? So you must notice that they have a consistent trend. And the sixth one that we'll be looking at is what we call the forecasting predict the full shot trend of the business line item along a linear or you call it a straight line trend. Before say by using the assisting value. And this method is good for fame that has series of data. You say for example, they record a revenue on a daily business or on a monthly business as the case may be. So for this now, they record it on a monthly business. You can see for January and then you can also see for February. Now, even I want to predict for, okay, what is going to be their sales in March, right? So let's say they want to predict what is going to be their sales in March. Let's say 12th of March, 2022. And what is the model? She will just say two. Yes, sir. So let's say that is the model for that particular that they sold in that particular date. Now let's forecast what will be the revenue for the 12th of March, 2022. And the method that I'll be using is what we call the forecasted linear. So when you come to your formulas, when you come to your formulas to have a lot of functions and all of that, you come down and then you come to where you have statistical, when you have statistical, you can always scroll down. You will see forecast ETS, forecast ETS, forecast ETS seasonality and all of that. Then you come to forecast linear. You can always hover around this function to know what they are for. But because of our time, we just go straight to the forecasted linear. And that forecasted linear is used to calculate or predict a full show value along a linear trend by using the existing values. So now let's demonstrate this. Let me demonstrate this. Now to be equal to just type forecasted linear, forecasted linear. And after typing forecasted linear, it will ask you, now what is your S? My X is the date. That is the trade dates. That is the trade dates. And what is my Y? That is my revenue. That's my revenue for the period. That's my revenue. And my known X, that is the trade dates from 1st of January to 27th of February. I will close my bracket and press enter. Now it has predicted that on the 12th of March, then my revenue is going to be 3,150,250.7, right? Now we can also incorporate this in our model as well. We can also incorporate it in your model, right? You can also incorporate it in your model. Let's say for example, you're looking at your revenue year on year growth. So let's just say equal to my forecasted linear, forecasted linear, forecasted linear. And then what is my S? What is my S? Now my S that I'm looking for is what is the year, which is 2022. And then what is my known Y? My known Y is this. And my known S is 2017 to 2021. I'll close the bracket and I'll press enter. Now it has forecasted that my year on year growth rate for 2022 is going to be 11.9%. I can also drag this to the right to see for the other year. So now let's make use of this to forecast what our revenue is going to be. So that will be equals to, for your revenue, you make use of the growth formula, which is your last year ending balance multiplied by open the bracket one plus the growth rate. And close your bracket and press enter. I can always drag this to the right and then press my control. Next one that we'll be looking at is what we call trained. Is what I call trained. And for the trained as well, it's also another function in Excel, right? This is another function in Excel that you use to forecast. And let's just use the same thing that we use the other time. Let's say I'm looking for the third of March, 2022. And then let's say that the model is 450-4,500 P and then what will now be my revenue? So let's start equal to trained. Equals to trained. And that train will return numbers in the linear train, machine known data points using the least square method. So I'll press equal to. Now, what is my known wise? My known wise are these. These are my known wise. That was my known wise. And then what is my known X? My known X is this. Then I'll press my command. Then what is my new X, which is this? Now press my command and I can press zero, close my bracket and press enter. Now you see that it's also focusing that I'm going to have 3.1. Let's compare to the previous one that told me that I was going to have 3.1500. And this as well is saying that I'm also going to have 3.13. And you can also make use of it in our model as well. So we can also come here and do the same thing. That should be equal to, remember what function are we using? Train. You are using train. That is train. What is my known Y? My known Y are these. Then what is my known X? My known X is this. And then my new X, which is the year that I'm looking for. I press zero, press enter. And I can just drag this to the right and press control R. Having done that, let's make use of it to forecast our revenue. That should be equal to the last year heading period multiplied by open brackets, one plus the root rate, close the bracket, press enter. And I can drag this to the right and press my control R. So I press my control R. The eighth method that we're building that is all called the linear regression. The linear regression. And this involved the use of graph. So it's involved the use of graph. So because of our time, let me just deal with the income statement part because of our time. So now let me create a blank sign and cut my share design and select data. What is my data range? So my data range is this. This is my data range. And then for my horizontal, then I can just select this. Can select this. And then I'll press okay. I'll also select okay. Now with this, remember, we are making use of the linear regression. For some of us that maybe you studied economics or we've done something like statistics, you know what they call the linear equation. So for me to get the linear equation, I'll just select this plus sign to see my chart element. And I will select my trend line. Having selected my trend line over to where you have this pointer, click on it and click on more options. Click on more options. When you click on more option, you see something like format trained. You see something like format trained. And then you can come down to where you have display equation on chart. Display equation on chart. Now I can see my equation. Can see my question. So let me just try to also enlighten this. I don't want to be able to see that. Okay. I'm going to make it this. Now, bring it to where we can see it clearly. All right. You can also look at, let's see the R square as well. You can see the R square. Come here. You can also display the R square there. I have the R square there and I love that. But because of time, I just proceed and do our forecast. Now what we're going to do here is this, you know, for my Y, the equation now is Y is equal to minus 0.0051 plus 0.1497. So all I need to do is to come here. I'll type my equal to, what is the first thing minus 0.0051. Multiply by, let me say my, multiply by what is my X? The X will be five, right? That is for the five here. That is for the five here. Then you can also put this into bracket. You can just put that into bracket and then come here and say plus, plus 0.1497. And you press enter. And it's going to give you what you can use as your forecast, right? So I can also drag to the right. You just copy and then paste a special value. And then we can use that to forecast. Having done that, we can always delete this. Let me just move this here and then use it to forecast. That will be equal to my last historical year. Multiply by open bracket one plus the growth rate. Close my bracket and then press enter. And I can always drag this to the right. Now the next one that we'll be looking at today before we go is the forecast sheet. The forecast sheet. And for the forecast sheet, you there's something in Excel when you come to your data, when you come to your data and you move down in your ribbon, you see what we call the forecast sheet. And this one is going to create a new worksheet to predict your official train. It's going to create a new worksheet to predict your official train. So you're going to notice something here. But now let me just select my, let me just select my data. Let me select my data first. Let me select my data. And then I go back to data, go back to data and then I click on forecast sheet. Then you see that it has forecasted it for me, right? And then it will ask me that what is the forecast end? What is my forecast end? My forecast end is 2026. My forecast end is 2026. And then you can also click on option. You can click on option where you want the forecast start period. You can always select the forecast start period if you want to do it automatically. You can always do that. But then let's just proceed. You can always explore this after the webinar. So let's click on create. And you'll notice that it has created it for me, right? It has created it for me. So I can always highlight this from 2022. Let me just copy, copy it, come back to the worksheet that I am. I want to type it here. Now, you know, I've selected it on a column and I want to change it to horizontal. So what I can do is just to make sure of transpose. So I would just say ought ESV, okay? So let me copy that again, come back here. Then I want to paste here. And then I want to paste this values and you now create select transpose and I press enter, I'll press enter. And then we can always use that to forecast. So that will be equal to, that will be equals to my last year, historical period multiplied by open brackets, one plus this particular year, close brackets, I'll press enter. Then we can highlight the right and press control R. And before we go to our question and answer period, let's look at the last one. And the last one that I'll be talking about today is the compound annual growth rates method. I call it CAGR method. And this method indicates the growth rates over multiple time period, right? Over the multiple time period. And the one thing about these compound annual growth rates is that it is not prone to volatility on like the moving or the rolling average. You can make use of it to calculate your capacity. You can make use of it to calculate your revenue, right? And well, that's just about it. Let's just calculate, do that. Let's just make calculate the compound annual growth rates. It has a formula, but then it's not something really serious. It's something that you can always understand. So what you just need to do is to say equal to what is the last value? Let me select the last value. Or should I just put it into brackets? Let me put this into brackets. So let's say the last period, let's say the last period divided by the first period, then I can close my brackets, raise to the power of open under brackets, then one divided by one thing that I also need to note here is that my year will not start counting from 2017. So I'll just, for my 2018, then that will be year one. 2019, what will be year two? Period three, and then period four. So I will divide by four, close the brackets, and I will now say minus one, minus one, and I'll press enter. I'll press enter, and I can use this to forecast as well. I'll just say equal to my last year and the period, multiplied by open brackets one plus the growth rate, close bracket, and press enter. So either way, you can just align this to the right, and then you also do same for this. You also do same for this, right? So that's all about it on 10 different ways in which you can forecast industry based model.