INTEGRATED FINANCIAL MODEL
Beginning with the spreadsheet entitled ProformaExercise, we will be developing an integrated set of financial statements that will ultimately be used for decision-making purposes. This will take about 4 classes to complete when we consider additional time for covering the topics of Cost of Capital and we develop a Loan Amortization spreadsheet that we can use in determining how we want to finance a project if we determine that it is a worthwhile one.
The projections will be made by students and the assumptions that they make will appear in Column L. In many cases, there is no “right” or “wrong” assumptions, only those which may seem more reasonable than others. Predicting the future is difficult and uncertain, so assumptions must be made.
Income Statements
The BaseCase tab of the workbook contains historical financial statements for the firm that we will be forecasting. This company’s primary business is in leasing equipment to businesses, such as electric generators, ditch witches, etc., in the San Antonio area. The first thing that was done was to insert 4 new rows between the Income Statements title in row 1 and the years in row 4 to give us room to place certain data that we’ll want to reference.
The driving force behind proforma statements is the level of sales, or revenues, that can reasonably be anticipated in the future. In this case, it was assumed that the future growth in revenues would be the average of the growth rate between the years 2005 and 2010. The figures in row 7 were placed there in order to calculate the effective growth rate, using the IRR function, which was labeled and placed in cell B2. Cell A2 was used to label this assumed growth rate of sales. Cell A3 was labeled Inflation Rate for future use and an estimate of inflation of 2.7% was placed in cell B3. The resulting 11.1% growth rate is the historical average annual increase in sales between 2005 and 2010. This may be on the high side. Sales were flat in the last year, but, then again, the economy has been in a recession as well.
The Cost of Sales was estimated by using the weighted average percentage of sales that it had been in the years 2005 – 2010. This is okay, but it a regression might be a better estimate. One way to find out – try a regression against revenues!
Salaries were forecast using a regression of salaries vs. sales in the preceding six years. As another student pointed out, this yielded a projection of salaries in the following three years that was less than it had been in 2010. Looking at the regression equation, it is not statistically significant, and the variable cost as a percentage of sales was less than 1%. This suggests that it is more of a fixed cost rather than a variable costs. In that case, it is probably more appropriate to simply increase salaries by the rate of inflation over the next 3 years.
Advertising was projected by using a weighted average percent of sales for the years 2005 – 2010. Looking at the past levels of advertising, you can see that there was a significant amount that occurred in 2007, when the firm was trying to replace the business of a major competitor, as well as in 2009 when the current recession really started to kick in. The projected high level of advertising will probably be needed to realize the assumed growth in sales given the current economic climate.
An examination of the past levels of Automotive expense led to the conclusion that this category would simply be increased by the estimated rate of inflation of 2.7% from its most recent total of $36,625 in 2010.
Insurance, which includes all sorts of insurances including auto, property and liability, perhaps unemployment and worker’s compenation, was assumed to be a weighted average of sales. Originally, a regression versus sales was attempted (results on Calculations tab), but the results were found to not be at a statistically significant level. At this point, it was recognized that the use of a weighted average of sales was becoming a common assumption, so the $$ signs in the equation were adjusted accordingly in order to make it easy to copy.
Miscellaneous, the “trash basket” category of expenses, was increased each year by the 2.7% projected rate of inflation.
Professional fees, usually legal and accounting for most businesses, were calculated as the average of the years 2006 – 2010 for 2011 and then increased by inflation for the years 2012 – 2013. The expense for 2005 looked unusually low compared to the rest, so it was left out of the calculation of the average.
Office expense was another category in which a weighted average of sales was used to project the expense for the next 3 years.
Taxes, which could be combination of payroll taxes, perhaps unemployment and worker’s compensation is expensed here, property taxes (including taxes on inventories and other non-fixed assets) were calculated based upon the average for the years 2007 – 2010. 2005 and 2006 were excluded since they seemed unusually low and unusually high for those years. The average was used for the 2011 projection and then increased by the rate of inflation for 2012 and 2013.
Telephone expense was increased simply by inflation. The thought here was that long distance is really pretty much “free” these days with businesses getting unlimited long distance for $20 per month or so. Also, there really wasn’t much variation from year-to-year in this expense in the past.
Utilities were calculated as simply an average of the preceding years and then increased by inflation.
The last category, Depreciation, was calculated as a weighted average of Net Fixed Assets since depreciation expense is derived from the company’s fixed assets. When the formula is entered at first, it returns a value of zero since we have not projected the fixed assets for the company yet.
At this point, we can sum the Total General & Administrative Expenses and calculate the Operating Income of the firm.
The next category on the income statements is Other Income (Expense). This is normally related to one of two things: Interest Income (Expense), both which are related to financing and not operating characteristics of the firm; and Extraordinary Expense, or something that is not normally related to operations. In this case, it is probably related to financing. Since it has bounced around between being positive and negative over the years, the assumption was to just make it zero in the future. We’ll relax this assumption when we develop a part of the model related to financing.
Finally, Taxes were estimated using a tax rate of 8% of Earnings Before Taxes. Since the company is a Limited Liability Company, it pays no federal income taxes – just the State of Texas Franchise Tax which is based on gross profit margins with adjustments. Since the “margins tax”, as it is called, is rather complex, we are just assuming an 8% rate based on a casual observation of the amount paid in recent years as a percentage of EBT.
Balance Sheets
The Cash account of the Balance Sheets will be our “plug” figure; i.e., the amount that is required to make our balance sheets “balance”.
Accounts receivable was assumed to be a weighted average of sales in the past. Similarly, Inventory levels were based on a weighted average of cost of sales (not revenues themselves).
Since we don’t really know what the Other Current Assets are or the nature of the Prepaid Insurance amounts, it was assumed that future levels of these assets would remain the same as that at the end of 2010.
For Net Fixed Assets, a regression was run against sales. Since the results indicated a negative coefficient (indicating that Net FA would go down as sales rise), a weighted average of sales was used for 2011 followed by increases equal to inflation in subsequent years.
Other Assets was left constant. Finally, we were able to total all of the assets.
On the liability side of the balance sheet, the first item was Accounts Payable. A weighted average of the Cost of Sales was used for this item since the two are related to each other.
Taxes Payable was left constant. It is not clear if these are property taxes, payroll taxes, income taxes withheld from employee salaries or what.
For the Current Portion of L-T Debt, Deferred Taxes and Note Payable, the amounts were left constant for the years 2011 – 2013. The primary reason of doing so with respect to the L-T Debt and Note Payable is that these are financing choices and do not necessarily change with sales except through a conscious decision. Deferred Taxes was kept constant since that is really a function of the accounting difference between tax accounting and financial accounting.
Common stock was also left constant. Again, this is a financing decision that is made only if we need additional funding.
Retained Earnings is a function of Net Income and Dividends. As we know,
Beginning R/E + Net Income – Dividends = Ending R/E
In this case, we are assuming (for the time being) that Dividends will be zero. Nonetheless, we will connect it to the Dividends row in the Statement of Cash Flows so that if we decide to pay some dividends, it will automatically update.
Finally, we total all of the Liabilities and Equity. We also inserted a line for Assets – Liab. & Eq. to take the difference between the two. Of course, this needs to be zero in order for our balance sheets to “balance”. They do not balance. The reason for this is that we have to go back and now put in the equation for cash that will make sure that it balances. When the Other Assets, Net Fixed Assets and sum of Accounts Receivable through Prepaid Insurance is subtracted from the Total Liabilities & Equity as the cash figure, the balance sheet does balance.
Statements of Cash Flow
The Statements of Cash Flow were constructed to complete our set of financial statements. Since these are derived directly from the forecasted Income Statements and Balance Sheets, there was no need to make any assumptions. One thing did occur in their construction: When the equation for calculation of dividends was copied across, we got a circularity alert. This was due to our calculation of Retained Earnings in the forecasted balance sheet. The circularity disappeared when we plugged in forecasted dividends for 2011 – 2013 of zero each year.
Project Spreadsheet – Income Statements
Once the integrated set of financial statements has been created, we need to look at what will happen if we undertake some sort of project, be it an acquisition, a new product line, a major advertising campaign, etc. We want to be able to analyze the option in comparison to our BaseCase (or “do nothing”) activities. The first thing to do is to copy the entire set of financial statements from the BaseCase tab to the Project tab.
The project that the company is considering is an expansion to Kerrville, Texas. The estimate of projected sales at the Kerrville location was $400,000 in 2011, $700,000 in 2012 and $1.2 million in 2013 and is reflected in cells B4 – D4. Since the growth in sales reflects a ramp-up period of 3 years, the combined sales for the San Antonio location and the Kerrville project was created by adding the BaseCase sales of the company to the projected sales for Kerrville.
The Cost of Sales was assumed to be the same as in the San Antonio location. Thus, the projected cost of sales automatically updated when we projected the combined sales figures.
Although we concluded previously that the Salaries expense was, for all practical purposes, a fixed cost, we had used a regression for the BaseCase San Antonio salaries. Thus, we assumed that Salary expense for Kerrville would be $50,000 in 2011 and increased this amount by inflation in the following years as reflected in cells B5 – D5. Then, we added this to the projected Salaries of the BaseCase just as we added the two locations’ sales estimates.
Since Advertising was a percent-of-sales in the BaseCase projections, this figure automatically updated; however, it would also have been easy to add in an extra amount for the first year or two in order to reflect the cost of trying to break into a new market.
Projected Auto Expense for the Kerrville expansion is calculated in cells F2 – H2 on the Project worksheet. Although all three years of the project were calculated as growing at the rate of inflation and added to the projections for the BaseCase, the $15,000 increase for Kerrville would have automatically been adjusted for inflation by the existing equation which projected this category of expense only increasing by inflation in the BaseCase as well.
Insurance was already calculated as a weighted-average of Revenues, so this figure automatically updated when we projected the combined revenues.
Miscellaneous Expense was increased by inflation each year beginning with the 2010 amount of expense in the BaseCase. For the combined cost, including Kerrville, an amount of $5,000 was assumed to be relevant for Kerrville (cell F3) and added for 2011. 2012 and 2013 then automatically increased the combined amounts by the rate of inflation.
Professional fees were increased by $4,000 in 2011 (cell F4) to cover anticipated costs for Kerrville. As in the case of Miscellaneous Expense, the values for 2012 and 2013 also automatically adjusted for the rate of inflation.
Since Office Expense in the BaseCase was calculated using a weighted average of sales, the projection for the combined income statement automatically updated with the Revenue figures.
Taxes were assumed to increase by $7,000 in 2011 (cell F5) for Kerrville. Since 2012 and 2013 are based on taxes increasing by inflation, these figures automatically updated once the $7,000 Kerrville cost was incorporated into the equation for 2011.