How to Build a Financial Model in Excel Transcript

This is the transcript from our popular building a financial model in Excel course at the Corporate Finance Institute

Building a Financial Model

Part 1 (video coures)

Content slide 1 – Session objectives

Welcome to building a financial model – part 1

By the end of this session you will be able to:

–            Apply a structured approach to financial modeling in Excel

–            Outline the hallmarks of good financial models

–            Explain the importance of planning your model structure

–            Forecast an income statement from operating revenues down to operating profit.

Content slide 2 – Key structure for model building

Let’s start with the importance of applying a structure approach to model building.

If you break apart a robust financial model, you will typically see the same core building blocks.

Robust models clearly separate inputs, from processing and outputs.

Your financial models should have inputs clearly identified and inputs should only ever be entered once.

The processing of inputs into outputs should be transparent and broken down into steps that are easy to follow.

Outputs should be organized so that users can quickly access the outputs they wish to see or use.

In this session we will explore each of these fundamental building blocks in more detail.

Content slide 3 – Modeling best practice

Before we examine the building blocks, it’s important to stress that model building is not an iterative process.  Models built on the fly typically contain errors.

In order to minimize errors when building your financial models, consider the following five steps:

First – clarify the business problem.  What problem is this financial model meant to solve?  Who are the end users?  What do users need to be able to with this model?

Secondly – think about all inputs and outputs needed.  Try and keep the model as simple as possible. Ask yourself what are the minimum number of inputs and outputs that we can get away with and still have a useful model?

Thirdly – plan your model structure including how the inputs, processing and outputs will be laid out in your model.  Try and keep your inputs all in once place if possible.

Fourthly – once you’ve created your model, consider Excel tools that will help protect the integrity of your model data such as “Data Validation” and “Conditional Formatting.”

Finally – Consider using test data to check that your model works as expected.

Content slide 4 – Inherent tensions in model building

The second point we referred to on the previous screen related to simplicity. Think about a financial model that you have recently built or used.

How large is the model?  Is it less than 1MB or more than 10MB?

There is an inherent tension in model building between making your model realistic and keeping it simple and robust.

Large models may appear to offer users a high degree of detail and precision but by their very nature, are harder to follow and audit.

Small models are generally easier to follow and audit yet lack the degree of precision necessary for decision making.

The best financial models work to reconcile these opposing forces, keeping things as simple as possible while still providing sufficient detail for decision making.

Content slide 5 – Model inputs

Now let’s look at those financial modeling building blocks – the inputs, processing, and outputs – in more detail.

When building the “input” section of your financial model, you’ll need to consider how your model ensures that:

Inputs are accurate

Inputs remain within reasonable data ranges

And that inputs are easy to use, understand, and update.

Your model should be structured so that data is only ever entered once.

Your inputs also should be clearly highlighted with colour so that they are easily identified.  Yellow shading or blue font colour are often used for model inputs.

The models you will see in this course always have inputs shaded in yellow.

Finally, consider using Excel tools such as data validation, conditional formatting, and comments to help you maintain the integrity of your model inputs.

Content slide 6 – Using data validation

Let’s look at how Excel’s data validation tool can be used to enhance the integrity of your financial models.

In Excel, “Data Validation” is found on the Data ribbon > Data validation.

That “Data Validation” window has three tabs – “Settings”, “Input Message”, and “Error Alert”.  In this screen shot, the “Gross Margin” input range has been set to between 0 and 90 percent.

Although you cannot see the “Error Alert” tab, it has been set to give the user a warning message if Gross Margins are set outside this prescribed range.

Content slide 7 – Using data validation to find invalid data

If you put data validation on your model inputs, you can check if a user has set inputs to numbers outside the preset data validation ranges.

To circle invalid data, go to Data ribbon > Data validation > Circle invalid data.

Content slide 8 – Data validation demonstration

Okay, let’s see data validation first hand in Excel.

For this demonstration and throughout this course we use Excel 2016 so I will find “Data Validation” on the Data ribbon.

Before selecting “Data Validation, use your cursor to select the cell that has the Gross margin input.

I am going to set Gross Margins to between zero and 90 percent.

On the settings tab I will set the validation criteria to allow a decimal.  I will then set the Data box to “between”, the Minimum to zero, and the Maximum to 0.9.

On the “Input Message” tab, I will set the “Title” to “Please Note”, and the “Input Message” to: “Please input your assumption for gross margins.  Historically, gross margins are between zero and 90 percent.”

On the “Error Alert” tab, I will set the “Style” to warning, the “Title” to “Warning”, and the “Error Message” to “You have input a gross margin assumption that is not between zero and 90 percent.”

I the click “OK” to finish.

I am now going to type in 95% into the gross margin input to ensure that the error message is working correctly.

When the error message appears, I am going to click “Yes” to continue.

I can now use the “Circle Invalid Data” tool to find the input that is set outside my prescribed ranges.  In Excel 2007 this tool is found right next to Data Validation on the Data ribbon.

In Excel 2003, you will need to make the Formula Auditing toolbar visible.  Instructions are on an earlier slide.

Content slide 9 – Data validation exercise

Now it’s your turn.  Click on the attachment link entitled “Data Validation Exercise” with instructions.  Once you’ve had a go, check your attempt with the attached “Data Validation Solution.”  Good Luck!

Content slide 10 – Model processing

