Welcome back. In this lesson, we're going to get the financial model set up. To begin with, you can open up the start file that has been provided with this course, I have used my great imaginative powers to conjure up an example that I will refer to throughout the course as model company. This is a company that manufactures and sells doohickeys. The wholesale price is approximately $300 per unit, and that company has been selling approximately a quarter million of these units in its most recent year. The Excel file provided to you contains three tabs.

The first tab is the historical financial statements for 2000 x one and 2000. Next to the second tab is an operating budget for 2000 x three and the third tab is the capital asset schedule for 2000. Next one 2000 x two as well as the budgeted numbers for 2000 and x three. The executive team is meeting next week to work on the five year strategic plan, and to help them prepare for that meeting, you have been asked to develop a financial model of the business. The current five year assumptions are as shown on your screen. Use the 2000 x three for the first year for the years following assume an annual growth rate of 2%, a cost and gross profit percentage of 21% and an overhead cost escalation factor of 3%.

The corporate assumptions include an effective income tax rate for the company of 40%. The interest rates are expected to be constant and the line of credit is a floating rate of prime plus 1%. And the current prime rate is 5%. The interest rate on the long term debt is fixed at 9%. And the annual repayments of $5 million are required for each year until maturity in 2018. Working Capital policies are set to achieve an inventory turnover of six times.

Receivables are collected on average in 45 days, and disbursements for manufacturing costs are made on a 20 day average. capital assets are depreciated using a 50 year straight line basis for the building in a 10% declining balance basis for the equipment. Sustaining capital expenditures are estimated $2 million per year split $100,000 for the building and $1.9 million to the equipment. We're going to assume all this information has been fully vetted. Typically, a financial analysts will spend considerable time developing assumptions using various analytical and research techniques. This is beyond the scope of this course.

In this lesson, we're going to set up the structure of our model. In other words, establish the rows and the columns in the spreadsheet. The decisions we need to make at the outset are first what should be our reporting for At, practically speaking, determining the labels for each row, and then secondly, determining what should be the forecasting periods, which is another way of saying how should we label our columns. Let's begin with the reporting format. When we talk about the reporting format, we are talking about row descriptors. It's extremely important that each row have a descriptor, and there are a lot of choices we can use.

We're going to make the upfront assumption that our financial model will contain an income statement, balance sheet and cash flow statement. These three components make for a robust perspective of the entity. This level of depth is not always warranted, but it should always be considered. I'm emphasizing this point because in practice, I often find budget models or financial forecasts that ignore the balance sheet or cash flow impact of various planning scenarios. By using your standard financial statement presentation format, you're able to calculate more performance indicators and To better understand the full impact of any particular set of assumptions. The next reporting format issue is how much detail and it's entirely up to you and your users as to what level of detail is relevant.

You could include every account in your chart of accounts, though most of the time that is far too much. A budget model for managerial approval will likely present sales by product line or customer segment, and expenses broken down by department. The same budget model for executive approval may only have the expenses broken out by business unit, and then the model rolled up into an entity level model, they consolidate all the sales and expenses across the business units. Each user of the financial model will consume the information differently and look at different metrics. The manager will want to understand how each line item has been determined. They will be focused on cost containment and operational metrics.

The executive team will be looking at each line to business to evaluate how resources are deployed in the strategy execution. These measurements will focus on relative returns and return on investment. And the board of directors will consume information and even at a higher level and look at the overall shareholder returns and the earnings metrics of the consolidated entity. Practically speaking, I look at the existing reports that are used by my target audience. If I'm building a financial model for a manager, there's likely a financial report that is currently being generated by the system that the managers already comfortable using to control the business using a similar format and your financial model then becomes a natural continuation of the existing management report. In our situation, we're preparing a financial model to support a strategic planning process.

This is high level analysis that goes over a number of years in this situation, using our external financial statements as the reporting format makes a lot of sense. Most investment bankers and perfect valuators will prepare pro forma statements using this as their reporting format to support financings mergers and acquisitions, public offerings and the like. So today we're going to do the same. Let's set up a new workbook and call it pro forma. Because we have our financial statements in Excel, it's easy, just copy them over to the new workbook as a starting point. do that now.

