Monthly Archives: December 2017

Startup Financial Models

To understand the need and importance of startup financial models, we first need to know what exactly it is that the financial sector involves itself in. Financial institutions, such as investment companies, banks and security firms handle the flow of cash, the amount of which can often be of such a nature as to make it unpredictable. In some cases this amount is dependent on certain future conditions, as in equity or bonds. This makes the very nature of financial transactions uncertain and unstable.

Choosing the right model

A particular context or decision should go in the actual realization of practical startup financial models. This decision or context depends on the horizon within which it has to be located. Many businesses and other financial activities require a limited horizon, while others operate within horizons that may stretch for weeks, months and maybe even years. Models that have been designed to work with continually and frequently changing data and processes would not be applicable in instances which are gradual and remain static for longer periods of time. Hence the need to choose the right type of financial model is a crucial one.

Which models are good?

While the large number of uncontrollable factors make it difficult for most startup financial models to work as focused tools for predictions. However, these models can be used for various other purposes such as risk and profit assessment, projecting the values of assumptions that are made based on existing market conditions, calculating the margins that are needed to avoid adverse situations, and various forms of sensitivity analysis. These are necessary to regulate minimum capital investment, capital allocation and measuring performance.

The best place to start a financial model from, is a profound understanding of the case that requires this model. The approach is partially fulfilled by those startup financial models that contain parts of market behavior, but reality can often be vastly different from the theory that is to be followed. To understand fully the nature of all the forms of risk that the business might be exposed to, the financial model should clearly reveal the possible areas of dependencies. These dependencies can be seen between different kinds of activities and between consecutive time periods. It is also helpful if the model explains the relationship between asset types and types of business. In such a case, the way that the two sides of a balance sheet interact would be shown up clearly.

Some startup financial models

There are a number of startup financial models available for various businesses and the particular situations that they encounter. Some of the more general financial models are, comparative financial analysis, cash flow forecasting and business plan models. These models look to setting up the best methods of controlling the cash that comes in and goes out. However, these are only very few of the models that are available and there are a number of business specific startup financial models in the market to choose from.

Financial Modeling For Individuals

Most people concerned with their financial well being will maintain a budget and keep track of how their investments are performing. People desiring to be more sophisticated in their financial planning might even hire a financial planner to help them target specific goals and develop an efficient way to achieve those goals. However, most people stop short of modeling their financial future. This articles touches on the meaning of financial modeling, its merits, ways to implement it and things to watch out for.

Financial modeling is the projection of a set of financial figures to some future point based on a set of assumptions. One might create a base model with assumptions that he or she believes to be the most probable and then vary certain assumptions to see what the outcome would be. This is done by every major company and is even required for many types of companies that fall under regulatory scrutiny. However, individuals rarely have used this valuable tool for their own financial well being.

The benefits to individuals of financial modeling in this author’s opinion are tremendous. Merely in order to build a financial model, an individual must have a grasp of several important items. One is having a good understanding of their current financial situation. Assumptions for financial models, such as expense levels, are often developed based on a person’s financial history. The base step for a financial model is for a person to have at least a rough plan for the actions they might take during the model period that would affect them financially. Each of the items is a valuable tool that would help an individual before a financial model is even started.

Once the financial model is created and a base model using the most likely assumptions is established, a person should have a clear picture of where they are going. A person might be pleasantly surprised or this might be a rude wake up call. Either way, the person is better off having the knowledge available to them.

The most valuable part of a financial model is its application to analyzing risk. Have you ever asked yourself what would happen if you retired early? Would your savings carry you? What if you lost your job? How long could you survive without your main source of income and what expenses would you need to eliminate or reduce? How would a major purchase such as a vacation home or a recreational vehicle affect your financial position? You may know that your current budget has room for the loan payments, but may the purchase derail your retirement plans? Financial modeling helps you answer the question, “What if?” By adjusting your assumptions to reflect different possibilities such as the ones I have mentioned here, you can identify and understand the risks to your financial future and even test how effective plans to mitigate those risks are.

There are several ways to build a financial model. If you have the skills to do so, you can build one yourself with a spreadsheet application such as Microsoft Excel. Many people are not that comfortable with financial mathematics. For those of you that fall in the latter category, there are still several options available. There is software available on the market at varied costs and levels of sophistication. Financial planners often offer this service for free in order to up-sell their other services. There are also consultants that will build a financial model for you. There are, of course, plusses and minuses with each of these methods.

If you chose to build your own, you will need to verify the output. Good techniques include entering only portions of your finances with known outcomes to test the results, testing extreme assumptions to make sure the output makes sense under those conditions and having someone with the appropriate background peer review your work.

If you chose to use a financial planner, you should be aware that his or her analysis may be geared more to up-selling their products than to giving you a detailed financial analysis. Also, beware of the software they use. It is unlikely the person putting your analysis together is the one who created the software. Therefore, the program might be a black box to the financial planner. This is often the cause for errors due to not understanding the computations that the program uses to perform the analysis.

The software option also presents this black box issue. If you choose to use software, you should make sure you have a good understanding of how the software works and what each input you enter means. Buying a software package that is user friendly, but still sophisticated in the calculations behind the seen is the best way to go.

Using a consultant is probably the best route. This is especially true if the consultant created proprietary software to create the model. A consultant can provide you sophisticated results displayed in an easy to understand manner. The consultant can explain what assumptions were made and any simplifications that were made in building the model. The consultant is also not going to try to up-sell you on other products. The main draw back to using a consultant is cost. Specialized attention to you as a customer is often on the expensive side. Also, the consultant has no other products other than his services to use to offset his charges.

