Implementing Data Analysis with Financial Data

Jan 30, 2020 • 14 Minute Read

Introduction

Analyzing financial data is a critical requirement for any organization. In financial analytics, it is important to understand the basics of financial data and be able to analyze it.

In this guide, you will learn the fundamental financial concepts of growth, time value of money, cost of capital, and the capital budgeting methods to evaluate project financing decisions. You will also learn how to compute these measures using Python.

Growth Rates

The entire world is driven by growth and returns. Companies that manage to maintain their growth rates succeed, while others fall behind. We'll look more closely at this concept, but before that, we'll create a fictitious dataset with pandas using the lines of code below.

The first line of code below imports the pandas library, while the second line initializes the list. The third line of code uses the .DataFrame() function to convert this list into a data frame, while the fourth line prints the data.

      import pandas as pd 

dat = [{'2015': 5, '2016':6, '2017':8, '2018':10, '2019':13}, {'2015': 4, '2016':4.8, '2017':6.1, '2018':8, '2019':10},{'2015': 1, '2016':1.2, '2017':1.9, '2018':2, '2019':3},] 

df = pd.DataFrame(dat, index =['Revenue', 'Expenses', 'Profit']) 
df

Output:

      |          	| 2015 	| 2016 	| 2017 	| 2018 	| 2019 	|
|----------	|------	|------	|------	|------	|------	|
| Revenue  	| 5    	| 6.0  	| 8.0  	| 10   	| 13   	|
| Expenses 	| 4    	| 4.8  	| 6.1  	| 8    	| 10   	|
| Profit   	| 1    	| 1.2  	| 1.9  	| 2    	| 3    	|
    

The output above is a simplified version of the income statement, but it will serve our purpose of understanding the concept of growth. If we want to calculate the year-on-year growth rate in revenues for 2019, we can use the first line of code below, and the second line will print the resulting data with the new variable.

      df['YoY Growth'] = ((df['2019'] - df['2018'])/df['2018'])*100
df
    

Output:

      |          	| 2015 	| 2016 	| 2017 	| 2018 	| 2019 	| YoY Growth 	|
|----------	|------	|------	|------	|------	|------	|------------	|
| Revenue  	| 5    	| 6.0  	| 8.0  	| 10   	| 13   	| 30.0       	|
| Expenses 	| 4    	| 4.8  	| 6.1  	| 8    	| 10   	| 25.0       	|
| Profit   	| 1    	| 1.2  	| 1.9  	| 2    	| 3    	| 50.0       	|
    

From the above output, we can infer that year-on-year Revenue, Expenses and Profit growth in 2019 were 30 percent, 25 percent, and 50 percent, respectively. However, looking at only one year growth might be misleading, which is why it's preferred to look at the compounded annual growth rate (CAGR). CAGR measures mean growth rates of numeric variables over the years and is a better metric for analyzing growth rates. We can calculate CAGR using the code below.

      df['CAGR'] = ((df['2019']/df['2015'])**(1/4.0)-1)*100
df
    

Output:

      |          	| 2015 	| 2016 	| 2017 	| 2018 	| 2019 	| YoY Growth 	| CAGR      	|
|----------	|------	|------	|------	|------	|------	|------------	|-----------	|
| Revenue  	| 5    	| 6.0  	| 8.0  	| 10   	| 13   	| 30.0       	| 26.982343 	|
| Expenses 	| 4    	| 4.8  	| 6.1  	| 8    	| 10   	| 25.0       	| 25.743343 	|
| Profit   	| 1    	| 1.2  	| 1.9  	| 2    	| 3    	| 50.0       	| 31.607401 	|
    

We can compare the difference between the one-year growth rate, YoY Growth, and the compounded annual growth rate, CAGR. For example, annual profit growth is 50 percent in 2019, but the CAGR for the years 2015 through 2019 is approximately 32 percent. This difference illustrates the importance of measuring financial performance with CAGR.

Projections

The growth rates we discussed above can be used for future projections as well. For instance, if we want to project a financial statement for this data three years from now using CAGR, we can easily do so using the lines of code below.

      df['Projection_3year'] = df['2019']*(1+(df['CAGR']/100))**(5)
df
    

Output:

      |          	| 2015 	| 2016 	| 2017 	| 2018 	| 2019 	| YoY Growth 	| CAGR      	| Projection_3year 	|
|----------	|------	|------	|------	|------	|------	|------------	|-----------	|------------------	|
| Revenue  	| 5    	| 6.0  	| 8.0  	| 10   	| 13   	| 30.0       	| 26.982343 	| 42.920032        	|
| Expenses 	| 4    	| 4.8  	| 6.1  	| 8    	| 10   	| 25.0       	| 25.743343 	| 31.435836        	|
| Profit   	| 1    	| 1.2  	| 1.9  	| 2    	| 3    	| 50.0       	| 31.607401 	| 11.844666        	|
    

Time Value of Money

The time value of money is one of the most important concepts in financial investing. The simple rationale is that the present value of money will depreciate with time, which means that one dollar in your pocket today is more than the one dollar in your pocket one year from now because of inflation. Let's illustrate this by calculating the present value of $100 five years from now, assuming inflation of 3 percent, using the lines of code below.

We use the pv(rate, nper, pmt, fv) function from the numpy package to calculate the present value of money with a few simple parameters.

rate: The rate of return.

nper: The timespan of the investment.

pmt: The fixed payment at the beginning or end of each period. In our example, this value is zero.

fv: The future value of the investment.

      import numpy as np
np.pv(rate=0.03, nper=5, pmt=0, fv=100)
    

Output:

      -86.2608784384164