Now that we’ve covered inputs, let’s consider how to translate inputs into outputs.  Do you try to put all your processing calculations into as few cells as possible?  Do you hide your processing cells or worksheets?

Hiding calculation cells or putting too many calculations into a cell makes your financial model harder to maintain and audit.

Ideally you want your processing calculations to be easy to maintain, structured to ensure accurate processing, and above all else, transparent so that users understand how the model works.

Where possible break down complex calculations into several steps.

Use comments to explain how your model works.

Use formatting to ensure that formulas are not accidentally overtyped.

Finally, calculate final figures on your processing worksheets and then link these figures to your final output sheets.  This will make your model easier to follow and audit.

Content slide 11 – Model outputs

We’ve covered inputs and processing. Now let’s spend some time on outputs. Financial model outputs include things like balance sheet forecasts, cash flow forecasts, DCF valuations, and so on.

Ideally you want to make sure your output cells or worksheets are easy to understand and unambiguous.

Consider making your outputs modular so that the end user can chose which outputs he or she wants to review.

For example, put your income statement forecast, balance sheet forecast, and cash flow forecast on separate worksheets rather than all on the same worksheet.

Consider creating a summary output sheet that allows users to review the key model outputs without having to go through the entire model.

Use colour to inform the user that your output cells are formulas and should not be touched.

Finally, consider protecting your output cells and worksheets in order to maintain model integrity.

Content slide 12 – Locking/unlocking cells

Let’s talk about how to protect cells and worksheets.

The first thing you should know is that the default setting in Excel is for individual cells to be locked.

When a worksheet is protected, this default setting means that all cells will be locked down.

Although your model’s output cells should be locked, input cells need to be unlocked before protecting a worksheet.

The Excel shortcut to unlock cells (as well as format cells) is Ctrl + 1.

The “Format Cells” window has a separate “Protection” tab where you can lock and unlock individual cells.

Content slide 13 – Protecting a worksheet

Once you’ve unlocked your input cells, you then protect your worksheet by going to the Review ribbon > Protect sheet.

Content slide 14 – Protecting data demonstration

Now that we’ve explained the context, let’s demonstrate how locking and unlocking cells and protecting worksheets actually work in Excel. The shortcut for formatting cells is (Ctrl +1)

First I am going to highlight the cells I need to unlock.  These are the input cells highlighted in yellow.

I am then going to Hit Ctrl +1 to access the “Format Cells” window.

I need to toggle to the “Protection” tab to ensure that the “Locked” box is not selected.

Once I have de-selected the “Locked” box I hit OK to return to my spreadsheet.

I then need to lock down the worksheet.

I go to the “Review” ribbon, and then click on “Protect Sheet”.

I am going to leave all the default setting and am not going to use a password.  Passwords are optional.

When I click OK I should find that all cells are locked except for the three input cells that I have unlocked.

Content slide 15 – Protecting data exercise

It’s your turn again.

Click on the attachment link entitled “Protecting Data Exercise” with instructions.

Once you’ve had a go, check your attempt with the attached “Protecting Data Solution”.

Content Slide 16 – Grouping Cells

Let’s talk about how to use the grouping function in Excel to create “sections” in the worksheet.

This is a handy way to make the model easily expandable and contactable, like an accordion effect

This makes the model extremely easy to audit

It’s very handy for large businesses with multiple operating divisions, as each division can be on one worksheet

It also makes your model look super professional

Steps:

Select the rows you want to group, leaving the “title” row at the top, unselected

On the data ribbon select “group” and then “group again

Content slide 17 – The four step approach

Now that we’ve covered the building blocks, let’s talk about actually building a financial forecast model.

Over the next  few sessions we will build a complete set of financial statement forecasts.  We are going to start with the income statement.

For now, we are going to work on the forecast income statement down to operating profit or EBIT.

Once we have completed this step, we will move to the balance sheet where we will forecast operating assets.

We will then forecast finance which has implications for both the income statement and balance sheet.

Finally, we will derive a cash flow forecast.

Content slide 18 – Forecasting operating revenues and profits

The first step in our model building case study is to forecast revenues down to operating profit (or EBIT).

We will stop at this point because after EBIT comes financing costs which will depend in part on what we are forecasting for our operating assets.

Content slide 19 – Forecast revenues

Earlier in this session we talked about the inherent tension in model building between making your model realistic and keeping it simple and robust.

The first principles approach outlined here identifies how you would go about modeling revenues if you need a high degree of detail and precision.

The “Quick and dirty” approach outlines how you can model revenues in a much more straight forward way with the benefit that your model will be smaller as well as easier to follow and audit.

For our case study, we are going to use the “Quick and dirty” approach – using historic figures and trends to predict future growth.

Content slide 20 – Forecasting gross margin and SG&A expenses

After forecasting revenues, we next want to forecast gross margins.

Gross margins are usually forecast as a percent of revenues.

Again you can use historic figures or trends to quickly forecast future margins.

However, you may need to consider a more detailed approach which considers the cost of inputs as well as factors such as economies of scale and learning effects.

This second approach will make your mode more realistic but also more complex and harder to follow.

The next step would be to forecast overhead costs. Forecasting overhead costs such as selling, general, and administration costs (or SG&A) is often done as a percent of revenues.

Although these costs are fixed over the short term, over the longer term they are more variable.  This allows us to link them to revenues.

For forecasts over shorter periods (like weeks or months), using revenues to help forecast SG&A may not be appropriate.

