ch08boc-model

# ch08boc-model - A 1 2 3 B C D E F G H I BOC Model for...

This preview shows pages 1–2. Sign up to view the full content.

1 of 9 BOC Model for Chapter 8, Financial Forecasting 3/1/03 Note also that the model contains circular references. This is done deliberately, so if Excel asks about it, just click "Cancel." Finally, note that we use Excel's Scenario Manager in this model. You can skip this, but Scenario Manager is a very useful tool, and we use it in a number of subsequent chapter models. Therefore, if you have time, It would pay you to to learn how to use it. See the explanation on the tab labeled "Using the Scenario Manager Tool" when you get to the appropriate section of the Main Model. THE SALES FORECAST Financial planning begins with a sales forecast, and that forecast generally starts with a review of the firm's recent history. Here are Company X's sales over the past 5 years: Sales Growth 1999 \$844.5 2000 \$1,400.0 65.8% 2001 \$1,500.0 7.1% 2002 \$1,666.7 11.1% 2003 \$2,000.0 20.0% Average = 26.0% To make the graph, highlight the range A12:B16, click the chart wizard, select the scatter diagram, and then choose the graph with dots and curved lines. You can "pretty up" the graph, but you don't have to. You can add the trend line by having the chart active, then clicking "chart" on the tool bar and then "add trend line." Then click "Options" and check the appropriate boxes to get the equation and R-square. There are several ways to estimate the historical growth rate. The simplest is the average annual growth rate as shown above. We could also get the point-to-point compound growth rate, from 1998 to 2003, using the RATE function: Compound annual growth rate = 24.1% Note, though, that this ignores all data for the intervening years, hence is too heavily influenced by the first and last years. It is generally better to use regression to estimate growth, as this takes account of all the data. Also, it is better to regress logs of the data rather than the data themselves. You could use Excel's regression function, but it is easier to use the LOGEST function, proceeding as follows: Click fx > Statistical > LOGEST. Then specify C13:C17 as the Y variable range and G 13:B17 as the X variable range. Click OK and subtract 1.0 to get the growth rate: This is the best historical growth rate: 20.9% The regression forecast, rounded to 20%, is used in the forecast. 20% This model goes through the steps required to make and then evaluate a financial forecast. No forecast will ever be exactly correct because the inputs cannot be predicted exactly. Still, given the inexact nature of the forecasted inputs, this model is accurate enough for most purposes. The model goes through a series of iterations to find the AFN that causes the liabilities and capital to equal total assets. FOR THE ITERATIONS TO OCCUR, YOUR COMPUTER MUST BE SET TO ITERATE. Click Tools>Options>Calculations and then check the "Iterations" box and enter 100 in the number of iterations box. We provide historical data on Company X, along with industry average data.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

## This note was uploaded on 04/22/2011 for the course BBA FIN 423 taught by Professor Mahmudulhaque during the Spring '11 term at BRAC University.

### Page1 / 9

ch08boc-model - A 1 2 3 B C D E F G H I BOC Model for...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online