Trend Percentage for Net Sales Analysis, Formula & Examples

trend analysis formula

In the case of using the TREND function, if your Excel is an older version than MS Office 365 then you must use the following key instead of using ENTER to get the result. The syntax of the TREND function only provides for one known_y’s and one known_x’s range. This smart package will ease many routine operations and solve complex tedious tasks in your spreadsheets. This comprehensive set of time-saving tools covers over 300 use cases to help you accomplish any task impeccably without errors or delays. At first sight, the syntax of the TREND function might seem excessively complicated, but the following examples will make things a lot easier.

For the most recent year, the trend percentage is 90 percent. Trend analysis aims to identify and analyze potentially significant patterns in historical data. Generally, this information is used to better predict future trends.

Chapter 12: Financial Statement Analysis

We’re firm believers in the Golden Rule, which is why editorial opinions are ours alone and have not been previously reviewed, approved, or endorsed by included advertisers. Editorial content from The Ascent is separate from The Motley Fool editorial content and is created by a different analyst team. If you purchased several fixed assets during 2018, the increase is easily explained, but if you didn’t, this would need to be researched. That means that from 2017 to 2018, your revenue increased by 24%.

What is the formula for TREND?

Formula. The TREND function uses the following arguments: Known_y's (required argument) – This is the set of y-values we already know in the relationship y = mx + b. Known_x's (optional argument) – This is a set of x-values.

All of the amounts on the balance sheets and the income statements for analysis will be expressed as a percentage of the base year amounts. The amounts from three years earlier are presented as 100% or simply 100. The amounts from the most recent years will be divided by the base year amounts. For instance, if a most recent year amount was three times as large as the base year, the most recent year will be presented as 300. This type of analysis reveals trends in line items such as cost of goods sold. When you review your small business’s balance sheet, it’s important to compare accounts across multiple years to identify any trends. To make this process easier, you can convert each account’s dollar amount into a trend percentage.


Comparing the growth of sales and net profit together, it is clear that net profit growth is greater than sales growth. Specifically, over the five years, while there was an increase of 100% in terms of sales, the net profit increased by 181%. As a case in point, if the index is found to increase 1.20 times over the same period, we can assume that Safeway experienced real growth in sales between 2015 and 2019. One way to find out is to compare the index number for sales growth to the rate of inflation for the same period. Inflation can be measured using an index such as the consumer price index for all urban consumers, or a specific price index for the industry.

  • This is due to the fact that trends can change over time, the data itself can be flawed, and outliers can distort results.
  • Disadvantages of using a trend analysis of financial statements include the potential for inaccurate data points and the reliance on historical data.
  • It is customary to see the rate of growth in sales for a mature region begin to decline and then settle into a relatively tight range over time.
  • Additionally, the TREND function can extend the trendline into the future to project dependent y-values for a set of new x-values.
  • A trendline is commonly used to observe the general trend in your current data as well as project future data movements.
  • It basically indicates the change either in terms of amount or as a percentage change year over year (Y-o-Y).

Semantic Scholar is a free, AI-powered research tool for scientific literature, based at the Allen Institute for AI. Learn how InetSoft’s native big data application is specifically designed for a big data operating system. This information is usually only plotted for the largest customers, to focus the attention of the sales staff.

Horizontal Analysis: What It Is vs. Vertical Analysis

These trend percentages indicate the changes taking place in the organization and highlight the direction of these changes. For instance, the percentage of sales is increasing each year compared to the base year. Cost of goods sold increased at a lower rate than net sales in 20Y3 and 20Y5, causing gross profit to increase at a higher rate than net sales. Operating expenses in 20Y4 increased due to the provision for restructured operations, causing horizontal analysis a significant decrease in income before income taxes. Percentages provide clues to an analyst about which items need further investigation or analysis. In reviewing trend percentages, a financial statement user should pay close attention to the trends in related items, such as the cost of goods sold in relation to sales. Trend analysis that shows a constantly declining gross margin rate may be a signal that future net income will decrease.

trend analysis formula

The mere increase or decrease in trend percentage may give misleading results if studied in isolation. An increase of 20% in current assets may be treated favorable. If this increase in current assets is accompanied by an equivalent increase in current liabilities, then this increase will be unsatisfactory. The increase in sales may not increase profits if the cost of production has also gone up. Learn the definition of trend analysis and understand how to calculate trend percentages.

Formula to Calculate Index Number

All the key data points are connected by a straight line to depict the overall pattern and trend. The Area Chart in Excel is best-suited for displaying patterns and trends of key data points. An Area Chart is a visualization design that uses a line graph and color combination to display pattern and trend insights into key data points in a specified period. A trend is a general direction of key data points in a specified time. While there is no specified minimum time required for a direction to be a trend, the longer it is, the more notable the trend. Master excel formulas, graphs, shortcuts with 3+hrs of Video. Firstly, select a different cell E11 where you want to calculate the Trend analysis of the forecast value.

trend analysis formula

Google Sheets offers plenty of Data Analysis features that we can use to make sense of large data sets. This comparative growth clearly reflects the efficiency and effectiveness of the company’s management. The sales and net profit of Zenith Company for five years are shown below. A Line Chart is a visualization design that displays information as a series of data points connected by straight lines. In other words, you’ve got to spend more time editing the chart to fit your requirements. So, using the TREND function in Excel above, we predicted the three Y values for the new test scores.

Run a Finance Blog? See How You Can Partner With Us

However, since then, the company’s financials have been on an uptrend for the last two years. Trend Analysis is calculated using the change in amount formula given below. Read how InetSoft was rated #1 for user adoption in G2 Crowd’s user survey-based index. This analysis can reveal which product sales are following a steep growth path and which are stalling or declining.

  • Comparability is the ability to review two or more different companies’ financials as a benchmarking exercise.
  • The sales have shown an upward trend except in 2008 when sales were less than the previous year i.e., 2004.
  • Using trend percentages, you can quickly identify whether an account has increased or decreased each year compared to the base year.
  • The trend percentage formula can be used to calculate the percent change in any type of data series.
  • If we provide the argument, it should be of the same length as the set of known_y’s.

Costco Wholesale Corporation presents selected income statement information for the past five years. The fact that these financial data are provided in the annual report confirms the importance of presenting trend information to shareholders. As you have seen, trend analysis has applications in any industry. Within finance, accountants often use it to analyze the company’s financial statements and to identify trends or inconsistencies that could impact the company’s financial performance. Proper analysis does not stop with the calculation of increases and decreases in amounts or percentages over several years. Such changes generally indicate areas worthy of further investigation and are merely clues that may lead to significant findings. Considering these factors along with horizontal analysis, vertical analysis, and trend analysis should provide a reasonable basis for predicting future performance.