Obviously, if you're working from a paper copy or a PDF, then you will need to retype all this information. The line items I've included here are the minimum number of line items required to perform most types of financial analysis, including ratio analysis, so it's not a bad template to use. If you're preparing pro forma financial statements, on the income statement, you can calculate gross profit margin operating margin even a margin on the balance sheet you can calculate working capital liquidity ratio financial leverage ratios. And using information from the income statement you can calculate efficiency and return ratios. And the cash flow statement allows you to calculate operating cash flows, funds from operations, free cash flows and distributable cash flows. Adding more lines to any one of these statements is perfectly acceptable.

In later lessons, we will add additional sub schedules to support or calculate various line items presented here. Our second issue was to determine the forecasting periods. That is to say should we forecast information on a monthly, quarterly or annual basis, and once again, how we envision users interacting with the model will guide us in this decision. Consider the following examples. operating budgets and cash budgets are typically prepared on a monthly basis. Rolling forecasts and earning guidance are typically prepared on a quarterly basis.

Pro formas and projected financial statements which are used to support a variety of analysis are typically prepared on an annual basis, one important advantage of preparing monthly or quarterly projections is that seasonality is captured. This helps in forecasting cash balances working capital build up cash flow excesses or shortages. monthly and quarterly information can easily be aggregated later for presentation purposes on an annual basis if need be. So it's important to consider all the uses of your financial model at the outset, an ounce of planning saves a pound of headache in my experience. For our strategic planning purposes, annual projections are suitably sufficient. One final point about our forecast periods which form the columns of our spreadsheet has to do with historical information.

If it's available, I suggest including it for a couple of reasons. First, it will help you in developing your financial model. You will often use ratio analysis as we'll see in later lessons to validate your assumptions and reconcile future performance against past performance. Having all the numbers inside one Excel file will help you do this. Secondly, it's common for users to want to compare projected information against historical performance, whether it's budget versus prior year actuals, or a trend of certain line items year over year. Having this information inside your model, whether it's visible or not to the user is helpful.

Because we copied our historical financials to start our pro forma, column D, and E already contain our comparative results, how handy in the real world you realize you'll probably have to jig with this a little to get it into the worksheet quite so cleanly. Let's return to an idea that we talked about in our last lesson around organizing our worksheets in our workbooks. This is probably a good time to set up a separate area of the worksheet to hold all of our assumptions. And let's put these below our projected financial statements say starting on row 104. Now I've split my screen by changing my view, which you can find in the view section of your Excel ribbon, so that the calculations are on the top of my screen and the assumptions are on the bottom. These assumptions could just as easily be on a separate worksheet.

Though I suggest not putting them at the top of the worksheet, for reasons I'll discuss when we get to building multi divisional models. The point is, is that they're in a separate area from the calculation so that our assumptions remain clearly visible. To begin with, let's set up our dates and column headers as assumptions. This may seem excessive, but if we want to be religious with our no hard code policy, then this is entirely appropriate. Notice that I'm carefully standardizing the columns where I put the assumption information. everything to do with 2000 x three will show in column f keeping this level of discipline Makes your financial model that much easier to review at the end, standardized, standardized and standardized again, to every extent possible.

Now, let's copy and format our dates in period headers across all five years. And repeat this for both the balance sheet and the cash flow statement so that you have your years and labels across the top of each one of those statements. We are now ready to begin preparing our projections, which will be the topic of our next lesson. In this lesson, we covered off three really important lessons about setting up the financial model. First of all, consider the users and the uses of the financial model to determine the level of reporting detail in the accounts presented. Secondly, set up your rows to be descriptive and detailed.

Now you can summarize multiple line items and simplify the presentation later on. And finally, consider the time periods if users should be considering seasonality and volatility throughout the year, then monthly or quarterly columns may be better suited for your financial model. By this point in the course, you're getting the idea I'm really a bit of a fanatic when it comes to thoughtfully and carefully setting up the financial model. This comes from two decades of learning by doing sometimes the hard way. We will begin modeling the income statement in our next lesson. So we'll tell them