Welcome back. And I'm glad you've accepted to take on the challenge ahead. In this first lesson, we're going to talk about the importance of planning. The temptation of every analyst is to open up a spreadsheet and begin typing numbers into cells. This spells disaster from the outset. Before even beginning to forecast revenue, you need to step back and think about the macro structure of your model.

You need to think like a programmer, programmers just don't start writing code when they go to develop new applications. They work from a detailed document of specifications. This document includes all the important features, the user interfaces, and the back end database architecture. Whoa, holy geek, am I implying that we're going to turn this into a course on programming? No, no, I'm not. But I am going to ingrain into you some of the discipline that is required because as much as you might not recognize it at first, a financial model is an application, complete with databases, processing logic, user interfaces and fancy reports.

So let's start with a three tier architecture that every financial model should employ. At the bottom layer, you have your assumptions. These are the basic bits of raw numeric data that drive the model. Developing assumptions is something entirely separate from this course, but should not be taken for granted for us, we'll assume the assumptions provided had been rigorously developed. In the middle layer, you have a logic processing function, in other words, a whole bunch of formulas, and this can get complicated and fast. But this is also where errors are highly prone to arise.

And at the highest level, we have a reporting layer and this is the part of the model that you'll customize to meet the needs of your users. This will include the projections, the graphs, the sensitivity tables in the like. The first mistake that many Junior analysts make is that they try to combine all three of these layers together into a single cell or a single worksheet. And in doing so, mistakes happen, it's almost virtually guaranteed. errors can arise from the use of complicated formulas. errors can also arise when numeric data and formulas are combined.

Errors can also arise when there are hidden assumptions or processing calculations that are beyond the view of the reviewer or users. Don't do it don't think of doing it. If you've got a formula that wraps itself across multiple lines, or you have columns, rows or worksheets that are hidden or linked to other workbooks, detecting errors is nearly impossible. If you learn nothing else in this course, I want you to think about creating three separate areas in your workbook. These could be three separate areas of your worksheet as illustrated here, or three separate worksheets with the architecture out of the way. The second planning consideration is establishing a reporting structure and the key metrics.

Consider the following. Do you have more than one line of business? Will users want information on different lines of business? What level of detail is insightful for different line items? Can we group all the expenses together as selling general and administrative? Or is there a better more logical way to split expenses say by department or by fixed and variable behavior?

What metrics will be used to define performance? earnings per share return on equity, economic value added free cash flow and the like. And finally, what timeframe should we be using should information be forecast on a monthly, quarterly or annual basis? Taking a few hours To thoughtfully consider the parameters of the financial model can save you many multiples of those hours down the road. More importantly, it will preserve the integrity of your model and protect your financial credibility with those who matter. tinkering with a financial model, and its formulas after has been built greatly increases the risk of an unintended consequence.

For complicated models. I'll draw a map of the worksheets that make up the workbook. Sometimes I will refer to worksheets as tabs and the workbooks as the file. For instance, let's take a look at this schematic. This was a monthly budgeting and rolling forecasting model that I once built for a $400 million business that has seven product channels, one production facility and an administrative function. As you can see, this model had a lot of tabs.

However, every one of these tabs had the exact same formatting and formulas. They were all identically programmed. So the only difference Being that the assumptions for each channel came from different data sets. Also notice that I've used short intuitive labels for each worksheet, which help when you go to look at the cell contents in the formula bar. Nothing is more daunting than a formula that wraps itself across the top of the screen when you have a long descriptive name for each of the worksheets. From this model, I could prepare reports that compared actuals to budget forecasts, the budgets actual the prior year's forecast the prior years and any other variations that users thought relevant for the discussion.

One final point of this lesson returns to this idea of model integrity. And before you begin building your financial model, you should have a few protocols in mind to document your work. Many times your model will be reviewed by a supervisor or a client. You will want to make this review as easy as possible. And in doing so you'll often catch your own errors before someone more important does Let's discuss a few modeling protocols. Before we begin this course, it's a good idea to have a tab at the front of your model, documenting your protocols, and even including a map of the model or an index of the model as we just discussed, protocols will also include the use of color.

So for instance, you may want to shade cells or use colored fonts to indicate where numeric data has been included or where formulas have been overridden. Hard codes that is any raw numeric data should never be put directly into a formula. Hard codes need to be captured and aggregated at a place of their own. Because these are the assumptions that drive the model. This even includes things like dates and tax rates and inflation rates and interest rates and number of shares outstanding, even if it's unlikely that these values will ever change. document them separately, are related protocol is to only document assumptions once if it's a growth rate.

You want all formulas that use that growth rate. to point to one cell in one cell only to get this growth rate assumption, I have a general rule of thumb that has served me well throughout my career. And that is, keep the math simple. Excel comes with so much analytical power that it's tempting just to write compound formulas using beautifully impressive looking functions. I personally prefer simple addition, subtraction, multiplication and division. Not to say I don't use functions, but to do so carefully and cautiously.

Related to this is creating and labeling different columns and rows for each calculation. Sure, you could calculate revenue directly by multiplying price by volume by an inflation rate. But where's the harm in showing the volume on one line, and the unit price on a second and perhaps the inflation rate on the third and, and a total calculated revenue on the fourth, you'll be thankful that you have broken out calculations into simple math later on. In this lesson on planning Our financial model, we covered three really super important points. Number one, use a three tier architecture for every financial model, no matter how simple, raw data goes into one, place formulas into another, and if necessary reports in a third. Secondly, plan your model, draw a picture of it if you have to.

If you're doing a lot of modeling, you will find that portions of a model can be templated and reused. This is highly desirable. And I've done this once creating a valuation model that allowed me to value any potential business opportunity very quickly and very thoroughly. But it's impossible to do if you're just throwing a bunch of numbers into a spreadsheet each and every time. Thirdly, document your protocols. Make your financial model easy for someone to follow.

In doing so you're not only helping yourself, if you put your model down for a few weeks and come back to it, you will begin to wonder yourself where the numbers came from, where did they go and how were they calculated. So I'm sorry to disappoint. Once you Here we are at the end of our first lesson and we've yet to begin any real financial modeling. But this stuff is so important. It needs to be first. In our next lesson, I promise we'll get our hands dirty.

Until then.