Financial Model with Debt Funding Excel Template contains the following sheets and calculations described in each step of this tutorial:
- Cash Flow Model: Uses basic assumptions and Excel formulas to project revenue and costs. The projected sales units drive revenues and direct sales. Then, fixed costs calculations follow and finally, capital expenditures. The model also calculated the estimated taxes, working capital and depreciation charge.
- Debt Model: Takes the cash flow model results to determine the level of potential debt finding. The model splits the cash flow into tranches, so the debt is raised based on the period. That allows you to differ terms such as interest, grace periods and repayments over time.
- Summary: Consolidates the Cash Flow Model and Debt Model sheets results and aggregates them into yearly view.
- Equity Returns: Calculates the level of required equity funding under both pre-debt financing (unlevered) and post-debt financing (levered) scenario. It uses an EBITDA multiplier to estimate the enterprise/exit value level and the corresponding returns of equity.
- Balance Sheet and Debt: This sheet summarises the projected yearly balance of assets, liabilities and equity investment. The debt summary summarises the debt liability.