Module 4 Mini-Project: Constructing the Cash Flow Statement¶

#Here we are cleaning the data and preping the statments
def prep_raw_statement(path: Path) -> pd.DataFrame:
    df = pd.read_csv(path, parse_dates=["date"])
    df["fiscalYear"] = pd.to_numeric(df["fiscalYear"], errors="coerce").astype("Int64")
    df = df.dropna(subset=["fiscalYear"]).copy()
    df["fiscalYear"] = df["fiscalYear"].astype(int)
    df["period"] = df["period"].astype(str).str.upper()
    df["PeriodLabel"] = "FY" + df["fiscalYear"].astype(str) + "-" + df["period"]
    return df.sort_values("date")


is_raw = prep_raw_statement(CSV_DIR / "RL_income_statement.csv")
bs_raw = prep_raw_statement(CSV_DIR / "RL_balance_sheet.csv")
cf_raw = prep_raw_statement(CSV_DIR / "RL_cash_flow.csv")

print("Loaded raw tables:")
print("Income statement:", is_raw.shape)
print("Balance sheet   :", bs_raw.shape)
print("Cash flow       :", cf_raw.shape)
#output should all be "40" for the amount of quarters we have on the csvs

#data cleaning and aligment of the periods
common_timeline = (
    is_raw[["date", "fiscalYear", "period", "PeriodLabel"]]
    .merge(bs_raw[["date", "fiscalYear", "period", "PeriodLabel"]], on=["date", "fiscalYear", "period", "PeriodLabel"])
    .merge(cf_raw[["date", "fiscalYear", "period", "PeriodLabel"]], on=["date", "fiscalYear", "period", "PeriodLabel"])
    .sort_values("date")
    .reset_index(drop=True)
)

model_timeline = common_timeline.iloc[1:].copy().reset_index(drop=True)
periods = model_timeline["PeriodLabel"].tolist()

display(model_timeline.head(10))
print("Modeled periods:", model_timeline.iloc[0]["PeriodLabel"], "to", model_timeline.iloc[-1]["PeriodLabel"])
print("Number of modeled quarters:", len(model_timeline))
# displaying first 10 to ensure correctness

is_q = is_raw.set_index("PeriodLabel").loc[periods].copy()
cf_q = cf_raw.set_index("PeriodLabel").loc[periods].copy()
bs_all = bs_raw.set_index("PeriodLabel").loc[common_timeline["PeriodLabel"]].copy()
bs_q = bs_raw.set_index("PeriodLabel").loc[periods].copy()
prev_bs_q = bs_all.shift(1).loc[periods].copy()

timeline_index = model_timeline.set_index("PeriodLabel")[["date", "fiscalYear", "period"]].copy()
timeline_index.index.name = "Period"

print("Aligned shapes:")
print("is_q     :", is_q.shape)
print("bs_q     :", bs_q.shape)
print("prev_bs_q:", prev_bs_q.shape)
print("cf_q     :", cf_q.shape)

quarterly_inputs = timeline_index.copy()
quarterly_inputs["revenue"] = is_q["revenue"].values
quarterly_inputs["ebit"] = is_q["ebit"].values
quarterly_inputs["incomeBeforeTax"] = is_q["incomeBeforeTax"].values
quarterly_inputs["incomeTaxExpense"] = is_q["incomeTaxExpense"].values
quarterly_inputs["interestExpense"] = is_q["interestExpense"].values
quarterly_inputs["netIncome"] = is_q["netIncome"].values
quarterly_inputs["depreciationAndAmortization"] = cf_q["depreciationAndAmortization"].values
quarterly_inputs["deferredIncomeTax"] = cf_q["deferredIncomeTax"].values
quarterly_inputs["stockBasedCompensation"] = cf_q["stockBasedCompensation"].values
quarterly_inputs["changeInWorkingCapital"] = cf_q["changeInWorkingCapital"].values
quarterly_inputs["otherNonCashItems"] = cf_q["otherNonCashItems"].values
quarterly_inputs["capitalExpenditure"] = cf_q["capitalExpenditure"].values
quarterly_inputs["netDebtIssuance"] = cf_q["netDebtIssuance"].values
quarterly_inputs["netDividendsPaid"] = cf_q["netDividendsPaid"].values
quarterly_inputs["cash_begin_reported"] = prev_bs_q["cashAndCashEquivalents"].values
quarterly_inputs["cash_end_reported"] = bs_q["cashAndCashEquivalents"].values
quarterly_inputs["delta_cash_from_bs"] = quarterly_inputs["cash_end_reported"] - quarterly_inputs["cash_begin_reported"]

quarterly_inputs_usdm = to_usd_mm(quarterly_inputs, exclude_cols=["fiscalYear"])
display(quarterly_inputs_usdm.head(8).round(1))
#making sure the dataframe was correctly built

working_capital_bridge = timeline_index.copy()
# we create the dataframe columns
wc_cols = [
    "netReceivables",
    "inventory",
    "prepaids",
    "otherCurrentAssets",
    "accountPayables",
    "accruedExpenses",
    "deferredRevenue",
    "taxPayables",
    "otherCurrentLiabilities",
]

#function to get the true delta (unsure if this is the optimal way)
for col in wc_cols:
    working_capital_bridge[f"delta_{col}"] = bs_q[col].values - prev_bs_q[col].values

working_capital_bridge["wc_adjustment_from_bs"] = (
    -working_capital_bridge["delta_netReceivables"]
    - working_capital_bridge["delta_inventory"]
    - working_capital_bridge["delta_prepaids"]
    - working_capital_bridge["delta_otherCurrentAssets"]
    + working_capital_bridge["delta_accountPayables"]
    + working_capital_bridge["delta_accruedExpenses"]
    + working_capital_bridge["delta_deferredRevenue"]
    + working_capital_bridge["delta_taxPayables"]
    + working_capital_bridge["delta_otherCurrentLiabilities"]
)
working_capital_bridge["changeInWorkingCapital"] = cf_q["changeInWorkingCapital"].values
working_capital_bridge["wc_scope_gap"] = (
    working_capital_bridge["changeInWorkingCapital"] - working_capital_bridge["wc_adjustment_from_bs"]
)
#Display making sure is correct and years aligned
working_capital_bridge_usdm = to_usd_mm(working_capital_bridge, exclude_cols=["fiscalYear"])
display(working_capital_bridge_usdm[["fiscalYear", "period", "wc_adjustment_from_bs", "changeInWorkingCapital", "wc_scope_gap"]].head(12).round(1))

cash_flow_statement = timeline_index.copy()

#Creating a single dataframe to work
cash_flow_statement["netIncome"] = is_q["netIncome"].values
cash_flow_statement["depreciationAndAmortization"] = cf_q["depreciationAndAmortization"].values
cash_flow_statement["deferredIncomeTax"] = cf_q["deferredIncomeTax"].values
cash_flow_statement["stockBasedCompensation"] = cf_q["stockBasedCompensation"].values
cash_flow_statement["changeInWorkingCapital"] = cf_q["changeInWorkingCapital"].values
cash_flow_statement["otherNonCashItems"] = cf_q["otherNonCashItems"].values
cash_flow_statement["reclassifications"] = 0.0

#Making CFO calculation
cash_flow_statement["CFO"] = (
    cash_flow_statement["netIncome"]
    + cash_flow_statement["depreciationAndAmortization"]
    + cash_flow_statement["deferredIncomeTax"]
    + cash_flow_statement["stockBasedCompensation"]
    + cash_flow_statement["changeInWorkingCapital"]
    + cash_flow_statement["otherNonCashItems"]
    + cash_flow_statement["reclassifications"]
)

#Appending what is needed for CFI and making the calculations by "hand"
cash_flow_statement["capitalExpenditure"] = cf_q["capitalExpenditure"].values
cash_flow_statement["acquisitionsNet"] = cf_q["acquisitionsNet"].values
cash_flow_statement["purchasesOfInvestments"] = cf_q["purchasesOfInvestments"].values
cash_flow_statement["salesMaturitiesOfInvestments"] = cf_q["salesMaturitiesOfInvestments"].values
cash_flow_statement["otherInvestingActivities"] = cf_q["otherInvestingActivities"].values
cash_flow_statement["CFI"] = (
    cash_flow_statement["capitalExpenditure"]
    + cash_flow_statement["acquisitionsNet"]
    + cash_flow_statement["purchasesOfInvestments"]
    + cash_flow_statement["salesMaturitiesOfInvestments"]
    + cash_flow_statement["otherInvestingActivities"]
)


#Repeat for CFF
cash_flow_statement["netDebtIssuance"] = cf_q["netDebtIssuance"].values
cash_flow_statement["netCommonStockIssuance"] = cf_q["netCommonStockIssuance"].values
cash_flow_statement["netPreferredStockIssuance"] = cf_q["netPreferredStockIssuance"].values
cash_flow_statement["netDividendsPaid"] = cf_q["netDividendsPaid"].values
cash_flow_statement["otherFinancingActivities"] = cf_q["otherFinancingActivities"].values
cash_flow_statement["CFF"] = (
    cash_flow_statement["netDebtIssuance"]
    + cash_flow_statement["netCommonStockIssuance"]
    + cash_flow_statement["netPreferredStockIssuance"]
    + cash_flow_statement["netDividendsPaid"]
    + cash_flow_statement["otherFinancingActivities"]
)

#Putting everything together to find the cash delta
cash_flow_statement["cash_begin"] = prev_bs_q["cashAndCashEquivalents"].values
cash_flow_statement["cash_end_reported_bs"] = bs_q["cashAndCashEquivalents"].values
cash_flow_statement["effectOfForexChangesOnCash"] = cf_q["effectOfForexChangesOnCash"].values
cash_flow_statement["cash_definition_gap"] = (
    cash_flow_statement["cash_end_reported_bs"]
    - cash_flow_statement["cash_begin"]
    - cash_flow_statement["CFO"]
    - cash_flow_statement["CFI"]
    - cash_flow_statement["CFF"]
    - cash_flow_statement["effectOfForexChangesOnCash"]
)
#Not forgetting the other as mentioned in chapters PDF

cash_flow_statement["other_cash"] = (
    cash_flow_statement["effectOfForexChangesOnCash"]
    + cash_flow_statement["cash_definition_gap"]
)
cash_flow_statement["delta_cash"] = (
    cash_flow_statement["CFO"]
    + cash_flow_statement["CFI"]
    + cash_flow_statement["CFF"]
    + cash_flow_statement["other_cash"]
)
cash_flow_statement["cash_end"] = cash_flow_statement["cash_begin"] + cash_flow_statement["delta_cash"]

cash_flow_statement_usdm = to_usd_mm(cash_flow_statement, exclude_cols=["fiscalYear"])
display(cash_flow_statement_usdm[["fiscalYear", "period", "CFO", "CFI", "CFF", "other_cash", "delta_cash", "cash_begin", "cash_end"]].head(12).round(1))

balance_sheet_shell = timeline_index.copy()
balance_sheet_shell["cashAndCashEquivalents_model"] = cash_flow_statement["cash_end"].values
balance_sheet_shell["shortTermInvestments"] = bs_q["shortTermInvestments"].values
balance_sheet_shell["netReceivables"] = bs_q["netReceivables"].values
balance_sheet_shell["inventory"] = bs_q["inventory"].values
balance_sheet_shell["prepaids"] = bs_q["prepaids"].values
balance_sheet_shell["otherCurrentAssets"] = bs_q["otherCurrentAssets"].values
balance_sheet_shell["totalCurrentAssets_model"] = (
    balance_sheet_shell["cashAndCashEquivalents_model"]
    + balance_sheet_shell["shortTermInvestments"]
    + balance_sheet_shell["netReceivables"]
    + balance_sheet_shell["inventory"]
    + balance_sheet_shell["prepaids"]
    + balance_sheet_shell["otherCurrentAssets"]
)
balance_sheet_shell["nonCurrentAssets_reported"] = bs_q["totalAssets"].values - bs_q["totalCurrentAssets"].values
balance_sheet_shell["totalAssets_model"] = (
    balance_sheet_shell["totalCurrentAssets_model"]
    + balance_sheet_shell["nonCurrentAssets_reported"]
)
balance_sheet_shell["totalLiabilitiesAndTotalEquity"] = bs_q["totalLiabilitiesAndTotalEquity"].values
balance_sheet_shell["balance_error"] = (
    balance_sheet_shell["totalAssets_model"]
    - balance_sheet_shell["totalLiabilitiesAndTotalEquity"]
)