Some models forecast gross and operating profit margins and leave SG&A as the balancing figure.

Content slide 22 – Income forecast part 1 demonstration

Okay, let’s see how building the income statement forecast is actually going to work in Excel.

First off, you will see that all the forecast inputs are on the same section entitled  Assumptions & Drivers

I have then created separate “Output” sections for the income statement, balance sheet, and cash flow statement.

I also have a Supporting Schedules section where detailed processing calculations for PP&E and equity are broken down in order to make the model easier to follow and audit.

We are only going to work on the assumptions drivers and income statement sections right now.

All of my income statement input assumptions down to EBIT can be found in rows 8 through 14.

All of the expenses are being forecasted as a percent of revenues.

Only sales is being forecast based on growth over the previous year.

My inputs on the Assumptions & Drivers section are also ordered in the way they will appear in the income statement.

Now let’s move to the income statement section.  We are going to work in Column D and work downward.

To forecast revenues in 2016, I take the previous year, and grow it by the assumption on the ratio drivers page.  My formula will read =C42*(1+D8).

I then will calculate my cost of good sold.  My formula will read as  –D42*(1-D9).

I will then sum revenues and COGS to get the gross profit (as I have recorded COGS as a negative number).  A great shortcut for summing is Alt + =.

I am then going to forecast all the other operating expenses down to EBIT as a percent of revenues.  I am going to do this once for distribution expenses and then copy the formula down to the depreciation expense.

The formula in D9 will read –D$42*D10.  I put the dollar sign in because I want to make row 42 of cell D42 an absolute reference.

I then copy this formula down using the short cut Ctrl + D (or fill down) and over to the right using Ctrl + R (or fill right).

Finally I net gross profit off with all the other operating expenses to get operating income or EBIT.

Content slide 23 – Income forecast part 1 exercise

Now you have a go.  Click on the attachment link entitled “Forecast Part 1 Exercise.  Once you’ve finished, check your results with the attached “Forecast Part 1 Solution.” Enjoy!

Content slide 24 – Conclusion

That concludes this session on building a financial model – part 1.  The key messages from this session are:

 Clearly separate your  model inputs, processing and outputs

Plan the overall structure of your model before beginning to build the model.

Recognize that there is an inherent tension between building realistic financial models and building robust easy-to-follow models.

Breakdown your model building process into steps.

Bye for now.

Part 2 (video course)

Content slide 1 – Session objectives

Welcome to building a financial model – part 2

By the end of this session you will be able to:

  • Forecast property, plant and equipment and other non-current operating assets

  • Forecast the various components of working capital including accounts receivable, inventories, and accounts payable

Content slide 2 – Our financial forecast modeling framework

To get started, let’s return to the financial forecasting framework introduced in the last session.

In the previous session we worked on forecasting the income statement by forecasting revenues down to EBIT or net profit. Now it’s time to move to the balance sheet where we will explore how to model operating assets such as property, plant and equipment, accounts receivable, inventories, and accounts payable.

Content slide 3 – Forecasting financial statement

At this stage, we are not aiming to forecast the entire balance sheet.

Having forecast the revenues and costs of an operation, the next step is to consider forecasting the operating assets required to generate them.

We will leave all finance-related items on the balance sheet for later.  Right now we are only going to forecast operating non-current assets, accounts receivable, inventories, and accounts payable.

Quiz 1

Do you remember what the two approaches were that we used for financial forecasting in part 1?

  • “Long and clean” approach

  • “Quick and dirty” approach

  • The CFI approach

  • First principles approach

Content slide 4 – Forecasting property, plant and equipment.

We start with modeling property plant & equipment, or PP&E. But before we begin to forecast, there are the same two approaches to consider here as were explained in the first module – the first principles approach and the quick and dirty approach. Applying the first principles approach here identifies how you would go about modeling PP&E if you need a high degree of detail and precision.

The “Quick and dirty” approach outlines how you can model property, plant and equipment in a much more straight forward way with the benefit that your model will be smaller as well as easier to follow and audit.

The other benefit of using the “Quick and dirty” approach is that linking PP&E to revenues ensures that as revenues grow, PP&E also grows.

For our case study, we are going to use the “Quick and dirty” approach – using historic figures and trends to predict future growth.

Content slide 5 – Capital asset (PPE) turnover ratio

The capital asset or PP&E turnover ratio is often used to link capital asset forecasting directly to revenue.

The first formula seen here defines the capital asset turnover ratio.

The second formula shows how we can use forecast sales and capital asset turnover to forecast capital assets.

Content slide 6 – Forecasting PP&E acquisitions and disposals

In more complex forecasts, we may need to breakdown PP&E in more complex forecasts. In order to do this easily within a model, the best approach is to put the PP&E breakdown in a note.

If you recall from the first module, we put all of our ‘Supporting Schedules’ in a separate section to again keep our building blocks for inputs, processing and outputs separate.

Breaking down the calculation will allow us to identify forecast acquisitions and disposals which are necessary to complete a cash flow forecast.

When forecasting PP&E from first principles, we typically start with forecasting acquisitions and disposals and then work down to the PP&E net book value.

When forecasting using a “Quick and Dirty” approach, we do the reverse.  We start with PP&E net book value and work upwards to get acquisitions and disposals.  You will see this first hand in the case study.

Disposals can be forecast based on the historic relationship between the gross cost of opening PP&E and the gross cost of disposals.

Content slide 7 – Forecasting PP&E demonstration

