Financial Projections
The following are a few financial models. Some have been stand alone models and others have been integrated into business plans, etc. Please click on the respective colored buttons.
Real Estate - Los Angeles Developer - Investor Returns
problem: create easy-to-use IRR scenario model (from separate cash flow model) for investor into 5-Year Working Capital Notes sold by developer.
Run two scenarios:
-
(Case 1) All money invested in Year 1
-
(Case 2) Money invested over Years 1-3
Final kicker will required extending the holding period by 1 year (thus, for Case 1, it is a 6 Year IRR calculation; and, for Case 2, an 8-Year IRR).
Create a single flexible model.
solution: the developer was offering a fixed interest rate on 5 year notes with a kicker (additional interest) to the investor, based on the profits earned on individual development projects.
The model has key input areas highlighted in yellow. These estimate 'number of projects (per year)' and the value of the projects based on a given project module ($ 1 milion).
Problem: These projections were part of a business plan for a Real Estate Developer Startup - wanting 3 separate, related funding vehicles.
This was a startup developer that wanted (1) initial capital (identified as a Seed Round) of $250,000; (2) working capital (with a $5 million Working Capital Fund); and, (3) individual Reg. D project funds.
Solution:
The Seed Fund investor was offered a convertible 5 year note with significant preferences in terms of tiered profit shares.
The Working Capital Fund offered investors a fixed interest rate with a profit participation based on number of projects per year and related turnovers. A basic modular base of a $1 million Project was created to provide profits to the developer to be allocated to Working Capital and Seed Fund investors.
The Project model assumed that it would be initially funded by the Working Capital Fund, which would be repaid by Equity investors into the individual Projects of the developer.
Real Estate - New York City Investor
problem: A NYC investor was looking to determine the rent required if the investor bought and refurbished apartments that might be described as 'fixers'. The goal was to have a simple and easy-to-use template.
solution: The investor model with the output being rent required. In the spreadsheet, there were many variable direct inputs (shown in blue) that could be entered both as fixed monthly figures and as percentage factors.
problem: The above NYC investor wanted to look at the expected returns from an investment into a specific property with significantly expanded variable inputs. Key variables included: all cash vs. some debt (variable leverage and mortgage rates); rent growth rates; vacancy factor; condo fees and growth rates; reserves (as % of rent received); other costs input value and growth rate; and, a separate market appreciation rate (separate from rental income).
solution: The expanded model went from 1 sheet to 3 sheets and was extended out to 30 years (annualized) with financing and variable appreciation rates.
Real Estate - Philadelphia Developer
problem: As part of a business plan, there was a need to provide a consistent set of projections and spreadsheets in a situation in which the developer provided their own financial projections for developments.
solution: New Excel spreadsheets were created using the developers standard metrics. These were designed to allow variable inputs for investor return sensitivity analyses for two key projects.
Business Startup: Hospitality - Looking for Investors
Problem: Hospitality Business Financial Projections + Investor Options
The client was building a sustainable-living holiday camp and wanted a cash flow projection including two build-out stages. The client then wanted some options, which could be presented to investors. The total investment was $ 1,450,000.
Solution: There was a basic set of projected business cash flows. The goal was to illustrate how these cash flows could be returned to investors, based on differing investor goals as identified below. Along with the following 4 scenarios, BBA offered to do additional projections for specific investors.
Scenario 1: FAST CASH BACK - Rapid refinance of investor cash with bank loan _ 14% IRR.
Scenario 2: 7 YEAR INVESTMENT - Let investor share in profits with cash-out in year 7 _ 15% IRR.
Scenario 3: MORE CASH - Amortization of investor capital over time (mortgage like) with a lower total return _ 9% IRR.
Scenario 4: EB 5 PROGRAM - 7 % bond coupon, 5% profit share _ 10% IRR.
Business Startup - Agri-Business / Renewable Energy
Problem: The business goal was to develop a series of biomass (bamboo) plantations to supply biomass to torrefaction -based, bio-coal production plants in Southern Africa for a company, Renewable Energy Solutions (RES).
It was necessary to build both a biomass plantation model (with a 5-year planting plan with plants maturing over a period of 7 years to reach maximum output).
There were financing issues with funding over the establishment period (5 years) and maximum output royalties from year 12.
It was also necessary to be able to run scenarios based on variables such as production volumes / plant sale pricing to farmers / royalty rates to investors / exchange rates / discount rates
Solution: This set of models was one-part of a two-stage development plan in which the actual manufacturing (torrefaction) plants were to be funded by a separate Swiss investment fund looking to invest into such production facilities. The operating expense figures (also variable) came from the technology provider, a Dutch company Topell.
These model shots do not show the extensive underlying calculation rows that are hidden in the presentation sheets shown. The multiple currencies involved show up in the inputs table.
This model was for a long-term investor looking for significant future cash flows and a long-term hold. The model involves a 5-7 year staged investment and plantation growth stage (ending in year 12 where all plants are at a stabilized annual yield). In order to generate an IRR, based on a terminal value, a future sale scenario was plugged in at an estimated cap rate on the cash flows.
There was extensive interest in this investment and business opportunity by a major European electric utility but the technology component of this project (torrefaction) was unable to be commercialized (with an investment write-off of almost 100 million euros).
Business Startup - Renewable Energy Tech Company
problem: In relation to the above project for the torrefaction of municipal solid waste (MSW) to provide power plant fuel, the Investment Coordinator (Maxipar) wanted to take a look at its jointly owned operating company's (EBC-E) income, which it would receive over time from the sale of long-term MSW waste conversion plants.
Each sale would involve an initial 'pilot' plant, which would be needed to tailor the design of the production plant for each location's MSW profile mix. Then the production plant would be built.
There were 2 income streams for EBC-E: (1) from the pilot plant's sale and operation (ongoin); and, (2) from the production plant. A basic module was needed for the income from each.
The client, which was EB Clean Energy - Europe (EBC-E), wanted to look at cash flows based on variable sales of MSW plants.
solution: A 7-year model was built. At this time no IRR or cash-on-cash or payback analysis was done because EBC-E had no investment. Maxipar had investors who needed the technology and would separately fund all costs for each plant.
As such, each investor had their own accounting and projections that were outside of EBC-E. This was just a very profitable opportunity for EBC-E, which was jointly owned by Maxipar and the technology provider EB Clean Energy.
Real Estate: Construction - Development - Investor Return Models
Artz Plaza was a foreclosed, partially built mixed-use building complex. Among the key issues were a determination of both the costs to complete (both developer and construction) and future value.
Numerous other materials and models are available on this project, please ask for information.
Business Start-Up: Tech Company Investor
problem: There were several significant issues for an investor in Brazil that wanted to build a municipal solid waste (MSW) torrefaction plant using a US / Israel technology. Also, what would be the future income override (royalty) stream to the technology provider.
These projections were part of an investor presentation.
(1) before a commercial plant could be built, a pilot plant would have to be built ($ 15 million cost).
(2) once the pilot plant tweaked the technology for the specific MSW fuel source, a commercial plant would be built ($ 900 million cost).
(3) based on operations, what would the ongoing income stream be to the technology provider?
solution: A simple 6-year spreadsheet was created with input variables.
General - How Models Are Designed
BASIC DESIGN APPROACH
There are many ways in which to build financial models.
In iterative models, it is important to have both a relevant input area and also to know where calculations have new and dependent formulas. After many years of experience with MS Excel, Bruce Birkett Associates has found the linked description to be a good way in which to build spreadsheet models for clients.
I'm a paragraph. Click here to add your own text and edit me. It's easy.