Financial Modeling: Functions to Up Your Game

Financial modeling is truly an art form. Great writers draw on a broad vocabulary to find the right word to communicate their ideas. A good financial modeler should be proficient in using a variety of functions so that he or she can closely mirror the behavior of a company’s financial statements in a financial model. Let’s take a look at a few functions that every financial modeler should know.

The vast majority of financial modeling can be done with your basic arithmetic operators (+ – x /), but there is also a significant amount of business logic that cannot be easily illustrated without incorporating other functions.

The IF function

Let’s say, for example, that we’ve modeled out an income statement, but we want to add a dividend payment. If we don’t have enough net income available to pay out a dividend (and don’t want to draw funds from our retained earnings), we don’t want to pay a dividend. If we do have enough net income on hand, we would like to pay a $0.10 per share dividend to investors.

This is a perfect place to use Excel’s IF function. The IF function evaluates a certain condition and returns one value if the condition is true and another value if the condition is false. In our case, the function would read as follows:

=IF(net income < dividend payment x # of shares, 0, dividend payment x # of shares)

Specifically, “net income” would reference the cell where net income is calculated. The “dividend payment” would reference a cell that contains the value of the dividend payment, and the “# of shares” would reference a cell the contains the number of shares outstanding.

The “0″ input value is the value that is returned if the condition is true. That is, if net income is less than the total amount of dividends that are to be paid, then we won’t pay out a dividend.

Finally, if the statement is not true, and there is enough net income to pay a dividend, we will return the amount of the dividend to be paid (a cell referencing the dividend payment amount multiplied by a cell referencing the number of outstanding shares).

This is just one example of how the IF function might be used in a financial model. This function can be critical for any model that involves conditional logic and is used quite often.

The VLOOKUP and HLOOKUP functions

Now let’s say we’ve created a financial model in which we have a number off assumptions, and we want to create several different scenarios that involve different assumptions. Rather than changing the assumptions each time we want to look at a different scenario, we can create a table of assumptions that include all of our scenarios.

The first column of the table would contain the scenario – scenario 1, scenario 2, etc. The next columns would contain each assumption for the model along with the corresponding values that should be used in each scenario.

To pull these values into our model, we’ll use the VLOOKUP function. Depending on the orientation of your data, you can use the VLOOKUP or HLOOKUP functions to grab data from a table based on a value from the first row or column of the table. Since we put the scenario in the first column of our data, we’ll use the VLOOKUP function because the function will use the vertical orientation of our data to lookup values.

First, we’ll create an assumption cell that will contain our scenario. In this cell, we’ll put “scenario 1″ as a placeholder. Next we’ll put the following function of in the assumption cell of each of our assumptions:

=VLOOKUP(scenario, assumption table range, column number of assumption, FALSE)

“Scenario” will refer to the scenario assumption cell we just created. By changing the value in this cell, we will now be able to change the all values for each of our scenarios at once. The “assumption table range” will be a reference to all the cells contained in the assumptions table we created. This will tell the function where to lookup our values.

The “column number” refers to the column of the table that contains the assumption that we want. For example, if you want to pull the assumption from the column right after the scenario column, you would put 2 because it is the second column. The “FALSE” value in the last input for the function refers to the fact that we want an exact match of the value from our scenario assumption cell and the value in the scenario column of our table.

Now we can change the value in our scenario cell and all of our assumptions will automatically update to that scenario. This is just one use of the VLOOKUP and HLOOKUP functions, but it illustrates the concept behind them.

The ability to quickly reference a specific value in a table brings the power of a small database into a spreadsheet and can result in significant efficiencies for financial modeling.

The INDIRECT function

Another function that really opens up doors in financial models and helps to sort data in spreadsheets in general is the INDIRECT function. The INDIRECT function uses values within cells to construct a cell reference. For example, if cell A1 contains the value “B5,” and the cell B5 contains the value “$100,” then the function INDIRECT(A1) would result in the value $100.

At first blush, this function might seem inconsequentially. Why not just reference cell B5 directly? But what if we need to pull values from different worksheets within a model?

Let’s assume we have a model that projects a companies future income statements based on its latest income statement. We want to pull the values from the actual income statement on a separate worksheet tab and use them in our model to forecast new statements.

Each time the company puts out a new financial statement, we would have to copy and paste the new values into our model. To avoid this tedious process, we can use the INDIRECT function to grab the values off the new financial statement just by adding the new statement as a worksheet in our model and changing the value in a single cell.

Here’s how it can work. Create one input cell that would reference your latest financial statement and put “2010″ in the cell as a placeholder. For the 2010, financial statement worksheet, make sure the tab is named “2010″ (note that using spaces in worksheet names will require you to use single quotes around the name of the worksheet in a formula reference).

Then for each value in our model where we need to reference a value on the actual financial statement, we use the INDIRECT function. Let’s say that the revenue value is contained in cell D8 on the 2010 financial statement worksheet and our input cell for that worksheet is in A5. We would write the following function:


Excel will interpret this function as a reference that looks like this:


This will pull the revenue value on the “2010″ worksheet from cell D8. We would use a similar function for all the remaining values that we need to pull, replacing “D8″ with the appropriate cell reference on the financial statement worksheet.

Assuming that all financial statements for the company follow the same format, we can now easily update the model for the 2011 financials. We simply copy them into a new worksheet in our model and rename the worksheet “2011.” We then change our input cell to “2011,” and the INDIRECT function will now pull all the values from the “2011″ worksheet instead of the “2010.”