Okay, let’s see first hand how we can forecast PP&E using a “Quick and dirty “ approach

Let’s start on the Assumptions Drivers” section.  Here you see that PP&E will be forecasted using the capital asset turnover ratio and disposals will be forecasted using percent of gross assets.

We are going to build our PP&E forecast on the Supporting Schedules section.

We are going to start by forecasting net book value and then work upwards.

In column D, row 106 we need to put the formula – Forecast sales divided by the capital asset turnover ratio or =D42/D23.

We then move upward.  The next things we will complete are the opening balanced for gross cost and depreciation.  These are simply the closing balances from the year before.  In column D, row 101 the formula will read = C104.  In column D, row 95, the formula will read =C98.

We can take the current year’s depreciation directly from our income statement forecast.  In column D, row 102 we type =-D48.

Next we calculate gross disposals as a percent of opening gross.  In column D, row 97 we type =-D95*D24.  We are going to assume that assets are full depreciated on disposal. This allows us to use the same number in D103.

Finally, we are left with forecast acquisitions in column D, row 96.  This is the balancing figure.  In order to get the balancing figure, we first need to sum all the items in accumulated depreciation.  We then add the net book value with the closing accumulated depreciation to get the closing gross cost of PP&E.

Once we have these subtotals, we can work back to get the acquisitions number.

In column D, row 96 we type =D98-D97-D95

Finally, we can link the net book value forecast in row 106 to our balance sheet.

On the balance sheet section column D, row 66 the formula should read: =D106.

Content slide 8 – Forecasting PP&E exercise

Now it’s your turn.  Click on the attachment link entitled “Forecast model Part 2a Exercise” with instructions.  Once you’ve had a go, check your attempt with the attached “Forecast Model Part 2a Solution.”  Good Luck!

Content slide 9 – Forecasting working capital

Now that we’ve worked through our PP&E, let’s turn to forecasting working capital.

A “First Principles” approach to forecasting working capital typically involves forecasting individual current assets and current liabilities using various working capital ratios such as receivable days, inventory days, and payable days.

Our forecast model will use this approach.

Alternatively, we could calculate working capital as one item in a “Quick and Dirty” way based on historic trends.

Cash although often considered part of working capital will be forecasted as a financing item later.

Quiz 2

What are the three working capital ratios that we use in a first principles approach?

  • Receivable days

  • Inventory days

  • Account days

  • Payable days

Content slide 10 – Working capital equations part 1

The first working capital item that we will forecast is accounts receivable.

The accounts receivable days ratio is often used to link forecast receivables to revenue.

The first formula seen here defines the accounts receivable days ratio.

The second formula shows how we can use forecast sales and receivable days to forecast receivables.

Content slide 11 – Working capital equations part 2

After forecasting receivables, we can then forecast accounts payable.

In a similar manner, the accounts payable days ratio can be used to link forecast payables to cost of sales.

If cost of sales data is not available, revenues can be used.

The first formula seen here defines the accounts payable days ratio.

The second formula shows how we can use forecast cost of sales and payable days to forecast accounts payables.

Content slide 12 – Working capital equations part 3

The last working capital item to forecast is inventory. The inventory days ratio can be used to link forecast inventories to cost of sales.

Once again if cost of sales data is not available, revenues can be used.

The first formula seen here defines the inventory days ratio.

The second formula shows how we can use forecast cost of sales and inventory days to forecast inventories.

Content slide 13 – Forecasting working capital demo

Now that the theory has been covered, let’s use our case study model and actually forecast working capital using receivable days, payable days and inventory days.

Let’s start on the Assumptions Drivers section .  Here you see that receivables, inventory and payables will be forecasted as a percent of sales or cost of sales.

We are going to build our working capital formulas on the “Balance sheet” section starting with receivable days.

The formula to calculate forecast receivables is receivable days divided by 365 times sales.  On the balance sheet section in column D, row 61 I am going to enter the following formula: =D42/365*D27.  The number works out to 14,721.

The formula to calculate forecast inventories is inventory days divided by 365 times cost of sales.  On the Balance Sheet in column D, row 62 I am going to enter the following formula:

=-D28/365*D43.  The number you should get is 8,036.

Finally, the formula to calculate forecast payables is payable days divided by 365 times cost of sales.  On the Balance Sheet in column D, row 72 I am going to enter the following formula:

=-D29/365*D43.  The number you should get is 11,077.

Content slide 14 – Income forecast exercise

It’s your turn again.

Click on the attachment link entitled “Forecast Model Part 2b Exercise” with instructions.

Once you’ve had a go, check your attempt with the attached “Forecast Model Part 2b Solution”.

Enjoy!

Content slide 15 – Conclusion

That concludes this session on building a financial model – part 2.  The key messages from this session are:

Consider forecasting PP&E using the capital asset turnover ratio.  This approach enables you to forecast capital assets quickly and ensures that capital assets grow as revenues grow.

Alternatively, build your PP&E forecast from first principles based on known capital plans and budgets.

Consider using ratios such as receivable days, payable days, and inventory days to forecast working capital.

Bye for now.

Part 3 (video course)

Slide 1  Notes for course

Welcome to building a financial model – part 3

By the end of this session you will be able to:

  • Forecast debt financing and related interest costs

  • Forecast equity financing and dividends

  • Use both iterative and analytic approaches to managing circular references

Slide 2

