During this post, we are going to perform an income statement sensitivity analysis using Python. We will first retrieve the income statement for a company. Then, we will check how an increase of sales impact net income by projecting the whole income statement. And all what we need is Python.
Photo by Pixabay on Pexels
Why Sensitivity Analysis in Finance?
Sensitivity analysis is used to project values based on changes in an independent variable. This method is particularly useful to answer what if questions.
We can apply sensitivity analysis in finance in order to analyse what if scenarios in any metrics or company financials that we are interested in. For example, we can analyse what will be the effect on net income if sales drop by 10%. Or we could simply have a range of values for sales and based on them, simulate potential effects on different financial ratios or company financial statements.
In this post, we will only apply the sensitivity analysis into income statement projections. Note that sensitivity analysis in finance has many other use cases. For example, we could predict the price of a stock by changing different variables affecting stock prices such as company earnings, debt ratio, etc.
Lets have a look at how to perform such a sensitivity analysis in a company income statement using Python.
Income Statement analysis with Python
To do income statement analysis, we need to do the following:
-
Retrieve latest yearly Apple Income Statement using the free endpoint.
-
Calculate each income statement item as a percentage of revenue. This will be used to calculate the projection of the income statement based on different sensitivity values (i.e. increase in sales of 10%).
-
Project a good outcome income statement. We will calculate and simulate an increase of sales and project the rest of the income statement items based on this change.
-
Project a bad outcome (i.e. negative sales) income statement.
To start with the Python code, we will perform an http request to retrieve Apple income statement for the last year.
import pandas as pd
import requests
demo='your api key'
stock = 'AAPL'
IS = requests.get(f'https://financialmodelingprep.com/api/v3/income-statement/{stock}?apikey={demo}').json()
IS2021 = IS[0]
Then, we define our sensitivity values. In this case, we want to see the effect on the income statement if revenues grow by 3%. That will be our good outcome scenario. In addition, we will also check the effect of a 10% reduction of sales.
growth = 1.03
negative_growth = 0.90
#create the dictionaries
sensititvity = {}
sensititvity['last_year'] = {}
sensititvity['projection'] = {}
sensititvity['projection_bad'] = {}
We will add the three income statements, that is the actual and the two projected ones, into a Python dictionary. The dictionary will contain three nested dictionaries with each of the three income statements.
Next, we parse the API returned data and add each of the variables into the last_year dictionary by parsing the relevant keys.
You can see here, the structure and the key value pairs of the returned dictionary. Below is the code to extract the income statement line items and add them to our dictionary.
To keep it short, I will only extract a few of the income statement line items as per below code:
#latest available data
sensititvity['last_year']['revenue'] = IS2021['revenue']
sensititvity['last_year']['COGS'] = IS2021['costOfRevenue']
sensititvity['last_year']['grossProfit'] = IS2021['grossProfit']
sensititvity['last_year']['opinc'] = IS2021['operatingIncome']
sensititvity['last_year']['intexp'] = IS2021['interestExpense']
sensititvity['last_year']['netIncome'] = IS2021['netIncome']
Having now the latest income statement for Apple into the last_year dictionary, we can move on and calculate each of the line items as a percentage of sales. This percentage will be our key to determine each of the line items of the projected income statement other than sales.
#item as a percentage of sales
sensititvity['last_year']['COGSpersales'] = sensititvity['last_year']['COGS'] /sensititvity['last_year']['revenue']
sensititvity['last_year']['grossProfitpersales'] = sensititvity['last_year']['grossProfit'] /sensititvity['last_year']['revenue']
sensititvity['last_year']['opincpersales'] = sensititvity['last_year']['opinc']/sensititvity['last_year']['revenue']
sensititvity['last_year']['intexppersales'] = sensititvity['last_year']['intexp']/sensititvity['last_year']['revenue']
sensititvity['last_year']['netIncomepersales'] = sensititvity['last_year']['netIncome']/sensititvity['last_year']['revenue']
Projecting the Income Statement
Finally, we can start calculating our income statement projections. For the good projection, note that we calculate the increase of revenue by multiplying revenue by the growth rate. Then, the rest of the income statement items are calculated using the percentage of sales allocation that we computed above:
#good projection
sensititvity['projection']['revenue'] = sensititvity['last_year']['revenue'] * growth
sensititvity['projection']['COGS'] = sensititvity['last_year']['COGSpersales'] * sensititvity['projection']['revenue']
sensititvity['projection']['grossProfit'] = sensititvity['last_year']['grossProfitpersales'] * sensititvity['projection']['revenue']
sensititvity['projection']['opinc'] = sensititvity['last_year']['opincpersales'] * sensititvity['projection']['revenue']
sensititvity['projection']['intexp'] = sensititvity['last_year']['intexppersales'] * sensititvity['projection']['revenue']
sensititvity['projection']['netIncome'] = sensititvity['last_year']['netIncomepersales'] * sensititvity['projection']['revenue']
And we repeat the same steps in order to project the bad outcome income statement:
#bad projection
sensititvity['projection_bad']['revenue'] = sensititvity['last_year']['revenue'] * negative_growth
sensititvity['projection_bad']['COGS'] = sensititvity['last_year']['COGSpersales'] * sensititvity['projection_bad']['revenue']
sensititvity['projection_bad']['grossProfit'] = sensititvity['last_year']['grossProfitpersales'] * sensititvity['projection_bad']['revenue']
sensititvity['projection_bad']['opinc'] = sensititvity['last_year']['opincpersales'] * sensititvity['projection_bad']['revenue']
sensititvity['projection_bad']['intexp'] = sensititvity['last_year']['intexppersales'] * sensititvity['projection_bad']['revenue']
sensititvity['projection_bad']['netIncome'] = sensititvity['last_year']['netIncomepersales'] * sensititvity['projection_bad']['revenue']
Apple sensitivity analysis - overview
In the last step, we are going to represent the data in a Pandas DataFrame following the income statement structure. To do this, we are going to transform our three dictionaries into a Pandas DataFrame and perform some basic data manipulations.
First, lets create the Pandas DataFrame using the method pd.DataFrame.from_dict(). Note that we pass our dictionary as an argument:
sensitivity_analysis = pd.DataFrame.from_dict(sensititvity,orient='columns')
By looking into above Pandas DataFrame, we can see that we still have some data clean up to do.
For example, lets represent the amounts in millions by dividing each item by 1,000,000. Also, we do not need to keep the per sales percentage of each of income statement items. Therefore, we will drop all rows where the index contains per sales.
That should give us a cleaner overview of the projected income statements for Apple:
#show in milions
sensitivity_analysis = sensitivity_analysis/1000000
sensitivity_analysis.reset_index(inplace=True)
sensitivity_analysis = sensitivity_analysis[~sensitivity_analysis["index"].str.contains('persales')]
sensitivity_analysis
Wrapping Up
As we have seen during the article, we can easily use Python in order to make financial projections or sensitivity analysis. We have projected the income statement for Apple based on a good and a bad outcome.
Alternatively, you could perform sensitivity analysis with multiple options or change other line items to project the effect on the income statement. For example, what if COGS increase by 10% and sales remain constant? Or if suddenly taxes are 2% lower, how that would impact the net income?
Thanks for making it to the end of the post on sensitivity analysis with Python!
