Calculating balance sheet ratios for Ford
Now we will look at a real life example, Ford Inc, a company producing motor vehicles. We have uploaded a dataset: balance_sheet with the data for Ford Inc's most recent balance sheet. The sales and cost of sales figures have been provided for 2017 with the Key_Figures_Memodataset.
We are only interested in one line on the balance sheet, the Receivables (another name for Debtors), and therefore need to create a filter for this. In this exercise, we will use boolean indexing to filter our dataset for Receivables in the metric column. We will first specify our metric of interest ('Receivables'), and then check whether the column of interest has this value in each row. This will generate a boolean series of True and False values. With this series, we can then filter our existing dataset.
Once we have filtered our dataset, we can retrieve the receivables values from the most recent time period and calculate the debtor days ratio (provided below).
\(Debtor Days = \frac{Ending\,Balance\,Debtors}{Sales} \times Days\,in\,Financial\,Year\)
The balance_sheet and sales figures are provided.
This exercise is part of the course
Financial Forecasting in Python
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
# Create the filter metric for Receivables
receivables_metric = ____
# Create a boolean series with your metric
receivables_filter = balance_sheet.____.____(____)
# Use the series to filter the dataset
filtered_balance_sheet = ____[____]