Let’s return to the four step financial forecasting framework introduced in earlier sessions to review where we’ve been and what we have left to do.

Having completed our forecasts of revenues and operating assets, we can now move to complete the balance sheet and income statement by forecasting debt and equity financing.

Slide 3

Forecasting the finance structure impacts both the balance sheet and the income statement.

Forecasting equity requires forecasting stock issuance and stock repurchases as well as forecasting changes in retained earnings.  Retained earnings changes will be largely driven by our forecasted net income and dividends.

As an aside, when forecasting retained earnings changes in our model, we will complete a ‘retained earnings’ note, much like we used a note to forecast capital assets in the previous module.

Forecasting debt requires forecasting both short term and long term debt as well as the associated interest costs.

Once we have completed our financing forecast, we can complete the cash section thereby completing the balance sheet.  In short, cash is determined simply as  the “balancing figure” in the balance sheet.

Slide 4

When we get started with forecasting financing, the first question that we need to ask is: how do we know what the split between equity and debt financing should be?

To answer this question, we need to consider leverage ratios such as debt/equity and coverage ratios such as EBIT/interest expense.

Typically, financial covenants on loan agreements will dictate maximum leverage ratios and minimum coverage ratios that can be factored into our model.

If this information is not available, we can at least consider management’s willingness to take on additional debt or equity funding as well as the company’s current level of access to the debt and equity capital markets.

Slide 5

There are many practical modeling issues that need to be considered when forecasting the finance structure.

A useful question to ask at the start is whether you need to use a target leverage figure in your model.  In other words, do you need to forecast debt levels as a multiple of equity?

If the answer is no, consider using opening debt to calculate interest and assume that long term debt is constant.

This will keep your model simple and straight forward.  More importantly, it will minimize the chance of getting circular references.

Our financial model case study uses this simple approach.

However, if  you need to use target leverage figures in your model, the next question to ask is “what level of precision is required in modeling the financing structure?”.

Do you need to go down the high complexity route and model debt and equity issuance from first principles?  If so you will inevitably generate circular references that need to be solved.  We will look at solutions for circular references later on in this session.

The half way point is to build your model with an intermediate level of complexity where leverage ratios are used to calculate debt and opening debt is used to calculate interest.

Slide 6 – Forecasting financing

Okay, let’s see first hand how we can forecast financing using the simple approach outlined on the previous screen.

We are going to assume that long term debt is constant.  In effect we are assuming that future asset growth will be funded by equity.

We are also going to use opening debt to calculate the interest expense in order to avoid circular references.

Let’s look at the “ratio drivers” worksheet first.  Rows 16, 17, and 18 have all the debt financing costs.  Long term debt incurs interest costs at 6.2% while any short term debt bears interest at 7.2%.  The short term debt is a line of credit that is currently undrawn.  In order to have the line of credit available, the company pays a “short term debt flat fee” of $80 million.  All of these items need to be factored into our model.

Remember that we are also using opening debt to calculate interest.

The first cell we are going to complete is “interest expense” on the income statement.  To complete the formula we need to bounce between the “ratio drivers” sheet and the balance sheet.  The formula reads: “=-(‘Ratio drivers’!D16*’Balance sheets’!C27)-(‘Ratio drivers’!D17*’Balance sheets’!C23)-‘Ratio drivers’!D18”.

The number you should get in column D, row 14 is $-1284. Netting interest expense with EBIT gives us earnings before taxes of $5,234.

Now let’s move on to the tax expense. On the “ratio drivers” sheet the tax rate as a percent of earnings before tax is forecast at 30%.

In column D, row 16 the formula should read: “=-D15*’Ratio drivers’!D20”.

To complete the income statement, we need to net off the tax expense with earnings before tax.  The number you should get is $3,664.

The last thing to do is to complete the retained earnings note on the “notes” worksheet.

In order to do this, we simply take net income from the income statement and add it to retained earnings in column D, row 26.  The formula reads:  =’Income statements’!D17.

Opening retained earnings is closing retained earnings from the previous year.  As a result, in D25 we type “= C29”.

The dividend line in row 27 is calculated by taking net income in row 26 and multiplying it by the dividend payout ratio on the “ratio drivers” worksheet.  The formula reads: “ =-D26*’Ratio drivers’!D39”.

Finally, we sum rows 25 through 27 on the “notes” page and transfer the closing balance to the balance sheet.  The closing retained earnings balance should be $8,067.              Screen capture

Slide 7 – Forecasting financing 3a exercise

Now it’s your turn.  Click on the attachment link entitled “Forecast model Part 3a Exercise” with instructions.  Once you’ve had a go, check your attempt with the attached “Forecast Model Part 3a Solution.”  Good Luck!

Slide 8 – Forecasting financing 3b demo

Now that we’ve completed the income statement and retained earnings note, let’s now move on to completing the balance sheet.

Recall that we are holding long term debt constant. You will also see that we are holding common stock constant.  We need to link these items on the “ratio drivers” worksheet to the balance sheet.

To link together, the formula in D27 for long term debt should read: “=’Ratio drivers’!D35” while the formula in D29 for shareholders’ equity should read: “=’Ratio drivers’!D37”.

Total shareholders’ equity is $13,829.

With the financing figures in place, it’s time to turn our attention to dividends payable and income taxes payable in order to complete the liabilities section of the balance sheet.  In this model, the dividend is annual, declared at year end but paid after year end.  Therefore we simply take the dividend calculation undertaken on the “notes” page.  Cell D22 should read: “=-Notes!D27”.

