Financial Analysis from A Simple Model's Users
Attention: Please exercise caution before accessing links included by ASM users in files available for download.
Presco Plc is a firm that produces Bleached Palm Oil in Nigeria, West Africa. Palm Oil is a highly profitable agricultural commodity with its use in producing Soap, Confectionary among other household items. The model has no Equity schedule since there is no planned share buyback. Additionally, the PPE (Depreciation schedule) has been reduced to just a lump of all assets (My first Model, please forgive me). Other than that, I think it's close to what should be a fully integrated 3 statement model.
...Read More
A quarterly model for Duolingo ("NAS: DUOL").
This model contains a quarterly model for DUOL. It has a five-year forecast from 12/31/2022 historical financials, transaction comps, trading comps, quarterly summary, annual summary, debt schedule, equity returns, and dynamic transaction section. I used this model for a hypothetical buyout. The transaction tab is dynamic to test the sensitivity of capital structures, term details, and closing dates.
...Read More
This model was created for a single-sponsor search fund to evaluate the feasibility and financial attractiveness of a two-part transaction involving a small business buyout (asset purchase) and commercial real estate purchase. As part of the deal, an operating company ("OpCo" or "NewCo") was created to acquire the assets of OldCo and a property company ("PropCo") was created to purchase the commercial real estate. OpCo and PropCo are the exclusive subsidiaries of a newly-formed holding company ("HoldCo"). The LBO model is modelled at the HoldCo level since the bank set the debt covenants at the holding company level. The model includes multiple pro forma operating and financing scenarios, allowing the user to fully evaluate the risk-reward profile of the deal across a full range of scenarios.
...Read More
ASM Challenge Case
...Read More
School of Investment Banking: Heineken DCF Model
I went through a DCF Model with instruction from the School of Investment Banking.
...Read More
School of Investment Banking: Mariott 3 Statement Financial Model
With the School of Investment Banking, I went through their template and worked through the 3 statement financial model while following their instruction for Mariott.
...Read More
Adventis 3 Statement Financial Model
I started the Adventis Bootcamp. In this first lesson, we went through a fully circular 3 financial statement model for their made-up company Adventis Air.
...Read More
Plum Sweets SRL - Integrated Financial Statements
The integrated financial statements of a start-up business that manufactures hand-made chocolates under "Plum" brand name in the Republic of Moldova.
...Read More
The model is build considering a few sales and cost data provided by the Owner based on sales made in person and at different exhibitions.
The scope of the model was to make a projection of the activity during 5 years time under three different growth scenarios.
Integrated Financial Statements with Extended PPE Schedule
Three Statement Integrated model Financial Statement with cash flow projections,scenario selectors and an extended PPE schedule incorporating sum of years digits accelerated depreciation and half year convention for asset depreciation
...Read More
Financial Model for Siemens AG
My attempt at creating a model for Siemens AG, its far from perfect and is very basic in nature. All constructive criticism welcomed. Thank you very much.
...Read More
[Working Progress] Exelixis Inc. Forecasting
- NOTE: work-in-progress
- Exelixis Inc. oncology-focused biotechnology company (https://www.exelixis.com/mission/)
- All data is publicly available
- Using regression analysis to predict changes in AR, inventory, and AP
Simple Dynamic Integrated Financial Model
- Model built for the integrated financial statement challenge 1
- Using 2-year trailing balance sheet and income statement data of a fictitious company
- Includes supporting PP&E and Debt Schedules
3-Statement LBO model with Cash Sweep - Paperchase 2017
First attempt of building up a 3-statement based LBO model with Cash Sweep
...Read More
Excel: Dynamic Column Reference Lookup
This attached Excel workbook contains a formula I use frequently to evaluate businesses that have multiple locations and a lot of data. The SUMIF(), INDEX() and MATCH() functions can be used together to dynamically call columns of information. The trick is to input a "0" in the INDEX() function for the value "row_num." This results in the function returning the entire column.
...Read More
Personal Budget (Basic Version)
Over the weekend a friend of mine asked for a simple template that would help them track their expenses. This template was built to work with information downloaded from bank statements and credit card statements.
...Read More
Preferred Stock Schedule (Vertical)
I generally build supporting schedules horizontally so that the accounting periods match the financials above, but lately I have needed a vertical preferred stock schedule on a couple occasions. I thought I would post one here for future reference.
...Read More
If you are wondering when this might be useful, lately I have used this when I only need summarized financial data from the company, but I need a greater level of accuracy from the supporting schedule. If you have a security that compounds on a quarterly basis, for example, then annual periods are less convenient to work with. By keeping the preferred stock schedule on a separate tab I can use lookup functions (or INDEX() + MATCH()) to pull the relevant data into a schedule with different accounting intervals.
If you are not familiar with lookup functions or using INDEX() + MATCH() to return a value please see the Excel for Models video series.
Income Statement Drivers (Monthly)
This workbook presents two approaches to projecting the income statement on a monthly basis. The objective is to create a template (IS Drivers) that makes it easy to manipulate the projected period. But because the data you are working with can change substantially from one project to the next, I have found it can be easier to keep these inputs and drivers on a separate worksheet.
...Read More
Excel: Lookup Formula With Multiple Criteria
I occasionally receive financial information about a company detailed by subsidiary, division or location. Frequently the format in which it is delivered displays the same date horizontally for each division, which means that you have multiple columns with the same date. I prefer to view the information rearranged so that each period only appears once.
...Read More
This can be done easily be creating a lookup formula with the =INDEX() function that uses to variables to identify the relevant information. For an example please download the associated worksheet.
Evaluating Revenue by Customer and SKU
A template that helps evaluate revenue by customer and SKU. This can be useful for any business with customer concentration. A consistently broader SKU base might suggest that the customer is less likely to find an alternative vendor with a similarly diversified offering.
...Read More
The tab titled "Top Customer Metrics" pulls from the tab titled "Raw Data." The final tab, titled "Breaking Down the Formula," explains how the Excel formula returning revenue by SKU works.
A recent issue of Grant's Interest Rate Observer motivated me to look at the effects of volatility on levered funds. I find the combination interesting in a market growing accustomed to a lack of volatility.
...Read More
Paraphrased from a recent investor letter: Since the VIX (Volatility Index) was introduced in 1990, the index has closed below 10 on only 16 days. Remarkably 7 of those days occurred since May 1, 2017. From 1990 to 2016 the average closing level of the VIX has been around 20; for 2017 year-to-date the average closing level has been around 12. [1]
The concern relates to investments structured in response to the current investing climate; leveraged investments benefit from low volatility (as the attached template demonstrates). In contrast, a spike in volatility can go so far as to reverse these returns.
Per Jim Grant: "Say that you own $100 of a thrice-leveraged ETF. On day one, the underlying index moves up by 5%; it's worth $115. Next day, the index falls by 5%; oops, your fund is now worth $97.75. Repeat across 10 days - alternating up 5% and down 5% - and you finish with $89.24, down 10.8%. Over the same course of choppy trading days, an unlevered fund would have lost only 1.2%. 'At higher ranges of volatility,' warns the summary pospectus for the Direxion Daily S&P Biotech Bull 3X Shares, 'there is a chance of a near complete loss of value in the Fund, even if the Index's return is flat." [2]
As the template attached shows, the appeal of these funds comes from the outsized returns generated by upward trends and low volatility. Changing the input in cell E10 from 1% to 5% demonstrates how returns are inflated.
The template has three examples:
- Comparison of a levered and unlevered fund with low volatility and upward movement.
- Comparison of a levered and unlevered fund mirroring the price movements described by Jim Grant above.
- The last example is similar to the second example, but demonstrates the outcome with actual leverage recalculated each trading day.
Footnotes:
[1] Paraphrased from an investor letter dated July 13, 2017
[2] Grant's Interest Rate Observer, May 19, 2017
Real Estate Distribution Waterfall (WIP)
This is a simple example of a real estate distribution waterfall. There are 5 IRR hurdles controlling how cash flows are split between the limited partners (LPs or investors) and the financial sponsor (GP).
This is not a complete model for a real estate property (search ASM website for "multifamily" for an example).
...Read More
100% of the cash flows go to the LP until the LP has received a sum equivalent to the original investment plus a preferred return (8% in the model). After that the LP and GP split proceeds according to the different hurdles. No catch up is included.
This is not a complete model for a real estate property (search ASM website for "multifamily" for an example).
This Excel file is an example of a schedule that would pull from a three statement model to evaluate a company's performance relative to debt covenants provided by lenders.
Subordinated Notes: This example debt schedule includes a toggle that permits a portion of the interest be paid in kind (Payment-in-Kind or PIK). This feature can be activated or deactivated for any monthly period with a simple "Y" / "N" input.