Building a Pro Forma Forecasting Spreadsheet in Excel
By Steven C. Isberg, Ph.D
January 2002
Introduction
As you have learned in this course, a financial statement forecast is usually based on a set of forecast parameters that measure certain values as a percentage of sales and as a percentage of other performance outcomes. In this model, the notion of such parameters will be broadened to include other relationships that are used in forecasting, for example, the fact that inventory is more closely associated with the cost of goods sold as opposed to sales. What will be done in this exercise is twofold. First, we will build a forecast of an income statement and partial balance sheet using these parametric relationships; second, we will convert the forecast data into a net cash flow and discount it to determine value of the entity for which the forecast is being made. In this case, the entity is a company.
As you work through this exercise, it will be a good idea to print this document and work through it with Excel open on your computer. This will give you step-by-step instructions in building the spreadsheet. You can start by opening the Excel file named: proformabuilding.xls.
Proformabuilding .xls
What you will see in this file are several rows and columns of information. The first column provides the titles of balance sheet and income statement accounts. The labels included in parentheses ( ), indicate what parameter is used to forecast values for that account in the future. For example, when you look at row 17, accounts receivable, you can see that the accounts receivable turnover will be used as a parameter to forecast future account receivable balances. Column B provides the actual data for the year 2001, and column C provides the values of the parameters to be used in the forecasts. Column D repeats the information for 2001.
Parameters
Forecast parameters are usually determined by a combination of financial analysis and the judgement of the financial analyst him or herself. They may be based on targets set by the company in determining its strategy. In the next few steps, we’ll go through each line and discuss the parametric relationships.
Sales
The parameter for forecasting sales is the sales growth rate. The growth rate is an estimate based on all information available to the analyst. It is subjective in nature, but may be based upon analysis of trends in the past. For this example, it is estimated to be 5%.
Gross Profit
The parameter used to forecast gross profit is the gross profit margin. It can be determined based on past performance of the company or it can be subjectively set based on a target or a value based on expected changes in the market for the product. In this case, the 40% gross profit margin is based upon the most recent actual performance of the company (gross margin = gross profit/sales = $60,000/$150,000 = 40%).
Cost of Goods Sold
Cost of goods sold is simply the difference between sales and gross profit, so we don’t need a separate parameter to forecast it.
Operating Expenses
Operating expenses here are forecasted by relating them directly to sales. In this case, it is assumed that operating expenses will be 15% of sales, as they were in the year 2001.
Depreciation Expense
Depreciation expense is driven by the value of the gross fixed assets and the particular depreciation method being used. For the purposes of the simple forecast, we will assume that depreciation expense in any given year will be 10% of the previous year’s gross fixed assets. As you will see, this will help avoid a circularity problem in Excel when we set up our forecast.
Net Operating Income
NOI is simply the difference between gross profit and the operating and depreciation expense, so no parameter is needed.
Taxes
Taxes are based on a tax rate, which in this case is assumed to be 40% based on the company’s tax paying position and situation.
Net Income
No parameter is needed because net income is simply EBIT less taxes.
Cash
Cash is typically forecasted as a percentage of sales. In this case, the parameter for forecasting cash is going to be 2% of sales, which reflects the relationship for 2001.
Accounts Receivable
Accounts receivable are usually benchmarked and forecasted by an analysis of the turnover or days sales outstanding. This is done because these are the two ratios used to evaluate the accounts receivable management performance (you know this from the chapter on financial ratios). The turnover is the simpler of the two to use, and in this case, it is set to 10 times per year based on the 2001 performance.
Inventory
Like accounts receivable, inventory is benchmarked using either a turnover or days cost (of good sold) outstanding. Rather than being linked to sales, inventory turnover is more correctly related to cost of goods sold. The turnover parameter of four (4) used in this example is again based on the company’s performance for 2001.
Gross Fixed Assets
Gross fixed assets are not usually related directly to sales on a year-to-year basis. Rather, fixed asset investment decisions are made on a discretionary basis by management, and may differ from year to year. For the purposes of this example, we are going to assume that management will reinvest all depreciation PLUS and additional $1,000 per year in additional capital expenditures.
Accumulated Depreciation
Accumulated depreciation for any given year is simply the sum of last year’s accumulated depreciation and the current year’s depreciation expense.
Net Fixed Assets
As you well know, net fixed assets are the difference between gross fixed asets and accumulated depreciation.
Accounts Payable
Like accounts receivable, accounts payable are usually benchmarked in a financial analysis by a turnover or days cost (of good sold) outstanding. They are related to cost of good sold since such purchases tend to be for inventory. The turnover ratio of 10 times per year is chosen here based on their recent performance.
Accruals
Accrued expenses or accruals, are created by things such as wages and salaries payable, and are related to operating expenses. In this example, the forecast parameter of 10% of operating expense is based on the company’s performance for 2001.
From Parameters to Forecast
The actual balance sheet and income statement data are copied into column D to make the actual forecasting calculations easier. The forecast begins with a sales forecast and proceeds down the spreadsheet to calculate other values, many of which depend on the sales forecast. As you work through this part of the exercise, you will be directed to set up your forecast by creating formulas within the Excel spreadsheet. By the end of the exercise, you should be able to create your own forecast from scratch (YES—believe it!!). Begin by entering the label “2002E” into cell E2 (enter what is within the quotation marks, not the quotation marks themselves). Be sure to properly format the cells where you will be working to display currency values with no decimal places and dollar signs in front. Also be sure to set it up so it displays negative values in parentheses. Use the “FORMAT CELLS” option to do this.
Sales Forecast
The sales forecast for 2002 will be based on actual sales for 2001 and the forecasted sales growth rate parameter. The mathematical relationship is:
Sales t = Sales t-1 * (1+ g).
In Excel you will input the following formula into cell E4, but before you put it in, read past the next line:
=D4*(1+$C$4).
To put this formula into cell E4, let your mouse be of service to you by completing the following steps:
1. type an “=” sign (do not type in the quotation marks, only what is within them)
2. Move your mouse and click on sales for 2001 in cell D4, notice that the reference for cell D4 will now appear in the formula you are creating.
3. Type the “*” symbol.
4. Type in a left parentheses “(“
5. Type in “1 +”
6. Move your mouse and click on cell C4 (the 5% parameter) and then hit the F4 key. You will notice that when you hit the F4 key, a $ will appear in front of the C and the 5. What this does is to “lock” that reference onto that cell, so when you copy the formulas over to the next four years of the forecast, each formula will refer back to that exact cell to find the growth rate. Otherwise, when you copy the formula over to the next column (i.e., the next year in the forecast) it will change the cell reference from C5 to D5, which you don’t want to happen.
7. Type in a right parenthesis “)”
8. Hit “ENTER”
9. What should appear in cell E4 is the value $157,500 for sales for 2002E.
From now on, you will NOT be typing cell references into your Excel spreadsheet. Whenever you want to refer to a value in a cell, you simply move your mouse to click on the cell. Be sure that before you do this you have the proper mathematical or function operator inputted. For example, I start every formula in Excel by typing in the “=.” This tells Excel that you want to create a formula and it allows you to then point to a cell. Likewise, I could point to cell C5 in the previous equation because I had inputted a “+” before doing so.
Gross Profit Forecast
Gross profit will be 40% of sales. Example: Sales * GP%. In Excel, this will be handled by a formula that multiplies sales for the year (found in E4) by the GP% found in C6. This formula in cell E6 is:
=E4 * $C$6.
To write the formula, use the following steps:
1. Type “=”
2. Move your mouse and click on cell E4
3. Type the “*” sign
4. Move your mouse and click on cell C6 and hit the F4 key
5. Hit “ENTER”
If you have done so correctly, you should have a value of $63,000 as a gross profit for 2002E.
Cost of Goods Sold
Cost of goods sold will be the difference between sales and gross profit. It should appear as a negative value since it is a cost. You will enter the following formula in cell E5:
=(E4-E6)*-1
Again, let your mouse do the work:
1. Type “=(“
2. Move mouse and click on cell E4
3. Type “-“
4. Move mouse and click on cell E6
5. Type “)”
6. Hit enter.
The correct input will result in a value of ($94,500). You did not put $’s in front of either the row or column references because as you copy that formula to the next year, you want the formula to use sales and gross profits from that year to calculate cost of good sold for that year. When you do copy that formula over into F5, it will appear as =(F4-F6)*-1.
From here on, you will not be prompted to let the mouse do the work of entering cell references into formulae. If you want to take full advantage of Excel and work faster, it is the only way to go!
Operating Expenses
Operating Expenses are 10% of the current year’s sales. To put this into a formula in cell E7, you will need to multiply this year’s sales by 10% parameter found in column C, and then by negative one to reflect is as an expense, as follows:
=E4*$C$7*-1.
The correct entry will yield a value of ($23,625), with the parentheses indicating a negative value in Excel (if you have the cell formatted to show a negative dollar value in parentheses instead of with a negative sign).
Depreciation Expense
As mentioned earlier, depreciation expense will be based on a straight-line percentage and the gross fixed asset value for the previous year. In this case, what you want to do for 2002 is to multiply the gross fixed asset value for 2001A by the depreciation percentage of 10% in the parameters, as follows (input in cell E8):
=D21*$C$8 *-1.
Again, you multiply be negative one to convert the value to a negative number. The correct value should be ($10,000).
Net Operating Income
The net operating income is simply the sum of the gross profit and the operating and depreciation expenses. You can use a simple summation function because the expenses have the proper negative signs. In Excel, you may either build a formula or use the “SUM” function. To get an index of functions and how to use them, you can click on the “function help” key in Excel, which is the f x icon in the toolbar. Your formula will look like this in cell E9:
=SUM(E6:E8).
To enter this formula, do the following:
1. Type “=sum(“
2. Move your mouse to cell E6, click AND HOLD
3. Pull the mouse down to cell E8 while holding
4. Release the click
5. Type “)”
6. Hit ENTER
The value displayed should be $29,375.
Taxes and Net Income
Taxes are simply 40% of the net operating income, and net income is the difference between NOI and taxes. You will need to input the following formulae. Be sure to let the mouse do the work as often as possible.
In cell E10: =E9*$C$10*-1
In cell E11: =SUM(E9:E10)
You should get ($11,750) for taxes and $17,625 for net income.
The Balance Sheet
Cash
Cash is a simple percentage of sales. You have done formulae for percentages of sales already (gross profits, operating expenses). See if you can figure this out and set it up on your own. The correct answer for cash as 2% of sales is $3,150.
Accounts Receivable
Accounts receivable (A/R) are benchmarked using a turnover ratio. The turnover ratio is typically calculated as follows: