Exercise

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.

Instructions 1/2

undefined XP
    1
    2
  • Set the receivables_metric to a list containing 'Receivables'.
  • Using the method .isin(), create a boolean series receivables_filter, based on whether each row in the column metric contains receivables_metric.
  • Use this series to filter balance_sheet, assigning the result to filtered_balance_sheet.