balance_sheet_shell_usdm = to_usd_mm(balance_sheet_shell, exclude_cols=["fiscalYear"])
display(balance_sheet_shell_usdm[["fiscalYear", "period", "cashAndCashEquivalents_model", "totalAssets_model", "totalLiabilitiesAndTotalEquity", "balance_error"]].head(12).round(1))

integrity_report = timeline_index.copy()
integrity_report["cash_flow_identity_error"] = (
    cash_flow_statement["delta_cash"]
    - cash_flow_statement["CFO"]
    - cash_flow_statement["CFI"]
    - cash_flow_statement["CFF"]
    - cash_flow_statement["other_cash"]
)
integrity_report["cash_roll_forward_error"] = (
    cash_flow_statement["cash_end"]
    - cash_flow_statement["cash_begin"]
    - cash_flow_statement["delta_cash"]
)
integrity_report["cash_to_bs_error"] = (
    cash_flow_statement["cash_end"] - bs_q["cashAndCashEquivalents"].values
)
integrity_report["balance_error"] = balance_sheet_shell["balance_error"].values
integrity_report["cfo_vs_reported"] = (
    cash_flow_statement["CFO"] - cf_q["netCashProvidedByOperatingActivities"].values
)

# What I believe to be the most important part, making sure our calculations are the same values as reported in the original CF reported by RL
integrity_report["cfi_vs_reported"] = (
    cash_flow_statement["CFI"] - cf_q["netCashProvidedByInvestingActivities"].values
)
integrity_report["cff_vs_reported"] = (
    cash_flow_statement["CFF"] - cf_q["netCashProvidedByFinancingActivities"].values
)
integrity_report["delta_cash_vs_reported"] = (
    cash_flow_statement["delta_cash"] - cf_q["netChangeInCash"].values
)
integrity_report["cash_definition_gap"] = cash_flow_statement["cash_definition_gap"].values

#Tolerance for the test checks of fail
tolerance = 2.0 * USD_MM
integrity_report["passes_all_core_checks"] = (
    integrity_report[["cash_flow_identity_error", "cash_roll_forward_error", "cash_to_bs_error", "balance_error"]]
    .abs()
    .max(axis=1)
    <= tolerance
)

integrity_report_usdm = to_usd_mm(integrity_report, exclude_cols=["fiscalYear"])
display(integrity_report_usdm[["fiscalYear", "period", "cash_to_bs_error", "balance_error", "cash_definition_gap", "passes_all_core_checks"]].tail(12).round(3))
print("All quarters pass:", bool(integrity_report["passes_all_core_checks"].all()))

pretax_income = is_q["incomeBeforeTax"].replace(0, np.nan)
raw_tax_rate = is_q["incomeTaxExpense"] / pretax_income
fallback_tax_rate = raw_tax_rate.replace([np.inf, -np.inf], np.nan).median()
if pd.isna(fallback_tax_rate):
    fallback_tax_rate = 0.25
tax_rate_used = raw_tax_rate.where(
    (pretax_income > 0) & raw_tax_rate.between(0.0, 0.60),
    np.nan,
).fillna(fallback_tax_rate)