For income taxes payable, we will need information from the “ratio drivers” and income statement worksheets.  The “ratio drivers” sheet let us know that 37% of the income statement tax expense is still payable at year end.  The formula in cell D21 should read: “=-‘Ratio drivers’!D33*’Income statements’!D16”.

 And with the liabilities and equity section now complete, we are almost there.  We are going to finish off the balance sheet by using cash and short term debt as balancing figures.  Below the balance sheet in row 36 you will see a cash working figure.  This is the difference between assets and liabilities.

Copying the formula in cell C36 to D36 informs us that the balance sheet doesn’t balance by $4,486.  A positive number means that assets are less than liabilities.

The best way to use Excel’s functionality for balancing purposes in this case is  to use the “max” and “min” functions.

By using the “max” function,  if the “cash working” number is positive, the number will be placed into cash, otherwise cash should be zero.  The formula in D8 should read: “=MAX(0,D36)”

Likewise, by using the “min” function, if “cash working” is negative, the number will be placed into short term debt.  The formula in D23 should read: “=-MIN(0,D36)”

Finally, we can complete all the sub totals and totals.  Total assets should equal $48,418.

Slide 9 – Forecasting financing 3b exercise

Now it’s your turn.  Click on the attachment link entitled “Forecast model Part 3b Exercise” with instructions.  Once you’ve had a go, check your attempt with the attached “Forecast Model Part 3b Solution.”  Have fun!

Slide 10

Earlier in the session, we mentioned circular references and the problems they can cause.  Let’s explore circular references in more detail.

When calculating the interest expense in our financial forecast we have the option of using:

  • Opening debt which is the same as last year’s closing debt

  • This year’s closing debt, or

  • An average of opening and closing debt.

Using average debt will typically give us the most accurate interest expense; however, it can also lead to circular references.

Slide 11

Let’s look at why using average debt leads to circular references.

For simplicity sake, consider a debt facility with no principal repayments and where accrued interest is added to the existing principal instead of being paid out each period.

In order to get closing debt we need two figures – opening debt and the accrued interest expense.

In order to get the accrued interest expenses we need two more figures – the interest rate and average debt.

However, in order to calculate average debt, we need both opening and closing debt.

In essence, we need closing debt in order to calculate closing debt.

This problem is an example of a “circular reference”.

Slide 12

Excel can solve circular references using iteration.  Iteration is the act of trying different values for model variables with the aim of ultimately finding a solution. Each repitition of the process is called an iteration, and the results of one iteration are used as the starting point for the next iteration. In Excel 2007 and later versions, Iteration is accessed from the Office button / Excel options / Formulas menu.

Slide 13

In Excel 2003 and prior versions – Iteration is accessed from the Tools / Options / Calculations menu.

Slide 14

Although we can use Excel’s iteration function, often there is an analytical approach to solving circular references which will help us avoid turning on that function.

Why avoid turning it on?  Simply put, turning on the iteration function can be dangerous when building financial models because once it is turned on, Excel no longer alerts us to any additional circular references.  Instead, it automatically tries to solve these circular references.  This is particularly dangerous if there are multiple possible solutions to a circular reference.

This screen illustrates an analytical solution that can be used when modelling amortizing debt  and that allows us to avoid turning on iteration.

Slide 15 – circular reference demo

Okay, let’s see first hand how we can solve circular references using both the iteration option as well as an analytic approach.

We will start by using iteration on the worksheet entitled “Iterative.”

We need to complete the blue boxes using the formulas on the right.

Let’s start with closing debt.  Closing debt equals opening debt plus accrued interest expense less repayments.  At this stage the result should be $8,000.

The formula in cell C10 for average debt should read opening debt plus closing debt divided by two.

The accrued interest expense in cell C12 should read average debt times the interest rate or: = C10*C11

Finally, to create the circular reference we need to put the results just calculated into cell C5.

This generates a circular reference.  In order to turn on iteration in this version of Excel, which is 2007, we go to the Office Button> Excel Options> Formulas.  In older versions of Excel, you need to go to Options on the Tools pull-down menu.

Turning on iteration solves our circular reference.  The accrued interest expense is $947.

Now let’s try an analytic approach to solving this same circular reference.

First, be sure to turn off iteration  Once turned off, we also need to delete the formula in C5.

Now let’s move to the “Analytic” worksheet.

Again we will start with closing debt in cell C7.  Closing debt equals opening debt plus accrued interest expense less repayments.  At this stage the result should be $8,000.

The formula in cell C10 for average debt should read opening debt minus repayments divided by two divided by 1 minus the interest rate divided by two or: “=(C4-C6/2)/(1-C11/2)”.  The result in cell C10 should be $9,474.

The accrued interest expense in cell C12 should read average debt times the interest rate or: “= C10*C11”

Finally, we need to transfer the result in cell C12 to cell C5.

By using this analytic approach,  we get no circular reference and therefore, we can avoid turning on the iteration option.

Slide 16 – Dealing with circular references exercise

It’s your turn again.

Click on the attachment link entitled “Circular Reference Exercise” with instructions.

Once you’ve had a go, check your attempt with the attached “Circular Reference Solution”.

Enjoy!

Slide 17 Summary

That concludes this session on building a financial model – part 3.  The key messages from this session are:

Consider using leverage ratios and coverage ratios to forecast debt finance