Similarly, we can calculate the future value of $100 invested for 5 years at a 3 percent annual rate of return using the numpy's .fv(rate, nper, pmt, pv) function, using the lines of code below.

      np.fv(rate=0.03, nper=5, pmt=0, pv=-100)

Output:

      115.92740743

The future value comes out to be $115. It is important to note that in the above function, we have passed the negative value into the pv parameter. This is because it represents a cash outflow made in the first year and is required for calculating the future value.

Capital Budgeting

Capital budgeting is the process in financial management of evaluating potential projects or investments. Construction of a new plant or purchase of a new machinery are examples of capital budgeting projects. There are several methods for evaluating projects. We'll consider the two most popular ones—net present value (NPV) and internal rate of return (IRR)—and their implementation in Python.

Net Present Value

In simple terms, the net present value (NPV) is the difference between the present value of a project's cash inflows and cash outflows over a period of time. We'll use the numpy function .npv(array of values) to compute the net present value. Let's take four projects with the following cash flows. The discount rate is 5 percent for all these projects.

Project 1: Cash outflow of -$1000 in first year, followed by cash inflows of $1000 in second year, $2000 in third year, $3000 in fourth year, and $4000 in the fifth year.

Project 2: Cash inflows of $1000 in first year and $2000 in second year, followed by cash outflow of -$1000 in third year, inflow of $3000 in fourth year, and inflow of $4000 in fifth year.

Project 3: Cash outflow of -$1000 in first year, followed by cash inflows of $4000 in second year, $3000 in third year, $2000 in fourth year, and $1000 in fifth year.

Project 4: Cash outflows of -$1000 and -$2000 in first and third years with cash inflows of $3000 in second year, $2000 in fourth year, and $7000 in fifth year.

It's important to note that even though the cash flow pattern is different in these four projects, the absolute project value for each project comes out to be $9000 if we sum all the cash flows. So the question is, which of these projects should be selected? The answer comes from the magical concept of the time value of money, which we'll use to calculate the present value of all the cash flows. The lines of code below perform this task.

      # Project 1 NPV
print(np.npv(rate=0.05, values=np.array([-1000, 1000, 2000, 3000, 4000])))

# Project 2 NPV
print(np.npv(rate=0.05, values=np.array([1000, 2000, -1000, 3000, 4000])))

# Project 3 NPV
print(np.npv(rate=0.05, values=np.array([-1000, 4000, 3000, 2000, 1000])))

# Project 4 NPV
print(np.npv(rate=0.05, values=np.array([-1000, 3000, -2000, 2000, 7000])))
    

Output:

The above output shows that the NPV is highest for the third project, so with everything else being equal, the third project should get preference over the others. While NPV is useful, another important measure widely used by finance professionals for selecting capital budgeting proposals is the IRR method, as discussed below.

Internal Rate of Return

Internal rate of return (IRR) is the rate of return at which the net present value of all the cash flows from a project or investment becomes equal to zero. IRR is more intuitive to understand than NPV. We'll use the numpy .irr(array of values) function to compute the internal rate of return.

Let's calculate the IRR of all four projects discussed above using the lines of code below.

      # Project 1 IRR
print(np.irr(np.array([-1000, 1000, 2000, 3000, 4000])))

# Project 2 IRR
print(np.irr(np.array([1000, 2000, -1000, 3000, 4000])))

# Project 3 IRR
print(np.irr(np.array([-1000, 4000, 3000, 2000, 1000])))

# Project 4 IRR
print(np.irr(np.array([-1000, 3000, -2000, 2000, 7000])))
    

Output:

517996077869768
 
 nan

732626152716434

8474807808952747

The IRR for Project 1, Project 3, and Project 4 is 151 percent, 373 percent, and 185 percent, respectively. The second project does not return any IRR because there is no cash outflow in the first year, which is a requirement for calculating IRR and a limitation of this method. Using the IRR method, we would select Project 3 with the highest IRR of 373 percent.

Weighted Average Cost of Capital

IRR is a good metric to evaluate investment projects. However, what if the rate of return is less than the cost of financing the investment? For instance, if the IRR of a project is ten percent and the cost of capital for funding the project is 12 percent, then it will not be a lucrative idea to invest in that project. That is why it's necessary to compute the weighted average cost of capital.

The weighted average cost of capital (WACC) is the rate that a company is expected to pay to all its security holders for financing its assets. The WACC is commonly referred to as the firm's cost of capital and is also influenced by external market factors. The formula for calculating the weighted average cost of capital is

WACC = (equity_proportion*equity_cost) + (debt_proportion*debt_cost) * (1 - taxrate)

where

equity_proportion: The percentage of a company's financing via equity.
equity_cost: The cost of equity for the company.
debt_proportion: The percentage of a company's financing via debt.
debt_cost: The cost of debt for the company.
taxrate: The corporate tax rate.

Let's calculate the WACC of a company with a 10 percent cost of debt, 15 percent cost of equity, 40 percent debt financing and 60 percent equity financing. The effective corporate tax rate is 30 percent. It's simple to calculate the WACC using the above formula.

      equity_proportion = 0.60
debt_proportion = 0.40
equity_cost = 0.15
debt_cost = 0.10
taxrate = 0.30
WACC = (equity_proportion*equity_cost) + (debt_proportion*debt_cost) * (1 - taxrate)
WACC
    

Output:

      0.118

The WACC comes out to be 11.8 percent. The simple interpretation of this is that if the IRR of the project is more than the WACC for the company, it makes sense to select the project proposal.

Conclusion

In this guide, you learned about the basics of analyzing financial data. You learned the important concepts of growth, CAGR and the time value of money. You also learned several capital budgeting techniques for evaluating investment project proposals. Finally, you learned how to implement these concepts in Python.

To learn more about data science using Python, please refer to the following guides.