fcf_outputs = timeline_index.copy()
fcf_outputs["revenue"] = is_q["revenue"].values
fcf_outputs["ebit"] = is_q["ebit"].values
fcf_outputs["tax_rate_used"] = tax_rate_used.values
fcf_outputs["NOPAT"] = (is_q["ebit"] * (1.0 - tax_rate_used)).values
fcf_outputs["depreciationAndAmortization"] = cf_q["depreciationAndAmortization"].values
fcf_outputs["other_operating_adjustments"] = (
    cf_q["deferredIncomeTax"]
    + cf_q["stockBasedCompensation"]
    + cf_q["otherNonCashItems"]
).values
fcf_outputs["netCapEx"] = (-cf_q["capitalExpenditure"]).values
fcf_outputs["delta_owc_use_of_cash"] = (-cf_q["changeInWorkingCapital"]).values
fcf_outputs["FCFF"] = (
    fcf_outputs["NOPAT"]
    + fcf_outputs["depreciationAndAmortization"]
    + fcf_outputs["other_operating_adjustments"]
    - fcf_outputs["netCapEx"]
    - fcf_outputs["delta_owc_use_of_cash"]
)
fcf_outputs["netBorrowing"] = cf_q["netDebtIssuance"].values
fcf_outputs["after_tax_interest"] = (is_q["interestExpense"] * (1.0 - tax_rate_used)).values
fcf_outputs["FCFE_from_NI_bridge"] = (
    is_q["netIncome"]
    + cf_q["depreciationAndAmortization"]
    + cf_q["deferredIncomeTax"]
    + cf_q["stockBasedCompensation"]
    + cf_q["otherNonCashItems"]
    - (-cf_q["capitalExpenditure"])
    - (-cf_q["changeInWorkingCapital"])
    + cf_q["netDebtIssuance"]
).values
fcf_outputs["FCFE_from_FCFF_bridge"] = (
    fcf_outputs["FCFF"]
    - fcf_outputs["after_tax_interest"]
    + fcf_outputs["netBorrowing"]
)
fcf_outputs["FCFE_bridge_gap"] = (
    fcf_outputs["FCFE_from_NI_bridge"] - fcf_outputs["FCFE_from_FCFF_bridge"]
)

fcf_outputs_usdm = to_usd_mm(fcf_outputs, exclude_cols=["fiscalYear", "tax_rate_used"])
display(fcf_outputs_usdm[["fiscalYear", "period", "FCFF", "FCFE_from_NI_bridge", "FCFE_from_FCFF_bridge", "FCFE_bridge_gap"]].tail(12).round(1))

quarterly_inputs_usdm.to_csv(OUT_DIR / "chapter5_quarterly_inputs_usdm.csv")
working_capital_bridge_usdm.to_csv(OUT_DIR / "chapter5_quarterly_working_capital_bridge_usdm.csv")
cash_flow_statement_usdm.to_csv(OUT_DIR / "chapter5_quarterly_cash_flow_statement_usdm.csv")
balance_sheet_shell_usdm.to_csv(OUT_DIR / "chapter5_quarterly_balance_sheet_shell_usdm.csv")
integrity_report_usdm.to_csv(OUT_DIR / "chapter5_quarterly_integrity_report.csv")
fcf_outputs_usdm.to_csv(OUT_DIR / "chapter5_quarterly_fcf_outputs_usdm.csv")

latest_period = cash_flow_statement_usdm.index[-1]
latest_cf = cash_flow_statement_usdm.loc[latest_period]
latest_fcf = fcf_outputs_usdm.loc[latest_period]



(OUT_DIR / "chapter5_quarterly_review_summary.txt")

print("Outputs saved to:", OUT_DIR)

ax = cash_flow_statement_usdm[["CFO", "CFI", "CFF"]].plot(
    kind="bar",
    stacked=True,
    figsize=(14, 6),
    color=["#1b6ca8", "#f08a24", "#2f855a"],
)
cash_flow_statement_usdm["delta_cash"].plot(
    ax=ax,
    color="black",
    marker="o",
    linewidth=2,
    label="delta_cash",
)
ax.set_title("Quarterly CFO / CFI / CFF and delta_cash")
ax.set_xlabel("Quarter")
ax.set_ylabel("USD mm")
ax.axhline(0, color="black", linewidth=1)
ax.tick_params(axis="x", rotation=75)
ax.legend(loc="best")
plt.tight_layout()
plt.show()