Be prepared for circular references when modelling debt finance and consider both iterative and analytic solutions.

If possible, try and avoid circular references by using opening debt to calculate interest expense.

See you next time for Part 4.

Part 4 (video course)

Slide 1

Welcome to building a financial model – part 4

By the end of this session you will be able to:

  • Understand the importance of incorporating error checks in your financial models

  • Derive a forecast cash flow statement based on a forecast income statement and balance sheet

  • Derive a free cash flow statement that can be used for equity valuation

Slide 2

Let’s return to the four step financial forecasting framework introduced in earlier sessions.

Having completed our income statement and balance sheet forecasts, we can now turn to the cash flow statement

Slide 3

A cash flow forecast can be derived from the balance sheet and income statement.

We start by forecasting cash flows from operating activities before moving on to forecasting cash flows from investing and financing activities.  From these activities, we will arrive at the forecasted net cash movement.

Slide 4

Again, the first step in our cash flow forecast is to forecast cash flows from operating activities.

To forecast operating income, we start with the forecasted income statement.  From the income statement, we use forecast net income and add back forecast depreciation.  We then use the forecasted balance sheet in order to calculate changes in operating assets and liabilities.  For each operating asset and liability we must compare our forecast year in question with the prior year.

In this example, changes in receivables and inventories have the effect of increasing total cash flows.  In other words, receivables and inventories in our forecast year are both lower than the prior year.

Changes in trade and other payables as well as income taxes payable in this example have the reverse effect – decreasing total cash flows from operating activities.

 In other words, both figures are lower in our forecast year than the prior year.

Slide 5  – Operating cash flows demo

Okay, let’s see first hand how we can forecast operating cash flows using figures from our forecasted income statement and balance sheet.

The first item to note is that there is an error check at the top of the cash flow statement worksheet.  This will disappear as the cash flow total at the bottom matches the cash figure in the balance sheet.  Checks like this help ensure the integrity of your model. (Scott – Did you want to actually build and explain the error check?  I think you need an explanation as to how to do so as it is deemed one of the key learning points from the module.  You might want to introduce it in a slide or 2 or keep it here – your choice)

We start our cash flow forecast by linking net income and depreciation to the values forecasted on our income statement.  The formula in cell D7 reads: “=’Income statements’!D17”  while the formula in cell D8 reads: “=-‘Income statements’!D12”

 Once complete, we then move to changes in operating assets and liabilities.  It is important to remember that increases in operating assets have the effect of decreasing operating cash flows.  Increases in operating liabilities have the reverse effect.

As a result, the formula for receivables in cell D10 should read: “=+’Balance sheets’!C9-‘Balance sheets’!D9”

We can then copy down the formula in cell D10 to inventories in cell D11.

The formula for inventories in cell D11 should read: “=+’Balance sheets’!C109-‘Balance sheets’!D10”.

For changes in operating liabilities, our formula will be reversed. The formula for trade and other payables in cell D12 should read:” =+’Balance sheets’!D20-‘Balance sheets’!C20”

Again, we can copy down the formula in cell D12 to income taxes payables in cell D13.

Finally to get net cash provided by operating activities , simply sum all of the items just calculated.

Slide 6 – Forecasting operating cash flows 4a exercise

Now it’s your turn.  Click on the attachment link entitled “Forecast model Part 4a Exercise” with instructions.  Once you’ve had a go, check your attempt with the attached “Forecast Model Part 4a Solution.”  Good Luck!

Slide 7

Now that we’ve calculated our operating activity cash flows,  let’s look at how to forecast cash flows from investing activities.

All of the investing activity items come from specific fixed asset, or Property, Plant & Equipment, forecasts.

If you recall, our model forecasts fixed assets in detail on the “Notes” worksheet, where we assume that assets are fully depreciated when disposed of and that there are no cash flows associated with these disposals.  Our model also assumes no purchase or sales of businesses.

As a result, the only item we will forecast in our model will relate to acquisitions of fixed assets (sometimes referred to as CAPEX).

Slide 8

Once we’ve forecast our investing activities, it’s time to look at cash flows from financing activities.

Most of the financing activity items are calculated by simply comparing the forecast year with the prior year.

In the model we are building, we have included dividends as a financing activity.  In practice, some organizations include dividend cash flows in operating activities.  The choice is yours to make and should reflect how dividends are reported in your actual financial statements.

Slide 9  – Investing and financing cash flows demo

Now that we’ve covered the theory and explained how to forecast the investing and financing cash flows, it’s time to see how this works in practice using Excel.

We start with cell D17, which is where the fixed asset acquisition is found.  We link this to the “Acquisitions” line on the “Notes” worksheet in our model.

In order to link, the formula in cell D17 should read:” =-Notes!D10”

For financing activities, we link common stock, long term debt, and short term debt  to changes reflected on the Balance Sheet.

First, the issuance of common stock in cell D21 will have the following formula:  “=+’Balance sheets’!D30-‘Balance sheets’!C30”

The formulas for long term debt and short term debt will be similar.

For long term debt the formula reads:  “=+’Balance sheets’!D27-‘Balance sheets’!C27”.

For short term debt the formula reads: “=+’Balance sheets’!D23-‘Balance sheets’!C23”.

Finally the dividend in cell D22 is linked to our “Notes” worksheet.  This dividend is annual and is paid after the year end.  As a result, we will need to link cell D22 to column C.

The formula reads:  “= Notes!C27”

