Foundation Of Financial Planning Assignment

Top 10 types of financial models

There are many different types of financial models.  In this guide, we will outline the top 10 most common models used in corporate finance by financial modeling professionals.

Here is a list of the 10 most common types of financial models:

  1. Three Statement Model
  2. Discounted Cash Flow (DCF) Model
  3. Merger Model (M&A)
  4. Initial Public Offering (IPO) Model
  5. Leveraged Buyout (LBO) Model
  6. Sum of the Parts Model
  7. Consolidation Model
  8. Budget Model
  9. Forecasting Model
  10. Option Pricing Model

 

More detail about each type of financial model

To learn more about each of the types of financial models and perform detailed financial analysis, we have laid out detailed descriptions below.  The key to being able to model finance effectively is to have good templates and a solid understanding of corporate finance.

 

 

#1 Three Statement Model

The 3 statement model is the most basic setup for financial modeling. As the name implies, in this model the three statements (income statement, balance sheet, and cash flow) are all dynamically linked with formulas. in Excel.  The objective is to set it up so all the accounts are connected, and a set of assumptions can drive changes in the entire model.  It’s important to know how to link the 3 financial statements, which requires a solid foundation of accounting, finance and Excel skills.

Learn the foundations in our online financial modeling courses.

 

#2 Discounted Cash Flow (DCF) Model

The DCF model builds on the 3 statement model to value a company based on the Net Present Value (NPV) of the business’ future cash flow. The DCF model takes the cash flows from the 3 statement model, makes some adjustments where necessary, and then uses the XNPV function in Excel to discount them back to today at the company’s Weighted Average Cost of Capital (WACC).

These types of financial models are used in equity research and other areas of the capital markets.

 

#3 Merger Model (M&A)

The M&A model is a more advanced model used to evaluate the pro forma accretion/dilution of a merger or acquisition.  It’s common to use a single tab model for each company, where the consolidation where Company A + Company B = Merged Co.  The level of complexity can vary widely and is most commonly used in investment banking and/or corporate development.

 

#4 Initial Public Offering (IPO) Model

Investment bankers and corporate development professionals will also build IPO models in Excel to value their business in advance of going public. These models involve looking at comparable company analysis in conjunction with an assumption about how much investors would be willing to pay for the company in question.  The valuation in an IPO model includes “an IPO discount” to ensure the stock trades well in the secondary market.

 

#5 Leveraged Buyout (LBO) Model

A leveraged buyout transaction typically requires modeling complicated debt schedules and is an advanced form of financial modeling.  An LBO is often one of the most detailed and challenging of all types of financial models as they many layers of financing create circular references and require cash flow waterfalls.  These types of models are not very common outside of private equity or investment banking.

 

#6 Sum of the Parts Model

This type of model is built by taking several DCF models and adding them together.  Next, any additional components of the business that might not be suitable for a DCF analysis (i.e. marketable securities, which would be valued based on the market) are added to that value of the business.  So, for example, you would sum up (hence “Sum of the Parts”) the value of business unit A, business unit B, and investments C, less liabilities D to arrive at the Net Asset Value for the company.

 

#7 Consolidation Model

This type of model includes multiple business units added into one single model. Typically each business unit is its own tab, with consolidation tab that simply sums up the other business units.  This is similar to a Sum of the Parts exercise where Division A and Division B are added together and a new, consolidated worksheet is created.

 

#8 Budget Model

This is used to model finance for professionals in financial planning & analysis (FP&A) to get the budget together for the coming year(s).  Budget models are typically designed to be based on monthly or quarterly figures and focus heavily on the income statement.

 

#9 Forecasting Model

This type is also used in financial planning and analysis (FP&A) to build a forecast that compares to the budget model. Sometimes the budget and forecast models are one combined workbook and sometimes they are totally separate.

Learn more: see a step-by-step demonstration of how to build a forecast model.

 

#10 Option Pricing Model

The two main types of models are binomial tree and Black-Sholes. These models are based purely on mathematical models rather than subjective criteria and therefore are more or less a straightforward calculator built into Excel.

 

Examples of financial models

Below are some screenshots of the various types of financial models discussed above.

If you’d like to have the templates, you can always download our financial models.

 

3 Statement Model

Here is a screenshot of the balance sheet section of a 3 statement, single worksheet model.  Each of the other sections can easily be expanded or contracted to view sections of the model independently.  See our free webinar on how to build a 3 statement model.

 

 

Learn more: download our 3 statement financial model.

 

DCF Model

Here is a screenshot of the valuation section in a DCF model.  In this section, the cash flows that were calculated above are being placed in sequence along with the purchase prices of the business to arrive at the internal rate of return (IRR) and Net Present Value (NPV).  See our guide to DCF models.

 

 

Learn more: download our DCF model.

 

LBO Model

Here is an example of an LBO model.  As you see below, the LBO transactions require a specific type of financial model that focuses heavily on the company’s capital structure and leverage to enhance equity returns.  Learn more about LBO transactions and LBO models.

 

 

Learn more: download our LBO model.

 

M&A Model

Here is an example of an M&A model used to evaluate the impact of an acquisition.  The M&A model is a more advanced type of financial modeling as requires making adjustments to arrive a Pro Forma closing balance sheet, incorporating synergies and terms of the deal, as well as modeling accretion/dilution, as well as performing sensitivity analysis, and determining the expected impact on valuation.

 

 

View of the above models in CFI’s Financial Modeling Templates Course.

 

More learning

We hope this has been a helpful guide! To learn more about financial modeling and valuation you may want to check out:

To find out more about finance careers out our interactive Career Map.

BU113: Critical Thinking and Communication in Business
Saint Michael's College

Preparing the Business Plan--report and presentation (due 3/5)

  • Write the Business Plan as a report, not a memo. 
    • Include a cover page with the name of the group/company, the names and positions of the members/managers, the class, date.  Please state on the cover page that “This information is confidential.” The next page should be a Table of Contents. 
    • The body of the Business Plan should be divided into sections with headings (see sections 1. - 4. below) and written in paragraph form, single-spaced, with no indentations.  Paragraphs (with topic sentences) should introduce each section.
    • Charts (graphs, tables) included in the report must also be discussed in text form.
    • There is no length limitation for this assignment.
  • See the slide show on the Financial Plan (part of the Business Plan slide show) to do the financial calculations for your report and to see what your Projected Income Statement should look like.
  • Use the Capital-Inventory-Sales-Expenses spreadsheet set up for your group to calculate your capital and inventory and to keep a weekly record of all sales and expenses (see email).
  • See the Business Report Scoring Rubric, which will be used to evaluate your Business Plan--a report.
  • Review the samples available in DocSharing--Samples of Class Assignments.  Click the drop-down arrow next to "Select View".  See also the real-world sample with great graphs and charts for the Bakery Business Plan.
  • Attach the completed and signed Group Contribution Form to the paper (also found in DocSharing > Forms).
  • In class, submit the written Business Plan with supporting PowerPoint slides (in handout form, 6 slides per page).
  • Present your Business Plan to the class in a PowerPoint presentation of approximately 7-9 minutes.
  • Upload your PowerPoint presentation to eCollege DocSharing > Business Plan & Presentation BEFORE class.
  • Review the evaluation criteria in the Team Presentation Rubric.

Use the following outline as a guide as you prepare your Business Plan for your company in BU113.  For Writing and Presentation instructions, see the guidelines above.

  1. COVER PAGE
    • Name of the company, names of the management team, headquarters/office, phone number; include "Confidential" on the cover page.
  2. TABLE OF CONTENTS
    • List sections and page references.
  3. MISSION STATEMENT
    • Purpose of the business
    • How your company will impact or meet the needs of the various stakeholder groups: customers, investors, the company, and society
  4. MANAGEMENT STRUCTURE/BACKGROUND
    1. Organization Chart
    2. Management Background (for each member of the management team)
      • Experience
      • Key skills and abilities
      • Role in this business and why this person is suited for this role
  5. MARKETING PLAN
    1. Product/Service
      • Supplier of the product (if applicable)
      • Description of product and key characteristics
      • What makes it unique/competitive
    2. Target Market
      • Who will use this product/why
      • Specific demographics of target market
      • Economic characteristics
    3. Competitive Environment
      • Who are your competitors
      • Strengths and weaknesses of competition
    4. Price
      • Pricing strategy
      • Pricing strategy vs. competition
    5. Promotion
      • How will you develop demand for your product?
    6. Place
      • Where will you produce your product and how will you get it to the market?
  6. FINANCIAL PLAN
    1. Projected income statement by week
      • Sales:  Number of units of product/service estimated to be sold each week  X  price
        • Example: 10 roses per week  X  $2 per rose = $20 estimated sales per week
    2. Expenses per week
      • Cost of Goods Sold (COGS): Per unit costs  X  number of units of product/service estimated to be sold each week
        • Example: 10 roses per week  X  $.25 cost to the company per rose = $2.50 total COGS
      • Other expenses per week (e.g. advertising, taxes)
    3. Net income per week: Total Sales – Total Expenses per week
    4. Projected income statement for the period
      • Aggregate Sales – Aggregate Expenses during the 6 weeks of running your business
    5. Equipment list (if applicable)
    6. Capital needed to get the business started
      • Schedule (table) of how much capital is needed and for what
    7. Financing sources: Outline your initial financing plan.
      • How much money will be raised through the sale of stock?
        • How many shares of stock ($.25 per share) will be sold?
          • Teams must decide on the total number of shares to be offered to outside and management investors combined.
          • All teams must offer at least 20 shares to outside investors.
          • Teams must indicate in writing in the Business Plan and at the Marketplace what percentage (%) of the company one share represents (e.g. If a team has a total of 100 shares to be offered to outsiders and management, each share = 1% ownership.)
          • Management must actually spend the money to purchase all shares that they buy.
      • ATTACH THE FOLLOWING SHAREHOLDER INFORMATION TO THE BUSINESS PLAN:
        • List of shareholders: Name of each shareholder, contact information, and number of shares bought (including management shareholders)
        • Summary of the number of shares issued to management and the number of shares issues to outside shareholders
        • The total amount of equity capital that the sale of stock to management and outsiders raised. 
        • If you expect to raise additional capital during the course of running your business, estimate how much and indicate what sources of capital you will use. (e.g. loans, stock)
    8. Projected return on equity
      • Formula to be used: Estimated aggregate net income after taxes divided by $ raised from stock issued to management and outside shareholders
         
  7.  SUPPORTING DOCUMENTS
    • Completed up-to-date CAPITAL-INVENTORY-SALES-EXPENSES spreadsheet (capital, inventory, and loans/money borrowed areas filled in)
    • Revised resumes of each of the management team members
    • logo (and documentation of permission from SMC if using SMC official logo)
    • PowerPoint slides in handout form (6 slides per page)

This page was last updated: March 5, 2012

0 Replies to “Foundation Of Financial Planning Assignment”

Lascia un Commento

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *