Building Your First Financial Projection and Model - The Startup
Building Your First Financial Projection and Model The Startup

After inputting the data into the excel, we can now do some quick analysis. We want to calculate the growth rates over each year, and the basic way to forecast is to use an average of the previous historical growth rates.

Calculations for Growth Rate and % of Net Sales to find Average

Notice how we only calculate the % growth for Net Sales, and every other line is based on a percentage of Net Sales. For example, in 2019, R&D makes up 6.2% of Net Sales. Compared to 2015, when R&D only made up 3.5% of Net Sales. The reasoning behind this is that as Net Sales increase, so will the cost of sales, and operating expenses that support this increase in Net Sales.

The new Historical Average column is now the simple average of the 5 columns before it. Using the growth rate, and applying it on 2019 Net Sales, we will now have a new 2020 estimated Net Sales amount. For 2021 and onwards, the same historical average of 3.1% growth is applied, giving us the next 5 years of Net Sales.

From there, using the % of Net Sales amounts that we calculated for Cost of Sales, 61.2%, we calculate the amount of Cost of Sales for each corresponding year.

The next step is to now do it for every line item using the same approach. Notice how we are only using the historical average amounts to derive the line item amounts.

Now you have forecasted the next 5 years of Apple’s business using some simple assumptions based on their historical finances. Although not perfect, and can definitely be improved upon, this is the basics behind forecasting the Income Statement. This is not at the level of a 3-Statement Model but is an important first step.

For companies without 5-years of historical data or positive profits yet, it will require more assumptions. Instead of 5 years, maybe use 3 years. Imagine your company finally meeting profitability, what would that look like?

Once again, this process is art, and not science.

You don’t need an accounting or finance degree to understand what is going on here, and it is very intuitive.

You may be thinking, how would we know if they can continue growing their sales at their historical value? What if the industry tanks, or what if the competition starts to pick up? This is where your additional research and knowledge of the company come into play. If the industry is forecasted to take a hit, you may want to adjust certain percentage amounts in the model.

Currently, every year is the same, but you may want to increase the Cost of Sales by 1% a year, starting with 62.2% in 2021 due to expectations of higher input costs. If Apple talked about increasing their R&D expenditures, you may also want to use the numbers that they mentioned itself. Management for large companies tends to provide guidance on certain percentages and even amounts for the near future to aid analysts in projecting.

Remain skeptical and always use your own knowledge when possible.

The best way to sense check these numbers is to see what the street is saying. Look at what other analysts are forecasting, and also ask yourself if things make sense. Create different scenarios for your own model, which have a bear, bull and base case. In the bull, flex the amounts to be very bullish, potentially increase the annual growth rate to 4% or higher. In the bear case, assuming a bad economy, decrease the growth rate.

The beauty behind a financial model is that it helps you visualize certain impacts. You can see exactly how your bottom line will take a hit with a certain event. This is just the basics, but you now know how financial analysts build a model. Compare your 2024 forecasts with 2019 actual amounts. When the 2020 amounts come out, see how close you were. Create your own model for your own company and see what happens! The possibilities are endless.

Although there are many steps and improvements you can make, this allows you to research and think about the process more. Can you think of ways you can automate this process with Python or Pandas?

Drop any questions you may have about the process below, and I’ll help you out.

Source link