To finish forecasting our investing and financing activities, we need to complete the totals.  If done correctly, our “Error” message will disappear.

Slide 10 – Forecasting investing/financing cash flows exercise

Now it’s your turn.  Click on the attachment link entitled “Forecast model Part 4b Exercise” with instructions.  Once you’ve had a go, check your attempt with the attached “Forecast Model Part 4b Solution.”  Have fun!

Slide 11

Now that we have completed a standard cash flow forecast, let’s look at how we can forecast free cash flows to the firm.

Free cash flows to the firm is the preferred approach when valuing equities using discounted cash flows.  Free cash flows to the firm are defined as:

EBIT x (1- tax rate) where the tax rate is expressed as a percent or a decimal .  Since depreciation and amortization are non cash expenses, they are added back.  Capital expenditures as well as increases in working capital are then deducted.  Note that decreases in working capital would be added rather than deducted.

Slide 12

This screen gives an example using simple financial statement extracts for ABC Inc.  We will use this financial information to calculate ABC’s free cash flows to the firm.

Let’s first identify all the data we need.  Our starting point is EBIT.  ABC’s EBIT is 250.  We also need the effective tax rate which for ABC is 33 1/3  %.

We also need depreciation which is 400, and CAPEX which is 300.

Finally we will need to calculate the change in working capital.  In this example, working capital has increased from 100 to 250.

Slide 13

Now that we’ve identified all of the data we need for the free cash flow calculation, we can calculate the free cash flow formula.

EBIT of 250 times 1 minus 33.33% gives us 167.

The difference of 83 represents taxes.  This calculated tax number is larger than the published tax number of 50.  Why?  The difference is due to the fact that EBIT is before interest expense.

Essentially, we are treating the company as though it is unlevered – that is, the company is funded entirely by equity.  We will come back to this difference later on in the module.

The next steps are to add back depreciation of 400, deduct CAPEX of 300 and deduct the increase in working capital of 150.

ABC’s free cash flows to the firm are therefore calculated as 117.-                       I’d like to see a robust animation that draws the numbers from the one slide and places them into this one.  I will have to describe this as I have a visual picture that would take me pages to explain!

Slide 14

Although free cash flows to the firm is the preferred approach to equity valuation, it isn’t the only free cash flow calculation used.  There is another free cash flow variant that you may have heard of, called free cash flows to equity.

Free cash flows to equity are used to determine how much cash a company has left over after satisfying its sustainable obligations.

A simple definition of free cash flows to equity is cash flows from operations less capital expenditures.

Slide 15

Let’s calculate free cash flows to equity for ABC Inc using the same financial information introduced earlier.

The free cash flow to equity calculation has two major differences from our free cash flows to the firm calculation.

As you can see, we have used the same information that we used to calculate free cash flows to the firm – we added back depreciation, deducted our increase in working capital and deducted the CAPEX.  However, for this calculation we also, deduct interest and finally, we deduct the actual taxes rather than deducting taxes calculated based on EBIT.

You may recall that we calculated taxes of 83 when figuring out free cash flows to the firm.  The taxes of 50 deducted here are based on earnings before tax but after interest expense.

Slide 16

Since there are only two major differences between free cash flows to equity and free cash flows to the firm, it is relatively easy to reconcile the two.

By starting with free cash flows to equity of 50, simply add back the interest expense of 100 and deduct the tax shield on interest of 33.

The tax shield on interest is the difference between taxes calculated on EBIT (83) and taxes calculated on earnings before tax (50).

Slide 17 – free cash flows demo

We’ve covered the theory and explained the formula.  Now it’s time to demonstrate the calculation of free cash flow to the firm on the “Free cash flow valuation” worksheet in the Excel model.

The first thing we need to link to this worksheet is EBIT.  In cell D7 the formula should read:  “=+’Income statements’!D13”.

Next,  add back depreciation, linking cell D8 to the depreciation expense in the income statement.  The formula should read:  “=-‘Income statements’!D12”.

Summing the cells gives us EBITDA of 9,425

Next, in order to calculate the tax based on EBIT, the formula should : “=-D7*’Ratio drivers’!D20”.

After calculating the tax, we then calculate the changes in operating assets and liabilities.  As this has already been done on the cash flow statement forecast, it’s easy to simply link these cells to that worksheet.  The formula in cell D12 should therefore read:” =+’Cash flow statements’!D10”.

 We then copy this formula down and find the sub total.  The number you should get is 6,737.

Finally we need to deduct CAPEX in cell D19 which can also be taken from the cash flow statement forecast.  The formula in cell D19 should read “=+’Cash flow statements’!D17”

Summing all the items above should show 3,027 of free cash flows to the firm.

Then,  copy the formulas across.  You know your free cash flows are correct if the target stock price in cell D64 works out to 23.85.

Slide 18 – Forecasting free cash flows exercise

Now it’s your turn.  Click on the attachment link entitled “Forecast model Part 4c Exercise” with instructions.  Once you’ve had a go, check your attempt with the attached “Forecast Model Part 4c Solution.”  Enjoy!

Slide 19 Summary

That completes the course on how to build a financial model.

The key messages from this last session are:

  • Robust financial models include “error” check calculations

  • A standard cash flow forecast can quickly be derived from an income statement and balance sheet forecast.

  • A free cash flow forecast, which is used in equity valuation, can also be quickly derived if the other financial statements have already been forecasted.